Re: Simple postgresql.conf wizard

On Thu, 4 Dec 2008, Gregory Stark wrote:

> My point was more that you could have a data warehouse on a 
> non-dedicated machine, you could have a web server on a non-dedicated 
> machine, or you could have a mixed server on a non-dedicated machine.

I should just finish the documentation, where there will be a big 
disclaimer saying "THESE SETTINGS ASSUME A SERVER DEDICATED TO 
POSTGRESQL!"  That's the context here.  Why, after you follow my tuning 
instructions, you're lucky if the server will run anything but the 
database afterwards.

> Josh's logic is impeccable -- for the specific use case he's describing of a
> truly dedicated server with enough disk space for a major production database.
> But not every install is going to have gigabytes of space reserved for it and
> not every admin is going to realize that he really should set aside gigabytes
> of space even though he only expects his database to be a few megabytes.

It's really quite simple.  Josh and I don't care directly about disk space 
used by the WAL for people with trivial databases.  At all.  Whatsoever. 
Maybe once, long ago, when we were young and frugal and skinny[1]; not 
now, or probably ever again the future.  If that's your concern, maybe 
there can be some companion utility named pgmiser that lowers parameters 
back down again.  Your mascot can be some sort of animal that efficiently 
lives off small scraps of food or something.[2]

The context here is pgtune, which is aiming to make a fat elephant of a 
server faster so that there's an answer to people who say "My benchmarks 
are all running really slow, is this because my system with 16PT of RAM is 
only using 32MB of it for the database?  This sucks, I'm going back to 
Oracle which used all my RAM."  If there are people who instead think, 
"hey, I'll run this tuning utility to make my database faster, then it 
will also be a lot smaller!", maybe we can find a class about space/time 
tradeoffs in algorithm design to send them to or something.[3]

There are exactly two important things here.  The first is how large 
checkpoint_settings needs to be in order to for the considerable overhead 
of checkpoints to be bearable.  That drives the setting up.  Our super-fat 
DW application gets set to at least 64 so that when you bulk-load another 
TB of data into it, that doesn't get bottlenecked dumping gigabytes of 
dirty buffers every few seconds.  If the database crashes and recovery 
reads or writes a bunch of data, who cares about random writes because 
your SAN has a 4GB write cache on it and dozens of drives slaving away.

Driving the setting down is knowing how much time you'll have to wait for 
recovery to happen, which is really a measure of what your tolerance for 
downtime is.  We're thinking that someone who picks the Desktop tuning may 
have no tolerance for the database to be sluggish coming back up after 
Windows crashed and they rebooted, so tiny setting for them to make 
recovery super fast.

Everybody else in our sample profiles fall in the middle of those two 
extremes, which is why the values curve the way they do.  Web app? 
Probably not a lot of write volume, probably trouble if it's down a long 
time; how about 8, on the low side, but it gives checkpoints more time to 
spread out their I/O so worst-case latency isn't as bad.  That's the sort 
of analysis those numbers come from.  Do performance tuning and juggle 
these trade-offs for long enough for new people all the time, you get a 
gut feel for the right ballpark an app should start at based on its type. 
The whole idea behind this tool is that we're taking some of that hard-won 
knowledge and trying to automate the distribution of it.

> It's great that Postgres has such great documentation but whenever we have the
> chance to replace something with an option which doesn't need any
> documentation that would be even better. I'm just exploring whether that's an
> option here.

I would be glad to have a post-CommitFest discussion of this very topic as 
it's quite a pain to me in its current form.  Just not right now because 
it's too late to touch it.

> Nobody's even tried to do this side of things before. They always got 
> bogged down in trying to parse config files and such.

It's actually because most of them were working in Perl, which encourages 
deviant behavior where people delight in converting useful ideas into 
illegible punctuation rather than actually getting anything done.  Except 
for that other Greg around here who's not involved in this discussion, his 
Perl is pretty good.

[1] Josh is being aggressively bulked up right now for his next sumo 
match.

[2] Like a rat, which would give you an excuse to add the long overdue 
PL/Ratfor.

[3] This wouldn't actually help them learn anything, but it would make 
their heads explode at which point all their problems are gone.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD