timescaledb-tune
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.
Getting started
You need the Go runtime (1.12+) installed, then simply go get
this repo:
$ go get github.com/timescale/timescaledb-tune/cmd/timescaledb-tune
It is also available as a binary package on a variety systems using
Homebrew, yum
, or apt
. Search for timescaledb-tools
.
Using timescaledb-tune
By default, timescaledb-tune
attempts to locate your postgresql.conf
file for parsing by using heuristics based on the operating system, so the
simplest invocation would be:
$ timescaledb-tune
You'll then be given a series of prompts that require minimal user input to make sure your config file is up to date:
Using postgresql.conf at this path:
/usr/local/var/postgres/postgresql.conf
Is this correct? [(y)es/(n)o]: y
Writing backup to:
/var/folders/cr/zpgdkv194vz1g5smxl_5tggm0000gn/T/timescaledb_tune.backup201901071520
shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = 'timescaledb'
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 8.00 GB of available memory and 4 CPUs for PostgreSQL 11
Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 2GB
effective_cache_size = 6GB
maintenance_work_mem = 1GB
work_mem = 26214kB
Is this okay? [(y)es/(s)kip/(q)uit]:
If you have moved the configuration file to a different location, or
auto-detection fails (file an issue please!), you can provide the location
with the --conf-path
flag:
$ timescaledb-tune --conf-path=/path/to/postgresql.conf
At the end, your postgresql.conf
will be overwritten with the changes
that you accepted from the prompts.
Other invocations
If you want recommendations for a specific amount of memory and/or CPUs:
$ timescaledb-tune --memory="4GB" --cpus=2
If you want to set a specific number of background workers (timescaledb.max_background_workers
):
$ timescaledb-tune --max-bg-workers=16
If you have a dedicated disk for WAL, or want to specify how much of a shared disk should be used for WAL:
$ timescaledb-tune --wal-disk-size="10GB"
If you want to accept all recommendations, you can use --yes
:
$ timescaledb-tune --yes
If you just want to see the recommendations without writing:
$ timescaledb-tune --dry-run
If there are too many prompts:
$ timescaledb-tune --quiet
And if you want to skip all prompts and get quiet output:
$ timescaledb-tune --quiet --yes
And if you want to append the recommendations to the end of your conf file instead of in-place replacement:
$ timescaledb-tune --quiet --yes --dry-run >> /path/to/postgresql.conf
Restoring backups
timescaledb-tune
makes a backup of your postgresql.conf
file each time
it runs (without the --dry-run
flag) in your temp directory. If you find
that the configuration given is not working well, you can restore a backup
by using the --restore
flag:
$ timescaledb-tune --restore
Using postgresql.conf at this path:
/usr/local/var/postgres/postgresql.conf
Is this correct? [(y)es/(n)o]: y
Available backups (most recent first):
1) timescaledb_tune.backup201901222056 (14 hours ago)
2) timescaledb_tune.backup201901221640 (18 hours ago)
3) timescaledb_tune.backup201901221050 (24 hours ago)
4) timescaledb_tune.backup201901211817 (41 hours ago)
Use which backup? Number or (q)uit: 1
Restoring 'timescaledb_tune.backup201901222056'...
success: restored successfully
Contributing
We welcome contributions to this utility, which like TimescaleDB is released under the Apache2 Open Source License. The same Contributors Agreement applies; please sign the Contributor License Agreement (CLA) if you're a new contributor.