Re: PostgreSQL 8.4.8 bringing my website down every evening

On Thu, Jun 16, 2011 at 1:27 PM, Alexander Farber
<alexander(dot)farber(at)gmail(dot)com> wrote:
> For example pg_top shows (why is everything idle?):
>
> last pid:  5215;  load avg:  0.65,  1.64,  2.13;       up 0+00:46:48
>
> 20:16:37
> 22 processes: 22 sleeping
> CPU states: 12.4% user,  0.0% nice,  0.3% system, 87.4% idle,  0.1% iowait
> Memory: 1187M used, 2737M free, 34M buffers, 611M cached
> Swap: 7812M free
>
>  PID USERNAME PRI NICE  SIZE   RES STATE   TIME   WCPU    CPU COMMAND
>  3303 postgres  16    0 1170M  137M sleep   3:29 10.92% 20.16%
> postgres: pref pref [local] idle
>  5045 postgres  18    0 1169M  168M sleep   0:49  0.00%  0.00%
> postgres: pref pref [local] idle
>  5057 postgres  16    0 1169M  168M sleep   0:37  0.00%  0.00%
> postgres: pref pref [local] idle
>  5000 postgres  16    0 1169M  168M sleep   0:34  0.00%  0.00%
> postgres: pref pref [local] idle
>  5025 postgres  18    0 1169M  150M sleep   0:31  0.00%  0.00%
> postgres: pref pref [local] idle
>  5030 postgres  16    0 1171M  152M sleep   0:29  0.00%  0.00%

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
5 0 0 556168 480820 121441664 0 0 0 468 4005 4763 11 1 88 0
2 0 0 561660 480820 121441680 0 0 0 164 4032 4555 10 1 88 0
1 0 0 555076 480820 121441680 0 0 12 300 4171 4710 5 0 94 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