Performance issues from tstune settings when running timescaledb-docker on magnetic storage

[matthock]

When running timescaledb-docker on a magnetic hard drive/array thereof, performance rapidly degrades even with relatively small databases. In my case, I had a database that was only about 200mb, but it was encountering several second latency on a workload of only a few reads and writes a second with postgres' CPU core spiking up to 100% and staying there; this was on an 8 disk SAS RAID 6 array on a Dell PERC H730, so the hardware itself should have been quite capable.

After doing some diagnostics, I found that the effective_io_concurrency and random_page_cost settings of postgresql.conf were significantly out of line with the recommended values for such an array on magnetic storage, and rather matched the values one would normally recommend for SSDs. When I changed the values back to the defaults and restarted postgres, it immediately fixed the performance issues I was seeing - CPU load dropped to under 10% and query latency was nearly zero.

Having the SSD values as the default seems reasonable as I would expect most people who care about performance are using SSDs, but I found no way to set an environment variable to override it during image startup for those of us trying to support legacy hardware short of entirely disabling tuning. Investigating timescaledb-tune seems to corroborate that it can't be overridden there as tstune assumes the SSD values when it is run with no flags to override it.

I would like to propose that flags for overriding each of these values be added to tstune and then the ability to set these be added as environment variables to timescaledb-docker.

Related: timescale/timescaledb-tune#70