Re: long checkpoints
A user on IDS 7.31.UD8 (HP-UX) saw 150-160 second blocking checkpoints during a short, heavy insert/update batch; lowering LRU_MAX_DIRTY/LRU_MIN_DIRTY just slowed the batch instead. Respondents suggested measuring disk I/O (a small O_SYNC write test program and timed dd reads of a chunk), adding extra temp dbspaces to DBSPACETEMP, setting TRACECKPT=1 and checking onstat -g ioq, and dropping/recreating indexes around the load to cut logging and page I/O. The OP's results showed fast chunk reads but ~2 minutes to write 20MB synchronously, hinting at slow writes, but no resolution is recorded in the thread.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
On 21 Nov., 14:01, Apostrof <abdullah.ako...@gmail.com> wrote:
> hi,
> i have some problem with the long running checkpoints in IDS 7.31.UD8
> on HP/UX.
> every day a batch utility making hig volume of insert and updates. and
> this batch works about 5-10 minutes(checkpoint times included).
> during this batch load one or two (long) checkpoints occur.
> (unfortunately blocking checkpoints)
>
> 17:32:53 Checkpoint Completed: duration was 160 seconds.
> 17:32:53 Checkpoint loguniq 1250, logpos 0x1842484
> 17:38:59 Checkpoint Completed: duration was 155 seconds.
> 17:38:59 Checkpoint loguniq 1253, logpos 0x8fa098>
> some of the onconfig parameters are
>
> PHYSDBS rootdbs # Location (dbspace) of physical log
> PHYSFILE 100000 # Physical log file size (Kbytes)
>
> LOGFILES 40 # Number of logical log files
> LOGSIZE 20000 # Logical log size (Kbytes)> LOGSMAX 50
>
> LOCKS 150000 # Maximum number of locks
> BUFFERS 200000 # Maximum number of shared buffers
> NUMAIOVPS # Number of IO vps
> PHYSBUFF 64 # Physical log buffer size (Kbytes)
> LOGBUFF 64 # Logical log buffer size (Kbytes)
> CLEANERS 50 # Number of buffer cleaner processes
> SHMBASE 0x0 # Shared memory base address
> SHMVIRTSIZE 64000 # initial virtual shared memory> segment size
> SHMADD 32000 # Size of new shared memory segments
> (Kbytes)
> SHMTOTAL 0 # Total shared memory (Kbytes).
> 0=>unlimited
> CKPTINTVL 200 # Check point interval (in sec)
> LRUS 50 # Number of LRU queues
> LRU_MAX_DIRTY 60 # LRU percent dirty begin cleaning> limit
> LRU_MIN_DIRTY 50 # 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 64 # Stack size (Kbytes)>
> when checkpoint starts i look at onstat -F output and see one or two
> lines with state C. the chunk writes takes much of the checkpoint
> time.
> i changed the LRU_MAX_DIRTY:10, LRU_MIN_DIRTY:5 and try the batch
> again. but this time due to LRU writes
> the batch utility gets slower and finishes its job about at 20minutes.
> i've read in some notes that LRU writes is worse than chunk writes.
> so what can i do to make this batch process faster and shorten
> checkpoint times?
> thanks
>
> Abdullah
Hi Abdullah,
have you ever tried to find out your I/O speed? You might compile the
following c-code and start the test-program.
cat <<eof > prog.c
/*******save to prog.c ****************/
#include <fcntl.h>
main( argc, argv )
int argc;
char **argv;
{
int i;
int fd = -1;
char buffer_vc[4096*2];
char *ptr_pc;
for ( ptr_pc = buffer_vc; ( (int)ptr_pc % 1024 ) ; ptr_pc++ );
memset( ptr_pc, 'A', 2048 );
fd = open( "cifx_204", O_WRONLY|O_SYNC);
if ( fd == -1 )
{
perror( "Check existance of file cifx_204! touch cifx_204");
exit(1);
}
for ( i = 0; i < 10000; i++ )
write( fd, ptr_pc, 2048 );
close( fd );
}
/*********** end of code ****************/
eof
cc -o prog prog.c
touch cifx_204
time ./prog
I'm just interested how long it will take to write appr. 20MB
Best regards
Stefan
<SNIP > > > Hi Abdullah, > > have you ever tried to find out your I/O speed? You might compile the > following c-code and start the test-program. > > cat <<eof > prog.c > /*******save to prog.c ****************/ > #include <fcntl.h> > > main( argc, argv ) > int argc; > char **argv; > { > int i; > int fd = -1; > char buffer_vc[4096*2]; > char *ptr_pc; > > > for ( ptr_pc = buffer_vc; ( (int)ptr_pc % 1024 ) ; ptr_pc++ ); > > memset( ptr_pc, 'A', 2048 ); > fd = open( "cifx_204", O_WRONLY|O_SYNC); > > if ( fd == -1 ) > { > perror( "Check existance of file cifx_204! touch cifx_204"); > exit(1); > } > for ( i = 0; i < 10000; i++ ) > write( fd, ptr_pc, 2048 ); > close( fd ); > } > /*********** end of code ****************/ > eof > cc -o prog prog.c > touch cifx_204 > time ./prog > > I'm just interested how long it will take to write appr. 20MB > > Best regards > > Stefan > > > > Or, even check the read speed from the actual chunks : timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=20240 and timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=10240 the above read 20Mb each (bit late for me, so even basic maths is tricky), and you should be getting at least 4 Mb a second - so each of the above should take 5 seconds or less. As an aside, you only have the one temp dbspace, which appears pretty active, create another one or two and add them to DBSPACETEMP in the $ONCONFIG
On 22 Nov, 20:49, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote:
> <SNIP
>
>
>
>
>
>
>
> > Hi Abdullah,
>
> > have you ever tried to find out your I/O speed? You might compile the
> > following c-code and start the test-program.
>
> > cat <<eof > prog.c
> > /*******save to prog.c ****************/
> > #include <fcntl.h>
>
> > main( argc, argv )
> > int argc;
> > char **argv;
> > {
> > int i;
> > int fd = -1;
> > char buffer_vc[4096*2];
> > char *ptr_pc;
>
> > for ( ptr_pc = buffer_vc; ( (int)ptr_pc % 1024 ) ; ptr_pc++ );
>
> > memset( ptr_pc, 'A', 2048 );
> > fd = open( "cifx_204", O_WRONLY|O_SYNC);
>
> > if ( fd == -1 )
> > {
> > perror( "Check existance of file cifx_204! touch cifx_204");
> > exit(1);
> > }
> > for ( i = 0; i < 10000; i++ )
> > write( fd, ptr_pc, 2048 );
> > close( fd );
> > }
> > /*********** end of code ****************/
> > eof
> > cc -o prog prog.c
> > touch cifx_204
> > time ./prog
>
> > I'm just interested how long it will take to write appr. 20MB
>
> > Best regards
>
> > Stefan
>
> Or, even check the read speed from the actual chunks :
>
> timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=20240
>
> and
>
> timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=10240
>
> the above read 20Mb each (bit late for me, so even basic maths is tricky), and you should be getting at least 4 Mb a second - so
> each of the above should take 5 seconds or less.
>
> As an aside, you only have the one temp dbspace, which appears pretty active, create another one or two and add them to DBSPACETEMP
> in the $ONCONFIG- Hide quoted text -
>
> - Show quoted text -
1 As per http://www-1.ibm.com/support/docview.wss?uid=swg21250366 set
TRACECKPT=1
before starting the engine. What does that give?
2. What does onstat -g ioq give?
On Nov 23, 12:17 am, "da...@smooth1.co.uk" <da...@smooth1.co.uk>
wrote:
> On 22 Nov, 20:49, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote:
>
>
>
> > <SNIP
>
> > > Hi Abdullah,
>
> > > have you ever tried to find out your I/O speed? You might compile the
> > > following c-code and start the test-program.
>
> > > cat <<eof > prog.c
> > > /*******save to prog.c ****************/
> > > #include <fcntl.h>
>
> > > main( argc, argv )
> > > int argc;
> > > char **argv;
> > > {
> > > int i;
> > > int fd = -1;
> > > char buffer_vc[4096*2];
> > > char *ptr_pc;
>
> > > for ( ptr_pc = buffer_vc; ( (int)ptr_pc % 1024 ) ; ptr_pc++ );
>
> > > memset( ptr_pc, 'A', 2048 );
> > > fd = open( "cifx_204", O_WRONLY|O_SYNC);
>
> > > if ( fd == -1 )
> > > {
> > > perror( "Check existance of file cifx_204! touch cifx_204");
> > > exit(1);
> > > }
> > > for ( i = 0; i < 10000; i++ )
> > > write( fd, ptr_pc, 2048 );
> > > close( fd );
> > > }
> > > /*********** end of code ****************/
> > > eof
> > > cc -o prog prog.c
> > > touch cifx_204
> > > time ./prog
>
> > > I'm just interested how long it will take to write appr. 20MB
>
> > > Best regards
>
> > > Stefan
>
> > Or, even check the read speed from the actual chunks :
>
> > timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=20240
>
> > and
>
> > timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=10240
>
> > the above read 20Mb each (bit late for me, so even basic maths is tricky), and you should be getting at least 4 Mb a second - so
> > each of the above should take 5 seconds or less.
>
> > As an aside, you only have the one temp dbspace, which appears pretty active, create another one or two and add them to DBSPACETEMP
> > in the $ONCONFIG- Hide quoted text -
>
> > - Show quoted text -
>
> 1 As perhttp://www-1.ibm.com/support/docview.wss?uid=swg21250366set
> TRACECKPT=1
> before starting the engine. What does that give?
>
> 2. What does onstat -g ioq give?
i tried the prog.c program on the chunks directory.
results:
real 2:06.7
user 0.0
sys 0.0
output for timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k
count=20240
real 0.85
user 0.03
sys 0.33
output for timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k
count=10240
real 0.14
user 0.02
sys 0.12
On Nov 23, 12:17 am, "da...@smooth1.co.uk" <da...@smooth1.co.uk>
wrote:
> On 22 Nov, 20:49, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote:
>
>
>
> > <SNIP
>
> > > Hi Abdullah,
>
> > > have you ever tried to find out your I/O speed? You might compile the
> > > following c-code and start the test-program.
>
> > > cat <<eof > prog.c
> > > /*******save to prog.c ****************/
> > > #include <fcntl.h>
>
> > > main( argc, argv )
> > > int argc;
> > > char **argv;
> > > {
> > > int i;
> > > int fd = -1;
> > > char buffer_vc[4096*2];
> > > char *ptr_pc;
>
> > > for ( ptr_pc = buffer_vc; ( (int)ptr_pc % 1024 ) ; ptr_pc++ );
>
> > > memset( ptr_pc, 'A', 2048 );
> > > fd = open( "cifx_204", O_WRONLY|O_SYNC);
>
> > > if ( fd == -1 )
> > > {
> > > perror( "Check existance of file cifx_204! touch cifx_204");
> > > exit(1);
> > > }
> > > for ( i = 0; i < 10000; i++ )
> > > write( fd, ptr_pc, 2048 );
> > > close( fd );
> > > }
> > > /*********** end of code ****************/
> > > eof
> > > cc -o prog prog.c
> > > touch cifx_204
> > > time ./prog
>
> > > I'm just interested how long it will take to write appr. 20MB
>
> > > Best regards
>
> > > Stefan
>
> > Or, even check the read speed from the actual chunks :
>
> > timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=20240
>
> > and
>
> > timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=10240
>
> > the above read 20Mb each (bit late for me, so even basic maths is tricky), and you should be getting at least 4 Mb a second - so
> > each of the above should take 5 seconds or less.
>
> > As an aside, you only have the one temp dbspace, which appears pretty active, create another one or two and add them to DBSPACETEMP
> > in the $ONCONFIG- Hide quoted text -
>
> > - Show quoted text -
>
> 1 As perhttp://www-1.ibm.com/support/docview.wss?uid=swg21250366set
> TRACECKPT=1
> before starting the engine. What does that give?
>
> 2. What does onstat -g ioq give?
i tried the prog.c program on the chunks directory.
results:
real 2:06.7
user 0.0
sys 0.0
output for timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k
count=20240
real 0.85
user 0.03
sys 0.33
output for timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k
count=10240
real 0.14
user 0.02
sys 0.12
On Nov 23, 12:17 am, "da...@smooth1.co.uk" <da...@smooth1.co.uk>
wrote:
> On 22 Nov, 20:49, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote:
>
>
>
> > <SNIP
>
> > > Hi Abdullah,
>
> > > have you ever tried to find out your I/O speed? You might compile the
> > > following c-code and start the test-program.
>
> > > cat <<eof > prog.c
> > > /*******save to prog.c ****************/
> > > #include <fcntl.h>
>
> > > main( argc, argv )
> > > int argc;
> > > char **argv;
> > > {
> > > int i;
> > > int fd = -1;
> > > char buffer_vc[4096*2];
> > > char *ptr_pc;
>
> > > for ( ptr_pc = buffer_vc; ( (int)ptr_pc % 1024 ) ; ptr_pc++ );
>
> > > memset( ptr_pc, 'A', 2048 );
> > > fd = open( "cifx_204", O_WRONLY|O_SYNC);
>
> > > if ( fd == -1 )
> > > {
> > > perror( "Check existance of file cifx_204! touch cifx_204");
> > > exit(1);
> > > }
> > > for ( i = 0; i < 10000; i++ )
> > > write( fd, ptr_pc, 2048 );
> > > close( fd );
> > > }
> > > /*********** end of code ****************/
> > > eof
> > > cc -o prog prog.c
> > > touch cifx_204
> > > time ./prog
>
> > > I'm just interested how long it will take to write appr. 20MB
>
> > > Best regards
>
> > > Stefan
>
> > Or, even check the read speed from the actual chunks :
>
> > timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=20240
>
> > and
>
> > timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k count=10240
>
> > the above read 20Mb each (bit late for me, so even basic maths is tricky), and you should be getting at least 4 Mb a second - so
> > each of the above should take 5 seconds or less.
>
> > As an aside, you only have the one temp dbspace, which appears pretty active, create another one or two and add them to DBSPACETEMP
> > in the $ONCONFIG- Hide quoted text -
>
> > - Show quoted text -
>
> 1 As perhttp://www-1.ibm.com/support/docview.wss?uid=swg21250366set
> TRACECKPT=1
> before starting the engine. What does that give?
>
> 2. What does onstat -g ioq give?
i tried the prog.c program on the chunks directory.
results:
real 2:06.7
user 0.0
sys 0.0
output for timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k
count=20240
real 0.85
user 0.03
sys 0.33
output for timex dd if=/data1/db/datachunk1 of=/dev/null bs=2k
count=10240
real 0.14
user 0.02
sys 0.12
> ... >every day a batch utility making hig volume of insert and updates. and >this batch works about 5-10 minutes(checkpoint times included). >during this batch load one or two (long) checkpoints occur. >(unfortunately blocking checkpoints) > ... Just a thought. Have you thought about disabling/dropping any indexes before the load and then enabling/recreating them after the load? If this could be done (perhaps concurrency issues won't let you) your load would finish quicker and your indexes would be more compact and efficient. During the load it would also reduce the number of logical log records generated, pages needed to be read in from disk and pages needed to be written to disk. RoB