Implemented in 

• pre-6.0.18rc1 dc9d5faee7a, b079b7e2fa1
• pre-6.4.3rc1 813180f225f, 0f33adf9096
• pre-7.0.0alpha1 (master) 54dbce78b45, 6da43f9652a, e164f80595b

Documentation updated:

• Introduction > What's new in Zabbix (6.0.18, 6.4.3)
• Installation > Upgrade notes (6.0.18, 6.4.3)
• Appendixes > Process configuration > Zabbix proxy (6.0, 6.4, 7.0)

• CentOS Linux release 7.9.2009 (Core
• HyperV hypervisor
• SSD Disk
• Postgresql 9

Could you check if 'select itemid,min(clock) from history_uint group by itemid' returns NULLs ? That would crash housekeeper, however for that to happen something is wrong with history_uint table.

If it crashes later, could you please get the crash with DebugLog 4 ? That might to localize the crash. Also attaching objdump (objdump -DSswx <server binary path>) might help with it.

The query does not return NULL. We'll keep you posted about debug leve and objdump.

I've logged the housekeeper with 4 zabbix_server-housekeeper-debug4.log and 5 zabbix_server-housekeeper-debug5.log . Also I've attached the objdump zabbix_server_objdump.tar.gz .

An update to 4.0.42 also did not help.

The query mentioned above returns this:

select itemid,min(clock) from history_uint group by itemid;

       itemid       |    min     
--------------------+------------
             307879 | 1655794653
             341398 | 1655794198
              55290 | 1655794593
...
             271657 | 1648622773
             239576 | 1655795928
(33327 rows)

The other queries are:

select itemid,min(clock) from history group by itemid;

 itemid |    min     
--------+------------
 328954 | 1653806554
 210645 | 1653806652
 278938 | 1653979329
 239961 | 1655793752
...
 281661 | 1653979541
 365947 | 1656419792
(16674 rows)

select itemid,min(clock) from history_str group by itemid;

 itemid |    min     
--------+------------
 258851 | 1655794340
 102834 | 1655796579
 303319 | 1656226519
...
 283074 | 1653979404
  83203 | 1655795654
 119252 | 1655796110
 277617 | 1653979618
(14938 rows)

Could you please add sorting to history_uint (I'm interested if there are any abnormal values in the lowest/highest range), and maybe explicitly check for null clock ?

select itemid,min(clock) from history_uint group by itemid order by min(clock);
select itemid from history_uint where clock is null;

Everything (backtrace, objectdump and logs) tells that it crashed when trying to convert a null clock to integer.

select itemid,min(clock) from history_uint group by itemid order by min(clock);

       itemid       |    min     
--------------------+------------
             209544 | 1550225701
             209541 | 1550225701
             243194 | 1571881325
...
             366315 | 1656504283
             366306 | 1656504583
             366311 | 1656504584
             366319 | 1656506719
 595882534286393346 |           
 595882534286393345 |           
(33333 rows)       

Looks like those last two values could be the culprit.

Right, that's were the crashes are coming from. Are those real items? How many null clock records are there? Do 'value' contents in those records look normal? The clock field should have 'not null' setting, so it's quite weird. At any rate you should be safe removing the offending records from history_uint.

Thanks. That fixed it!

As the data was "corrupt" in Postgres and a DELETE did not work, due to the pg_subtrans errors we had to do the following workaround:

1. Stop Zabbix and webserver processes
2. Dump the offending table

   pg_dump -cCxO -Fc --no-security-labels -t history_uint zabbix > history_uint.pg_dump
   

3. Convert Dump to text (better for restore as it uses COPY instead of INSERT

   pg_restore history_uint.pg_dump > history_uint.sql
   

4. Remov empty/wrong entries

   sed -i '/595882534286393346/d' history_uint.sql
   sed -i '/595882534286393345/d' history_uint.sql
   

5. Restore the table

   pv /data/history_uint.sql | psql -U zabbix zabbix
   

6. Start Zabbix and webserver processes

Again, thanks a lot for the help! It is very much appreciated!

Frontend part implemented in
 - 4.0 feature/ZBXNEXT-5176
 - 4.2 feature/ZBXNEXT-5176_4.2

Fixed in:

• 4.0.11rc1 f2770b316ef
• 4.2.5rc1 c9fc882527c
• 4.4.0alpha1 (master) 5f0957e8233, 97259c1bda8

Documentation changes:

• Item configuration: 4.0, 4.2, 4.4
• What's new: 4.0.11, 4.2.5

Currently my only workaround is to deactivate global override and individually set to 7d and master items to 0.

Hello Markus,
Your statement is fully correct. But this is more a feature request rather a bug, so moved it here.

Additionally - I would say that this becomes a problem not only with Timescale support, but also without it since Zabbix is more and more about pre-processing and dependent items. Quite often users have a master item that gathers big amount of data which is being extracted by dependent items, and general Idea is to drop that huge data chunk in preprocessing stage without storing to database.

In a large environments quite often Override housekeeper is used, and that again will negatively affect such master items.

There is an alternative to global overrides — user macros.

@Glebs - that is my current workaround. I define an global macro {$DEFAULT_HISTORYPERIOD} = 7d and use use that in the items. Actually it's one of the first things I change in all my installations and on all Templates/Items. TBT I often have thought to bring that in for a change request.

But i still see how it is better to provide the global override - it just should honour the 0 setting!

@Dmitrijs - I thought it would fit better as a bug as I really thought it worked in previous versions. But I'm happy you see that as a problem too.

Official templates don't always follow the best practices of template building. As far as I recall discussions during ZBXNEXT-1675 implementation, it was decided not to use macros in update intervals and history/trend storage settings in official templates in order to keep them "humane" and easier to understand for Zabbix beginners. But you can still try to convince Zabbix to do this by filing a feature request and gathering few votes. Sometimes people do change their minds.

I think that Zabbix must not overwrite history storage period for items configured to have no history (i.e. item history period set to 0).

Another option could be to override only if the item period is larger. eg change the terminology there to "Maximum history period" or similar. This might be useful in some use cases, where no partitioning is used, but user would still wants smaller history period for some items.
Example:
Override max history to 30 days, but user has some items, that update really often, but do not require that much history. For such items, 1 day might be enough history and the setting might be honored in this case.

Fixed in:

• 4.0.11rc1 f2770b316ef
• 4.2.5rc1 c9fc882527c
• 4.4.0alpha1 (master) 5f0957e8233, 97259c1bda8

Documentation changes:

• Item configuration: 4.0, 4.2, 4.4
• What's new: 4.0.11, 4.2.5

I doubt it will ever be implemented due to performance considerations.

thanks for the response - performance is indeed the biggest concern here
could other backends for history in the future help somewhat ?
assuming only the zabbix server updates the data, it could also keep a record to make housekeeper's job easier, but that might be to complicated.

having a way to manually clean things up would still be desired - for example, one might test discovery actions, then start fresh with an action that only reacts to "discovered" events - that's not possible without direct db changes.

as an example of that, see https://www.reddit.com/r/zabbix/comments/53syqy/discovery_rule_that_respects_host_groups/

All

see housekeeper table, it holds a list of deleted items and housekeeper deletes all data for those items following different rules. i guess we can close this issue then - please reopen if there's still a need to do something

Kindly note the housekeeper has been running for the same years these orphan items exist. Before posting I double checked to make sure the housekeeper completed its run, changing the number of items to process per run to unlimited. Even when that had completed, these history entries describing events that happened to itemids not in the items table remained. I rebooted a few times, let the housekeeper complete, and yet millions of records referring to item record ids not in the items table remain.

So, closing this item on the basis the housekeeper would take care of it needs further explanation.

See reply above.

in general, it would be best to discuss things like this in one of the options at https://www.zabbix.org/wiki/Getting_help - irc would be a great place - but in any case, you did not specify zabbix version. if it's a recent one and you have upgraded, check that housekeeper is enabled at all in administration -> general -> housekeeping

I do think the housekeeper has been properly enabled and configured. Note the version is also in the shot, 2.2.

Really, just a feature request for an option to delete all references everywhere to items that no longer exist.

as noted, that is supposed to happen already. please use https://www.zabbix.org/wiki/Getting_help to discuss the details, until a specific bug or feature request can be reported.

Also as noted, though it was supposed to happen, it was not happening. So here we are on this page where I think it's created to ask for features.

To be clear: The feature requested is a server command line one-of recovery option to delete every orphan item in the database and generally not proceed until the database is in a known consistent state.

Having done this manually, finding a few thousand orphan records in trends* and several million orphan records in history* not mentioned in houskeeper and also not in items I think I've presented a reasonable use case. Look at it this way, if the server flag finds nothing to do it can act as an integrity check and so give confidence the server is about to begin operations on a known-good platform.

It's a reasonable enough feature request IMHO.

Not absolutely sure, but there can be other use cases.
For example we delete a child node (which passed its history to master) on master.
As I recall it was discussed already in other issues.

In ZBX-9278 (which I posted earlier ) I suggested some exact solutions for almost the same aspects as Alexey mentioned here.

I cannot imagine how we could drop the housekeeper table at all.

I'd suggest to close current report as duplicate and move unique details to the ZBX-9278

Now I understand how Alexey's scenario would work and I agree that it would be nice a for following scenario:
Existing zabbix installation, where long time ago type of information has been changed for many items (itemIDs), and these itemIDs are still in zabbix configuration.

How to "clean" currently existing installations? - yes, remove values for every history/trends table for items which do not exist already for this table type.
My suggestion in ZBX-9278 would not help for this scenario.

Before working on this issue, take a look please to ZBX-10012 discussion.

Zabbix 2.2 and 2.4

I'd support it.
I know several zabbix users where such a message would provide positive effect.

Added to:

https://www.zabbix.com/documentation/2.2/manual/installation/upgrade
https://www.zabbix.com/documentation/2.4/manual/installation/upgrade

RESOLVED.

sasha Housekeeper is enabled by default, but disabled after upgrade procedure.

REOPENED

martins-v Revised, please review.

RESOLVED.

sasha CLOSED

Redhat Enterprise 6.x x64 - Percona MySQL 5.6

i'm inclined to close this as a duplicate of ZBXNEXT-2570 - would you agree ?

This needs to be done before continuing work on ZBX-4063.

RESOLVED in svn://svn.zabbix.com/branches/dev/ZBXNEXT-2016.

(1) Please, correct get_request() to getRequest() in the changed lines:
adm.housekeeper.php: 92,95,105-113,165,169
chart3.php: 139
chart7.php: 86

jelisejev RESOLVED in r40537.

iivs CLOSED.

TESTED

I've made a minor change before merging: changed "Internal housekeeping" to "Enable internal housekeeping". It clearer and more consistent with other labels that way.

Available in 2.2.1rc1 r40559 and 2.3.0 r40560.

(2) Noted it in:

• what's new https://www.zabbix.com/documentation/2.2/manual/introduction/whatsnew221
• upgrade notes https://www.zabbix.com/documentation/2.2/manual/installation/upgrade_notes_221

We should probably also update the housekeeper settings docs.

martins-v Updated:

• https://www.zabbix.com/documentation/2.2/manual/web_interface/frontend_sections/administration/general#housekeeper
• https://www.zabbix.com/documentation/2.4/manual/web_interface/frontend_sections/administration/general#housekeeper

RESOLVED.

jelisejev These two changes need to be documented as well:

Change "Keep history" to "History storage period" and "Keep trends" to "Trend storage period" in item and item prototype forms, mass update form and error messages.
Change "Keep history" and "Keep trends" to just "History" and "Trends" in the item configuration filter.

martins-v Updated documentation:

• https://www.zabbix.com/documentation/2.2/manual/config/items/item (field label and screenshot)
• https://www.zabbix.com/documentation/2.2/manual/config/items/itemupdate (screenshot)
• https://www.zabbix.com/documentation/2.2/manual/web_interface/frontend_sections/configuration/hosts/items#filter (item filter screenshot)

The respective pages for 2.4, 3.0 have also been updated in this way. RESOLVED.

jelisejev CLOSED.

(3) also should be documented in :

• https://www.zabbix.com/documentation/2.2/manual/web_interface/definitions
• https://www.zabbix.com/documentation/2.4/manual/web_interface/definitions

martins-v For 2.2 noted that the constant is removed in 2.2.1; removed entirely from 2.4. RESOLVED.

jelisejev CLOSED.

This can also resolve ZBXNEXT-953. In the supplied patch we delete from the housekeeper table everything related to the disabled (partitioned) tables, so the housekeeper table doesn't keep filling up.

Patch for this feature.

Feature implementation.

I recall similar discussion (not very sure) here in Jira or on zabbix forum, but cannot find it at the moment.
added:
ohh, probably I meant ZBXNEXT-1539

Indeed this is similar. In ZBXNEXT-1539 there are two patches:
One makes sure we don't query from history tables more data than required which seems like a good idea in any case (regardless of partitioning).
The other patch in removes some work from the housekeeper but doesn't seem to handle other the work done on the "housekeeping_cleanup()" and doesn't provide enough flexibility in my opinion regarding what's partitioned.

Draft specification is available here https://www.zabbix.org/wiki/Docs/specs/ZBXNEXT-1649 for public review.

The specs seems promising and address the problem perfectly. One thing that comes to mind is that if there's willingness to redesign the housekeeper to that extent it might be worth considering adding partitioning itself as a feature to zabbix and make the housekeeper responsible for clearing the partitions.

In my main installation partition management is handled by external cron jobs. I've seen blog posts about how to manage partitions by adding stored procedures to the database too. But ideally this will all be handled by the housekeeper - perhaps as a second step or long term goal.

The major problem with partitioning is that it is database engine specific. Some engines (think of most of NoSQL solutions) handle data expiration differently with no or limited support of data partitioning.

(1) Let's increase the with of the label column on the housekeeper setting page so that the text would fit properly.

oleg.egorov RESOLVED IN r35001

jelisejev If you need to define a page-specific style, add a class to the widget div, not tab div.

oleg.egorov RESOLVED IN r35034

jelisejev To be more consistent with other class names please rename the class to "hk".

oleg.egorov RESOLVED IN r35062

jelisejev Please review some minor changes in r35072.

oleg.egorov CLOSED

(2) Some text corrections:
Remove IT services history -> Remove IT service history
Remove IT services history older than (in days) -> Remove IT service history older than (in days)
Remove autoregistration events and alerts older than (in days) -> Remove auto-registration events and alerts older than (in days)
### - Overridden by global housekeeper settings. -> Overriden by global housekeeper settings (#### day/days)

The link in the last message remains as is. Also add a translation context to the "global housekeeper settings" link so it could be properly translated.

oleg.egorov RESOLVED IN r35001

jelisejev
1. _s('Overridden by') - don't use the _s() helper here, the string doesn't have any arguments.
2. _('global housekeeper settings') - add a translation context to this string so that translators know, that this string is a part of a sentence.
3. when translating the "1 day/2 days" string don't substitute the "days" word as a parameter, use the _n() helper: it also supports parameters.
4. Don't use original gettext functions like ngettext(), use our helper wrappers instead.
5. In line 374: $data['config'] = select_config(); - the $data variable contains data passed to the view from the controller. To store configuration data - introduce a separate variable.

oleg.egorov RESOLVED IN r35047

jelisejev Please review r35056. I've corrected the usage of the _n() function and split the "Overriden by global housekeeper settings (# day/days)" string into "Overriden by global housekeeper settings" and "# day/days" to simplify translation. I've also added code comments to out getttext functions so that their usage would be clearer.

oleg.egorov CLOSED

jelisejev Missed one more thing, these strings must also be corrected in validation and audit messages in adm.housekeeper.php. REOPENED.

jelisejev Another minor correction in r35076 and 35077.

oleg.egorov CLOSED

(3) In configuration.item.edit.php:

1. No need for "== 1" in "$data['config']['hk_history_global'] == 1", just "$data['config']['hk_history_global']".
2. Please use the methods available in CWebUser: CWebUser::getType() instead of CWebUser::$data['type'].

oleg.egorov RESOLVED IN r35001

jelisejev CLOSED.

(4) In administration.general.housekeeper.edit.php

1. Please format the include according to the guidelines https://www.zabbix.org/wiki/Docs/specs/coding_style#File_includes
2. The CCheckBox::__construct method can accept a boolean value for the $checked parameter. No need to pass "yes" or "no".

oleg.egorov RESOLVED IN r35001

jelisejev No need to write the full "($this->data['config']['hk_audit_mode'] == 0) ? false : true" expression, you can just write "!$this->data['config']['hk_audit_mode']".

oleg.egorov RESOLVED IN r35035

jelisejev CLOSED.

(5) In CChart and CPie

1. Same as (3.1).
2. select_config(true) - true is already the default value for the first parameter, not need to pass it explicitly.
3. Let's pass the value of "hk_trends" to the CChart/CPie objects from the outside, either in the construct method or through a property. It's bad design for the graph object to access configuration data directly.

oleg.egorov RESOLVED IN r35001

jelisejev I meant that the "history" parameter should be passed to the CChart() and CPie() object when creating them, it shouldn't load them from the database itself. Also, if you add a property to a class, make sure to define it explicitly, with a corresponding PHP doc comment.

oleg.egorov RESOLVED IN r35035

jelisejev
1. Name "setHistory" is uncanonical, please rename it to something more meaningful, like "historyPeriod"; also explicitly define the propetry in both classes with the corresponding PHPDoc.
2. Add a PHP doc to the setHistory() method. Also don't forget to rename it when renaming the property.
3. In CChart.php line 221 "if ($this->setHistory) {" setHistory can also be "0", that means we need to always use history. You'll need to replace the if statement with "if ($this->setHistory !== null) {". Same thing in CPie.
4. When creating chart object, the setHistory property could be set simpler like this:

$graph->setHistory(($config['hk_history_global']) ? $config['hk_history'] : null);

Also note, that it's more logical to pass NULL in global history overriding is disabled.

oleg.egorov RESOLVED IN r35067

jelisejev Please review my changes in r35073.

oleg.egorov CLOSED

Server side fixed and ready for testing in development branch svn://svn.zabbix.com/branches/dev/ZBXNEXT-1649 r34985

(10) Charts must use the "hk_history" field, not "hk_trends".

oleg.egorov RESOLVED IN r35035

jelisejev CLOSED.

(11) [D] We'll need to remove the ZBX_HISTORY_DATA_UPKEEP constant from the docs

https://www.zabbix.com/documentation/2.2/manual/web_interface/definitions?s[]=zbx&s[]=history&s[]=data&s[]=upkeep

jelisejev We decided to leave the ZBX_HISTORY_DATA_UPKEEP constant as is for now. CLOSED.

(12) The "Overriden by global housekeeper settings (#### day/days)" must be displayed in item prototype and template item forms.

oleg.egorov RESOLVED

jelisejev Sorry, typo, they must NOT be displayed.

oleg.egorov RESOLVED IN r35064

jelisejev CLOSED.

Frontend TESTED.

Server side successfully tested!

(15) [I] The upgrade patch only saves old configuration values for the "hk_events_trigger" field.

sasha RESOLVED in r35108.

jelisejev CLOSED.

(16) I have the "Enable housekeeping" and "Override item history period" checkboxes for history enabled. When I unset the "Enable housekeeping" and save the form, the "Override item history period" checkbox is also unset. It shouldn't be updated.

oleg.egorov RESOLVED IN r35193

jelisejev CLOSED.

(17) In default.css:

1. It would be better if instead of duplicating styles for .element-group and .element-group-first you would assign both classes to the first group, set the border to "0" in .element-group and then undo it .element-group-first. That way all of the other styles won't need to be duplicated.
2. Instead of introducing the "hkElementLabel" class you can use a generic selector, like ".hk .element-group label" and remove the extra div.

oleg.egorov RESOLVED IN r35183

jelisejev Regarding 1 - that's not exactly what I meant. Please review my changes in r35212.

oleg.egorov CLOSED

(18) In aministration.general.housekeeper.edit.js.php

Instead of manually ednabling and disabling inputs when settings checkboxes, you can just set the checkbox and then call trigger('change') to do everything else.

oleg.egorov RESOLVED IN r35205

jelisejev CLOSED.

Frontend TESTED. Please close (17) before merging.

Available in version pre-2.1.0 (trunk) r35212
CLOSED

(19) Documentation needs to be updated.

martins-v Updated:

https://www.zabbix.com/documentation/2.2/manual/introduction/whatsnew220#finer_control_over_housekeeping_tasks
https://www.zabbix.com/documentation/2.2/manual/installation/upgrade_notes_220#housekeeper_changes
https://www.zabbix.com/documentation/2.2/manual/web_interface/frontend_sections/administration/general
https://www.zabbix.com/documentation/2.2/manual/appendix/config/zabbix_server (removed DisableHousekeeping parameter)

sasha CLOSED

FIXED IN version pre-2.1.0 (trunk) r35343

(21) currently one label says "Override item trends period"

should be changed to "Override item trend period" - sounds better and is more consistent with "Keep trend data for" string

oleg.egorov FIXED IN version pre-2.1.0 (trunk) r35368

CLOSED

FIXED IN version pre-2.1.0 (trunk) r35368
CLOSED

(22) [S] PostgreSQL errors when running server:

  7209:20130509:180125.988 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  column "i.history" must appear in the GROUP BY clause or be used in an aggregate function
 [select i.itemid,min(t.clock),i.history from history t,items i where t.itemid=i.itemid group by i.itemid]
  7219:20130509:180125.989 server #25 started [proxy poller #1]
  7209:20130509:180125.989 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  column "i.history" must appear in the GROUP BY clause or be used in an aggregate function
 [select i.itemid,min(t.clock),i.history from history_str t,items i where t.itemid=i.itemid group by i.itemid]
  7194:20130509:180125.989 server #0 started [main process]
  7209:20130509:180125.989 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  column "i.history" must appear in the GROUP BY clause or be used in an aggregate function
 [select i.itemid,min(t.clock),i.history from history_log t,items i where t.itemid=i.itemid group by i.itemid]
  7220:20130509:180125.989 server #26 started [self-monitoring #1]
  7209:20130509:180125.989 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  column "i.history" must appear in the GROUP BY clause or be used in an aggregate function
 [select i.itemid,min(t.clock),i.history from history_uint t,items i where t.itemid=i.itemid group by i.itemid]
  7209:20130509:180125.990 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  column "i.history" must appear in the GROUP BY clause or be used in an aggregate function
 [select i.itemid,min(t.clock),i.history from history_text t,items i where t.itemid=i.itemid group by i.itemid]
  7215:20130509:180125.990 server #21 started [history syncer #3]
  7209:20130509:180125.990 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  column "i.trends" must appear in the GROUP BY clause or be used in an aggregate function
 [select i.itemid,min(t.clock),i.trends from trends t,items i where t.itemid=i.itemid group by i.itemid]
  7209:20130509:180125.991 [Z3005] query failed: [0] PGRES_FATAL_ERROR:ERROR:  column "i.trends" must appear in the GROUP BY clause or be used in an aggregate function
 [select i.itemid,min(t.clock),i.trends from trends_uint t,items i where t.itemid=i.itemid group by i.itemid]
  7209:20130509:180125.999 housekeeper deleted: 0 records from history and trends, 0 records of deleted items, 0 events, 0 sessions, 0 service alarms, 0 audit items

wiper RESOLVED in r35560

sasha TESTED

<richlv> this was merged -> CLOSED

Released in pre-2.1.0 r35581

this might have resulted in a translation-related regression : ZBX-6978

Why we didn't mention in doc that in 2.2 the housekeeper will have better performance ?
https://www.zabbix.com/documentation/2.2/manual/introduction/whatsnew220#finer_control_over_housekeeping_tasks

The spec https://www.zabbix.org/wiki/Docs/specs/ZBXNEXT-1649#Performance_improvements says:

it should process one table after another (history->history_uint->etc) instead of grouping operations on per-itemid. In this case DB cache will be used with much better efficiency thus greatly reducing disk IO and seek operations.

Dunno, but there were multiple design changes that should positively affect the performance. Although the resulting performance improvements were not tested.

I sent it once by email, but wort to mention it also here:

just got a stat from a prod installation (a middle power VM).
previously (in 2.0) housekeeper worked for 3,5 hours deleting ~ 3,3M of records.
now (in 2.2) it spends 1,5 hours for the same 3,3M records.
conclusion -> housekeeping in 2.2 is better

I'm still seeing same error on Zabbix 2.2.3:

140502 14:07:48 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: delete from history_uint where itemid=30748 limit 500

So, it's really fixed for Zabbix 2.2.0 ?

Thanks.

Updated stat (server 2.2.9) as for my previous comment, just in case:

  3195:20150410:045134.982 housekeeper [deleted 5108319 hist/trends, 46500 items, 9427 events, 0 sessions, 0 alarms, 0 audit items in 7999.990979 sec, idle 1 hour(s)]
  3195:20150410:055134.982 executing housekeeper
  3195:20150410:075934.660 housekeeper [deleted 4929723 hist/trends, 46500 items, 10737 events, 0 sessions, 0 alarms, 0 audit items in 7679.676903 sec, idle 1 hour(s)]
  3195:20150410:085934.660 executing housekeeper
  3195:20150410:111026.979 housekeeper [deleted 4693741 hist/trends, 80009 items, 10044 events, 0 sessions, 0 alarms, 3 audit items in 7852.318823 sec, idle 1 hour(s)]

real NVPS year ago was ~210, now it's ~360

Wort to mention that related changes were done further in ZBXNEXT-2016

All systems with partitioned databases should have this functionality included

This additional patch removes full table scan of all tables in case of partitioned system.

ZBXNEXT-1649 is related

It will be resolved under ZBXNEXT-1649.

lastest trunk revision

hmm. the problem is that frontend has no idea that housekeeper is disabled - it currently can not query server for that, and server does not store that in the database...

maybe housekeeper option move into database? in table config?

so let's consider this as a feature request to keep housekeeper config in the db & for frontend not to write housekeeper entries if housekeeping is disabled.

this would solve ZBXNEXT-30

I think not needed write into housekeeper table information about removed items (for few days we have 5million records in this table, but we not using housekeeper)

How do you know the housekeeper will not be enabled eventually? Let's move housekeeper setting to the front-end, but information regarding removed objects must be also stored regardless of housekeeper settings.

It will be resolved under ZBXNEXT-1649.

As far as I see, this issue can be closed.

CLOSED.

Implemented in ZBXNEXT-1675, closing as Duplicate.

most likely - a switch for zabbix_server which signals a running server instance to start housekeeping. that could also be added as a script from a frontend etc

I think this should be handled as two separate issues:
1) Specifying the housekeeping period(s) in the configuration file
2) Reloading the configuration file (safe options) without restart, this would satisfy the need for users wanting cronjobs to do fancy stuff (if such users exists?)

Our system has a fairly large housekeeping backlog so I would like to implement part 1. Part 2 is not important for me.

Suggested specification/implementation of part 1:
New configuration parameter:
HousekeepingPeriod
Simple example, run nightly all days:
1-7,00:00-07:00
Complex example, run outside workhours:
1-5,00:00-08:00;1-5,16:00-23:59;6-7,00:00-23:59;
An empty or undefined value behaves as the current implementation - i.e. runs all day with the specified HousekeepingFrequency.

All loops in the housekeeper process should stop when they are outside the configured period and sleep. Ideally the process should wake at the beginning of each configured period. This parameter combined with high values for HousekeepingFrequency could give some weird results, so HousekeepingFrequency=1 is probably best?

Alexei/Rich - do you think such an implementation would be accepted?

Any news about this feature ?

I'm also think it could be great if we could define when the housekeeping start.

generic runtime server control : ZBXNEXT-416
forced item check : ZBXNEXT-653

Please implement this - very useful.

I agree, this functionality will be very useful.

I would like to know if the implementation of this feature is planned for a next release?

It is a vital principle to be able to schedule resources consuming tasks.

there actually is some discussion about redesigning retention configuration to make housekeeper potentially unnecessary in the future, so housekeeper improvements currently are on hold

Using SSD for DB makes housekeeping impact on system load relatively low. We had forgot about housekeepeng after migrating to SSD.

@richlv: table-partitioning?!

I agree, being able to schedule housekeeping to a specific time is very important - Zabbix is borderline unusable when it is running.

Housekeeper starts if zabbix_server starts. Just create a cronjob to restart the zabbix_server and change the HouseKeepingFrequency to 24 in the zabbix_server.conf.

Steve Mushero talked about this during the Zabbix Conference 2014. Apparently he has a solution for this.
I'd also like this feature implemented, perhaps his patch can be used as a basis?

Fixed in svn://svn.zabbix.com/branches/dev/ZBXNEXT-207

missing specification ....

wiper right, that would have helped to avoid a misunderstanding in current implementation

wiper I added housekeeper information to https://www.zabbix.org/wiki/Docs/specs/ZBXNEXT-101 (we should copy it to some other place for generic specifications).

(1) The config_cache_reload message should keep the old format and also the new housekeeper_execute message should be sent without defining target scope (like config_cache_reload).

Please review my changes in r49585

igorsh Thank you. Looks good.

wiper CLOSED

(2) Housekeeper wakeup should be renamed to housekeeper execute (in command line options and constant definitions).

igorsh RESOLVED in r49588.

wiper CLOSED

(3) man pages and documentation have to be updated

man pages have been updated in r49592.

wiper CLOSED

(4) Allow setting HousekeepingFrequency option in configuration file to zero to disable automatic housekeeping. This will allow to use only manual (scheduled) housekeeping procedures.

The following steps must be implemented:

1. allow HousekeepingFrequency option to accept zero value
2. add a function zbx_sleep_forever(), which would sleep until zbx_wakeup() is called
3. update housekeeper process to use zbx_sleep_forever() if HousekeepingFrequency is set to zero

igorsh RESOLVED in r49907, r49912, r49913.

wiper CLOSED

The specifications were created https://www.zabbix.org/wiki/Docs/specs/ZBXNEXT-207

It'd also be good to be able to configure the range the housekeeper cleans up in one run, not just a fixed 4x last sleeptime.

configuring the range would be a separate feature request, though

The original requestee may or may not know what's happening under the hood and how the change may effect performance. Going from short housekeeping intervals to a once-per-night run may kill some servers which have more than the normal amount of cleanup to do. Implementing at least a configurable factor at the same time would be smart.

The cleanup interval must greater than the time between cleanups. So going from short intervals to a nightly run would require the cleanup interval to be at 24+ hours anyway.

(5) Documentation must be updated:

1. new runtime control option -R housekeeper_execute
2. disabling automatic housekeeping procedure by setting HousekeepingFrequency to 0 in server/proxy configuration files

igorsh RESOLVED in

https://www.zabbix.com/documentation/3.0/manual/introduction/whatsnew300
https://www.zabbix.com/documentation/3.0/manual/concepts/server
https://www.zabbix.com/documentation/3.0/manual/concepts/proxy
https://www.zabbix.com/documentation/3.0/manual/appendix/config/zabbix_server
https://www.zabbix.com/documentation/3.0/manual/appendix/config/zabbix_proxy

wiper CLOSED

Successfully tested, but please review coding style and HousekeepingFrequency description changes in r50116

igorsh Thank you. The canges look good.

(6) Perhaps help info also has to be updatated.

Now:

    Runtime control options:
      config_cache_reload               Reload configuration cache
      housekeeper_execute               Execute the housekeeper

but it should be like that

    Runtime control options:
      config_cache_reload               Reload configuration cache. Ignored if cache is being currently loaded.
      housekeeper_execute               Execute the housekeeper. Ignored if housekeeper is being currently executed.

wiper I'm not sure if I'd mention it even in manual.

<richlv> we mention it for the config cache reload in the manpages, but not in the help message - i would do the same for the housekeeper

igorsh Then it stays unchanged. CLOSED.

(7) Please take a look at r50146, r50147. It fixes few issues on systems without sigqueue support.

igorsh Thank you. CLOSED

Fixed in 2.5.0 (trunk) r50157.

(8) Man pages have to be updated:

https://www.zabbix.com/documentation/3.0/manpages/zabbix_server
https://www.zabbix.com/documentation/3.0/manpages/zabbix_proxy

<richlv> synced web versions from the manpages, RESOLVED

igorsh CLOSED

(9) Broken reloading of configuration cache on proxy side

Available in svn://svn.zabbix.com/branches/dev/ZBXNEXT-207 r52148.

RESOLVED.

wiper CLOSED

This fix is available in 2.5.0 (trunk) r52196.

another similar combo :
DisablePassive and StartAgents (which is for passive instances only)

ZBXNEXT-953 calls for moving housekeeper config into the db

I think that housekeeper settings must be configurable in the front-end under Administration->General->Housekeeper. See also my comment in ZBXNEXT-953.

It will be resolved under ZBXNEXT-1649.

ZBXNEXT-1649 removed disablehousekeeping, so this issue is not relevant anymore

Zabbix 5.0.14
PostgreSQL 12.5

Available in:

• 5.0.18rc1: 89be5099d3b
• 5.4.8rc1: ade7002d91d
• 6.0.0alpha7 (master): 748878a569f

Shouldn't there be a check for SERVER? Do we want to have that index in proxy?

UPD: Oh, judging by the code we always create indexes without the check for component.

I am not sure why it is still open. All DB upgrades require special permissions, there is nothing new. I am closing it.

4.0.7

The issue looks similar to ZBX-18802

Excuse me.
It's the same problem.

confirmed.
It does not occur in Zabbx 4.0.29.

Resolved in development branch feature/ZBX-18206-5.0.

Fixed in:

• 5.0.4rc1 0bd9c62e99b

Btw, affected installations likely may log slow SQL warnings for this SQL:

select housekeeperid,tablename,field,value from housekeeper where tablename in ('events') order by tablename

as the table became big.
I saw these slow SQL already on 2 installations.
They should go away after some period after upgrade, of course.

Fixed in

• pre-5.0.3rc1 9c3ca976629
• pre-5.2.0alpha1 (master) 9078bba3fef

• pre-5.0.10rc1 4189dd282e
• pre-5.2.6rc1 eaf712507e
• pre-5.4.0beta1 c44275c1b7

If talking about second part of the issue it would be nice not to generate internal events if checked in cache that there are no actions to be executed for them.

Centos7 - Zabbix Server 4.2.2

Hello,

If history* and trends* tables are partitioned - housekeeping for that items should be disabled.
Add Administration - General - Housekeeping screenshot and show output from the following SQL as one single text file:

show create table history\G;
show create table trends\G;
... # add output for all history*/trends* tables, replace * with actual names
show create table trends_*\G;
show create table history_*\G;
select from_unixtime(min(clock)) from history_str limit 1;

Regards,
Edgar

Hello,

information requested was attached.

Thank you,

Mariana

Hello,

All history*/trends* should be partitioned and housekeeping for this tables should be done by stored procedures or scripts, not by housekeeper. All other tables should be cleaned by housekeeper.

Also please note, 4.2 is an unsupported version now.

Please be advised that this section of the tracker is for bug reports only. The case you have submitted can not be qualified as one, so please reach out to [email protected] for commercial support or consultancy services. Alternatively, you can also use our IRC channel or community forum (https://www.zabbix.com/forum) for assistance. With that said, we are closing this ticket. Thank you for understanding.

Regards,
Edgar

On 4.2.6 version housekeeper settings, ar installed as:

Did execute from command line housekeeper multiple times. But records with an old timestamp (bigger than 1d) remain in the database.

aigars.kadikis, I confirm problem with remaining records with old timestamp in version 4.0.15.

Initial issue should be documented because housekeeper shouldn't remove orphaned records within data storage period due to performance reasons.

First, let's clarify the rules that housekeeper follow.
As stated in HousekeepingFrequency parameter documentation:

Note: To prevent housekeeper from being overloaded (for example, when history and trend periods are greatly reduced), no more than 4 times HousekeepingFrequency hours of outdated information are deleted in one housekeeping cycle, for each item. Thus, if HousekeepingFrequency is 1, no more than 4 hours of outdated information (starting from the oldest entry) will be deleted per cycle.

Basically, there are sliding time point for cleanup, where items on the left are removed.

After Zabbix server is restarted, once first housekeeping takes place, the cleanup point is initialized relatively to oldest record that match "outdated" criteria (has no parent and is older than data storage period).
If time difference between oldest item and "cleanup point" is significant it will take time (many housekeeper turns) for housekeeper to reach it.
But, we can speed that process up by "housekeeper_execute" runtime option. It will move cleanup point right by 4 hours in one turn.
Perhaps, we could always rely on oldest existing item when moving cleanup point right, but this will hit the performance, because events table may be huge.
Normally, if housekeeper is running periodically, it will keep cleaning outdated items properly.
If HousekeepingFrequency is set to 0, then user himself is responsible for starting housekeeper by runtime option at least once a day.
Also, if server restarts often, it might happen that housekeeper never runs, because its first run is postponed for 30 minutes after server start.

aigars.kadikis does it clarify your observations?
If so, I am going to close this ticket, because there is no problem with housekeeper.

Thank you atumilovics for the picture. 

If I execute a housekeeper manually a few times, then for some point the data should be cleared away. 

But still, the events (not the plain metrics) which have been generated by now non-existing triggers, still remains.

This is the last output when I manually execute the housekeeper:

  3696:20191202:131344.518 forced execution of the housekeeper
  3696:20191202:131344.520 executing housekeeper
  3696:20191202:131349.931 housekeeper [deleted 2324 hist/trends, 0 items/triggers, 43 events, 0 problems, 55 sessions, 0 alarms, 0 audit, 0 records in 5.396280 sec, idle for 1 hour(s)]
  3696:20191202:131403.992 forced execution of the housekeeper
  3696:20191202:131403.992 executing housekeeper
  3696:20191202:131409.421 housekeeper [deleted 12 hist/trends, 0 items/triggers, 8 events, 0 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 5.420089 sec, idle for 1 hour(s)]
  3696:20191202:131433.420 forced execution of the housekeeper
  3696:20191202:131433.420 executing housekeeper
  3696:20191202:131435.636 housekeeper [deleted 15 hist/trends, 0 items/triggers, 14 events, 0 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 2.213003 sec, idle for 1 hour(s)]
  3696:20191202:131500.763 forced execution of the housekeeper
  3696:20191202:131500.764 executing housekeeper
  3696:20191202:131502.872 housekeeper [deleted 15 hist/trends, 0 items/triggers, 8 events, 0 problems, 2 sessions, 0 alarms, 0 audit, 0 records in 2.107308 sec, idle for 1 hour(s)]
  3696:20191202:131613.983 forced execution of the housekeeper
  3696:20191202:131613.985 executing housekeeper
  3696:20191202:131618.497 housekeeper [deleted 38 hist/trends, 0 items/triggers, 14 events, 0 problems, 1 sessions, 0 alarms, 0 audit, 0 records in 4.499046 sec, idle for 1 hour(s)] 

Should I need to schedule a cronjob to execute the housekeeper manually a few times per hour?

aigars.kadikis  I don't think you really need a chronjob, 1h period is configurable by HousekeepingFrequency=1 (which is default) and it should work fine. Yes, it might not happen on the next housekeeper run, but will be done after several housekeeper turns for recently orphaned items.

Thank you. Will try to increase HousekeepingFrequency= and wait a few days.

Cannot reproduce the issue.

Updated documentation for the MaxHousekeeperDelete parameter in versions 3.0, 4.0, 4.4, 5.0. More specifically, the additional information is added as a footnote that is clearly linked to from the parameter description.

Hello Oleg,

Thank you for reporting the issue. Please, provide the following information:

1. operating system used and it's version
2. objdump -Dswx $(which zabbix_server) | gzip -c > zabbix_server.objdump.gz
3. ldd $(which zabbix_server)

Regards,
Edgar

Hi Edgar, thanks for reply. Attached zabbix_server.objdump.gz

# lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.6 LTS
Release: 16.04
Codename: xenial

# uname -a
Linux *** 4.15.0-38-generic #41~16.04.1-Ubuntu SMP Wed Oct 10 20:16:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux

# ldd $(which zabbix_server)
linux-vdso.so.1 => (0x00007ffc6a3f5000)
libmysqlclient.so.20 => /usr/lib/x86_64-linux-gnu/libmysqlclient.so.20 (0x00007f821e9b4000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f821e797000)
libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f821e57d000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f821e274000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f821e06c000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f821de68000)
libiksemel.so.3 => /usr/lib/x86_64-linux-gnu/libiksemel.so.3 (0x00007f821dc5a000)
libxml2.so.2 => /usr/lib/x86_64-linux-gnu/libxml2.so.2 (0x00007f821d89f000)
libodbc.so.2 => /usr/lib/x86_64-linux-gnu/libodbc.so.2 (0x00007f821d636000)
libnetsnmp.so.30 => /usr/lib/x86_64-linux-gnu/libnetsnmp.so.30 (0x00007f821d359000)
libssh2.so.1 => /usr/lib/x86_64-linux-gnu/libssh2.so.1 (0x00007f821d130000)
libOpenIPMI.so.0 => /usr/lib/libOpenIPMI.so.0 (0x00007f821ce22000)
libOpenIPMIposix.so.0 => /usr/lib/libOpenIPMIposix.so.0 (0x00007f821cc1c000)
libevent-2.0.so.5 => /usr/lib/x86_64-linux-gnu/libevent-2.0.so.5 (0x00007f821c9d6000)
libssl.so.1.0.0 => /lib/x86_64-linux-gnu/libssl.so.1.0.0 (0x00007f821c76d000)
libcrypto.so.1.0.0 => /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 (0x00007f821c328000)
libldap_r-2.4.so.2 => /usr/lib/x86_64-linux-gnu/libldap_r-2.4.so.2 (0x00007f821c0d7000)
liblber-2.4.so.2 => /usr/lib/x86_64-linux-gnu/liblber-2.4.so.2 (0x00007f821bec8000)
libcurl.so.4 => /usr/lib/x86_64-linux-gnu/libcurl.so.4 (0x00007f821bc59000)
libresolv.so.2 => /lib/x86_64-linux-gnu/libresolv.so.2 (0x00007f821ba3e000)
libpcre.so.3 => /lib/x86_64-linux-gnu/libpcre.so.3 (0x00007f821b7ce000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f821b404000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f821b082000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f821ae6c000)
/lib64/ld-linux-x86-64.so.2 (0x00007f821f498000)
libgnutls.so.30 => /usr/lib/x86_64-linux-gnu/libgnutls.so.30 (0x00007f821ab3c000)
libicuuc.so.55 => /usr/lib/x86_64-linux-gnu/libicuuc.so.55 (0x00007f821a7a8000)
liblzma.so.5 => /lib/x86_64-linux-gnu/liblzma.so.5 (0x00007f821a586000)
libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007f821a37c000)
libgcrypt.so.20 => /lib/x86_64-linux-gnu/libgcrypt.so.20 (0x00007f821a09b000)
libOpenIPMIutils.so.0 => /usr/lib/libOpenIPMIutils.so.0 (0x00007f8219e92000)
libsasl2.so.2 => /usr/lib/x86_64-linux-gnu/libsasl2.so.2 (0x00007f8219c77000)
libgssapi.so.3 => /usr/lib/x86_64-linux-gnu/libgssapi.so.3 (0x00007f8219a36000)
libidn.so.11 => /usr/lib/x86_64-linux-gnu/libidn.so.11 (0x00007f8219803000)
librtmp.so.1 => /usr/lib/x86_64-linux-gnu/librtmp.so.1 (0x00007f82195e7000)
libgssapi_krb5.so.2 => /usr/lib/x86_64-linux-gnu/libgssapi_krb5.so.2 (0x00007f821939d000)
libp11-kit.so.0 => /usr/lib/x86_64-linux-gnu/libp11-kit.so.0 (0x00007f8219139000)
libtasn1.so.6 => /usr/lib/x86_64-linux-gnu/libtasn1.so.6 (0x00007f8218f26000)
libnettle.so.6 => /usr/lib/x86_64-linux-gnu/libnettle.so.6 (0x00007f8218cf0000)
libhogweed.so.4 => /usr/lib/x86_64-linux-gnu/libhogweed.so.4 (0x00007f8218abd000)
libgmp.so.10 => /usr/lib/x86_64-linux-gnu/libgmp.so.10 (0x00007f821883d000)
libicudata.so.55 => /usr/lib/x86_64-linux-gnu/libicudata.so.55 (0x00007f8216d86000)
libgpg-error.so.0 => /lib/x86_64-linux-gnu/libgpg-error.so.0 (0x00007f8216b72000)
libheimntlm.so.0 => /usr/lib/x86_64-linux-gnu/libheimntlm.so.0 (0x00007f8216969000)
libkrb5.so.26 => /usr/lib/x86_64-linux-gnu/libkrb5.so.26 (0x00007f82166df000)
libasn1.so.8 => /usr/lib/x86_64-linux-gnu/libasn1.so.8 (0x00007f821643d000)
libcom_err.so.2 => /lib/x86_64-linux-gnu/libcom_err.so.2 (0x00007f8216239000)
libhcrypto.so.4 => /usr/lib/x86_64-linux-gnu/libhcrypto.so.4 (0x00007f8216006000)
libroken.so.18 => /usr/lib/x86_64-linux-gnu/libroken.so.18 (0x00007f8215df0000)
libkrb5.so.3 => /usr/lib/x86_64-linux-gnu/libkrb5.so.3 (0x00007f8215b1e000)
libk5crypto.so.3 => /usr/lib/x86_64-linux-gnu/libk5crypto.so.3 (0x00007f82158ef000)
libkrb5support.so.0 => /usr/lib/x86_64-linux-gnu/libkrb5support.so.0 (0x00007f82156e4000)
libffi.so.6 => /usr/lib/x86_64-linux-gnu/libffi.so.6 (0x00007f82154dc000)
libwind.so.0 => /usr/lib/x86_64-linux-gnu/libwind.so.0 (0x00007f82152b3000)
libheimbase.so.1 => /usr/lib/x86_64-linux-gnu/libheimbase.so.1 (0x00007f82150a4000)
libhx509.so.5 => /usr/lib/x86_64-linux-gnu/libhx509.so.5 (0x00007f8214e59000)
libsqlite3.so.0 => /usr/lib/x86_64-linux-gnu/libsqlite3.so.0 (0x00007f8214b84000)
libcrypt.so.1 => /lib/x86_64-linux-gnu/libcrypt.so.1 (0x00007f821494c000)
libkeyutils.so.1 => /lib/x86_64-linux-gnu/libkeyutils.so.1 (0x00007f8214748000)

Oleg,

One more thing, please. Version of the ElasticSearch used?

Regards,
Edgar

Does disabling housekeeping of history helps ?
Is there any history in MySQL database ?

select count(*) from history;
select count(*) from history_text;
select count(*) from history_uint;
select count(*) from history_str;
select count(*) from history_log;

Vladislav, history tables are empty since we switched to elasticsearch. I've checked, no records in history* tables.

Housekeeping for history currently enabled, I'll try now disable it and make few hk runs.

Disabled history housekeeping via web-interface and made 20 runs.

Memory leak is still present, but now it eats 6500 kbytes instead of 15872 kbytes for one run.

10 runs with 5 sec delay (1 sec is enough according to log)

# for i in {1..10}; do zabbix_server -R housekeeper_execute; sleep 5; pmap -x 1286 > $i; done
zabbix_server [30896]: command sent successfully
zabbix_server [31050]: command sent successfully
zabbix_server [31163]: command sent successfully
zabbix_server [31277]: command sent successfully
zabbix_server [31420]: command sent successfully
zabbix_server [31626]: command sent successfully
zabbix_server [31847]: command sent successfully
zabbix_server [32013]: command sent successfully
zabbix_server [32210]: command sent successfully
zabbix_server [32325]: command sent successfully

# for i in {1..10}; do grep -m1 00005637a6b58000 $i; done
00005637a6b58000 3765408 3765224 3765224 rw--- [ anon ]
00005637a6b58000 3771908 3771724 3771724 rw--- [ anon ]
00005637a6b58000 3778408 3778224 3778224 rw--- [ anon ]
00005637a6b58000 3784908 3784724 3784724 rw--- [ anon ]
00005637a6b58000 3791408 3791224 3791224 rw--- [ anon ]
00005637a6b58000 3797908 3797724 3797724 rw--- [ anon ]
00005637a6b58000 3804408 3804224 3804224 rw--- [ anon ]
00005637a6b58000 3810908 3810724 3810724 rw--- [ anon ]
00005637a6b58000 3817408 3817224 3817224 rw--- [ anon ]
00005637a6b58000 3823908 3823724 3823724 rw--- [ anon ]

What if trends housekeeping is disabled ?

With trends housekeeping disabled no leakage after 10 runs.

Enabled trends housekeeping and leak is back again, so definitively leak somewhere in that place.

Reproduced. Please also provide screenshot of housekeeper configuration from frontend

steps:

• Enable elastic
• Override item history period and item trend period

Observe memory leak:

==11409== For counts of detected and suppressed errors, rerun with: -v
==11409== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 48 from 48)
==11413== 32,768 bytes in 1 blocks are possibly lost in loss record 70 of 75
==11413==    at 0x4838748: malloc (vg_replace_malloc.c:308)
==11413==    by 0x483AD63: realloc (vg_replace_malloc.c:836)
==11413==    by 0x849BF0: zbx_realloc2 (misc.c:550)
==11413==    by 0x810B58: zbx_default_mem_realloc_func (algodefs.c:331)
==11413==    by 0x8359C6: zbx_vector_ptr_reserve (vector.c:28)
==11413==    by 0x49B9D8: hk_history_delete_queue_prepare_global (housekeeper.c:491)
==11413==    by 0x49BCF8: hk_history_delete_queue_prepare_all (housekeeper.c:546)
==11413==    by 0x49C177: housekeeping_history_and_trends (housekeeper.c:651)
==11413==    by 0x49E427: housekeeper_thread (housekeeper.c:1197)
==11413==    by 0x83D318: zbx_thread_start (threads.c:132)
==11413==    by 0x4237EE: MAIN_ZABBIX_ENTRY (server.c:1165)
==11413==    by 0x80C8CD: daemon_start (daemon.c:392)
==11413== 
==11413== 196,608 bytes in 6 blocks are definitely lost in loss record 73 of 75
==11413==    at 0x4838748: malloc (vg_replace_malloc.c:308)
==11413==    by 0x483AD63: realloc (vg_replace_malloc.c:836)
==11413==    by 0x849BF0: zbx_realloc2 (misc.c:550)
==11413==    by 0x810B58: zbx_default_mem_realloc_func (algodefs.c:331)
==11413==    by 0x8359C6: zbx_vector_ptr_reserve (vector.c:28)
==11413==    by 0x49B9D8: hk_history_delete_queue_prepare_global (housekeeper.c:491)
==11413==    by 0x49BCF8: hk_history_delete_queue_prepare_all (housekeeper.c:546)
==11413==    by 0x49C177: housekeeping_history_and_trends (housekeeper.c:651)
==11413==    by 0x49E427: housekeeper_thread (housekeeper.c:1197)
==11413==    by 0x83D318: zbx_thread_start (threads.c:132)
==11413==    by 0x4237EE: MAIN_ZABBIX_ENTRY (server.c:1165)
==11413==    by 0x80C8CD: daemon_start (daemon.c:392)
==11413== 
==11413== 229,376 bytes in 7 blocks are definitely lost in loss record 75 of 75
==11413==    at 0x4838748: malloc (vg_replace_malloc.c:308)
==11413==    by 0x483AD63: realloc (vg_replace_malloc.c:836)
==11413==    by 0x849BF0: zbx_realloc2 (misc.c:550)
==11413==    by 0x810B58: zbx_default_mem_realloc_func (algodefs.c:331)
==11413==    by 0x8359C6: zbx_vector_ptr_reserve (vector.c:28)
==11413==    by 0x49ADE9: hk_history_prepare (housekeeper.c:294)
==11413==    by 0x49BD71: hk_history_delete_queue_prepare_all (housekeeper.c:553)
==11413==    by 0x49C177: housekeeping_history_and_trends (housekeeper.c:651)
==11413==    by 0x49E427: housekeeper_thread (housekeeper.c:1197)
==11413==    by 0x83D318: zbx_thread_start (threads.c:132)
==11413==    by 0x4237EE: MAIN_ZABBIX_ENTRY (server.c:1165)
==11413==    by 0x80C8CD: daemon_start (daemon.c:392)

Suspicious place to blame(notice skipping but no clearing of allocated vectors)

		if (ZBX_HK_MODE_DISABLED == *rule->poption_mode || FAIL == zbx_history_requires_trends(rule->type))
			continue;

Workaround, disable history and trends housekeeping since they are not stored in MySQL database anyway.

Attached screenshot.

We cannot disable trends housekeeping since Zabbix cannot use elasticsearch for trends, only for history.

It looks like housekeeper also does not delete trends when elastic search is enabled.

It looks like housekeeper also does not delete trends when elastic search is enabled.

Nice.

Fixed in development branch:
svn://svn.zabbix.com/branches/dev/ZBX-15774

fixed old trends not being deleted by housekeeper and a memory leak in housekeeper when elasticsearch is used.

Trends and item delete would be queued for deletion but never get deleted so vector would grow.

Fixed in:

• pre-4.0.6rc1 r90872
• pre-4.2.0rc1 (trunk) r90873

Спасибо. Thanks.

I know that this is not enough but if it is monitored by Zabbix proxy and you remove proxy then it will also delete autoreg_host

zalex_ua Yes, I know that, because of constraints on DB level.

We have more than 2000000 rows in this table.

Could you advise, is it safe to perform something like this:

DELETE from autoreg_host where autoreg_hostid in (

select a.autoreg_hostid
from autoreg_host a left join hosts h
on a.host = h.host
where h.host is null

)

