RFE: postgresql add shared memory parameters to /etc/sysctl.conf

[decibel (Jim Nasby)]

With the default parameters, postgresql will only operate with a bare minimum of buffers and sort memory. A quick google returns a page that suggests modifying /etc/rc, which is a really bad idea.

Maybe the best way to handle this is to add appropriate commented out sysctl lines to /etc/sysctl.conf, and message the user that they should probably uncomment them. Then again, I don't know of any downside to upping the parameters, so maybe it should just make the change.

BTW, I think if this is done an the machine is rebooted before initdb is run, much better default values should be chosen.

[mww@…]

assign to maintainer

[mww@…]

what OS are you on? FreeBSD or Darwin?

in principle we try to mess the least possible with the OS itself, so editing /etc/sysctl.conf (FreeBSD?) is nothing I want this port to do. We could add some post-install messages (for FreeBSD?).

[decibel (Jim Nasby)]

OS X.

Actually, it should be safe to change /etc/sysctl.conf on FreeBSD, since that is the approved place to make such changes. There's no /usr/local version that I know of either.

For OS X, /etc/sysctl.conf also appears to be the cannonical place to make such changes. From /etc/sysctl-macosxserver.conf:

## # /etc/sysctl-macosxserver.conf is reserved for Apple's use. # # If you wish to override these or any other sysctl settings, create /etc/sysctl.conf # and put your settings there, not here. # # The /etc/rc script first processes /etc/sysctl-macosxserver.conf, then /etc/sysctl.conf # # Refer to man sysctl for additional information

[danielluke (Daniel J. Luke)]

Note that /etc/sysctl.conf doesn't appear to be consulted on 10.2 (darwin 6) but only on 10.3 (darwin 7).

I'm not sure we need to add shared memory setting changes to the port, the people who need the extra performance from their postgres installations are likely going to want to tweak the settings themselves anyway.

just my 2 cents.

[toby@…]

This is something that the user should tweak - *not* the port.

[decibel (Jim Nasby)]

This is something that severly limits the capabilities of postgresql as installed. What's wrong with adding commented out entries and displaying text to the user that they should consider uncommenting them?

[danielluke (Daniel J. Luke)]

Instead of attempting to mess with /etc/sysctl.conf, an appropriate message could be printed with a suggestion of what to add there.

It would be nice for the port to be able to just make everything good, but I don't think it should be changing shared memory parameters on the user's machine.

[mww@…]

ok, to bring this one to an end, would someone please just tell me what message to add to the postgresql Portfile?

As I dont have access to a Panther server box: does a /etc/sysctl-macosxserver.conf affect an ordinary Panther system, too? Does this files just work like setting some kernel stuff with sysctl?

[decibel (Jim Nasby)]

By the way, it appears that setting shmmax in /etc/sysctl.conf is broken. It gets over-ridden by /etc/rc. I've provided Apple feedback about this, but they haven't fixed it yet.

[mww@…]

o.k. - added a post-install message to the postgresql8+server install about tweaking via /etc/sysctl.conf

[decibel (Jim Nasby)]

BTW, just for the record, /etc/sysctl.conf isn't broken; the issue is that all 5 shared memory parameters must be set at the same time. I've added a comment about this to ​http://www.postgresql.org/docs/8.1/interactive/kernel-resources.html, as well as submitting a docs patch to PostgreSQL.

It would be best if the comment in the port addressed this. I'll attach a patch.

[decibel (Jim Nasby)]

Documentation patch