Ad Widget

Collapse

Zabbix 5 with TimescaleDB not compressing chunks

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • keitarobr
    Junior Member
    • Apr 2014
    • 3
    #1

    Zabbix 5 with TimescaleDB not compressing chunks

    Dear all,

    our Zabbix 5 installation is not compressing older chunks in timescaledb. It seems to have stopped after we had a disk IO overload (solved now) that resulted in the following error:

    Code:
    32159:20201112:220215.184 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR: insert/update/delete not permitted on chunk "_hyper_7_32_chunk"
    Following this instructions we executed the following command about two weeks ago:

    Code:
    select decompress_chunk('_timescaledb_internal._hyper_7_3 2_chunk');

    After this command the error stopped but it seems that no new chunks are being compressed.

    This is the situation on November 27th (when we detected this issue):

    Code:
    zabbix=# select * from timescaledb_information.compressed_hypertable_stat s;
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_log     |           22 |                        3 | 3904 kB                 | 952 kB                   | 15 MB                    | 20 MB                    | 56 kB                 | 48 kB                  | 192 kB                 | 296 kB
history_str     |           29 |                        9 | 66 MB                   | 82 MB                    | 0 bytes                  | 148 MB                   | 19 MB                 | 2288 kB                | 120 kB                 | 21 MB
history_text    |           29 |                        9 | 2872 kB                 | 2328 kB                  | 336 kB                   | 5536 kB                  | 2920 kB               | 552 kB                 | 352 kB                 | 3824 kB
trends          |            2 |                        1 | 1552 kB                 | 1368 kB                  | 0 bytes                  | 2920 kB                  | 872 kB                | 72 kB                  | 208 kB                 | 1152 kB
history_uint    |           30 |                        8 | 1784 MB                 | 2732 MB                  | 0 bytes                  | 4516 MB                  | 33 MB                 | 4056 kB                | 217 MB                 | 254 MB
trends_uint     |            2 |                        1 | 2440 kB                 | 2392 kB                  | 0 bytes                  | 4832 kB                  | 1976 kB               | 200 kB                 | 72 kB                  | 2248 kB
history         |           30 |                        8 | 3074 MB                 | 3826 MB                  | 0 bytes                  | 6899 MB                  | 192 MB                | 6560 kB                | 247 MB                 | 445 MB
    And today (November 30th):

    Code:
    zabbix=# select * from timescaledb_information.compressed_hypertable_stat s;
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_log     |           25 |                        3 | 3904 kB                 | 952 kB                   | 15 MB                    | 20 MB                    | 56 kB                 | 48 kB                  | 192 kB                 | 296 kB
history_str     |           32 |                        9 | 66 MB                   | 82 MB                    | 0 bytes                  | 148 MB                   | 19 MB                 | 2288 kB                | 120 kB                 | 21 MB
history_text    |           32 |                        9 | 2872 kB                 | 2328 kB                  | 336 kB                   | 5536 kB                  | 2920 kB               | 552 kB                 | 352 kB                 | 3824 kB
trends          |            2 |                        1 | 1552 kB                 | 1368 kB                  | 0 bytes                  | 2920 kB                  | 872 kB                | 72 kB                  | 208 kB                 | 1152 kB
history_uint    |           33 |                        8 | 1784 MB                 | 2732 MB                  | 0 bytes                  | 4516 MB                  | 33 MB                 | 4056 kB                | 217 MB                 | 254 MB
trends_uint     |            2 |                        1 | 2440 kB                 | 2392 kB                  | 0 bytes                  | 4832 kB                  | 1976 kB               | 200 kB                 | 72 kB                  | 2248 kB
history         |           33 |                        8 | 3074 MB                 | 3826 MB                  | 0 bytes                  | 6899 MB                  | 192 MB                | 6560 kB                | 247 MB                 | 445 MB
    Housekeeping is enabled for 7d with compression turned on.
    Tags: None
    • keitarobr
      Junior Member
      • Apr 2014
      • 3
      #2
      For the record, I've opened a ticket with timescaleDB as well regarding this issue, because it seems the chunk _hyper_7_3 2_chunk still has problems: https://github.com/timescale/timescaledb/issues/2704

        Comment

        • keitarobr
          Junior Member
          • Apr 2014
          • 3
          #3
          Finally we could fix this issue. I will write it here for someone who faces the same problem in the future.

          The following command shows the status of the timescaleDB processes:

          Code:
          zabbix=# SELECT * FROM timescaledb_information.policy_stats;

