0

I have an issue with autovacuum - I have several big tables (over 10 million records) which are updated very frequently (500 updates per second). Everything works OK until bloat happen :)

As I can see, some of the tables are not autovacuumed for more than 10 minutes, and in that period, a lot of dead tuples are generated which then kill performance.

So, my question is - what is the flow of autovacuum process? E.g. If I have 10 tables that should be vacuumed (autovacuum threshold is reached on those tables), will Postgres vacuum tables one-by-one, or will it vacuum all tables in parallel? I couldn't find that info in official documentation. Another question - Postgres will spawn single autovacuum worker per database? If I have only one database, then I do not have to change "autovacuum_max_workers" parameter?

I have changed the following autovacuum config parameters:

  • autovacuum_vacuum_cost_limit = 1000
  • autovacuum_max_workers = 6
  • autovacuum_vacuum_scale_factor = 0.01
  • maintenance_work_mem = 512MB

Beside that, I have configured autovacuum threshold on big tables (because 1% od 10 million is 100k):

  • ALTER TABLE mytable SET (autovacuum_vacuum_threshold = 15000);
  • ALTER TABLE mytable SET (autovacuum_vacuum_scale_factor = 0);
Improve this question
4
  • 500 updates per second on a 10,000,000 row table is only 3% turnover in 10 minutes (assuming single row updates). That should not constitute bloat. What are you seeing that indicates bloat? When things stop "working OK", what is it that is happening? What specific queries are having their performance drop, and what is the execution plan for those queries?
    – jjanes
    Mar 14, 2019 at 15:36
  • Example - when "big table" has more than 150k dead tuples, then query which analyses something on them become 10x slower compared with the duration when there are less dead tuples. And then query starts to become slower and slower, and in that time, the number of dead tuples is constantly increasing (because transactions lasts longer and dead tuples cannot be removed until transaction commits).
    – elBastarde
    Mar 20, 2019 at 15:38
  • There may be workarounds for that other than frantically vacuuming the table, but it requires seeing the query and the execution plan. Also, what version of PostgreSQL?
    – jjanes
    Mar 20, 2019 at 15:51
  • I am using Postgres 11.2
    – elBastarde
    Mar 22, 2019 at 10:32

1 Answer 1

Reset to default
1

If only one database needs any attention, then upto autovacuum_max_workers will all get launched in that one database. But it will take a few passages of autovacuum_naptime to get all of them launched. Each one will take a different table to work on at a given time.

This might not have the effect you want, however, as the IO throttling threshold will get divided up between them. That means that while multiple tables are vacuumed in parallel, increasing the number autovacuum_max_workers does not increase the overall speed of vacuuming when you have IO throttling turned on. The purpose of using a higher value of autovacuum_max_workers is to ensure that small tables don't get entirely neglected if all the (would-be-lower max number) of workers are individually pinned down by large tables.

If you are worried autovac is not working fast enough, I would just go for the big guns and set autovacuum_vacuum_cost_delay = 0 in order to turn off IO throttling, and set log_autovacuum_min_duration = 0 so you can see the what is going on. This should quickly give you a definitive answer. As opposed to incrementally changing autovacuum_vacuum_cost_limit, which will slowly give you ambiguous answers.

Improve this answer
1
  • Thank you very much for help. I turned on autovacuum logging and checked the logs - autovacuum lasts more than 5 minutes on big tables! I have more then 10 big tables and autovacuum is often stuck on them, so other tables are not being vacuumed in that time (several minutes without autovacuum). I tried to increase "autovacuum_vacuum_cost_delay" but then I kill query performance. So my opinion is that I need faster disk (SSD). But this autovacuum tuning is pain in the ass, now I think that Postgres is not good enough for my project (heavy writes). I did checkpoint tuning and a lot more
    – elBastarde
    Mar 20, 2019 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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