Postgres Tuning
Jump to navigation
Jump to search
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
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
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.