Zabbix 5 and TimescaleDB Compression

[GOID]

Hi!

I have clear installation:

• Ubuntu 20.04
• Zabbix 5.0.3
• PostgreSQL 12.4
• timescaledb-postgresql-12 1.7.4

Housekeeping config in attachments.

Chunk interval configured 86400 seconds (1 day) and chunk sizes:

echo "select chunk_table, partitioning_columns, ranges FROM chunk_relation_size_pretty('history_uint')" | sudo -u postgres psql zabbix
chunk_table | partitioning_columns | ranges
-------------------------------------------+----------------------+---------------------------------
_timescaledb_internal._hyper_2_2_chunk | {clock} | {"['1599868800','1599955200')"}
_timescaledb_internal._hyper_2_8_chunk | {clock} | {"['1599955200','1600041600')"}
_timescaledb_internal._hyper_2_11_chunk | {clock} | {"['1600041600','1600128000')"}
_timescaledb_internal._hyper_2_4136_chunk | {clock} | {"['1600128000','1600214400')"}
_timescaledb_internal._hyper_2_4170_chunk | {clock} | {"['1600214400','1600300800')"}
_timescaledb_internal._hyper_2_4174_chunk | {clock} | {"['1600300800','1600387200')"}
_timescaledb_internal._hyper_2_4178_chunk | {clock} | {"['1600387200','1600473600')"}
_timescaledb_internal._hyper_2_4182_chunk | {clock} | {"['1600473600','1600560000')"}
_timescaledb_internal._hyper_2_4186_chunk | {clock} | {"['1600560000','1600646400')"}

echo "select chunk_table, table_size, index_size, total_size FROM chunk_relation_size_pretty('history_uint')" | sudo -u postgres psql zabbix
chunk_table | table_size | index_size | total_size
-------------------------------------------+------------+------------+------------
_timescaledb_internal._hyper_2_2_chunk | 479 MB | 460 MB | 939 MB
_timescaledb_internal._hyper_2_8_chunk | 6449 MB | 9495 MB | 16 GB
_timescaledb_internal._hyper_2_11_chunk | 4788 MB | 5981 MB | 11 GB
_timescaledb_internal._hyper_2_4136_chunk | 4386 MB | 5704 MB | 10090 MB
_timescaledb_internal._hyper_2_4170_chunk | 6045 MB | 7740 MB | 13 GB
_timescaledb_internal._hyper_2_4174_chunk | 6042 MB | 7704 MB | 13 GB
_timescaledb_internal._hyper_2_4178_chunk | 6049 MB | 7736 MB | 13 GB
_timescaledb_internal._hyper_2_4182_chunk | 6050 MB | 7761 MB | 13 GB
_timescaledb_internal._hyper_2_4186_chunk | 2166 MB | 2878 MB | 5045 MB

Configured default compression after - 7day. I expecting _hyper_2_2_chunk AND _hyper_2_8_chunk must be compressed and free space released.
But no one chunk compressed and no free space released on disk.

In source code of zabbix no code with compress_chunk() TimescaleDB function.

In manual https://www.zabbix.com/documentation...db_compression nothing about additional configuring.

Can somebody explain to me how it works and when compression will by executed ?

[Glencoe]

You can verify what chunks are compressed by checking timescaledb_information.compressed_chunk_stats: https://docs.timescale.com/latest/ap...ed_chunk_stats

[GOID]

On moment when post created no one "compressed' state in timescaledb_information.compressed_chunk_stats.
But today:

echo "select chunk_table, table_size, index_size, total_size FROM chunk_relation_size_pretty('history_uint')" | sudo -u postgres psql zabbix
chunk_table | table_size | index_size | total_size
-------------------------------------------+------------+------------+------------
_timescaledb_internal._hyper_2_2_chunk | 0 bytes | 8192 bytes | 8192 bytes
_timescaledb_internal._hyper_2_8_chunk | 6449 MB | 9495 MB | 16 GB
...

echo "SELECT * FROM timescaledb_information.compressed_chunk_stats;" | sudo -u postgres psql zabbix
hypertable_name | chunk_name | compression_status | uncompressed_heap_bytes | uncompressed_index_bytes | uncompressed_toast_bytes | uncompressed_total_bytes | compressed_heap_bytes | compressed_index_bytes | compressed_toast_bytes | compressed_total_bytes
-----------------+-------------------------------------------+--------------------+-------------------------+--------------------------+--------------------------+--------------------------+-----------------------+------------------------+------------------------+------------------------
history_str | _timescaledb_internal._hyper_5_4_chunk | Compressed | 8192 bytes | 16 kB | 0 bytes | 24 kB | 8192 bytes | 16 kB | 8192 bytes | 32 kB
history_text | _timescaledb_internal._hyper_4_3_chunk | Compressed | 8192 bytes | 16 kB | 64 kB | 88 kB | 8192 bytes | 16 kB | 64 kB | 88 kB
history | _timescaledb_internal._hyper_1_1_chunk | Compressed | 1856 kB | 1328 kB | 0 bytes | 3184 kB | 72 kB | 16 kB | 312 kB | 400 kB
history_uint | _timescaledb_internal._hyper_2_2_chunk | Compressed | 479 MB | 460 MB | 0 bytes | 939 MB | 1280 kB | 344 kB | 51 MB | 52 MB
...

