Обсуждение: importance of bgwriter_percent
Hi, I have a 64 bit Linux box with 64GB RAM and 450GB HDD. I am running a benchmark on database of size 40GB using the following settings: - data=writeback - Moved wal logs to seperate partition - settings in postgresql.conf: shared_buffers = 100000 work_mem = 100000 maintenance_work_mem = 100000 max_fsm_pages = 200000 bgwriter_percent = 2 bgwriter_maxpages = 100 fsync = false wal_buffers = 64 checkpoint_segments = 2048 checkpoint_timeout = 3600 effective_cache_size = 1840000 random_page_cost = 2 geqo_threshold = 25 geqo_effort = 1 stats_start_collector = false stats_command_string = false stats_row_level = false add_missing_from = false I am not getting good performance here as I get when I am working on a small database of size 1GB with the following settings : shared_buffers = 3000 checkpoint_segments = 256 checkpoint_timeout= 1800 effective_cache_size= 250000 Rest all settings are the same as above. Here I have reduced value of some of the parameters since this database is very small and there is hardly any background data here while for the big database (size 40GB) there is lots of background data. I am getting a 4x performance improvement for small database just by setting bgwriter_percent =2 while the same setting when used against the big database is not giving much improvement. Do I need to increase the value of bgwriter_percent and/or bgwriter_maxpages or there's a problem with the other settings and I need to change one of them?? What will be a good value of bgwriter_percent for such a big database (running 4 processes in parallel here which write to the database all at once and which is the major bottleneck in my case)? Regards, Vinita Bansal Why is this happening. Do I need _________________________________________________________________ Expressions unlimited! http://server1.msn.co.in/sp04/messenger/ The all new MSN Messenger!
On Fri, 2005-04-01 at 00:36, vinita bansal wrote: > Hi, > > I have a 64 bit Linux box with 64GB RAM and 450GB HDD. I am running a > benchmark on database of size 40GB using the following settings: > - data=writeback You might want to read this post about ext3 with writeback: http://archives.postgresql.org/pgsql-general/2005-01/msg00830.php > - Moved wal logs to seperate partition > - settings in postgresql.conf: > shared_buffers = 100000 > work_mem = 100000 > maintenance_work_mem = 100000 > max_fsm_pages = 200000 > bgwriter_percent = 2 > bgwriter_maxpages = 100 > fsync = false > wal_buffers = 64 > checkpoint_segments = 2048 > checkpoint_timeout = 3600 > effective_cache_size = 1840000 > random_page_cost = 2 > geqo_threshold = 25 > geqo_effort = 1 > stats_start_collector = false > stats_command_string = false > stats_row_level = false > add_missing_from = false > > I am not getting good performance here as I get when I am working on a small > database of size 1GB with the following settings : > shared_buffers = 3000 > checkpoint_segments = 256 > checkpoint_timeout= 1800 > effective_cache_size= 250000 > Rest all settings are the same as above. What is the difference in performance on the big database if you use the settings from the small setup instead of the ones you're using now? Have you tried starting there and increasing each setting some incremental amount to gauge the increase in performance you get from the changes? Sometimes certain settings that you think will speed up the database will actually slow it down, and without some kind of empirical testing, you really don't know if the new setting is really "better" or not. I'm not familiar enough with the new bgwriter stuff yet to offer any real advice on tuning its parameters.
Hi, I have not tried all the settings on the big database but yeah I have tried most of the settings and I am not seeing any performance improvement. Rather I would say that there was a performance degradation in my case when I used some of the settings used for small database (like decreasing shared_buffers, checkpoint_segments etc). I am trying to change these parameters one by one and then see how things are getting affected. Regards, Vinita Bansal >From: Scott Marlowe <smarlowe@g2switchworks.com> >To: vinita bansal <sagivini@hotmail.com> >CC: pgsql-general@postgresql.org >Subject: Re: [GENERAL] importance of bgwriter_percent >Date: Fri, 01 Apr 2005 09:43:38 -0600 > >On Fri, 2005-04-01 at 00:36, vinita bansal wrote: > > Hi, > > > > I have a 64 bit Linux box with 64GB RAM and 450GB HDD. I am running a > > benchmark on database of size 40GB using the following settings: > > - data=writeback > >You might want to read this post about ext3 with writeback: > >http://archives.postgresql.org/pgsql-general/2005-01/msg00830.php > > > > - Moved wal logs to seperate partition > > - settings in postgresql.conf: > > shared_buffers = 100000 > > work_mem = 100000 > > maintenance_work_mem = 100000 > > max_fsm_pages = 200000 > > bgwriter_percent = 2 > > bgwriter_maxpages = 100 > > fsync = false > > wal_buffers = 64 > > checkpoint_segments = 2048 > > checkpoint_timeout = 3600 > > effective_cache_size = 1840000 > > random_page_cost = 2 > > geqo_threshold = 25 > > geqo_effort = 1 > > stats_start_collector = false > > stats_command_string = false > > stats_row_level = false > > add_missing_from = false > > > > I am not getting good performance here as I get when I am working on a >small > > database of size 1GB with the following settings : > > shared_buffers = 3000 > > checkpoint_segments = 256 > > checkpoint_timeout= 1800 > > effective_cache_size= 250000 > > Rest all settings are the same as above. > >What is the difference in performance on the big database if you use the >settings from the small setup instead of the ones you're using now? >Have you tried starting there and increasing each setting some >incremental amount to gauge the increase in performance you get from the >changes? Sometimes certain settings that you think will speed up the >database will actually slow it down, and without some kind of empirical >testing, you really don't know if the new setting is really "better" or >not. > >I'm not familiar enough with the new bgwriter stuff yet to offer any >real advice on tuning its parameters. > _________________________________________________________________ Make money with Zero Investment. http://adfarm.mediaplex.com/ad/ck/4686-26272-10936-31?ck=RegSell Start your business.