A working class hero
And the employee of the decade goes to ……..
Auto-vacuum Worker
The most under-appreciated but hardest working employee of any organization with any flavor of Postgres has to be Auto-vacuum Worker.
Auto-vacuum Job Description :-
- Work 24/7
- Magically fix all problems of Postgres MVCC
- Do not get in the way of application queries
- Accept all blame for high CPU/IO
- No Pay
Auto-vacuum has a serious role to play in keeping Postgres engine efficient :-
- “dead tuples” cleanup
- Run Analyze for keeping stats fresh
- Update Free Space Map
- Update Visibility Map
- Prevent transaction id wraparound
I have attached links to a couple of nice articles from Percona and Cybertec that explain the above concepts in detail to allow me “artistic freedom” of keeping the blog focused on niche field of database comedy.
As soon as you’re born, they make you feel small
By giving you no time instead of it all
’Til the pain is so big you feel nothing at all
A working class hero is something to be
A working class hero is something to be
John Lennon was definitely a Postgres DBA since he predicted default parameters controlling Auto-vacuum resource consumption being too conservative and causing gradual pain via dead tuple pile-up. I wrote about Auto-vacuum woes in a previous blog so you may ask why sudden change of heart? Well, That was concerning the team’s desire of making databases as Cattle. I have always admired and viewed Auto-vacuum as an ally, not a foe.
Auto-vacuum Tuning
To all new Postgres DBAs transitioning from Oracle/Sql server/Mysql world, There is no need to be afraid of auto-vacuum. Oracle-type of databases implement MVCC via separate undo segments so they do a great job of hiding the pain. Most DBAs or end users in Oracle wouldn’t care about undo until they hit the snapshot_too_old error.
A Postgres database with optimal auto-vacuum is the secret to eternal bliss for DBAs. Before starting with Auto-vacuum optimization you should know your data and the reason to fiddle with parameters. Are your auto-vacuums running very long? Do you have tables with a high rate of updates/deletes that auto-vacuum cleanup is not able to keep up with dead tuples piling up? Do you have insert-only tables which end up with more resource intensive than normal auto-vacuum to prevent wraparound frequently? Do you have a large number of small tables that hinder the frequency of auto-vacuum on large and important tables? Or Is it just that auto-vacuum runs regularly as expected but doesn’t clean up dead tuples efficiently (subsequently results in application performance degradation)? Let’s go over the solution one by one.
Scenario 1 : Long-running auto-vacuums
Default values for autovacuum_vacuum_cost_limit and autovacuum_vacuum_cost_delay are very conservative. Sometimes auto-vacuum on large tables with the heavy update or delete can be slower than the rate at which dead tuples are generated. This will cause dead tuple pile up, and bloating of tables and indexes which hurts the performance of the application queries. You can increase cost limit and reduce cost delay to speed up auto-vacuum but it comes at cost of higher io/cpu. One thing that helps in decision-making is the dead tuple count against every table in pg_stat_all_tables. If you have a small number of large and heavily updated or deleted tables then you could set higher values for those parameters at the table level. Ideally, I would love to dig deeper and see if those large tables can be partitioned so that auto-vacuum will operate only on a small chunk of data and cold data partitions can stay out of the whole process. Dropping unused/duplicate indexes should help as well.
Conversely, If auto-vacuum is causing high io/cpu then you could use the same parameters to make it less aggressive. We have a large number of databases with minimum instance specification on the cloud due to micro-service architecture and we are often faced with the dilemma of toning down auto-vacuum to allow application queries enough resources.
Scenario 2 : Not enough auto-vacuum runs on important tables
This could happen in the database with a large number of tables of varying sizes. Since autovacuum triggering is based on the ratio of dead tuples to table size via autovacuum_vacuum_scale_factor. If many small tables get picked up by auto-vacuum before they could reach large and important tables then it would be better to define lower scale factors and thresholds on table level.
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.005, autovacuum_vacuum_threshold = 10000);
So now this large table will be picked by autovacuum when dead tuples reach 0.5% of table size + 10000. Of course, you will have to perform calculations looking at your data and instance capacity to reach the optimal value for these parameters.
Scenario 3: Insert only tables
Insert-only tables do not trigger auto-vacuum (before PG13) as inserts do not generate dead tuples. You may ask then why we need autovacuum on these tables. The answer is wraparound prevention.
PG13 to rescue with autovacuum_vacuum_insert_scale_factor but what about unfortunate souls still stuck on say PG12? For the short term, We picked the easier option of pg_cron or airflow dag running vacuum on insert-only tables during the weekend. There is another option that we did not utilize heavily i.e. lowering autovacuum_freeze_max_age significantly so that it runs more frequently but each run has less work to do. For a long-term solution, we would investigate the possibility of archival and deletion of old data so that would trigger auto-vacuum and the size of the database in check as well. It needs a bit of context, We are in the financial domain and with hundreds of databases due to micro-service architecture. Thus, it’s highly likely that service teams do not need data in the OLTP database from the inception of the company to function in daily operations.
Scenario 4: Auto-vacuum runs but does not clean dead tuples
It’s mostly due to long-running transactions blocking dead tuple cleanup. One of the reasons why Postgres has a hard time coping with mixed workload databases than pure OLTP. There is no easy solution, get cracking those explain plans and tune your queries.
Another reason could be abandoned replication slots, less common in the rest of the industry but we have quite a few cases of these due to the unique usage of Pipelinewise as explained in the earlier blog topic replication blues.
Last but not least, you can reduce the pain of under-optimized Auto-vacuum by doing more hot updates via a lower fill factor and avoiding updates on the indexed column. Applications with mostly short index range scan queries can lower the pain of dead tuple pile up too.
PS: Don’t forget,
If you want to be a hero, well, just follow me
If you want to be a hero, well, just follow me
To be continued… click here ….
