Re: understanding postgres issues/bottlenecks

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.nichele@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux