Selva manickaraja <mavles78@xxxxxxxxx> wrote: > Since the production database is running, I plan to do now is > this > > 1. Set archive_timeout = 20m (Does the change require db restart > to take effect?) You'd better make that '20min'. Reload should suffice. > 2. Set autovacuum=on and track_count=on (Does the change require > db restart to take effect?) Reload should suffice. > Does that mean we are running autovacuum? Autovacuum is always there to prevent transaction ID wraparound, but you really should be using it with at least the default aggressiveness in almost all environments. > 3. Run VACUUM FREEZE ANALYZE since bulk loading was done earlier. > (Can this be done while the db is active and on production?) It can be done while the server is running, but be prepared for a massive WAL volume and a likely-noticeable performance impact. We usually do our bulk loads with archiving off and run VACUUM FREEZE ANALYZE before turning archiving on and letting the users in. Otherwise, since everything in a bulk load will have the same xmin, or very close to that, autovacuum can kick in and tank performance and glut the WAL streaming for a long time at some unpredictable moment -- although that moment is most likely to be in the midst of high transaction volume. > Is this a workable action to achieve the result required? Those are all sound like good ideas, given your situation. As mentioned elsewhere on the thread, WAL file compression can help quite a bit. That's especially true if you filter the WAL through pg_clearxlogtail or pg_lesslog before compression. The issue there is that WAL files are reused without clearing the tail of the file, so if you don't use one of these programs before compression a file which is switched out based on time, before it's full, will probably take just as much space as a full file. With pg_clearxlogtail an "empty" WAL file will gzip to 16KB (three orders of magnitude better than uncompressed). I haven't used pg_lesslog but it might do even better. -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
