Ad Widget

Collapse

The housekeeper is malfunctioning

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Bryam Balan
    Junior Member
    • Jul 2024
    • 4
    #1

    The housekeeper is malfunctioning

    Code:
    [FONT=monospace][COLOR=#000000]zabbix_server -V [/COLOR]
zabbix_server (Zabbix) 6.4.17
Revision c12261f00b4 15 July 2024, compilation time: Jul 15 2024 11:05:06

Copyright (C) 2024 Zabbix SIA
License GPLv2+: GNU GPL version 2 or later <https://www.gnu.org/licenses/>.
This is free software: you are free to change and redistribute it according to
the license. There is NO WARRANTY, to the extent permitted by law.

This product includes software developed by the OpenSSL Project
for use in the OpenSSL Toolkit (http://www.openssl.org/).

Compiled with OpenSSL 1.1.1k  25 Mar 2021
Running with OpenSSL 1.1.1w  11 Sep 2023[/FONT]
    I've been collecting historical data since January 10, 2024. I've recently enabled the Housekeeper and set its global configuration to remove data older than 15 days. Despite this, I've observed that the Housekeeper is not deleting data beyond the 15-day threshold.

    I've investigated further by examining the Zabbix debug logs. It appears that the system is directly fetching History and Trends data from the item (table items). As an additional test, I manually adjusted the retention period within the item itself to 15 days, but the Housekeeper continues to disregard this setting and retain older data.

    Code:
    [FONT=monospace][COLOR=#000000]MariaDB [zabbix]> select * from history_uint limit 10; [/COLOR]
+--------+------------+-------+-----------+
| itemid | clock      | value | ns        |
+--------+------------+-------+-----------+
|  23271 | 1704898971 |     0 | 353580782 |
|  23271 | 1704899271 |     0 | 307559939 |
|  23271 | 1704899571 |     0 | 614478255 |
|  23271 | 1704899871 |     0 | 268948575 |
|  23271 | 1704900171 |     0 | 437756238 |
|  23271 | 1704900471 |     0 | 482623896 |
|  23271 | 1704900771 |     0 | 359932855 |
|  23271 | 1704901071 |     0 | 266082600 |
|  23271 | 1704901371 |     0 | 513590172 |
|  23271 | 1704901671 |     0 | 117621391 |
+--------+------------+-------+-----------+
[COLOR=#000000][B]10 rows in set (0,001 sec)[/B][/COLOR][/FONT]
    TimeStamp 1704898971 = GMT: Wednesday, 10 January 2024 15:02:51


    Front-End Config:
    Click image for larger version Name: image.png Views: 373 Size: 82.8 KB ID: 488629​


    Config Table:
    Code:
    [FONT=monospace][COLOR=#000000]MariaDB [zabbix]> Select hk_events_mode,hk_events_trigger,hk_events_internal,_events_disc[/COLOR]
overy,hk_events_autoreg, hk_services_mode,      hk_services,    hk_audit_mode,         hk
_audit, hk_sessions_mode,      hk_sessions,  hk_history_mode,hk_history_global,       hk_
history,   hk_trends_mode, hk_trends_global,        hk_trends from config\G
ERROR 1054 (42S22): Unknown column '_events_discovery' in 'field list'
MariaDB [zabbix]> select               hk_events_mode,           hk_events_trigger,      
   hk_events_internal,         hk_events_discovery,           hk_events_autoreg,        
   hk_services_mode,                 hk_services,               hk_audit_mode,          
         hk_audit,            hk_sessions_mode,                 hk_sessions,            
hk_history_mode,           hk_history_global,                  hk_history,              
hk_trends_mode,            hk_trends_global,                   hk_trends from config\G
*************************** 1. row ***************************
    hk_events_mode: 1
 hk_events_trigger: 365d
hk_events_internal: 1d
hk_events_discovery: 1d
 hk_events_autoreg: 1d
  hk_services_mode: 1
       hk_services: 365d
     hk_audit_mode: 0
          hk_audit: 100d
  hk_sessions_mode: 1
       hk_sessions: 365d
   hk_history_mode: 1
 hk_history_global: 1
        hk_history: 15d
    hk_trends_mode: 1
  hk_trends_global: 1
         hk_trends: 1096d
[COLOR=#000000][B]1 row in set (0,001 sec)[/B][/COLOR][COLOR=#000000] [/COLOR]

MariaDB [zabbix]> [/FONT]
​

    Item Config:
    Click image for larger version Name: image.png Views: 247 Size: 112.1 KB ID: 488630​


    Code:
    [FONT=monospace][COLOR=#000000]root@XXXX-SRV-ZABBIX:/home/XXXXX# cat agoravai.log | grep '23271' [/COLOR]
[B]1142868:20240729:165929.166 query [txnlev:0] [delete from history_uint where itemid=23271 and clock<1704697371][/B]
1142868:20240729:170115.501 query [txnlev:0] [delete from trends_uint where itemid=23271 and clock<1557525600]
root@XXXX-SRV-ZABBIX:/home/XXX# [/FONT]
​
    1704697371 = GMT: Monday, 8 January 2024 07:02:51​

    After activating the Housekeeper debug mode, I observed that the SQL query generated to delete item data is not using the expected 15-day retention period. Instead, it's targeting data from January 8, 2024.

    I've manually trimmed the Housekeeper log so you can follow the process. Despite the item, global configuration, and item macros all being set to a 15-day retention period, the Housekeeper is using a timestamp that's over 6 months old for cleanup. Any ideas why this might be happening?


    Housekeeper Debug:
    Code:
    1142868:20240729:155244.396 zbx_setproctitle() title:'housekeeper [connecting to the database]'
1142868:20240729:155244.397 zbx_setproctitle() title:'housekeeper [removing old history and trends]'
1142868:20240729:155244.397 In housekeeping_history_and_trends() now:1722279164
1142868:20240729:155244.397 query [txnlev:0] [select i.itemid,i.value_type,i.history,i.trends,h.hostid from items i,hosts h where i.flags in (0,4) and i.hostid=h.hostid and h.status in (0,1)]
1142868:20240729:155244.825 In substitute_simple_macros_impl() data:'30d'
1142868:20240729:155244.825 End substitute_simple_macros_impl() data:'30d'
1142868:20240729:155244.825 In substitute_simple_macros_impl() data:'1096d'
1142868:20240729:155244.825 End substitute_simple_macros_impl() data:'1096d'
1142868:20240729:155244.825 In substitute_simple_macros_impl() data:'30d'
1142868:20240729:155244.825 End substitute_simple_macros_impl() data:'30d'
1142868:20240729:155244.825 In substitute_simple_macros_impl() data:'1096d'
1142868:20240729:155244.825 End substitute_simple_macros_impl() data:'1096d'
1142868:20240729:155244.825 In substitute_simple_macros_impl() data:'30d'
1142868:20240729:155250.837 query without transaction detected
1142868:20240729:155250.837 query [txnlev:0] [delete from history where itemid=23252 and clock<1704668552]
1142868:20240729:155250.839 query without transaction detected
1142868:20240729:155250.839 query [txnlev:0] [delete from history where itemid=23253 and clock<1704668553]
1142868:20240729:155250.840 query without transaction detected
1142868:20240729:155250.840 query [txnlev:0] [delete from history where itemid=23255 and clock<1704668555]
1142868:20240729:155250.841 query without transaction detected
1142868:20240729:155250.841 query [txnlev:0] [delete from history where itemid=23256 and clock<1704668556]
1142868:20240729:155250.842 query without transaction detected
1142868:20240729:155250.844 query [txnlev:0] [delete from history where itemid=23257 and clock<1704668557]
1142868:20240729:155250.845 query without transaction detected
1142868:20240729:155250.845 query [txnlev:0] [delete from history where itemid=23258 and clock<1704668558]
1142868:20240729:155250.846 query without transaction detected
1142868:20240729:155250.846 query [txnlev:0] [delete from history where itemid=23259 and clock<1704668559]
1142868:20240729:155250.846 query without transaction detected
1142868:20240729:155250.847 query [txnlev:0] [delete from history where itemid=23260 and clock<1704668560]
1142868:20240729:155250.847 query without transaction detected
1142868:20240729:155250.847 query [txnlev:0] [delete from history where itemid=23264 and clock<1704668564]
1142868:20240729:155250.848 query without transaction detected
1142868:20240729:155250.848 query [txnlev:0] [delete from history where itemid=23265 and clock<1704668565]
1142868:20240729:155250.849 query without transaction detected
1142868:20240729:155437.398 query [txnlev:0] [delete from history_uint where itemid=287959 and clock<1720983164]
1142868:20240729:155437.399 query without transaction detected
1142868:20240729:155437.399 query [txnlev:0] [delete from history_uint where itemid=287960 and clock<1720983164]
1142868:20240729:155437.399 query without transaction detected
1142868:20240729:155437.399 query [txnlev:0] [delete from history_uint where itemid=287961 and clock<1720983164]
1142868:20240729:155437.400 query without transaction detected
1142868:20240729:155437.400 query [txnlev:0] [delete from history_uint where itemid=287962 and clock<1720983164]
1142868:20240729:155437.400 query without transaction detected
1142868:20240729:155437.400 query [txnlev:0] [delete from history_uint where itemid=287963 and clock<1720983164]
1142868:20240729:155437.401 query without transaction detected
1142868:20240729:155443.776 query [txnlev:0] [delete from trends where itemid=159238 and clock<1600509600]
1142868:20240729:155443.776 query without transaction detected
1142868:20240729:155443.776 query [txnlev:0] [delete from trends where itemid=159239 and clock<1600509600]
1142868:20240729:155443.777 query without transaction detected
1142868:20240729:155443.777 query [txnlev:0] [delete from trends where itemid=159264 and clock<1600509600]
1142868:20240729:155443.778 query without transaction detected
1142868:20240729:155443.778 query [txnlev:0] [delete from trends where itemid=159265 and clock<1600509600]
1142868:20240729:155443.778 query without transaction detected
1142868:20240729:155443.778 query [txnlev:0] [delete from trends where itemid=159266 and clock<1600509600]
1142868:20240729:155456.014 query [txnlev:0] [delete from trends_uint where itemid=96017 and clock<1581847200]
1142868:20240729:155456.014 query without transaction detected
1142868:20240729:155456.014 query [txnlev:0] [delete from trends_uint where itemid=96035 and clock<1581847200]
1142868:20240729:155456.015 query without transaction detected
1142868:20240729:155456.015 query [txnlev:0] [delete from trends_uint where itemid=96043 and clock<1581847200]
1142868:20240729:155456.016 query without transaction detected
1142868:20240729:155456.016 query [txnlev:0] [delete from trends_uint where itemid=96047 and clock<1581847200]
1142868:20240729:155456.017 query without transaction detected
1142868:20240729:155456.017 query [txnlev:0] [delete from trends_uint where itemid=96049 and clock<1581847200]
1142868:20240729:155456.017 query without transaction detected
1142868:20240729:155456.017 query [txnlev:0] [delete from trends_uint where itemid=96050 and clock<1581847200]
1142868:20240729:155456.018 query without transaction detected
1142868:20240729:155456.018 query [txnlev:0] [delete from trends_uint where itemid=96095 and clock<1581847200]
1142868:20240729:155531.496 End of housekeeping_history_and_trends():2417857
1142868:20240729:155531.498 zbx_setproctitle() title:'housekeeper [removing old problems]'
1142868:20240729:155531.498 In housekeeping_problems() now:1722279164
1142868:20240729:155531.498 query [txnlev:0] [select p1.eventid from problem p1 where p1.r_clock<>0 and p1.r_clock<1722192764 and not exists (select NULL from problem p2 where p1.eventid=p2.cause_eventid) limit 2000]
1142868:20240729:155531.499 query without transaction detected
1142868:20240729:155531.499 query [txnlev:0] [delete from problem where eventid in (14761927,14762304,14761947,14761929,14762318,14762361,14762217,14761701,14762473,14762401,14762474,14762477,14762341,14762484,14762483,14762485,14762487,14762486,14762569,14762452,14762453,14762454,14762305,14762577,14762243,14762670,14762671,14762684,14762685,14762637,14762672,14762639,14762673,14762674,14762675,14762676,14762677,14762678,14762679,14762680,14762681,14762682,14762683,14762633,14762686,14762687,14762707,14762720,14761554,14762636,14762786,14762840,14762841,14762854,14762855,14762842,14762829,14762843,14762844,14762845,14762846,14762847,14762848,14762849,14762850,14762851,14762852,14762838,14762853,14762857,14762879,14762856,14762887,14762934,14762954,14762971)]
1142868:20240729:155531.510 query [txnlev:0] [select p1.eventid from problem p1 where p1.r_clock<>0 and p1.r_clock<1722192764 and not exists (select NULL from problem p2 where p1.eventid=p2.cause_eventid) limit 2000]
1142868:20240729:155531.511 End of housekeeping_problems():76
1142868:20240729:155531.511 zbx_setproctitle() title:'housekeeper [removing old events]'
1142868:20240729:155531.511 In housekeeping_process_rule() table:'events' field_name:'eventid' filter:'events.source=0 and events.object=0 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid) and not exists(select null from event_symptom where events.eventid=event_symptom.cause_eventid)' min_clock:-1 now:1722279164
1142868:20240729:155531.511 query [txnlev:0] [select min(clock) from events where events.source=0 and events.object=0 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid) and not exists(select null from event_symptom where events.eventid=event_symptom.cause_eventid)]
1142868:20240729:155532.242 query [txnlev:0] [select eventid from events where clock<1690743164 and events.source=0 and events.object=0 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid) and not exists(select null from event_symptom where events.eventid=event_symptom.cause_eventid) order by eventid limit 2000]
1142868:20240729:155532.244 query without transaction detected
1142868:20240729:155532.244 query [txnlev:0] [delete from events where eventid in (195347,195353,195428,195442,195457,195458,195459,195492,195505,195507,195570,195590,195601,195606,195614,195691,195712,195761,195766,195768,195784,195854,195870,195890,195901,195903,195964,195969,196010,196014,196074,196077,196089,196090,196091,196093,196109,196114,196128,196149,196188,196189,196285,196299,196406,196409,196431,196432,196437,196438,196439,196470)]
1142868:20240729:155532.307 query [txnlev:0] [select eventid from events where clock<1690743164 and events.source=0 and events.object=0 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid) and not exists(select null from event_symptom where events.eventid=event_symptom.cause_eventid) order by eventid limit 2000]
1142868:20240729:155532.308 End of housekeeping_process_rule():52
1142868:20240729:155532.308 In housekeeping_process_rule() table:'events' field_name:'eventid' filter:'events.source=3 and events.object=0 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid)' min_clock:-1 now:1722279164
1142868:20240729:155532.308 query [txnlev:0] [select min(clock) from events where events.source=3 and events.object=0 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid)]
1142868:20240729:155532.309 End of housekeeping_process_rule():0
1142868:20240729:155532.309 In housekeeping_process_rule() table:'events' field_name:'eventid' filter:'events.source=3 and events.object=4 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid)' min_clock:-1 now:1722279164
1142868:20240729:155532.310 query [txnlev:0] [select min(clock) from events where events.source=3 and events.object=4 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid)]
1142868:20240729:155532.310 End of housekeeping_process_rule():0
1142868:20240729:155532.310 In housekeeping_process_rule() table:'events' field_name:'eventid' filter:'events.source=3 and events.object=5 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid)' min_clock:-1 now:1722279164
1142868:20240729:155532.310 query [txnlev:0] [select min(clock) from events where events.source=3 and events.object=5 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid)]
1142868:20240729:155532.311 End of housekeeping_process_rule():0
1142868:20240729:155532.311 In housekeeping_process_rule() table:'events' field_name:'eventid' filter:'events.source=1 and events.object=1' min_clock:1692527175 now:1722279164
1142868:20240729:155532.311 query [txnlev:0] [select eventid from events where clock<1692541575 and events.source=1 and events.object=1 order by eventid limit 2000]
1142868:20240729:155532.312 query without transaction detected
1142868:20240729:155532.312 query [txnlev:0] [delete from events where eventid in (934270,934282,934308,934325,934368,934399,934452,934467,934495,934517,934537,934554,934578,934607,934676,934692,934716,934748,934771,934780,934797,934812,934830,934863,934896,934927,934943,934972,935009,935033,935046,935070,935110,935185,935201,935226,935278,935313,935327,935344,935372,935406,935463,935479,935498,935556,935585,935600,935618,935640,935670,935734,935750,935769,935799,935825,935835,935858,935877,935911,935990,936018,936039,936076,936107,936124,936144,936169,936196,936270,936284,936303,936344,936369,936379,936398,936417,936449,936518,936539,936553,936587,936608,936625,936643,936680,936704,936760,936781,936827,936854,936878,936891,936912,936935,936976,937007,937014,937027,937045,937083,937107,937129,937144,937178,937221,937242,937264,937304,937332,937353,937365,937388,937420,937463,937532,937560,937576,937603,937621,937637,937662,937695,937736,937760,937778,937814,937839,937857,937873,937900,937922,937958,938001,938022,938040,938076,938093,938110,938131,938163,938190,938236,938255,938308,938340,938354,938370,938391,938427,938478,938492,938515,938551,938585,938604,938627,938639,938671,938692,938740,938764,938808,938832,938850,938872,938888,938931,938994,939007,939026,939055,939094,939111,939113,939137,939153,939190,939228,939238,939257,939281,939313,939338,939356,939379,939407,939478,939489,939512,939533,939563,939582,939605,939625,939669,939707,939726,939739,939772,939805,939825,939844,939871,939900,939955,939965,939987,940012,940039,940050,940072,940095,940132,940212,940229,940250,940291)]
1142868:20240729:155532.320 query [txnlev:0] [select eventid from events where clock<1692541575 and events.source=1 and events.object=1 order by eventid limit 2000]
1142868:20240729:155532.320 End of housekeeping_process_rule():218
1142868:20240729:155532.320 In housekeeping_process_rule() table:'events' field_name:'eventid' filter:'events.source=1 and events.object=2' min_clock:1692527175 now:1722279164
1142868:20240729:155532.320 query [txnlev:0] [select eventid from events where clock<1692541575 and events.source=1 and events.object=2 order by eventid limit 2000]
1142868:20240729:155532.322 query without transaction detected
1142868:20240729:155532.322 query [txnlev:0] [delete from events where eventid in (934269,934281,934307,934324,934367,934398,934451,934466,934494,934516,934536,934553,934577,934606,934675,934691,934715,934747,934767,934770,934779,934796,934811,934829,934862,934895,934926,934942,934971,935008,935032,935045,935069,935109,935184,935200,935225,935277,935312,935326,935343,935371,935405,935462,935478,935497,935555,935584,935599,935617,935639,935669,935733,935749,935768,935798,935824,935834,935857,935876,935910,935989,936017,936038,936075,936106,936123,936143,936168,936195,936269,936283,936302,936343,936368,936378,936397,936416,936448,936517,936538,936552,936586,936607,936624,936642,936679,936703,936759,936780,936826,936853,936877,936890,936911,936934,936975,937005,937006,937013,937026,937044,937082,937106,937128,937143,937177,937220,937241,937263,937303,937331,937352,937364,937387,937419,937462,937531,937559,937575,937602,937620,937636,937661,937694,937735,937759,937777,937813,937838,937856,937872,937899,937921,937957,938000,938021,938039,938075,938092,938109,938130,938162,938189,938235,938254,938307,938339,938353,938369,938390,938426,938477,938491,938514,938550,938584,938603,938626,938638,938670,938691,938739,938763,938807,938831,938849,938871,938887,938930,938993,939006,939025,939054,939093,939109,939110,939112,939136,939152,939189,939227,939237,939256,939280,939312,939337,939355,939378,939406,939477,939488,939511,939532,939562,939581,939604,939624,939668,939706,939725,939738,939771,939804,939824,939843,939870,939899,939954,939964,939986,940011,940038,940049,940071,940094,940131,940211,940228,940249,940290)]
1142868:20240729:155532.328 query [txnlev:0] [select eventid from events where clock<1692541575 and events.source=1 and events.object=2 order by eventid limit 2000]
1142868:20240729:155532.328 End of housekeeping_process_rule():221
1142868:20240729:155532.328 In housekeeping_process_rule() table:'events' field_name:'eventid' filter:'events.source=2 and events.object=3' min_clock:1722189676 now:1722279164
1142868:20240729:155532.328 query [txnlev:0] [select eventid from events where clock<1722192764 and events.source=2 and events.object=3 order by eventid limit 2000]
1142868:20240729:155532.329 End of housekeeping_process_rule():0
1142868:20240729:155532.329 In housekeeping_process_rule() table:'events' field_name:'eventid' filter:'events.source=4 and events.object=6 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid)' min_clock:-1 now:1722279164
1142868:20240729:155532.329 query [txnlev:0] [select min(clock) from events where events.source=4 and events.object=6 and not exists(select null from problem where events.eventid=problem.eventid) and not exists(select null from problem where events.eventid=problem.r_eventid)]
1142868:20240729:155532.330 End of housekeeping_process_rule():0
1142868:20240729:155532.330 zbx_setproctitle() title:'housekeeper [removing old sessions]'
1142868:20240729:155532.330 In housekeeping_sessions() now:1722279164
1142868:20240729:155532.330 query without transaction detected
1142868:20240729:155532.330 query [txnlev:0] [delete from sessions where lastaccess<1690743164 limit 2000]
1142868:20240729:155532.331 End of housekeeping_sessions():0
1142868:20240729:155532.331 zbx_setproctitle() title:'housekeeper [removing old service alarms]'
1142868:20240729:155532.331 In housekeeping_process_rule() table:'service_alarms' field_name:'servicealarmid' filter:'' min_clock:1590324660 now:1722279164
1142868:20240729:155532.331 query [txnlev:0] [select servicealarmid from service_alarms where clock<1590339060 order by servicealarmid limit 2000]
1142868:20240729:155532.331 End of housekeeping_process_rule():0
1142868:20240729:155532.331 zbx_setproctitle() title:'housekeeper [removing old audit log items]'
1142868:20240729:155532.331 zbx_setproctitle() title:'housekeeper [removing old autoreg_hosts]'
1142868:20240729:155532.331 In housekeeping_autoreg_host()
1142868:20240729:155532.331 query [txnlev:0] [select ah.autoreg_hostid from autoreg_host ah where not exists (select null from hosts h where ah.host=h.host) and not exists (select null from events e where ah.autoreg_hostid=e.objectid and e.source=2 and e.object=3) order by ah.autoreg_hostid limit 2000]
1142868:20240729:155532.332 End of housekeeping_autoreg_host():0
1142868:20240729:155532.332 zbx_setproctitle() title:'housekeeper [removing old records]'
1142868:20240729:155532.332 In housekeeping_proxy_dhistory() now:1722279164
1142868:20240729:155532.332 query without transaction detected
1142868:20240729:155532.332 query [txnlev:0] [delete from proxy_dhistory where clock<1722192764]
1142868:20240729:155532.333 End of housekeeping_proxy_dhistory():0
1142868:20240729:155532.333 zbx_setproctitle() title:'housekeeper [removing deleted items data]'
1142868:20240729:155532.333 In housekeeping_cleanup()
1142868:20240729:155532.333 query [txnlev:0] [select housekeeperid,tablename,field,value from housekeeper where tablename in ('history','history_log','history_str','history_text','history_uint','trends','trends_uint','events') order by tablename]
1142868:20240729:155532.333 query without transaction detected
1142868:20240729:155532.333 query [txnlev:0] [delete from history where itemid=221543 limit 2000]
1142868:20240729:155532.336 query without transaction detected
1142868:20240729:155532.337 query [txnlev:0] [delete from history where itemid=221558 limit 2000]
1142868:20240729:155532.339 query without transaction detected
1142868:20240729:155532.339 query [txnlev:0] [delete from history_uint where itemid=221529 limit 2000]
1142868:20240729:155532.343 query without transaction detected
1142868:20240729:155532.343 query [txnlev:0] [delete from trends where itemid=221543 limit 2000]
1142868:20240729:155532.351 query without transaction detected
1142868:20240729:155532.351 query [txnlev:0] [delete from trends where itemid=221558 limit 2000]
1142868:20240729:155532.426 query without transaction detected
1142868:20240729:155532.426 query [txnlev:0] [delete from trends_uint where itemid=221529 limit 2000]
1142868:20240729:155532.433 query without transaction detected
1142868:20240729:155532.434 query [txnlev:0] [delete from housekeeper where housekeeperid in (1258261,1258266,1258273);
]
1142868:20240729:155532.435 End of housekeeping_cleanup():8127
1142868:20240729:155532.435 housekeeper [deleted 2417857 hist/trends, 8127 items/triggers, 491 events, 76 problems, 0 sessions, 0 alarms, 0 audit, 0 autoreg_host, 0 records in 168.037358 sec, idle for 1 hour(s)]
1142868:20240729:155532.435 zbx_setproctitle() title:'housekeeper [deleted 2417857 hist/trends, 8127 items/triggers, 491 events, 0 sessions, 0 alarms, 0 audit items, 0 autoreg_host, 0 records in 168.037358 sec, idle for 1 hour(s)]'
1142868:20240729:155532.435 In zbx_ipc_async_socket_recv() timeout:3600

​
    Tags: None
    • Markku
      Senior Member
      Zabbix Certified SpecialistZabbix Certified ProfessionalZabbix Certified Expert
      • Sep 2018
      • 1781
      #2
      Have you tried not overriding the housekeeper setting in the global configuration, does the housekeeper then delete the data better?

      I'm using database table partitioning, having history and trends housekeeper disabled, so I don't have much experience about housekeeper workings recently.

      Markku

        Comment

        • Bryam Balan
          Junior Member
          • Jul 2024
          • 4
          #3
          Originally posted by Markku
          Have you tried not overriding the housekeeper setting in the global configuration, does the housekeeper then delete the data better?

          I'm using database table partitioning, having history and trends housekeeper disabled, so I don't have much experience about housekeeper workings recently.

          Markku
          I've confirmed this. When running the Housekeeper, I noticed it processes some extra queries at the beginning. Even though it appears to be considering the item macro, which is set to 15 days, the timestamp it's using for cleanup is from January 2024.

          Code:
          1429909:20240729:170642.756 zbx_ipc_async_socket_recv() code:3 size:0 data:
1429909:20240729:170642.756 End of zbx_ipc_async_socket_recv():0
1429909:20240729:170642.756 forced execution of the housekeeper
1429909:20240729:170642.756 In zbx_ipc_async_socket_recv() timeout:0
1429909:20240729:170642.756 End of zbx_ipc_async_socket_recv():0
1429909:20240729:170642.756 executing housekeeper
1429909:20240729:170642.756 zbx_setproctitle() title:'housekeeper [connecting to the database]'
1429909:20240729:170642.756 In zbx_db_connect() flag:0
1429909:20240729:170642.757 End of zbx_db_connect():0
1429909:20240729:170642.757 zbx_setproctitle() title:'housekeeper [removing old history and trends]'
1429909:20240729:170642.757 In housekeeping_history_and_trends() now:1722283602
1429909:20240729:170642.757 In hk_history_delete_queue_prepare_all()
1429909:20240729:170642.757 query [txnlev:0] [select itemid,min(clock) from history group by itemid]
1429909:20240729:170648.489 slow query: 5.732123 sec, "select itemid,min(clock) from history group by itemid"
1429909:20240729:170648.491 query [txnlev:0] [select itemid,min(clock) from history_str group by itemid]
1429909:20240729:170651.793 slow query: 3.302460 sec, "select itemid,min(clock) from history_str group by itemid"
1429909:20240729:170651.795 query [txnlev:0] [select itemid,min(clock) from history_log group by itemid]
1429909:20240729:170651.796 query [txnlev:0] [select itemid,min(clock) from history_uint group by itemid]
1429902:20240729:170721.384 cannot decrease log level: minimum level has been already set
1429909:20240729:170748.664 slow query: 56.868692 sec, "select itemid,min(clock) from history_uint group by itemid"
1429909:20240729:170748.677 query [txnlev:0] [select itemid,min(clock) from history_text group by itemid]
1429909:20240729:170749.154 query [txnlev:0] [select itemid,min(clock) from trends group by itemid]
1429909:20240729:170821.611 slow query: 32.456979 sec, "select itemid,min(clock) from trends group by itemid"
1429909:20240729:170821.614 query [txnlev:0] [select itemid,min(clock) from trends_uint group by itemid]
1429909:20240729:171345.208 slow query: 323.593115 sec, "select itemid,min(clock) from trends_uint group by itemid"
1429909:20240729:171345.263 query [txnlev:0] [select i.itemid,i.value_type,i.history,i.trends,h.hostid from items i,hosts h where i.flags in (0,4) and i.hostid=h.hostid and h.status in (0,1)]
1429909:20240729:171345.946 In substitute_simple_macros_impl() data:'15d'
1429909:20240729:171345.946 End substitute_simple_macros_impl() data:'15d'
1429909:20240729:171345.946 In substitute_simple_macros_impl() data:'1096d'
1429909:20240729:171345.946 End substitute_simple_macros_impl() data:'1096d'
1429909:20240729:171345.946 In substitute_simple_macros_impl() data:'15d'
1429909:20240729:171345.946 End substitute_simple_macros_impl() data:'15d'
1429909:20240729:171345.946 In substitute_simple_macros_impl() data:'1096d'
1429909:20240729:171345.946 End substitute_simple_macros_impl() data:'1096d'
1429909:20240729:171345.946 In substitute_simple_macros_impl() data:'15d'
1429909:20240729:171345.946 End substitute_simple_macros_impl() data:'15d'
1429909:20240729:171345.946 In substitute_simple_macros_impl() data:'1096d'
....
1492289:20240730:091645.306 query [txnlev:0] [delete from history_uint where itemid=23271 and clock<1704913371]
.....

1704913371 = GMT: Wednesday, 10 January 2024 19:02:51

            Comment

            • Markku
              Senior Member
              Zabbix Certified SpecialistZabbix Certified ProfessionalZabbix Certified Expert
              • Sep 2018
              • 1781
              #4
              I've been collecting historical data since January 10, 2024. I've recently enabled the Housekeeper and set its global configuration to remove data older than 15 days.
              Do you mean that before just recently you didn't delete history data at all but kept everything?

              Markku

                Comment

                Previous template Next
                Working...