On 4 Aug 2003, Jenny Zhang wrote:

> On Mon, 2003-08-04 at 06:33, Manfred Koizar wrote:
> > | effective_cache_size           | 1000
> > 
> > With 4GB of memory this is definitely too low and *can* (note that I
> > don't say *must*) lead the planner to wrong decisions.
> > 
> I changed the default to effective_cache_size=393216 as calculated by
> Scott.  Another way to check the execution plan is to go to the results
> dir:
> http://khack.osdl.org/stp/276917/results
> There is a 'power_plan.out' file to record the execution plan.  I am
> running a test with the changed effective_cache_size, I will see how it
> affect the plan.
> > | shared_buffers                 | 15200
> > 
> > ... looks reasonable.  Did you test with other values?
> I have only one with shared_buffers=1200000 at:
> http://khack.osdl.org/stp/276847/
> The performance degraded.  

Well, that's truly huge, even for a machine with lots-o-ram.  Most tests 
find that once the shared_buffers are big enough to use more than about 25 
to 33% of RAM, they're too big, as you get little return.

> > | sort_mem                       | 524288
> > 
> > This is a bit high, IMHO, but might be ok given that DBT3 is not run
> > with many concurrent sessions (right?).
> > http://khack.osdl.org/stp/276912/results/plot/vmstat_swap.png shows
> > some swapping activity towards the end of the run which could be
> > caused by a too high sort_mem setting.
> Right, I run only 4 streams.  Setting this parameter lower caused more
> reading/writing to the pgsql/tmp.  I guess the database has to do it if
> it can not do sorting in memory. 

Note that IF your sortmem really is 1/2 gig, then you'll likely have LESS 
than 3 gigs left for OS system cache.  About how big does top show buff 
and cached to be on that box under load?  Not that it's a big deal if you 
get the effective cache size off by a little bit, it's more of a rubber 
mallet setting than a jeweler's screw driver setting.

Thanks a bunch for all the great testing.  It's a very nice tool to have 
for convincing the bosses to go with Postgresql.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to