Re: bgwriter, checkpoints, curious (seeing delays)

From: Devrim GÜNDÜZ
Subject: Re: bgwriter, checkpoints, curious (seeing delays)
Date: ,
Msg-id: 1267166541.2094.52.camel@hp-laptop2.gunduz.org
(view: Whole thread, Raw)
In response to: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue)
Responses: Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue)
Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue)
List: pgsql-performance

Tree view

bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
 Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
  Re: bgwriter, checkpoints, curious (seeing delays)  (Jochen Erwied, )
 Re: bgwriter, checkpoints, curious (seeing delays)  (Devrim GÜNDÜZ, )
  Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
   Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
    Re: bgwriter, checkpoints, curious (seeing delays)  (Greg Smith, )
   Re: bgwriter, checkpoints, curious (seeing delays)  (Devrim GÜNDÜZ, )
    Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
  Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
   Re: bgwriter, checkpoints, curious (seeing delays)  ("Jorge Montero", )
    Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
     Re: bgwriter, checkpoints, curious (seeing delays)  (Robert Haas, )
   Re: bgwriter, checkpoints, curious (seeing delays)  ("Fernando Hevia", )
 Re: bgwriter, checkpoints, curious (seeing delays)  ("Kevin Grittner", )
  Re: bgwriter, checkpoints, curious (seeing delays)  (Tory M Blue, )
   Re: bgwriter, checkpoints, curious (seeing delays)  (Ben Chobot, )

On Thu, 2010-02-25 at 22:12 -0800, Tory M Blue wrote:
> shared_buffers = 1500MB

Some people tend to increase this to 2.2GB(32-bit) or 4-6 GB (64 bit),
if needed. Please note that more shared_buffers will lead to more
pressure on bgwriter, but it also has lots of benefits, too.

> work_mem = 100MB

This is too much. Since you have 300 connections, you will probably swap
because of this setting, since each connection may use this much
work_mem. The rule of the thumb is to set this to a lower general value
(say, 1-2 MB), and set it per-query when needed.

> checkpoint_segments = 100
> checkpoint_warning = 3600s

What about checkpoint_timeout? Please note that even if
checkpoint_segments = 100, if timeout value is low (say 5 mins),
PostgreSQL will probably checkpoint every checkpoint_timeout minutes
(unless PostgreSQL creates $checkpoint_segments xlogs before
checkpoint_timeout value). Depending on your workload, this may not be
intended, and it may cause spikes -- which will lead to the issues you
complain.

I'll stop here, and suggest you read this:

http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

for details about this subject. As noted there, if you are running 8.3+,
pg_stat_bgwriter will help you to tune checkpoint & bgwriter settings.

-HTH.

--
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz

Attachment

pgsql-performance by date:

From: Richard Huxton
Date:
Subject: Re: Multiple data base on same server
From: Richard Huxton
Date:
Subject: Re: Multiple data base on same server