bottlenecks in linux databse system

Hi I am having some performance problems with postgres 8.2 on a raid disk system. I have used top, iostat and vmstat, but the timing options are too coarse, i.e. minimum 1 second updates. I am mostly seeing the spikes or the idle time, so I am having some problem seeing the pattern of the problem. I am wondering if there are other tools I can use, that specifically can update much more often, say every 100ms. Perhaps a graphical tool that can draw a continuous graph or am I just using the mentioned tools wrong? (e.g. iostat 1, and vmstat 1) regards Tom

Tom Forsmo wrote: > Hi > > I am having some performance problems with postgres 8.2 on a raid disk > system. I have used top, iostat and vmstat, but the timing options are > too coarse, i.e. minimum 1 second updates. I am mostly seeing the spikes > or the idle time, so I am having some problem seeing the pattern of the > problem. > > I am wondering if there are other tools I can use, that specifically can > update much more often, say every 100ms. Perhaps a graphical tool that > can draw a continuous graph or am I just using the mentioned tools > wrong? (e.g. iostat 1, and vmstat 1) > > regards > > Tom Its hard to say what tools would suit when the problem is so ill defined, and, in this case, rather sparsely described.. Happy to help if more info forthcoming.. Is postgres the best engine in the first place, for example? Database tuning consists in identifying the worst offenders, and tackling them first. any swapouts indicate memory limitations, usually. Periods of high CPU usage usually represent bad code or bad indexing on the database. Periods of high I/O usage (lots of processes waiting on IO) represent a disk bottleneck, often helped by adding more cache..

Tom Forsmo wrote: > Hi > > I am having some performance problems with postgres 8.2 on a raid disk > system. I have used top, iostat and vmstat, but the timing options are > too coarse, i.e. minimum 1 second updates. I am mostly seeing the spikes > or the idle time, so I am having some problem seeing the pattern of the > problem. > > I am wondering if there are other tools I can use, that specifically can > update much more often, say every 100ms. Perhaps a graphical tool that > can draw a continuous graph or am I just using the mentioned tools > wrong? (e.g. iostat 1, and vmstat 1) > > regards > > Tom You could probably get more specific help by joining the pgsql-performance-owner@postgresql.org mailing list. If you need help or have questions about the mailing list, please contact the people who manage the list by sending a message to pgsql-performance-owner@postgresql.org -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jersey http://counter.li.org ^^-^^ 10:05:01 up 28 days, 16:11, 4 users, load average: 4.21, 4.54, 4.60

Jean-David Beyer wrote: > You could probably get more specific help by joining the > > pgsql-performance-owner@postgresql.org > > mailing list. Thanks, I am already using that list, but I thought this was more of a general linux question so I used this list instead. regards tom

On 2008-09-04, Tom Forsmo <spam@nospam.net> wrote: > Hi > > I am having some performance problems with postgres 8.2 on a raid disk > system. I have used top, iostat and vmstat, but the timing options are > too coarse, i.e. minimum 1 second updates. I am mostly seeing the spikes > or the idle time, so I am having some problem seeing the pattern of the > problem. you can update top every as often as you would like, try top -d 0.1 it is really fascinating to watch. i > I am wondering if there are other tools I can use, that specifically can > update much more often, say every 100ms. Perhaps a graphical tool that > can draw a continuous graph or am I just using the mentioned tools > wrong? (e.g. iostat 1, and vmstat 1) > > regards > > Tom -- Due to extreme spam originating from Google Groups, and their inattention to spammers, I and many others block all articles originating from Google Groups. If you want your postings to be seen by more readers you will need to find a different means of posting on Usenet. http://improve-usenet.org/

