Postgres Tuning

https://github.com/timescale/timescaledb-tune

root@postgres0-0:/# /tmp/timescaledb-tune  -conf-path=/var/lib/postgresql/data/postgresql.conf
Using postgresql.conf at this path:
/var/lib/postgresql/data/postgresql.conf

Writing backup to:
/tmp/timescaledb_tune.backup202204232131

shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]: y
success: shared_preload_libraries will be updated

Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: y
Recommendations based on 31.36 GB of available memory and 8 CPUs for PostgreSQL 14

Memory settings recommendations
Current:
shared_buffers = 4GB
effective_cache_size = 8GB
maintenance_work_mem = 640MB
work_mem = 100MB
Recommended:
shared_buffers = 8027MB
effective_cache_size = 24081MB
maintenance_work_mem = 2047MB
work_mem = 10274kB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: memory settings will be updated

Parallelism settings recommendations
Current:
missing: timescaledb.max_background_workers
#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_workers = 8
Recommended:
timescaledb.max_background_workers = 8
max_worker_processes = 19
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: parallelism settings will be updated

WAL settings recommendations
Current:
#wal_buffers = -1
min_wal_size = 80MB
max_wal_size = 4GB
Recommended:
wal_buffers = 16MB
min_wal_size = 512MB
max_wal_size = 1GB
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: WAL settings will be updated

Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.9
max_connections = 200
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_connections = 100
max_locks_per_transaction = 256
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 256
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /var/lib/postgresql/data/postgresql.conf

https://blog.crunchydata.com/blog/optimize-postgresql-server-performance

https://www.cybertec-postgresql.com/en/3-ways-to-detect-slow-queries-in-postgresql/

https://www.postgresql.org/docs/14/runtime-config-resource.html

Zabbix

• https://youtu.be/xy_PImgRBT0

Query failed: ERROR: could not resize shared memory segment "/PostgreSQL.980262028" to 2097152 bytes: No space left on device

https://e-mc2.net/blog/using-zabbix-postgresql-database-backend/

listen_addresses = '*'
max_connections = 100                   # (change requires restart)
shared_buffers = 4GB                    # min 128kB
temp_buffers = 24MB                     # min 800kB
work_mem = 100MB                                # min 64kB
maintenance_work_mem = 640MB            # min 1MB
dynamic_shared_memory_type = posix      # the default is the first option
max_wal_size = 4GB
min_wal_size = 80MB
effective_cache_size = 8GB
log_timezone = 'Etc/UTC'
datestyle = 'iso, mdy'
timezone = 'Etc/UTC'
lc_messages = 'en_US.utf8'                      # locale for system error message
lc_monetary = 'en_US.utf8'                      # locale for monetary formatting
lc_numeric = 'en_US.utf8'                       # locale for number formatting
lc_time = 'en_US.utf8'                          # locale for time formatting
default_text_search_config = 'pg_catalog.english'

/dev/shm default is 64GB

spec:
  volumes:
  - name: dshm
    emptyDir:     
        medium: Memory
  - name: hostpath
    hostPath:
      path: /path/to/host/directory # Change to your directory
  containers:
  - image:  image-name #specify your image name here
    volumeMounts:
      - mountPath: /dev/shm
        name: dshm
      - mountPath: /workspace/container
        name: hostpath

https://medium.com/dive-into-ml-ai/raising-shared-memory-limit-of-a-kubernetes-container-62aae0468a33

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/395973-zabbix-not-write-data-to-db-postgresql-grow-queue

postgres tuned by timescaledb-tune(with small manual tune ) on both system.

grep -v "^#" postgresql.conf|cut -d "#" -f1|grep -v "^[[:space:]]*$"
listen_addresses='*'
max_connections = 512
shared_buffers = 7978MB
work_mem = 10212kB
maintenance_work_mem = 2047MB
dynamic_shared_memory_type = posix
effective_io_concurrency = 200
max_worker_processes = 19
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
synchronous_commit = off
wal_buffers = 32MB
wal_writer_delay = 2000ms
max_wal_size = 8GB
min_wal_size = 4GB
checkpoint_completion_target = 0.9
random_page_cost = 1.1
effective_cache_size = 23936MB
default_statistics_target = 500
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 0
log_error_verbosity = verbose
log_line_prefix = '%m [%p] '
log_timezone =---------
autovacuum = off
autovacuum_max_workers = 10
autovacuum_naptime = 10
datestyle = 'iso, mdy'
timezone = ------------
lc_messages = 'en_US.UTF8'
lc_monetary = 'en_US.UTF8'
lc_numeric = 'en_US.UTF8'
lc_time = 'en_US.UTF8'
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = 'timescaledb'
max_locks_per_transaction = 256
timescaledb.max_background_workers = 8
timescaledb.last_tuned = '2019-12-11T15:09:26+02:00'
timescaledb.last_tuned_version = '0.7.0'

we turn off synchronous_commit
and increase wal_buffers.