On Mon, Jan 5, 2009 at 10:15 AM, Scot Kreienkamp <skre...@la-z-boy.com> wrote: > Scott, > > Would the "alter user postgres set statement_timeout=0;" be a permanent > change? I ask because our application is using that for its login to > the database. (No lectures please, I inherited the system that way. I > already read the riot act to our developers about that.) If so I'll > have to set it back after the vacuum is done.
Then you can just issue a "set statement_timeout=0" before you run vacuum / vacuum full. The update versus insert ratio isn't as important as how many rows are updated out of the total between each run of vacuum analyze. Vacuum full is definitely NOT a regular, recommended practice. I don't think the docs really say it is. But a few other people have seemed to get the same idea from the docs, so there must be some gray area I'm not seeing when I read them. Given the usage pattern you described earlier, I'd say vacuum full is definitely NOT called for, but regular vacuum should be plenty. The best thing to do is to examine how many dead tuples you've got to keep track of, and if that number keeps rising then figure out if fsm pages needs to be bumped up, and / or autovacuum needs more aggresive settings. Note that autovacuum is kind of hand cuffed on pg versions before 8.3 because it was single threaded, and one really big table could throw it behind on other more frequently updated tables getting bloated while the vacuum thread runs against that one large table. Use vacuum verbose to get an idea of how many dead tuples there are in the database, and see if they rise to a plateu, or just keep rising. For most usage patterns with autovacuum enabled, you'll see a steady rise to about 10-20% dead tuples then it should level off. > FYI, when I inherited the system it was doing nightly vacuum fulls. It > was that way for several months. If that causes bloated indexes, then > that's fairly likely a problem I have. Sounds like I should quit > running vacuum fulls altogether except maybe once or twice per year. A lot of times a pgsql doing nightly fulls is a sign of someone who started out with an old version that only supported full vacuum and applying the faulty knowledge they gained from there to the newer version which likely doesn't need it. If you do find one table that really needs full vacuums because of its usage pattern, it's best to cron up a single vacuum (regular) to run more often on it, or make autovacuum more aggresive, or, failing those two, to make a regular nightly vacuum full / cluster / reindex for that one relation. Usually cluster is a better choice, as it doesn't bloat indexes and puts the table into index order (on the index you clustered on). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
