How to obtain SQL statements with performance problems and SQL statements with performance problems

How to obtain SQL statements with performance problems and SQL statements with performance problems

1. Obtain SQL statements with performance problems through user feedback.

 

2. query logs to obtain SQL statements with performance.

 

Start slow log query

Slow_query_log = on

 

Set global slow_query_log = on;

Slow_query_log_file specifies the log storage path and file for slow Query

By default, data is stored in the mysql data directory. It is best to separate log storage from data storage.

Long_query_time

Specifies the threshold value for the SQL Execution time of slow query logs. The unit is seconds. The default value is 10 seconds. Accurate to microseconds,

If it is a millisecond, the value is 0.001.

The recorded statements include

1. query statement

2. data modification statement

3. SQL statements that have been rolled back

 

Does log_queries_not_using_indexes record SQL statements with no indexes used?

 

Content recorded in slow query logs:

The first line records:

User information, thread ID user information sbtest, thread ID 17

Row 2: record the query time

Row 3: Lock time

Row 4: Number of returned records

Row 5: Number of scanned rows

Row 6: execution time

Row 7: executed statements

 

Common slow query log analysis tools

1. mysqldumpslow

Summarize all SQL statements except the query conditions, and output the analysis results in the order specified in the parameters.

Mysqldumpslow-s r-t 10 slow. log

-S order (c, t, l, r, at, al, ar)

C: total times

T: Total time

L: Lock time

R: total data rows

Average of at, al, ar: t, l, r

At total time/total times

 

-T top: Specify the first few results for output.

 

2.pt-query-digest

Pt-query-digest-explain-h = 127.0.0.1, u = root, p = root slow. log> slow. report

It can include execution plans.

 

3. SQL for real-time performance Query

 

 

Select id, user, host, db, command, time, state, info from information_schema.processlist where time> 60;

Query the SQL statements on the server that have been queried for more than 60 seconds.