Re: Shared pool vs Private Pool for SQL

I have seen similar performance issue when implementing fieldproc() for
encryption on some tables... we had to review the DB design to isolate
private data from big tables and access it only on purpose...
So just an idea : no new fieldproc or trigger in you database ?

Paul




From: DrFranken <midrange@xxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 09/12/2016 05:31
Subject: Re: Shared pool vs Private Pool for SQL
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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