Re: Simple postgresql.conf wizard

On Tue, 18 Nov 2008, Josh Berkus wrote:

> Regarding the level of default_stats_target, it sounds like people agree
> that it ought to be raised for the DW use-case, but disagree how much.
> If that's the case, what if we compromize at 50 for "mixed" and 100 for
> DW?

That's what I ended up doing. The attached version of this script and its
data files (I dumped all the useful bits in the current HEAD pg_settings
for it to use) now hits all of the initial goals I had for a useful
working tool here. Here's a sample run on a system with 8GB of RAM and
the default "mixed" workload. I told the tool absolutely nothing:

./pgtune -i ~/data/postgresql.conf

And it came up with a decent mixed starter configuration for this system
appended to the input postgresql.conf:

default_statistics_target = 50 # pg_generate_conf wizard 2008-11-30
maintenance_work_mem = 480MB # pg_generate_conf wizard 2008-11-30
constraint_exclusion = on # pg_generate_conf wizard 2008-11-30
checkpoint_completion_target = 0.9 # pg_generate_conf wizard 2008-11-30
effective_cache_size = 5632MB # pg_generate_conf wizard 2008-11-30
work_mem = 48MB # pg_generate_conf wizard 2008-11-30
wal_buffers = 8MB # pg_generate_conf wizard 2008-11-30
checkpoint_segments = 16 # pg_generate_conf wizard 2008-11-30
shared_buffers = 1920MB # pg_generate_conf wizard 2008-11-30
max_connections = 80 # pg_generate_conf wizard 2008-11-30

I do plan to make that look a bit prettier.

Loose ends
----------

-Kernel shm suggestions. The main hard part there is implementing the
formula from the documentation to figure out the total expected shared
block size that will be allocated. That just needs another block of time
to finish, will fit that in this week.

-Providing hints (to stderr) for things that fall somewhere between
changes to be made automatically and just things to put into the docs. I
want to warn about the implications of the default listen_addresses for
example.

-Documentation. I've got the whole outline sketched out and some text
written, just haven't had time yet to finish implementing that as Docbook.

-Tests on more platforms (this was developed on Linux). Memory detection
works on recent (>=2.5) version of Python for Windows now. I want to do
some more tests there, on Mac OS X, and on Solaris. Some *BSD testing
would also be in order once I'm outputting shm suggestions, I don't have
any systems to test that platform myself.

Other than the docs and testing, the rest of these are really optional
anyway. What is completely done is the tuning model itself and the inputs
it needs. I made some small tweaks to what Josh suggested, documented
below, and consider that frozen from my side except for review feedback.
I'll get everything mentioned above done next weekend, and expect to
submit something of commit candidate quality at that time.

Naming and promotion
--------------------

Now that the finish line is in sight, I thought a bit about what to name
this thing. The original idea Josh threw out was based on the idea that
this would generate the postgresql.conf file from scratch, which may still
happen eventually but is not a goal for this release. This really just a
tuning tool. When searching for possible names that might fit, one of the
ones I thought of was already suggested quite some time ago, in the
original discussion that led to the TODO item I'm trying to complete:

http://archives.postgresql.org/pgsql-advocacy/2003-02/msg00085.php

Since that was the only claim I could find on the name, I've changed the
name on this new version to "pgtune" and would propose that as its final
name in contrib.

I'd like to add some text mentioning the availability of this tool to the
sample postgresql.conf, and I'd certainly be in favor of the suggestion
from that old message that a note suggesing you run it should even show up
in the output from initdb.

Tuning model
------------

The actual tuning part of the code is exactly 83 lines right now and
pretty easy to read, the rest is mainly text and GUC-related baggage.
For anyone who wants to dig into the model itself, here are the other
changes I made from what Josh has suggested.

-Since I'm focused right now just on getting a good 8.4 version of this
tool running, I dropped any attempt at tuning the FSM parameters since
they're gone. With that change, there's no need to even ask about
database size anymore. I'll put something related to that in a future
release that also targets 8.3.

-The tool doesn't do anything memory-related if you have less than 256MB
of RAM. I wasn't sure how far down this model scaled and may revisit that
cut-off.

-I was uncomfortable turning off auto-vacuum in the DW case, for fear it
would cause trouble for a newbie admin who happens to be stuck with such a
job. Instead I'm putting a section in the documentation about the
parameters that might be turned by hand beyond what the tool does. That
can mention that further increases in default_stats_target and disabling
auto-vacuum might make sense in the DW case.

-I will add a HINT generated if listen_addresses isn't set to '*", but the
tool itself never makes that change. As I said before in this thread, I
don't want to be responsible for making a change that might not be noticed
that impacts system security.

-There was no difference in the Web vs. OLTP parameters. I added one
place they diverge: the OLTP settings now have a higher
checkpoint_segments parameter. I felt that 8 is a bit low for that
application type and increased it to 16. This makes it possible to spread
the checkpoint out much more usefully.

-I changed the model for wal_buffers to be 512KB * checkpoint_segments.
That is the same value in most cases as what was originally suggested, and
the delta in the ones that changed wasn't large.

-maint_work_mem is now capped at 1GB. My own tests and others I've seen
reported on the lists here have suggested its useful upper setting is
around that range, and I'd hate to see someone run this on a DW system
with, say, 64GB of RAM and set aside 8GB of RAM just for maint_work_mem.

-checkpoint_completion_target is raised to the maximum you can set it to
while still having completion with at least one segment of slop for
rounding. That's the logic behind why it's only at 0.5 with the default
of 3 segments. The actual underlying formula I normally use is to be
slightly more conservative than
(checkpoint_segments - 1)/checkpoint_segments, capped at 0.9.

-I ignored the "round up/down" suggestions and just round everything down
using an algorithm that makes it more likely that a larger unit (MB
instead of kB, GB instead of MB) can be used when displaying the value.

And here are some platform improvements made since my last code drop here:

-Operating system is detected, as is RAM if on an OS that the detection
works on. The only parameter you really should need to specify if all
that works is the application type. I expect a future version of this
might support overriding the OS, so that you can run the tuning tool on a
system other than the server that configuration is going to be deployed
on.

-Platform bit width is detected (Python looks at how wide a pointer is to
figure that out), and that's used to figure out whether to load a 32-bit
based set of information from pg_settings or a 64-bit one.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD