Agreed about indices, but in prev post wanted to bring up the list of other
causes that are often overlooked.
PTFs, the QAQQINI implementation, the Win part itself (and for the years
I've been at this shop, 50%
Of the issues we have traced to the Win side), and CQE versus SQE.
The analyzer, both in SQL Plan cache and SQL Performance Monitors has been a
big help.
Having been bitten badly twice by ptfs in IBM's transition from CQE to SQE,
we don't load ptfs on production until
An extensive database test run in development.
And since the OP was about pools - we run the system in default shared
pools. They have never given us a big enough configuration to divide up that
way (large amount of disk, moderate memory, but no big multiprocessors). We
have a large volume of sql hitting us from Win systems all day & night, plus
a fair amount of sqlrpgle, and the system handles it well.
Jim Franz
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Thursday, December 08, 2016 8:57 PM
To: Midrange Systems Technical Discussion
Subject: RE: Shared pool vs Private Pool for SQL
Jim,
We were seeing totally unchanged queries jumping from 20 minutes to 2.5
hours. Same volume of data.
Weird thing was you could restore it on other lpars and not have the same
issue. And, the restore was on a less beefy lpar and/or box.
This particular lpar needed the indices.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "midrange" <franz9000@xxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 12/08/2016 08:51 PM
Subject: RE: Shared pool vs Private Pool for SQL
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Indexes can make a big difference, but the 20 minutes to 24 hrs indicates
it
is likely something else...
Our database is a fair amount larger on a Power 7+ single core and they
pull
almost the entire database into a warehouse
In about an hour every night..
In our shop the win team is sometimes changing the queries so we have to
watch the indexes.
Certain changes could cause it to run the old "classic" engine which is
most
likely much slower (but not always).
The analyzer can tell you if old versus newer SQE.
DB2 changes via ptf have bitten us couple times where it would spin for
hours, on a very complex query - we sent it to the lab
And they said it broke theirs as well (it was one query pages long ...)
The
win guys rewrote it after I pointed out how bad some of it was.
I assume the network connections and packet sizes and all that did not
change.
A big possibility is that the win team changed or upgraded the data
connection product or parms - I have seen dramatic changes.
We do build all indexes as logical files so the Power I team has view of
the
source code (still running Hawkeye to track all the database and programs.
Jim Franz
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
DrFranken
Sent: Thursday, December 08, 2016 8:14 PM
To: Midrange Systems Technical Discussion
Subject: Re: Shared pool vs Private Pool for SQL
Faults? None. Paging? None. Those were all those .0's from WrkSysSts!
That's the frustrating part. The thing is doing almost no I/O at all but
burning four Power8 processors at 100% each. Basically for 24 Hours.
We are building the indices and hope they make the difference we need!!
- Larry "DrFranken" Bolhuis
www.Frankeni.com
www.iDevCloud.com - Personal Development IBM i timeshare service.
www.iInTheCloud.com - Commercial IBM i Cloud Hosting.
On 12/8/2016 2:44 PM, Charles Wilt wrote:
Doc...
My first thought is that the 50% more data has outgrown the memory
pool....
But I'd expect to see more disk I/O...what do the fault rates look like?
My understanding, private pools are great for ensuring a given level
of performance, but the cost is extra admin to change them if the
workload changes. Adding 50% more data, I'd expect to have to add
memory to keep the same level of performance.
Charles
On Thu, Dec 8, 2016 at 12:19 PM, DrFranken <midrange@xxxxxxxxxxxx>
wrote:
Have a customer running some pretty serious SQL queries from MS
products to i. Those have recently begun taking Waaaaaaaaaaaaaaaaaay
longer than they used to. Like from 20 minutes last month end to 24+
Hours this time.
Only difference is about 50% more data. Yeah that's a lot but it
shouldn't make this level of difference!!!
Power8. 6 Cores. 384GB Memory. SSD Storage on Storwize. i 7.1.
Current PTFs.
The queries in question (QZDASOINIT) run in a private memory pool to
a subsystem NOT in a shared pool. Thus the paging option (*CALC
*FIXED) is forced to *FIXED.
Anyone seen where this makes a significant difference moving to a
shared pool with *CALC? Given the fabulously low I/O I'm thinking not.
A bit more on the environment:
The pool has 15GB memory and just one query at a time runs there. For
EXTENDED periods of time the I/O numbers for the pool from WRKSYSSTS
are
.0 .0 .0 .0 .0 .0 .0. I/Os in the job are from WRKACTJOB might
be
4 or 5 per second. There are four active threads each effectively
consuming a full processor and all are running
DbopThreadMain__FP14DbopThreadParm.
The jobs burn through 250,000 CPU seconds in 24 hours.
We did bring up visual explain and yeah there is a lot going on
there. The thing could make a cool "FatHead" decal and would need to
be that big to be readable on a wall. But the same queries ran last
month
so....
IBM's only advice is 'you need to build the indices the query
optimizer is recommending.' There are 9 it recommends with a total
build time of about
45 minutes so not insignificant. Tables are in the 4M rows plus in
size so 'big' but certainly not 'huge'. We are working on building
these to shop standard naming. Hoping they really do help!
--
- Larry "DrFranken" Bolhuis
midrange.com