echo "SELECT * FROM timescaledb_information.compressed_hypertable_stat s;" | sudo -u postgres psql zabbix
hypertable_name | total_chunks | number_compressed_chunks | uncompressed_heap_bytes | uncompressed_index_bytes | uncompressed_toast_bytes | uncompressed_total_bytes | compressed_heap_bytes | compressed_index_bytes | compressed_toast_bytes | compressed_total_bytes
-----------------+--------------+--------------------------+-------------------------+--------------------------+--------------------------+--------------------------+-----------------------+------------------------+------------------------+------------------------
history_str | 10 | 1 | 8192 bytes | 16 kB | 0 bytes | 24 kB | 8192 bytes | 16 kB | 8192 bytes | 32 kB
history_text | 10 | 1 | 8192 bytes | 16 kB | 64 kB | 88 kB | 8192 bytes | 16 kB | 64 kB | 88 kB
history_uint | 10 | 1 | 479 MB | 460 MB | 0 bytes | 939 MB | 1280 kB | 344 kB | 51 MB | 52 MB
history | 10 | 1 | 1856 kB | 1328 kB | 0 bytes | 3184 kB | 72 kB | 16 kB | 312 kB | 400 kB
(4 rows)

Compressed chunk closed for writes

date -d @1599955200
Sun Sep 13 03:00:00 MSK 2020

I think need wait for compression: 7day(default) + 1 day.
Lets check tomorrow...

[GOID]

Today stats:

echo "select chunk_table, table_size, index_size, total_size FROM chunk_relation_size_pretty('history_uint')" | sudo -u postgres psql zabbix
chunk_table | table_size | index_size | total_size
-------------------------------------------+------------+------------+------------
_timescaledb_internal._hyper_2_2_chunk | 0 bytes | 8192 bytes | 8192 bytes
_timescaledb_internal._hyper_2_8_chunk | 0 bytes | 16 kB | 16 kB
_timescaledb_internal._hyper_2_11_chunk | 4788 MB | 5981 MB | 11 GB

echo "SELECT * FROM timescaledb_information.compressed_chunk_stats;" | sudo -u postgres psql zabbix
hypertable_name | chunk_name | compression_status | uncompressed_heap_bytes | uncompressed_index_bytes | uncompressed_toast_bytes | uncompressed_total_bytes | compressed_heap_bytes | compressed_index_bytes | compressed_toast_bytes | compressed_total_bytes
-----------------+-------------------------------------------+--------------------+-------------------------+--------------------------+--------------------------+--------------------------+-----------------------+------------------------+------------------------+------------------------
history_str | _timescaledb_internal._hyper_5_4_chunk | Compressed | 8192 bytes | 16 kB | 0 bytes | 24 kB | 8192 bytes | 16 kB | 8192 bytes | 32 kB
history_text | _timescaledb_internal._hyper_4_3_chunk | Compressed | 8192 bytes | 16 kB | 64 kB | 88 kB | 8192 bytes | 16 kB | 64 kB | 88 kB
history | _timescaledb_internal._hyper_1_1_chunk | Compressed | 1856 kB | 1328 kB | 0 bytes | 3184 kB | 72 kB | 16 kB | 312 kB | 400 kB
history_uint | _timescaledb_internal._hyper_2_2_chunk | Compressed | 479 MB | 460 MB | 0 bytes | 939 MB | 1280 kB | 344 kB | 51 MB | 52 MB
history_str | _timescaledb_internal._hyper_5_9_chunk | Compressed | 8192 bytes | 32 kB | 0 bytes | 40 kB | 8192 bytes | 16 kB | 8192 bytes | 32 kB
history_text | _timescaledb_internal._hyper_4_10_chunk | Compressed | 800 kB | 184 kB | 56 kB | 1040 kB | 40 kB | 16 kB | 104 kB | 160 kB
history | _timescaledb_internal._hyper_1_7_chunk | Compressed | 8584 kB | 10 MB | 0 bytes | 19 MB | 88 kB | 16 kB | 1416 kB | 1520 kB
history_uint | _timescaledb_internal._hyper_2_8_chunk | Compressed | 6449 MB | 9495 MB | 0 bytes | 16 GB | 16 MB | 4080 kB | 688 MB | 708 MB

echo "SELECT * FROM timescaledb_information.compressed_hypertable_stat s;" | sudo -u postgres psql zabbix
hypertable_name | total_chunks | number_compressed_chunks | uncompressed_heap_bytes | uncompressed_index_bytes | uncompressed_toast_bytes | uncompressed_total_bytes | compressed_heap_bytes | compressed_index_bytes | compressed_toast_bytes | compressed_total_bytes
-----------------+--------------+--------------------------+-------------------------+--------------------------+--------------------------+--------------------------+-----------------------+------------------------+------------------------+------------------------
history_str | 11 | 2 | 16 kB | 48 kB | 0 bytes | 64 kB | 16 kB | 32 kB | 16 kB | 64 kB
history_text | 11 | 2 | 808 kB | 200 kB | 120 kB | 1128 kB | 48 kB | 32 kB | 168 kB | 248 kB
history_uint | 11 | 2 | 6929 MB | 9955 MB | 0 bytes | 16 GB | 17 MB | 4424 kB | 738 MB | 760 MB
history | 11 | 2 | 10 MB | 12 MB | 0 bytes | 22 MB | 160 kB | 32 kB | 1728 kB | 1920 kB
(4 rows)

It works!
If i correct understand stats - table history_uint (chunks of this hypertable) (most expensive by storage usage) compressed from 16GB to 760MB. Nice rate.
How it looks on graph (Used space)