Re: The Last Optimization

On Fri, 6 Sep 2002, Gregory Wood wrote:

> > I did already a some optimization optimization :
> >
> > max_connections = 64
> > shared_buffers = 32000
> > sort_mem = 64336
> > fsync = false
>
> Ouch... 64MB of sort_mem? It'll only take 32 sort operations to exhaust your
> memory... actually much less since your 2GB isn't likely to be used
> exclusively for sorting.
>
> I would start by pushing sort_mem back to 8192 (you might be able to get
> away with 16384, but I wouldn't go any higher). Anything else, we'll need to
> know what you're doing, schema, queries, explain analyze, etc.

FYI, in testing on my Dual PIII 750 / 512Meg mem box, and long before
memory was exhausted, performance on selects with sorts started going DOWN
after I passed 2048k of sort memory.

I've said it before, and I'll keep saying it, until I get around to
writing a new bit for the performance tuning section of the docs, that if
you're performance tuning, do so incrementally.

Think increasing shared buffers is a good idea? Then increase it some
small amount (i.e. 4000 to 8000) and re run your queries to see how much
faster they are. Keep increasing it until you hit the "knee" and then
go back to the last setting before the knee. That should be the procedure
for any performance tuning. Plus only change one thing at a time.

More than likely the problem here is no index on the tables. Run an

explain analyze YOURQUERYHERE

and let us know what it says.