Re: PostgreSQL 8.4.8 bringing my website down every evening

On 6/16/11, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> What do vmstat 1 and iostat -xd 1 (or equivalent for your OS) say? So
> here's a healthy not working too hard machine:
>
> procs -----------memory---------- ---swap-- -----io---- -system--
> ----cpu----
> r b swpd free buff cache si so bi bo in cs us sy id
> wa
> 4 0 0 552768 480820 121441792 0 0 1 24 0 0 8 1
> 91 0
> Note that the in and cs values are pretty reasonable (interrupts and
> context switches / sec) and idle CPU is ok. 0 wait.
>
> If those cs and in start climbing and the wait starts climbing your IO
> is getting saturated.
>
> In iostat keep an eye on %util

It shows (but I have switched pgbouncer from session
to transaction mode now and it seems to have helped -
Drupal+my scripts are running again and pg_top shows
2-3 SELECTs at the top and the rest is "idle"):

# vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
r b swpd free buff cache si so bi bo in cs us sy id wa st
3 1 0 2896644 30748 617744 0 0 70 359 350 260 61
1 37 1 0
2 0 0 2875252 30760 619516 0 0 8 140 1333 1231 46
1 53 0 0
2 0 0 2873888 30768 618160 0 0 0 332 1293 1294 59
1 40 1 0
2 0 0 2871904 30768 619920 0 0 0 16 1257 453 38
0 61 0 0
4 0 0 2872896 30768 618136 0 0 0 24 1204 574 51
1 48 0 0
2 1 0 2868308 30768 622096 0 0 0 5608 1304 603 66
1 33 0 0
2 0 0 2870628 30780 620688 0 0 0 236 1329 999 53
1 46 2 0
0 0 0 2873356 30792 616816 0 0 0 88 1247 696 13
1 85 0 0
1 0 0 2873736 30792 616708 0 0 0 80 1270 745 20
0 80 0 0
0 0 0 2873736 30792 616708 0 0 0 44 1234 535 10
1 89 0 0
0 0 0 2873800 30804 616708 0 0 0 252 1249 576 13
0 86 0 0
2 0 0 2900708 30804 616932 0 0 0 72 1340 646 21
1 79 0 0
2 0 0 2898360 30812 618908 0 0 0 160 1549 799 45
0 54 0 0
3 0 0 2895004 30812 621448 0 0 0 0 1622 829 46
2 52 0 0
2 0 0 2896068 30812 619320 0 0 0 72 1501 1115 50
0 50 0 0
3 0 0 2897804 30820 617196 0 0 0 48 1445 1044 50
1 49 0 0
1 0 0 2895820 30832 619024 0 0 0 1128 1431 826 44
0 55 0 0
3 0 0 2893292 30844 621212 0 0 0 88 1353 1379 52
1 47 0 0
2 0 0 2894424 30844 620380 0 0 0 56 1298 668 63
1 36 0 0
3 0 0 2890580 30852 624332 0 0 0 8 1251 480 60
1 39 0 0
5 0 0 2895696 30860 618688 0 0 0 80 1304 876 75
1 24 0 0
6 0 0 2890612 30876 624612 0 0 0 3636 1437 610 87
1 12 0 0
4 0 0 2891908 30900 622388 0 0 0 112 1316 960 99
1 0 0 0
3 0 0 2890544 30908 624360 0 0 0 0 1279 1174 97
0 3 0 0
2 0 0 2891908 30912 623860 0 0 0 112 1289 1319 80
1 19 0 0
2 0 0 2896868 30924 617672 0 0 0 88 1242 764 53
0 47 0 0
1 0 0 2894264 30936 619504 0 0 0 1492 1433 733 44
1 54 0 0
1 0 0 2894884 30944 616824 0 0 0 84 1556 846 39
1 60 0 0
2 0 0 2894544 30944 616784 0 0 0 24 1436 710 14
1 85 0 0
3 0 0 2871412 30944 618220 0 0 16 96 1335 1511 48
1 51 0 0
2 0 0 2867444 30944 622332 0 0 0 332 1429 546 61
1 38 0 0
3 0 0 2867196 30956 621984 0 0 0 6684 1522 1033 75
1 24 0 0

And I have RAID1, but it seems to be ok right now:

# cat /proc/mdstat
Personalities : [raid1]
md0 : active raid1 sdb1[1] sda1[0]
1023936 blocks [2/2] [UU]

md2 : active raid1 sdb5[1] sda5[0]
277728192 blocks [2/2] [UU]

md3 : active raid1 sdb6[1] sda6[0]
185151360 blocks [2/2] [UU]

md1 : active raid1 sdb3[1] sda3[0]
20479936 blocks [2/2] [UU]

unused devices: <none>

Regards
Alex