[Bug]: Bad performance / Very high CPU load when using INSERT DO UPDATE ON CONFLICT on compressed chunks #5801

mblsf · 2023-06-19T13:40:38Z

What type of bug is this?

Performance issue

What subsystems and features are affected?

Compression

What happened?

When using the new feature INSERT DO UPDATE ON CONFLICT on compressed chunks that contain a lot of data. the performance is really bad (Processing 100 records takes 4 seconds instead of 40 ms) and the CPU load on postgres is always very high during this operation.

TimescaleDB version affected

2.11.0

PostgreSQL version used

14.8

What operating system did you use?

Ubuntu 22.04 x64

What installation method did you use?

Docker

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

No response

How can we reproduce the bug?

Perform INSERT DO UPDATE ON CONFLICT on compressed chunks that contain a lot of data

lkshminarayanan · 2023-06-19T15:06:19Z

Hi @mblsf,

Thank you for filing the issue!
I tested INSERT DO UPDATE ON CONFLICT on a hypertable that had around 50M rows.
Without compression, the statement took around 23ms and with compression it took around 380ms.
This increase in time is expected due to the overhead of compression.

But I was not able to observe the level of performance degradation that you mention in the bug.
It could be the case that this degradation occurs only under certain conditions.
So, can you provide more information about your setup where you faced this issue?
Hypertable definition, number of rows in the hypertable, the complete query with which you faced the issue - anything would be helpful.

mblsf · 2023-06-19T16:28:10Z

Forget to mention that we are using batch inserts. The values to insert can be for different chunks. For the definition of hypertable definition I need to wait for a colleague to return. Query locks like this:

INSERT INTO values(key, ts_utc, value, state, id) VALUES
('key1', '2023-01-13 14:23:43.580076', 'value1', 'new', get_id()),
('key2', '2023-02-12 16:18:38.780324', 'value2', 'changed', get_id()),
....
('key100', '2023-03-01 10:34:25.674424', 'value100', 'deleted', get_id())
ON CONFLICT (key, ts_utc) DO UPDATE SET
value = EXCLUDED.value,
state = EXCLUDED.state;

get_id() is used to generate a unique key

sb230132 · 2023-06-20T14:08:19Z

Hi @mblsf
If the INSERT is going to affect all segments in compressed chunk, then this INSERT operation will end up decompressing everything in compressed chunk which will be time consuming.

Prior 2.11.0 INSERT on compressed chunk will compress only the new row and insert into compressed chunk. From 2.11.0
we extract (decompress) all those segments which will be affected and then do the INSERT operation. Thus you see a performance hit.

Anyways, we need details on hypertable, and compression settings like what are the segmentby columns to understand better on the issue.

mblsf · 2023-07-03T12:23:50Z

Here is the requested information. Hope it's complete. If not, please reply.

CREATE TABLE IF NOT EXISTS warp_tsa_core.signal_raw_values
(
signal_key VARCHAR(50) NOT NULL ,
raw_ts_utc TIMESTAMP NOT NULL ,
raw_dupl_id INTEGER NOT NULL DEFAULT 0,
raw_value_num FLOAT8,
raw_value_string VARCHAR(1000),
raw_state_name VARCHAR(1000),
raw_good_flag BOOLEAN,
load_id INTEGER NULL,
CONSTRAINT signal_raw_values_key PRIMARY KEY
( signal_key, raw_ts_utc ,raw_dupl_id )
);

-- convert table to hypertable
SELECT warp_tsa_core.create_hypertable
(relation => 'warp_tsa_core.signal_raw_values'
,time_column_name =>'raw_ts_utc'
,chunk_time_interval => INTERVAL '1 day'
,partitioning_column => 'signal_key'
,number_partitions => 1 -- 2 when using 2 data nodes; 4 when using 4 data nodes
,if_not_exists => TRUE
,migrate_data => TRUE
);

-- Data older than 400 days in table signal_raw_values should be deleted automatically by timescaledb
SELECT warp_tsa_core.add_retention_policy('warp_tsa_core.signal_raw_values', INTERVAL '400 days');

-- Activates compression on signal_raw_values for data older than 30 days
ALTER TABLE warp_tsa_core.signal_raw_values SET (
timescaledb.compress,
timescaledb.compress_orderby = 'raw_ts_utc,raw_dupl_id',
timescaledb.compress_segmentby = 'signal_key'
);

-- Set compress policy to automatic compress chunks older than 30 days
SELECT warp_tsa_core.add_compression_policy(
'warp_tsa_core.signal_raw_values' -- hypertable
,INTERVAL '30d' -- after interval compress
,true -- no error (only warning) if compression policy already exists
);

RafiaSabih · 2023-07-18T08:46:02Z

@mblsf Please provide the queries you are facing issues with also.

