Speed up database writes with synchronous_commit=off (and full_page_write=off and fsync=off?)

[anthonyfok]

2021-11-22 update: This issue was essentially fixed in PR #105 (Pipeline optimization, Sprint 34–36) merged in June 2021; see [https://github.com//issues/77#issuecomment-975802148](reasons why I am keeping this issue open for the time being]

---

docker-compose up --build currently takes several hours to run. In particular, importing data into PostgreSQL database seems somewhat slow to me, apparently synchronizing to the physical disk for each single operation.

It turns out there are PostgreSQL settings to optimize its speed, and some users report like 10x speed improvement for e.g. CI test runs.

[anthonyfok]

Hi @drotheram, please assign me to this issue (and add labels, link to project, etc.) when you have time. Many thanks!

Tasks:

• Benchmark, before and after. (with time or with docker-compose logs?)
• Relevant fsync=off, synchronous_commit=off, full_page_write=off
• Shutdown postgis/postgis and restart it with just synchronous_commit=off when we are done?
• (optional) Use pg_dump to backup the databases and store them somewhere, probably with a README.md or machine-readable file

Relevant links:

---

Related links (to be sorted):

eatmydata (no, it doesn't work with PostgreSQL

• eatmydata for bash - Google Search

• [MDLSITE-2152] Try out "eatmydata" on ci servers - Moodle Tracker

• Bug #1257036 “services started with eatmydata should remove eatm...” : Bugs : sysvinit package : Ubuntu

• [RFC PATCH] docker: Add support for using eatmydata in the database

• Eat ALL my data or how to LD_PRELOAD with sudo

• postgresql fsync - Google Search

• Don't set fsync=off if you want to keep your data - 2ndQuadrant | PostgreSQL

• synchronous_commit - Google Search

• PostgreSQL: Documentation: 9.4: Asynchronous Commit

• PostgreSQL: Documentation: 9.1: Write Ahead Log

• The "synchronous_commit" parameter

• "synchronous_commit" postgresql docker - Google Search

• Postgres non-durable options Docker container. | No Todo Es Mecánico

• Building a postgres docker image...

• How do I get into a Docker container's shell? - Stack Overflow

• trytond-gis/.drone.yml at develop · tryton/trytond-gis

• postgresql fsync off benchmark - Google Search

• Farewell to fsync(): 10× faster database tests with Docker

• FOSDEM 2019 - PostgreSQL vs. fsync

• A Performance Cheat Sheet for PostgreSQL | Severalnines

• postgresql direct IO - Google Search

• PostgreSQL - hackers - Asynchronous and "direct" IO support for PostgreSQL.

• Fsyncgate: errors on fsync are unrecovarable

• Re: fsync() errors is unsafe and risks data loss [LWN.net]

Misc:

• sdras/awesome-actions: A curated list of awesome actions to use on GitHub

[anthonyfok]

One place to test this during psql: Running Create_all_tables_update.sql...:

Without synchronous_commit=off etc.:

python-opendrr_1         | 2021-05-20T18:13:20.046554364Z UPDATE exposure.canada_exposure SET geom = st_setsrid(st_makepoint(SauidLon,SauidLat),4326);
python-opendrr_1         | 2021-05-20T18:22:10.956967658Z UPDATE 2237515

With synchronous_commit=off etc.:

[TODO]

[anthonyfok]

Reminder (to self): Of the three parameters, only synchronous_commit can be set with ALTER DATABASE, and that was done in add_data.sh in PR #105:

$ psql opendrr -a
psql (13.3 (Debian 13.3-1))
Type "help" for help.

opendrr=> ALTER DATABASE opendrr SET fsync TO off;
ERROR:  parameter "fsync" cannot be changed now
opendrr=> ALTER DATABASE opendrr SET full_page_writes TO off;
ERROR:  parameter "full_page_writes" cannot be changed now
opendrr=> ALTER DATABASE opendrr SET synchronous_commit TO off;
ALTER DATABASE
opendrr=> 

So, if we want to set fsync and full_page_writes to off too, that will need to be done via #94, pending more benchmark and ways to ensure the database is fully written to the disk at the end of the docker-compose run.

[anthonyfok]

Issue status

synchronous_commit = off has been implemented in the set_synchronous_commit() function in from add_data.sh:

• Commit 947ea49 Pull Request Pipeline optimization (Sprint 34–36) #105 (merged on 2021-06-14)

Why am I keeping this issue open:

• Lacking proper (unit) benchmark
• full_page_write=off and fsync=off can or cannot be done from add_data.sh?
• Earlier "draft" Pull Request Speed up PostGIS database with with synchronous_commit=off and more #94 that introduces a one-line change to docker-compose.yml, but which I didn't end up using as I do not yet know how to turn the settings back on for data integrity reasons.

To revisit in year 2022. :-)