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 [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

Attachment: pgtune-v3.tar.gz
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to