PostgreSQL – Autovacuum Daemon not working

PostgreSQL – Autovacuum Daemon not working

I have configured the Autovacuum process. However, it does not seem to be working as intended.

Autovacuum Configuration

postgres=# show autovacuum;
 autovacuum
------------
 on
postgres=# show track_counts;
 track_counts
--------------
 on

According to the documentation, only 2 parameters are required to enable the autovacuum process.

Verifying that Autovacuum is not working as intended
Step 1:

# grep autovacuum /var/log/messages

Step 2:

# ps -aef | grep autovacuum
postgres 4923 4883 0 Jul20 ? 00:00:15 postgres: autovacuum launcher process
root 52995 5313 0 10:12 pts/0 00:00:00 grep autovacuum

Step 3:

select schemaname, relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze, vacuum_count, autovacuum_count, analyze_count, autoanalyze_count from pg_stat_user_tables;

All the values were 0 for the tables in every single database.

There’s something weird here. Autovacuum is configured correctly and the process is running. However, the autovacuum job did not happen even once.

Troubleshooting Process

Step 1: Verify autovacuum settings.

psql> SELECT name, setting || unit AS setting FROM pg_settings WHERE category = 'Autovacuum';
 name | setting
---------------------------------+-----------
 autovacuum |
 autovacuum_analyze_scale_factor |
 autovacuum_analyze_threshold | 50
 autovacuum_freeze_max_age | 200000000
 autovacuum_max_workers | 3
 autovacuum_naptime | 60s
 autovacuum_vacuum_cost_delay | 20ms
 autovacuum_vacuum_cost_limit | -1
 autovacuum_vacuum_scale_factor |
 autovacuum_vacuum_threshold | 50
(10 rows)

The threshold for Vacuum and Analyze was 50 rows. Could it be that the DML on the databases be lesser than 50 so far?

Step 2: Create test table for deletion

psql> create table test as select * from emp;
SELECT 12495
psql> select count(*) from test;
 count
-------
 12495
(1 row)

Step 3: Verify autovacuum statistics on test table

psql> select schemaname,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count from pg_stat_user_tables where relname='test';
-[ RECORD 1 ]-----+------------------------------
schemaname | public
relname | test
last_vacuum |
last_autovacuum |
last_analyze |
last_autoanalyze | 2015-07-22 12:02:23.931391+08
vacuum_count | 0
autovacuum_count | 0
analyze_count | 0
autoanalyze_count | 1

Step 4: Delete 5000 rows from test table

psql> delete from test where fingerprint in (select fingerprint from test order by updatetime limit 5000);
DELETE 5000

Step 5: Verify that the autovacuum statistics have changed.

psql> select schemaname,relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze,vacuum_count,autovacuum_count,analyze_count,autoanalyze_count from pg_stat_user_tables where relname='test';
-[ RECORD 1 ]-----+------------------------------
schemaname | public
relname | test
last_vacuum |
last_autovacuum | 2015-07-22 12:06:23.329375+08
last_analyze |
last_autoanalyze | 2015-07-22 12:06:24.109004+08
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 2

Summary

Autovacuum process is definitely working. The reason that it was not scheduling earlier was due to to low activity of the databases. This is proven by the autoanalyze_count counter which was increased from 1 to 2 after 5000 records was deleted.

Cheers,
Wei Shan

Advertisements

2 thoughts on “PostgreSQL – Autovacuum Daemon not working”

    1. Basically, the DML on the tables were below the vacuum threshold that’s why the daemon was not triggered.

      In this case;
      vacuum threshold = vacuum base threshold(50) + vacuum scale factor(0.2) * number of tuples(12495)
      vacuum threshold = 2,549

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s