5 ms·

Optimizing Postgres's autovacuum for high-churn tables

91 points by chuckhend 3y ago

abalashov 3y ago
Very helpful article! It jives with the evolution of my own understanding of autovacuum tuning over a decade and a half of writing high-performance Postgres-backed VoIP routing systems. I would add to this that if you truly have a high-churn table, perhaps a traditional RDBM is not the best choice. Ask me how I learned that lesson the hard way. There are many things that, in hindsight, really should have gone into ELK or Splunk or Redis or something more suited to ephemeral or short-lived data sets that also turn over constantly. But, sometimes there is no choice, or the churn is high but not quite high enough to rule out the use of an RDBM altogether. For those scenarios, the tips in this article really shine.
- sophacles 3y ago
  How'd you learn that lesson the hard way?
  - abalashov 3y ago
    I made the mistake of writing a searchable syslog archiver where the parsing was done in stored functions (very basic string-splitting) and the log entries were stored in a heavily indexed Postgres table. At the time it was created, the company was in its infancy, there wasn't much throughput, the service emitting the logs was kind of obscure, and the solution was a quick, dirty stopgap meant to be easily digestible and consumable to the customer, compatible with the rest of their in-house knowledge and the rest of their backend stack. However, as happens too often, the company grew meteorically, the volume got to be insane, many more default-on log entries were added to the standard event loop, and the system was put straight into production without any interest in redesign; after all, it worked fine so far. As you might guess, it got to the point where we were seeing insanely degraded performance and cascade failures after only a few days' log activity, and increasing time and resources were devoted to the endless care and feeding of this beast. It took a surprisingly long time for the rubber band to snap and for the customer to consider ELK, despite the fact that I advocated for this almost from the very beginning.
zamalek 3y ago
I haven't had much success with finding an answer to this: what flags should be set to allow postgres to run as fast as possible without caring about data loss at all? This is for integration tests where the database is thrown away at the end of the run anyway. I'll definitely be looking at further tweaking vacuuming based on this article.
- brianwawok 3y ago
  You get rid of fsync and a few smiliar flags. Made my tests about 50% faster https://postgresqlco.nf/doc/en/param/fsync/#:~:text=Setting%20fsync%3Doff%20is%20the,a%20performance%20concern%2C%20see%20synchronous_commit https://postgresqlco.nf/doc/en/param/fsync/#:~:text=Setting%....
- chuckhend 3y ago
  Check out unlogged tables! https://www.crunchydata.com/blog/postgresl-unlogged-tables https://www.crunchydata.com/blog/postgresl-unlogged-tables
- deleted 3y ago
  [deleted]
- nicolaslem 3y ago
  I would be interested if anyone had similar tweaks for MySQL. I used to mount the directory where MySQL stores data into memory, this used to be very effective until upgrading to MySQL 8. Now my test suite takes three times as long to run and I never really figured out why.
  - rbanffy 3y ago
    > I never really figured out why. How is memory consumption during the tests? If you run out of memory on a tmpfs, you'll hit swap and that'll slow you down considerably. Do you see other disk access during the test?
- cxcorp 3y ago
  For the postgres config, set fsync=off and full_page_writes=false, and increase min_wal_size, max_wal_size and checkpoint interval with the hope that your tests pass before having to flush the WAL. Maybe slap in some tunings from PGTune. If you're using docker/podman or docker-compose and your db size is small, a major speedup on linux is to just mount the entire data dir into memory with --tmpfs /var/lib/postgresql/data (or tmpfs: - /var/lib/postgresql/data in docker-compose) Additionally, if you constantly reset your db in the tests, consider making a template db at the start and later just doing CREATE DATABASE ... TEMPLATE foo; to copy the pages from that template instead of running migrations that produce WAL log. In fact, consider making a db for every test suite from that template at the start - then you can run each suite in parallel (if your app's only state is the db and a single backend).
cett 3y ago
I found that partitioning the table to allow parallel auto vacuuming was necessary to scale.
h1fra 3y ago
Vacuuming is such a complex task in optimize in Postgres. Depending on your access pattern you will need to also configure index with conditions and/or partitioning (on top of sane vacuuming settings). And when you realise that Update is the same as Delete+Create it adds up to the confusion.
- refset 3y ago
  > Update is the same as Delete+Create I gave a talk titled "UPDATE Considered Harmful" featuring exactly this idea a few months back. The etymology still makes no sense to me...they should have named it REPLACE instead of UPDATE: https://www.youtube.com/watch?v=JxMz-tyicgo https://www.youtube.com/watch?v=JxMz-tyicgo
  - chuckhend 3y ago
    Good talk! It was surprising to me when I first learned that UPDATE does not literally overwrite what is on disk, its more like an abstracted API that gives me the impression that I updated the row on disk.
    - refset 3y ago
      Thanks :) The surprises don't stop though - it's abstract turtles all the way down...meaning plenty of fat to trim between UPDATE and what's happening in the MOSFETs. Might be of interest: https://arxiv.org/pdf/2307.11866.pdf https://arxiv.org/pdf/2307.11866.pdf
- hobs 3y ago
  Fwiw many databases have update be delete+create because you've already invented those primitives so why do something else?
  - rbanffy 3y ago
    This is useful for transactional operations where you want to preserve the previous state until the transaction completes. If you don't need transactions, or you don't need point-in-time queries, then updating in place would save time.
hinkley 3y ago
The first real time GC I encountered accomplished its goal by amortizing deletes across creates so the max allocation time could be guaranteed. After all these years, I wonder why vacuum is still such a PITA with Postgres. Vacuuming should be at least partially concurrent with CREATE.
- ses1984 3y ago
  How often are you executing create, compared to how often do you need to vacuum?
  - hinkley 3y ago
    If the data set isn't growing, how big of a priority is vacuuming? You're at homeostasis if you aren't creating or updating records. If memory serves the GC implementation was freeing/defragging 10 chunks of memory for each one it allocated. In a real time system if you're allocating a bunch of memory, you're already on notice, so having it cost a few thousand instructions instead of a few hundred isn't that big of an inconvenience. And with SQL the network time is going to dominate.
    - ses1984 3y ago
      Did you mean insert…?

91 points by chuckhend 3y ago

Very helpful article! It jives with the evolution of my own understanding of autovacuum tuning over a decade and a half of writing high-performance Postgres-backed VoIP routing systems. I would add to this that if you truly have a high-churn table, perhaps a traditional RDBM is not the best choice. Ask me how I learned that lesson the hard way. There are many things that, in hindsight, really should have gone into ELK or Splunk or Redis or something more suited to ephemeral or short-lived data sets that also turn over constantly. But, sometimes there is no choice, or the churn is high but not quite high enough to rule out the use of an RDBM altogether. For those scenarios, the tips in this article really shine.

sophacles 3y ago
How'd you learn that lesson the hard way?
- abalashov 3y ago
  I made the mistake of writing a searchable syslog archiver where the parsing was done in stored functions (very basic string-splitting) and the log entries were stored in a heavily indexed Postgres table. At the time it was created, the company was in its infancy, there wasn't much throughput, the service emitting the logs was kind of obscure, and the solution was a quick, dirty stopgap meant to be easily digestible and consumable to the customer, compatible with the rest of their in-house knowledge and the rest of their backend stack. However, as happens too often, the company grew meteorically, the volume got to be insane, many more default-on log entries were added to the standard event loop, and the system was put straight into production without any interest in redesign; after all, it worked fine so far. As you might guess, it got to the point where we were seeing insanely degraded performance and cascade failures after only a few days' log activity, and increasing time and resources were devoted to the endless care and feeding of this beast. It took a surprisingly long time for the rubber band to snap and for the customer to consider ELK, despite the fact that I advocated for this almost from the very beginning.

zamalek 3y ago

I haven't had much success with finding an answer to this: what flags should be set to allow postgres to run as fast as possible without caring about data loss at all? This is for integration tests where the database is thrown away at the end of the run anyway. I'll definitely be looking at further tweaking vacuuming based on this article.

