[Bug]: Performance degradation (5x slower) on chunk compression after update from TimescaleDB 2.9.0 to 2.11.1 #5902
Comments
|
After doing some digging, I believe that this PR #4821 is the one causing the performance regression.
|
|
@nikkhils This is my hypertable schema and indexes: CREATE TABLE device_readings (
time timestamp with time zone,
device_id integer,
metric_id integer,
value double precision NOT NULL,
metadata jsonb,
PRIMARY KEY (time, device_id, metric_id)
);
CREATE INDEX ON device_readings(time DESC);
CREATE INDEX ON device_readings (device_id, metric_id, time DESC); |
|
@ebreijo can you also please provide the compression segment by column details? |
|
Hi @ebreijo, Thanks for bringing this up. Help us with the following information.
Replace HYPERTABLE_NAME with the hypertable/s from the database.
|
As I mentioned above, I believe the change that caused this issue was introduced on PR #4821. Disabling the |
|
From the |
|
Hi @ebreijo , while we try to further investigate the performance regression you report, we can suggest the following workaround you could use in the meantime to make sure that you have a background compression job that performs compression with the GUC set to off (since you've noticed that improves the situation): Let us know if there are any issues with the suggested workaround |
|
Hi @ebreijo , Thanks for being patient on this. We are going through this issue in detail and trying to find a trade-off pattern between Seq Access and Random Access. Help us with some more information related to the postgres configuration and hardware. Information Required
|
|
|
@ebreijo , It would be interesting to compare the I/O stats (cache hits and misses) between Index path and Non-Index path compression.
please share us with the output of the above stats and time taken |
With disabled
|
|
@ebreijo , Thanks again for highlighting this issue. Brief History about Indexscan path introductionBefore 2.11 cost of compression can be expressed as cost(Seq_IO) + cost(Sort). Usually, cost of sorting depends on the table size and allocated work_mem. If table doesn't fit in the work_mem, in that case tuples will be written out to disk as temp files. Now, Cost of Sorting includes IO too, We have seen trends in our customer's system where large temp files created during large chunk compression leading to performance slowness. To optimize compression we have added an intelligence in our compression code to cherry pick relevant index that matches the compression settings configuration. This will nullify the cost(Sort) part as the entries are sorted in the indexes. In theory this sounds like a "Victory", until this issue :-) The Caveat:
The Analysis:
Heap_misses: 1597277 (Seq I/O)
Heap_misses: 1764482 (Random I/O) Idx_misses : 428894 (Seq I/O) Recommendation:System with high ingestion rate (hourly 10G to 12G data inserts) will put pressure on the shared buffer. Less buffer configuration will invite frequent page eviction leading to higher cache misses. Make shared buffer upto 40% of the RAM. |
|
I have follow your recommendation of setting the shared buffers to 40% of the RAM and I still don't see any improvements/benefits when compressing a chunk. It is still taking too much time to compress a chunk with the
I have also tested with the Please let me know if there is anything else you need from my end to figure out this issue. |
|
Hello @ebreijo, Thanks for sharing this detailed information. Based on the data, I also recommend keeping the setting |
|
Is there any update on the issue because i am also facing the same problem. I have chunk interval of 24 hours and average chunk size is 100GB. Compression policy also set to be applied on chunks older than 24 hours. Following is my schema and other details. Queries were execute in the same order as given below: Version Details:PostgreSQL 14.9 (Ubuntu 14.9-1.pgdg22.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
CREATE TABLE IF NOT EXISTS public.sensor_statuses
(
dtm timestamp with time zone NOT NULL,
device_id character(12) COLLATE pg_catalog."default" NOT NULL,
name character(20) COLLATE pg_catalog."default" NOT NULL,
event_type character(1) COLLATE pg_catalog."default",
status character(1) COLLATE pg_catalog."default",
fault character(1) COLLATE pg_catalog."default",
is_controlling boolean,
type character(1) COLLATE pg_catalog."default",
is_wireless boolean,
reading numeric,
rssi smallint,
bat smallint,
CONSTRAINT sensor_statuses_pkey1 PRIMARY KEY (dtm, device_id, name)
USING INDEX TABLESPACE sensorstatusesspace
);
SELECT create_hypertable(
'sensor_statuses', 'dtm', create_default_indexes => false,
chunk_time_interval => INTERVAL '24 hours', if_not_exists => TRUE);
CREATE INDEX IF NOT EXISTS ix_sensor_statuses
ON sensor_statuses("device_id", dtm DESC) TABLESPACE sensorstatusesspace;
SELECT attach_tablespace('sensorstatusesspace', 'sensor_statuses', if_not_attached => true);
ALTER TABLE sensor_statuses SET (timescaledb.compress, timescaledb.compress_segmentby = 'device_id');
SELECT add_compression_policy('sensor_statuses', INTERVAL '24 hours', if_not_exists => true);I was using timescaledb extension version 2.11.2 and today upgrade the version to 2.12.0. Tried compression using policy and manual both ways but same issue. During compression of first few chunks i did not noticed the problem and got compression rate of almost 97% but 4 days back i realized this issue. |
|
Few other details: Query: SELECT
pg_size_pretty(before_compression_total_bytes) as "before compression",
pg_size_pretty(after_compression_total_bytes) as "after compression"
FROM hypertable_compression_stats('sensor_statuses');Results:
Query: SELECT chunk_name,pg_size_pretty(total_bytes) as total_size FROM chunks_detailed_size('**HYPERTABLE_NAME**');Results:
postgres.config: Hardware: Ingestion Rate: |
|
Looks like the |
|
Yes, Are you still experiencing issues with slow compression times on the latest versions? |
|
@antekresic I'm not experiencing issues with slow compression on the version I'm running right now which is timescaledb 2.16.1 |
|
That's great! In that case, I'll close out this ticket and feel free to open another one if the issue arises again. Thanks! |
What type of bug is this?
Performance issue
What subsystems and features are affected?
Compression
What happened?
I have very recently updated our self-hosted TimescaleDB running on Postgres 13.11 from 2.9.0 to 2.11.1.
I noticed right after the hypertable compression policy started running that chunk compression on TimescaleDB 2.11.1 is very slow compared to chunk compression on TimescaleDB 2.9.0.
Setup:
Findings from Postgres logs provided below
CALL _timescaledb_internal.policy_compression_executebut on Timescale DB 2.11.1 one chunk compression is taking around 32 minutes when looking at the timestamps between two consecutiveCALL _timescaledb_internal.policy_compression_execute.I also have provided a screenshot of our TimescaleDB data disk usage in the last 4 days
Please let me know if there is any other information I can provide from my end. Also advice if there is any workaround for this issue.
TimescaleDB version affected
2.11.1
PostgreSQL version used
13.11
What operating system did you use?
Ubuntu 22.04.2 LTS (GNU/Linux 5.19.0-1029-aws x86_64)
What installation method did you use?
Deb/Apt
What platform did you run on?
On prem/Self-hosted
Relevant log output and stack trace
How can we reproduce the bug?
The text was updated successfully, but these errors were encountered: