Hi All, I ran pgbench. Here some result: -bash-3.1$ pgbench -c 50 -t 1000 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 50 number of transactions per client: 1000 number of transactions actually processed: 50000/50000 tps = 377.351354 (including connections establishing) tps = 377.788377 (excluding connections establishing)
Some vmstat samplings in the meantime: procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 0 4 92 127880 8252 3294512 0 0 458 12399 2441 14903 22 9 34 35 0 11 49 92 125336 8288 3297016 0 0 392 11071 2390 11568 17 7 51 24 0 0 2 92 124548 8304 3297764 0 0 126 8249 2291 3829 5 3 64 28 0 0 1 92 127268 7796 3295672 0 0 493 11387 2323 14221 23 9 47 21 0 0 2 92 127256 7848 3295492 0 0 501 10654 2215 14599 24 9 42 24 0 0 2 92 125772 7892 3295656 0 0 34 7541 2311 327 0 1 59 40 0 0 1 92 127188 7952 3294084 0 0 537 11039 2274 15460 23 10 43 24 0 7 4 92 123816 7996 3298620 0 0 253 8946 2284 7310 11 5 52 32 0 0 2 92 126652 8536 3294220 0 0 440 9563 2307 9036 13 6 56 25 0 0 10 92 125268 8584 3296116 0 0 426 10696 2285 11034 20 9 39 32 0 0 2 92 124168 8604 3297252 0 0 104 8385 2319 4162 3 3 40 54 0 0 8 92 123780 8648 3296456 0 0 542 11498 2298 16613 25 10 16 48 0 -bash-3.1$ pgbench -t 10000 -c 50 starting vacuum...end. transaction type: SELECT only scaling factor: 100 number of clients: 50 number of transactions per client: 10000 number of transactions actually processed: 500000/500000 tps = 8571.573651 (including connections establishing) tps = 8594.357138 (excluding connections establishing) -bash-3.1$ pgbench -t 10000 -c 50 -S starting vacuum...end. transaction type: SELECT only scaling factor: 100 number of clients: 50 number of transactions per client: 10000 number of transactions actually processed: 500000/500000 tps = 8571.573651 (including connections establishing) tps = 8594.357138 (excluding connections establishing) (next test is with scaling factor 1) -bash-3.1$ pgbench -t 20000 -c 8 -S pgbench starting vacuum...end. transaction type: SELECT only scaling factor: 1 number of clients: 8 number of transactions per client: 20000 number of transactions actually processed: 160000/160000 tps = 11695.895318 (including connections establishing) tps = 11715.603720 (excluding connections establishing) Any comment ? I can give you also some details about database usage of my application: - number of active connections: about 60 - number of idle connections: about 60 Here some number from a mine old pgfouine report: - query peak: 378 queries/s - select: 53,1%, insert 3,8%, update 2,2 %, delete 2,8 % The application is basically a web application and the db size is 37 GB. Is there a way to have the number of queries per second and the percentages of select/update/insert/delete without pgfouine ? What is the performance impact of stats_start_collector = on and stats_row_level = on (they are on since I use autovacuum) Thanks a lot for your help. ste On Wed, Jan 7, 2009 at 8:05 PM, Stefano Nichele <stefano.nich...@gmail.com>wrote: > Ok, here some information: > > OS: Centos 5.x (Linux 2.6.18-53.1.21.el5 #1 SMP Tue May 20 09:34:18 EDT > 2008 i686 i686 i386 GNU/Linux) > RAID: it's a hardware RAID controller > The disks are 9600rpm SATA drives > > (6 disk 1+0 RAID array and 2 separate disks for the OS). > > > About iostat (on sdb I have pg_xlog, on sdc I have data) > > > iostat -k > Linux 2.6.18-53.1.21.el5 (*******) 01/07/2009 > > avg-cpu: %user %nice %system %iowait %steal %idle > 17.27 0.00 5.13 45.08 0.00 32.52 > > Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn > sda 30.42 38.50 170.48 182600516 808546589 > sdb 46.16 0.23 52.10 1096693 247075617 > sdc 269.26 351.51 451.00 1667112043 2138954833 > > > > > iostat -x -k -d 2 5 > Linux 2.6.18-53.1.21.el5 (*******) 01/07/2009 > > Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz > avgqu-sz await svctm %util > sda 0.17 12.68 0.47 29.95 38.51 170.51 13.74 > 0.03 0.86 0.19 0.57 > sdb 0.01 80.11 0.05 46.11 0.23 52.01 2.26 > 0.01 0.22 0.22 1.01 > sdc 7.50 64.57 222.55 46.69 350.91 450.98 5.96 > 0.57 2.05 3.13 84.41 > > 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 196.00 1.00 117.00 4.00 1252.00 21.29 > 0.02 0.19 0.19 2.30 > sdc 1.50 66.00 277.00 66.50 3100.00 832.00 22.89 > 50.84 242.30 2.91 100.10 > > 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 264.50 0.00 176.50 0.00 1764.00 19.99 > 0.04 0.21 0.21 3.70 > sdc 3.50 108.50 291.50 76.00 3228.00 752.00 21.66 > 89.42 239.39 2.72 100.05 > > Device: rrqm/s wrqm/s r/s w/s rkB/s wkB/s avgrq-sz > avgqu-sz await svctm %util > sda 0.00 4.98 0.00 1.00 0.00 23.88 48.00 > 0.00 0.00 0.00 0.00 > sdb 0.00 23.88 0.00 9.45 0.00 133.33 28.21 > 0.00 0.21 0.21 0.20 > sdc 1.00 105.97 274.13 53.73 3297.51 612.94 23.85 > 67.99 184.58 3.04 99.55 > > 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 79.00 0.00 46.00 0.00 500.00 21.74 > 0.01 0.25 0.25 1.15 > sdc 2.50 141.00 294.00 43.50 3482.00 528.00 23.76 > 51.33 170.46 2.96 100.05 > > > vmstat in the same time: > > vmstat 2 > procs -----------memory---------- ---swap-- -----io---- --system-- > -----cpu------ > r b swpd free buff cache si so bi bo in cs us sy id > wa st > 0 27 80 126380 27304 3253016 0 0 98 55 0 1 17 5 33 > 45 0 > 0 26 80 124516 27300 3255456 0 0 3438 1724 2745 4011 11 2 8 > 78 0 > 1 25 80 124148 27276 3252548 0 0 3262 2806 3572 7007 33 11 3 > 53 0 > 1 28 80 128272 27244 3248516 0 0 2816 1006 2926 5624 12 3 12 > 73 0 > > > I will run pgbench in the next days. > > > >> Aside from all the advice here about system tuning, as a system admin I'd >> also ask is the box doing the job you need? And are you looking at the >> Postgres log (with logging of slow queries) to see that queries perform in a >> sensible time? I'd assume with the current performance figure there is an >> issue somewhere, but I've been to places where it was as simple as adding >> one index, or even modifying an index so it does what the application >> developer intended instead of what they ask for ;) >> >> > > I already checked postgres log and resolved index/slow queries issues. > Actually I have queries that sometime are really fast, and sometime go in > timeout. > But all the required indexes are there. For sure, there are space to > improve performances also in that way, but I would like also to investigate > issue from other point of views (in order to understand also how to monitor > the server). > > > Cheers and thanks a lot. > ste > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >
