4 ms·

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

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).
- williamdclt 3y ago
  I was surprised but CREATE DATABASE TEMPLATE is still pretty slow, a few hundred ms on my machine. Better than alternatives though. A few more application-level optimisations: if you need to clear your database between tests, the best way is to wrap each test in a transaction and roll it back (Postgres supports nested transactions with checkpoints which might help make that transparent to the application under test). The second best way is usually to use DELETE rather than TRUNCATE, the latter is much slower on small tables (but much faster on big ones). The third fastest way is to create a new DB from template, although it plays nice with parallelisation.
- magicalhippo 3y ago
  How much does an fsync cost against memory (tmpfs/RAM disk)? Just made me curious.
- zamalek 3y ago
  Thank you!
rbanffy 3y ago
> This is for integration tests where the database is thrown away at the end of the run anyway. For situations like these, creating the database in a tmpfs volume and having enough memory on the db host would do wonders.
hitchstory 3y ago
-c fsync=off -c synchronous_commit=off -c full_page_writes=off I asked Karen Jex this question at her Djangocon 2023 talk on obscure postgres switches. I used this among other things to build some model integration tests which exhibit best practices: https://github.com/hitchdev/hitchstory/tree/master/examples/website https://github.com/hitchdev/hitchstory/tree/master/examples/... I considered using tmpfs but since I wanted to cache the entire database volume after it had been built according to the fixtures and couldnt figure out how to do that with podman and tmpfs.
Twirrim 3y ago
On a more generic front, there's always libeatmydata which forcibly disables fsync. I've used it in the past for doing, eg, faster database restores. Run mysqld under it, do the restore, manually force a sync, shut down MySQL, restart and then carry out validation activities. Usually dramatically speeds up bringing up a replica. https://www.flamingspork.com/projects/libeatmydata/ https://www.flamingspork.com/projects/libeatmydata/
aeyes 3y ago
Additional tricks I didn't see in the thread: I just disable autovacuum completely for test runs with autovacuum=off. Remember that it kicks in according to the percentage of data updated so with a table which starts empty you are guaranteed to see autovacuum run once you insert a few rows. If you have a lot of updates you can also try change the table fillfactor. This makes HOT updates a bit more efficient.

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).

williamdclt 3y ago
I was surprised but CREATE DATABASE TEMPLATE is still pretty slow, a few hundred ms on my machine. Better than alternatives though. A few more application-level optimisations: if you need to clear your database between tests, the best way is to wrap each test in a transaction and roll it back (Postgres supports nested transactions with checkpoints which might help make that transparent to the application under test). The second best way is usually to use DELETE rather than TRUNCATE, the latter is much slower on small tables (but much faster on big ones). The third fastest way is to create a new DB from template, although it plays nice with parallelisation.
magicalhippo 3y ago
How much does an fsync cost against memory (tmpfs/RAM disk)? Just made me curious.
zamalek 3y ago
Thank you!

rbanffy 3y ago

> This is for integration tests where the database is thrown away at the end of the run anyway. For situations like these, creating the database in a tmpfs volume and having enough memory on the db host would do wonders.

hitchstory 3y ago

-c fsync=off -c synchronous_commit=off -c full_page_writes=off I asked Karen Jex this question at her Djangocon 2023 talk on obscure postgres switches. I used this among other things to build some model integration tests which exhibit best practices: https://github.com/hitchdev/hitchstory/tree/master/examples/website https://github.com/hitchdev/hitchstory/tree/master/examples/... I considered using tmpfs but since I wanted to cache the entire database volume after it had been built according to the fixtures and couldnt figure out how to do that with podman and tmpfs.

Twirrim 3y ago

On a more generic front, there's always libeatmydata which forcibly disables fsync. I've used it in the past for doing, eg, faster database restores. Run mysqld under it, do the restore, manually force a sync, shut down MySQL, restart and then carry out validation activities. Usually dramatically speeds up bringing up a replica. https://www.flamingspork.com/projects/libeatmydata/ https://www.flamingspork.com/projects/libeatmydata/

aeyes 3y ago

Additional tricks I didn't see in the thread: I just disable autovacuum completely for test runs with autovacuum=off. Remember that it kicks in according to the percentage of data updated so with a table which starts empty you are guaranteed to see autovacuum run once you insert a few rows. If you have a lot of updates you can also try change the table fillfactor. This makes HOT updates a bit more efficient.