Re: Auto Tuning

[rob@xxxxxxxxx]

If 'native' became popular after SQL, would you say that it's pathetic 
that tuning is necessary to enhance native?

Or that switching an application from interactive to batch makes it 
pathetic because tuning may improve the batch run?

I'll agree that, just like native, some coders using sql, have written 
code with performance never in mind.  However they both have their places 
and they do their work differently.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Nathan M. Andelin" <nandel@xxxxxxxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
02/04/2004 02:35 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
<midrange-l@xxxxxxxxxxxx>
cc

Fax to

Subject
Re: Auto Tuning






Clare,

It sounds like we agree that Peter is robbed to pay Paul when pool memory
sizes and activity levels are adjusted, but whether auto tuning or manual
tuning is doing the robbing, is a matter of perspective.

The case for enabling auto tuning is that its primary function is to
minimize paging.  When there's no paging, the auto tuner leaves memory and
activity levels alone.  Paging is a useless activity that robs CPU time 
from
threads that perform meaningful work.  When paging is high, memory may be
added, or the activity level reduced.  For the benefit of all, it makes
sense to minimize paging.

Although believable, it sounds pathetic that manual tuning is needed for 
the
benefit of SQL.  It sounds like application developers are abdicating 
their
responsibility for writing efficient code.  It's sad that the SQL 
optimizer
would need to be activated to soften a problem that should have been taken
care of when the code was written.

When adjusting time slice, keep in mind that depending on iSeries model, 
the
CPU on one system may be 20+ times faster than another (among current
models), and CPU speed has increased from one generation of the box to the
next.

Nathan.




----------------------------------------------------------------------

message: 1
date: Wed, 4 Feb 2004 06:41:01 -0000
from: "Clare Holtham" <Clare.Holtham@xxxxxxxxxxxxxx>
subject: Re: Auto Tuning

I don't agree at all - that's what autotuning does - robs Peter to pay 
Paul.
The only time I would leave this on is where the customer does not have
enough memory overall.(Recommended for BPCS sites at least 2Gb per
processor.)

Tuning a pool (reducing the activity level etc) is crucial where an
application uses SQL, and since I usually tune for BPCS this is where huge
performance improvements can be achieved. The SQL optimizer looks at many
factors before deciding how to process a statement, including the size of
the pool divided by the activity level. Changing this can mean the
difference between an inefficient way of processing, and a way that uses
more memory but is many times faster.

Obviously index creation is also crucial for SQL, and it is also worth
remembering that if the SQL Optimizer decides to dynamically create an 
index
that doesn't exist, this process (creation of an index) is dedicated and
does not look at any timeslice - if the file is large it can take many
seconds to complete.

Also, I can verify that for most BPCS sites (mostly complex processing) 
the
appropriate timeslice is between 200ms and 300ms, rather than 50ms, 
although
this may prove different on the latest hardware. I imagine this would also
apply to similar applications using SQL rather than straight RPG.

But if you're not doing complex commercial workloads, then maybe autotune
will work for you....

cheers,

Clare



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