MySQL Performance tuning (soft tuning)

Based on a common version of MySQL server

Purpose: Speed up the responsiveness of the server

Number of processes = Process * thread

1, the query process of MySQL

1) The client sends a connection request to the server

2) server-side (connection pooling) threading to respond to user requests

3) User initiated SQL statement query database select * FROM DB.A;

4) Query cache: Record the user's SQL execution statement and query results

2 and speed up the MySQL server

1) Replace the problematic hardware (the most effective method)

such as: CPU, memory, hard disk

2 ) to tune the settings of the MySQL process

A , concurrency number

optimize the performance parameters of MySQL database:

–max_connections //The number of concurrent accesses allowed by the server, default value 100

–max_used_connections

// number of connections already in response, max_connections * 100% = 99.6% ( ideal value ≈85%)

–VI/ETC/MY.CNF//Set permanent in configuration file/etc/my.cnf

[Mysqld]

max_connections = 1000

　　　　

Mysql>use MySQL;

mysql> Show variables like ' max_connections '; //View the maximum number of concurrent connections, default to mysql>show global status like ' Max_used_connections '; // Current number of connections

mysql> flush status; Refresh

Mysql>set global max_connections=200; Modify parameter values, currently in effect

B, timeout

mysql>Show variables like ' connect_timeout'; Connection time-out, in seconds, default 10s mysql>show variables like ' wait_timeout';

When the client connects to the server, the client does not perform any action at 28800S, the server side automatically disconnects, the number of seconds to wait on a connection before shutting it down, and the default 28800s

Mysql> set global connect_timeout=20; //Global modifications are preceded byGlobal Modifications

Mysql>set wait_timeout=7200; Not globally modified, with this command

The number of concurrent, can be set connect_timeout shorter, the number of concurrent, you can set its long

C, index buffer

–key_buffer index buffer size unit M, default is 16M, Show Unit bytes

– calculate the probability formula for an index Miss cache: key_reads/key_read_requests * 100%= value is as small as possible.

high probability indicates that the odds of finding are small, and the Key_buffer value can be increased appropriately in the configuration file .

-should be increased appropriately when the probability is low Key_buffer the value

Mysql>Show variables like ' key_buffer_size ';   View the size of the index buffer mysql>show variables like ' key_read% ';

Key_read_requests Total number of index read requests key_reads not found in memory, read the number of indexes directly from the hard disk

VIM/ETC/MY.CNF//settings

[Mysqld]                 Key_buffer = 16M                     

8bit=1 bytes, 1024 bytes =1k,1024k=1m, 1024m=1g,1024g=1t

D, other settings

Sort_buffer_size = 512K

– Each thread that needs to be sorted allocates a buffer of that size. Increase this value to speed up the order by or group by query, the query displays the unit bytes

mysql> Show variables like ' sort_buffer_size ';   

read_buffer_size = 256K

– The length of the buffer reserved for read operations that read data from the data table order

mysql> Show variables like "read_buffer_size%";

Read_rnd_buffer_size = 512K

– Query results that are output in a particular order (for example, a query that uses an ORDER BY clause)

mysql> Show variables like "read_rnd_buffer_size%";

Thread_cache_size = 10

– Number of threads that can be reused for saving in the cache (default is 0) mysql> show variables like ' thread_cache_size ';

Mysql> set global thread_cache_size=10; Specify size

    3 ) Optimization of Queries

A, record slow query

Vi/etc/my.cnf

[Mysqld]

log-slow-queries //enable slow query log

log-query-time=5 //Record 5 seconds query with no result set displayed default 10 seconds

log-queries-not-using-indexes //Record not using index query

[[Email protected] ~] mysqldumpslow mysqld-slow.log //View the contents of the slow query log

B, server configuration for query caching

Mysql> Show variables like "query_cache%";

+------------------------------+---------+

| variable_name | Value |

+------------------------------+---------+

| Query_cache_limit | 1048576 | queries that exceed this size will not be cached

| Query_cache_min_res_unit |4096 | minimum size of cache block

| Query_cache_size | 0 | Query Cache Size

| Query_cache_type | On | cache type, determines what queries are cached

| Query_cache_wlock_invalidate | OFF |                                   when other clients are writing to the MyISAM table                                  If the query cache is returned , the cache result, or wait for the write operation to complete the reread table to get results

+------------------------------+---------+

Mysql> show global Status Like "qcache%";

+-------------------------+-------+

| variable_name | Value |

+-------------------------+-------+

| Qcache_free_blocks | 0 | Number of contiguous memory blocks in the cache, which indicates a possible fragmentation

| Qcache_free_memory | 0 | Free memory in the cache

| Qcache_hits | 0 | Increases each time the query hits the cache

| Qcache_inserts | 0 | Each query is inserted to increase the number of hits/inserts is not the ratio

| Qcache_lowmem_prunes | 0 | The number of times the cache is out of memory and must be cleaned up to provide space for more queries

| qcache_not_cached | 0 | The number of cache queries that are not appropriate

| Qcache_queries_in_cache | 0 | The number of current cache queries (and responses)

| Qcache_total_blocks |  0 | Number of blocks in cache

+-------------------------+-------+

This article is from the "Dave-Technology blog" blog, please be sure to keep this source http://davewang.blog.51cto.com/6974997/1858703

MySQL Performance tuning (soft tuning)