"FAQ Series": DB server generates a lot of physics reading problem optimization idea

A "phenomenon"

The 1, 7-point to 9-point IO monitoring indicator Util is particularly high, as follows:

2, read read-write situation: Reading produces very high physical IO, as follows

"Analysis": compared to other servers, the buffer pool is 80G, normally the hot data are read from the buffer pool, the physical reading is very little, but this group has produced a lot of physical reading, there must be a problem.

Two "Find the Inside Ghost"

"Basic idea": now that we have a physical reading, the next step is to think about what tables produced physical IO? More specifically, what SQL on these tables will cause physical IO?

1, the use of Performance_schema file_summary_by_instance table statistics IO situation. 2, write a script, respectively, at 7 points 14 to 7 points 20, 7:14 to 8:14 these two peak period, the most physical reading of the table is produced. 3, find the table, look at the table suspicious SQL, focus on slow query.

1, through the statistical Performance_schema File_summary_by_instance find the two peaks between the reading of the most 2 tables

The table is already positioned, so go deeper and find out which SQL on these tables might cause a lot of physical reads. Statistics are done through the Sql-trace platform we develop or through performance_schema.events_statements_summary_by_digest. Specific statement:

Select Digest_text,count_star,first_seen,last_seen from events_statements_summary_by_digest where DIGEST_TEXT like '% ' A1 '% ';

2. Suspicious SQL statements on the A1 table

SELECT Max (daydate) as daydate from A1 WHERE t_id =2572244 and bid>0;

3. Suspicious SQL statements on the A2 table

SELECT Max (daydate) as daydate from A2 WHERE m_id =2572244 and bid>0;

Why is it suspicious?

Suspicious 1, from the time period of view, the above SQL execution time and produce physical reading is consistent, are 7 to 9 points. As follows:

Suspicious 2, more than two SQL are slow queries.
Suspicious 3, through set profiling trace discovery produces many disk io,block_ops_in=22176,block_ops_out=664.

　

Three "Yue Heyue"

Find the responsible development students, found to be running through the job, in order to confirm whether the SQL statement caused by the problem, let the development of students to delay the job one hours running
The next day, it was found that the high disk IO was also delayed by one hours, it is essential to determine the "inside Ghost".

"FAQ Series": DB server generates a lot of physics reading problem optimization idea