Housekeeping - This setting should be enabled, because history tables contain...

[Spectator]

Hi!

I have a 6.0 LTS Zabbix system with PostgreSQL 14.5 and TimeScaleDB 2.8.1.

On the Zabbix web interface, I see an entry like this in the System information:



My Housekeeping settings:



I see this in the database:

tnwzabbixdb=# 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('trends');  before compression | after compression
--------------------+-------------------
                    |
(1 row)

tnwzabbixdb=# 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('trends_uint');
 before compression | after compression
--------------------+-------------------
                    |
(1 row)

tnwzabbixdb=# 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('history');
 before compression | after compression
--------------------+-------------------
 120 MB             | 9112 kB
(1 row)

tnwzabbixdb=# 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('history_uint');
 before compression | after compression
--------------------+-------------------
 64 MB              | 3440 kB
(1 row)

tnwzabbixdb=# 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('history_str');
 before compression | after compression
--------------------+-------------------
 328 kB             | 192 kB
(1 row)

tnwzabbixdb=# 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('history_text');
 before compression | after compression
--------------------+-------------------
 432 kB             | 384 kB
(1 row)

tnwzabbixdb=# 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('history_log');
 before compression | after compression
--------------------+-------------------
(0 rows)

tnwzabbixdb=# SELECT * FROM timescaledb_information.jobs;
 job_id |     application_name      | schedule_interval | max_runtime | max_retries | retry_period |      proc_schema      |     proc_name      |  owner   | scheduled |                     config
          |          next_start           | hypertable_schema | hypertable_name |     check_schema      |        check_name
--------+---------------------------+-------------------+-------------+-------------+--------------+-----------------------+--------------------+----------+-----------+--------------------------------------
----------+-------------------------------+-------------------+-----------------+-----------------------+--------------------------
      1 | Telemetry Reporter [1]    | 24:00:00          | 00:01:40    |          -1 | 01:00:00     | _timescaledb_internal | policy_telemetry   | postgres | t         |
          | 2022-11-16 03:29:34.028528+01 |                   |                 |                       |
   1000 | Compression Policy [1000] | 1 day             | 00:00:00    |          -1 | 01:00:00     | _timescaledb_internal | policy_compression | zabbix   | t         | {"hypertable_id": 1, "compress_after"
: 612000} | 2022-11-15 16:54:33.876362+01 | 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         | {"hypertable_id": 2, "compress_after"
: 612000} | 2022-11-15 16:54:33.311821+01 | 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         | {"hypertable_id": 5, "compress_after"
: 612000} | 2022-11-15 16:54:31.80317+01  | 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         | {"hypertable_id": 4, "compress_after"
: 612000} | 2022-11-15 16:54:31.816562+01 | 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         | {"hypertable_id": 3, "compress_after"
: 612000} | 2022-11-15 16:54:31.785696+01 | 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         | {"hypertable_id": 6, "compress_after"
: 612000} | 2022-11-15 16:54:31.754771+01 | 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         | {"hypertable_id": 7, "compress_after"
: 612000} | 2022-11-15 16:54:31.737346+01 | public            | trends_uint     | _timescaledb_internal | policy_compression_check
(8 rows)

tnwzabbixdb=# 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 | 2022-11-14 16:54:33.013436+01 | 2022-11-14 16:54:33.876362+01 | Success         | Scheduled  | 00:00:00.862926   | 2022-11-15 16:54:33.876362+01 |         11
|              11 |              0
 public            | history_log     |   1004 | 2022-11-14 16:54:31.768171+01 | 2022-11-14 16:54:31.785696+01 | Success         | Scheduled  | 00:00:00.017525   | 2022-11-15 16:54:31.785696+01 |         22
|              11 |             11
 public            | history_str     |   1002 | 2022-11-14 16:54:31.765535+01 | 2022-11-14 16:54:31.80317+01  | Success         | Scheduled  | 00:00:00.037635   | 2022-11-15 16:54:31.80317+01  |         11
|              11 |              0
 public            | history_text    |   1003 | 2022-11-14 16:54:31.775828+01 | 2022-11-14 16:54:31.816562+01 | Success         | Scheduled  | 00:00:00.040734   | 2022-11-15 16:54:31.816562+01 |         11
|              11 |              0
 public            | history_uint    |   1001 | 2022-11-14 16:54:32.635166+01 | 2022-11-14 16:54:33.311821+01 | Success         | Scheduled  | 00:00:00.676655   | 2022-11-15 16:54:33.311821+01 |         11