(not tested)

Depends. It wont crash or something, but some of auto registration events might lose the associated autoreg_host record (not likely, but in theory it can happen). What it means - if the autoregistration event was not yet processed it will be ignored. Of course when server will receive next active checks request from this host it will create new autoreg_host record and process the new event, so in most cases it would just delay host auto registration by 2 minutes (the default RefreshActiveChecks interval).

So yes, it should be safe unless you have some very specific setup/requirements.

Dear users, would you be able to run the following SQL SELECT statement and provide the output:

UPD: please see the SQL in comment below.

Hi dimir, you wrote:

would you be able to run the following SQL SELECT statement and provide the output:

The result on my system (with PostgreSQL) is following:

Error: ERROR: function from_unixtime(unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 217
SQLState: 42883
ErrorCode: 0

Oh, sorry, my bad, this function is non general-SQL. Could you try this one:

UPD: please see the SQL in the comment below.

Could you try this one

For me, the result is: 0 (zero).
At the same time, the table autoreg_host contains 621 record, some of them are very old (these hosts do not exist several years yet).

That probably means for those autoreg_host records the corresponding events are already deleted.

Please try the following one (but please include the whole output, where the query time is seen):

select count(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);

I'm interested in amount of returned records and time spent on the statement.

Please try the following one (but please include the whole output, where the query time is seen):

The result is:

Query 1 of 1, Rows read: 1, Elapsed time (seconds) - Total: 0.212, SQL query: 0.197, Reading results: 0.015

Thanks a lot! zalex_ua would it be possible for you to run that also?

<zalex_ua> asked to get. But I'm not sure about expectation.

konstantin.kornienko any chance to run the above mentioned SQL (this comment) to see the query execution time?

Fixed in development branch for 6.0.

Overview

The table autoreg_host was never cleared before this fix. After this fix the records in autoreg_host table will be handled by Housekeeper. Whenever it is run it will delete records in it that do not reference any of the autoregistration events (autoreg_host.autoreg_hostid=event.objectid) and any of the hosts (autoreg_host.host=host.host) respecting the MaxHousekeeperDelete option.

Fixed in

• 6.0.22rc1 02e9290f703
• 6.4.7rc1 1b0f8c750a8
• 7.0.0alpha5 24b522ee98f

Updated documentation:

• Upgrade notes: 6.0.22, 6.4.7

Thank you, guys!

Zabbix server on Debian 9 with official packages from http://repo.zabbix.com/zabbix/4.0/debian/, MariaDB database (Debian package).

Someone needs to try this. If yes, this is a regression.

Hello Nicolas,

Unfortunately I can't reproduce in my test environment. I will try to do this on a bigger database. Are you able to reproduce this?

Regards,
Aleksejs!

Hello,

Unfortunately, I can't reproduce the issue. I've restored the snapshot from before the upgrade and re-did the upgrade and this time the housekeeper configuration is OK (unchanged).

We have a 240GB MySQL database with now close to 3 years of trends so there is no way we could have run with the housekeeper enabled without issues. I checked Apache's logs: no one made changes to the conf.

I found a similar issue where someone with database partitioning also had a busy housekeeper after upgrading: https://support.zabbix.com/browse/ZBX-15102

Regards,

Hello Nicolas,

I'm closing the issue since it's not reproducible.

The ZBX you've mentioned isn't related to the Server but related to the Proxy.

Regards,
Aleksejs!

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-14778

Released in:

• pre-3.0.22rc1 r84341
• pre-3.4.14rc1 r84342
• pre-4.0.0beta2 r84343

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-14777

Released in:

• pre-3.4.14rc1 r84349
• pre-4.0.0beta2 r84350

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-13696

Successfully tested.

Released in:

• pre-3.4.9rc1 r79478
• pre-4.0.0alpha6 r79479

Database: PostgreSQL 9.5

The SQL should have been originated from src/zabbix_server/housekeeper/housekeeper.c, function DBdelete_from_table.

Good catch!
Since table partitioning is not officially supported, I wonder whether this is possibly rather a feature request than a bug...

Consider one of the suggested method to tackle high housekeeper usage is using table partitioning, and I think it would be rather easy to fix the problem (by adding the same filter to the outer delete statement, the low cost of tid scan would make any additional filter cost nothing), I wish they would be willing to fix it.

For example, the SQL mentioned could be changed to:

delete from history_uint where (itemid=36109) and ctid = any(array(select ctid from history_uint where itemid=36109 limit 5000))

Testing with more than 6 mil. values in history table:

# explain analyze delete from history where ctid=any(array(select ctid from history where (itemid=23676) limit 1000000));
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Delete on history  (cost=24653.59..24693.69 rows=10 width=6) (actual time=7287.174..7287.174 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..24653.58 rows=1000000 width=6) (actual time=0.125..2122.481 rows=1000000 loops=1)
           ->  Seq Scan on history history_1  (cost=0.00..290320.90 rows=11776014 width=6) (actual time=0.122..1075.796 rows=1000000 loops=1)
                 Filter: (itemid = 23676)
   ->  Tid Scan on history  (cost=0.01..40.11 rows=10 width=6) (actual time=2765.850..3790.410 rows=1000000 loops=1)
         TID Cond: (ctid = ANY ($0))
 Planning time: 0.163 ms
 Execution time: 7289.960 ms
(9 rows)

# explain analyze delete from history where (itemid=23676) and ctid=any(array(select ctid from history where (itemid=23676) limit 1000000)); 
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Delete on history  (cost=24653.59..24693.72 rows=9 width=6) (actual time=7967.702..7967.702 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..24653.58 rows=1000000 width=6) (actual time=9.761..2277.569 rows=1000000 loops=1)
           ->  Seq Scan on history history_1  (cost=0.00..290320.90 rows=11776014 width=6) (actual time=9.758..1217.197 rows=1000000 loops=1)
                 Filter: (itemid = 23676)
   ->  Tid Scan on history  (cost=0.01..40.14 rows=9 width=6) (actual time=2956.973..4110.316 rows=1000000 loops=1)
         TID Cond: (ctid = ANY ($0))
         Filter: (itemid = 23676)
 Planning time: 0.173 ms
 Execution time: 7969.142 ms
(10 rows)

# explain analyze delete from history where (itemid=23676) and ctid=any(array(select ctid from history limit 1000000));
                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Delete on history  (cost=18936.30..18976.42 rows=9 width=6) (actual time=7320.094..7320.094 rows=0 loops=1)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..18936.29 rows=1000000 width=6) (actual time=0.188..1964.917 rows=1000000 loops=1)
           ->  Seq Scan on history history_1  (cost=0.00..256462.32 rows=13543432 width=6) (actual time=0.185..922.297 rows=1000000 loops=1)
   ->  Tid Scan on history  (cost=0.01..40.14 rows=9 width=6) (actual time=2615.487..3730.859 rows=1000000 loops=1)
         TID Cond: (ctid = ANY ($0))
         Filter: (itemid = 23676)
 Planning time: 0.163 ms
 Execution time: 7322.085 ms
(9 rows)

No significant increase in execution time with the introduced changes.

Successfully tested.

Implemented:

• 3.0.16rc1 in r78294.

Implemented:

• 3.4.8rc1 in r78533,
• 4.0.0alpha5 (trunk) in r78534

CentOS Linux release 7.3.1611 (Core)
postgresql-9.2.18-1.el7.x86_64