Postgres Tuning?

[MTRNord]

Hi is there a way to modify my postgres settings? I would like to do some tuning. (Using something like https://pgtune.leopard.in.ua/#/ ). Is there a way to set the generated variables easily?

[spantaleev]

Looking at the "Database configuration" section for the postgres Docker image, it seems like we have a few ways to go about it:

• either by modifying the start command to be something like this: postgres -c 'option=value' -c 'another=value'

• or by writing out a configuration file, mounting it in the container and using a start command like this: postgres -c 'config_file=/path/to/postgres.custom.conf'

Perhaps the latter means that we have to specify a full configuration? It's not just some overrides on top of a sane default configuration. So it may not work directly with this configuration tuning generator (other options may be necessary).

Going with the former is likely better, but leads to a long and ugly command line. So I guess we should first investigate the config-file idea.

[ptman]

Many postgres parameters can also be set on the connection or database level. So you can connect using psql and e.g.:

SHOW random_page_cost;
SET random_page_cost=1.1;
ALTER DATABASE homeserver SET random_page_cost=1.1;
ALTER DATABASE homeserver RESET random_page_cost;

Edit: this is documented in pgtune:

ALTER SYSTEM writes the given parameter setting to the postgresql.auto.conf file, which is read in addition to postgresql.conf

[MTRNord]

@ptman oh that permanent. In that case alter system is a solution. I did think I read it is only uintil restart but I reread and I see it seems to be persistent :o

[ptman]

I'm not entirely sure in case of docker. Is the postgresql.auto.conf file in a persistent location?

[spantaleev]

Yes, postgresql.auto.conf is stored on disk in a persistent location (/matrix/postgres/data/postgresql.auto.conf).

Perhaps it won't survive Postgres upgrades though. We make a Postgres dump, relocate the data folder (to make a backup), then start fresh, then import the dump file. I'm not sure if the dump we do would contain these ALTER SYSTEM statements. So Postgres upgrades will likely lead to this being lost (unless we take special care to restore the old postgresql.auto.conf after importing the dump).

[lovelaced]

If you need postgres tuning, you probably need workers as well and if you're using the commit from @eMPee584 your matrix-synapse.service is going to be super long already anyway, so honestly adding a bunch of -c's to your matrix-postgres.service shouldn't be a big deal. Really important is raising the max_connections. What I'm using for a box with 32GB of RAM:

			postgres:12.3-alpine \
                         -c max_connections=3000 \
                         -c shared_buffers=8GB \
                         -c effective_cache_size=24GB \
                         -c maintenance_work_mem=1GB \
                         -c checkpoint_completion_target=0.9 \
                         -c wal_buffers=16MB \
                         -c default_statistics_target=100 \
                         -c random_page_cost=1.1 \
                         -c effective_io_concurrency=200 \
                         -c work_mem=349kB \
                         -c min_wal_size=1GB \
                         -c max_wal_size=4GB \
                         -c max_worker_processes=8 \
                         -c max_parallel_workers_per_gather=4 \
                         -c max_parallel_workers=8 \
                         -c max_parallel_maintenance_workers=4

[PC-Admin]

Worth noting that custom postgresql settings can be defined in the matrix-postgres role now, see this pull for details: #642

Might be time to close this issue.

[aaronraimist]

Would be nice to document it and also make sure it is using sane defaults

[PC-Admin]

Here's out postgres config for a 6 core 24GB server with 1 federation sender and around ~100 users, if it helps:

matrix_postgres_process_extra_arguments: [
  "-c max_connections=40",
  "-c shared_buffers=1536MB",
  "-c checkpoint_completion_target=0.7",
  "-c wal_buffers=16MB",
  "-c default_statistics_target=100",
  "-c random_page_cost=1.1",
  "-c effective_io_concurrency=100",
  "-c work_mem=2621kB",
  "-c min_wal_size=1GB",
  "-c max_wal_size=4GB",
  "-c max_worker_processes=6",
  "-c max_parallel_workers_per_gather=3",
  "-c max_parallel_workers=6",
  "-c max_parallel_maintenance_workers=3",
  "-c synchronous_commit=off"
]