light scans with update statistics
User reported UPDATE STATISTICS LOW taking several hours on large tables in Informix 11.50 FC8, stuck in IO Wait despite low I/O activity. Light scans don't apply to LOW statistics since it reads index pages in index-key order, not full table scans.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix environment: 11.50 FC8 with HP-UX
Hi,
I want to improve the performance of "update statistics low" for large tables.
I have the following facts:
a) When I run "update statistics low for table <bigtable>", the sentence runs
for several hours.
b) When I review the details of the session with onstat-g ses appear status =
IO Wait- most of the time.
c) I have reviewed in the operating system level and the amount of I/O
operations barely growing. In terms of CPU and memory, the operating system
seems not to be much variation.
d) When I check onstat-g lsc nothing appears. I tried with set "isolation to
dirty read" and LIGHT_SCANS = FORCE but with no success.
My questions are:
1. Why "update statistics low" not use light scans?
2. What other considerations should be taken to improve the performance of
update statistics low?
Thanks in advance,
Roger
Update statistics low takes so long because it's not reading your data
pages... It's reading the index pages in an ordered way (by index key)which does not mean an ordered read in physical terms. It really depends on
your index layout. Light scans will not be used for this because it's not a
full scan of the table.
There are things that can make it better but all of them have drawbacks:
1- Rebuild your indexes. They tend to be more "physical ordered" and that
helps because the system will take a bit advantage of hardware cache. The
gains can sometimes be relevant, but naturally on big tables this may not
be practicable. The gains will probably be more evident on indexes with
timestamps
2- Run UPDATE STATISTICS LOW FOR table (non_index_column);
This is run instantaneously, but will not collect all the normal
information collected by LOW. It will update number of records and pages in
systables. It will not update information on sysindices (specifically the
clustered column which is the reason why the described behavior happens).
Depending on your schema this may or may not affect your query plans.
3- Upgrade to at least 11.70.FC5 (maybe FC4) and use the new parameter
USTLOW_SAMPLE (check the manuals). This parameter was introduced in FC3 or
FC4 but it had some issues. FC5 should be safe. This will turn on
sampling... again, the information will not be as complete as without it,
but should be good enough for the vast majority of the situations.
Regards
On Sun, Mar 31, 2013 at 3:09 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Informix environment: 11.50 FC8 with HP-UX
>
> Hi,
> I want to improve the performance of "update statistics low" for large
> tables.
> I have the following facts:
> a) When I run "update statistics low for table <bigtable>", the sentence
> runs
> for several hours.
> b) When I review the details of the session with onstat-g ses appear
> status =
> IO Wait- most of the time.
> c) I have reviewed in the operating system level and the amount of I/O
> operations barely growing. In terms of CPU and memory, the operating system
> seems not to be much variation.
> d) When I check onstat-g lsc nothing appears. I tried with set "isolation
> to
> dirty read" and LIGHT_SCANS = FORCE but with no success.
>
> My questions are:
> 1. Why "update statistics low" not use light scans?
> 2. What other considerations should be taken to improve the performance of
> update statistics low?>
> Thanks in advance,
> Roger
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--f46d043d64afd0494304d9393ef5
Thanks for your response Fernando,
By the moment, the upgrade is not posible.
In HP-UX, the page size is 2K.
When I run onstat-u for the session, I get a range between 300 and 500 nreads
per second, which I find very little. I have more hardware to use, but I can
not know what parameters to change to improve specifically the duration of
update statistics. What parameters could be ?
Weekly, I execute the following statements to the table:
update statistics medium for table <big table> distributions only;
update statistics high for table <big table> (index head) distributions only;
update statistics low for table <big table>;
If the statement "update statistics low" is instantaneous for field that are
not part of indexes, then the statements previously mentioned could be
optimized?
Thanks for your help.
Regards,
Roger
Roger:
Several points:
1. The algorithm for LOW stats has been seriously improved in the latest
release (12.10), so an upgrade might be a good idea.
2. You should not need to perform a LOW on the whole table if you are
also performing MEDIUM and HIGH on selected columns. The HIGHs in index
leading columns and MEDIUM on all other columns will take care of the LOW
stats for those columns - which together covers all columns - unless you
specify DISTRIBUTIONS ONLY.
3. The recommended suite of update statistics commands in the
Performance Guide does include separate LOWs for the entire key of each
index (you only have to do that for indexes with more than one key column
since the HIGH on the leading and only column for singleton indexes will
take care of that already).
4. You should use my dostats utility for update statistics - in the
package utils2_ak downloadable from the IIUG Software Repository.
5. You can speed all of your update statistics in the following ways:
1. Set PDQPRIORITY as high as possible without adversely affecting
other processes that are running.
2. Set PSORT_NPROCS to 2X the number of CPU VPs
3. Set PSORT_DBTEMP to at least three but up to six filesystems with
enough space each to hold a sorted copy of the largest/widest index. This
will be faster than temp dbspaces in many cases.
4. If you do not set PSORT_DBTEMP make sure that you have at least
three and as many as six temp dbspaces available and listed in
DBSPACETEMP.
5. Set DBUPSPACE to increase the amount of memory available for
in-memory sorting
6. Set the ONCONFIG variable DS_NONPDQ_QUERY_MEM to at least 15MB
(the default is 128K) and up to 25% of DS_TOTAL_MEMORY (you may have to
increase SHMVIRTSIZE and/or DS_TOTAL_MEMORY).
Here's a link to a paper on optimizing UPDATE STATS that John Miller posted
several years ago:
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/miller
/0203miller.html
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Sun, Mar 31, 2013 at 10:09 AM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Informix environment: 11.50 FC8 with HP-UX
>
> Hi,
> I want to improve the performance of "update statistics low" for large
> tables.
> I have the following facts:
> a) When I run "update statistics low for table <bigtable>", the sentence
> runs
> for several hours.
> b) When I review the details of the session with onstat-g ses appear
> status =
> IO Wait- most of the time.
> c) I have reviewed in the operating system level and the amount of I/O
> operations barely growing. In terms of CPU and memory, the operating system
> seems not to be much variation.
> d) When I check onstat-g lsc nothing appears. I tried with set "isolation
> to
> dirty read" and LIGHT_SCANS = FORCE but with no success.
>
> My questions are:
> 1. Why "update statistics low" not use light scans?
> 2. What other considerations should be taken to improve the performance of
> update statistics low?>
> Thanks in advance,
> Roger
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e01161a9478998a04d93af120
Thank you for your reply Art, I downloaded your package.
I used dostats (dostats_ng: Features Version 7.00). But I have a question
about it.
The dbschema of my table is:
create table largetable
(
numero_cliente integer not null ,
corr_facturacion smallint not null ,
codigo_cargo char(3) not null ,
valor_cargo float not null ,
cantidad integer default 1 not null ,
tipo_cargo char(1) not null ,
llave integer,
empresa char(3) default '7' not null
)
fragment by round robin in dbspace1 , dbspace2, dbspace3
extent size 1948666 next size 324761 lock mode row;
create index idx1 on largetable (numero_cliente,
corr_facturacion,codigo_cargo) using btree in dbspaceidx1;
When I run dostat with option -f, this is the output:
UPDATE STATISTICS LOW FOR TABLE largetable (numero_cliente, corr_facturacion,codigo_cargo);
UPDATE STATISTICS HIGH FOR TABLE largetable (numero_cliente) DISTRIBUTIONSONLY;
UPDATE STATISTICS MEDIUM FOR TABLE largetable (corr_facturacion, codigo_cargo,
valor_cargo, cantidad, tipo_cargo, llave, empresa);
According your point 2, I should not need to perform a LOW on the whole table
if I are also performing MEDIUM and HIGH on selected columns.
In this case, why some fields are repeated in LOW and MEDIUM ?
Thanks for your response.
Regards,
Roger
Roger:
That's an excellent question and while one of my public speaking teachers
said that an excellent question is one you have to research before
answering, I do have a good answer to hand.
Dostats follows the original recommendations in the Performance Guide which
includes a LOW on each entire index key, a HIGH on the lead columns of each
index key (and the first column that is different if multiple keys begin
with the same initial column(s)), and MEDIUM on all other columns. Since
the LOW includes the leading column of the index, the HIGH is
DISTRIBUTIONS ONLY. The medium, which includes the non-lead columns of the
index, does not include the DISTRIBUTIONS ONLY clause so that low level
stats are written to syscolumns for those listed columns. The list in the
MEDIUM does include the non-lead columns from the indexes that do not have
a HIGH run on them which does duplicate some of the work done by the LOW,
however, since the MEDIUM is sampled and does not read the entire table, it
is rather inexpensive, and it turns out that running a single MEDIUM with
all of the non-HIGH columns is cheaper and faster than running two MEDIUMS,
one for columns in index keys with DISTRIBUTIONS ONLY and one without that
clause for columns not included in index keys. This aspect is discussed
somewhat in John's paper that I provided a link for and verified in my own
testing.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Sun, Mar 31, 2013 at 12:48 PM, ROGER VILCA <rvilca@luzdelsur.com.pe>wrote:
> Thank you for your reply Art, I downloaded your package.
>
> I used dostats (dostats_ng: Features Version 7.00). But I have a question
> about it.
>
> The dbschema of my table is:
>
> create table largetable
> (>
> numero_cliente integer not null ,
>
> corr_facturacion smallint not null ,
>
> codigo_cargo char(3) not null ,
>
> valor_cargo float not null ,
>
> cantidad integer default 1 not null ,
>
> tipo_cargo char(1) not null ,
>
> llave integer,
>
> empresa char(3) default '7' not null
> )
> fragment by round robin in dbspace1 , dbspace2, dbspace3
> extent size 1948666 next size 324761 lock mode row;
>
> create index idx1 on largetable (numero_cliente,>
> corr_facturacion,codigo_cargo) using btree in dbspaceidx1;
>
> When I run dostat with option -f, this is the output:
>
> UPDATE STATISTICS LOW FOR TABLE largetable (numero_cliente,
> corr_facturacion,> codigo_cargo);
> UPDATE STATISTICS HIGH FOR TABLE largetable (numero_cliente) DISTRIBUTIONS> ONLY;
> UPDATE STATISTICS MEDIUM FOR TABLE largetable (corr_facturacion,
> codigo_cargo,
> valor_cargo, cantidad, tipo_cargo, llave, empresa);>
> According your point 2, I should not need to perform a LOW on the whole
> table
> if I are also performing MEDIUM and HIGH on selected columns.
>
> In this case, why some fields are repeated in LOW and MEDIUM ?
>
> Thanks for your response.
>
> Regards,
> Roger
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec54b48d088a32004d93ba98a