Shulou(Shulou.com)06/01 Report--

A problem description

With the increase of hosts and monitoring projects monitored by Zabbix, the historical data of Zabbix will be more and more, the disk space of MySQL database will easily be full, and the query data of Zabbix front end will become slower and slower. Especially when a custom Screen is generated through Zabbix's API, opening each Screen is very slow

View Zabbix database catalog files

# ls-lh | grep Gtotal 248G May RW May-1 mysql mysql 4.7G May 5 21:34 alerts.ibd-rw-rw---- 1 mysql mysql 3.4G May 5 21:34 events.ibd-rw-rw---- 1 mysql mysql 95G May 5 21:34 history.ibd-rw-rw---- 1 mysql mysql 25G May 5 21:34 history_text.ibd-rw-rw---- 1 mysql mysql 112G May 5 21:34 history_uint.ibd-rw-rw-- -- 1 mysql mysql 2.9G May 5 21:34 trends.ibd-rw-rw---- 1 mysql mysql 4.3G May 5 21:34 trends_uint.ibd

The directory size of the entire Zabbix database is 248G, and a few tables such as history_text and history_uint take up most of the disk space.

2. Solution

If you don't want to keep historical data for too long, for example, you can use the following methods to keep historical data for only one month.

1. Stop the zabbix server.

Service zabbix-server stop

It should be noted here that when deploying the Zabbix architecture, it is best to choose the architecture of Zabbix server-Zabbix proxy-Zabbix-agent. Even if there are only a few hosts, it is best to deploy one proxy, and multiple proxy can be deployed according to different applications or different computer rooms. Deploying zabbix has the following benefits:

Proxy specializes in collecting and temporarily storing data from agent, which can reduce the pressure on the server side.

Proxy can be used to achieve distributed monitoring, such as monitoring servers whose different networks are not connected to each other.

Enhance security by not directly exposing zabbix server information

It is very convenient to maintain, for example, to clean up the historical data of zabbix. After zabbix is stopped, the monitoring data can be set to be retained on the proxy side for a longer time. After maintenance, the proxy will synchronize the data to the server side to minimize data loss.

You need to pay attention to two parameters of zabbix proxy before stopping zabbix server

ProxyLocalBuffer=3

Sets the time that the zabbix proxy temporarily resides in the monitoring data of the local mysql. The default is 0 and is not temporarily saved. Even if zabbix proxy has sent the data to zabbix server, it still temporarily stores the time the data is set locally. The value range is 0: 720 hours.

ProxyOfflineBuffer=5

Sets the interval at which monitoring data is retained when zabbix proxy and zabbix server cannot be connected. The default is 1 hour, and the value is 1-720 hours. This parameter is particularly useful. I did not set this parameter for zabbix proxy after stopping zabbix server in the previous maintenance, so when I start zabbix server after the maintenance is over, I will find that there is no data for a period of time. This is because zabbix proxy executes housekeeper in accordance with the default retention time to delete expired data.

This time is best set according to the time to be maintained. For example, if you want to maintain for 10 hours, you should set up ProxyOfflineBuffer=10.

In this way, the data of these 10 hours will not be lost. There is also a problem that if the time interval is too large, zabbix proxy re-pushing data to zabbix server will increase server pressure on both sides.

two。 Create a new table

Create table history_new like history

Create table history_uint_new like history_uint

Create table history_text_new like history_text

3. Insert recent (one month) data into a new table

Insert into history_new select * from history where clock > '1459785600'

Insert into history_text_new SELECT * FROM history_text WHERE clock > '1461945600'

Insert into history_uint_new SELECT * FROM history_uint WHERE clock > '1461945600'

The clock here is the UNIX timestamp

It can take hours here, depending on the amount of data.

4. Re-change the table name

Alter table history rename history_old

Alter table history_new rename history

Alter table history_uint rename history_uint_old

Alter table history_uint_new rename history_uint

Alter table history_text rename history_text_old

Alter table history_text_new rename history_text

5. Restart zabbix server

After restarting zabbix, the alarm message that zabbix agent is not available will be reported continuously for a period of time, and it will return to normal after a period of time.

6. Delete old table

Drop table history_old

Drop table history_text_old

Drop table history_uint_old

You can also change the history field of the items table

UPDATE items SET history = '15' WHERE history >' 30'

This sets the history retention time for each item to 15 days. But if you set it this way, you won't be able to see the data from a few months ago.

This method is so inefficient that you have to stop zabbix server and then import data from a month into a new table, which is time-consuming if the amount of data is large.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.