Thread: bad performance on irix
we're running on sgi powerchallenge, 8 r10000 4-way smp, and we're getting bad performance from postgres, throughput increases from 1 to 5 streams, but from 5 and above there is no further increase, performance analysis show high sleep waiting for resources an example:
running(user mode) =32.57
running(system mode)=7.15
running(graphics mode)=0.21
waiting(for block I/O)=0.03
waiting(paging)=0.00
waiting(for memory)=0.00
waiting(in select)=17.13
waiting(in cpu queue)=0.26
sleep(for resource)=42.87
any tip?
thanks and regards
almost forget postgres7.2
The bad performance in Irix appears to be a lack of resources, most likely system buffers for sockets and I/O. Try increasing the system parameter, nbuf, using systune, reboot, and see if it helps. Also, use the "par" program with options "-s -SS -i -u -p <pid>" to monitor activity in the backend -- that may provide some clues. +-----------------------------+------------------------------------+ | Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org | | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 | | +-----------------------------+------------------------------------+
thanks for your answer, I send three mails detailing, you must have missed one, briefing: we're running a like tpch on an 8 processor machine, performance grows from 1 to 5 streams, but in 5 streams and up results get steady, I mean, the results are measured on query-per-hour, it is obtained multiplying for the number of streams and dividing for real time, so when your up of 5 streams it is supposed than performance must grow up to 7 or 8 streams, but it gets steady. thank and regard
nbuf is set to 6653, here is a excerpt from par, thanks and regards 0.000mS(+ 0uS)[ 6] postgres(54373): END-semctl() = 0 0.038mS(+ 37uS)[ 6] postgres(54373): semop(606,0x7fff1b50, 1) OK 20.122mS(+20084uS)[ 6] postgres(54373): semop(606, 0x7fff1b10, 1) 27.747mS(+ 7624uS)[ 6] postgres(54373):END-semop() OK 27.772mS(+ 24uS)[ 6] postgres(54373): semop(606, 0x7fff1b50, 1) OK 30.772mS(+ 3000uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) 35.681mS(+ 4908uS)[ 6] postgres(54373):END-semop() OK 35.703mS(+ 21uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 40.219mS(+ 4516uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) 58.859mS(+18640uS)[ 6] postgres(54373):END-semop() OK 58.882mS(+ 23uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) 61.475mS(+ 2592uS)[ 6] postgres(54373): END-semop() OK 61.495mS(+ 20uS)[ 6] postgres(54373): semop(606, 0x7fff1a10,1) OK 61.967mS(+ 471uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 62.839mS(+ 871uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 63.063mS(+ 224uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 65.175mS(+ 2112uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) 83.060mS(+17884uS)[ 6] postgres(54373):END-semop() OK 83.083mS(+ 22uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) 85.848mS(+ 2764uS)[ 6] postgres(54373): END-semop() OK 85.869mS(+ 21uS)[ 6] postgres(54373): semop(606, 0x7fff1a10,1) OK 87.775mS(+ 1906uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 87.898mS(+ 122uS)[ 6] postgres(54373): semop(606, 0x7fff1b10, 1) OK 89.822mS(+ 1924uS)[ 6] postgres(54373): semop(606, 0x7fff1b50, 1) OK 91.676mS(+ 1853uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) 100.127mS(+ 8450uS)[ 6] postgres(54373):END-semop() OK 100.152mS(+ 25uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 110.706mS(+10553uS)[ 6] postgres(54373): semop(606, 0x7fff1b10, 1) OK 111.109mS(+ 403uS)[ 6] postgres(54373): semop(606, 0x7fff1b50, 1) OK 112.860mS(+ 1750uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 113.292mS(+ 432uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 118.938mS(+ 5646uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 119.440mS(+ 502uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 120.410mS(+ 969uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 120.553mS(+ 142uS)[ 6] postgres(54373): semop(606, 0x7fff1b50, 1) OK 126.386mS(+ 5833uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 126.919mS(+ 533uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 127.574mS(+ 654uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 128.011mS(+ 436uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 128.489mS(+ 477uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 128.895mS(+ 405uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 128.990mS(+ 95uS)[ 6] postgres(54373): semop(606, 0x7fff1b50, 1) OK 149.407mS(+20416uS)[ 6] postgres(54373): semop(606, 0x7fff1b10, 1) OK 149.969mS(+ 561uS)[ 6] postgres(54373): semop(606, 0x7fff1b10, 1) OK 150.364mS(+ 395uS)[ 6] postgres(54373): semop(606, 0x7fff1b50, 1) OK 151.462mS(+ 1097uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) 156.185mS(+ 4723uS)[ 6] postgres(54373):END-semop() OK 156.204mS(+ 18uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 156.876mS(+ 671uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 158.145mS(+ 1269uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 158.873mS(+ 728uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK 159.773mS(+ 899uS)[ 6] postgres(54373): semop(606, 0x7fff1a10, 1) OK 160.309mS(+ 535uS)[ 6] postgres(54373): semop(606, 0x7fff1a00, 1) OK
part of postgres.conf fsync on wal_files=6 wal_buffers=64 shared_buffers=81940 sort_mem=16384 checkpoint segments=10 thanks and regards
Dear Luis, > > nbuf is set to 6653, here is a excerpt from par, thanks and regards What kind of SGI are you using, and how much memory does it have? I don't know what to make out of this par output. If this is from a running Postgres, then it's waiting for a lock. Try the following: echo where | dbx -p <pid> where <pid> is for the Postgres backend. --Bob +-----------------------------+------------------------------------+ | Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org | | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 | | +-----------------------------+------------------------------------+
sorry, I thought i 've posted it before: Processor 0: 196 MHZ IP25 CPU: MIPS R10000 Processor Chip Revision: 2.5 FPU: MIPS R10010 Floating Point Chip Revision: 2.5 Processor 1: 196 MHZ IP25 CPU: MIPS R10000 Processor Chip Revision: 2.5 FPU: MIPS R10010 Floating Point Chip Revision: 2.5 Processor 2: 196 MHZ IP25 CPU: MIPS R10000 Processor Chip Revision: 2.5 FPU: MIPS R10010 Floating Point Chip Revision: 2.5 Processor 3: 196 MHZ IP25 CPU: MIPS R10000 Processor Chip Revision: 2.5 FPU: MIPS R10010 Floating Point Chip Revision: 2.5 Processor 4: 196 MHZ IP25 CPU: MIPS R10000 Processor Chip Revision: 2.6 FPU: MIPS R10010 Floating Point Chip Revision: 2.6 Processor 5: 196 MHZ IP25 CPU: MIPS R10000 Processor Chip Revision: 2.6 FPU: MIPS R10010 Floating Point Chip Revision: 2.6 Processor 6: 196 MHZ IP25 CPU: MIPS R10000 Processor Chip Revision: 2.6 FPU: MIPS R10010 Floating Point Chip Revision: 2.6 Processor 7: 196 MHZ IP25 CPU: MIPS R10000 Processor Chip Revision: 2.6 FPU: MIPS R10010 Floating Point Chip Revision: 2.6 Main memory size: 1024 Mbytes, 2-way interleaved Instruction cache size: 32 Kbytes Data cache size: 32 Kbytes Secondary unified instruction/data cache size: 2 Mbytes Integral SCSI controller 0: Version WD33C95A, single ended, revision 0 Tape drive: unit 4 on SCSI controller 0: DAT CDROM:unit 5 on SCSI controller 0 Integral SCSI controller 1: Version WD33C95A, differential, revision 0 Disk drive: unit 1 on SCSI controller 1 Disk drive:unit 2 on SCSI controller 1 Disk drive: unit 3 on SCSI controller 1 Disk drive: unit 4 on SCSI controller 1 Integral EPC serial ports: 4 Integral EPC parallel port: Ebus slot 5 Integral Ethernet controller: et0, Ebus slot 5 I/O board, Ebus slot 5: IO4 revision 1 VME bus: adapter 21 VME bus: adapter 0 mapped to adapter 21 EPC external interrupts thanks and regards
if you are interested, here is what dbx gives out, they are from 4 different backends thanks and regards
hi robert: postgres is not using all the ram it has allocated, our database is about 100Mb, it grows up to 300 - 400 Mb on an execution, so i don't think it should be lack of memory. thanks and regards
Yes, its waiting for locks, almost all orange area in the grafic is due to lock contention thanks and regards
Dear Luis,After looking at your system configuration, I would recommend buying more RAM (it's very inexpensive for older systems like yours), and then allocating much more buffer space for PostgreSQL. It will have a profound effect on overall performance, although not for this particular problem where lock contention is an issue. +-----------------------------+------------------------------------+ | Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org | | P.O. Box 314 | URL: http://www.congen.com/~bruc | | Pennington, NJ 08534 | | +-----------------------------+------------------------------------+
lamigo@atc.unican.es ("Luis Alberto Amigo Navarro") writes: > we're running on sgi powerchallenge, 8 r10000 4-way smp, and we're> getting bad performance from postgres, throughput increasesfrom 1> to 5 streams, but from 5 and above there is no further increase,> performance analysis show high sleep waitingfor resources IIRC there is a bottleneck on calls to sleep() or similar on IRIX SMP. All requests are dealt with on just one of the CPUs. I don't recollect whether there is a way to work around that or whether programs need to be rewritten. -- Pete Forman -./\.- Disclaimer: This post is originated WesternGeco -./\.- by myself and does not represent pete.forman@westerngeco.com -./\.- opinion of Schlumberger, Baker http://petef.port5.com (new) -./\.- Hughes or their divisions.