Re: Shared pool vs Private Pool for SQL

I'm a bit late to this party but after the index builds, did you also remove
the existing plan caches so they would rebuild? With autotune off (a
good thing in an SQL environment my view) the optimizer may not
automatically rebuild the access plan just because an index showed up.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Diego
Kesselman
Sent: Friday, December 09, 2016 9:57 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: Shared pool vs Private Pool for SQL

Have you checked the Explain Plan after index creation?
We have experienced performance issues when having a PK constrain

On the other hand...

You mentioned the queries WERE really fast. When you checked that, on the
storage system, how was the workload? Do you have another systems on the
same storage with high workload? Is this storage system an IBM Storwize?

Diego Kesselman




El 09/12/16 a las 09:50, DrFranken escribió:

I am not clearly an expert on the tables themselves and there are MANY
tables involved in the queries. I do expect many of the tables have
unique keys. but I do not believe there are any constraints on these
tables at all.

I do know they were DDS created.

QQRYDegree is set to *OPTIMISE. Not willing to mess with this one just
now. Possibly override with QAQQINIT would be an option.

The index advisor summary has MAN MANY advised indices. Many. About 40
were built overnight.


- 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/9/2016 12:50 AM, Diego Kesselman wrote:

Just a couple more questions (sorry):

1) Do you have a Primary Key constrain on the tables? If so, have you
tried to create a copy with an external DDL Unique Index and NO PK
constrain?
2) Have you checked the Condensed Index Advisor view on the tables
involved?
3) Have you tried to lowered the aggressiveness for QQRYDEGREE to
something more conservative? Even *NONE?
4) Is this a DDS or DDL based table?

Regards

Diego Kesselman


El 08/12/16 a las 22:31, DrFranken escribió:

lparstat -E on the VIO partitions shows 3.0Ghz(100%) so no
restriction there.

For the first month end Yes! It took off and ran much quicker. Then
for this second month end, *SPLAT. Ugly.


- 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 11:21 PM, Evan Harris wrote:

Well you could run more threads if you wanted (but that's
potentially just another rabbit hole to disappear down). It might
help your performance for later on so maybe somethgni to keep up
your sleeve.

Did you get the performance improvement you expected on power 8 ?
We had
some CPU's that went into safe mode and were running at around
2/3's capacity so RRD2LPAR and performance tools would show the CPU
at 100% but the frequency of the chip had been dropped due to a
power cyclign test.
When we compared Rperfs/CPW between the Power 7 and Power 8 kit we
were left scratching our heads. That started out as a performance
investigation as well. Don;t know if you have any AIX LPARs but
lparstat -E will show you how what frequency the chip is runnign
at.

On Fri, Dec 9, 2016 at 5:12 PM, DrFranken <midrange@xxxxxxxxxxxx>
wrote:

Indices seem to be the favorite target here. Also IBM pretty much
said "Build indices or stop calling." And so we are. Besides I
think everyone here has seen building an index or three that
'shouldn't matter'
but IT
MATTERED! It's been a bit of a push to get the customer to agree,
though he finally does.

Performance explorer showed as you describe, huge CPU queuing.
More the
twice the time spent queuing as in the processor. And the
processors were at 100%.

As the system i i 7.1 it's running 4 threads.
DB2 Multi-system IS installed. It was NOT last month end. Seems
like all it does is allow more processor burn. So that's actually
a 'Thing that changed' as we say. Hadn't realized when it was
installed until you asked.


- 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 10:43 PM, Evan Harris wrote:

Hi

I figure you have this covered anyway, but I have a few questions
apart form the obvious (just being nosey really).

Last couple of times I had something like this it was absolutely
indexe related. In once case, we had some temp indexes that got
dropped as part of a restore that no-one noticed and once we did
what the advisor said all was good again. I wonder whether an IPL
or restricted state might have some kind of effect on the SQL
plan cache or whatever. The SQL advisor and plan stuff was really
interesting/useful for looking at this stuff.

How many threads are you running on the power 8 ?
Is DB2 multisystem on for these queries ?
Are you collecting statistics ?

Anything interesting in Performance explorer ? Recently saw
interesting stuff in terms of excessive CPU queuing on a Power .
Using the Wait graphs turned up a lot of waiting on CPU queues

It strikes me that all that extra data could cause some
interesting changes to the best way to query, so I would be going
with the indexes....


On Fri, Dec 9, 2016 at 4:34 PM, DrFranken <midrange@xxxxxxxxxxxx>
wrote:

We do know it's using SQE it reports that.

The queries do come from outside the i as you mention yours do.
A single query is 20K in a text document. We cannot with
absolute certainty say they haven't changed the queries but the
analysts swear they didn't.

There were no PTFs loaded since last month. We did go restricted
and take a full SAVE 21 and then bring it back up. Not sure if
anything would change based on ending and restarting subsystems
like that. I certainly would not expect any.

One other thing is that last me that all that extra data could
cause some interesting changes to the best way to query, so I
would be going with the indexes....
me that all that extra data could cause some interesting changes
to the best way to query, so I would be going with the indexes....
month-end was the first on Power8. It was MUCH faster than on
the Power7 before it. The Power7 system had internal disk but
most were SSD and had 3 cores vs 6 and about 1/4TB memory vs
3/8TB. This is the second month end on Power8 and the again the
primary change was about 50% additional data added due to a new
customer.

We are in the process of building the requested indices as well
as a few others recommended.

- 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 8:50 PM, midrange wrote:

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

--

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

--

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

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