On 6-Jan-07, at 11:32 PM, Guy Rouillier wrote:
Dave Cramer wrote:
The box has 3 GB of memory. I would think that BigDBMS would be
hurt by this more than PG. Here are the settings I've modified
in postgresql.conf:
As I said you need to set shared_buffers to at least 750MB this is
the starting point, it can actually go higher. Additionally
effective cache should be set to 2.25 G turning fsync is not a
real world situation. Additional tuning of file systems can
provide some gain, however as Craig pointed out some queries may
need to be tweaked.
Dave, thanks for the hard numbers, I'll try them. I agree turning
fsync off is not a production option. In another reply to my
original posting, Alex mentioned that BigDBMS gets an advantage
from its async IO. So simply as a test, I turned fsync off in an
attempt to open wide all the pipes.
Regarding shared_buffers=750MB, the last discussions I remember on
this subject said that anything over 10,000 (8K buffers = 80 MB)
had unproven benefits. So I'm surprised to see such a large value
suggested. I'll certainly give it a try and see what happens.
That is 25% of your available memory. This is just a starting point.
There are reports that going as high as 50% can be advantageous,
however you need to measure it yourself.
autovacuum=on
stats_row_level = on
max_connections = 10
listen_addresses = 'db01,localhost'
shared_buffers = 128MB
work_mem = 16MB
maintenance_work_mem = 64MB
temp_buffers = 32MB
max_fsm_pages = 204800
checkpoint_segments = 30
redirect_stderr = on
log_line_prefix = '%t %d'
--
Guy Rouillier
---------------------------(end of
broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
