Ad Widget

**kloczek** · 30-05-2018, 11:53

100% mens that housekeeper is busy (it is single process).
So long time of running housekeeper means that you have a lot of old data to remove and you probably have enough big volume of monitoring data that removing oldest one using delete queries is no longer effective.
You need to switch to delete oldest data by dropping partitions with oldest data on at least history* tables.
Try to google for "zabbix partitioning <you_db_engine_name>" to find documentation about how to do this.

**nymus** · 31-05-2018, 14:30

Thank you for your answer and support
I've tried to remove manually old data and it was ok for the small tables.
For both uint table (history_uint 200GB, trends_uint 30GB) I cannot delete the old data. When I do the following,

Code:

DELETE FROM trends_uint WHERE clock < UNIX_TIMESTAMP('2017-05-01');

nothing happens and I see nothing happens with:

Code:

SHOW FULL PROCESSLIST;

Are my command correct or should I do check with itemid?
I'm not a DB specialist and your support is very helpful.

**kloczek** · 31-05-2018, 17:48

Problem is that as long as lhh history* tables data are stored in single per table file number of IOs per data points which is necessary to be deleted oldest data grows with natural logarithm of those stored in history* tables.
If all tables data are stored in clock based values ranges which spans across (for example) single day delete all data older than N days takes only those IOs which are necessary to delete single file.
It is only few IOs instead sometime millions of IOs when DELETE query is used.
Look on my history table definition (just as example of partitioned table):

Code:

mysql> use zabbix; SHOW CREATE TABLE history\G;
*************************** 1. row ***************************
       Table: history
Create Table: CREATE TABLE `history` (
  `itemid` bigint(20) unsigned NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` double(16,4) NOT NULL DEFAULT '0.0000',
  `ns` int(11) NOT NULL DEFAULT '0',
  KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`clock`)
(PARTITION p201805030000 VALUES LESS THAN (1525388400) ENGINE = InnoDB,
 PARTITION p201805040000 VALUES LESS THAN (1525474800) ENGINE = InnoDB,
 PARTITION p201805050000 VALUES LESS THAN (1525561200) ENGINE = InnoDB,
 PARTITION p201805060000 VALUES LESS THAN (1525647600) ENGINE = InnoDB,
 PARTITION p201805070000 VALUES LESS THAN (1525734000) ENGINE = InnoDB,
 PARTITION p201805080000 VALUES LESS THAN (1525820400) ENGINE = InnoDB,
 PARTITION p201805090000 VALUES LESS THAN (1525906800) ENGINE = InnoDB,
 PARTITION p201805100000 VALUES LESS THAN (1525993200) ENGINE = InnoDB,
 PARTITION p201805110000 VALUES LESS THAN (1526079600) ENGINE = InnoDB,
 PARTITION p201805120000 VALUES LESS THAN (1526166000) ENGINE = InnoDB,
 PARTITION p201805130000 VALUES LESS THAN (1526252400) ENGINE = InnoDB,
 PARTITION p201805140000 VALUES LESS THAN (1526338800) ENGINE = InnoDB,
 PARTITION p201805150000 VALUES LESS THAN (1526425200) ENGINE = InnoDB,
 PARTITION p201805160000 VALUES LESS THAN (1526511600) ENGINE = InnoDB,
 PARTITION p201805170000 VALUES LESS THAN (1526598000) ENGINE = InnoDB,
 PARTITION p201805180000 VALUES LESS THAN (1526684400) ENGINE = InnoDB,
 PARTITION p201805190000 VALUES LESS THAN (1526770800) ENGINE = InnoDB,
 PARTITION p201805200000 VALUES LESS THAN (1526857200) ENGINE = InnoDB,
 PARTITION p201805210000 VALUES LESS THAN (1526943600) ENGINE = InnoDB,
 PARTITION p201805220000 VALUES LESS THAN (1527030000) ENGINE = InnoDB,
 PARTITION p201805230000 VALUES LESS THAN (1527116400) ENGINE = InnoDB,
 PARTITION p201805240000 VALUES LESS THAN (1527202800) ENGINE = InnoDB,
 PARTITION p201805250000 VALUES LESS THAN (1527289200) ENGINE = InnoDB,
 PARTITION p201805260000 VALUES LESS THAN (1527375600) ENGINE = InnoDB,
 PARTITION p201805270000 VALUES LESS THAN (1527462000) ENGINE = InnoDB,
 PARTITION p201805280000 VALUES LESS THAN (1527548400) ENGINE = InnoDB,
 PARTITION p201805290000 VALUES LESS THAN (1527634800) ENGINE = InnoDB,
 PARTITION p201805300000 VALUES LESS THAN (1527721200) ENGINE = InnoDB,
 PARTITION p201805310000 VALUES LESS THAN (1527807600) ENGINE = InnoDB,
 PARTITION p201806010000 VALUES LESS THAN (1527894000) ENGINE = InnoDB,
 PARTITION p201806020000 VALUES LESS THAN (1527980400) ENGINE = InnoDB,
 PARTITION p201806030000 VALUES LESS THAN (1528066800) ENGINE = InnoDB,
 PARTITION p201806040000 VALUES LESS THAN (1528153200) ENGINE = InnoDB,
 PARTITION p201806050000 VALUES LESS THAN (1528239600) ENGINE = InnoDB,
 PARTITION p201806060000 VALUES LESS THAN (1528326000) ENGINE = InnoDB) */
1 row in set (0.01 sec)

As you see there are few partitions created in advance (for next full week) and 30 partitions daily used partitis. Each day is created new partition to keep 7 empty partitions and is dropped oldest partition.
Zabbix is fully adapted to use such hack in database scheme because in Administration->General->Housekeeping is possible to disable fire internal housekeeper deleting oldest data in few tables.
Using such way is ONLY way deal with housekeeping after passing some database scale factor which depends on combination of you storage IO latency and volume of the new data which fromw to the database.

Ad Widget

Housekeeper troubleshooting, does it work?

Housekeeper troubleshooting, does it work?

Comment

Comment

Comment