"mysql optimization topic" What is slow query? " How to optimize with slow query log? (10)

The log is the same as people write diaries, record the past things. But the diary of a person is subjective (remember what you want to remember), and the database log is objective, according to the record content is divided into the following several kinds of log (technical article):

A, error log: Records the issue that occurs when you start, run, or stop mysqld.

B. General log: Records the established client connections and executed statements.

C, update log: The statement that records the change data. The log is no longer used in MySQL 5.1.

D, binary log: A statement that records all changes to the data. Also used for replication.

E, slow query log: Records all queries that have been executed for more than long_query_time seconds, or queries that do not use indexes.

F, InnoDB logs: InnoDB redo Log

By default, all logs are created in the MYSQLD data directory.

You can force mysqld to close and reopen the log file (or, in some cases, switch to a new log) by refreshing the log.

When you execute a FLUSH logs statement or perform mysqladmin flush-logs or mysqladmin refresh, the log is aged.

In the case of MySQL replication, more log files will be maintained from the replication server, known as the replacement log.

This time we are introducing the slow query log. What is a slow query log? MySQL will record the query over a specified time of the statement, we will exceed the specified time of the SQL statement query called Slow query, are recorded in the slow query log, we open to see exactly which statements in the slow query

Turn on Slow query log (technical text)

Mysql>show variables like "%slow%"; To view the slow query configuration, no is added in My.cnf, as follows

Analysis Log –mysqldumpslow (technical article)

Analysis log, available with MySQL Mysqldumpslow, easy to use, parameters can be help view

Recommended Analysis Log Tool –mysqlsla (technical article)

"description" (technical article)

Queries Total: Query count unique: number of SQL to go back

Sorted by: Sorting the contents of the output report

The most significant slow SQL statistics, including the average execution time, the waiting lock time, the total number of result rows, and the total number of rows scanned.

Count, the number of executions of SQL and the percentage of total slow log count.

Time, execution times, including total time, average time, minimum, maximum time, and percentage of total slow SQL time.

95% of time, removing the fastest and slowest SQL, coverage accounted for 95% of SQL execution times.

Lock time, waiting for the lock.

95% of lock, 95% slow SQL waits for lock time.

Rows sent, resulting row statistics, including average, minimum, maximum number.

Rows examined, the number of lines scanned.

database, which databases belong to

Users, which user, IP, accounts for the percentage of SQL performed by all users

Query abstract, SQL statement after abstraction

Query sample, SQL statement

"mysql optimization topic" What is slow query? " How to optimize with slow query log? (10)