6

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

  • 1
    1G updated tuples on a 1M row table? You are doing some heavy updates. There are chances there is a better approach to your problem, whatever it is. – dezso Jan 3 '17 at 12:00
  • @dezso yea I think the root cause of the issue is some major updates I am doing on the table to update when users are online. I have decided to move this to a redis database, so hopefully will resolve the issues. – Andrew Cetinic Jan 3 '17 at 13:19
  • If the autovacuum is taking a long time, presumably it's throttling itself and you should set vacuum_cost_limit to something higher, but hard to say how that relates to the IOPS issue you're seeing – jberryman May 17 '18 at 14:43
4

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.

  • 2
    Installing and using those two tools is impossible on an RDS instance. – dezso Jan 3 '17 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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