Re: long checkpoint problem
andykent.bristol@virgin.net (Andy Kent) wrote in message news:<a3525a11.0401290307.42f6e997@posting.google.com>...
> It's quite plausible that page cleaning would lag behind buffer writes
> during a bulk load. Disk i/o is obviously inherently much, much slower
> than buffer i/o. It's likely to be slower if:
>
> - Everything's being written out to one disk or volume (e.g. the table
> doesn't use fragmentation)
Yes! It was disk with temporary dbspace.
device %busy avque r+w/s blks/s avwait avserv (-d)
16:20:00 Sdsk-4 99.21 1.00 98.09 722.96 0.00 10.11
16:40:00 Sdsk-4 100.00 1.00 90.63 742.17 0.00 15.09
17:20:00 Sdsk-4 100.00 1.00 185.21 1486.84 0.00 9.00
17:40:00 Sdsk-4 100.00 1.00 225.13 1217.06 0.00 9.65
and long checkpoint:
16:23:38 Checkpoint Completed: duration was 17 seconds.
17:10:34 Checkpoint Completed: duration was 20 seconds.
17:23:41 Checkpoint Completed: duration was 55 seconds.
17:31:17 Checkpoint Completed: duration was 28 seconds.
> - CLEANERS != no. of disks it's writing to
Yes. There are 3 disks, each is RAID0 (mirror), for data and 1 - for
tempdbs. But only 2 physical CPU...
> - You're using RAID5 (NO RAID5! NO RAID5! NO RAID5!)
I agree - NO RAID5!
> - You're using INSERTs rather than HPL (though to use HPL you'd have
> to redefine the table as a permanent one)
It's application. Before make work all selected rows puts into temp
table. It may be a quite large number of rows and it in user's hands!
> - AIO VPs aren't tuned correctly
May be - please see i/o statistics at the end of message.
> - The table hasn't been sized correctly so it keeps having to write
> out new extent allocation information
Yes! For temp table was used default extent size. Good notice!
>
> Care to post some info on your disk config?
>
> What were you hoping to achieve by making the physical log smaller? If
> it was to increase checkpoint frequency then yes this would achieve
> that effect but could be lethal if you made it TOO small.
Yes, you are rigth. I was upseted and not thinked appropriately.
> Try adjusting CKPTINTVL instead.
>
Sorry my English.
> Andy
Alex.
P.S.
AIO global info:
6 aio classes
31 open files
64 max global files
AIO I/O queues:
q name/id len maxlen totalops dskread dskwrite dskcopy
adt 0 0 0 0 0 0 0
msc 0 0 1 439 0 0 0
aio 0 0 1 328 164 0 0
pio 0 0 1 852 0 852 0
lio 0 0 1 1985 0 1985 0
gfd 3 0 16 26988 1709 25279 0
gfd 4 0 77 2180641 556205 1624436 0
gfd 5 0 52 95219 69921 25298 0
gfd 6 0 38 800590 793263 7327 0
gfd 7 0 40 338987 332497 6490 0
gfd 8 0 41 285926 285712 214 0
gfd 9 0 53 1185978 1137356 48622 0
gfd 10 0 52 975820 962830 12990 0
gfd 11 0 1 146 145 1 0
gfd 12 0 38 40389 28059 12330 0
gfd 13 0 52 214247 214194 53 0
gfd 14 0 1 108 108 0 0
gfd 15 0 10 367 302 65 0
gfd 16 0 0 0 0 0 0
gfd 17 0 43 281857 280579 1278 0
gfd 18 0 16 1099250 1098449 801 0
gfd 19 0 16 816414 813330 3084 0
gfd 20 0 16 304076 275245 28831 0
gfd 21 0 74 1352536 1349018 3518 0
gfd 22 0 52 832408 806664 25744 0
gfd 23 0 52 832408 806664 25744 0
gfd 24 0 1 7 7 0 0
gfd 25 0 1 4946 4946 0 0
gfd 26 0 32 706516 703726 2790 0
gfd 27 0 1 3 3 0 0
gfd 28 0 52 1596150 1588429 7721 0
gfd 29 0 1 3 3 0 0
gfd 30 0 1 3 3 0 0
AIO I/O vps:
class/vp s io/s totalops dskread dskwrite dskcopy wakeups io/wup
errors
msc 0 i 0.0 439 0 0 0 438 1.0
0
aio 0 i 13.2 1000139 922709 77286 0 875052 1.1
0
aio 1 i 12.1 913225 838889 74326 0 827833 1.1
0
aio 2 i 11.3 857368 788895 68469 0 780730 1.1
0
aio 3 i 10.7 808501 742739 65760 0 738572 1.1
0
aio 4 i 10.1 765309 702871 62437 0 699782 1.1
0
aio 5 i 9.7 730822 671013 59809 0 668452 1.1
0
aio 6 i 9.3 705079 647127 57951 0 644157 1.1
0
aio 7 i 9.0 680232 623834 56398 0 624003 1.1
0
aio 8 i 8.7 661005 605972 55033 0 605157 1.1
0
aio 9 i 8.5 640230 587107 53122 0 583304 1.1
0
aio 10 i 8.1 614319 562517 51802 0 532505 1.2
0
aio 11 i 7.7 579855 529708 50147 0 455235 1.3
0
aio 12 i 7.2 547176 498112 49063 0 411269 1.3
0
aio 13 i 6.7 509861 461989 47872 0 416278 1.2
0
aio 14 i 6.5 494899 447238 47661 0 419991 1.2
0
aio 15 i 6.5 494212 444279 49933 0 407697 1.2
0
pio 0 i 0.0 852 0 852 0 852 1.0
0
lio 0 i 0.0 1985 0 1985 0 1985 1.0
0
AIO global files:
gfd pathname totalops dskread dskwrite io/s
3 /dev/rrootdbs 16923 1709 15214 0.2
4 /dev/rctempdbs0 1209694 438389 771305 16.0
5 /dev/ramaindbs0 82093 64698 17395 1.1
6 /dev/ramaindbs1 799270 793162 6108 10.6
7 /dev/ramaindbs2 337585 332484 5101 4.5
8 /dev/ramaindbs3 285892 285679 213 3.8
9 /dev/rbmaindbs0 1098656 1065954 32702 14.5
10 /dev/rbmaindbs1 909405 898178 11227 12.0
11 /dev/rbmaindbs2 146 145 1 0.0
12 /dev/ramaindbs6 35795 27132 8663 0.5
13 /dev/rbmaindbs6 186331 186295 36 2.5
14 /dev/rbmaindbs5 108 108 0 0.0
15 /dev/rbmaindbs3 315 268 47 0.0
16 /dev/rctempdbs1 0 0 0 0.0
17 /dev/ramaindbs4 281810 280543 1267 3.7
18 /dev/rdmaindbs0 1099249 1098448 801 14.5
19 /dev/rdmaindbs1 816292 813329 2963 10.8
20 /dev/rdmaindbs2 292986 275217 17769 3.9
21 /dev/rdmaindbs3 1120646 1117135 3511 14.8
22 /dev/rdmaindbs4 282959 265211 17748 3.7
23 /dev/rdmaindbs4 384167 378935 5232 5.1
24 bchunkt1 859 7 852 0.0
25 dchunk8 6931