mblsf · 2023-07-25T12:54:36Z

This query inserts 153 new entries, it can be up to 1000 entries per query:

INSERT INTO signal_raw_values(signal_key, raw_ts_utc, raw_value_num, raw_value_string, raw_state_name, raw_good_flag, load_id) VALUES (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id()), (%s, %s, %s, %s, %s, %s, get_load_id())ON CONFLICT (signal_key, raw_ts_utc, raw_dupl_id) DO UPDATE SET raw_value_num = EXCLUDED.raw_value_num, raw_value_string = EXCLUDED.raw_value_string, raw_state_name = EXCLUDED.raw_state_name, raw_good_flag = EXCLUDED.raw_good_flag

We don't have any problems when executing this query with uncompressed chunks.

S-imo-n · 2023-08-07T07:49:05Z

Any updates yet? Do you need more information?

mblsf · 2023-08-08T11:53:05Z

Maybe our chunk size is too large. What's the recommended uncompressed chunk size for TimescaleDB?

mblsf · 2023-09-04T11:48:31Z

@lkshminarayanan, @sb230132, @RafiaSabih Can someone of you please have a look at this issue again? I think we've recently provided all necessary information.

mkindahl · 2023-09-11T14:56:05Z

@mblsf Thank you for providing the information, especially the segment-by and order-by columns that you use for compression. Given this information, I do not think that a large chunk size is is the reason for bad performance.

Could you run the following queries to get some statistics on the columns?

SELECT schemaname, tablename, attname, n_distinct
  FROM pg_stats
 WHERE schemaname = 'warp_tsa_core'
   AND tablename = 'signal_raw_values'
   AND inherited;

sb230132 · 2023-09-12T09:00:45Z

Here is the issue which should fix the performance in INSERTS ... ON CONFLICT queries on compressed chunks.
#6063

The INSERT query with ON CONFLICT on compressed chunk does a heapscan to verify unique constraint violation. This patch improves the performance by doing an indexscan on compressed chunk, to fetch matching records based on segmentby columns. These matching records are inserted into uncompressed chunk, then unique constraint violation is verified. Since index on compressed chunk has only segmentby columns, we cannot do a point lookup considering orderby columns as well. This patch thus will result in decompressing matching records only based on segmentby columns. Fixes #6063 Fixes #5801

mblsf · 2023-09-12T15:51:32Z

@mkindahl Here are the results for your query, if still relevant:

"warp_tsa_core" "signal_raw_values" "signal_key" 22309
"warp_tsa_core" "signal_raw_values" "raw_ts_utc" 1.261464e+06
"warp_tsa_core" "signal_raw_values" "raw_dupl_id" 1
"warp_tsa_core" "signal_raw_values" "raw_value_num" 143824
"warp_tsa_core" "signal_raw_values" "raw_value_string" 708
"warp_tsa_core" "signal_raw_values" "raw_state_name" 113
"warp_tsa_core" "signal_raw_values" "raw_good_flag" 2
"warp_tsa_core" "signal_raw_values" "load_id" 14295

The INSERT query with ON CONFLICT on compressed chunk does a heapscan to verify unique constraint violation. This patch improves the performance by doing an indexscan on compressed chunk, to fetch matching records based on segmentby columns. These matching records are inserted into uncompressed chunk, then unique constraint violation is verified. Since index on compressed chunk has only segmentby columns, we cannot do a point lookup considering orderby columns as well. This patch thus will result in decompressing matching records only based on segmentby columns. Fixes #6063 Fixes #5801

mkindahl · 2023-10-27T08:06:06Z

@mkindahl Here are the results for your query, if still relevant:

"warp_tsa_core" "signal_raw_values" "signal_key" 22309 "warp_tsa_core" "signal_raw_values" "raw_ts_utc" 1.261464e+06 "warp_tsa_core" "signal_raw_values" "raw_dupl_id" 1 "warp_tsa_core" "signal_raw_values" "raw_value_num" 143824 "warp_tsa_core" "signal_raw_values" "raw_value_string" 708 "warp_tsa_core" "signal_raw_values" "raw_state_name" 113 "warp_tsa_core" "signal_raw_values" "raw_good_flag" 2 "warp_tsa_core" "signal_raw_values" "load_id" 14295

Thank you. It seems you picked a good segment-by key, and I just wanted to eliminate that you had picked a bad segment-by key.

mblsf · 2023-10-27T08:39:20Z

@mkindahl You're welcome. Please let me know when there are any updates related to this issue.

S-imo-n · 2023-11-20T18:56:19Z

Hi @mkindahl any updates?

aarondglover · 2023-12-21T04:21:34Z

Using 2.13 on PG15 in AIVEN/MST.

