Basic machine setup - IBM AS400

This is a discussion on Basic machine setup - IBM AS400 ; Hello, I am working for a company that has an iSeries 520 that has 8gigs of memory and a terabyte of storage. My chief job here is development. In a previous job I developed and took care of several machines ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: Basic machine setup

  1. Basic machine setup

    Hello,

    I am working for a company that has an iSeries 520 that has 8gigs of memory
    and a terabyte of storage. My chief job here is development. In a previous
    job I developed and took care of several machines (beige boxes and a 520).
    The box is having performance problems especially SQL jobs run very slow.

    It was my understanding that the basic machine setup should include shared
    memory pools and the subsystems should then be assigned to these pools.
    Each pool having reserved memory. The people who take care of the machine(s)
    are of the opinion that the sub-systems should just run out of *base.

    They also are of the opinion that running the performance tools and
    associated utilities to report back to IBM about problems and performance
    are not worth setting up. (This explains why they have been down three
    times in the last 4 months). I am of the opinion that they need PT running
    so they can at least get a baseline on the performance of the box.

    I have been told that the auto tune feature on the box, when they turned it
    on, did a very poor job and in fact caused more problems than it solved.

    Additionally they added another 8 drives in an external cabinet and did not
    balance the data over the new drives from the old drives. the old drives
    are 91+% full while the new drives are at about 20%. I believe this is a
    big problem but have failed to convince them.

    Any comments will be appreciated and any advice is welcome.

    Thank you in advance.



  2. Re: Basic machine setup

    The out of balance on the disk drives can be a problem. However the
    system should balance them eventually. A posible problem here is if
    the drives are different sizes. This can drive the system crazy
    trying to balance them.

    The *BASE is part of the SQL problem, esp with large or long-running
    queries.
    Here is why: When the SQL optimizer runs, it calcs best processing
    methods/paths based on available memory. However, it doesn't
    "reserve" that memory. So, if for example, it might see 7G available,
    but as the job runs, some of that gets taken away by other jobs, and
    by the IBM auto-tune feature. So ten minutes into the query it might
    find only 3G available, and the query starts thrashing trying to use a
    method that requires a lot more memory. Assigning SQL to its own
    memory pool, you can control this. I have done a lot of SQL
    performance tuning with very good results. Beside building the
    suggested indexes, here are a couple of things that helped a lot.

    1. Put SQL processing in its own memory pool, and set the minimum and
    maximum size identical (say 2MB). Then SQL always knows what memory it
    has and gets to "keep" it.
    2. Make sure files QUSRSYS/QAQQINI file has option
    IGNORE_DERIVED_INDEX set to *YES (default is *NO). This allows a lot
    more queries to use SQE rather than CQE.


+ Reply to Thread