MYSQL deleting too slow

[Godzilla]

It takes me 19 mins to delete 4500 records from the history table.

I dont think zabbix housekeeping is really clearing the data since the delete in mysql is too slow. I only keep at most 90 days of history, but I still have history data from 2005.

Anyone have suggestions to speed up the process? My history table is 16GB now.

mysql> delete FROM `history` where `clock` < 1122000000;
Query OK, 4545 rows affected (19 min 57.07 sec)

Thanks

[den_crane]

1. Table history has index itemidclock(itemid,clock), your delete must be where itemid=lalala and clock<lalala

2. http://www.zabbix.com/forum/showpost...31&postcount=8

[den_crane]

mysql> DELETE FROM `history` WHERE `itemid` =17271;
Query OK, 285368 rows affected (14.6254 sec)

[Godzilla]

Thanks for the replies.

I'm using:
ZABBIX 1.1.2 Copyright 2001-2006 by SIA Zabbix

I'm slowly deleting data before 2006. I'm too busy to write code to cycle through all the itemid's to delete all data before 2006.

I thought the housekeeping would help me do that, but I guess that it doesn't work on big "history" tables? Or my mysql innodb is too slow? At most, my history retention is 90 days, but I still have data in my history table from 2005. I thought the trends table took care of this data?

[den_crane]

I'm not sure but probably housekeeper sometimes don't delete history, for deleted item's.
In my db (with some period housekeeper not work in 1.1.1) I found some history with itemid not existent in table items.

May be query help u: select distinct(itemid) FROM `history` where itemid not in (SELECT itemid FROM `items`);

[Godzilla]

Thanks for your suggestion. I know what you mean about deleted items. Surprisingly, there are no deleted items in the history table.

mysql> select distinct(itemid) FROM `history` where itemid not in (SELECT itemid FROM `items`);

Empty set (17.20 sec)

I freed up around 2GB from mysql innodb "history" table, removing 2005 data.