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.
