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)