Forum OpenACS Q&A: Re: Insert Performance Issues - PG 7.2.3/OpenACS 4.5/perl/dbi

Here are my suggestions:

1.  increase the WAL files setting.  I think the usual setting is 0.  I usually increase it to 3 or 4.

2.  increase the sort_mem from 512 to 2048; this means "use up to 2MB RAM for in-memory sorting before dumping the data to disk and using the slower disk-based sorting routine".  512KB is not enough.

3. Check the fsync parameter - this varies by platform.  Turning it off is less safe but faster.

4.  Increase the shared_buffers setting.

5.  Make sure you have some checkpoint_segments (number greater than 0) .

Remember to restart the server after each change and test.  If using the pg_ctl program, check the actual logfile, since it has the bug that if the server exits due to a misconfigured .conf file it will still report success.