|
IDS Forum
Re: long checkpoints
Posted By: jspalla@anexsys.com Date:
--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 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=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 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 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%"><img src=3D"cid:30__=3D09BBE58EDFCBA4818f9e8a93df93@anexsys.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">ids@iiug.org</font></td></tr>
<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">cc</font></div></td><td width=3D"= 100%"><img src=3D"cid:30__=3D09BBE58EDFCBA4818f9e8a93df93@anexsys.com" = border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> </td></tr>
<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">Subject</font></div></td><td widt= h=3D"100%"><img src=3D"cid:30__=3D09BBE58EDFCBA4818f9e8a93df93@anexsys.= com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><br> <font size=3D"2">long checkpoints [3494]</font></td></tr> </table>
<table border=3D"0" cellspacing=3D"0" cellpadding=3D"0"> <tr valign=3D"top"><td width=3D"58"><img src=3D"cid:30__=3D09BBE58EDFCB= A4818f9e8a93df93@anexsys.com" border=3D"0" height=3D"1" width=3D"1" alt= =3D""></td><td width=3D"336"><img src=3D"cid:30__=3D09BBE58EDFCBA4818f9= e8a93df93@anexsys.com" border=3D"0" height=3D"1" width=3D"1" alt=3D""><= /td></tr> </table> </td></tr> </table> <br> <tt>Hi all,<br> <br> the performance on one of our Customer's production databases has decre= ased dramatically in the last couple of weeks. Checkpoints seem to be t= aking 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.<br> <br> If anyone has any pointers on the config parameters below that may help= to improve performance they would be greatly appreciated. Also are the= re any know bugs with checkpoints on this version of Informix.<br> <br> Below are what I believe are the relevant onconfig parameters<br> <br> PHYSDBS omc_db_plog # Locatio= n (dbspace) of physical log<br> PHYSFILE 253894 = # Physical log file size (Kbytes)<br> <br> LOGFILES 3  = ; # Number of logical log files<br> LOGSIZE 2000 &n= bsp; # Logical log size (Kbytes)<br> <br> NOFUZZYCKPT 1 &= nbsp; # No fuzzy checkpoints<br> <br> <br> MULTIPROCESSOR 1 = # 0 for single-processor, 1 for multi-processor<br> NUMCPUVPS 2 &nb= sp; # Number of user (cpu) vps<br> SINGLE_CPU_VP 0 = # If non-zero, limit number of cpu vps to one<br> <br> <br> # Shared Memory Parameters<br> LOCKS 75000 &nb= sp; # Maximum number of locks<br> BUFFERS 50000 &= nbsp; # Maximum number of shared buffers<br> NUMAIOVPS 1 &nb= sp; # Number of IO vps<br> PHYSBUFF 128 &nb= sp; # Physical log buffer size (Kbytes)<br> LOGBUFF 128 &nb= sp; # Logical log buffer size (Kbytes)<br> LOGSMAX 50 &nbs= p; # Maximum number of logical log files<br> CLEANERS 6  = ; # Number of buffer cleaner processes<br> SHMBASE 0xa000000 &nbs= p; # Shared memory base address<br> SHMVIRTSIZE 32000 # in= itial virtual shared memory segment size<br> SHMADD 8192 &nbs= p; # Size of new shared memory segments (Kbytes)<br> SHMTOTAL 0  = ; # Total shared memory (Kbytes). 0=3D>unlimited<br> CKPTINTVL 300 &= nbsp; # Check point interval (in sec)<br> LRUS 15 &= nbsp; # Number of LRU queues<br> LRU_MAX_DIRTY 2 = # LRU percent dirty begin cleaning limit<br> LRU_MIN_DIRTY 1 = # LRU percent dirty end cleaning limit<br> LTXHWM 50 = # Long transaction high water mark percentage<br> LTXEHWM 60 &nbs= p; # Long transaction high water mark (exclusive)<br> TXTIMEOUT 0x12c = # Transaction timeout (in sec)<br> STACKSIZE 32 &n= bsp; # Stack size (Kbytes)<br> RA_PAGES 32 &nbs= p; # Number of pages to attempt to read ahead<br> RA_THRESHOLD 28 = # Number of pages left before next group<br> DBSPACETEMP omc_db_temp # Default temp dbsp= aces<br> <br> output from onstat -p<br> <br> Profile<br> dskreads pagreads bufreads =CAched dskwrits pagwrits bufwrits =CAched<b= r> 890960 1087371 36156569 97.54 851638 1850908= 3389826 74.88 <br> <br> isamtot open start read = write rewrite delete commit rollbk<br>=
27502723 47701 2493635 17606895 857421 17010 = 300250 823 0<br> <br> gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp= _curs <br> 0 0 0 &nbs= p; 0 0 &nb= sp;0 0 <br> <br> ovlock ovuserthread ovbuff usercpu syscpu nu= mckpts flushes <br> 0 0  = ;0 2598.48 409.81 84 &nb= sp; 196 <br> <br> bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compres= s seqscans<br> 164589 93 18694337 0 &= nbsp;0 88 42716 = 5906 <br> <br> ixda-RA idx-RA da-RA RA-pgsused lchwaits<br> 271575 11542 14060 292760  = ; 6173 <br> <br> onstat -m <br> <br> Informix Dynamic Server 2000 Version 9.21.UC6 -- On-Line = -- Up 08:16:55 -- 147456 Kbytes<br> <br> Message Log File: /usr/informix/online.log_OMC<br> <<Informix Dynamic Server 2000>>> Logical Log 12764 C= omplete. <br> 01:50:59 Logical Log 12764 Complete.<br> 01:53:23 Checkpoint Completed: duration was 97 seconds.<br>=
01:53:23 Checkpoint loguniq 12765, logpos 0x2af3d4<br> <br> 01:59:59 Checkpoint Completed: duration was 95 seconds.<br>=
01:59:59 Checkpoint loguniq 12765, logpos 0x14df54c<br> <br> 02:06:04 Checkpoint Completed: duration was 66 seconds.<br>=
02:06:04 Checkpoint loguniq 12765, logpos 0x2595418<br> <br> <br> <<Informix Dynamic Server 2000>>> Logical Log 12765 C= omplete. <br> 02:10:10 Logical Log 12765 Complete.<br> 02:12:06 Checkpoint Completed: duration was 62 seconds.<br>=
02:12:06 Checkpoint loguniq 12766, logpos 0x3bc418<br> <br> 02:18:16 Checkpoint Completed: duration was 70 seconds.<br>=
02:18:16 Checkpoint loguniq 12766, logpos 0x1676018<br> <br> <br> </tt><br> <br> <br> 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, co= pying, distribution, or use of the information contained herein (includ= ing any reliance thereon) is STRICTLY PROHIBITED. If you received this = transmission in error, please immediately contact the sender and destro= y the material in its entirety, whether in electronic or hard copy form= at.</body></html>=
--1__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481--
--0__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481 Content-type: image/gif; name="graycol.gif" Content-Disposition: inline; filename="graycol.gif" Content-ID: <10__=09BBE58EDFCBA4818f9e8a93df93@anexsys.com> Content-transfer-encoding: base64
R0lGODlhEAAQAKECAMzMzAAAAP///wAAACH5BAEAAAIALAAAAAAQABAAAAIXlI+py+0PopwxUbpu ZRfKZ2zgSJbmSRYAIf4fT3B0aW1pemVkIGJ5IFVsZWFkIFNtYXJ0U2F2ZXIhAAA7
--0__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481 Content-type: image/gif; name="pic04664.gif" Content-Disposition: inline; filename="pic04664.gif" Content-ID: <20__=09BBE58EDFCBA4818f9e8a93df93@anexsys.com> Content-transfer-encoding: base64
R0lGODlhWABDALP/AAAAAK04Qf79/o+Gm7WuwlNObwoJFCsoSMDAwGFsmIuezf///wAAAAAAAAAA AAAAACH5BAEAAAgALAAAAABYAEMAQAT/EMlJq704682770RiFMRinqggEUNSHIchG0BCfHhOjAuh EDeUqTASLCbBhQrhG7xis2j0lssNDopE4jfIJhDaggI8YB1sZeZgLVA9YVCpnGagVjV171aRVrYR RghXcAGFhoUETwYxcXNyADJ3GlcSKGAwLwllVC1vjIUHBWsFilKQdI8GA5IcpApeJQt8L09lmgkH LZikoU5wjqcyAMMFrJIDPAKvCFletKSev1HBw8KrxtjZ2tvc3d5VyKtCKW3jfz4uMKmq3xu4N0nK BVoJQmx2LGVOmrqNjjJf2hHAQo/eDwJGTKhQMcgQEEAnEjFS98+RnW3smGkZU6ncCWav/4wYOnAI TihRL/4FEwbp28BXMMcoscQCVxlepL4IGDSCyJyVQOu0o7CjmLN50OZlqWmyFy5/6yBBuji0AxFR M00oQAqNIstqI6qKHUsWRAEAvagsmfUEAImyxgbmUpJk3IklNUtJOUAVLoUr1+wqDGTE4zk+T6FG uQb3SizBCwatiiUgCBN8vrz+zFjVyQ8FWkOlg4NQiZMB5QS8QO3mpOaKnL0Z2EKvNMSILEThKhCg zMKPVxYJh23qm9KNW7pArPynMqZDiErsTMqI+LRi3QAgkFUbXpuFKhSYZALd0O5RKa2z9EYKBbpb qxIKsjUPRgD7I2XYV6wyrOw92ykExP8NW4URhknC5dKGE4v4NENQj2jXjmfNgOZDaXb5glRmXQ33 YEWQYNcZFnrYcIQLNzyTFDQNkXIff0ExVlY4srziQk43inZgL4rwxxINMvpFFAz1KOODHiu+4aEw NEjFl5B3JIKWKF3k6I9bfUGp5ZZcdunll5IA4cuHvQQJ5gcsoCWOOUwgltIwAKRxJgbIkJAQZEq0 2YliZnpZZ4BH3CnYOXldOUOfQoYDqF1LFHbXCrO8xmRsfoXDXJ6ChjCAH3QlhJcT6VWE6FCkfCco CgrMFsROrIEX3o2whVjWDjoJccN3LdggSGXLCdLEgHr1lyU3O3QxhgohNKXJCWv8JQr/PDdaqd6w 2rj1inLiGeiCJoDspAoQlYE6QWLSECehcWIYxIQES6zhbn1iImTHEQyqJ4eIxJJoUBc+3CbBuwZE V5cJPPkIjFDdeEabQbd6WgICTxiiz0f5dBKquXF6k4senwEhYGnKEFJeGrxUZy8dB8gmAXI/sPvH ESfCwVt5hTgYiqQqtdRNHQIU1PJ33ZqmzgE90OwLaoJcnMop1WiMmgkPHQRIrwgFuNV90A3doNKT mrKIN07AnGcI9BQjhCBN4RfA1qIZnMqorJCogKfGQnxSCDilTVIA0yl5ciTovgLuBDKFUDE9aQcw 9SA+rjSNf9/M1gxrj6VwDTS0IUSElMzBfsj0NFXR2kwsV1A5IF1grLgLL/r1R40BZEnuBWgmQEyb jqRwSAt6bqMCOFkvKFN2GPPkUzIm/SCF8z8pVzpbjVnMsy0vOr1hw3SaSRUhpY09v0z0J1FnwzPl fmh+xl4WtR0zGu24I4KbMQm3lnVu2oNWxI9W/lcyzA+mCKF4DBikxb/+UWtOGRiFP8qEwAayIgIA Ow==
--0__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481 Content-type: image/gif; name="ecblank.gif" Content-Disposition: inline; filename="ecblank.gif" Content-ID: <30__=09BBE58EDFCBA4818f9e8a93df93@anexsys.com> Content-transfer-encoding: base64
R0lGODlhEAABAIAAAAAAAP///yH5BAEAAAEALAAAAAAQAAEAAAIEjI8ZBQA7
--0__=09BBE58EDFCBA4818f9e8a93df938690918c09BBE58EDFCBA481--
Messages In This Thread
- long checkpoints
Egan Karen-kegan01 -- Tuesday, 28 September 2004, at 9:49 a.m.
- Re: long checkpoints
jspalla@anexsys.com -- Tuesday, 28 September 2004, at 12:10 p.m.
- RE: long checkpoints
Alexey Sonkin -- Thursday, 28 October 2004, at 1:28 p.m.
IDS Forum is maintained by Administrator with WebBBS 5.12.
|
|