this post was submitted on 18 Aug 2023
6 points (100.0% liked)

PostgreSQL

662 readers
1 users here now

The world's most advanced open source relational database

Project
Events
Podcasts
Related Fediverse communities

founded 1 year ago
MODERATORS
[email protected]
[email protected]
[email protected]
6
PostgreSQL Optimizations (lemmy.daqfx.com)
submitted 1 year ago* (last edited 1 year ago) by [email protected] to c/[email protected]
11 comments fedilink hide all child comments
 

cross-posted from: https://lemmy.daqfx.com/post/24701

I'm hosting my own Lemmy instance and trying to figure out how to optimize PSQL to reduce disk IO at the expense of memory.

I accept increased risk this introduces, but need to figure out parameters that will allow a server with a ton of RAM and reliable power to operate without constantly sitting with 20% iowait.

Current settings:

# DB Version: 15
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 32 GB
# CPUs num: 8
# Data Storage: hdd

max_connections = 200
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 10485kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4
fsync = off
synchronous_commit = off
wal_writer_delay = 800
wal_buffers = 64MB

Most load comes from LCS script seeding content and not actual users.

Solution: My issue turned out to be really banal - Lemmy's PostgreSQL container was pointing at default location for config file (/var/lib/postgresql/data/postgresql.conf) and not at the location where I actually mounted custom config file for the server (/etc/postgresql.conf). Everything is working as expected after I updated docker-compose.yaml file to point PostgreSQL to correct config file. Thanks @[email protected] for pointing me in the right direction!

you are viewing a single comment's thread
view the rest of the comments
[–] [email protected] 2 points 1 year ago* (last edited 1 year ago) (2 children)

I've always seen this site recommended when tuning postgres config:

https://pgtune.leopard.in.ua/

Interestingly it provides the same config for a 32gb/8core/HDD setup, except for the last 4 lines.

fsync = off
synchronous_commit = off
wal_writer_delay = 800
wal_buffers = 64MB

From my understanding of PG, fsync and synchronous commit are both likely to reduce data integrity by speeding up writes. It will allow PG to send file/page changes into the void of the OS without waiting for confirmation that it's happened successfully.

The wal options there, I do not know about.

wal_writer_delay (default 200) 800

After flushing WAL the writer sleeps for the length of time given by wal_writer_delay, unless woken up sooner by an asynchronously committing transaction.

I guess this gives the OS more time to deal with other PG operations before writing any new data to disk.

wal_buffers (default -1 auto) 64mb

The amount of shared memory used for WAL data that has not yet been written to disk. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers

The default auto setting would have assigned 250mb for wal_buffers. Not sure why this is explicitly set, or what the benefit of reducing it is

[–] [email protected] 1 points 1 year ago (1 children)

I'll try adjusting wal_buffers.

I think I was hoping there's s magic setting that would allow psql to operate more like Redis that uses ram for everything until it dumps it to disk at specific intervals.

[–] [email protected] 1 points 1 year ago

I wouldn't take anything I say as a recommendation. I'm learning, too. And was hoping to start a conversation (or get corrected).

I should've referenced the actual docs. Google directed me to some 3rd party bullshit.

The amount of shared memory used for WAL data that has not yet been written to disk. The default setting of -1 selects a size equal to 1/32nd (about 3%) of shared_buffers, but not less than 64kB nor more than the size of one WAL segment, typically 16MB. This value can be set manually if the automatic choice is too large or too small, but any positive value less than 32kB will be treated as 32kB. If this value is specified without units, it is taken as WAL blocks, that is XLOG_BLCKSZ bytes, typically 8kB. This parameter can only be set at server start.

The contents of the WAL buffers are written out to disk at every transaction commit, so extremely large values are unlikely to provide a significant benefit. However, setting this value to at least a few megabytes can improve write performance on a busy server where many clients are committing at once. The auto-tuning selected by the default setting of -1 should give reasonable results in most cases.

So, it's more about concurrent client writes... I guess?