This is a multi-part message in MIME format. --------------090905090907000402040202 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Statistics output attached, please have a look at it to check that I am interpreting it correctly. The Natural Philosopher wrote: > Its hard to say what tools would suit when the problem is so ill > defined, and, in this case, rather sparsely described.. (hmm how to formulate question without it being to pg specific...) The system is running latest kubuntu in console mode as a development server. It has : - Dual Quad-Core Opteron 2352 - 8G of RAM - 1 Areca 1220 SATA 8 port RAID Controller - PCI-E, SATA II - 8 WD Raptor - 150GB SATA 1.5GB/s 10K RPM 16MB disks the disks are set up with ext3 and the following options auto,rw,async,noatime,nodiratime I am currently using four disks, two for data and two for transaction logs, set up with raid0 for each volume set. The disks are set to write-back as far as I can see. (the machine is set up this way to test db disk performance and multi threaded application design and performance, i.e there wont be serious memory demands) The problem is writing data to a single large table. Without an index the job takes 150ms, with an index it takes 4-500 ms. Each insert contains 125K rows of 9 elements, 4 integers and 5 floats, but it needs to support at least 250K rows per second and preferably 500K rows per second. The execution times suggests to me that the problem is either - db design/config problem or - a linux system/hardware problem I have checked with the pg_performance list and have made some adjustments to the pg database, it gave me perhaps 10-20% better performance. There are other options as well in pg, but I want to asses the hardware first. I looked at hardware, using iostat, vmstat and top. and here is where the problem is I am not sure I am interpreting the output properly. What I see is 1) a lot of io, but does not seem to saturate the system. 2) no big cpu or swapping problem - no swapping - some cpu usage, between 10-50% - variably high amounts of data written - typically 10-20MB/s at normal load, - but up to 120MB/s at peaks, happens every couple of minutes - no significant iowait or iocpu load I conclude one of the following (really, almost anything could be the problem, as I see it :( ) - db indexing problem - db disk usage problem, - e.g. relocate parts of db (index tablespace) to another disk - disk performance problem - either some raid or disk controller config problem - increase raid0 volume set with more disks - linux config problem - system config problem - filesystem config problem Please have a look at the vmstat, iostat output I have attached to check the numbers (BTW, I quite like postgres, maybe there is some faster db out there, but its note really an option any more. In any case postgres is quite fast, and there are otherways options in pg. But at the moment I am trying to profile each method to see which is more suitable.) regards thomas --------------090905090907000402040202 Content-Type: text/plain; name="performance_notes.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="performance_notes.txt" listings contains 1) top 2) vmstat 3) iostat : ---------------- tofi@v8:~$ top -d 1 top - 16:32:50 up 6:32, 10 users, load average: 0.96, 0.41, 0.15 Tasks: 176 total, 2 running, 174 sleeping, 0 stopped, 0 zombie Cpu0 : 6.9%us, 0.0%sy, 0.0%ni, 93.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 19.5%us, 0.0%sy, 0.0%ni, 80.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 5.9%us, 0.0%sy, 0.0%ni, 94.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu5 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 : 6.0%us, 0.0%sy, 0.0%ni, 94.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 59.8%us, 2.0%sy, 0.0%ni, 37.3%id, 1.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8186480k total, 3882152k used, 4304328k free, 229500k buffers Swap: 15623172k total, 0k used, 15623172k free, 3302004k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8738 postgres 20 0 2095m 377m 375m S 46 4.7 0:24.22 postgres: postgres perf_test_attributes 127.0.0.1(49954) idle 8734 postgres 20 0 2095m 378m 376m R 20 4.7 0:24.12 postgres: postgres perf_test_attributes 127.0.0.1(49950) COPY 8713 tofi 20 0 2898m 103m 8924 S 19 1.3 0:30.00 java -server -Xms2600M -Xmx2600M -DORBid=server -Dorg.omg.CORBA.ORBClass=org.jaco 8737 postgres 20 0 2095m 378m 376m S 10 4.7 0:24.12 postgres: postgres perf_test_attributes 127.0.0.1(49953) idle 8164 postgres 20 0 2095m 2.0g 2.0g S 2 25.4 0:31.00 postgres: writer process 8756 tofi 20 0 18988 1324 940 R 2 0.0 0:00.08 top -d 1 1 root 20 0 4016 940 656 S 0 0.0 0:01.38 /sbin/init 2 root 15 -5 0 0 0 S 0 0.0 0:00.00 [kthreadd] 3 root RT -5 0 0 0 S 0 0.0 0:00.00 [migration/0] 4 root 15 -5 0 0 0 S 0 0.0 0:00.12 [ksoftirqd/0] 5 root RT -5 0 0 0 S 0 0.0 0:00.00 [watchdog/0] 6 root RT -5 0 0 0 S 0 0.0 0:00.00 [migration/1] 7 root 15 -5 0 0 0 S 0 0.0 0:00.00 [ksoftirqd/1] 8 root RT -5 0 0 0 S 0 0.0 0:00.00 [watchdog/1] 9 root RT -5 0 0 0 S 0 0.0 0:00.00 [migration/2] 10 root 15 -5 0 0 0 S 0 0.0 0:00.00 [ksoftirqd/2] ------ tofi@v8:~$ vmstat -SM 1 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 2 0 0 3778 225 3633 0 0 3 379 34 39 4 1 94 0 1 0 0 3773 225 3638 0 0 12 10196 358 1136 14 1 85 0 1 0 0 3769 225 3642 0 0 0 30472 480 1271 11 0 88 0 1 0 0 3765 225 3646 0 0 0 9088 350 2041 11 0 88 0 1 0 0 3760 225 3651 0 0 0 9920 388 460 15 0 85 0 1 0 0 3755 225 3655 0 0 0 8776 358 747 11 0 89 0 1 0 0 3750 225 3659 0 0 12 8752 339 1621 11 1 88 0 1 0 0 3746 225 3664 0 0 0 33340 553 743 15 0 84 1 1 0 0 3742 225 3668 0 0 0 9256 320 457 10 0 89 0 1 0 0 3737 225 3672 0 0 0 8776 378 2655 11 0 88 0 1 0 0 3732 225 3676 0 0 0 9760 315 385 12 1 88 0 1 0 0 3728 225 3680 0 0 0 8756 389 2956 14 1 85 0 1 0 0 3725 225 3684 0 0 24 30516 507 584 10 1 88 1 0 0 0 3721 225 3688 0 0 0 9344 420 754 11 0 88 0 1 0 0 3716 225 3692 0 0 0 8648 322 570 12 0 88 0 1 0 0 3712 225 3696 0 0 0 8748 358 1414 12 0 88 0 1 0 0 3708 225 3701 0 0 0 9224 314 1108 12 0 88 0 1 0 0 3703 225 3705 0 0 12 31372 558 2055 12 1 87 0 1 0 0 3699 225 3709 0 0 0 8260 309 2957 13 0 86 0 1 0 0 3695 225 3713 0 0 0 9432 392 2193 10 1 90 0 1 0 0 3690 225 3718 0 0 0 10124 345 707 13 0 86 0 1 0 0 3685 225 3722 0 0 0 9788 330 708 11 0 89 0 1 0 0 3681 225 3727 0 0 0 34908 565 2740 12 0 87 1 1 0 0 3676 225 3732 0 0 12 10168 393 1080 11 1 88 0 1 0 0 3670 225 3737 0 0 0 10308 317 1957 11 0 88 0 0 0 0 3666 225 3742 0 0 0 11020 434 1890 12 1 86 0 vmstat procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 2 0 0 2053040 166456 5553568 0 0 12 31128 516 506 17 6 78 0 2 0 0 2040044 166480 5565868 0 0 12 8028 293 3639 21 5 74 0 2 0 0 2025656 166528 5580056 0 0 28 9580 363 1396 18 5 76 0 2 0 0 2011932 166548 5593076 0 0 12 5084 262 322 21 6 73 0 2 0 0 2002508 166576 5602608 0 0 8 7372 327 1901 15 6 76 4 2 0 0 1989440 166628 5614972 0 0 12 32500 515 537 19 7 74 0 2 0 0 1977160 166660 5627404 0 0 8 8220 346 2136 18 5 77 0 2 0 0 1961924 166684 5642052 0 0 12 5260 275 314 20 5 74 0 2 0 0 1948296 166732 5655160 0 0 20 10532 364 1811 19 6 75 0 2 0 0 1940092 166748 5663268 0 0 8 5196 263 795 15 5 80 0 2 0 0 1927128 166796 5676036 0 0 12 33148 550 887 21 6 72 1 2 0 0 1912596 166824 5690004 0 0 12 7788 298 897 18 7 75 0 2 0 0 1899532 166852 5702412 0 0 8 8436 345 2232 18 5 77 0 2 0 0 1886364 166880 5715024 0 0 24 9100 302 1865 21 7 72 0 2 0 0 1874888 166920 5726704 0 0 8 7604 344 641 18 4 77 0 2 0 0 1862284 166948 5738996 0 0 8 31268 491 2961 18 5 76 0 2 0 0 1847668 166992 5753592 0 0 12 10008 359 2162 18 6 76 0 2 0 0 1834472 167012 5765556 0 0 12 5100 281 355 23 7 69 1 2 0 0 1820720 167072 5777720 0 0 20 10272 396 3029 17 5 78 0 2 0 0 1805280 167100 5792400 0 0 12 5188 266 335 21 6 73 0 2 0 0 1789976 167152 5804920 0 0 8 34864 587 1323 20 6 74 0 1 1 0 1780336 167180 5814732 0 0 12 4256 278 690 18 6 76 1 2 0 0 1770172 167196 5824312 0 0 4 10140 345 1439 18 6 72 4 2 0 0 1755916 167240 5838368 0 0 12 5136 283 4219 17 6 76 0 2 0 0 1742564 167264 5850588 0 0 20 11148 363 6252 18 6 76 0 2 0 0 1730344 167312 5862496 0 0 12 30484 496 1255 17 7 76 0 2 0 0 1715716 167336 5877336 0 0 8 6228 324 436 20 6 74 0 2 0 0 1702356 167376 5889860 0 0 12 10336 315 1369 18 6 76 0 2 0 0 1689736 167396 5902064 0 0 8 8976 348 3912 19 5 76 0 2 0 0 1675040 167428 5915688 0 0 24 7888 312 2821 20 6 74 0 2 0 0 1662516 167480 5928680 0 0 12 33900 576 3083 18 6 74 1 ------ tofi@v8:~$ iostat -kx 1 Linux 2.6.24-16-generic (v8) 09/04/2008 avg-cpu: %user %nice %system %iowait %steal %idle 4.06 0.00 1.49 0.38 0.00 94.07 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.18 352.32 1.25 19.52 5.50 1609.77 155.54 1.58 76.29 0.90 1.87 sdb 0.18 86.72 0.06 16.63 0.30 1430.53 171.52 0.09 5.58 0.68 1.14 sdc 0.16 0.00 0.01 0.00 0.11 0.00 17.29 0.00 2.44 1.04 0.00 sr0 0.00 0.00 0.00 0.00 0.01 0.00 50.40 0.00 206.40 204.40 0.01 sdd 0.70 0.79 0.78 0.65 14.02 5.75 27.73 0.01 3.53 1.52 0.22 avg-cpu: %user %nice %system %iowait %steal %idle 13.15 0.00 0.14 0.14 0.00 86.58 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 130.00 0.00 127.00 0.00 9320.00 146.77 0.02 0.19 0.09 1.20 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 12.05 0.00 0.38 0.13 0.00 87.45 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 4.00 0.00 16.00 8.00 0.00 0.00 0.00 0.00 sdb 0.00 66.00 0.00 92.00 0.00 9160.00 199.13 0.02 0.22 0.13 1.20 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 4.00 0.00 4.00 0.00 32.00 16.00 0.01 2.00 2.00 0.80 avg-cpu: %user %nice %system %iowait %steal %idle 11.26 0.00 0.48 0.36 0.00 87.89 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 131.00 0.00 127.00 0.00 8752.00 137.83 0.04 0.28 0.16 2.00 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 0.00 3.00 0.00 12.00 0.00 8.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 10.54 0.00 0.34 0.34 0.00 88.79 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 5595.00 0.00 189.00 0.00 23528.00 248.97 2.17 11.49 0.17 3.20 sdb 0.00 67.00 0.00 110.00 0.00 9352.00 170.04 0.02 0.22 0.11 1.20 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 6.00 0.00 5.00 0.00 44.00 17.60 0.00 0.80 0.80 0.40 avg-cpu: %user %nice %system %iowait %steal %idle 14.26 0.00 0.30 0.60 0.00 84.83 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 9.00 0.00 85.00 0.00 10160.00 239.06 0.07 0.80 0.42 3.60 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 1.00 0.00 3.00 0.00 16.00 10.67 0.00 1.33 1.33 0.40 avg-cpu: %user %nice %system %iowait %steal %idle 11.16 0.00 0.12 0.35 0.00 88.37 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 132.00 0.00 123.00 0.00 8504.00 138.28 0.11 0.91 0.26 3.20 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 11.38 0.00 0.60 0.12 0.00 87.90 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 5.00 0.00 20.00 8.00 0.00 0.00 0.00 0.00 sdb 0.00 67.00 0.00 88.00 0.00 8968.00 203.82 0.02 0.23 0.18 1.60 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 1.00 0.00 4.00 0.00 20.00 10.00 0.01 2.00 2.00 0.80 avg-cpu: %user %nice %system %iowait %steal %idle 14.18 0.00 0.76 0.30 0.00 84.76 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 199.00 0.00 168.00 0.00 9244.00 110.05 0.10 0.62 0.17 2.80 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 4.00 0.00 3.00 0.00 28.00 18.67 0.00 0.00 0.00 0.00 qavg-cpu: %user %nice %system %iowait %steal %idle 10.56 0.00 0.56 0.56 0.00 88.31 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 5262.00 0.00 191.00 0.00 22200.00 232.46 2.34 12.27 0.21 4.00 sdb 0.00 69.00 0.00 86.00 0.00 8208.00 190.88 0.03 0.33 0.19 1.60 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 10.00 6.00 5.00 24.00 60.00 15.27 0.01 0.73 0.73 0.80 avg-cpu: %user %nice %system %iowait %steal %idle 10.83 0.00 0.46 0.34 0.00 88.37 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 128.00 0.00 125.00 0.00 8320.00 133.12 0.08 0.67 0.19 2.40 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 11.90 0.00 0.51 0.13 0.00 87.47 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 62.00 0.00 101.00 0.00 9656.00 191.21 0.02 0.16 0.08 0.80 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 1.00 0.00 3.00 0.00 16.00 10.67 0.00 1.33 1.33 0.40 --------------090905090907000402040202--

This is a multi-part message in MIME format. --------------010603000700020607020209 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Statistics output attached, please have a look at it to check that I am interpreting it correctly. The Natural Philosopher wrote: > Its hard to say what tools would suit when the problem is so ill > defined, and, in this case, rather sparsely described.. (hmm how to formulate question without it being to pg specific...) The system is running latest kubuntu in console mode as a development server. It has : - Dual Quad-Core Opteron 2352 - 8G of RAM - 1 Areca 1220 SATA 8 port RAID Controller - PCI-E, SATA II - 8 WD Raptor - 150GB SATA 1.5GB/s 10K RPM 16MB disks the disks are set up with ext3 and the following options auto,rw,async,noatime,nodiratime I am currently using four disks, two for data and two for transaction logs, set up with raid0 for each volume set. The disks are set to write-back as far as I can see. (the machine is set up this way to test db disk performance and multi threaded application design and performance, i.e there wont be serious memory demands) The problem is writing data to a single large table. Without an index the job takes 150ms, with an index it takes 4-500 ms. Each insert contains 125K rows of 9 elements, 4 integers and 5 floats, but it needs to support at least 250K rows per second and preferably 500K rows per second. The execution times suggests to me that the problem is either - db design/config problem or - a linux system/hardware problem I have checked with the pg_performance list and have made some adjustments to the pg database, it gave me perhaps 10-20% better performance. There are other options as well in pg, but I want to asses the hardware first. I looked at hardware, using iostat, vmstat and top. and here is where the problem is I am not sure I am interpreting the output properly. What I see is 1) a lot of io, but does not seem to saturate the system. 2) no big cpu or swapping problem - no swapping - some cpu usage, between 10-50% - variably high amounts of data written - typically 10-20MB/s at normal load, - but up to 120MB/s at peaks, happens every couple of minutes - no significant iowait or iocpu load I conclude one of the following (really, almost anything could be the problem, as I see it :( ) - db indexing problem - db disk usage problem, - e.g. relocate parts of db (index tablespace) to another disk - disk performance problem - either some raid or disk controller config problem - increase raid0 volume set with more disks - linux config problem - system config problem - filesystem config problem Please have a look at the vmstat, iostat output I have attached to check the numbers (BTW, I quite like postgres, maybe there is some faster db out there, but its note really an option any more. In any case postgres is quite fast, and there are otherways options in pg. But at the moment I am trying to profile each method to see which is more suitable.) regards thomas --------------010603000700020607020209 Content-Type: text/plain; name="performance_notes.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="performance_notes.txt" listings contains 1) top 2) vmstat 3) iostat : ---------------- tofi@v8:~$ top -d 1 top - 16:32:50 up 6:32, 10 users, load average: 0.96, 0.41, 0.15 Tasks: 176 total, 2 running, 174 sleeping, 0 stopped, 0 zombie Cpu0 : 6.9%us, 0.0%sy, 0.0%ni, 93.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu1 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 19.5%us, 0.0%sy, 0.0%ni, 80.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 5.9%us, 0.0%sy, 0.0%ni, 94.1%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu5 : 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 : 6.0%us, 0.0%sy, 0.0%ni, 94.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 59.8%us, 2.0%sy, 0.0%ni, 37.3%id, 1.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 8186480k total, 3882152k used, 4304328k free, 229500k buffers Swap: 15623172k total, 0k used, 15623172k free, 3302004k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8738 postgres 20 0 2095m 377m 375m S 46 4.7 0:24.22 postgres: postgres perf_test_attributes 127.0.0.1(49954) idle 8734 postgres 20 0 2095m 378m 376m R 20 4.7 0:24.12 postgres: postgres perf_test_attributes 127.0.0.1(49950) COPY 8713 tofi 20 0 2898m 103m 8924 S 19 1.3 0:30.00 java -server -Xms2600M -Xmx2600M -DORBid=server -Dorg.omg.CORBA.ORBClass=org.jaco 8737 postgres 20 0 2095m 378m 376m S 10 4.7 0:24.12 postgres: postgres perf_test_attributes 127.0.0.1(49953) idle 8164 postgres 20 0 2095m 2.0g 2.0g S 2 25.4 0:31.00 postgres: writer process 8756 tofi 20 0 18988 1324 940 R 2 0.0 0:00.08 top -d 1 1 root 20 0 4016 940 656 S 0 0.0 0:01.38 /sbin/init 2 root 15 -5 0 0 0 S 0 0.0 0:00.00 [kthreadd] 3 root RT -5 0 0 0 S 0 0.0 0:00.00 [migration/0] 4 root 15 -5 0 0 0 S 0 0.0 0:00.12 [ksoftirqd/0] 5 root RT -5 0 0 0 S 0 0.0 0:00.00 [watchdog/0] 6 root RT -5 0 0 0 S 0 0.0 0:00.00 [migration/1] 7 root 15 -5 0 0 0 S 0 0.0 0:00.00 [ksoftirqd/1] 8 root RT -5 0 0 0 S 0 0.0 0:00.00 [watchdog/1] 9 root RT -5 0 0 0 S 0 0.0 0:00.00 [migration/2] 10 root 15 -5 0 0 0 S 0 0.0 0:00.00 [ksoftirqd/2] ------ tofi@v8:~$ vmstat -SM 1 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 2 0 0 3778 225 3633 0 0 3 379 34 39 4 1 94 0 1 0 0 3773 225 3638 0 0 12 10196 358 1136 14 1 85 0 1 0 0 3769 225 3642 0 0 0 30472 480 1271 11 0 88 0 1 0 0 3765 225 3646 0 0 0 9088 350 2041 11 0 88 0 1 0 0 3760 225 3651 0 0 0 9920 388 460 15 0 85 0 1 0 0 3755 225 3655 0 0 0 8776 358 747 11 0 89 0 1 0 0 3750 225 3659 0 0 12 8752 339 1621 11 1 88 0 1 0 0 3746 225 3664 0 0 0 33340 553 743 15 0 84 1 1 0 0 3742 225 3668 0 0 0 9256 320 457 10 0 89 0 1 0 0 3737 225 3672 0 0 0 8776 378 2655 11 0 88 0 1 0 0 3732 225 3676 0 0 0 9760 315 385 12 1 88 0 1 0 0 3728 225 3680 0 0 0 8756 389 2956 14 1 85 0 1 0 0 3725 225 3684 0 0 24 30516 507 584 10 1 88 1 0 0 0 3721 225 3688 0 0 0 9344 420 754 11 0 88 0 1 0 0 3716 225 3692 0 0 0 8648 322 570 12 0 88 0 1 0 0 3712 225 3696 0 0 0 8748 358 1414 12 0 88 0 1 0 0 3708 225 3701 0 0 0 9224 314 1108 12 0 88 0 1 0 0 3703 225 3705 0 0 12 31372 558 2055 12 1 87 0 1 0 0 3699 225 3709 0 0 0 8260 309 2957 13 0 86 0 1 0 0 3695 225 3713 0 0 0 9432 392 2193 10 1 90 0 1 0 0 3690 225 3718 0 0 0 10124 345 707 13 0 86 0 1 0 0 3685 225 3722 0 0 0 9788 330 708 11 0 89 0 1 0 0 3681 225 3727 0 0 0 34908 565 2740 12 0 87 1 1 0 0 3676 225 3732 0 0 12 10168 393 1080 11 1 88 0 1 0 0 3670 225 3737 0 0 0 10308 317 1957 11 0 88 0 0 0 0 3666 225 3742 0 0 0 11020 434 1890 12 1 86 0 vmstat procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 2 0 0 2053040 166456 5553568 0 0 12 31128 516 506 17 6 78 0 2 0 0 2040044 166480 5565868 0 0 12 8028 293 3639 21 5 74 0 2 0 0 2025656 166528 5580056 0 0 28 9580 363 1396 18 5 76 0 2 0 0 2011932 166548 5593076 0 0 12 5084 262 322 21 6 73 0 2 0 0 2002508 166576 5602608 0 0 8 7372 327 1901 15 6 76 4 2 0 0 1989440 166628 5614972 0 0 12 32500 515 537 19 7 74 0 2 0 0 1977160 166660 5627404 0 0 8 8220 346 2136 18 5 77 0 2 0 0 1961924 166684 5642052 0 0 12 5260 275 314 20 5 74 0 2 0 0 1948296 166732 5655160 0 0 20 10532 364 1811 19 6 75 0 2 0 0 1940092 166748 5663268 0 0 8 5196 263 795 15 5 80 0 2 0 0 1927128 166796 5676036 0 0 12 33148 550 887 21 6 72 1 2 0 0 1912596 166824 5690004 0 0 12 7788 298 897 18 7 75 0 2 0 0 1899532 166852 5702412 0 0 8 8436 345 2232 18 5 77 0 2 0 0 1886364 166880 5715024 0 0 24 9100 302 1865 21 7 72 0 2 0 0 1874888 166920 5726704 0 0 8 7604 344 641 18 4 77 0 2 0 0 1862284 166948 5738996 0 0 8 31268 491 2961 18 5 76 0 2 0 0 1847668 166992 5753592 0 0 12 10008 359 2162 18 6 76 0 2 0 0 1834472 167012 5765556 0 0 12 5100 281 355 23 7 69 1 2 0 0 1820720 167072 5777720 0 0 20 10272 396 3029 17 5 78 0 2 0 0 1805280 167100 5792400 0 0 12 5188 266 335 21 6 73 0 2 0 0 1789976 167152 5804920 0 0 8 34864 587 1323 20 6 74 0 1 1 0 1780336 167180 5814732 0 0 12 4256 278 690 18 6 76 1 2 0 0 1770172 167196 5824312 0 0 4 10140 345 1439 18 6 72 4 2 0 0 1755916 167240 5838368 0 0 12 5136 283 4219 17 6 76 0 2 0 0 1742564 167264 5850588 0 0 20 11148 363 6252 18 6 76 0 2 0 0 1730344 167312 5862496 0 0 12 30484 496 1255 17 7 76 0 2 0 0 1715716 167336 5877336 0 0 8 6228 324 436 20 6 74 0 2 0 0 1702356 167376 5889860 0 0 12 10336 315 1369 18 6 76 0 2 0 0 1689736 167396 5902064 0 0 8 8976 348 3912 19 5 76 0 2 0 0 1675040 167428 5915688 0 0 24 7888 312 2821 20 6 74 0 2 0 0 1662516 167480 5928680 0 0 12 33900 576 3083 18 6 74 1 ------ tofi@v8:~$ iostat -kx 1 Linux 2.6.24-16-generic (v8) 09/04/2008 avg-cpu: %user %nice %system %iowait %steal %idle 4.06 0.00 1.49 0.38 0.00 94.07 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.18 352.32 1.25 19.52 5.50 1609.77 155.54 1.58 76.29 0.90 1.87 sdb 0.18 86.72 0.06 16.63 0.30 1430.53 171.52 0.09 5.58 0.68 1.14 sdc 0.16 0.00 0.01 0.00 0.11 0.00 17.29 0.00 2.44 1.04 0.00 sr0 0.00 0.00 0.00 0.00 0.01 0.00 50.40 0.00 206.40 204.40 0.01 sdd 0.70 0.79 0.78 0.65 14.02 5.75 27.73 0.01 3.53 1.52 0.22 avg-cpu: %user %nice %system %iowait %steal %idle 13.15 0.00 0.14 0.14 0.00 86.58 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 130.00 0.00 127.00 0.00 9320.00 146.77 0.02 0.19 0.09 1.20 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 12.05 0.00 0.38 0.13 0.00 87.45 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 4.00 0.00 16.00 8.00 0.00 0.00 0.00 0.00 sdb 0.00 66.00 0.00 92.00 0.00 9160.00 199.13 0.02 0.22 0.13 1.20 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 4.00 0.00 4.00 0.00 32.00 16.00 0.01 2.00 2.00 0.80 avg-cpu: %user %nice %system %iowait %steal %idle 11.26 0.00 0.48 0.36 0.00 87.89 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 131.00 0.00 127.00 0.00 8752.00 137.83 0.04 0.28 0.16 2.00 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 0.00 3.00 0.00 12.00 0.00 8.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 10.54 0.00 0.34 0.34 0.00 88.79 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 5595.00 0.00 189.00 0.00 23528.00 248.97 2.17 11.49 0.17 3.20 sdb 0.00 67.00 0.00 110.00 0.00 9352.00 170.04 0.02 0.22 0.11 1.20 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 6.00 0.00 5.00 0.00 44.00 17.60 0.00 0.80 0.80 0.40 avg-cpu: %user %nice %system %iowait %steal %idle 14.26 0.00 0.30 0.60 0.00 84.83 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 9.00 0.00 85.00 0.00 10160.00 239.06 0.07 0.80 0.42 3.60 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 1.00 0.00 3.00 0.00 16.00 10.67 0.00 1.33 1.33 0.40 avg-cpu: %user %nice %system %iowait %steal %idle 11.16 0.00 0.12 0.35 0.00 88.37 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 132.00 0.00 123.00 0.00 8504.00 138.28 0.11 0.91 0.26 3.20 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 11.38 0.00 0.60 0.12 0.00 87.90 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 5.00 0.00 20.00 8.00 0.00 0.00 0.00 0.00 sdb 0.00 67.00 0.00 88.00 0.00 8968.00 203.82 0.02 0.23 0.18 1.60 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 1.00 0.00 4.00 0.00 20.00 10.00 0.01 2.00 2.00 0.80 avg-cpu: %user %nice %system %iowait %steal %idle 14.18 0.00 0.76 0.30 0.00 84.76 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 199.00 0.00 168.00 0.00 9244.00 110.05 0.10 0.62 0.17 2.80 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 4.00 0.00 3.00 0.00 28.00 18.67 0.00 0.00 0.00 0.00 qavg-cpu: %user %nice %system %iowait %steal %idle 10.56 0.00 0.56 0.56 0.00 88.31 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 5262.00 0.00 191.00 0.00 22200.00 232.46 2.34 12.27 0.21 4.00 sdb 0.00 69.00 0.00 86.00 0.00 8208.00 190.88 0.03 0.33 0.19 1.60 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 10.00 6.00 5.00 24.00 60.00 15.27 0.01 0.73 0.73 0.80 avg-cpu: %user %nice %system %iowait %steal %idle 10.83 0.00 0.46 0.34 0.00 88.37 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 128.00 0.00 125.00 0.00 8320.00 133.12 0.08 0.67 0.19 2.40 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 11.90 0.00 0.51 0.13 0.00 87.47 Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz avgqu-sz await svctm %util sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdb 0.00 62.00 0.00 101.00 0.00 9656.00 191.21 0.02 0.16 0.08 0.80 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sr0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 sdd 0.00 1.00 0.00 3.00 0.00 16.00 10.67 0.00 1.33 1.33 0.40 --------------010603000700020607020209--

On 2008-09-04, Tom Forsmo <spam@nospam.net> wrote: > > Jean-David Beyer wrote: >> You could probably get more specific help by joining the >> >> pgsql-performance-owner@postgresql.org >> >> mailing list. > > Thanks, I am already using that list, but I thought this was more of a > general linux question so I used this list instead. ...NOPE. This is a very DEEP RDBMS specific problem. [deletia] The OS will probably be the last thing you need/want to tweak. -- How did irc manage to get so pretentious about civility ||| of discourse when it doesn't even allow for the free and / | \ open exchange of ideas? Posted Via Usenet.com Premium Usenet Newsgroup Services ---------------------------------------------------------- http://www.usenet.com

Tom Forsmo wrote: > > What > I see is > > 1) a lot of io, but does not seem to saturate the system. > 2) no big cpu or swapping problem > > - no swapping > - some cpu usage, between 10-50% > - variably high amounts of data written > - typically 10-20MB/s at normal load, > - but up to 120MB/s at peaks, happens every couple of minutes That could well be cache flushing going on. I think the most significant thing is that its data insertion that is the problem - a lot of it - rather then retrieval. Indices slow insertions, but improve retrieval. > - no significant iowait or iocpu load > > I conclude one of the following (really, almost anything could be the > problem, as I see it :( ) > > - db indexing problem > - db disk usage problem, > - e.g. relocate parts of db (index tablespace) to another disk > - disk performance problem > - either some raid or disk controller config problem > - increase raid0 volume set with more disks > - linux config problem > - system config problem > - filesystem config problem > I suspect that you may have a combination of caching issues between the db and disk subsystems. Up to a point you can trade resilience for write speed by utilising more cache. However the details of that are outside of my detailed knowledge, so I am going to step back here.

JEDIDIAH wrote: > On 2008-09-04, Tom Forsmo <spam@nospam.net> wrote: >> Jean-David Beyer wrote: >>> You could probably get more specific help by joining the >>> >>> pgsql-performance-owner@postgresql.org >>> >>> mailing list. >> Thanks, I am already using that list, but I thought this was more of a >> general linux question so I used this list instead. > > ...NOPE. This is a very DEEP RDBMS specific problem. > > [deletia] > > The OS will probably be the last thing you need/want to tweak. My original question was which tools can I use to profile the system performance, is the problem cpu, io, memory etc. These are linux questions. I do not deny that the problem is heavily related to the db. But at the same time, I want to make sure that there isnt an os specific config option I need to set or unset to improve db performance. regards thomas

Tom Forsmo wrote: > > JEDIDIAH wrote: >> On 2008-09-04, Tom Forsmo <spam@nospam.net> wrote: >>> Jean-David Beyer wrote: >>>> You could probably get more specific help by joining the >>>> >>>> pgsql-performance-owner@postgresql.org >>>> >>>> mailing list. >>> Thanks, I am already using that list, but I thought this was more of a >>> general linux question so I used this list instead. >> ...NOPE. This is a very DEEP RDBMS specific problem. >> >> [deletia] >> >> The OS will probably be the last thing you need/want to tweak. I agree. For me, I would need an OS tweak that would speed up seeks on my hard drives (see below). > > My original question was which tools can I use to profile the system > performance, is the problem cpu, io, memory etc. These are linux questions. When I run postgreSQL, and doing large inserts (e.g., two hours worth), the system is seek-limited on the hard drives. I.e., I do not keep the processors (two Intel 3.06GHz Hyperthreaded Xeons) anywhere near busy. The transfer rates are usually around 8 Megabytes/second (though I have seen them up around 65 Megabytes/second under special circumstances), but Idle time is fairly high. In my system, I have four 10,000 rpm Ultra/320 SCSI hard drives on one controller. Each hard drive has one partition, and the tables are distributed on these. The index for a table is on a different hard drive from the corresponding table. Since for bulk inserts, I usually load one table at a time, the indices are on a separate drive from the corresponding table to minimize seek contention. (BTW, I am not using RAID.) The write-ahead-log is on a separate 10,000 rpm hard drive on a separate Ultra/320 SCSI controller from the four hard drives, above. The OS and stuff are on a sixth SCSI hard drive in the same controller as the WAL. Using the iostat command tells me that the most io and seek time is to the WAL partition on the WAL drive. I have lots of memory (8 GBytes) and postgreSQL is configured to take 2 GBytes for itself, but it usually uses only about half that except when doing a CLUSTER. There is about 6 GBytes of RAM used for cache most of the time. So on my system, the big problem is the WAL. I tried various things to speed it up, by diddling shared_buffers to 252000, and so on, but none helped much. Short of getting disks with much faster seek times, I believe there is little I can do. I fun ext2 for the data disks. The biggest speedup I have managed was to group a large number (around 3000 rows) of row inserts into a single transaction. This reduced the number of COMMITs and helped a whole lot. > > I do not deny that the problem is heavily related to the db. But at the > same time, I want to make sure that there isnt an os specific config > option I need to set or unset to improve db performance. > > regards > > thomas -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jersey http://counter.li.org ^^-^^ 15:00:01 up 28 days, 21:06, 4 users, load average: 4.38, 4.56, 4.61

Jean-David Beyer wrote: This is really a pg_performance list question, but here are some quick hint. Look op the pg_performance list and search for your problems. > I have lots of memory (8 GBytes) and postgreSQL is configured to take 2 > GBytes for itself, but it usually uses only about half that except when > doing a CLUSTER. There is about 6 GBytes of RAM used for cache most of the time. > > So on my system, the big problem is the WAL. I tried various things to speed > it up, by diddling shared_buffers to 252000, and so on, but none helped > much. Short of getting disks with much faster seek times, I believe there is > little I can do. I fun ext2 for the data disks. Here is a config option you should try, it helped me alot effective_cache_size = 7000MB shared_buffers = 2000MB work_mem = 512MB maintenance_work_mem = 512MB wal_buffers = 1024kB checkpoint_completion_target = 0.9 #PG 8.3 command wal_sync_method = open_sync You can go down a little on the effective cache size if you need to, but remember pg does not automatically steal all this memory, its just an upper limit. work_mem or shared buffers are per connection limits, so if you have many concurrent connections you might need to reconsider their values. But they are important if you do large sorts etc. You should read about each config option in the documentation so you understand how they affect performance, and resource consumption. > The biggest speedup I have managed was to group a large number (around 3000 > rows) of row inserts into a single transaction. This reduced the number of > COMMITs and helped a whole lot. You should try using COPY, its up to 5 times faster, or try batched inserts. You could also do multirow insert: insert into tableA values (1,2,3) (4,5,6) (7,8,9) ... All of these techniques inserts all data in one command and you would escape the commit mess for every insert regards tom

Similiar Articles:

Disk bottleneck? - comp.unix.solaris
... outputs: This is a datawarehouse system on oracle 9 , rehat linux ... Does this an indication of a disk bottleneck ... forcedirectio while mounting the database file systems ...

Oracle 10g on HP-UX, Terrible Poor Performance!! - comp.sys.hp ...
It said "Disk bottleneck probability = 100%". I changed ... When we sent some quarries to database (It is not heavy > > load, I tested it with a Linux system on proliant ...

.nfs files won't rm - comp.unix.solaris
... the whitepaper on bigadmin titled "Storage System Bottlenecks ... unix.solaris ..... at the moment because the database ... LinuxQuestions.org > Forums > Linux Forums > Linux ...

How to diagnose performance problems in web applications? - comp ...
How about database tuning? Some, and our DBMs do ... from tools, to determine where the >bottleneck is on your system. ... the SPARC hardware, replacing it with a Linux ...

Maximum shared memory segment size - comp.unix.solaris
... what to > look for to determine where bottlenecks were in the system and ... of shared memory segment - comp.unix ... linux ... When the database server creates the required ...

Encrypted Disk - comp.unix.solaris
TrueCrypt says it has a solution for Linux. But not ... 21:46:20 -0800, al wrote: > I'm wondering if the bottleneck ... Many Thanks ... encryption database in access 2007 - comp ...

Very fast delimited record parsing with boost - comp.lang.c++ ...
... market analytics tool on HP-UX and Linux on 32 ... starters, he'd probably gain by doing raw, system ... results are reformatted and written to a database, then the bottleneck ...

Ora err: No buffer space? - comp.sys.hp.hpux
There are some differences between the systems ... exception problem when open and closed oracle database ... Tcl Expect ssh to shutdown Linux box - comp.lang.tcl ...

IBM COBOL Migration to Windows COBOL - comp.lang.cobol
You have a system that runs on the new networked ... A good DBA can identify and nullify bottlenecks very ... The second thing I recommend is using Linux. The NTFS ...

improve strlen - comp.lang.asm.x86
Virtual machines and realtime optimized systems where ... was writing code for the never-ending flavours of Linux ... that's no big deal, but if they're too slow for a database ...

Pinpointing I/O bottlenecks on Linux « Dirty Cache
Does this story sound familiar? The end users of a database application start complaining about poor system response and long running batch jobs.

BASH-DBA: Identifying System Bottlenecks (vmstat)
Oracle database real life problems and ... usage every five seconds on a Linux system: $ vmstat 5 The output of vmstat helps to determine if the performance bottleneck is ...

7/23/2012 10:01:05 AM