hypertable | job_id | job_type | last_run_success | last_finish | last_successful_finish | last_start | next_start | total_runs | total_failures
--------------+--------+-----------------+------------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+------------+----------------
history       | 1000 | compress_chunks | t | 2020-11-12 22:04:04.877674+00 | 2020-11-12 22:04:04.877674+00 | 2029-05-26 14:48:28.809717+00 | 2029-05-26 14:48:28.809717+00 | 16 | 0
history_log   | 1004 | compress_chunks | t | 2020-11-12 21:57:55.264188+00 | 2020-11-12 21:57:55.264188+00 | 2029-05-26 14:48:30.620379+00 | 2029-05-26 14:48:30.620379+00 | 18 | 0
history_str   | 1002 | compress_chunks | t | 2020-11-12 21:57:56.015463+00 | 2020-11-12 21:57:56.015463+00 | 2029-05-26 14:48:30.748837+00 | 2029-05-26 14:48:30.748837+00 | 17 | 0
history_text  | 1003 | compress_chunks | t | 2029-05-26 14:48:30.617838+00 | 2029-05-26 14:48:30.617838+00 | 2029-05-26 14:48:30.43354+00 | 2029-05-26 14:48:30.43354+00 | 17 | 0
history_uint  | 1001 | compress_chunks | t | 2020-11-12 22:00:39.418766+00 | 2020-11-12 22:00:39.418766+00 | 2029-05-26 14:48:28.792392+00 | 2029-05-26 14:48:28.792392+00 | 16 | 0
trends        | 1005 | compress_chunks | t | 2020-12-07 23:01:05.240236+00 | 2020-12-07 23:01:05.240236+00 | 2020-12-07 23:01:05.206835+00 | 2020-12-08 23:01:05.240236+00 | 42 | 1
trends_uint   | 1006 | compress_chunks | t | 2020-12-07 21:59:25.112371+00 | 2020-12-07 21:59:25.112371+00 | 2020-12-07 21:59:25.037725+00 | 2020-12-08 21:59:25.112371+00 | 41 | 0
          As you can see, next_start and last_start are completly wrong. To force the jobs to execute again and reestablish the regular schedule, the following commands were executed:

          Code:
          SELECT alter_job_schedule(1000, next_start => now());
SELECT alter_job_schedule(1001, next_start => now());
...
          After that the chunks began to be created and compressed as expected:

          Code:
          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_text    |           41 |                       32 | 59 MB                   | 52 MB                    | 520 kB                   | 112 MB                   | 39 MB                 | 6192 kB                | 4904 kB               | 50 MB
trends          |            3 |                        1 | 1552 kB                 | 1368 kB                  | 0 bytes                  | 2920 kB                  | 872 kB                | 72 kB                  | 208 kB                | 1152 kB
history_uint    |           42 |                       33 | 33 GB                   | 51 GB                    | 0 bytes                  | 84 GB                    | 552 MB                | 72 MB                  | 4135 MB               | 4758 MB
trends_uint     |            3 |                        1 | 2440 kB                 | 2392 kB                  | 0 bytes                  | 4832 kB                  | 1976 kB               | 200 kB                 | 72 kB                 | 2248 kB
history_log     |           34 |                       25 | 118 MB                  | 28 MB                    | 268 MB                   | 414 MB                   | 232 kB                | 400 kB                 | 3600 kB               | 4232 kB
history         |           42 |                       33 | 76 GB                   | 95 GB                    | 0 bytes                  | 172 GB                   | 4998 MB               | 138 MB                 | 5964 MB               | 11 GB
history_str     |           41 |                       32 | 579 MB                  | 752 MB                   | 0 bytes                  | 1331 MB                  | 235 MB                | 29 MB                  | 672 kB                | 264 MB

            Comment

            Previous template Next
            Working...