Hello,
About three years ago we implemented Zabbix 2.0.2 to monitor about 50 VMs. The Zabbix server and MySQL database were installed on a VM with a single file system mounted on /. The / file system has a 60GB capacity.
Throughout those three years, the used disk space on the Zabbix server was consistently about 20GB.
Back in January of this year, we upgraded the Zabbix server to version 2.2. Recently, Zabbix alerted us to the fact that there was less than 15% available room on the / file system. Upon viewing the history in Zabbix, it seemed that the available disk space on the / file system had been declining steadily since the upgrade to Zabbix 2.2.
After a bit of Googling, I discovered that the housekeeping tasks had been migrated from the Zabbix server configuration file to the web UI and that they were disabled by default in Zabbix 2.2. I re-enabled them. However, that was not enough and we continued to consume disk space.
I went into the zabbix_server.conf file and changed the MaxHouseKeeperDelete from the default of 500 to 5000. That has helped level off the decline in available disk space but not reverse it. Housekeeping is scheduled to run once an hour. On average, it takes about 20 minutes.
Now I am faced with the challenge of how to purge the database. We keep 14 days of history and 720 days of trends.
Here are the sizes of some of the history and trend tables:
mysql> show table status like 'history%';
+-------------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| history | InnoDB | 10 | Compact | 129633484 | 59 | 7699709952 | 0 | 3726950400 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_log | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_str | InnoDB | 10 | Compact | 206103 | 86 | 17842176 | 0 | 9519104 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_str_sync | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 8388608 | 1 | 2015-01-23 10:47:58 | NULL | NULL | utf8_general_ci | NULL | | |
| history_sync | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 8388608 | 1 | 2015-01-23 10:47:57 | NULL | NULL | utf8_general_ci | NULL | | |
| history_text | InnoDB | 10 | Compact | 356996 | 94 | 33619968 | 0 | 22085632 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_uint | InnoDB | 10 | Compact | 354152835 | 57 | 20275281920 | 0 | 10080174080 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_uint_sync | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 8388608 | 1 | 2015-01-23 10:47:58 | NULL | NULL | utf8_general_ci | NULL | | |
+-------------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
8 rows in set (0.86 sec)
As you can see, the history_uint table is about 20GB in size. The history table is about 8GB in size.
mysql> show table status like 'trends%';
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| trends | InnoDB | 10 | Compact | 19616678 | 72 | 1423343616 | 0 | 0 | 6291456 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| trends_uint | InnoDB | 10 | Compact | 52401893 | 72 | 3787915264 | 0 | 0 | 6291456 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.46 sec)
I am looking for methods to purge the history and trends so that they are back in line with 14 and 720 days, respectively.
I found one approach here: https://github.com/burner1024/zabbix...ld-data.my.sql
I would appreciate any advice on how to proceed. I am not a DBA.
Thanks in advance,
Trevor
About three years ago we implemented Zabbix 2.0.2 to monitor about 50 VMs. The Zabbix server and MySQL database were installed on a VM with a single file system mounted on /. The / file system has a 60GB capacity.
Throughout those three years, the used disk space on the Zabbix server was consistently about 20GB.
Back in January of this year, we upgraded the Zabbix server to version 2.2. Recently, Zabbix alerted us to the fact that there was less than 15% available room on the / file system. Upon viewing the history in Zabbix, it seemed that the available disk space on the / file system had been declining steadily since the upgrade to Zabbix 2.2.
After a bit of Googling, I discovered that the housekeeping tasks had been migrated from the Zabbix server configuration file to the web UI and that they were disabled by default in Zabbix 2.2. I re-enabled them. However, that was not enough and we continued to consume disk space.
I went into the zabbix_server.conf file and changed the MaxHouseKeeperDelete from the default of 500 to 5000. That has helped level off the decline in available disk space but not reverse it. Housekeeping is scheduled to run once an hour. On average, it takes about 20 minutes.
Now I am faced with the challenge of how to purge the database. We keep 14 days of history and 720 days of trends.
Here are the sizes of some of the history and trend tables:
mysql> show table status like 'history%';
+-------------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| history | InnoDB | 10 | Compact | 129633484 | 59 | 7699709952 | 0 | 3726950400 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_log | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 32768 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_str | InnoDB | 10 | Compact | 206103 | 86 | 17842176 | 0 | 9519104 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_str_sync | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 8388608 | 1 | 2015-01-23 10:47:58 | NULL | NULL | utf8_general_ci | NULL | | |
| history_sync | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 8388608 | 1 | 2015-01-23 10:47:57 | NULL | NULL | utf8_general_ci | NULL | | |
| history_text | InnoDB | 10 | Compact | 356996 | 94 | 33619968 | 0 | 22085632 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_uint | InnoDB | 10 | Compact | 354152835 | 57 | 20275281920 | 0 | 10080174080 | 8388608 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| history_uint_sync | InnoDB | 10 | Compact | 0 | 0 | 16384 | 0 | 16384 | 8388608 | 1 | 2015-01-23 10:47:58 | NULL | NULL | utf8_general_ci | NULL | | |
+-------------------+--------+---------+------------+-----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
8 rows in set (0.86 sec)
As you can see, the history_uint table is about 20GB in size. The history table is about 8GB in size.
mysql> show table status like 'trends%';
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| trends | InnoDB | 10 | Compact | 19616678 | 72 | 1423343616 | 0 | 0 | 6291456 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
| trends_uint | InnoDB | 10 | Compact | 52401893 | 72 | 3787915264 | 0 | 0 | 6291456 | NULL | 2012-09-28 15:21:00 | NULL | NULL | utf8_general_ci | NULL | | |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
2 rows in set (0.46 sec)
I am looking for methods to purge the history and trends so that they are back in line with 14 and 720 days, respectively.
I found one approach here: https://github.com/burner1024/zabbix...ld-data.my.sql
I would appreciate any advice on how to proceed. I am not a DBA.
Thanks in advance,
Trevor