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

cause

After the MySQL server runs mysqld_exporter, it is found that the number of active connections in the database has skyrocketed, and all of them are slow queries from mysqld_exporter, as shown in the following statement:

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ifnull (ENGINE, 'NONE') as ENGINE, ifnull (VERSION,' 0') as VERSION, ifnull (ROW_FORMAT, 'NONE') as ROW_FORMAT, ifnull (TABLE_ROWS,' 0') as TABLE_ROWS, ifnull (DATA_LENGTH,'0') as DATA_LENGTH Ifnull (INDEX_LENGTH,'0') as INDEX_LENGTH, ifnull (DATA_FREE,'0') as DATA_FREE, ifnull (CREATE_OPTIONS, 'NONE') as CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_SCHEMA =' xxx' Analysis.

1. Execute the statement in this database, and the execution time is very slow

102 rows in set (6.35 sec)

two。 In the slave execution of the database, the result is completely different.

3. At this point, it can be determined that it should be related to MySQL statistics.

View MySQL statistics related introduction: https://blog.csdn.net/n88Lpo/article/details/79144495

4. Check the parameters of the master-slave database and find the differences.

# # main Library mysql > show variables like 'innodb_stats_on_metadata' +-- +-+ | Variable_name | Value | +-+-+ | innodb_stats_on_metadata | ON | +-+ -+ 1 row in set (0.00 sec) # # from the library mysql > show variables like 'innodb_stats_on_metadata' +-- +-+ | Variable_name | Value | +-+-+ | innodb_stats_on_metadata | OFF | +-+ -+ 1 row in set (0.00 sec)

5. The reason is that the statistics are updated each time the query is made.

View the MySQL official document https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html

Non-persistent statistics will be automatically updated in the following cases: 1 execute ANALYZE TABLE2 innodb_stats_on_metadata=ON, execute SHOW TABLE STATUS, SHOW INDEX, query TABLES under INFORMATION_SCHEMA, STATISTICS3 enable-- auto-rehash function, login table 4 using mysql client is opened for the first time 5 from the last update of statistical information, and the data of Table 1 and 16 are modified.

Modify parameter innodb_stats_on_metadata

Mysql > set global innodb_stats_on_metadata=0;Query OK, 0 rows affected (0.00 sec)

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.