Re: FW: [ADMIN] Shared_buffers and kernel parameters, tuning

On 2003-06-10 15:43:47 -0400, Tom Lane wrote:
> Vincent van Leeuwen <pgsql(dot)spam(at)vinz(dot)nl> writes:
> > Halving the cpu_tuple_cost has given a very impressive performance boost
> > (performance roughly doubled). I'm not sure why, because the plans of the
> > large queries I was checking haven't changed as far as I can see, but maybe
> > some smaller queries I didn't bother to check are using a different plan now.
>
> That's very curious; I'd expect that parameter to have only marginal
> effect in the first place (unless you make huge changes in it, of course).
> It must have changed some plan that you didn't take note of. If you can
> find it I'd be interested to know.
>

Unfortunately, we're not exactly in the best position to test a lot of things.
Our website has been running on MySQL and PHP for the last 3 years, and I've
been wanting to switch to PostgreSQL for about the last 2 years. A lot of
preparation went in to the change, but once we switched our live site to use
PostgreSQL as it's main database we were utterly dissapointed in our own
preparations. I knew our website was somewhat optimized for MySQL usage, but
looking back I am totally amazed that we were able to squeeze so much
performance out of a database that locks entire tables for every update (yes,
we used the MyISAM table format). One of the most surprising things we learned
was that MySQL was totally bottlenecking on I/O, with a large chunk of CPU
unused, and with PostgreSQL it's the other way around.

The last couple of weeks have been a nice collection of whacky antics and
performance tuning all over the place. The first week everything performed
abysmal, and another week later we're close to our original performance again.
Ofcourse, the goal is to exceed MySQL's performance by a comfortable margin,
but we're not there yet :)

So, basically, this server is pushed far harder than it should be. Average
system load is at about 4, and there are always 50-200 postgresql threads
running during daytime. A new server that will replace this one and which is
roughly 2-3 times as fast will be put live in a few weeks, and until that's
here this box will have to bear the burden on it's own.

> > Just to be absolutely sure: all *_cost parameters only influence the
> > chosen plan, right? There is absolutely nothing else influenced which
> > doesn't show up in an EXPLAIN ANALYZE, right?
>
> AFAIR, the only one of these parameters that the executor pays any
> attention to is SORT_MEM; that will determine how soon the runtime code
> starts to spill tuples to disk in sorts, hash tables, etc.
>

Current sort_mem setting is based on monitoring the pgsql_tmp directory and
concluding that sort_mem needed to be doubled to avoid swapping to disk. It's
not as if this box doesn't have enough RAM :)

But this means I'll have to look more closely at my query plans, more things
are changing than I'm noticing when I tweak various settings.

One of the hardest parts is that some queries which should use sequential
scans are using indexes and some queries which should use indexes are using
sequential scans :) We're currently using some ugly 'set enable_seqscan to
off;' hacks in a few places, until everything is tweaked right, but I hope we
can remove those as soon as possible.

Regards,

Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/