On 05/10/2011 03:01 AM, AI Rumman wrote:
I am using Postgresql 8.2.13 and I found that most of the commits and
insert or update statements are taking more than 4s in the db and the
app performance is slow for that.
My db settings are as follows;
bgwriter_all_maxpages | 300 |
bgwriter_all_percent | 15 |
bgwriter_delay | 300 | ms
bgwriter_lru_maxpages | 50 |
bgwriter_lru_percent | 10 |
Reduce bgwriter_all_maxpages to 0, definitely, and you might drop
bgwriter_lru_maxpages to 0 too. Making the background writer in
PostgreSQL 8.2 do more work as you've tried here increases the amount of
repeated I/O done by a lot, without actually getting rid of any pauses.
It wastes a lot of I/O capacity instead, making the problems you're
seeing worse.
shared_buffers
----------------
4GB
On 8.2, shared_buffers should be no more than 128MB if you want to avoid
long checkpoint pauses. You might even find best performance at the
default of 32MB.
I have 32 gb RAM and its a 4*2=8 core processors.
Any idea how to improve the performance?
There's nothing you can do here that will work better than upgrading to
8.3. See
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm for
more information. PostgreSQL 8.2 had serious problems with the sort of
pauses you're seeing back when systems had only 4GB of memory; you'll
never get rid of them on a server with 32GB of RAM on that version.
--
Greg Smith 2ndQuadrant US greg@xxxxxxxxxxxxxxx Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
