Postgres Tuning - Revision history

Busk at 21:34, 23 April 2022

2022-04-23T21:34:13Z

Busk at 14:27, 23 April 2022

2022-04-23T14:27:07Z

Busk at 13:39, 23 April 2022

2022-04-23T13:39:50Z

Busk at 00:14, 23 April 2022

2022-04-23T00:14:45Z

Busk at 00:08, 23 April 2022

2022-04-23T00:08:56Z

Busk at 00:07, 23 April 2022

2022-04-23T00:07:24Z

Busk at 00:06, 23 April 2022

2022-04-23T00:06:15Z

Busk at 17:04, 22 April 2022

2022-04-22T17:04:25Z

Busk at 17:04, 22 April 2022

2022-04-22T17:04:14Z

Busk at 15:42, 22 April 2022

2022-04-22T15:42:03Z

← Older revision		Revision as of 21:34, 23 April 2022
Line 1:		Line 1:
	https://github.com/timescale/timescaledb-tune		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://blog.crunchydata.com/blog/optimize-postgresql-server-performance

← Older revision		Revision as of 14:27, 23 April 2022
Line 1:		Line 1:
		+	https://github.com/timescale/timescaledb-tune
		+
	https://blog.crunchydata.com/blog/optimize-postgresql-server-performance		https://blog.crunchydata.com/blog/optimize-postgresql-server-performance

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

← Older revision		Revision as of 00:14, 23 April 2022
Line 1:		Line 1:
	https://blog.crunchydata.com/blog/optimize-postgresql-server-performance		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		https://www.postgresql.org/docs/14/runtime-config-resource.html

← Older revision		Revision as of 00:08, 23 April 2022
Line 2:		Line 2:

	https://www.postgresql.org/docs/14/runtime-config-resource.html		https://www.postgresql.org/docs/14/runtime-config-resource.html
		+
		+	# Zabbix
		+	- https://youtu.be/xy_PImgRBT0

	```		```

← Older revision		Revision as of 00:07, 23 April 2022
Line 1:		Line 1:
	https://blog.crunchydata.com/blog/optimize-postgresql-server-performance		https://blog.crunchydata.com/blog/optimize-postgresql-server-performance
		+
		+	https://www.postgresql.org/docs/14/runtime-config-resource.html

	```		```

← Older revision		Revision as of 00:06, 23 April 2022
Line 45:		Line 45:
	```		```
	https://medium.com/dive-into-ml-ai/raising-shared-memory-limit-of-a-kubernetes-container-62aae0468a33		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.
		+	```

← Older revision		Revision as of 17:04, 22 April 2022
Line 44:		Line 44:
	name: hostpath		name: hostpath
	```		```
		+	https://medium.com/dive-into-ml-ai/raising-shared-memory-limit-of-a-kubernetes-container-62aae0468a33

@@ Line 24: / Line 24: @@
 lc_time = 'en_US.utf8'                          # locale for time formatting
 default_text_search_config = 'pg_catalog.english'
 ```

← Older revision		Revision as of 15:42, 22 April 2022
Line 1:		Line 1:
	https://blog.crunchydata.com/blog/optimize-postgresql-server-performance		https://blog.crunchydata.com/blog/optimize-postgresql-server-performance
		+
		+	```
		+	Query failed: ERROR: could not resize shared memory segment "/PostgreSQL.980262028" to 2097152 bytes: No space left on device
		+	```
		+
		+	```
		+	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'
		+	```