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 ...
-
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.
-
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.