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.
