Re: PostgreSQL strugling during high load - Mailing list pgsql-performance

> >   It looks like that not only vacuum causes our problems. vacuum_cost
> > seems to lower vacuum impact but we are still noticing slow queries
"storm".
> > We are logging queries that takes >2000ms to process.
> >   And there is quiet periods and then suddenly 30+ slow queries appears
in
> > log within the same second. What else could cause such behaviour?
>
> Checkpoints?  You should ensure that the checkpoint settings are such
> that checkpoints don't happen too often (certainly not oftener than
> every five minutes or so), and make sure the bgwriter is configured
> to dribble out dirty pages at a reasonable rate, so that the next
> checkpoint doesn't have a whole load of stuff to write.

  bgwriter settings are default. bgwriter_delay=200, bgwriter_maxpages=100,
bgwriter_percent=1. checkpoint_segments=8, checkpoint_timeout=300,
checkpoint_warning=30.

  But there's no checkpoint warnings in serverlog. And btw we are running
with fsync=off (yes I know the consequences).

  Database from the viewpoint of disk is practically write only since amount
of data is smaller than memory available. I also added some 'vmstat 1'
output.

  How to get more even load. As you see neither disk nor cpu looks too busy.

  Thanks,

  Mindaugas

   procs                      memory    swap          io     system
cpu
 r  b  w   swpd   free   buff  cache  si  so    bi    bo   in    cs  us  sy
id
 1  0  0 194724  12140  10220 1045356   0   1    33    24   60    20  13   3
83
 2  0  0 194724  11988  10228 1045464   0   0    12     0 1147  6107  13   4
82
 0  2  0 194724  12172  10284 1046076   0   0   244 20692 2067  3117   8   8
84
 1  0  0 194724  12164  10280 1045912   0   0     0     4  876  8831  15  11
74
 3  0  0 194724  11704  10328 1045952   0   0    24  2116  928  5122  13  12
75
 1  0  0 194724  11444  10236 1046264   0   0   340     0 1048  6538  19  10
71
 1  0  0 194724  11924  10236 1045816   0   0     0     0  885  7616  14  20
66
 0  0  0 194724  11408  10252 1044824   0   0    28  5488  959  4749  11  14
75
 1  0  0 194724  11736  10296 1042992   0   0   460  2868 1001  4116  12  12
75
 0  0  0 194724  12024  10296 1043064   0   0    36     0  903  5081  13  12
76
 1  0  0 194724  12404  10240 1043440   0   0   280     0  899  4246  12  12
75
 1  0  0 194724  13128  10236 1043472   0   0     0     0 1016  5394  12  10
78
 0  4  0 194724  13064  10244 1043652   0   0     0 14736 1882  9290  10  15
74
 0  4  0 194724  13056  10252 1043660   0   0     0  6012 1355  2378   2   3
95
12 21  0 194724  13140  10220 1043640   0   0     8     4  723  2984   5   3
92
 1  0  0 194724  13712  10228 1043956   0   0   200     0 1144 10310  30  21
50
 0  0  0 194724  13100  10220 1043992   0   0     4     0  840  4676  15  14
71
 0  0  0 194724  13048  10296 1041212   0   0     4  6132  918  4074  10  10
80
 0  0  0 194724  12688  10324 1041508   0   0   240  1864  849  3873  12  11
77
 2  0  0 194724  12544  10240 1041944   0   0    32     0 1171  4844  14   7
78
 1  0  0 194724  12384  10232 1041756   0   0     4     0  973  6063  16   9
75
 1  0  0 194724  12904  10244 1042116   0   0   264  6052 1049  4762  15  14
71
 0  0  0 194724  12616  10236 1042164   0   0     8     0  883  4748  13   8
79
 2  0  0 194724  12576  10288 1042460   0   0   252  3136  857  3929  13  15
73
 2  0  0 194724  12156  10284 1042504   0   0     0     0  858  8832  13   6
81
 2  0  0 194724  12024  10284 1042556   0   0     0     0  834  4229  16  10
74
 3  1  0 194724  12024  10364 1043096   0   0   316 10328 1024  5686  14   7
80
 0  5  0 194724  12024  10352 1043116   0   0     4  7996 2156  2816   4   5
90
 0  4  0 194724  12024  10360 1043124   0   0     4  8560 1369  2700   6   5
90
 3  0  0 194724  12024  10264 1043124   0   0     0     4 1037  5132  14  15
71
 1  1  0 194724  11876  10264 1043176   0   0     4     0  932  7761  20  20
6