pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed8.2.13 commit is taking too much time
3+ messages / 3 participants
[nested] [flat]
* 8.2.13 commit is taking too much time
@ 2011-05-10 07:01 AI Rumman <rummandba@gmail.com>
2011-05-10 09:44 ` Re: 8.2.13 commit is taking too much time tv@fuzzy.cz
2011-05-10 15:55 ` Re: 8.2.13 commit is taking too much time Greg Smith <greg@2ndQuadrant.com>
0 siblings, 2 replies; 3+ messages in thread
From: AI Rumman @ 2011-05-10 07:01 UTC (permalink / raw)
To: pgsql-performance
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 8.2.13 commit is taking too much time
2011-05-10 07:01 8.2.13 commit is taking too much time AI Rumman <rummandba@gmail.com>
@ 2011-05-10 09:44 ` tv@fuzzy.cz
1 sibling, 0 replies; 3+ messages in thread
From: tv@fuzzy.cz @ 2011-05-10 09:44 UTC (permalink / raw)
To: AI Rumman <rummandba@gmail.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: 8.2.13 commit is taking too much time
2011-05-10 07:01 8.2.13 commit is taking too much time AI Rumman <rummandba@gmail.com>
@ 2011-05-10 15:55 ` Greg Smith <greg@2ndQuadrant.com>
1 sibling, 0 replies; 3+ messages in thread
From: Greg Smith @ 2011-05-10 15:55 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-05-10 15:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-10 07:01 8.2.13 commit is taking too much time AI Rumman <rummandba@gmail.com>
2011-05-10 09:44 ` tv@fuzzy.cz
2011-05-10 15:55 ` Greg Smith <greg@2ndQuadrant.com>
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