housekeeper does not not delete all outdated records in one go by default.
look at the output of "select * from housekeeper;" - if that is empty and amount of rows in history tables does not match your expectations, please reopen.
if that query returns something, it means housekeeper is not done yet
Well, yes, the housekeeper table had 8 records while there were millions of orphans in history_x and trends_x tables :
I now have for example (Is the housekeeper working with zabbix 1.8.2?)
zabbix=# select count
from housekeeper;
count
-------
8
zabbix=# select count from history_uint where itemid not in ( select itemid from items );
count
----------
14164574
zabbix=# select count from history where itemid not in ( select itemid from items );
count
----------
33384202
Other tables also have millions of orphans.
Note that these come from hosts monitored while we were running Zabbix 1.4.x, then migrated to 1.6.x years later, and then 1.8 and now 1.8.2
For each migration, i followed the steps and database migrations requests in the release notes. I deleted these hosts when i was running zabbix 1.8.2
In case it matters, i am using Centos 5.5 x86_64
8 housekeeper records for millions of rows indeed sound a bit too low. which tables are listed in housekeeper tablename column ? if there's history or history_uint, does the value match any of the itemids in the corresponding table ?
Unfortunately, i do not have the content of the housekeeper table anymore. What i can give you at this time is the housekeeper log. I am not sure if that will help. I did not find any zabbix housekeeper fail, error or exception in zabbix_server.log
As for the all the history* and trends* tables orphan records, i deleted them manually ( i did save them in table copies in case of )
Usual, with all the hosts, once per day :
13342:20101227:112024.736 Deleted 93773 records from history and trends
13342:20101228:124223.556 Deleted 1505641 records from history and trends
13342:20101229:143532.601 Deleted 1531752 records from history and trends
13342:20101230:160341.172 Deleted 1543939 records from history and trends
13342:20101231:181752.666 Deleted 1486604 records from history and trends
13342:20110101:200815.702 Deleted 1531356 records from history and trends
13342:20110102:214710.538 Deleted 1504956 records from history and trends
13342:20110103:234156.168 Deleted 1472991 records from history and trends
Then, after the DB backup and restarting, about to delete many hosts ( i put the housekeeper every hour to see the cleaning at soon as possible, but the BIG cleaning did not happen )
13314:20110104:025654.489 Syncing history data...
13314:20110104:025654.843 Syncing history data... done. 13314:20110104:025654.844 Syncing trends data...
13314:20110104:025655.585 Syncing trends data... done. 13314:20110104:025655.598 Zabbix Server stopped. Zabbix 1.8.3 (revision 13928).
20434:20110104:041405.904 Starting Zabbix Server. Zabbix 1.8.3 (revision 13928). 20434:20110104:041405.929 **** Enabled features ****
20434:20110104:041405.929 SNMP monitoring: NO 20434:20110104:041405.929 IPMI monitoring: NO
20434:20110104:041405.929 WEB monitoring: YES 20434:20110104:041405.929 Jabber notifications: NO
20434:20110104:041405.930 ODBC: NO 20434:20110104:041405.930 SSH2 support: NO
20434:20110104:041405.930 IPv6 support: NO 20434:20110104:041405.930 **************************
20437:20110104:041408.583 server #1 started [DB Cache] 20438:20110104:041408.585 server #2 started [Poller. SNMP: NO]
20440:20110104:041408.587 server #3 started [Poller. SNMP: NO] 20442:20110104:041408.589 server #4 started [Poller. SNMP: NO]
20446:20110104:041408.593 server #6 started [Poller. SNMP: NO] 20448:20110104:041408.596 server #7 started [Trapper]
20450:20110104:041408.599 server #8 started [Trapper] 20452:20110104:041408.602 server #9 started [Trapper]
20453:20110104:041408.605 server #10 started [Trapper] 20443:20110104:041408.592 server #5 started [Poller. SNMP: NO]
20457:20110104:041408.622 server #12 started [ICMP pinger] 20459:20110104:041408.626 server #13 started [Alerter]
20461:20110104:041408.629 server #14 started [Housekeeper] 20461:20110104:041408.629 Executing housekeeper
20463:20110104:041408.632 server #15 started [Timer] 20465:20110104:041408.636 server #16 started [HTTP Poller]
20468:20110104:041408.648 server #18 started [DB Syncer] 20470:20110104:041408.651 server #19 started [DB Syncer]
20471:20110104:041408.654 server #20 started [DB Syncer] 20474:20110104:041408.657 server #21 started [DB Syncer]
20476:20110104:041408.660 server #22 started [Escalator] 20434:20110104:041408.662 server #0 started [Watchdog]
20454:20110104:041408.663 server #11 started [Trapper] 20466:20110104:041408.639 server #17 started [Discoverer. SNMP: NO]
20477:20110104:041408.712 server #23 started [Proxy Poller]
(...)
20461:20110104:044059.397 Deleted 377725 records from history and trends
(...)
22034:20110104:054613.639 Deleted 78773 records from history and trends
(...)
22034:20110104:072851.990 Deleted 103164 records from history and trends
22034:20110104:090850.707 Deleted 102787 records from history and trends
22034:20110104:104139.394 Deleted 94918 records from history and trends
22034:20110104:120937.497 Deleted 81077 records from history and trends
22034:20110104:133644.203 Deleted 73321 records from history and trends
22034:20110104:150215.531 Deleted 71858 records from history and trends
22034:20110104:162818.424 Deleted 71079 records from history and trends
22034:20110104:175201.046 Deleted 71518 records from history and trends
22034:20110104:191430.058 Deleted 69732 records from history and trends
22034:20110104:203645.660 Deleted 68793 records from history and trends
22034:20110104:215910.590 Deleted 69116 records from history and trends
22034:20110104:232119.546 Deleted 69146 records from history and trends
22034:20110105:004850.863 Deleted 72983 records from history and trends
22034:20110105:021211.583 Deleted 74626 records from history and trends
22034:20110105:033632.759 Deleted 85966 records from history and trends
22034:20110105:045920.709 Deleted 85394 records from history and trends
22034:20110105:062119.361 Deleted 83429 records from history and trends
22034:20110105:074734.404 Deleted 87686 records from history and trends
22034:20110105:090933.550 Deleted 83230 records from history and trends
(...)
As you can see, we just see the usual cleaning compared to before, not any big cleaning,
Isn't there any tool or script or procedure to check the integrity of Zabbix DB and eleminate or fix all discrepencies or orphans ?