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

An application scenario description

Two solutions to dealing with Zabbix historical data were described earlier, but it is not enough to solve the problem if the monitoring data continues to grow.

Method 2 for dealing with Zabbix historical database-partitioning with MySQL table

Method 1 for dealing with Zabbix history database

The first method is suitable for keeping the data for a period of time, such as one month, without partitioning the table, and then delete the old data and then use method 2, which takes a long time to execute.

The second method is to use the table partitioning function of MySQL to partition tables such as history by date, and then retain only the data for a period of time. But even if you use the table partitioning feature to delete unused partitioned data on a regular basis, there will be bottlenecks. For example, a large amount of monitoring data, a month of historical data are hundreds of gigabytes, continued growth will burst the server disk. Therefore, vertical expansion is not the best choice. Because Zabbix designs a single database structure, all table operations are performed in the same database, so large table operations like history will inevitably affect other table operations. How to isolate several large tables like history to other database servers is the key to solve the problem. Therefore, it is thought that we can use MySQL middleware to split tables on Zabbix database, and at the same time, with the help of the read-write separation function provided by MySQL middleware, these large tables can be read-write separated to further reduce the pressure.

Type selection of two MySQL middleware

Determine our requirements before choosing which MySQL middleware to use:

Preferably an open source product, but also an active development product.

Try to use it directly without changing the Zabbix code-key

Provide the function of subdatabase and table.

Provide the function of read-write separation

Stable performance and many online use cases

Rich parsing of SQL statements

Once you understand these requirements, you can start to look up the data and type selection. Several optional MySQL middleware are as follows:

Cobar Alibaba's open source product, which has not been maintained for more than 3 years, has been abandoned directly.

An improved version of Mycat Cobar, maintained by the community, with rich features and wired case references. At the same time, the documents and materials are complete.

Distributed Database Architecture and Enterprise practice-- based on Mycat Middleware

Can be tested

The community version of the commercial product developed by Alipay DBA before OneProxy is limited. However, they have a ready-made expansion plan for the Zabbix database. Give up directly

MySQL middleware developed by Kingshard in GE language supports the functions of sub-library and sub-table and read-write separation. Can be tested

DBProxy Meituan's recently open source MySQL middleware supports sub-table and read-write separation. Can be tested

Therefore, we need to test three MySQL middleware: Mycat,Kingshard and DBProxy

First deploy two MySQL database instances

172.28.2.231 3306 default DB1

172.28.2.227 DB2 3306 stores several large tables of history

Both DB instances set up Zabbix databases and accounts, and then deploy them according to the documents of the three middleware

History,history_uint,history_str,history_text,history_log,trends,trends_uint these tables are assigned to DB2 according to the document, and other tables default to DB1.

1. Test Mycat

There is no error in the Zabbix server log, but there is an error in the Zabbix page, mainly because of SELECT DISTINCT h. * this kind of SQL Mycat cannot be parsed. Testing SELECT DISTINCT * under the terminal will not report an error, and if several history tables do not go to DB2 but to the default DB1, it will not report an error.

two。 Test Kingshard

The Zabbix server error log is refreshed frequently, while the Kingshard error log is refreshed more frequently, and the Zabbix page has more errors.

3. Test DBProxy

Compilation and installation is not successful, just give up

So, next we focus on testing if you use Mycat to expand the database. There is a lot of information about MySQL middleware on the Internet, you can find it on your own. Sub-table functions include

Third, use Mycat to divide tables for Zabbix databases.

Reference documentation:

Https://github.com/flike/kingshard

Http://www.open-open.com/news/view/1708e64

Https://github.com/alibaba/cobar

Https://github.com/MyCATApache/Mycat-Server

Http://mycat.io/

Http://www.onexsoft.com/zh/oneproxy-scale-out-zabbix.html

Https://github.com/flike/kingshard

Https://github.com/Meituan-Dianping/DBProxy

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.