I am attempting to insert data into a timescaledb hypertable in bulk. Regardless of what I try, the memory usage grows gradually until the server process is killed due to a lack of memory. I have observed this with datasets as small as 1.6 million rows on a server where 128 GB of RAM is available to postgres/timescaledb, so I must be doing something wrong. Doing exactly the same inserts into a table that is not a hypertable works just fine, so the problem must be related to timescaledb.
What I am trying to do
The table I wish to insert into is defined as follows:
CREATE TABLE test ( gid BIGINT NOT NULL, location_ GEOGRAPHY(POINT), -- about 15 other VARCHAR and BIGINT columns omitted for brevity far_end_gid BIGINT NOT NULL, day_partition_key DATE NOT NULL, PRIMARY KEY (gid, far_end_gid, day_partition_key) ); SELECT create_hypertable( 'test', 'day_partition_key', chunk_time_interval => INTERVAL '1 day');
The data to be inserted is in another table in the database (a temp table created filled by a
\COPY operation); this other table has the same fields as the target hypertable except that some fields require some parsing (converting strings to date etc.). The
INSERT query that fails is
INSERT INTO topology_test SELECT tobigintornull(gid) AS gid_as_int, -- about 15 other VARCHAR and BIGINT columns omitted CAST(CAST(REPLACE(far_end_gid, ',', '.') AS DOUBLE PRECISION) AS BIGINT), CAST(tobigintornull(far_end_cell_id) AS BIGINT), TO_DATE(day_partition_key, 'YYYYMMDD') FROM test_temp_table WHERE tobigintornull(gid) is not null and tobigintornull(REPLACE(far_end_gid, ',', '.')) is not null and day_partition_key is not null ON CONFLICT DO NOTHING;
ON CONFLICT part is intended to drop duplicate primary keys silently and the function
tobigintornull does what its name suggests: it converts the input into a bigint if possible, and if not, returns null (this helps drop rows that cannot be parsed). Its definition is
CREATE OR REPLACE FUNCTION tobigintornull(text) RETURNS BIGINT AS $$ DECLARE x BIGINT; BEGIN x = $1::BIGINT; RETURN x; EXCEPTION WHEN others THEN RETURN NULL; END; $$ STRICT LANGUAGE plpgsql IMMUTABLE;
The definition for
DOUBLE PRECISION inputs is similar. Note that only a small fraction of rows (definitely less than 1%) are dropped by the
The full data set is 593 million rows, and it is not ordered in any way. Suspecting that the lack of ordering of the input was part of the problem, I have created a subset of the data containing 1.6 million rows where all rows have the same value for
day_partition_key (that subset should be perfectly ordered from timescaledb's point of view).
The problem manifests itself as postgres's memory usage increasing gradually until the full 128 GB available to the database is used (that takes about five minutes). After 100% memory usage for a minute or so, the insert query crashes. The logs show the following:
timescaledb_1 | 2021-08-23 11:56:50.727 UTC  LOG: server process (PID 1231) was terminated by signal 9: Killed timescaledb_1 | 2021-08-23 11:56:50.727 UTC  DETAIL: Failed process was running: INSERT INTO test timescaledb_1 | SELECT (...) timescaledb_1 | 2021-08-23 11:56:50.727 UTC  LOG: terminating any other active server processes timescaledb_1 | 2021-08-23 11:56:50.741 UTC  WARNING: terminating connection because of crash of another server process timescaledb_1 | 2021-08-23 11:56:50.741 UTC  DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. timescaledb_1 | 2021-08-23 11:56:50.741 UTC  HINT: In a moment you should be able to reconnect to the database and repeat your command. timescaledb_1 | 2021-08-23 11:56:50.744 UTC  WARNING: terminating connection because of crash of another server process timescaledb_1 | 2021-08-23 11:56:50.744 UTC  DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. timescaledb_1 | 2021-08-23 11:56:50.744 UTC  HINT: In a moment you should be able to reconnect to the database and repeat your command. timescaledb_1 | 2021-08-23 11:56:50.771 UTC  LOG: all server processes terminated; reinitializing timescaledb_1 | 2021-08-23 11:56:51.371 UTC  LOG: database system was interrupted; last known up at 2021-08-23 11:50:40 UTC timescaledb_1 | 2021-08-23 11:56:51.711 UTC  LOG: database system was not properly shut down; automatic recovery in progress timescaledb_1 | 2021-08-23 11:56:51.720 UTC  LOG: redo starts at 25A/16567EF8 timescaledb_1 | 2021-08-23 11:56:51.788 UTC  LOG: invalid record length at 25A/165C1A30: wanted 24, got 0 timescaledb_1 | 2021-08-23 11:56:51.788 UTC  LOG: redo done at 25A/165C19D0 timescaledb_1 | 2021-08-23 11:56:51.893 UTC  LOG: database system is ready to accept connections timescaledb_1 | 2021-08-23 11:56:52.039 UTC  LOG: TimescaleDB background worker launcher connected to shared catalogs
No rows are present in the target table after the db recovers, obviously.
What I have tried to fix it
- I have reduced the size of the data set from 593 M rows to 1.6 M rows, ensuring that the subset has only a single value for the date column used for chunking (
day_partition_key). The result is exactly the same.
- Following the discussion at https://github.com/timescale/timescaledb/issues/643, I have changed the timescaledb config with
SET timescaledb.max_open_chunks_per_insert=1;. The problem is still exactly the same.
- I have tried creating another target table without making it a hypertable. Inserting the 1.6 M row subset then works just fine. I expect the full set would work to, but I haven't taken the time to do that.
Versions, hardware and configuration
The docker image timescale/timescaledb-postgis:latest-pg13 (bf76e5594c98) was used to run timescaledb. It contains:
- PostgreSQL 13.3 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
- Timescaledb 2.3.0
- POSTGIS="2.5.5" [EXTENSION] PGSQL="130" GEOS="3.8.1-CAPI-1.13.3" PROJ="Rel. 7.1.1, September 1st, 2020" GDAL="GDAL 3.1.4, released 2020/10/20" LIBXML="2.9.10" LIBJSON="0.15" LIBPROTOBUF="1.3.3" RASTER
The docker container is limited to 16 cores and 128 GB of memory. For the tests referred to above, I used configuration parameters suggested by https://pgtune.leopard.in.ua/#/ for a data warehouse with my parameters, except that I lowered the available memory I provided to pgtune to 64 GB, hoping that would result in a config where the db would use less memory (I have also tried settings that are recommended for 128 GB of memory, the result is the same). The settings are:
max_connections = 30 shared_buffers = 16GB effective_cache_size = 48GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 500 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 34952kB min_wal_size = 4GB max_wal_size = 16GB max_worker_processes = 16 max_parallel_workers_per_gather = 8 max_parallel_workers = 16 max_parallel_maintenance_workers = 4