Shulou(Shulou.com)05/31 Report--

This article mainly introduces the Mysql optimization of Zabbix partition optimization example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

The biggest bottleneck in using zabbix is the database. If you maintain the data storage and alarm of zabbix, you can use zabbix to build a monitoring system. At present, the data of zabbix is mainly stored in two tables of history and trends. Over time, these two tables become very large, and their performance will be very poor, affecting the use of monitoring. Tuning MySQL can greatly improve the performance of Zabbix. This paper uses the method of partitioning MySQL to tune.

Principle

Partition the tables such as history and trends in zabbix, and partition by date, one per day, for a total of 90 days.

Detailed steps of operation

Operational impact: can be operated online, MySQL read and write slower, Zabbix performance slows down, the impact time varies according to the size of the data, generally about 2 hours.

The first step

Log in to the database of zabbix server and unify the configuration of MySQL

Cat > / etc/my.cnf crontab.txt cat > > crontab.txt / root/partition.log&

Note: observe the output of / root/partition.log

D. View the results

Log in to mysql and view tables such as history, as follows:

MariaDB [zabbix] > showcreate table history | history | CREATE TABLE `roomy` (`itemid` bigint (20) VALUES LESS THAN (20), `clock`int (11) NOT NULL DEFAULT '0percent, `value`double (161.440) ENGINE=InnoDB) NOT NULL DEFAULT' 0.0000stories, `ns`int (11) NOT NULL DEFAULT '0cycles, KEY`history _ 1` (`itemid`, `clock`) ENGINE=InnoDB DEFAULT CHARSETconversation utf8Granchy 50100 PARTITION BY RANGE (`clock`) (PARTITION p201708280000 VALUES LESS THAN (1503936000) ENGINE=InnoDB, PARTITION p201708290000 VALUES LESS THAN (1504022400) ENGINE=InnoDB, PARTITION p201708300000 VALUES LESS THAN (1504108800) ENGINE=InnoDB, PARTITION p201708310000 VALUES LESS THAN (15041958200) ENGINE=InnoDB PARTITION p201709010000 VALUES LESS THAN (1504281600) ENGINE = InnoDB, PARTITION p201709020000 VALUES LESS THAN (1504368000) ENGINE = InnoDB, PARTITION p201709030000 VALUES LESS THAN (1504454400) ENGINE = InnoDB, PARTITION p201709040000 VALUES LESS THAN (1504540800) ENGINE = InnoDB, PARTITION p201709050000 VALUES LESS THAN (1504627200) ENGINE = InnoDB, PARTITION p201709060000 VALUES LESS THAN (1504713600) ENGINE = InnoDB, PARTITION p201709070000 VALUES LESS THAN (1504800000) ENGINE = InnoDB, PARTITION p201709080000 VALUES LESS THAN (1504886400) ENGINE = InnoDB, PARTITION p201709090000 VALUES LESS THAN (1504972800) ENGINE = InnoDB, InnoDB p201709100000 PARTITION (150505059200) PARTITION p201709110000 VALUES LESS THAN (1505145600) ENGINE = InnoDB) * / |

A large number of PARTITION fields were found, indicating that the configuration is correct. Pay attention to the Slow Query of the Mysql. Generally, the Slow Query will almost be available the day after the operation is performed, and the Dashboard response speed of the Zabbix should be very smooth.

Thank you for reading this article carefully. I hope the article "sample Analysis of Zabbix Partition Optimization for Mysql Optimization" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.