Re: Simple postgresql.conf wizard - Mailing list pgsql-hackers

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@gregsmith.com http://www.gregsmith.com Baltimore, MD