Re: Shared pool vs Private Pool for SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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
>
> www.Frankeni.com
> www.iDevCloud.com - Personal Development IBM i timeshare service.
> www.iInTheCloud.com - Commercial IBM i Cloud Hosting.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD