IDS Forum

Re: Update stats impacts insert performance

Posted By: Art Kagel
Date: Friday, 21 August 2009, at 11:21 a.m.

In Response To: Re: Update stats impacts insert performance (HOWARD ANDERSON)

Great! The obfuscation is fine, I want to see structure not detail. Here
are my recommended commands and I've included some comments below with your
comments.

UPDATE STATISTICS MEDIUM FOR xxx( ccc, eee, fff, ggg, hhh, iii, jjj, kkk,
lll ); -- MEDIUM on all non-leading columns in one command
UPDATE STATISTICS HIGH FOR xxx( aaa, bbb, ddd ); -- HIGH on all leading
columns in one command
UPDATE STATISTICS LOW FOR xxx( aaa ); -- LOW for each full index key
UPDATE STATISTICS LOW FOR xxx( bbb );
UPDATE STATISTICS LOW FOR xxx( ddd );
UPDATE STATISTICS LOW FOR xxx( aaa, bbb, ccc );

Again, see below for some notes:

Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. 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 Fri, Aug 21, 2009 at 6:51 AM, HOWARD ANDERSON <
howard.anderson@edl.uk.eds.com> wrote:

> Art,
>
> Many thanks for the prompt reply,
>
> I enclose a snippet of the schema dump for this table, showing the index
> definitions.
> As you can see a primary key is defined on multiple columns, but I know of
> no
> other 'hidden' index constraints.
>
> Thanks for the link to John's paper, I had already read this, and was
> running
> some tests using the PDQ environment variables, but I did not see much (if
> any) gain. Possibly this is due to insufficent available physicial memory
> on
> the system. 'Set explain' showed that the sort data was 32Gb for HIGH, and
> 41Mb was available when PDQ was enabled. I only have 4Gb of physical memory
> installed, and much of this has already been devoured by the database. I
> also
> only have 2 processors in this system, so PSORT_NPROCS is only set to 2. I
> am
> going to set DBUPSPACE=0:50:0 as this gives 50Mb.

You should be able to set PSORT_NPROCS to 2x NUMCPUVPS. Remember that these
sort threads run inside the CPU VPS and as sort threads, they will
frequently be waiting for IO. By running more sort threads than there are
CPU VPs you will increase the parallelization of the sort processing
significantly and reduce runtime. Note that PSORT_NPROCS and PSORT_DBTEMP
are only effective when parallel sorting is possible which requires
PDQPRIORITY > 0. Since you have at least 10 and as many as 24 fragments on
this table and its indexes, setting PDQPRIORITY as high as you can safely
set it during maintenance window processing will dramatically improve
runtimes since the engine will sort each fragment independently and
PDQPRIORITY controls the percentage of memory and CPU VP resources the
selection and sorting processes can get.

Remember also that DBUPSPACE is ONLY affective when PDQPRIORITY is zero,
when PDQPRIORITY > 0 the MGM memory parameters in the ONCONFIG file control
how much memory is available for sorting (for example though the default for
DBUPSPACE is 15MB, you were getting 41MB of in-memory sort space with
PDQPRIORITY set). You may want to increase the MGM memory available for
sorting a bit since you are currently only keeping about 0.11% of your data
in memory at a time requiring between 800 and 1600 merge runs which is a lot
of IO. See the Administrator's Reference and the Performance Guide to see
how the engine calculates the amount of memory it will allow to be used by a
single sort thread.

>
>
> Chuck Berghoff also mentioned use of PDQ to utilise the Memory Grant
> Manager,
> and mentions PSORT_DBSPACE, which I have not yet tried, as I have
> DBSPACETEMP
> set in onconfig, however it may be worth trying PSORT_DBSPACE to override
> this, just for the update stats. I am using a Compaq ES40 Alphaserver and
> this
> would seem comparable to his Sun V490, however he is using V10.00.FC5, and
> this may be giving an improvement. (I cannot upgrade on my current
> hardware.)

Using PSORT_DBSPACE can improve sorts and reduce the impact on the engine
considerably. The sort-work files are rarely around long enough for the
UNIX buffer cache routines to actually write them to disk, so this becomes
in-memory sorting effectively as long as you have enough memory - yes I note
that you do not have enough on these systems, but.... The DBSPACETEMP
spaces on the other hand are constrained by IDS's caching policy which
forces all dirty pages to disk at every checkpoint, so there will be more
physical IO involved. You should use at least three and as many as six
filesystems in PSORT_DBTEMP for best performance.

>
>
> I will download a copy of your dostats utility and give it a try.
>
> A colleauge on an internal company forum pointed out to me that each week
> only
> a very small percentage of the data in the table has changed, (1bn to
> 1.001bn)
> so a weekly frequency for update sats on this table is too high. He has

