0

My DB have a main table that is has lots of commits on a daily basis, with about 15 indexes on this table. The table has 25M rows and the dead row count seem to exceed the number of live rows at the moment.

The auto-vacuum seems to run for over a day on this table, and doesn't seem to catch up with the new generated dead rows.

I diverted some actions that I used the DB for filtering to be done in memory, as in, instead of inserting rows and later clean them up using DB queries, I now do it in-memory in-advance.

I'm thinking of removing some indexes since they seem to get scanned to and add a significant overhead.

Any tips on how to improve the speed?

Additional details:

  • The DB has 8GB RAM, the table is around 30GB, but the indexes are big, each is around 5GB.
  • The dead row count is now around 40M while the rowcount seems to be stuck exactly the same number for a while 25M (and some change).
  • I use automatic vacuum tuned up to get triggered frequently; this is the "nightly vacuum" I mention above.

Update: It seems what I tried is working, previously I was committing lots of rows to the DB, and delete shortly after by launching a cleanup run. I changed it to filtering the data in advance and not commit data I will cleanup later, causing much less work on the DB and less dead rows.

It seems the auto-vacuum which is starting to catch up slowly, but it takes significant time since each vacuum on this table runs for almost two days.

(unfortunately since I'm on Heroku, I can't control many parameters)

  • Could you provide more details on the table DDL? How wide is the table? How many columns and what are the sizes of the columns? You said: "...and the dead row count seem to exceed the number of live rows...". By how many? Example data please. – hot2use Jul 5 '18 at 6:15
  • possilbly you need more maintenance_work_mem – Jasen Jul 5 '18 at 7:10
  • perhaps you can use SQL inheritance to put your temporary rows in a table that inherits the big table. – Jasen Jul 5 '18 at 7:12
  • 1
    A 'nightly vacuum' sound manual. Is it? If so, add 'verbose' and show is that output and please. – jjanes Jul 5 '18 at 11:40
  • @jjanes nothing manual, I use automatic vacuum tuned up to get triggered frequently. It seems Heroku default settings are pretty tame devcenter.heroku.com/articles/… – Ohad Dahan Jul 5 '18 at 11:48
0

This means you need to tune your autovacuum settings.

Auto vacuum is not the enemy

The default settings for autovacuum will trigger when the 20% rows are dead. This is not a good choice for a large OLTP system. So tune it on Number basis not the percentage.

Like, set auto vacuum when the dead tuple count reaches 10000.

autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000
autovacuum_max_workers = 4

OR

you can set this threshold on a particular table.

ALTER TABLE t SET (autovacuum_vacuum_scale_factor = 0);
ALTER TABLE t SET (autovacuum_vacuum_threshold = 10000);

In some worst cases auto vaccum will not work. Read why? The above parameters for Autovacuum for Ad-Hoc vacuum set something like,

vacuum_cost_delay = 20ms
vacuum_cost_limit = 200

vacuum_cost_page_hit = 1
vacuum_cost_page_miss = 10
vacuum_cost_page_dirty = 20

maintenance_work_mem is also a contributing for this

  • There are my settings (Heroku default devcenter.heroku.com/articles/…) I need to get make them more aggressive – Ohad Dahan Jul 5 '18 at 11:49
  • Better do a full vacuum using pg_repack. – Bhuvanesh Jul 5 '18 at 11:52
  • Full vacuum means I need to shutdown since it has a lock on the table, right? I rather not go that route unless nothing else succeed. – Ohad Dahan Jul 5 '18 at 11:54
  • No no, pg_repack is an online vacumm tool, there will not any downtime. – Bhuvanesh Jul 5 '18 at 11:58
  • I didn't find anything on pg_repack on Heroku, I don't think they supply it and I'm unsure I can install it on the DB, any idea? – Ohad Dahan Jul 5 '18 at 12:33

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.