Skip site navigation (1) Skip section navigation (2)

Re: What is the most optimal config parameters to keep stable write TPS ?..

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: Dimitri <dimitrik(dot)fr(at)gmail(dot)com>
Cc: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: What is the most optimal config parameters to keep stable write TPS ?..
Date: 2009-05-12 02:15:32
Message-ID: alpine.GSO.2.01.0905112200040.29395@westnet.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 11 May 2009, Dimitri wrote:

> I've tried to reduce checkpoint timeout from 5min to 30sec - it helped, 
> throughput is more stable now, but instead of big waves I have now short 
> waves anyway..

Tuning for very tiny checkpoints all of the time is one approach here. 
The other is to push up checkpoint_segments (done in your case), 
checkpoint_timeout, and checkpoint_completion_target to as high as you 
can, in order to spread the checkpoint period over as much time as 
possible.  Reducing shared_buffers can also help in both cases, you've set 
that to an extremely high value.

http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm is a 
long discussion of just this topic, if you saw a serious change by 
adjusting checkpoint_timeout than further experimentation in this area is 
likely to help you out.

You might also want to look at the filesystem parameters you're using 
under Solaris.  ZFS in particular can cache more writes than you may 
expect, which can lead to that all getting pushed out at the very end of 
checkpoint time.  That may very well be the source of your "waves", on a 
system with 64GB of RAM for all we know *every* write you're doing between 
checkpoints is being buffered until the fsyncs at the checkpoint end. 
There were a couple of sessions at PG East last year that mentioned this 
area, I put a summary of suggestions and links to more detail at 
http://notemagnet.blogspot.com/2008/04/conference-east-08-and-solaris-notes.html

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2009-05-12 02:38:38
Subject: Re: What is the most optimal config parameters to keep stable write TPS ?..
Previous:From: Tom LaneDate: 2009-05-11 23:02:19
Subject: Re: Query planner making bad decisions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group