TimescaleDB 时序数据库 之二 timescaledb-tune

os: ubuntu 16.04
db: postgresql 10.6

A tool for tuning your TimescaleDB for better performance by adjusting settings to match your system’s CPU and memory resources.
https://www.timescale.com/

timescaledb-tune is a program for tuning a TimescaleDB database to perform its best based on the host’s resources such as memory and number of CPUs. It parses the existing postgresql.conf file to ensure that the TimescaleDB extension is appropriately installed and provides recommendations for memory, parallelism, WAL, and other settings.

版本

# lsb_release -a
No LSB modules are available.
Distributor ID:	Ubuntu
Description:	Ubuntu 16.04.5 LTS
Release:	16.04
Codename:	xenial
#
# su - postgres
$ psql -c "select version();"
                                                     version                                                     
-----------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.11) 5.4.0 20160609, 64-bit
(1 row)

下载安装

# apt install golang-1.10 golang-1.10-go
# su - postgres
$ go get github.com/timescale/timescaledb-tune/cmd/timescaledb-tune
$ cd ./go/bin/
$ ls -l 
total 3000
-rwxrwxr-x 1 postgres postgres 3070633 Apr  9 14:35 timescaledb-tune

建议可以好好看看 ~/go/src 下面的代码.

使用

$ ~/go/bin/timescaledb-tune -version
timescaledb-tune 0.5.0 (linux amd64)

$ ~/go/bin/timescaledb-tune -conf-path /data/pgsql-10/data/postgresql.conf

会有如下的输出,需要手动输入yes确认.

Using postgresql.conf at this path:
/data/pgsql-10/data/postgresql.conf

Writing backup to:
/tmp/timescaledb_tune.backup201904091449

success: shared_preload_libraries is set correctly

Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]: yes
Recommendations based on 984.92 MB of available memory and 4 CPUs for PostgreSQL 10

Memory settings recommendations
Current:
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
effective_cache_size = 756420kB
maintenance_work_mem = 126070kB
work_mem = 1260kB
Is this okay? [(y)es/(s)kip/(q)uit]: yes
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 = 4
max_worker_processes = 11
max_parallel_workers_per_gather = 2
max_parallel_workers = 4
Is this okay? [(y)es/(s)kip/(q)uit]: yes
success: parallelism settings will be updated

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

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

如果想还原之前的参数值,可以使用 --restore

$ ~/go/bin/timescaledb-tune --restore

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