Mysql optimization (1) show command slow query log explain profiling

Directory

I. Optimization Overview

Ii. query and index optimization Analysis

1. performance bottleneck locating

Show command

Slow query log

Explain analysis Query

Profiling Analysis Query

 

2 Index and Query Optimization

Iii. Configuration Optimization

1) max_connections

2) back_log

3) interactive_timeout

4) key_buffer_size

5) query_cache_size

6) record_buffer_size

7) read_rnd_buffer_size

8) sort_buffer_size

9) join_buffer_size

10) table_cache

11) max_heap_table_size

12) tmp_table_size

13) thread_cache_size

14) thread_concurrency

15) wait_timeout

 

 

I. Optimization Overview

MySQL databases are two common bottlenecks: CPU and I/O bottlenecks. When the CPU is saturated, it usually occurs when the data is loaded into the memory or when the data is read from the disk. The disk I/O bottleneck occurs when the loading data is much larger than the memory capacity. If the application is distributed on the network, when the query volume is large, the ping bottleneck will appear on the network, we can use mpstat, iostat, sar, and vmstat to view the system performance status.

In addition to the performance bottleneck of the server hardware, for the MySQL system itself, we can use tools to optimize the database performance. There are usually three types: Using indexes, analyzing queries using EXPLAIN, and adjusting the internal configuration of MySQL.

Ii. query and index optimization Analysis

When optimizing MySQL, you usually need to analyze the database. Common analysis methods include slow query logs, EXPLAIN analysis and query, profiling analysis, and show command query system status and system variables, by locating the analysis performance bottleneck, we can better optimize the performance of the database system.

1. performance bottleneck locating Show command

We can use the show command to view the MySQL status and variables and find the System Bottleneck:

Mysql> show status -- display status information (Extended show status like 'xxx ')

Mysql> show variables -- display system variables (Extended show variables like 'xxx ')

Mysql> show innodb status -- display the InnoDB Storage engine status

Mysql> show processlist -- view the current SQL Execution, including the execution status and whether to lock the table.

Shell> mysqladmin variables-u username-p password -- Display System variables

Shell> mysqladmin extended-status-u username-p password -- display status information

View status variables and help:

