Postgresql – Postgres long autovacuum halting database

autovacuumawspostgresqlpostgresql-9.5

I have a fairly large table (1 million rows) and my database is stuck on an autovacuum (>30 mins) on the this table, causing the whole database to chock. Application won't even load now.

-00:37:31.137859 autovacuum: VACUUM public.users

SELECT n_tup_del, n_tup_upd FROM pg_stat_all_tables WHERE relname = 'users';

enter image description here

These are my autovacuum settings on my users table:

autovacuum_vacuum_scale_factor=0.0, 
autovacuum_vacuum_threshold=5000,
autovacuum_analyze_scale_factor=0.0,
autovacuum_analyze_threshold=5000

These suggested settings I used from Slow PostgreSQL Performance? Don't Forget to Vacuum your Database

Do I just have to wait it out? What are my options?

Update

I have upgraded to Postgres 9.5 and have also increased my RDS IOPS to 900 and the vacuum process still maxs out the IOPS and can't do anything else to the database. The process was running for at one point 1 day before the upgrade.

I also have removed the custom autovacuum settings I had, and now just using the default.

Here is an attachment of results of these queries;

SELECT * FROM pg_stat_activity;
SELECT * FROM pg_stat_database;
SELECT * FROM pg_stat_user_tables;
SELECT * FROM pg_stat_user_indexes;
SELECT * FROM pg_locks;

http://www.filedropper.com/output_5

Best Answer

VACUUM processes launched by autovacuum can be safely killed with:

SELECT pg_terminate_backend(PID_of_backend);

Actually, any client processes in Postgresql can be terminated this way. Uncommitted work by this backend will be simply discarded.

You could then re-run VACUUM manually at a low-traffic time:

VACUUM VERBOSE users;

Check whether Cost-based Vacuum Delay can help you. This would limit the amount of I/O your autovacuum process uses.

Maybe you simply hit your IOPS limit. You should be able to see the numbers on AWS interface. On standalone Linux use iostat -dtkxy 10 to measure I/O. (iostat is usually packaged in sysstat package).

Maybe the VACUUM kicks in again so often because of the aggressive settings in your config.

Related Question