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

Environment:

OS:centos6.5 DB: mysql5.7.9 (GA version)

The data of the search base instance is the data pulled by multi-source replication from the partial library of the online environment (the online data is isolated from the search data). It is mainly used to provide the query realized by some functions of the search (only select).

Question:

Received the zabbix alarm, online search base / data directory free space less than 10% cores CPU load reached 460%, check zabbix monitoring, BF refresh is also an instant surge

Diagnostics:

The pre-planning of the 1:/data catalog is 2T space. In the previous statistics, free space is 28%.

2: the search base only pulls the binlog of part of the database, and the business growth rate load is less than this high.

3:cpu load suddenly soared, iostat check IO load is not high, the first thought is a slow query, in the processlist and trx tables found clues, a large number of long-term incorrect query statements

4: the increase of load value caused by slow query has been determined. Why is the / data directory used so fast? Why is the frequency of BF refresh increasing?

Solution:

1: notify the search, stop the related query tasks, take out the slow query sql and optimize it. The statement roughly does union for two result sets, and the query frequency is once every 1 minute. The problem lies in the second query statement, resulting in temporary tables and poor index selection (re-indexing).

2: directory growth problem. Check the du in the / data directory. The growing file is ibtmp1, which has grown to about 320G. Check the official document ibtmp1 and explain it as follows

MySQL 5.7.2 introduces a new type of undo log for both normal and compressed temporary

Tables and related objects. The new type of undo log is not a redo log, as temporary tables are

Not recovered during crash recovery and do not require redo logs. Temporary table undo logs are

However, required for rollback, MVCC, and purging while the server is running. This special type

Of non-redo undo log benefits performance by avoiding redo logging I/O for temporary tables and

Related objects. The new undo log resides in the temporary tablespace. The default temporary

Tablespace file, ibtmp1, is located in the data directory by default and is always recreated on

Server startup. A user defined location for the temporary tablespace file can be specified by setting

Innodb_temp_data_file_path

Note the red section: 5.7.A new parameter innodb_temp_data_file_path has been introduced to store the table space of temporary tables and undo logs.

This frequent query of sql leads to the generation of a large number of temporary tables. BF refreshes undo frequently, while ibtmp1 continues to grow.

Ideas:

The strength of 1:sql audit.

In-depth study of version 2: 5.7

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.