By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,358 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,358 IT Pros & Developers. It's quick & easy.

Irreversible performance drop after increasing shared mem

P: n/a
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.)
Nov 22 '05 #1
Share this Question
Share on Google+
2 Replies

P: n/a
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 <kl*****@svana.org> 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


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFAQRO+Y5Twig3Ge+YRAj+ZAKCGNB1lN/JvdrEeLT4rij2incdShwCgge4X
M2/Zpg7AXACYjdnEasdsc4I=
=Ev6a
-----END PGP SIGNATURE-----

Nov 23 '05 #2

P: n/a
Martijn van Oosterhout <kl*****@svana.org> writes:
After you changed the conf file, did you restart the postmaster? Not
just reload, since that won't affect existing sessions I beleive.


SIGHUP will cause existing sessions to adopt a new value of sort_mem
from the config file (unless it's been locally overridden, eg with SET).

However, shared_buffers can only be set at postmaster start, so changing
it in the config file will affect nothing unless you stop and restart
the postmaster.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.