long checkpoints
Hi all,
the performance on one of our Customer's production databases has decreased
dramatically in the last couple of weeks. Checkpoints seem to be taking over a
minute to complete. Running IDS 9.21 UC6 (which I know is no longer supported
but unfortunately as it is out there in our product in the field I can't do
much about this) on Solaris 8.
If anyone has any pointers on the config parameters below that may help to
improve performance they would be greatly appreciated. Also are there any know
bugs with checkpoints on this version of Informix.
Below are what I believe are the relevant onconfig parameters
PHYSDBS omc_db_plog # Location (dbspace) of physical log
PHYSFILE 253894 # Physical log file size (Kbytes)
LOGFILES 3 # Number of logical log files
LOGSIZE 2000 # Logical log size (Kbytes)
NOFUZZYCKPT 1 # No fuzzy checkpoints
MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-processor
NUMCPUVPS 2 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps to one
# Shared Memory Parameters
LOCKS 75000 # Maximum number of locks
BUFFERS 50000 # Maximum number of shared buffers
NUMAIOVPS 1 # Number of IO vps
PHYSBUFF 128 # Physical log buffer size (Kbytes)
LOGBUFF 128 # Logical log buffer size (Kbytes)LOGSMAX 50 # Maximum number of logical log files
CLEANERS 6 # Number of buffer cleaner processes
SHMBASE 0xa000000 # Shared memory base address
SHMVIRTSIZE 32000 # initial virtual shared memory segment size
SHMADD 8192 # Size of new shared memory segments (Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes). 0=>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 15 # Number of LRU queues
LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water mark percentage
LTXEHWM 60 # Long transaction high water mark (exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 32 # Stack size (Kbytes)
RA_PAGES 32 # Number of pages to attempt to read ahead
RA_THRESHOLD 28 # Number of pages left before next group
DBSPACETEMP omc_db_temp # Default temp dbspaces
output from onstat -p
Profile
dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched
890960 1087371 36156569 97.54 851638 1850908 3389826 74.88
isamtot open start read write rewrite delete commit rollbk
27502723 47701 2493635 17606895 857421 17010 300250 823 0
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 2598.48 409.81 84 196
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
164589 93 18694337 0 0 88 42716 5906
ixda-RA idx-RA da-RA RA-pgsused lchwaits
271575 11542 14060 292760 6173
onstat -m
Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line -- Up 08:16:55 --
147456 Kbytes
Message Log File: /usr/informix/online.log_OMC
<<Informix Dynamic Server 2000>>> Logical Log 12764 Complete.
01:50:59 Logical Log 12764 Complete.
01:53:23 Checkpoint Completed: duration was 97 seconds.
01:53:23 Checkpoint loguniq 12765, logpos 0x2af3d4
01:59:59 Checkpoint Completed: duration was 95 seconds.
01:59:59 Checkpoint loguniq 12765, logpos 0x14df54c
02:06:04 Checkpoint Completed: duration was 66 seconds.
02:06:04 Checkpoint loguniq 12765, logpos 0x2595418
<<Informix Dynamic Server 2000>>> Logical Log 12765 Complete.
02:10:10 Logical Log 12765 Complete.
02:12:06 Checkpoint Completed: duration was 62 seconds.
02:12:06 Checkpoint loguniq 12766, logpos 0x3bc418
02:18:16 Checkpoint Completed: duration was 70 seconds.
02:18:16 Checkpoint loguniq 12766, logpos 0x1676018
--0__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481
Content-type: multipart/alternative;
Boundary="1__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481"
--1__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481
Content-type: text/plain; charset=ISO-8859-1
Content-transfer-encoding: quoted-printable
Are you running replication?
First, run sar -d 2 20 to see if you have a bad disk.
Jim Spalla
Anexsys
Database Administrator
312/441-4036 phone
312/441-4099 fax
815/483-9234 cell phone
=
"Egan Karen-...." =
<Karen.Egan@motor =
ola.com> =
To
Sent by: ids@iiug.org =
forum.subscriber@ =
cc
iiug.org =
Subj=
ect
long checkpoints [3494] =
09/28/2004 08:49 =
AM =
=
=
=
=
Hi all,
the performance on one of our Customer's production databases has decre=
ased
dramatically in the last couple of weeks. Checkpoints seem to be taking=
over a minute to complete. Running IDS 9.21 UC6 (which I know is no lon=
ger
supported but unfortunately as it is out there in our product in the fi=
eld
I can't do much about this) on Solaris 8.
If anyone has any pointers on the config parameters below that may help=
to
improve performance they would be greatly appreciated. Also are there a=
ny
know bugs with checkpoints on this version of Informix.
Below are what I believe are the relevant onconfig parameters
PHYSDBS omc_db_plog # Location (dbspace) of physical log
PHYSFILE 253894 # Physical log file size (Kbytes)
LOGFILES 3 # Number of logical log files
LOGSIZE 2000 # Logical log size (Kbytes)
NOFUZZYCKPT 1 # No fuzzy checkpoints
MULTIPROCESSOR 1 # 0 for single-processor, 1 formulti-processor
NUMCPUVPS 2 # Number of user (cpu) vps
SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps =to
one
# Shared Memory Parameters
LOCKS 75000 # Maximum number of locks
BUFFERS 50000 # Maximum number of shared buffers
NUMAIOVPS 1 # Number of IO vps
PHYSBUFF 128 # Physical log buffer size (Kbytes)
LOGBUFF 128 # Logical log buffer size (Kbytes)LOGSMAX 50 # Maximum number of logical log files
CLEANERS 6 # Number of buffer cleaner processes
SHMBASE 0xa000000 # Shared memory base address
SHMVIRTSIZE 32000 # initial virtual shared memory segment=
size
SHMADD 8192 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=3D>unlimited
CKPTINTVL 300 # Check point interval (in sec)
LRUS 15 # Number of LRU queues
LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limi=t
LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
LTXHWM 50 # Long transaction high water markpercentage
LTXEHWM 60 # Long transaction high water mark
(exclusive)
TXTIMEOUT 0x12c # Transaction timeout (in sec)
STACKSIZE 32 # Stack size (Kbytes)
RA_PAGES 32 # Number of pages to attempt to read ah=ead
RA_THRESHOLD 28 # Number of pages left before next grou=p
DBSPACETEMP omc_db_temp # Default temp dbspaces
output from onstat -p
Profile
dskreads pagreads bufreads =CAched dskwrits pagwrits bufwrits =CAched
890960 1087371 36156569 97.54 851638 1850908 3389826 74.88
isamtot open start read write rewrite delete commit
rollbk
27502723 47701 2493635 17606895 857421 17010 300250 823 =
0
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 2598.48 409.81 84 196
bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans=
164589 93 18694337 0 0 88 42716 5906
ixda-RA idx-RA da-RA RA-pgsused lchwaits
271575 11542 14060 292760 6173
onstat -m
Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line -- Up 08:1=
6:55
-- 147456 Kbytes
Message Log File: /usr/informix/online.log_OMC
<<Informix Dynamic Server 2000>>> Logical Log 12764 Comple=
te.
01:50:59 Logical Log 12764 Complete.
01:53:23 Checkpoint Completed: duration was 97 seconds.
01:53:23 Checkpoint loguniq 12765, logpos 0x2af3d4
01:59:59 Checkpoint Completed: duration was 95 seconds.
01:59:59 Checkpoint loguniq 12765, logpos 0x14df54c
02:06:04 Checkpoint Completed: duration was 66 seconds.
02:06:04 Checkpoint loguniq 12765, logpos 0x2595418
<<Informix Dynamic Server 2000>>> Logical Log 12765 Comple=
te.
02:10:10 Logical Log 12765 Complete.
02:12:06 Checkpoint Completed: duration was 62 seconds.
02:12:06 Checkpoint loguniq 12766, logpos 0x3bc418
02:18:16 Checkpoint Completed: duration was 70 seconds.
02:18:16 Checkpoint loguniq 12766, logpos 0x1676018
This transmission may contain information that is privileged, confident=
ial
and/or exempt from disclosure under applicable law. If you are not the
intended recipient, you are hereby notified that any disclosure, copyin=
g,
distribution, or use of the information contained herein (including any=
reliance thereon) is STRICTLY PROHIBITED. If you received this transmis=
sion
in error, please immediately contact the sender and destroy the materia=
l in
its entirety, whether in electronic or hard copy format.=
--1__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481
Content-type: text/html; charset=ISO-8859-1
Content-Disposition: inline
Content-transfer-encoding: quoted-printable
<html><body>
<p>Are you running replication?<br>
<br>
First, run sar -d 2 20 to see if you have a bad disk.<br>
<br>
<br>
Jim Spalla<br>
Anexsys<br>
Database Administrator<br>
312/441-4036 phone<br>
312/441-4099 fax<br>
815/483-9234 cell phone<br>
<img src=3D"cid:10__=3D09BBE58EDFCBA4818f9e8a93df93@anexsys.com" width=3D=
"16" height=3D"16" alt=3D"Inactive hide details for "Egan Karen-..=
.." <Karen.Egan@motorola.com>">"Egan Karen-...." &=
lt;Karen.Egan@motorola.com><br>
<br>
<br>
<table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">=
<tr valign=3D"top"><td style=3D"background-image:url(cid:20__=3D09BBE58=
EDFCBA4818f9e8a93df93@anexsys.com); background-repeat: no-repeat; " wid=
th=3D"40%">
<ul>
<ul>
<ul>
<ul><b><font size=3D"2">"Egan Karen-...." <Karen.Egan@moto=
rola.com></font></b><font size=3D"2"> </font><br>
<font size=3D"2">Sent by: forum.subscriber@iiug.org</font>
<p><font size=3D"2">09/28/2004 08:49 AM</font></ul>
</ul>
</ul>
</ul>
</td><td width=3D"60%">
<table width=3D"100%" border=3D"0" cellspacing=3D"0" cellpadding=3D"0">=
<tr valign=3D"top"><td width=3D"1%" valign=3D"middle"><img src=3D"cid:3=
0__=3D09BBE58EDFCBA4818f9e8a93df93@anexsys.com" border=3D"0" height=3D"=
1" width=3D"58" alt=3D""><br>
<div align=3D"right"><font size=3D"2">To</font></div></td><td width=3D"=
100%@@D
Karen,
What platform are you on? Is it supporting KAIO?
(take a look at onstat -g iov)
It seems, you need to increase NUMAIOVPS to smth reasonable
(like: number of physical disk drives)
Is there any reason for setting " NOFUZZYCKPT 1"?
If is very useful to take a look at onstat -R before
the checkpoint and at onstat -F
-Alexey
> -----Original Message-----
> From: Egan Karen-.... [mailto:Karen.Egan@motorola.com]
>
> Hi all,
>
> the performance on one of our Customer's production databases has
decreased
> dramatically in the last couple of weeks. Checkpoints seem to be taking
over a
> minute to complete. Running IDS 9.21 UC6 (which I know is no longer
supported
> but unfortunately as it is out there in our product in the field I can't
do
> much about this) on Solaris 8.
>
> If anyone has any pointers on the config parameters below that may help to
> improve performance they would be greatly appreciated. Also are there any
know
> bugs with checkpoints on this version of Informix.
>
> Below are what I believe are the relevant onconfig parameters
>
> PHYSDBS omc_db_plog # Location (dbspace) of physical log
> PHYSFILE 253894 # Physical log file size (Kbytes)
>
> LOGFILES 3 # Number of logical log files
> LOGSIZE 2000 # Logical log size (Kbytes)>
> NOFUZZYCKPT 1 # No fuzzy checkpoints
>
>
> MULTIPROCESSOR 1 # 0 for single-processor, 1 for multi-> processor
> NUMCPUVPS 2 # Number of user (cpu) vps
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu vps toone
>
>
> # Shared Memory Parameters
> LOCKS 75000 # Maximum number of locks
> BUFFERS 50000 # Maximum number of shared buffers
> NUMAIOVPS 1 # Number of IO vps
> PHYSBUFF 128 # Physical log buffer size (Kbytes)
> LOGBUFF 128 # Logical log buffer size (Kbytes)> LOGSMAX 50 # Maximum number of logical log files
> CLEANERS 6 # Number of buffer cleaner processes
> SHMBASE 0xa000000 # Shared memory base address
> SHMVIRTSIZE 32000 # initial virtual shared memory segmentsize
> SHMADD 8192 # Size of new shared memory segments
(Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 15 # Number of LRU queues
> LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning limit
> LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning limit
> LTXHWM 50 # Long transaction high water markpercentage
> LTXEHWM 60 # Long transaction high water mark
(exclusive)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 32 # Stack size (Kbytes)
> RA_PAGES 32 # Number of pages to attempt to read ahead
> RA_THRESHOLD 28 # Number of pages left before next group
> DBSPACETEMP omc_db_temp # Default temp dbspaces>
> output from onstat -p
>
> Profile
> dskreads pagreads bufreads Êched dskwrits pagwrits bufwrits Êched
> 890960 1087371 36156569 97.54 851638 1850908 3389826 74.88
>
> isamtot open start read write rewrite delete commit
rollbk
> 27502723 47701 2493635 17606895 857421 17010 300250 823 0
>
> 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 2598.48 409.81 84 196
>
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans
> 164589 93 18694337 0 0 88 42716 5906
>
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 271575 11542 14060 292760 6173
>
> onstat -m>
> Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line -- Up
08:16:55 --
> 147456 Kbytes
>
> Message Log File: /usr/informix/online.log_OMC
> <<Informix Dynamic Server 2000>>> Logical Log 12764 Complete.
> 01:50:59 Logical Log 12764 Complete.
> 01:53:23 Checkpoint Completed: duration was 97 seconds.
> 01:53:23 Checkpoint loguniq 12765, logpos 0x2af3d4
>
> 01:59:59 Checkpoint Completed: duration was 95 seconds.
> 01:59:59 Checkpoint loguniq 12765, logpos 0x14df54c
>
> 02:06:04 Checkpoint Completed: duration was 66 seconds.
> 02:06:04 Checkpoint loguniq 12765, logpos 0x2595418
>
>
> <<Informix Dynamic Server 2000>>> Logical Log 12765 Complete.
> 02:10:10 Logical Log 12765 Complete.
> 02:12:06 Checkpoint Completed: duration was 62 seconds.
> 02:12:06 Checkpoint loguniq 12766, logpos 0x3bc418
>
> 02:18:16 Checkpoint Completed: duration was 70 seconds.
> 02:18:16 Checkpoint loguniq 12766, logpos 0x1676018
>
Is there any chance you can increase CLEANERS and
LRUS? I know this is =
a
memory balancing issue but if you can, try to increase those. When we =
get a
long checkpoint it is often because the LRUS and CLEANERS got swamped =
with
data and can't keep up.
Darrell Murphy
Burton Snowboards
-----Original Message-----
From: Alexey Sonkin [mailto:alexeis@grandvirtual.com]
Sent: Thursday, October 28, 2004 1:29 PM
To: ids@iiug.org
Subject: RE: long checkpoints [3587]=20
Karen,
What platform are you on? Is it supporting KAIO?
(take a look at onstat -g iov)
It seems, you need to increase NUMAIOVPS to smth reasonable
(like: number of physical disk drives)=20
Is there any reason for setting " NOFUZZYCKPT 1"?
If is very useful to take a look at onstat -R before
the checkpoint and at onstat -F
-Alexey
=20
> -----Original Message-----
> From: Egan Karen-.... [mailto:Karen.Egan@motorola.com]
>=20
> Hi all,
>=20
> the performance on one of our Customer's production databases has
decreased
> dramatically in the last couple of weeks. Checkpoints seem to be =
taking
over a
> minute to complete. Running IDS 9.21 UC6 (which I know is no longer
supported
> but unfortunately as it is out there in our product in the field I =
can't
do
> much about this) on Solaris 8.
>=20
> If anyone has any pointers on the config parameters below that may =
help to
> improve performance they would be greatly appreciated. Also are there =
any
know
> bugs with checkpoints on this version of Informix.
>=20
> Below are what I believe are the relevant onconfig parameters
>=20
> PHYSDBS omc_db_plog # Location (dbspace) of physical log
> PHYSFILE 253894 # Physical log file size (Kbytes)
>=20
> LOGFILES 3 # Number of logical log files
> LOGSIZE 2000 # Logical log size (Kbytes)
>=20> NOFUZZYCKPT 1 # No fuzzy checkpoints
>=20
>=20
> MULTIPROCESSOR 1 # 0 for single-processor, 1 for =
multi-> processor
> NUMCPUVPS 2 # Number of user (cpu) vps
> SINGLE_CPU_VP 0 # If non-zero, limit number of cpu =vps to
one
>=20
>=20
> # Shared Memory Parameters
> LOCKS 75000 # Maximum number of locks
> BUFFERS 50000 # Maximum number of shared buffers
> NUMAIOVPS 1 # Number of IO vps
> PHYSBUFF 128 # Physical log buffer size (Kbytes)
> LOGBUFF 128 # Logical log buffer size (Kbytes)> LOGSMAX 50 # Maximum number of logical log files
> CLEANERS 6 # Number of buffer cleaner processes
> SHMBASE 0xa000000 # Shared memory base address
> SHMVIRTSIZE 32000 # initial virtual shared memory =segment
size
> SHMADD 8192 # Size of new shared memory segments
(Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
0=3D>unlimited
> CKPTINTVL 300 # Check point interval (in sec)
> LRUS 15 # Number of LRU queues
> LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning =limit
> LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning =limit
> LTXHWM 50 # Long transaction high water markpercentage
> LTXEHWM 60 # Long transaction high water mark
(exclusive)
> TXTIMEOUT 0x12c # Transaction timeout (in sec)
> STACKSIZE 32 # Stack size (Kbytes)
> RA_PAGES 32 # Number of pages to attempt to read =ahead
> RA_THRESHOLD 28 # Number of pages left before next =group
> DBSPACETEMP omc_db_temp # Default temp dbspaces
>=20> output from onstat -p
>=20
> Profile
> dskreads pagreads bufreads =CAched dskwrits pagwrits bufwrits =CAched
> 890960 1087371 36156569 97.54 851638 1850908 3389826 74.88
>=20
> isamtot open start read write rewrite delete commit
rollbk
> 27502723 47701 2493635 17606895 857421 17010 300250 823 =
0
>=20
> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> 0 0 0 0 0 0 0
>=20
> ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> 0 0 0 2598.48 409.81 84 196
>=20
> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress =
seqscans
> 164589 93 18694337 0 0 88 42716 5906
>=20
> ixda-RA idx-RA da-RA RA-pgsused lchwaits
> 271575 11542 14060 292760 6173
>=20
> onstat -m
>=20
> Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line -- Up
08:16:55 --> 147456 Kbytes
>=20
> Message Log File: /usr/informix/online.log_OMC
> <<Informix Dynamic Server 2000>>> Logical Log 12764 Complete.
> 01:50:59 Logical Log 12764 Complete.
> 01:53:23 Checkpoint Completed: duration was 97 seconds.
> 01:53:23 Checkpoint loguniq 12765, logpos 0x2af3d4
>=20
> 01:59:59 Checkpoint Completed: duration was 95 seconds.
> 01:59:59 Checkpoint loguniq 12765, logpos 0x14df54c
>=20
> 02:06:04 Checkpoint Completed: duration was 66 seconds.
> 02:06:04 Checkpoint loguniq 12765, logpos 0x2595418
>=20
>=20
> <<Informix Dynamic Server 2000>>> Logical Log 12765 Complete.
> 02:10:10 Logical Log 12765 Complete.
> 02:12:06 Checkpoint Completed: duration was 62 seconds.
> 02:12:06 Checkpoint loguniq 12766, logpos 0x3bc418
>=20
> 02:18:16 Checkpoint Completed: duration was 70 seconds.
> 02:18:16 Checkpoint loguniq 12766, logpos 0x1676018
>=20
Hi,
I think first you need to diagnose the problem.
Send onstat -F, onstat -R , onstat -R | grep dirty when the checkpoint
starts and during the checkpoint. Then we will be able to understand what
s going on there.
After I saw your onconfig paramater I would say it is an small instance, if
you ´ve got only 50000 BUFFERS how many dirty pages have you at checkpoint
time?
So first you need to see how many dirty pages and how those pages go to disk.
esteban.-
At 02:46 p.m. 28/10/2004 -0400, Darrell Murphy wrote:
>Is there any chance you can increase CLEANERS and LRUS? I know this is =
>a
>memory balancing issue but if you can, try to increase those. When we =
>get a
>long checkpoint it is often because the LRUS and CLEANERS got swamped =
>with
>data and can't keep up.
>
>Darrell Murphy
>Burton Snowboards
>
>
>-----Original Message-----
>From: Alexey Sonkin [mailto:alexeis@grandvirtual.com]
>Sent: Thursday, October 28, 2004 1:29 PM
>To: ids@iiug.org
>Subject: RE: long checkpoints [3587]=20
>
>
>Karen,
>
>What platform are you on? Is it supporting KAIO?
>(take a look at onstat -g iov)
>
>It seems, you need to increase NUMAIOVPS to smth reasonable
>(like: number of physical disk drives)=20
>
>Is there any reason for setting " NOFUZZYCKPT 1"?
>
>If is very useful to take a look at onstat -R before
>the checkpoint and at onstat -F
>
>-Alexey
>=20
>
> > -----Original Message-----
> > From: Egan Karen-.... [mailto:Karen.Egan@motorola.com]
> >=20
> > Hi all,
> >=20
> > the performance on one of our Customer's production databases has
>decreased
> > dramatically in the last couple of weeks. Checkpoints seem to be =
>taking
>over a
> > minute to complete. Running IDS 9.21 UC6 (which I know is no longer
>supported
> > but unfortunately as it is out there in our product in the field I =
>can't
>do
> > much about this) on Solaris 8.
> >=20
> > If anyone has any pointers on the config parameters below that may =
>help to
> > improve performance they would be greatly appreciated. Also are there =
>any
>know
> > bugs with checkpoints on this version of Informix.
> >=20
> > Below are what I believe are the relevant onconfig parameters
> >=20
> > PHYSDBS omc_db_plog # Location (dbspace) of physical log
> > PHYSFILE 253894 # Physical log file size (Kbytes)
> >=20
> > LOGFILES 3 # Number of logical log files
> > LOGSIZE 2000 # Logical log size (Kbytes)
> >=20> > NOFUZZYCKPT 1 # No fuzzy checkpoints
> >=20
> >=20
> > MULTIPROCESSOR 1 # 0 for single-processor, 1 for =
>multi-> > processor
> > NUMCPUVPS 2 # Number of user (cpu) vps
> > SINGLE_CPU_VP 0 # If non-zero, limit number of cpu =>vps to
>one
> >=20
> >=20
> > # Shared Memory Parameters
> > LOCKS 75000 # Maximum number of locks
> > BUFFERS 50000 # Maximum number of shared buffers
> > NUMAIOVPS 1 # Number of IO vps
> > PHYSBUFF 128 # Physical log buffer size (Kbytes)
> > LOGBUFF 128 # Logical log buffer size (Kbytes)> > LOGSMAX 50 # Maximum number of logical log files
> > CLEANERS 6 # Number of buffer cleaner processes
> > SHMBASE 0xa000000 # Shared memory base address
> > SHMVIRTSIZE 32000 # initial virtual shared memory =>segment
>size
> > SHMADD 8192 # Size of new shared memory segments
>(Kbytes)
> > SHMTOTAL 0 # Total shared memory (Kbytes).
>0=3D>unlimited
> > CKPTINTVL 300 # Check point interval (in sec)
> > LRUS 15 # Number of LRU queues
> > LRU_MAX_DIRTY 2 # LRU percent dirty begin cleaning =>limit
> > LRU_MIN_DIRTY 1 # LRU percent dirty end cleaning =>limit
> > LTXHWM 50 # Long transaction high water mark>percentage
> > LTXEHWM 60 # Long transaction high water mark
>(exclusive)
> > TXTIMEOUT 0x12c # Transaction timeout (in sec)
> > STACKSIZE 32 # Stack size (Kbytes)
> > RA_PAGES 32 # Number of pages to attempt to read =>ahead
> > RA_THRESHOLD 28 # Number of pages left before next =>group
> > DBSPACETEMP omc_db_temp # Default temp dbspaces
> >=20> > output from onstat -p
> >=20
> > Profile
> > dskreads pagreads bufreads =CAched dskwrits pagwrits bufwrits =CAched
> > 890960 1087371 36156569 97.54 851638 1850908 3389826 74.88
> >=20
> > isamtot open start read write rewrite delete commit
>rollbk
> > 27502723 47701 2493635 17606895 857421 17010 300250 823 =
> 0
> >=20
> > gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs
> > 0 0 0 0 0 0 0
> >=20
> > ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes
> > 0 0 0 2598.48 409.81 84 196
> >=20
> > bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress =
>seqscans
> > 164589 93 18694337 0 0 88 42716 5906
> >=20
> > ixda-RA idx-RA da-RA RA-pgsused lchwaits
> > 271575 11542 14060 292760 6173
> >=20
> > onstat -m
> >=20
> > Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line -- Up
>08:16:55 --> > 147456 Kbytes
> >=20
> > Message Log File: /usr/informix/online.log_OMC
> > <<Informix Dynamic Server 2000>>> Logical Log 12764 Complete.
> > 01:50:59 Logical Log 12764 Complete.
> > 01:53:23 Checkpoint Completed: duration was 97 seconds.
> > 01:53:23 Checkpoint loguniq 12765, logpos 0x2af3d4
> >=20
> > 01:59:59 Checkpoint Completed: duration was 95 seconds.
> > 01:59:59 Checkpoint loguniq 12765, logpos 0x14df54c
> >=20
> > 02:06:04 Checkpoint Completed: duration was 66 seconds.
> > 02:06:04 Checkpoint loguniq 12765, logpos 0x2595418
> >=20
> >=20
> > <<Informix Dynamic Server 2000>>> Logical Log 12765 Complete.
> > 02:10:10 Logical Log 12765 Complete.
> > 02:12:06 Checkpoint Completed: duration was 62 seconds.
> > 02:12:06 Checkpoint loguniq 12766, logpos 0x3bc418
> >=20
> > 02:18:16 Checkpoint Completed: duration was 70 seconds.
> > 02:18:16 Checkpoint loguniq 12766, logpos 0x1676018
> >=20