Dave Cramer wrote:
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.
Ok, I ran with the settings below, but with
shared_buffers=768MB
effective_cache_size=2048MB
fsync=on
This run took 29000 seconds. I'm beginning to think configuration
changes are not going to buy significant additional improvement. Time
to look at the app implementation.
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
--
Guy Rouillier
