Re: Hard to Use WAS: Hard limit on WAL space

<div class="moz-cite-prefix">On 06/15/2013 02:16 AM, Josh Berkus wrote:<br /></div><blockquote
cite="mid:51BB5DE2.7080506@agliodbs.com"type="cite"><pre wrap="">On 06/12/2013 02:03 PM, Joshua D. Drake wrote:
 
</pre><blockquote type="cite"><pre wrap="">What concerns me is we seem to be trying to make this "easy". It isn't
supposed to be easy. This is hard stuff. Smart people built it and it
takes a smart person to run it. When did it become a bad thing to be
something that smart people need to run?
</pre></blockquote><pre wrap="">
1997, last I checked.

Our unofficial motto: "PostgreSQL: making very hard things possible, and
simple things hard."

It *is* hard.  But that's because we've *made* it hard to understand and
manage, not because the problem is inherently hard.</pre></blockquote><br /> I have to agree with all this... Pg has
someof the best docs around, a really nice SQL level interface, and some truly shocking usability outside that nice
zone.<br/><br /> Once a user steps into the "admin zone" they're confronted with a lot of settings they'll really
struggleto understand and manage.<br /><br /> I don't want this to be used as an argument not to commit early stages of
work,though. I think iterative development with exposure to real-world testing and experience is necessary when you're
gettingto the complexity of things that are now going in to Pg. It's more that "commited" != "done"; right now, once
itsusable at that power-user stage further management and improvement gets farmed out to external tools and the
usabilityof the core feature stays rather ... rough.<br /><br /> Some examples:<br /><br /> fsync=off<br />
------------<br/><br /> We have a giant foot-cannon in the config files, "fsync" with the "off" option neatly
documentedalongside all the others. No note saying "setting fsync=off is equivalent to setting
yes_you_can_eat_my_data=on".No WARNING in the logs, not that a user who'd set that without understanding it would look
atthe logs. The fsync section of <a
href="http://www.postgresql.org/docs/9.2/static/runtime-config-wal.html">http://www.postgresql.org/docs/current/static/runtime-config-wal.html</a>
isok, though it could do with a more prominent warning... but the user needs to know where to look. I've repeatedly
askedto change this - usually after yet another user comes onto -general with data loss due to not understanding
fsync=off- and haven't been able to get agreement on even a config file comment. <br /><br /> Proposed fix 9.3, config
filecomment saying "Warning, fsync=off may cause data loss, see the user manual."<br /><br /> Proposed fix 9.4+: Remove
fsync=offfrom docs. Leave the GUC enum there but have the postmaster FATAL when it sees it with a message saying
"fsync=offhas been replaced with unsafe_writes=on, please change your postgresql.conf". Add the corresponding new
GUC.<br/><br /> max_connections<br /> ------------------------<br /><br /> max_connections is another one. I see
systemswith max_connections=3000 in the wild... performing terribly, as you'd expect. Yet there's no indication (even
inthe docs) that this is often a terrible idea, and that you should really look into a connection pooler if you're
goingabove a few hundred (hardware/workload dependent). <a
href="http://www.postgresql.org/docs/9.1/static/runtime-config-connection.html">http://www.postgresql.org/docs/current/static/runtime-config-connection.html</a>
doesn'tmention it, there's no config file comment, etc.<br /><br /> Proposed fix: Comment in the config file saying
somethinglike "See the documentation before raising this above a few hundred". In the docs, a note about the perf
impactof high max_connections with a brief mention of external connection pooling and links to pgbouncer/pgpool-II,
mentionthat many app frameworks have built-in connection pools. Brief comment about there being an optimum
workload-and-hardwaredependent level of concurrency above which performance degrades. I'll be happy to write a draft
patchfor this if there's agreement on the idea.<br /><br /> vacuum/autovacuum<br /> ---------------------------<br
/><br/> autovaccum tuning. We've just had this conversation and there seems to be agreement that it needs some love,
butunlike the above two there's no easy fix and it's an ongoing process. I don't have any right to complain about it
unlessI do more to help fix it.<br /><br /> Bloat<br /> ------<br /><br /> Table bloat. Table bloat has been a major
issuewith PostgreSQL users/admins for years. Anyone care to explain to me in a simple paragraph how to find out if you
havetable or index bloat issues in your database and what to do about it? (Maybe we need "pg_catalog.pg_index_bloat"
and"pg_catalog.pg_table_bloat" views including FILLFACTOR correction?)<br /><br /> I think I'll draft up a patch to add
exactlythat.<br /><br /> Dump/restore and globals<br /> ----------------------------------<br /><br /> Dump and
restore.The standard advice I give is to do a "pg_dumpall --globals-only" followed by a "pg_dump -Fc" of each database,
sincewe don't have "pg_dumpfall -Fc". Users often seem to do single-DB dumps then find themselves having trouble
restoringthem due to missing user accounts, etc. Or they do a pg_dumpall then want to restore just one DB/table.<br
/><br/> There's also a lot of confusion around restoring dumps due to the different formats. This has improved now that
pg_restoretells the user to restore a SQL dump using psql:<br /><br /> $ pg_restore regress.sql <br /> pg_restore:
[archiver]input file appears to be a text format dump. Please use psql.<br /><br /> ... though psql still chokes
horriblyon a pg_dump -Fc file:<br /><br /> psql:regress.out:1: ERROR:  syntax error at or near "PGDMP"<br /> LINE 1:
PGDMP^A^L^A^A^A^A^A^A^A^AREVOKEALL ON SCHEMA public FROM postgres;<br />         ^<br /> psql:regress.out:2: WARNING: 
noprivileges were granted for "public"<br /> GRANT<br /><br /><br /> Proposed fix: Should we have a pg_dumpall that
producesper-database -Fc or -Fd output? Or perhaps --include-roles / --include-tablespaces options to pg_dump that
stashesa pg_dumpall --globals-only inside the -Fc archive?<br /><br /> Proposed fix : If psql sees the pseudo-command
PGDMPit should quit immediately with an error saying "This is a PostgreSQL custom format dump file and must be restored
withthe pg_restore command". (Happy to try to come up with a patch for this).<br /><br /> Proposed fix: Instead of just
tellingthe user to run psql, pg_restore should, if there are no selective restore options, propose a psql command. Or
evenjust invoke psql, though I'm hesitant about that because of issues where the psql on the PATH is one version and
theuser runs /path/to/pg_restore for another version. Or, if we built a full path using $0, the case where pg_restore
isbeing run from within the source tree so there's no psql in the same directory.<br /><br /> pg_hba.conf<br />
----------------<br/><br /> The #1 question I see on Stack Overflow has to be confusion about pg_hba.conf, mostly from
peoplewho have no idea it exists, don't understand how to configure it, etc. They can't tell the difference between
peer/ident/trust,don't understand that if you set 'md5' but don't set a password then the password will always be
wrong,etc.<br /><br /> I list this last because I think Pg's client authentication is well documented, it lacks obvious
foot-guns,and it's really not that hard. I have little sympathy for people who respond to a docs link with "I don't
havetime for that, I'm not a DBA, can you just tell me what I need to change?". Authentication and authorization isn't
simple,and attempts to make it too simple usually also make it wrong. At some point I want to think about how to make
iteasier to manage Pg's auth, but I know there are some big complexities around it because there's no DB available at
thetime pg_hba.conf checking is done on an incoming connection so not even a shared/global table may be read from. <br
/><br/> Proposed fix: None required at this time.<br /><br /><pre class="moz-signature" cols="72">-- Craig Ringer
           <a class="moz-txt-link-freetext"
href="http://www.2ndQuadrant.com/">http://www.2ndQuadrant.com/</a>PostgreSQLDevelopment, 24x7 Support, Training &
Services</pre>