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';
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?
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;