Shell> mysqld -- verbose -- help [| more # display row by row]

 

For more information about how to use the Show command, see: http://blog.phpbean.com/a.cn/18/

Slow query log

Enable slow query log:

Add two configuration parameters under the [mysqld] Line in the configuration file my. cnf or my. ini.

Log-slow-queries =/data/mysqldata/slow-query.log

Long_query_time = 2

Note: The log-slow-queries parameter is the location where slow query logs are stored. Generally, this directory must have the write permission of the mysql running account, generally, this directory is set to the mysql data storage directory;

2 In long_query_time = 2 indicates that the query takes more than two seconds to record;

Add the log-queries-not-using-indexes parameter to my. cnf or my. ini, indicating that no index is used in the query.

Log-slow-queries =/data/mysqldata/slow-query.log

Long_query_time = 10

Log-queries-not-using-indexes

Method 2:

We can use the command line to set variables to instantly start slow log query. It can be seen that the slow log is not opened. slow_launch_time = # indicates that the slow_launch_threads counter will increase if the thread creation takes longer time than this value.

Enable slow log

 

 

After MySQL, You can query the value of long_query_time.

To facilitate the test, you can set the slow query time to 5 seconds.

Slow query analysis mysqldumpslow

You can open the log file to find out which SQL statements are inefficient in execution.

[Root @ localhost mysql] # slow-query.log more

# Time: 081026 19:46:34

# User @ Host: root [root] @ localhost []

# Query_time: 11 Lock_time: 0 Rows_sent: 1 Rows_examined: 6552961

Select count (*) from t_user;

From the log, you can find that the SQL query time exceeds 5 seconds, but less than 5 seconds does not appear in this log.

If the slow query log contains many records, you can use the mysqldumpslow tool (which is included in the MySQL client installation) to classify and summarize the slow query logs. Mysqldumpslow classifies and summarizes log files and Displays summary results.

Enter the log storage directory and run

[Root @ mysql_data] # mysqldumpslow slow-query.log

Reading mysql slow query log from slow-query.log

Count: 2 Time = 11.00 s (22 s) Lock = 0.00 s (0 s) Rows = 1.0 (2), root [root] @ mysql

Select count (N) from t_user;

Mysqldumpslow command

/Path/mysqldumpslow-s c-t 10/database/mysql/slow-query.log

This will output 10 SQL statements with the maximum number of records, of which:

-S indicates the sorting method. c, t, l, and r are sorted by the number of records, time, query time, and number of returned records, ac, at, al, and ar indicate reverse descriptions;

-T indicates the top n, that is, the number of previous data records returned;

-G, followed by a regular expression matching mode, which is case insensitive;

For example:

/Path/mysqldumpslow-s r-t 10/database/mysql/slow-log

You can obtain up to 10 queries from the returned record set.

/Path/mysqldumpslow-s t-t 10-g "left join"/database/mysql/slow-log

Obtain the query statements containing the left join in the first 10 results sorted by time.

The mysqldumpslow command can be used to obtain various query statements that we need very clearly. Monitoring, analyzing, and optimizing MySQL query statements is an important step for MySQL optimization. After the slow query log is enabled, the log record operation may occupy CPU resources to some extent, affecting mysql performance. However, you can enable it to locate the performance bottleneck in stages.

Explain analysis Query

The EXPLAIN keyword can be used to simulate the optimizer to execute SQL query statements to know how MySQL processes your SQL statements. This helps you analyze the performance bottleneck of your query statement or table structure. Run the explain command to obtain the following information:

-Read sequence of tables

-Data read operation type

-Which indexes can be used?

-Which indexes are actually used?

-Reference between tables

-Number of rows in each table queried by Optimizer

EXPLAIN field:

Ø Table: indicates the Table on which the data in this row is displayed.

Ø possible _ keys: displays the indexes that may be applied to this table. If it is null, there is no possible index. You can select an appropriate statement from the WHERE statement for the relevant domain.

Ø key: actually used index. If it is NULL, no index is used. MYSQL rarely selects indexes with insufficient optimization. In this case, you can use index in the SELECT statement to forcibly USE an index or use ignore index (index) to forcibly IGNORE the INDEX.

Ø key _ len: the length of the index used. The shorter the length, the better.

Ø ref: displays which column of the index is used. If possible, it is a constant.

Ø rows: the number of rows that MySQL considers to be required to return request data

Ø type: this is one of the most important fields and shows the type used in the query. The connection types from the best to the worst are system, const, eq_reg, ref, range, index, and ALL.

Nsystem and const: You can convert the queried variables into constants. For example, id = 1; id is the primary key or unique key.

Neq_ref: access the index and return the data of a single row. (normally, the index used for query is the primary key or unique key)

Nref: Access Index. Data of a certain value is returned. (multiple rows can be returned.) It usually occurs when = is used.

Nrange: This connection type uses an index to return rows in a range, such as> or <search for something, and the field is indexed (Note: Not necessarily better than index)

Nindex: performs full table scan in the order of indexes. The advantage is that no sorting is required. The disadvantage is that full table scan is required.

NALL: full table scan, which should be avoided as much as possible

Ø Extra: There are several additional information about how MYSQL parses the query:

Nusing index: only indexes are used to avoid table access.

Nusing where: use where to overwrite data. Not all where clause must display using where. For example, use = to access the index.

Nusing tmporary: temporary table used

Nusing filesort: additional sorting is used. (when order by v1 is used, but index is not used, additional sorting is used)

Nrange checked for eache record (index map: N): no good index.

Profiling Analysis Query

Through slow log query, we can know which SQL statements are inefficient in execution. Through the explain command, we can know the specific execution status and index usage of the SQL statements. We can also view the execution status using the show command.

If you think that the explain information is not detailed enough, you can use the profiling command to obtain more accurate information about system resources consumed by SQL Execution.

Profiling is disabled by default. You can use the following statement to view

Function: mysql> set profiling = 1; run the SQL statement to be tested:

Mysql> show profiles \ G; to obtain the time and ID of the executed SQL statement

Mysql> show profile for query 1; obtain the execution details of the corresponding SQL statement.

Show Profile command format:

Show profile [type [, type]… ]

[For query n]

[LIMIT row_count [OFFSET offset]

Type:

ALL

| BLOCK IO

| CONTEXT SWITCHES

| CPU

| IPC

| MEMORY

| PAGE FAULTS

| SOURCE

| SWAPS

The above 16 rows are resource information for very simple select statements. For more complex SQL statements, there will be more rows and fields, for example, converting HEAP to MyISAM, Copying to tmp table, etc. These fields are not displayed because the preceding SQL statement does not have complex table operations. Through profiling resource consumption information, we can take targeted optimization measures.

After the test is completed, close the parameter: mysql> set profiling = 0

Excerpt: Trajectory