random_page_cost and effective_io_concurrency are hardcoded to SSD values

[jflambert]

Using pgtune, I get random_page_cost/effective_io_concurrency recommendations of 1.1/200 for SSD and 4/2 for HDD. I see these recommendations in other tools or websites as well.

timescaledb-tune hardcodes its recommendations to 1.1/200 (SSD)

How important is it for these two settings to be set according to your drive type?

[matthock]

I'm going to second this issue based on behavior I've seen in my own environment. It works fine when operating on an SSD, but when operating on a magnetic drive or a raid array thereof, the system will completely grind to a halt with relatively small amounts of data (200-300mb), resulting in several second query latency as psql spikes cores to 100% (for context, this is using the timescaledb docker container which is automatically running timescaledb-tune on initialization). When I go in to the configuration and hand change these values to what pgtune would recommend for the drives, the CPU usage drops to single digits and the queries become nearly instantaneous. The rest of the tuning seems fine, but these particular values will absolutely destroy performance on a magnetic drive.

[Kazmirchuk]

probably this can be worked around by e.g. ALTER SYSTEM, but I'd love to have tstune detect the storage type SSD/HDD automatically

[jflambert]

hi @matthock are you still concerned by this issue? Thanks to @Kazmirchuk bumping up my old issue, I read your other issue on timescaledb-docker and realized that having the wrong values for my deployments probably explains a LOT of bad stuff.

And even then, according to Bruce Momjian 16 would be a better number for HDD effective_io_concurrency today
https://www.postgresql.org/message-id/20210422195232.GA25061%40momjian.us

taken from tstune's own codebase:

timescaledb-tune/pkg/pgtune/misc.go

Line 30 in 154501b

// (the upper limit is 1000. For the SSDs we'll follow up the wise man's advice here:

The only question I have is if there's also a penalty for using HDD values on SSD (or just sacrificing some gains)

[matthock]

@jflambert Yes, I still run into this. I've got workarounds in place where I'm having to hook in and overwrite the values with ALTER SYSTEM after initial setup... but it would be nice if it worked out of the box, especially since I've seen others get caught by the same and it's a HUGE gotcha if you're doing anything with remotely legacy hardware!

I've got mine set up to automatically set up the HDD values. You lose a bit of performance on SSDs, but it's not a huge amount in my experience (low double digit percent), as opposed to the penalty of using SSD values on an HDD being several orders of magnitude degradation in performance.

[jflambert]

@matthock happy to share, I run this in an init-container post timescaledb-tune (which values do you use for random_page_cost and effective_io_concurrency?)

# timescaledb-tune uses SSD config by default. Use HDD config if rotational device is found.
ROTA=$(lsblk -o ROTA,MOUNTPOINTS | grep postgresql | awk '{print $1}')
if [ "$ROTA" == "1" ]; then
  sed -i '/^effective_io_concurrency/d' $PGDATA/postgresql.conf
  sed -i '/^random_page_cost/d' $PGDATA/postgresql.conf
  echo -e 'effective_io_concurrency=2 #HDD\nrandom_page_cost=4 #HDD' >> $PGDATA/postgresql.conf
fi

There are two ways timescaledb-tune can handle this.

1. "automatically" detect HDD/SSD and use appropriate values, but this brings up a ton of platform-specific inconsistencies (from what I read about detecting disk type on virtual servers)
2. simply provide a --use-hdd-settings or similar switch to override SSD defaults.

If #2 is acceptable to @jnidzwetzki or @svenklemm (i.e. the onus is on the user to determine their drive type) then I could pitch in a quick PR (I've done a few before).

[svenklemm]

Having an option to overwrite the builtins is fine. Maybe use the -profile switch to trigger this

[jflambert]

Having an option to overwrite the builtins is fine. Maybe use the -profile switch to trigger this

Interesting. I looked into this --profile flag, and while cool, not sure if it applies here. I don't understand if profiles can overlap (for example providing both promscale and hdd).