Posts tagged ‘autovacuum’

20 June, 2016

RDS: log vacuum messages

by gorthx

Last year I commented that “autovacuum log messages are still missing in [RDS] 9.4“.

Amazon fixed that for versions 9.4.5 and up with the custom GUC rds.force_autovacuum_logging_level.

I discovered this week that it’s also available on earlier versions of RDS – I have it enabled on a 9.3.10 instance. The default value in the config is one of the debug levels; just change it to ‘log’. No restart required (assuming you already have logging configured… and you do, don’t you?)

Works as expected:

2016-06-16 18:36:41 UTC::@:[31403]:LOG: automatic vacuum of table "my_db.public.my_dimension": index scans: 1
 pages: 0 removed, 29457 remain
 tuples: 3454 removed, 429481 remain, 0 are dead but not yet removable
 buffer usage: 64215 hits, 8056 misses, 22588 dirtied
 avg read rate: 1.018 MB/s, avg write rate: 2.855 MB/s
 system usage: CPU 0.10s/0.88u sec elapsed 61.80 sec

This makes me very happy – no more cron job to monitor vacuums!

15 December, 2014

Simple test for autovacuum log messages

by gorthx

I had reason recently to suspect that autovacuum jobs weren’t being properly logged on my RDS instance. Instead of compulsively re-running “SELECT relname, last_autovacuum FROM pg_stat_user_tables” while I waited for one to happen, I set up this quick test:

connect to database and do the following:

-- verify settings
-- log_autovacuum_min_duration should be 0 ("log all") for the purposes of this test
SHOW log_autovacuum_min_duration;
-- vac messages are LOG level messages, so the default value 'warning' should suffice here
SHOW log_min_messages;

-- set up a table with a really low vac threshold
(id serial primary key
, name text)
WITH (autovacuum_vacuum_threshold = 2, autovacuum_vacuum_scale_factor = 0)

-- add some data
INSERT INTO vac_test(name)

-- check the stats/vac jobs
SELECT * FROM pg_stat_user_tables WHERE relname = 'vac_test';

-- remove enough data to trigger an autovac
DELETE FROM vac_test WHERE id < 4;

-- check stats/vac jobs again
SELECT * FROM pg_stat_user_tables WHERE relname = 'vac_test';
-- assumes 9.3+
-- wait until you see an autovacuum job in the table
-- it'll help if you have autovacuum_naptime set to something short

Then go check the logs. You should have a helpful message about an automatic vacuum:

2014-12-07 21:01:32 PST LOG: automatic vacuum of table "postgres.public.vac_test": index scans: 1
pages: 0 removed, 1 remain
tuples: 3 removed, 6 remain
buffer usage: 60 hits, 4 misses, 4 dirtied
avg read rate: 33.422 MB/s, avg write rate: 33.422 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec

If you don’t, well, that’s the $64,000 question, isn’t it.

6 June, 2014

autovacuum: long naps aren’t better

by gorthx

There’s that saying about “the first time’s an accident, the second’s a coincidence, the third is a pattern”. It’s probably because I’ve been studying Postgres’s autovacuum feature so much lately and these things stand out to me now, but I’ve noticed a really intriguing pattern (n>5) over the past month or so: folks with their autovacuum_naptime set too dang high.

The autovacuum_naptime GUC is the amount of time the daemon waits between checking for tables that need vacuuming and analyzing (or have xids that need freezing). If you set this to, for example, one day1, each table in your database gets checked only once a day. It’s certainly possible that this will work ok for your use case. What tends to happen, though, is that you end up with several tables that need maintenance, but the long naptime doesn’t allow that to happen in a timely fashion. And of course, the longer you go between vaccing/analyzing/freezing them, the longer it takes to get that job done and soon you’re in a downward spiral, and maybe your database ends up taking a dirt nap, at least from the viewpoint of your users who are all saying “OMG, why is this so sloooooow?” Then you conclude “autovacuum sucks” and disable it and try to stay on top of it with cron jobs etc like back in the Bad Old Days When We Didn’t Have Nice Things.

So, while the autovac config params are all interdependent to some extent2, this is kind of a big one and IME you don’t want to increase the naptime beyond the already-conservative default of 1 minute.

1 – Yes, really. Not trying to embarrass anyone here! Rest assured you are not alone.
2 – I was going to say that individual GUCs don’t operate in a vacuum, but figured that might be taking things Just Too Far.