I have not measured the speed difference of INSERT ON CONFLICT etc but I what I have observed is that UNIQUE INDEXES are not observed. It allows for duplicate rows to be inserted as per my recent comments in #6302

Re comments from @sb230132 :

Prior 2.11.0 INSERT on compressed chunk will compress only the new row and insert into compressed chunk. From 2.11.0
we extract (decompress) all those segments which will be affected and then do the INSERT operation. Thus you see a performance hit.

I'm not sure the decompression is occurring prior to the insertion into a compressed chunk - I think this because the unique index is not being triggered and I am ending up with duplicate rows. One row in the compressed chunk, and one duplicate in the uncompressed part of the chunk. Once the duplicate row is in there, decompression of chunk into the uncompressed chunk then fails because the UNIQUE INDEX borks on the duplicate row.

aarondglover · 2023-12-21T04:22:55Z

Is DML on compressed chunks fundamentally broken??? It would seems so to me 😢

svenklemm · 2023-12-21T11:25:36Z

There is a bug preventing this from happening with unique expression indexes, for non-expression indexes it should work in 2.13.
See #6454

antekresic · 2024-12-09T12:00:35Z

INSERT ON CONFLICT DO UPDATE received optimizations recently that should have helped with this. They are available with version 2.17

Have you had the chance to try it out?

S-imo-n · 2024-12-09T12:32:47Z

It worked, thanks :)

mblsf added the bug label Jun 19, 2023

lkshminarayanan added compression performance waiting-for-author labels Jun 19, 2023

timescale-automation removed the waiting-for-author label Jun 19, 2023

lkshminarayanan added the waiting-for-author label Jun 20, 2023

timescale-automation removed the waiting-for-author label Jul 3, 2023

sb230132 mentioned this issue Sep 12, 2023

Fix INSERT query performance on compressed chunk #6061

Closed

gayyappan assigned antekresic Dec 6, 2023

antekresic added the 2024-Q1 label Dec 18, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

[Bug]: Bad performance / Very high CPU load when using INSERT DO UPDATE ON CONFLICT on compressed chunks #5801

[Bug]: Bad performance / Very high CPU load when using INSERT DO UPDATE ON CONFLICT on compressed chunks #5801

mblsf commented Jun 19, 2023

lkshminarayanan commented Jun 19, 2023

mblsf commented Jun 19, 2023 •

edited

Loading

sb230132 commented Jun 20, 2023

mblsf commented Jul 3, 2023

RafiaSabih commented Jul 18, 2023

mblsf commented Jul 25, 2023 •

edited

Loading

S-imo-n commented Aug 7, 2023

mblsf commented Aug 8, 2023

mblsf commented Sep 4, 2023

mkindahl commented Sep 11, 2023 •

edited

Loading

sb230132 commented Sep 12, 2023

mblsf commented Sep 12, 2023

mkindahl commented Oct 27, 2023

mblsf commented Oct 27, 2023

S-imo-n commented Nov 20, 2023

aarondglover commented Dec 21, 2023

aarondglover commented Dec 21, 2023

svenklemm commented Dec 21, 2023

antekresic commented Dec 9, 2024

S-imo-n commented Dec 9, 2024

[Bug]: Bad performance / Very high CPU load when using INSERT DO UPDATE ON CONFLICT on compressed chunks #5801

[Bug]: Bad performance / Very high CPU load when using INSERT DO UPDATE ON CONFLICT on compressed chunks #5801

Comments

mblsf commented Jun 19, 2023

What type of bug is this?

What subsystems and features are affected?

What happened?

TimescaleDB version affected

PostgreSQL version used

What operating system did you use?

What installation method did you use?

What platform did you run on?

Relevant log output and stack trace

How can we reproduce the bug?

lkshminarayanan commented Jun 19, 2023

mblsf commented Jun 19, 2023 • edited Loading

sb230132 commented Jun 20, 2023

mblsf commented Jul 3, 2023

RafiaSabih commented Jul 18, 2023

mblsf commented Jul 25, 2023 • edited Loading

S-imo-n commented Aug 7, 2023

mblsf commented Aug 8, 2023

mblsf commented Sep 4, 2023

mkindahl commented Sep 11, 2023 • edited Loading

sb230132 commented Sep 12, 2023

mblsf commented Sep 12, 2023

mkindahl commented Oct 27, 2023

mblsf commented Oct 27, 2023

S-imo-n commented Nov 20, 2023

aarondglover commented Dec 21, 2023

aarondglover commented Dec 21, 2023

svenklemm commented Dec 21, 2023

antekresic commented Dec 9, 2024

S-imo-n commented Dec 9, 2024

mblsf commented Jun 19, 2023 •

edited

Loading

mblsf commented Jul 25, 2023 •

edited

Loading

mkindahl commented Sep 11, 2023 •

edited

Loading