pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Mindaugas Riauba <mind@bi.lt>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: PostgreSQL strugling during high load
Date: Fri, 13 May 2005 17:45:45 +0300
Message-ID: <026b01c557ca$71c5b5c0$f20214ac@bite.lt> (raw)
References: <01cc01c557ba$a4fdb0b0$f20214ac@bite.lt>
	<23217.1115991763@sss.pgh.pa.us>
	<022901c557c5$73df1220$f20214ac@bite.lt>
	<23517.1115993561@sss.pgh.pa.us>


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




view thread (21+ messages)  latest in thread

Message-ID: <026b01c557ca$71c5b5c0$f20214ac@bite.lt>
Permalink:  ../026b01c557ca$71c5b5c0$f20214ac@bite.lt/
Also on:    postgresql.org/message-id/026b01c557ca$71c5b5c0$f20214ac@bite.lt

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mind@bi.lt, tgl@sss.pgh.pa.us
  Subject: Re: PostgreSQL strugling during high load
  In-Reply-To: <026b01c557ca$71c5b5c0$f20214ac@bite.lt>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox