Lädt...


🔧 Autovacuum Tuning in The Cloud Era


Nachrichtenbereich: 🔧 Programmierung
🔗 Quelle: dev.to

Image description

How often do you hear DBAs vent, "Autovacuum used to fly on bare-metal, but in the cloud, it feels like it's dragging forever!" Or they might say, "I've doubled the max_autovacuum_workers, yet dead tuples just keep stacking up," or "Autovacuum isn't giving us the throughput our cloud provider promised!" And then there's the developer chiming in, "Every time Autovacuum kicks in on that big table, our application queries start timing out!" These frustrations are all too common in cloud environments, where expectations meet the harsh reality of "you get what you pay for."

In the cloud era, PostgreSQL's Autovacuum tuning can feel like a tightrope walk - juggling application performance, costs, and autovacuum efficiency due to cloud-imposed limitations. The cloud puts strict caps on I/O and throughput. Push Autovacuum too hard, and it'll devour your application's resources, tanking performance. But dial it down too much, and bloat creeps in, eventually strangling the application's efficiency. It's all about finding that sweet spot.

Often DBAs increase autovacuum_max_workers , maintenance_work_mem, adjust autovacuum_vacuum_scale_factor but still wonder why it fails to clean the database effectively. This inefficiency arises because Autovacuum throughput is constrained by the autovacuum_vacuum_cost_limit and further limited by host restrictions on Cloud. Before we delve into tuning Autovacuum, we need some metrics at hand. Autovacuum is known for being I/O-intensive, but just how much strain does it place on your system? Let's explore.

#Firstly, let's see how much IO to expect.
#At most, an autovacuum can do IO as shown below.
Max Autovacuum throughput = (1000/autovacuum_vacuum_cost_delay) * (autovacuum_vacuum_cost_limit/vacuum_cost_page_hit) * 8 KB (default block_size)

#For PG17 default settings:
Max Autovacuum throughput = (1000/2) * (200/1) * 8 KB = 800MB per second

Now let's see what is io throughput limit on different cloud and at what cost?

