Обсуждение: 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?
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