Cleanup database unreferenced entries

[ulistaerk]

As the housekeeper is running for ages, I accidentially found during debugging some items in the housekeepers list, that do no longer exist:

select distinct value from housekeeper where value not in ( 
  select itemid from items
);
[...]
15992 rows in set (0.36 sec)

There are also some trends archieved for non-existing items:

mysql> select count(*) from history_uint where itemid not in ( select itemid from items );
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (1 min 45.59 sec)

mysql> select count(*) from history where itemid not in ( select itemid from items );
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (1 min 13.04 sec)

mysql> select count(*) from trends where itemid not in ( select itemid from items );
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (1 min 5.27 sec)

mysql> select count(*) from trends_uint where itemid not in ( select itemid from items );
+----------+
| count(*) |
+----------+
|  4221509 |
+----------+
1 row in set (54.95 sec)

I dont think these data can be accessed any more. Can I delete those entries in the housekeepers and trends table?

[alex790]

up!

I have the same problem. I deleted a bunch of hosts which were monitored at fast pace for a few years. I was expecting my 15GB Postgres DB to at least drop to less than 5GB, but it appears after the house keeper has run that the DB is the same size as before, and the history and trends table still have the same amount of records.

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

So, can we delete these safely ?

[ulistaerk]

33M rows is quite a lot. As I only have <1M there is (currently) no need to delete the records. But as far as I can tell, you can delete entries without damage to the database. I would do the following:

• show create table history;

  Now create a identical table structure with a different table-name like history_bak.
• Copy the records into the new table and delete them afterwards.
  

  SET autocommit=0;
  START TRANSACTION;
  INSERT INTO history_bak SELECT * FROM history WHERE itemid NOT IN ( SELECT itemid FROM items );
  DELETE FROM history WHERE itemid NOT IN ( SELECT itemid FROM items );
  COMMIT;
  SET autocommit=1;

• repeat there steps for each table

If anything goes wrong, either rollback or copy the data from the _bak table back into the original table.

Edit: Hint: If you dont have innodb-filepertable enabled ... dropping the _bak tables will have no effect to the ibdata filesize But required buffersize/RAM will decrease

[alex790]

did it, we will see zabbix running some time to see if it has problems.
Also cleared history_str, history_log, ...

[nima0102]

Hi
We have the same problem with records of deleted hosts in history table.
I think your work around is not one good permanent solution.
I have created one issue on https://support.zabbix.com/browse/ZBX-3394 .
If you have this issue please vote to this issue so that this problem be solved in the next versions.

Thanks in advance

[alex790]

I added a few details in the support ticket, but it seems i do not have the administrative rights to vote for it or reopen it. Maybe you can