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

Yesterday March 26th online application feedback: between 19:30-19:43 and 20:13-20:21 in the evening, all people, including their own classrooms, could not enter, cla***oom B suite could not be logged in, and the home page could not be accessed. More than 10 teachers and students had reported that they could not enter the classroom.

Through monitoring, check the monitoring between 00:00 on the 26th and 11:00 on the 27th, and check the cpu utilization, load, memory usage, swap surplus and other conditions of the database server when there is a problem. It is found that the database was under great pressure at that time.

There are many slow SQL in the database slow query log.

Looking at the slow query log, it is found that a SQL appears frequently in the slow query log and has a long execution time.

# User@Host: cms [cms] @ [172.17.43.24] # Query_time: 10.252490 Lock_time: 0.000052 Rows_sent: 1 Rows_examined: 2345869SET timestamp=1522065887 Select participan0_.id as id19_, participan0_.conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19_, participan0_.pin as pin19_, participan0_.email as email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from participant participan0_ where (participan0_.conferenceid=2680447) and (participan0_.pin='1219') # User@Host: cms [cms] @ [172.17.43.25] # Query_time: 10.297055 Lock_time: 0.000050 Rows_sent: 1 Rows_examined: 2345869SET timestamp=1522065887 Select participan0_.id as id19_, participan0_.conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19_, participan0_.pin as pin19_, participan0_.email as email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from participant participan0_ where (participan0_.conferenceid=2697493) and (participan0_.pin='1492') # User@Host: cms [cms] @ [172.17.43.25] # Query_time: 10.319839 Lock_time: 0.000048 Rows_sent: 1 Rows_examined: 2345869SET timestamp=1522065887 Select participan0_.id as id19_, participan0_.conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19_, participan0_.pin as pin19_, participan0_.email as email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from participant participan0_ where (participan0_.conferenceid=2680355) and (participan0_.pin='9590') # User@Host: cms [cms] @ [172.17.43.24] # Query_time: 10.163372 Lock_time: 0.000063 Rows_sent: 1 Rows_examined: 2345872SET timestamp=1522065887 Select participan0_.id as id19_, participan0_.conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19_, participan0_.pin as pin19_, participan0_.email as email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from participant participan0_ where (participan0_.conferenceid=2731041) and (participan0_.pin='1506') # User@Host: cms [cms] @ [172.17.43.24] # Query_time: 9.950549 Lock_time: 0.000073 Rows_sent: 1 Rows_examined: 2345881SET timestamp=1522065887 Select participan0_.id as id19_, participan0_.conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19_, participan0_.pin as pin19_, participan0_.email as email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from participant participan0_ where (participan0_.conferenceid=2682013) and (participan0_.pin='6086') # User@Host: cms [cms] @ [172.17.43.25] # Query_time: 9.992145 Lock_time: 0.000051 Rows_sent: 1 Rows_examined: 2345879SET timestamp=1522065887 Select participan0_.id as id19_, participan0_.conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19_, participan0_.pin as pin19_, participan0_.email as email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from participant participan0_ where (participan0_.conferenceid=2697493) and (participan0_.pin='1103')

Looking at the execution plan of the SQL, it was found that there was a full table scan, scanning more than 2 million rows of data.

Mysql > explain select participan0_.id as id19_, participan0_.conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19_, participan0_.pin as pin19_, participan0_.email as email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from participant participan0_ where (participan0_.conferenceid=2724963) and (participan0_.pin='5476') +-+ | id | select_type | table | type | possible _ keys | key | key_len | ref | rows | Extra | +-- +- -+ | 1 | SIMPLE | participan0_ | ALL | NULL | 2042005 | Using where | +-+-- -+-+ 1 row in set (0.02 sec)

It is found that there is no index on the column of the table's where condition:

Mysql > show index from participant +- +-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +- -+- -- + | participant | 0 | PRIMARY | 1 | id | A | 2384122 | NULL | NULL | | BTREE | +-- -+ 1 row in set (0.00 sec)

After communicating with the developer, add the following index to the table:

Mysql > ALTER TABLE `participant` ADD INDEX index_conferenceid (`participenceid`); Query OK, 0 rows affected (9.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql > ALTER TABLE `participant` ADD INDEX index_pin (`pin`); Query OK, 0 rows affected (6.96 sec) Records: 0 Duplicates: 0 Warnings: 0

View the index of the table

Mysql > show index from participant + -+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-+- -+- -+ | participant | 0 | PRIMARY | 1 | id | A | 2360697 | NULL | NULL | BTREE | | participant | 1 | index_conferenceid | 1 | conferenceid | A | | 199 | NULL | NULL | YES | BTREE | | participant | 1 | index_pin | 1 | pin | A | 199 | NULL | NULL | YES | BTREE | +-| -+- -+ 3 rows in set (0.00 sec)

Check the implementation plan of the SQL again, instead of full table scan, you will go to index_merge, and the execution time will be greatly reduced.

Mysql > explain select participan0_.id as id19_, participan0_.conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19_, participan0_.pin as pin19_, participan0_.email as email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from participant participan0_ where (participan0_.conferenceid=2724963) and (participan0_.pin='5476') +- +-- + | id | select_type | table | type | possible_keys | key | key_len | | ref | rows | Extra | + -- +-- + | 1 | SIMPLE | participan0_ | index_merge | index_conferenceid Index_pin | index_conferenceid,index_pin | 5P7 | NULL | 1 | Using intersect (index_conferenceid,index_pin) Using where | +-+- -- +-+ 1 row in set (0.01 sec)

-- I am the dividing line--

Wait until more time in the evening to observe the load of the database and the slow query log is normal.

Through this accident:

① optimizes slow queries in time

② opens the parameter log_queries_not_using_indexes to find the SQL that is not indexed in time

③ can adopt SQL audit-independent online platform to liberate manpower.

Https://blog.51cto.com/hcymysql/2053798#comment

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.