How to validate your TimescaleDB + Zabbix compression

[Rafael Trevisan]

Hello,

After following the current guide to setup TimescaleDB i was set adrift wondering if it worked, and by searching i've found how to check the basics of the TimescaleDB implementation, as a non postgreSQL user and with not much a of a experience with databases, i've decided to share how to check it, i hope that someone may find this useful:


First, how to check the current size of your database (log in to psql first)

zabbix=> SELECT pg_size_pretty( pg_database_size('zabbix') );
pg_size_pretty
----------------
599 MB
(1 row)

Then, how to check info about the database:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_catalog = 'zabbix'
AND table_schema NOT LIKE 'pg_%'
AND table_schema != 'zabbix'
ORDER BY table_schema, table_name;

If your setup is correct you will see TimescaleDB items like this:

table_schema | table_name
--------------------------+--------------------------------------------------
_timescaledb_cache | cache_inval_bgw_job
_timescaledb_cache | cache_inval_extension
_timescaledb_cache | cache_inval_hypertable
_timescaledb_catalog | chunk

and then, to check how is your compression setup, use:

SELECT * FROM timescaledb_information.jobs
WHERE proc_name='policy_compression';

Example:

job_id | application_name | schedule_interval | max_runtime | max_retries | retry_period | proc_schema | proc_name | owner | scheduled | fixed_schedule | config | next_start | initial_start | hypertable_schema | hypertable_name | check_schema | check_name
--------+---------------------------+-------------------+-------------+-------------+--------------+-----------------------+--------------------+--------+-----------+----------------+------------------------------------------------+-------------------------------+---------------+-------------------+-----------------+-----------------------+--------------------------
1000 | Compression Policy [1000] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_internal | policy_compression | zabbix | t | f | {"hypertable_id": 1, "compress_after": 612000} | 2023-08-02 16:01:33.817808-03 | | public | history | _timescaledb_internal | policy_compression_check
1001 | Compression Policy [1001] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_internal | policy_compression | zabbix | t | f | {"hypertable_id": 2, "compress_after": 612000} | 2023-08-02 16:01:33.805898-03 | | public | history_uint | _timescaledb_internal | policy_compression_check
1002 | Compression Policy [1002] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_internal | policy_compression | zabbix | t | f | {"hypertable_id": 5, "compress_after": 612000} | 2023-08-02 16:01:33.765747-03 | | public | history_str | _timescaledb_internal | policy_compression_check
1003 | Compression Policy [1003] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_internal | policy_compression | zabbix | t | f | {"hypertable_id": 4, "compress_after": 612000} | 2023-08-02 16:01:33.786179-03 | | public | history_text | _timescaledb_internal | policy_compression_check
1004 | Compression Policy [1004] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_internal | policy_compression | zabbix | t | f | {"hypertable_id": 3, "compress_after": 612000} | 2023-08-02 16:01:33.674456-03 | | public | history_log | _timescaledb_internal | policy_compression_check
1005 | Compression Policy [1005] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_internal | policy_compression | zabbix | t | f | {"hypertable_id": 6, "compress_after": 612000} | 2023-08-02 16:01:33.670589-03 | | public | trends | _timescaledb_internal | policy_compression_check
1006 | Compression Policy [1006] | 1 day | 00:00:00 | -1 | 01:00:00 | _timescaledb_internal | policy_compression | zabbix | t | f | {"hypertable_id": 7, "compress_after": 612000} | 2023-08-02 16:01:33.675299-03 | | public | trends_uint | _timescaledb_internal | policy_compression_check

[cyber]

Besides that you can also see some stats, how long it takes, when it is scheduled etc...

SELECT * FROM timescaledb_information.job_stats;
 hypertable_schema | hypertable_name | job_id |      last_run_started_at      |    last_successful_finish     | last_run_status | job_status | last_run_duration |          next_start           | total_runs | total_successes | total_failures
-------------------+-----------------+--------+-------------------------------+-------------------------------+-----------------+------------+-------------------+-------------------------------+------------+-----------------+----------------
 public            | history         |   1000 | 2023-08-03 07:33:46.053337+02 | 2023-08-03 07:35:40.23803+02  | Success         | Scheduled  | 00:01:54.184693   | 2023-08-04 07:35:40.23803+02  |          2 |               2 |              0
 public            | history_log     |   1004 | 2023-08-03 07:21:26.900492+02 | 2023-08-03 07:21:44.533347+02 | Success         | Scheduled  | 00:00:17.632855   | 2023-08-04 07:21:44.533347+02 |          2 |               2 |              0
 public            | history_str     |   1002 | 2023-08-03 07:19:39.765323+02 | 2023-08-03 07:19:42.360089+02 | Success         | Scheduled  | 00:00:02.594766   | 2023-08-04 07:19:42.360089+02 |          2 |               2 |              0
 public            | history_text    |   1003 | 2023-08-03 07:24:36.506967+02 | 2023-08-03 07:25:20.952705+02 | Success         | Scheduled  | 00:00:44.445738   | 2023-08-04 07:25:20.952705+02 |          2 |               2 |              0
 public            | history_uint    |   1001 | 2023-08-03 07:46:21.964682+02 | 2023-08-03 07:49:49.304475+02 | Success         | Scheduled  | 00:03:27.339793   | 2023-08-04 07:49:49.304475+02 |          2 |               2 |              0
 public            | trends          |   1005 | 2023-08-03 07:44:40.626712+02 | 2023-08-03 07:44:40.649132+02 | Success         | Scheduled  | 00:00:00.02242    | 2023-08-04 07:44:40.649132+02 |        127 |               2 |            125
 public            | trends_uint     |   1006 | 2023-08-03 08:16:44.568708+02 | 2023-08-03 08:16:44.588098+02 | Success         | Scheduled  | 00:00:00.01939    | 2023-08-04 08:16:44.588098+02 |         22 |               2 |             20
(7 rows)
​

I enabled compression just yesterday and TBH I have no idea, where those failures for trends* tables have come... They were there already before I added compression..:P

And if anyone has a question how much compression helps, then with a "compress everything older than 7 days" (as default) my DB size dropped from 1.4T to ~500G...For a duration of first compression, there was an increase in DB server load, but after that its all the same as it seems... A slight increase in config syncer process utilization... but also, nothing drastic... like ~2-3% maybe... Don't really care if its 32 or 35 average..

[Rafael Trevisan]

Thanks for the additional info cyber, nice to see how much compression helps, my current Zabbix 4 is running with 500 hosts, with mysql partitioning + housekeeping i'm using 100GB, hoping to stay similar to this size in my new instance after migrating the hosts.