brianwawok 3y ago
You get rid of fsync and a few smiliar flags. Made my tests about 50% faster https://postgresqlco.nf/doc/en/param/fsync/#:~:text=Setting%20fsync%3Doff%20is%20the,a%20performance%20concern%2C%20see%20synchronous_commit https://postgresqlco.nf/doc/en/param/fsync/#:~:text=Setting%....
chuckhend 3y ago
Check out unlogged tables! https://www.crunchydata.com/blog/postgresl-unlogged-tables https://www.crunchydata.com/blog/postgresl-unlogged-tables
deleted 3y ago
[deleted]
nicolaslem 3y ago
I would be interested if anyone had similar tweaks for MySQL. I used to mount the directory where MySQL stores data into memory, this used to be very effective until upgrading to MySQL 8. Now my test suite takes three times as long to run and I never really figured out why.
- rbanffy 3y ago
  > I never really figured out why. How is memory consumption during the tests? If you run out of memory on a tmpfs, you'll hit swap and that'll slow you down considerably. Do you see other disk access during the test?
cxcorp 3y ago
For the postgres config, set fsync=off and full_page_writes=false, and increase min_wal_size, max_wal_size and checkpoint interval with the hope that your tests pass before having to flush the WAL. Maybe slap in some tunings from PGTune. If you're using docker/podman or docker-compose and your db size is small, a major speedup on linux is to just mount the entire data dir into memory with --tmpfs /var/lib/postgresql/data (or tmpfs: - /var/lib/postgresql/data in docker-compose) Additionally, if you constantly reset your db in the tests, consider making a template db at the start and later just doing CREATE DATABASE ... TEMPLATE foo; to copy the pages from that template instead of running migrations that produce WAL log. In fact, consider making a db for every test suite from that template at the start - then you can run each suite in parallel (if your app's only state is the db and a single backend).

cett 3y ago

I found that partitioning the table to allow parallel auto vacuuming was necessary to scale.

h1fra 3y ago

Vacuuming is such a complex task in optimize in Postgres. Depending on your access pattern you will need to also configure index with conditions and/or partitioning (on top of sane vacuuming settings). And when you realise that Update is the same as Delete+Create it adds up to the confusion.

refset 3y ago
> Update is the same as Delete+Create I gave a talk titled "UPDATE Considered Harmful" featuring exactly this idea a few months back. The etymology still makes no sense to me...they should have named it REPLACE instead of UPDATE: https://www.youtube.com/watch?v=JxMz-tyicgo https://www.youtube.com/watch?v=JxMz-tyicgo
- chuckhend 3y ago
  Good talk! It was surprising to me when I first learned that UPDATE does not literally overwrite what is on disk, its more like an abstracted API that gives me the impression that I updated the row on disk.
  - refset 3y ago
    Thanks :) The surprises don't stop though - it's abstract turtles all the way down...meaning plenty of fat to trim between UPDATE and what's happening in the MOSFETs. Might be of interest: https://arxiv.org/pdf/2307.11866.pdf https://arxiv.org/pdf/2307.11866.pdf
hobs 3y ago
Fwiw many databases have update be delete+create because you've already invented those primitives so why do something else?
- rbanffy 3y ago
  This is useful for transactional operations where you want to preserve the previous state until the transaction completes. If you don't need transactions, or you don't need point-in-time queries, then updating in place would save time.

hinkley 3y ago

The first real time GC I encountered accomplished its goal by amortizing deletes across creates so the max allocation time could be guaranteed. After all these years, I wonder why vacuum is still such a PITA with Postgres. Vacuuming should be at least partially concurrent with CREATE.

ses1984 3y ago
How often are you executing create, compared to how often do you need to vacuum?
- hinkley 3y ago
  If the data set isn't growing, how big of a priority is vacuuming? You're at homeostasis if you aren't creating or updating records. If memory serves the GC implementation was freeing/defragging 10 chunks of memory for each one it allocated. In a real time system if you're allocating a bunch of memory, you're already on notice, so having it cost a few thousand instructions instead of a few hundred isn't that big of an inconvenience. And with SQL the network time is going to dominate.
  - ses1984 3y ago
    Did you mean insert…?