Re: PostgreSQL 8.4.8 bringing my website down every evening - Mailing list pgsql-general

On 6/16/11, Scott Marlowe <scott.marlowe@gmail.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