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

Mysql performance optimization, slow query analysis, index optimization and configuration

one。 The basic ideas and steps of each item

1. Performance bottleneck location: show command, slow query log, explain analysis query, profiling analysis query,

two。 Index and query optimization

3. Configuration optimization

MySQL is the two common bottlenecks, which are the bottleneck of cpu and iMaguo. Cup reads data on memory and disk. Disk iUnip occurs when the data is far larger than the memory capacity. When the query volume is too large, the bottleneck will be exposed in the network. You can check the system performance by command: mpstat, iostat, sar, vmstat.

Use the other two to view

There are usually three ways to optimize databases: using indexes, analyzing queries with explain, and adjusting the internal configuration of mysql

1. Query and index optimization

Optimizing mysql requires database analysis, including slow query, explain, profiling analysis, and show commands to view system status and system variables.

Show command:

Use variables to view database configuration information

View the various status values of the database running

Use mysqladmin to view system variables and status information on the command line

Use help show in the database to view all parameter references

Slow query log

Enable slow log: add 3 parameters to the my.cnf configuration file, and then restart the mysql database service

Restart the service and check to see if a directory exists

You can also set the

Set global slow_query_log=on; set long_query_time= time; set global slow_query_log_file= "directory"

View slow query setting information

In order to test how many rows can be created by creating a script to create a library and a table, 20000 rows are built in the second test.

Script content

Execute a command and view the slow query log

Check in the log file. If you cannot query in the log, reduce the time time in / etc/my.cnf.

Explain Analysis query

Use the explain keyword to simulate the optimizer's execution of sql query statements

EXPLAIN field:

Table: shows which table the data in this row is about

Type: this is one of the most important fields that show what type the query uses. The best to worst connection types are system, const, eq_reg, ref, range, index and ALL

Possible_keys: displays the indexes that may be applied to this table. If empty, there is no possible index.

Key: the index actually used. If NULL, the index is not used.

Key_len: the length of the index used. The shorter the length, the better without losing accuracy.

Ref: shows which column of the index is used and, if possible, a constant

The number of rows that rows:MySQL believes must be retrieved to return the request data

Extra: additional information about how MYSQL parses queries

No index is not a single lookup, but a full table scan.

Optimization method: create an index, and then execute explain before searching

Profiling Analysis query

The resource consumption information of sql execution can be found more accurately through the profiling command (profiling is turned off by default)

Open profiling and check to see if it is enabled

Perform a statement test

Use profile to view the details of the execution of sql statements status: is the state in profile, duration: is the time spent in the status state

You can test more information.

SHOW PROFILE [type [, type]...] [FOR QUERY n]

Type:

ALL: displays all the cost information

BLOCK IO: display block IO related overhead

CPU: displays user CPU time and system CPU time

IPC: displays information about sending and receiving overhead

PAGE FAULTS: displays overhead information related to page errors

SWAPS: displays information about the cost related to the number of exchanges

After testing, close profiling so as not to affect the normal use of the database

two。 Configuration optimization

The optimization of mysql parameters is related to different websites, such as current volume, number of visits, number of posts, network situation and hardware configuration. Only through continuous debugging can the best effect be achieved.

Variables of the connection request

(1) the maximum number of max_connections:mysql connections. If the server has a large number of concurrent connections, you must increase the connection value, otherwise it will be down. The more mysql connections, the more connection buffers will be provided for each connection, and the more memory will be spent.

View maximum number of connections

View the number of connections to the current response

Adjust the maximum number of connections and test to add the parameter max_connections = number of connections to / etc/my.cnf

When I got to the fourth connection, I reported the wrong connection. Too many connections are not allowed.

(2) back_log: can temporarily store the number of connections. When the main MySQL thread has many connection requests in a short period of time, it will work.

When you look at the list of host processes and find that there are a lot of them waiting to connect, use the following command to check

Check the number of connections in back_log

Modify the number of back_log connections and add parameters to / etc/my.cnf

Then restart the service

(3) wait_timeout and interactive_time

Wait_timeout: the number of seconds that mysql has to wait before closing a non-interactive connection

Interactive_time: the number of seconds that mysql has to wait before closing an interactive connection. If the number of seconds exceeds the default value, it will be disconnected automatically. The default value is 28800 to 8 hours, which can be adjusted to 7200.

Wait_timeout: if the setting is too small, the connection closes quickly, so some persistent connections don't work.

If the setting is too large, it is easy to cause the connection to open for too long, resulting in too many connections errors. Generally speaking, it is recommended that wait_timeout be reduced as much as possible.

View wait_timeout, interactive_timeout

Modify the / etc/my.cnf file and add it under [mysqld]

Wait_timeout = number of seconds

Interactive-timeout = number of seconds

(4) key-buff_size

Key_buff_size: specifies the size of the index buffer and determines the processing speed of the index. You can see whether the key_buffer_size setting is reasonable by viewing key_read_requests and key_reads.

View status valu

The best ratio of the two is key_reads/key_buffer_size 1 / 100 / 1 / 1000.

There were a total of 6 index read requests, 3 of which were not found in memory to read the index directly from the hard disk.

* key_buffer_size only works on mylsam engine tables, even if you don't use mylsam tables, you can use internal temporary disk tables. This table is the mylsam engine.

Adjust how many MB key_buffer_size adds parameter key_buffer_size= under [mysqld] in / etc/my.cnf

Restart mysqld and it will take effect

(5) query_cache_size is abbreviated to QC

Using query buffering, mysql stores the query results in a buffer, and for the same select statement, the results are read directly from the buffer. You can know whether the query_cache_size setting is reasonable by looking at the status value qcache%,.

Qcache_free_blocks: the number of adjacent memory blocks in the cache. If there are more, it proves that there are more fragments. Clean up the fragments in the cache through flush query cache to get free blocks.

The current amount of memory left in the qcache_free_memory:query cache

Qcache_hits: indicates how many times the cache has been hit

Qcache_inserts: indicates how many misses and then inserts

Qcache_lowmem_prunes: how many query are purged out of query cache due to insufficient memory

Qcache_not_cached: the number of queries that are not suitable for caching

Qcache_queries_in_cache: the number of query of cache in the current query cache

Qcache_total_blocks: the number of block in the current query cache

View the configuration of query_cache

Query_cache_limit: queries larger than this size will not be cached

Query_cache_min_res_unit: minimum size of the cache block

Query_cache_size: query cache size

Query_cache_type: cache typ

Modify / etc/my.cnf add query_cache_size = how many M query_cache_type=1 (on)

Restart the mysqld service to see if it started successfully

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.