Update statistics performance
Hi all
Update Statistic takes a long duration of up to 13 hours to complete, our DB
is more than 35 gb. I tried to use PDQ ,PSORT_NPROCS and DBSPACETEMP and the
was only a slight improvement i.e from 13 to 12 hours. our IDS version is
17.31 uc5 Kindly advice on how this duration can be reduced
Below is the profile and i/o queue stats and onstat -F profile
Profile
dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched
229690758 36261273 617122098 62.78 28229782 19567333 287833239 90.19
isamtot open start read write rewrite delete commit
rollbk
144865383 30923634 247207201 3231528422 186584701 26339127 233319 47761
4
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 529902.17 13244.00 1267 2534
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
30833338 50871 3330742991 0 0 4886 396427 3670835
ixda-RA idx-RA da-RA RA-pgsused lchwaits
6695267 15958151 175978636 198623320 15834291
AIO I/O queues:
q name/id len maxlen totalops dskread dskwrite dskcopy
kio 0 0 0 0 0 0 0
kio 1 0 16 57579748 52927126 4652622 0
kio 2 0 0 0 0 0 0
kio 3 0 16 108007501 96187935 11819566 0
kio 4 0 16 97292492 85534898 11757594 0
adt 0 0 0 0 0 0 0
msc 0 0 1 9313 0 0 0
aio 0 0 2 36214109 2 36213915 0
omstat -F
Fg Writes LRU Writes Chunk Writes
61 5808527 35281444
address flusher state data
60050500 0 I 0 = 0X0
600509ec 1 I 0 = 0X0
60050ed8 2 I 0 = 0X0
600513c4 3 I 0 = 0X0
600518b0 4 I 0 = 0X0
60051d9c 5 I 0 = 0X0
60052288 6 I 0 = 0X0
60052774 7 C 79 = 0X4f
60052c60 8 I 0 = 0X0
6005314c 9 I 0 = 0X0
60053638 10 I 0 = 0X0
60053b24 11 I 0 = 0X0
60054010 12 I 0 = 0X0
600544fc 13 I 0 = 0X0
600549e8 14 C 78 = 0X4e
60054ed4 15 I 0 = 0X0
states: Exit Idle Chunk Lru
Have you tried rebuilding your indexes and defragging your tables that are
taking many extents?
-----Original Message-----
From: Kenneth Makobe [mailto:Kenneth.Makobe@logicacmg.com]
Sent: Thursday, July 22, 2004 1:39 AM
To: ids@iiug.org
Subject: Update statistics performance [3275]
Hi all
Update Statistic takes a long duration of up to 13 hours to complete, our DB
is more than 35 gb. I tried to use PDQ ,PSORT_NPROCS and DBSPACETEMP and the
was only a slight improvement i.e from 13 to 12 hours. our IDS version is
17.31 uc5 Kindly advice on how this duration can be reduced
Below is the profile and i/o queue stats and onstat -F profile
Profile
dskreads pagreads bufreads Eched dskwrits pagwrits bufwrits Eched
229690758 36261273 617122098 62.78 28229782 19567333 287833239 90.19
isamtot open start read write rewrite delete commit
rollbk
144865383 30923634 247207201 3231528422 186584701 26339127 233319 47761
4
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 529902.17 13244.00 1267 2534
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
30833338 50871 3330742991 0 0 4886 396427 3670835
ixda-RA idx-RA da-RA RA-pgsused lchwaits
6695267 15958151 175978636 198623320 15834291
AIO I/O queues:
q name/id len maxlen totalops dskread dskwrite dskcopy
kio 0 0 0 0 0 0 0
kio 1 0 16 57579748 52927126 4652622 0
kio 2 0 0 0 0 0 0
kio 3 0 16 108007501 96187935 11819566 0
kio 4 0 16 97292492 85534898 11757594 0
adt 0 0 0 0 0 0 0
msc 0 0 1 9313 0 0 0
aio 0 0 2 36214109 2 36213915 0
omstat -F
Fg Writes LRU Writes Chunk Writes
61 5808527 35281444
address flusher state data
60050500 0 I 0 = 0X0
600509ec 1 I 0 = 0X0
60050ed8 2 I 0 = 0X0
600513c4 3 I 0 = 0X0
600518b0 4 I 0 = 0X0
60051d9c 5 I 0 = 0X0
60052288 6 I 0 = 0X0
60052774 7 C 79 = 0X4f
60052c60 8 I 0 = 0X0
6005314c 9 I 0 = 0X0
60053638 10 I 0 = 0X0
60053b24 11 I 0 = 0X0
60054010 12 I 0 = 0X0
600544fc 13 I 0 = 0X0
600549e8 14 C 78 = 0X4e
60054ed4 15 I 0 = 0X0
states: Exit Idle Chunk Lru
Ken,
Maybe perf tuning to deal with the 61 foreground writes (these are sqlexec
user threads writing to disk, this is bad), the read cache % of 62.78% (kinda
low), and the 3670835 seqscans (this might not be bad)....
Perf tuning those might help the perf of your update stats?
Fg Writes LRU Writes Chunk Writes
61 5808527 35281444
I'm not sure on this.... But maybe your LRU writes are too low in comparison
to your chunk writes. Aren't chunk writes done during checkpoints (no prob
there with that number), and LRU writes done during min/max dirty infx
monitoring of the buffer cache? ... If so, perhaps getting more self-cleaning
via LRU writes instead of letting the checkpoints do all the work might
help.... BUT then you didn't say anything about checkpoint issues...
I could be way off base, but had to toss that in .... (I'm dealing with the
nitty gritty perf issues on my system now so it catches my eye).
Norma Jean
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org] On Behalf
Of Kenneth Makobe
Sent: Thursday, July 22, 2004 12:39 AM
To: ids@iiug.org
Subject: Update statistics performance [3275]
Hi all
Update Statistic takes a long duration of up to 13 hours to complete, our DB
is more than 35 gb. I tried to use PDQ ,PSORT_NPROCS and DBSPACETEMP and the
was only a slight improvement i.e from 13 to 12 hours. our IDS version is
17.31 uc5 Kindly advice on how this duration can be reduced Below is the
profile and i/o queue stats and onstat -F profile
Profile
dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched
229690758 36261273 617122098 62.78 28229782 19567333 287833239 90.19
isamtot open start read write rewrite delete commit
rollbk
144865383 30923634 247207201 3231528422 186584701 26339127 233319 47761
4
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 529902.17 13244.00 1267 2534
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
30833338 50871 3330742991 0 0 4886 396427 3670835
ixda-RA idx-RA da-RA RA-pgsused lchwaits
6695267 15958151 175978636 198623320 15834291
AIO I/O queues:
q name/id len maxlen totalops dskread dskwrite dskcopy
kio 0 0 0 0 0 0 0
kio 1 0 16 57579748 52927126 4652622 0
kio 2 0 0 0 0 0 0
kio 3 0 16 108007501 96187935 11819566 0
kio 4 0 16 97292492 85534898 11757594 0
adt 0 0 0 0 0 0 0
msc 0 0 1 9313 0 0 0
aio 0 0 2 36214109 2 36213915 0
omstat -F
Fg Writes LRU Writes Chunk Writes
61 5808527 35281444
address flusher state data
60050500 0 I 0 = 0X0
600509ec 1 I 0 = 0X0
60050ed8 2 I 0 = 0X0
600513c4 3 I 0 = 0X0
600518b0 4 I 0 = 0X0
60051d9c 5 I 0 = 0X0
60052288 6 I 0 = 0X0
60052774 7 C 79 = 0X4f
60052c60 8 I 0 = 0X0
6005314c 9 I 0 = 0X0
60053638 10 I 0 = 0X0
60053b24 11 I 0 = 0X0
60054010 12 I 0 = 0X0
600544fc 13 I 0 = 0X0
600549e8 14 C 78 = 0X4e
60054ed4 15 I 0 = 0X0
states: Exit Idle Chunk Lru
-----------------------------------------
============================================================
The information contained in this message may be privileged
and confidential and protected from disclosure. If the
reader of this message is not the intended recipient, or an
employee or agent responsible for delivering this message to
the intended recipient, you are hereby notified that any
reproduction, dissemination or distribution of this
communication is strictly prohibited. If you have received
this communication in error, please notify us immediately by
replying to the message and deleting it from your computer.
Thank you.
Tellabs
============================================================
If I read the original question
correctly, you're using 7.31.UC5.
You should be on 7.31.UDn for a fairly large value of n -- you need to
upgrade, preferably to 9.40, but at least to 7.31.UDn.
That's in part on simple pragmatics - there have been security fixes in
7.31.UD7 (and 9.30.UC7, and 9.40.UC3), so you should upgrade for those
fixes alone.
IDS 9.40 definitely has performance improvements in update statistics -
but I believe they were proven in 7.31.UDx first.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
forum.subscriber@iiug.org wrote on 07/22/2004 04:52:21 AM:
> Ken,
> Maybe perf tuning to deal with the 61 foreground writes (these are
> sqlexec user threads writing to disk, this is bad), the read cache %
> of 62.78% (kinda low), and the 3670835 seqscans (this might not be
bad)....
>
> Perf tuning those might help the perf of your update stats?
>
>
> Fg Writes LRU Writes Chunk Writes
> 61 5808527 35281444
>
> I'm not sure on this.... But maybe your LRU writes are too low in
> comparison to your chunk writes. Aren't chunk writes done during
> checkpoints (no prob there with that number), and LRU writes done
> during min/max dirty infx monitoring of the buffer cache? ... If
> so, perhaps getting more self-cleaning via LRU writes instead of
> letting the checkpoints do all the work might help.... BUT then you
> didn't say anything about checkpoint issues...
>
> I could be way off base, but had to toss that in .... (I'm dealing
> with the nitty gritty perf issues on my system now so it catches my
eye).
>
> Norma Jean
>
>
>
>
> -----Original Message-----
> From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]
> On Behalf Of Kenneth Makobe
> Sent: Thursday, July 22, 2004 12:39 AM
> To: ids@iiug.org
> Subject: Update statistics performance [3275]
>
> Hi all
> Update Statistic takes a long duration of up to 13 hours to
> complete, our DB is more than 35 gb. I tried to use PDQ ,
> PSORT_NPROCS and DBSPACETEMP and the was only a slight improvement
> i.e from 13 to 12 hours. our IDS version is
> 17.31 uc5 Kindly advice on how this duration can be reduced Below
> is the profile and i/o queue stats and onstat -F profile
>
> Profile
> dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched
> 229690758 36261273 617122098 62.78 28229782 19567333 287833239 90.19
>
> isamtot open start read write rewrite delete commit
> rollbk
> 144865383 30923634 247207201 3231528422 186584701 26339127 233319 47761
> 4
>
> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> 0 0 0 0 0 0 0
>
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 529902.17 13244.00 1267 2534
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 30833338 50871 3330742991 0 0 4886 396427 3670835
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 6695267 15958151 175978636 198623320 15834291
>
> AIO I/O queues:
> q name/id len maxlen totalops dskread dskwrite dskcopy
> kio 0 0 0 0 0 0 0
> kio 1 0 16 57579748 52927126 4652622 0
> kio 2 0 0 0 0 0 0
> kio 3 0 16 108007501 96187935 11819566 0
> kio 4 0 16 97292492 85534898 11757594 0
> adt 0 0 0 0 0 0 0
> msc 0 0 1 9313 0 0 0
> aio 0 0 2 36214109 2 36213915 0
> omstat -F
>
>
> Fg Writes LRU Writes Chunk Writes
> 61 5808527 35281444
>
> address flusher state data
> 60050500 0 I 0 = 0X0
> 600509ec 1 I 0 = 0X0
> 60050ed8 2 I 0 = 0X0
> 600513c4 3 I 0 = 0X0
> 600518b0 4 I 0 = 0X0
> 60051d9c 5 I 0 = 0X0
> 60052288 6 I 0 = 0X0
> 60052774 7 C 79 = 0X4f
> 60052c60 8 I 0 = 0X0
> 6005314c 9 I 0 = 0X0
> 60053638 10 I 0 = 0X0
> 60053b24 11 I 0 = 0X0
> 60054010 12 I 0 = 0X0
> 600544fc 13 I 0 = 0X0
> 600549e8 14 C 78 = 0X4e
> 60054ed4 15 I 0 = 0X0
> states: Exit Idle Chunk Lru
>
>
> -----------------------------------------
> ============================================================
> The information contained in this message may be privileged
> and confidential and protected from disclosure. If the
> reader of this message is not the intended recipient, or an
> employee or agent responsible for delivering this message to
> the intended recipient, you are hereby notified that any
> reproduction, dissemination or distribution of this
> communication is strictly prohibited. If you have received
> this communication in error, please notify us immediately by
> replying to the message and deleting it from your computer.
>
> Thank you.
> Tellabs
> ============================================================
>
-----Original Message-----
From: mac horn [mailto:mac.horn@cox.net]
Sent: Sunday, July 25, 2004 4:50 AM
To: 'Kenneth Makobe '
Subject: RE: Update statistics performance [3275]=20
Could you send your update stats script?
I hope you mistyped the size of your database. 35 gig should not take
anywhere that long.
We update stats on 4TB in a little over two hours with the recommended
high/medium.
mac
-----Original Message-----
From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On
Behalf Of Kenneth Makobe
Sent: Wednesday, July 21, 2004 10:39 PM
To: ids@iiug.org
Subject: Update statistics performance [3275]
Hi all
Update Statistic takes a long duration of up to 13 hours to complete, =
our DB
is more than 35 gb. I tried to use PDQ ,PSORT_NPROCS and DBSPACETEMP =
and the
was only a slight improvement i.e from 13 to 12 hours. our IDS version =
is
17.31 uc5 Kindly advice on how this duration can be reduced
Below is the profile and i/o queue stats and onstat -F profile
Profile
dskreads pagreads bufreads =CAched dskwrits pagwrits bufwrits =CAched
229690758 36261273 617122098 62.78 28229782 19567333 287833239 90.19
isamtot open start read write rewrite delete commit
rollbk
144865383 30923634 247207201 3231528422 186584701 26339127 233319 =
47761
4
gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
0 0 0 0 0 0 0
ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
0 0 0 529902.17 13244.00 1267 2534
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
30833338 50871 3330742991 0 0 4886 396427 =
3670835
ixda-RA idx-RA da-RA RA-pgsused lchwaits
6695267 15958151 175978636 198623320 15834291
AIO I/O queues:
q name/id len maxlen totalops dskread dskwrite dskcopy
kio 0 0 0 0 0 0 0
kio 1 0 16 57579748 52927126 4652622 0
kio 2 0 0 0 0 0 0
kio 3 0 16 108007501 96187935 11819566 0
kio 4 0 16 97292492 85534898 11757594 0
adt 0 0 0 0 0 0 0
msc 0 0 1 9313 0 0 0
aio 0 0 2 36214109 2 36213915 0
omstat -F
Fg Writes LRU Writes Chunk Writes
61 5808527 35281444
address flusher state data
60050500 0 I 0 =3D 0X0
600509ec 1 I 0 =3D 0X0
60050ed8 2 I 0 =3D 0X0
600513c4 3 I 0 =3D 0X0
600518b0 4 I 0 =3D 0X0
60051d9c 5 I 0 =3D 0X0
60052288 6 I 0 =3D 0X0
60052774 7 C 79 =3D 0X4f
60052c60 8 I 0 =3D 0X0
6005314c 9 I 0 =3D 0X0
60053638 10 I 0 =3D 0X0
60053b24 11 I 0 =3D 0X0
60054010 12 I 0 =3D 0X0
600544fc 13 I 0 =3D 0X0
600549e8 14 C 78 =3D 0X4e
60054ed4 15 I 0 =3D 0X0
states: Exit Idle Chunk Lru
#!/bin/sh
########################################################################=
####
###
# update_stats D.L. Samuels 02/06/99
#
# Perform Informix recommended sequence of update statistics statements =
on the
# specified tables. Execute with no parameters for explanation of =
usage.
#
########################################################################=
####
###
if [ $# -ne 1 ]
then=20
echo "\\
Usage:\\
"=20
echo " `expr //$0 : '.*/\\\\(.*\\\\)'` DATABASE_NAME "TABLE_NAME =
TABLE_NAME..""
echo "\\
Where:\\
"=20
echo " DATABASE_NAME =3D Name of the database to be updated."
echo " TABLE_NAME =3D Name of tables to update. If more than =
one table"
echo " is specified, the list of table names =
should be"
echo " enclosed in quotes with a white space =
separating"
echo " the table names. Wildcards can also be =
matched"
echo " by enclosing the table name and wildcards =
in"
echo " quotes.\\
"
echo " To update all tables specify ALL.\\
"
exit 1=20
fi
DBNAME=3D$1
########################################################################=
####
###
# The following query will return the columns needed for the various =
update
# statistics statements. The output of this query is piped to the awk =
script
# which follows.
### Loop through the list of tables specified:
TABLES=3D`cat tables.dat`
for TABNAME in $TABLES
do
-- A single UPDATE STATISTICS MEDIUM for columns which do not head an =
index:
#-- The hard coded value of " NONE" for all of these columns will =
cause
#-- the awk script to generate a single statement.
select t.tabname, " NONE" idxname, "1 medium" level, c.colno part, =c.colname=20
from syscolumns c, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and c.colno not in (select part1=20
from sysindexes i=20
where i.tabid =3D c.tabid)
union all
#-- A separate UPDATE STATISTICS HIGH for each column which heads an =
index:
#-- (The column name is used as the index name here, so that if a =
column
#-- heads more than one index, it will only be processed once.)
select unique t.tabname, c.colname, "2 high" level, 1 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and c.colno =3D i.part1
union all
-- A separate UPDATE STATISTICS LOW for each multi-column index:
-- (The actual name of each index is included on each row returned =by
-- this part of the query, allowing the awk script to group all
-- all columns of the same index together.)
select t.tabname, i.idxname, "3 low" level, 1 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part1
union all
select t.tabname, i.idxname, "3 low" level, 2 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part2
union all
select t.tabname, i.idxname, "3 low" level, 3 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part3
union all
select t.tabname, i.idxname, "3 low" level, 4 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid
and i.tabid =3D t.tabid
and i.part2 !=3D 0
and c.colno =3D i.part4
union all
select t.tabname, i.idxname, "3 low" level, 5 part, c.colname=20
from syscolumns c, sysindexes i, systables t
where t.tabname matches "$TABNAME"
and t.tabid >=3D 100
and t.tabtype =3D "T"
and c.tabid =3D t.tabid@