Starting with AWS :-(since it's most familiar to me)
Old answer but I just wanted to show that throughput limit in AWS depends on both instance class and storage class.

Image description

Burstable instance class : It's too small throughput and generally not suitable for high performance demanding production workloads hence ignoring it.

Standard instance class : General purpose standard instances of latest graviton series offer better throughput upto 2.5GBps.
Gbps/Mbps here is in Bits hence division by 8 to get GB/s or MB/s.

Image description

"Up to" is a very vague term. I found another piece of the puzzle in AWS document.

Image description

Since maximum performance is guaranteed for only 30 minutes per day, we will focus only on baseline values.

Next storage classes and limits:
We will only consider SSDs and latest storage classes availble.
GP3: GP3 offers 15MB/s for tiny (below 400GB) databases and customizable range of 62.5MB/s to 500MB/s for larger database.

Image description

io2 :

Image description

For io2 we can reach up to 500MB/s as we crank up iops knob.

Image description

Cost : Addressing the Elephant in the room

Image description

Let's see in AWS pricing calculator how much we would need to spend for reaching 500MB/s throughput capacity on AWS (in cheapest region) for 1TB RDS Postgres database. For simplifying calculations and comparisons we will consider single-AZ RDS, you should always use multi-AZ in production.
Instance class m7g.4xlarge (baseline is 625MB
Storage io2 256000 iops

Image description

Image description

holy IOPS!! 26K for 1TB

Image description

Don't worry GP3 saves the day.

Image description

Image description

Seems reasonable cost if you have high throughput requirement on RDS.

Next we will see pricing and capacity for Google Cloud SQL for PostgreSQL and Azure Database for PostgreSQL — Flexible Server to achieve same 500MB/s throughput. These two are closest matching counterparts of Amazon RDS Postgres.

Google :

Image description

So we will need a Zonal extreme PD and N2 VM with 64 vCPUs to get 500MB/s on GCP.

Image description

Google pricing calculator is very limited, it does not allow me to select zonal extreme-pd and just offers generalized cost of ssd.

Azure Database for PostgreSQL — Flexible Server

We will need a configuration of at least 16 vCores (for example, the D16ds_v5 instance) as it can provide up to 600 MB/s throughput.

Image description

SSD v2 is still in preview as of writing this article.

So estimated monthly cost would be around $1,400.00 slightly lower than AWS.

Why did I dive into the cost of compute and storage? Well, it’s not just about convincing the cloud FinOps team that we need a buffer for throughput — though that’s part of it. As a DBA, you might find yourself in a production incident where a small capacity bump could cost your company only a few hundred dollars. But in doing so, you’d be preventing a potential disaster worth thousands! Sometimes, the difference between smooth sailing and costly downtime is just a matter of proactively increasing capacity before things go south. 🚀

Juggling Cost, Autovacuum I/O, and Application Performance

Image description

Autovacuum Tuning consider the following two scenarios:

  1. Your “Pet” Database:
    If you only have a single beloved database to care with ample throughput capacity, you can fine-tuneautovacuum_vacuum_cost_limit and autovacuum_max_workersto keep the vacuum backlog low. Regularly monitor dead tuples using pg_stat_all_tables to identify any potential issues. For very large tables that require special attention, adjust the table-level Autovacuum parameters while maintaining conservative settings for the rest.

  2. Microservices Architecture:
    In a company operating a microservices architecture with hundreds of independent RDS PostgreSQL databases, cloud FinOps experts aim to optimize costs by selecting RDS instance classes and storage that align with the application’s throughput and latency requirements — a practice known as right-sizing. However, this approach often overlooks the Autovacuum component. Being on an undersized instance means that merely increasing autovacuum_vacuum_cost_limit, reducing autovacuum_vacuum_cost_delay, or adding more workers won’t suffice.

As a DBA, you must think creatively :

  1. Consider partitioning problematic tables, enabling Autovacuum to operate on smaller chunks. With indexes local to partitions, their sizes decrease, aiding Autovacuum as it’s vacuuming all indexes each time.

  2. Detect and drop unused indexes. Lesser the number of indexes, quicker Autovacuum can finish.

  3. Minimize long-running transactions to prevent them from holding up xmin, rendering some Autovacuum runs ineffective.

4.If Autovacuum on large tables still impacts application performance by hogging throughput, you may need to dial back cost limit and delay.

  1. However, don’t go too conservative on cost limit and delay; this risks TXID wraparound, which can bring down your entire database. Always implement an Early Warning System for TXID wraparound (AWS offers a detailed guide on this).

Additionally, log these warnings and observe dead tuple growth trends in your most critical databases. Share these findings with FinOps, advocating funding for more throughput capacity at both instace and storage level.

A picture is worth a thousand words

Image description

...

🔧 Autovacuum Tuning in The Cloud Era


📈 60.19 Punkte
🔧 Programmierung

🔧 Tìm Hiểu Về RAG: Công Nghệ Đột Phá Đang "Làm Mưa Làm Gió" Trong Thế Giới Chatbot


📈 34.69 Punkte
🔧 Programmierung

🔧 How to Set and Tune Autovacuum Settings in PostgreSQL to Avoid Performance Issues


📈 34.69 Punkte
🔧 Programmierung

🔧 Có thể bạn chưa biết (Phần 1)


📈 34.69 Punkte
🔧 Programmierung

📰 Und Microsoft so: Cloud, Cloud, Cloud, Cloud, Cloud, Cloud, Cloud


📈 23.48 Punkte
📰 IT Security Nachrichten

📰 Self-Data Distilled Fine-Tuning: A Solution for Pruning and Supervised Fine-tuning Challenges in LLMs


📈 23.17 Punkte
🔧 AI Nachrichten

📰 Self-Data Distilled Fine-Tuning: A Solution for Pruning and Supervised Fine-tuning Challenges in LLMs


📈 23.17 Punkte
🔧 AI Nachrichten

🔧 Swarm-Tuning AI Experts: Collaborative Fine-Tuning of Large Language Models


📈 23.17 Punkte
🔧 Programmierung

🎥 Tuning Parameters in Postgres vs. Tuning Your Queries


📈 23.17 Punkte
🎥 Video | Youtube

📰 What is Fine Tuning and Best Methods for Large Language Model (LLM) Fine-Tuning


📈 23.17 Punkte
🔧 AI Nachrichten

🎥 Azure SQL Database: Improving Performance Tuning with Automatic Tuning | Data Exposed: MVP Edition


📈 23.17 Punkte
🎥 Video | Youtube

🔧 Sonos Era 300 und Era 100 im Test: Das können die WLAN-Lautsprecher


📈 21.12 Punkte
🔧 Programmierung

📰 Neue Sonos-Lautsprecher: Details zu Era 300, Era 100 und Move 2 durchgesickert


📈 21.12 Punkte
📰 IT Nachrichten

🔧 Sonos Era 300 und Era 100 im Test: Das können die WLAN-Lautsprecher


📈 21.12 Punkte
🔧 Programmierung

🍏 Sonos Era 100 & Era 300 speakers coming soon


📈 21.12 Punkte
🍏 iOS / Mac OS

📰 Sonos Era 300 und Era 100: Was die neuen Lautsprecher können – und wo ihr sie mit Rabatt bekommt


📈 21.12 Punkte
📰 IT Nachrichten

🔧 Sonos Era 300 und Era 100 im Test: Das können die neuen WLAN-Lautsprecher


📈 21.12 Punkte
🔧 Programmierung

📰 Sonos Era 100 und Era 300 im Test: So klingen die neue Smartspeaker


📈 21.12 Punkte
📰 IT Nachrichten

📰 Beginn einer neuen Ära? Sonos präsentiert Era 300 und Era 100


📈 21.12 Punkte
📰 IT Nachrichten

📰 Sonos Era 300 und Era 100: Neue Sonos-Lautsprecher offiziell vorgestellt


📈 21.12 Punkte
📰 IT Nachrichten

🍏 Sonos unveils next generation Era 100 & Era 300 smart home speakers


📈 21.12 Punkte
🍏 iOS / Mac OS

📰 Sonos Era 100 und Era 300: Bilder und Details zu neuen Smart-Speakern


📈 21.12 Punkte
📰 IT Nachrichten

📰 Era 100 und Era 300: Alle Details der neuen Sonos-Lautsprecher enthüllt


📈 21.12 Punkte
📰 IT Nachrichten

🍏 Selten im Angebot: Die Sonos-Lautsprecher Era 100 und Era 300


📈 21.12 Punkte
🍏 iOS / Mac OS

📰 Sonos Era 100 und Era 300: Weitere Bilder der neuen Lautsprecher


📈 21.12 Punkte
📰 IT Nachrichten

📰 Sonos Era 100 und Era 300: Rabattaktion für Bundles und Stereo-Sets bei tink


📈 21.12 Punkte
📰 IT Nachrichten

🍏 Sonos Era 100 & Era 300 HomePod competitors coming in March


📈 21.12 Punkte
🍏 iOS / Mac OS

📰 Sonos Era 100 und Era 300: Neue Smart-Speaker schon jetzt bei tink günstiger


📈 21.12 Punkte
📰 IT Nachrichten

matomo