timescale/timescaledb-tune

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 install this repo:

$ go install github.com/timescale/timescaledb-tune/cmd/timescaledb-tune@main

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

By default, timescaledb-tune provides recommendations for a typical timescaledb workload. The --profile flag can be used to tailor the recommendations for other workload types. Currently, the only non-default profile is "promscale". The TSTUNE_PROFILE environment variable can also be used to affect this behavior.

$ timescaledb-tune --profile promscale

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.

Releasing

Please follow the instructions here to publish a release of this tool.