49

I'm trying to get PostgreSQL to aggressively auto vacuum my database. I've currently configured auto vacuum as follows:

  • autovacuum_vacuum_cost_delay = 0 #Turn off cost based vacuum
  • autovacuum_vacuum_cost_limit = 10000 #Max value
  • autovacuum_vacuum_threshold = 50 #Default value
  • autovacuum_vacuum_scale_factor = 0.2 #Default value

I notice that the auto vacuum only kicks in when the database is not under load, so I get into situations where there are far more dead tuples than live tuples. See the attached screenshot for an example. One of the tables has 23 live tuples but 16845 dead tuples awaiting vacuum. That's insane!

Lots of Dead Tuples

Auto vacuum kicks in when the test run finishes and the database server is idle, which is not what I want as I would like auto vacuum to kick in whenever the number of dead tuples exceeds 20% live tuples + 50, as the database has been configured. Auto vacuum when the server is idle is useless to me, as the production server is expected to hit 1000s of updates / sec for a sustained period which is why I need auto vacuum to run even when the server is under load.

Is there anything that I'm missing? How do I force auto vacuum to run while the server is under heavy load?

Update

Could this be a locking issue? The tables in question are summary tables which are populated via an after insert trigger. These tables are locked in SHARE ROW EXCLUSIVE mode to prevent concurrent writes to the same row.

Improve this question

5 Answers 5

Reset to default
49

Eelke is almost certainly correct that your locking is blocking autovacuum. Autovacuum is designed to give way to user activity, deliberately. If those tables are locked, autovacuum cannot vacuum them.

For posterity, however, I wanted to give an example set of settings for hyper-aggressive autovacuum, since the settings you gave don't quite do it. Note that making autovacuum more aggressive is unlikely to solve your problem, however. Also note that the default autovacuum settings are based on running over 200 test runs using DBT2 seeking an optimal combination of settings, so the defaults should be assumed to be good unless you have a solid reason to think otherwise, or unless your database is significantly outside the mainstream for OLTP databases (e.g. a tiny database which gets 10K updates per second, or a 3TB data warehouse).

First, turn on logging so you can check up on whether autovacuum is doing what you think it is:

log_autovacuum_min_duration = 0

Then let's make more autovac workers and have them check tables more often:

autovacuum_max_workers = 6
autovacuum_naptime = 15s

Let's lower the thresholds for auto-vacuum and auto-analyze to trigger sooner:

autovacuum_vacuum_threshold = 25
autovacuum_vacuum_scale_factor = 0.1

autovacuum_analyze_threshold = 10
autovacuum_analyze_scale_factor = 0.05

Then let's make autovacuum less interruptable, so it completes faster, but at the cost of having a greater impact on concurrent user activity:

autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 1000

There's your full program for generically aggressive autovacuum, which might be apppropriate for a small database getting a very high rate of updates, but might have too great of an impact on concurrent user activity.

Also, note that autovacuum parameters can be adjusted per table, which is almost always a better answer for needing to adjust autovacuum's behavior.

Again, though, it's unlikely to address your real problem.

Improve this answer
0
41

Just to see which tables qualify for autovacuum at all, the following query may be used (based on http://www.postgresql.org/docs/current/static/routine-vacuuming.html). Note however, that the query does not look for table specific settings:

 SELECT psut.relname,
     to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
     to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
     to_char(pg_class.reltuples, '9G999G999G999') AS n_tup,
     to_char(psut.n_dead_tup, '9G999G999G999') AS dead_tup,
     to_char(CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
         + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
            * pg_class.reltuples), '9G999G999G999') AS av_threshold,
     CASE
         WHEN CAST(current_setting('autovacuum_vacuum_threshold') AS bigint)
             + (CAST(current_setting('autovacuum_vacuum_scale_factor') AS numeric)
                * pg_class.reltuples) < psut.n_dead_tup
         THEN '*'
         ELSE ''
     END AS expect_av
 FROM pg_stat_user_tables psut
     JOIN pg_class on psut.relid = pg_class.oid
 ORDER BY 1;
Improve this answer
0
11

Yes it is a locking issue. According to this page (non full) VACUUM needs SHARE UPDATE EXCLUSIVE access which is blocked by the lock level you are using.

