Re: Irreversible performance drop after increasing shared mem

On Tue, Feb 24, 2004 at 10:40:30AM -0800, jake johnson wrote:
> I'm running PG 7.4.1 on FreeBSD 5.2.1_RC1 and experienced a general
> 40% drop in performance after increasing Shared mem buffers to 2000
> (from the 1000 default setting) and the Sort Mem to 1024 from 16. 
> After changing the .conf file back to the original values, performance
> didn't change back.  Only after dropping the database and reloading
> from dump, did performance return.  Is this unusual behavior in
> anyone's opinion?  (Note that I didn't forget to do a pg_ctl reload to
> have postmaster re-read the .conf file.)

Firstly, Sort Mem to 16 is obviously silly. You want this system to be
able to perform sorts without swapping to disk, no? The defaults are so
conservative I don't think you'd ever want to be reducing them.

After you changed the conf file, did you restart the postmaster? Not
just reload, since that won't affect existing sessions I beleive.

And it's not due to anything else you did, like forgetting to VACUUM or
ANALYZE after a large load or update.

Hope this helps,
-- 
Martijn van Oosterhout      http://svana.org/kleptog/
> If the Catholic church can survive the printing press, science fiction
> will certainly weather the advent of bookwarez.
>    http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow