Re: PostgreSQL strugling during high load

> > 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