Are you certain you need this lock? PostgreSQL is ACID compliant so concurrent writes are in most cases not a problem as PostgreSQL will abort one of the transactions if a serialization violation would occur.

Also you could lock rows by using SELECT FOR UPDATE to lock rows instead of the whole table.

Another alternative without locking would be using the transaction isolation level serializable. However this might impact the performance of other transactions and you should be prepared for more serialization failures.

Improve this answer
1
  • This is due to locking of the summary tables, as these are locked using SHARE ROW EXCLUSIVE MODE. Concurrent writes without the lock may succeed, but they are most certainly going to end up with wrong values. Imagine I'm maintaining a count N of rows of type X. If I concurrently insert 2 rows of type X, without locking I will end up with N + 1 in my summary table instead of N + 2. The solution I've adopted is to have a cron job that manually vacuums the summary tables in my database. It works well and seems to be the recommended approach, but it feels too much like a hack to me.
    – CadentOrange
    Jul 24, 2012 at 9:23
10

Existing "qualify for autovacuum" script is very useful, but (as correctly stated) was missing table specific options. Here is modified version of it that takes those options into account:

WITH rel_set AS
(
    SELECT
        oid,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_threshold=', 2), ',', 1)::BIGINT
        END AS rel_av_vac_threshold,
        CASE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)
            WHEN '' THEN NULL
        ELSE split_part(split_part(array_to_string(reloptions, ','), 'autovacuum_vacuum_scale_factor=', 2), ',', 1)::NUMERIC
        END AS rel_av_vac_scale_factor
    FROM pg_class
) 
SELECT
    PSUT.relname,
    to_char(PSUT.last_vacuum, 'YYYY-MM-DD HH24:MI')     AS last_vacuum,
    to_char(PSUT.last_autovacuum, 'YYYY-MM-DD HH24:MI') AS last_autovacuum,
    to_char(C.reltuples, '9G999G999G999')               AS n_tup,
    to_char(PSUT.n_dead_tup, '9G999G999G999')           AS dead_tup,
    to_char(coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples, '9G999G999G999') AS av_threshold,
    CASE
        WHEN (coalesce(RS.rel_av_vac_threshold, current_setting('autovacuum_vacuum_threshold')::BIGINT) + coalesce(RS.rel_av_vac_scale_factor, current_setting('autovacuum_vacuum_scale_factor')::NUMERIC) * C.reltuples) < PSUT.n_dead_tup
        THEN '*'
    ELSE ''
    END AS expect_av
FROM
    pg_stat_user_tables PSUT
    JOIN pg_class C
        ON PSUT.relid = C.oid
    JOIN rel_set RS
        ON PSUT.relid = RS.oid
ORDER BY C.reltuples DESC;
Improve this answer
6

Increasing the number of autovacuum processes and reducing the naptime will probably help. Here is the configuration for a PostgreSQL 9.1 that I use on a server that stores backup information and as a result gets a lot of insert activity.

http://www.postgresql.org/docs/current/static/runtime-config-autovacuum.html

autovacuum_max_workers = 6              # max number of autovacuum subprocesses
autovacuum_naptime = 10         # time between autovacuum runs
autovacuum_vacuum_cost_delay = 20ms     # default vacuum cost delay for

I will also try lowering the cost_delay to make vacuuming more aggressive.

I can also test autovacuuming by using pgbench.

http://wiki.postgresql.org/wiki/Pgbenchtesting

High contention example :

Create bench_replication database

pgbench -i -p 5433 bench_replication

Run pgbench

pgbench -U postgres -p 5432 -c 64 -j 4 -T 600 bench_replication

Check autovacuuming status

psql
>\connect bench_replicaiton
bench_replication=# select schemaname, relname, last_autovacuum from pg_stat_user_tables;
 schemaname |     relname      |        last_autovacuum        
------------+------------------+-------------------------------
 public     | pgbench_branches | 2012-07-18 18:15:34.494932+02
 public     | pgbench_history  | 
 public     | pgbench_tellers  | 2012-07-18 18:14:06.526437+02
 public     | pgbench_accounts |
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.