Thread: 8.2.13 commit is taking too much time

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      |
 
SHOW checkpoint_segments ;
 checkpoint_segments
---------------------
 300
(1 row)

 show work_mem ;
 work_mem
----------
 16MB
(1 row)

 show checkpoint_timeout ;
 checkpoint_timeout
--------------------
 5min
(1 row)

 show checkpoint_warning ;
 checkpoint_warning
--------------------
 30s
(1 row)

show shared_buffers ;
 shared_buffers
----------------
 4GB
(1 row)


I have 32 gb RAM and its a 4*2=8 core processors.
Any idea how to improve the performance?

> Any idea how to improve the performance?

Hmmm, I guess we'll need more info about resource usage (CPU, I/O, locks)
used when the commit happens. Run these two commands

$ iostat -x 1
$ vmstat 1

and then execute the commit. See what's causing problems. Is the drive
utilization close to 100%? You've problems with disks (I'd bet this is the
cause). Etc.

There's a very nice chapter about this in Greg's book.

BTW what filesystem are you using? Ext3, ext4, reiserfs, xfs? I do
remember there were some problems with sync, that some filesystems are
unable to sync individual files and always sync everything (which is going
to suck if you want to sync just the WAL).

regards
Tomas

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@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books