|              11 |              0
 public            | trends          |   1005 | 2022-11-14 16:54:31.736801+01 | 2022-11-14 16:54:31.754771+01 | Success         | Scheduled  | 00:00:00.01797    | 2022-11-15 16:54:31.754771+01 |         19
|              11 |              8
 public            | trends_uint     |   1006 | 2022-11-14 16:54:31.718066+01 | 2022-11-14 16:54:31.737346+01 | Success         | Scheduled  | 00:00:00.01928    | 2022-11-15 16:54:31.737346+01 |         11
|              11 |              0
                   |                 |      1 | 2022-11-15 05:27:38.421405+01 | -infinity                     | Failed          | Scheduled  | 00:00:03.107123   | 2022-11-16 03:29:34.028528+01 |         26
|               0 |             26
(8 rows)

tnwzabbixdb=# select * from hypertable_compression_stats('history_uint');
 total_chunks | number_compressed_chunks | before_compression_table_bytes | before_compression_index_bytes | before_compression_toast_bytes | before_compression_total_bytes | after_compression_table_bytes |
 after_compression_index_bytes | after_compression_toast_bytes | after_compression_total_bytes | node_name
--------------+--------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+-------------------------------+
-------------------------------+-------------------------------+-------------------------------+-----------
           12 |                        3 |                       30720000 |                       36560896 |                              0 |                       67280896 |                        278528 |
                         81920 |                       3162112 |                       3522560 |
(1 row)

tnwzabbixdb=# SELECT * FROM timescaledb_information.chunks where is_compressed=true;
 hypertable_schema | hypertable_name |     chunk_schema      |    chunk_name     | primary_dimension | primary_dimension_type | range_start | range_end | range_start_integer | range_end_integer | is_compres
sed | chunk_tablespace | data_nodes
-------------------+-----------------+-----------------------+-------------------+-------------------+------------------------+-------------+-----------+---------------------+-------------------+-----------
----+------------------+------------
 public            | history_uint    | _timescaledb_internal | _hyper_2_1_chunk  | clock             | integer                |             |           |          1667520000 |        1667606400 | t
    |                  |
 public            | history         | _timescaledb_internal | _hyper_1_2_chunk  | clock             | integer                |             |           |          1667520000 |        1667606400 | t
    |                  |
 public            | history_str     | _timescaledb_internal | _hyper_5_3_chunk  | clock             | integer                |             |           |          1667520000 |        1667606400 | t
    |                  |
 public            | history_text    | _timescaledb_internal | _hyper_4_6_chunk  | clock             | integer                |             |           |          1667520000 |        1667606400 | t
    |                  |
 public            | history_uint    | _timescaledb_internal | _hyper_2_7_chunk  | clock             | integer                |             |           |          1667606400 |        1667692800 | t
    |                  |
 public            | history         | _timescaledb_internal | _hyper_1_8_chunk  | clock             | integer                |             |           |          1667606400 |        1667692800 | t
    |                  |
 public            | history_str     | _timescaledb_internal | _hyper_5_9_chunk  | clock             | integer                |             |           |          1667606400 |        1667692800 | t
    |                  |
 public            | history_text    | _timescaledb_internal | _hyper_4_10_chunk | clock             | integer                |             |           |          1667606400 |        1667692800 | t
    |                  |
 public            | history_uint    | _timescaledb_internal | _hyper_2_11_chunk | clock             | integer                |             |           |          1667692800 |        1667779200 | t
    |                  |
 public            | history         | _timescaledb_internal | _hyper_1_12_chunk | clock             | integer                |             |           |          1667692800 |        1667779200 | t
    |                  |
 public            | history_str     | _timescaledb_internal | _hyper_5_13_chunk | clock             | integer                |             |           |          1667692800 |        1667779200 | t
    |                  |
 public            | history_text    | _timescaledb_internal | _hyper_4_14_chunk | clock             | integer                |             |           |          1667692800 |        1667779200 | t
    |                  |
(12 rows)

tnwzabbixdb=#​

Do you have any ideas what could be the problem?

[Spectator]

Does anyone have any ideas?

[Singularity]

If I am not wrong, history and trends are the 2 most important tables which the housekeeper cleans.( Some senior member will definitely correct me if I am wrong here but I am extremely positive ). You can turn off housekeeping from Zabbix( though it's recommended to keep it on ). In that case you have to do cleaning of database by yourself.( Do it manually or create a script/cronjob )

Note : When you do it yourself manually or by script, there will be locks which will slow down zabbix processes.( To handle this, I used to create a maintenance time per week. You do it per day or per week or whatever interval, basically it depends on the volume of data in zabbix database )

[kikoz]

https://support.zabbix.com/browse/ZBX-21889

[Spectator]

Thank you kikoz!