He's probably right, but more important than how much of the data changes
weekly is in what way does the data change. For example, ddd is a serial
column so its values are monotonically increasing and the stats on that
column will not reflect those changes. The engine thinks that there are no
values greater than the highest one that was current at the time of the
stats. The engine knows how serials work, so for a while that's OK, but
when the percentage of new rows increases enough, queries against those rows
may choose the wrong index.

The other leading columns, aaa & bbb, may be more stable. If the new values
being added tend to be distributed in similar percentages as existing values
then the data distributions you have for them will be perfectly effective
for the optimizer to base decisions on for quite a while before you would
see a performance degradation. If this is the case, you could run the full
suite quarterly and only redo the HIGH on ddd only (without the
DISTRIBUTIONS ONLY clause so that the LOW isn't needed) on a weekly basis.
I would also do a LOW on the whole table just to keep the page and row
counts current. This is important especially if there are other tables with
large numbers of rows so that the table ordering in the query plans remains
optimal when joining. If this is the only huge table, you can do the whole
table LOW less frequently.

>
> suggested that a biannual frequency may be more appropriate. My only
> concern
> with this is that it could still impact performance at that point. The root
> problem is the performance impact on insertion. Is it possible to 'nice'
> the
> update stats process in some way ? (Reduce its use of system resorces, at
> the
> expense of time taken.)
>
> Would setting a 'DIRTY READ' isolation level have any performance benefit ?
> Or
> would this just produce inaccurate stats ?

Isolation has no effect on update statistics processing. If it misses or
picks up a couple of inconsistent rows, it doesn't materially affect the
optimizer.

>
>
> Schema dump:
> (I have obfuscated the folowing somewhat, as I don't want to expose the
> real
> table or column names.)
>
> { TABLE "informix".xxx row size = 122 number of columns = 12 index size =
> 70
>
> }
> create table "informix".xxx
> (
>
> aaa varchar(12),
>
> bbb datetime year to second,
>
> ccc char(2),
>
> eee char(1),
>
> fff char(2),
>
> ggg char(2),
>
> hhh smallint,
>
> iii integer,
>
> jjj char(2),
>
> kkk varchar(80),
>
> lll char(1),
>
> ddd serial not null ,
>
> check (lll IN ('Y' ,'N' ))
> )
> fragment by expression
>
> ((MONTH (bbb ) = 1 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm001 ,
>
> ((MONTH (bbb ) = 1 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm002 ,
>
> ((MONTH (bbb ) = 2 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm003 ,
>
> ((MONTH (bbb ) = 2 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm004 ,
>
> ((MONTH (bbb ) = 3 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm005 ,
>
> ((MONTH (bbb ) = 3 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm006 ,
>
> ((MONTH (bbb ) = 4 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm007 ,
>
> ((MONTH (bbb ) = 4 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm008 ,
>
> ((MONTH (bbb ) = 5 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm009 ,
>
> ((MONTH (bbb ) = 5 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm010 ,
>
> ((MONTH (bbb ) = 6 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm011 ,
>
> ((MONTH (bbb ) = 6 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm012 ,
>
> ((MONTH (bbb ) = 7 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm013 ,
>
> ((MONTH (bbb ) = 7 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm014 ,
>
> ((MONTH (bbb ) = 8 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm015 ,
>
> ((MONTH (bbb ) = 8 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm016 ,
>
> ((MONTH (bbb ) = 9 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm017 ,
>
> ((MONTH (bbb ) = 9 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm018 ,
>
> ((MONTH (bbb ) = 10 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm019 ,
>
> ((MONTH (bbb ) = 10 ) AND (DAY (bbb ) > 15 )
>
> ) in fragm020 ,
>
> ((MONTH (bbb ) = 11 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm021 ,
>
> ((MONTH (bbb ) = 11 ) AND (DAY (bbb ) > 15 )
>
> ) in fragm022 ,
>
> ((MONTH (bbb ) = 12 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm023 ,
>
> ((MONTH (bbb ) = 12 ) AND (DAY (bbb ) > 15 )
>
> ) in fragm024
> extent size 128000 next size 128000 lock mode row;
> revoke all on "informix".xxx from "public";
>
> create unique index "informix".ix_x_aaa on "informix".xxx
>
> (aaa,bbb,ccc)
> fragment by expression
>
> (aaa [1,1] <= 'A' ) in fragm001 ,
>
> (aaa [1,1] = 'B' ) in fragm002 ,
>
> (aaa [1,1] = 'C' ) in fragm003 ,
>
> (aaa [1,1] = 'D' ) in fragm004 ,
>
> (aaa [1,1] = 'E' ) in fragm005 ,
>
> (aaa [1,1] = 'F' ) in fragm006 ,
>
> (aaa [1,1] = 'G' ) in fragm007 ,
>
> (aaa [1,1] = 'H' ) in fragm008 ,
>
> (aaa [1,1] = 'I' ) in fragm009 ,
>
> (aaa [1,1] = 'J' ) in fragm010 ,
>
> (aaa [1,1] = 'K' ) in fragm011 ,
>
> (aaa [1,1] = 'L' ) in fragm012 ,
>
> (aaa [1,1] = 'M' ) in fragm013 ,
>
> (aaa [1,1] = 'N' ) in fragm014 ,
>
> (aaa [1,1] = 'O' ) in fragm015 ,
>
> (aaa [1,1] IN ('P' ,'Q' )) in fragm016 ,
>
> (aaa [1,1] = 'R' ) in fragm017 ,
>
> (aaa [1,1] = 'S' ) in fragm018 ,
>
> (aaa [1,1] = 'T' ) in fragm019 ,
>
> (aaa [1,1] = 'U' ) in fragm020 ,
>
> (aaa [1,1] = 'V' ) in fragm021 ,
>
> (aaa [1,1] = 'W' ) in fragm022 ,
>
> (aaa [1,1] = 'X' ) in fragm023 ,
>
> (aaa [1,1] >= 'Y' ) in fragm024 ;
>
> create index "informix".ix_x_bbb on "informix".xxx (bbb)
>
> fragment by expression
>
> ((MONTH (bbb ) = 1 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm001 ,
>
> ((MONTH (bbb ) = 1 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm002 ,
>
> ((MONTH (bbb ) = 2 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm003 ,
>
> ((MONTH (bbb ) = 2 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm004 ,
>
> ((MONTH (bbb ) = 3 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm005 ,
>
> ((MONTH (bbb ) = 3 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm006 ,
>
> ((MONTH (bbb ) = 4 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm007 ,
>
> ((MONTH (bbb ) = 4 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm008 ,
>
> ((MONTH (bbb ) = 5 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm009 ,
>
> ((MONTH (bbb ) = 5 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm010 ,
>
> ((MONTH (bbb ) = 6 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm011 ,
>
> ((MONTH (bbb ) = 6 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm012 ,
>
> ((MONTH (bbb ) = 7 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm013 ,
>
> ((MONTH (bbb ) = 7 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm014 ,
>
> ((MONTH (bbb ) = 8 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm015 ,
>
> ((MONTH (bbb ) = 8 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm016 ,
>
> ((MONTH (bbb ) = 9 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm017 ,
>
> ((MONTH (bbb ) = 9 ) AND (DAY (bbb ) > 15 ) )
>
> in fragm018 ,
>
> ((MONTH (bbb ) = 10 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm019 ,
>
> ((MONTH (bbb ) = 10 ) AND (DAY (bbb ) > 15 )
>
> ) in fragm020 ,
>
> ((MONTH (bbb ) = 11 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm021 ,
>
> ((MONTH (bbb ) = 11 ) AND (DAY (bbb ) > 15 )
>
> ) in fragm022 ,
>
> ((MONTH (bbb ) = 12 ) AND (DAY (bbb ) <= 15 )
>
> ) in fragm023 ,
>
> ((MONTH (bbb ) = 12 ) AND (DAY (bbb ) > 15 )
>
> ) in fragm024 ;
>
> create index "informix".ix_x_ddd on "informix".xxx (ddd)
> fragment by expression
>
> (mod(ddd , 10 ) = 0 ) in fragm010 ,
>
> (mod(ddd , 10 ) = 1 ) in fragm011 ,
>
> (mod(ddd , 10 ) = 2 ) in fragm012 ,
>
> (mod(ddd , 10 ) = 3 ) in fragm013 ,
>
> (mod(ddd , 10 ) = 4 ) in fragm014 ,
>
> (mod(ddd , 10 ) = 5 ) in fragm015 ,
>
> (mod(ddd , 10 ) = 6 ) in fragm016 ,
>
> (mod(ddd , 10 ) = 7 ) in fragm017 ,
>
> (mod(ddd , 10 ) = 8 ) in fragm018 ,
>
> (mod(ddd , 10 ) = 9 ) in fragm019 ;
>
> alter table "informix".xxx add constraint primary key (aaa,
>
> bbb,ccc) ;
>
> Regards,
>
> Howard Anderson
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--0015174794962557a40471a86a04

Messages In This Thread

• Update stats impacts insert performance
  HOWARD ANDERSON -- Wednesday, 19 August 2009, at 1:19 p.m.
  • Re: Update stats impacts insert performance
    Art Kagel -- Wednesday, 19 August 2009, at 1:29 p.m.
    • Re: Update stats impacts insert performance
      HOWARD ANDERSON -- Friday, 21 August 2009, at 6:51 a.m.
      • Re: Update stats impacts insert performance
        Art Kagel -- Friday, 21 August 2009, at 11:21 a.m.
        • Re: Update stats impacts insert performance
          KAMRAN HAQ -- Monday, 24 August 2009, at 10:27 a.m.
          • Re: Update stats impacts insert performance
            Art Kagel -- Monday, 24 August 2009, at 10:35 a.m.
      • Re: Update stats impacts insert performance
        Art Kagel -- Friday, 21 August 2009, at 12:33 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.