The MySQL configuration variable detailed introduction

Key_buffer_size

Set this variable to assign a space of the specified size to the key buffer (or key cache). But the operating system will only be allocated when the space is actually used. For example, setting the key buffer size to 1GB does not mean that the server will actually allocate 1GB space to it.

Setting a non-0 value on an existing cache will flush the cache, technically an online operation, but it will block all actions that access the cache until the cache is flushed.

Table_cache_size

Setting this variable does not take effect immediately until the next thread opens the table. When it takes effect, MySQL checks the value of the variable. If the value is greater than the number of tables in the cache, the thread can insert the newly opened table into the cache. If the value is less than the number of tables in the cache, MySQL deletes the unused table from the cache.

Thread_cache_size

Setting this variable does not take effect immediately and is deferred until the next time the thread is closed. At that time, MySQL checks to see if there is a space store thread in the cache. If it does, it caches the thread for use by another connection. If not, it will simply end the thread off. In this case, the number of cached threads and the amount of memory used by the thread cache do not fall immediately. The drop is only seen when a new connection is removed from the cache to use a thread. (MySQL only adds threads to the cache when the connection is closed, and removes threads from the cache only when a new connection is created.) ）

Query_cache_size

When the server is enabled, MySQL allocates the amount of memory defined for a variable at once for the query cache. If the variable is updated (even if the value is set to the current value), MySQL immediately deletes all cached queries, sets the cache to the defined size, and reinitialize the cached memory.

Read_buffer_size

MySQL allocates memory for the buffer only when it is needed, but it assigns the specified size to the cache at one time.

Read_rnd_buffer_size

MySQL allocates memory to the buffer only when it is queried, and only allocates the required memory.

Sort_buffer_size

MySQL allocates memory for this buffer only when the query needs to be sorted. But whenever a sort happens, MySQL immediately assigns all the memory defined by the variable, whether or not it requires such a large amount of space.

Adjust the MySQL running parameters, modify the/etc/my.cnf file to adjust the MySQL running parameters after the restart of MySQL, after the MySQL4 version, some of the internal variables can be set in the MySQL runtime, but restart MySQL failed.
MYSQLD Programs--Directories and files
Basedir = path # uses the given directory as the root directory (the installation directory).
DataDir = path # reads the database file from the given directory.
Pid-file = filename # Specifies a file that holds the process ID for the MYSQLD program (for the unix/linux system only);

[Mysqld]
Socket =/tmp/mysql.sock # Specifies a socket file for local communication between the MySQL client and the server (default is/var/lib/mysql/mysql.sock file under Linux)
Port = 3306 # Specify ports for MSSQL listening
Key_buffer = 384M # Key_buffer is the size of the buffer used for the index block, increasing the index that it can be better processed (for all read and multiple writes).
The index block is buffered and shared by all threads, and the size of the key_buffer depends on the size of the memory.
Table_cache = 512 # Opens the number of tables for all threads. Increasing this value can increase the number of file descriptors required by mysqld. Avoids the overhead of frequently opening data tables
Sort_buffer_size = 2M # Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by action.
Note: This parameter corresponds to the allocated memory is per connection exclusive! If there are 100 connections, the total sorted buffer size that is actually allocated is 100X6=600MB
Read_buffer_size = 2M # Read Query operation can use the size of the buffer. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
Query_cache_size = 32M # Specifies the size of the MySQL query result buffer
Read_rnd_buffer_size = 8M # change parameters are read randomly after using row pointer sorting.
Myisam_sort_buffer_size =64m # MyISAM The buffer needed to reorder when the table changed
thread_concurrency = 8 # Maximum number of concurrent threads, value is the number of server logical CPUs x2, if the CPU supports H.T Hyper-threading, then X2
Thread_cache = 8 # #缓存可重用的线程数
skip-locking # Avoid MySQL external locking, reduce error probability and enhance stability.
[Mysqldump]
Max_allowed_packet =16m # Maximum possible packet of messages between server and client

[Myisamchk]
Key_buffer = 256M
Sort_buffer = 256M
Read_buffer = 2M
Write_buffer = 2M

Other Optional parameters:
Back_log = 384
Specifies the number of possible connections to MySQL.
When the MySQL main thread receives very many connection requests in a very short time, this parameter takes effect, the main thread takes a short time to check the connection and starts a new thread.
The value of the Back_log parameter indicates how many requests can be on the stack in the short time before MySQL temporarily stops responding to the new request.
If the system has many connections in a short time, you need to increase the value of the parameter, which specifies the size of the incoming TCP/IP connection's listening queue.
Attempting to set Back_log above your operating system will be ineffective. The default value is 50. For Linux systems It is recommended to set to an integer less than 512.
Max_connections = n
The maximum number of database connections that the MySQL server handles at the same time (the default setting is 100). Too Many connections error will be reported after exceeding the limit
Key_buffer_size = n
The RMA value used to hold the index block (the default setting is 8M), which increases the index (for all read and multiple writes) that can be better processed.
Record_buffer:
Each thread that carries out a sequential scan assigns a buffer of this size to each table it scans.
If you do a lot of sequential scans, you might want to add that value. The default value is 131072 (128K)
Wait_timeout:
The number of seconds the server waits for action on a connection before closing it.
Interactive_timeout:
The number of seconds the server waits for action on an interactive connection before closing it.
An interactive customer is defined as a customer who uses the client_interactive option for Mysql_real_connect ().
The default value is 28800, which can be changed to 3600.
Skip-name-resolve
To prevent MySQL from DNS resolution of external connections, use this option to eliminate the time that MySQL makes DNS resolution.
Note, however, that if this option is turned on, all remote host connection authorizations should be in the IP address mode, otherwise MySQL will not handle the connection request properly!
Log-slow-queries = Slow.log
Record slow queries, then optimize for slow query one by one
Skip-innodb
Skip-bdb
Turn off unwanted table types, and if you need them, do not add this


# > Show VARIABLES like '%query_cache% ';
# > Show STATUS like ' qcache% ';
If the value of Qcache_lowmem_prunes is very large, it indicates that the buffer is often insufficient;
If the value of the qcache_hits is very large, it indicates that query buffering is used very frequently, if the value is small but it will affect efficiency, then you can consider not to query buffer;
If the value of the qcache_free_blocks is very large, it indicates that there are many fragments in the buffer.


##########################################
###### Max_allowed_packet ######
##########################################
A communication packet is a single SQL statement sent to a MySQL server, or a single line sent to the client.
The maximum possible packet of information between the MySQL 5.1 server and the client is 1GB.
When a MySQL client or mysqld server receives a packet larger than max_allowed_packet byte, it emits a "packet too large" error and closes the connection. For some clients, if the communication packet is too large, a "lost connection to the MySQL server" error can be encountered during query execution.
Both the client and the server have their own max_allowed_packet variables, so if you plan to handle large packets, you must increase the variables on both the client and the server.
If you are using the MySQL client program, the default value for the Max_allowed_packet variable is 16MB. To set a larger value, you can start MySQL in the following ways:
mysql> MySQL--max_allowed_packet=32m
It sets the packet size to 32MB.
The default Max_allowed_packet value for the server is 1MB. If the server needs to process large queries, you can increase the value (for example, if you are preparing to handle large BLOB columns). For example, to set this to 16MB, you can start the server in the following ways:
Mysql> mysqld--max_allowed_packet=16m

You can also use the option file to set the Max_allowed_packet. To set this variable to 16MB for the server, you can add the following line to the options file:
[Mysqld]
max_allowed_packet=16m
Increasing the value of this variable is safe because additional memory is allocated only when needed. For example, mysqld allocates more memory only if you issue a long query or mysqld must return a large result row. The variable's smaller default value is a precaution to capture the error packets between the client and the server, and to ensure that there is no memory overflow caused by accidental use of large packets.
If you are using a large BLOB value and do not grant mysqld access to enough memory to process the query, you may also experience strange problems with large packets. If this situation is suspected, try adding ulimit-d 256000 to the Mysqld_safe script and restart mysqld.
##########################################
##### how MySQL opens and closes database tables #####
##########################################
Table_cache, Max_connections, and max_tmp_tables affect the maximum number of files that the server keeps open. If you add one or two of these values, you can encounter restrictions imposed on the number of file descriptors per process open by your operating system. However, you can add this limit to many systems. Ask your OS documentation to find out how to do this because the methods of changing the limits vary greatly from one system to another.
Table_cache is associated with max_connections. For example, for 200 open connections, you should have a table with a buffer of at least N, where n is the maximum number of tables in a join (join).

Show variables like '%slow% '

Max_used_connections/max_connections =0.85---max_connections

Key_cache_miss_rate=key_reads/key_read_requests 0.1%--key_buffer_size is effective for MyISAM tables

key_blocks_used/(key_blocks_used+key_blocks_used)--key_buffer_size is valid for MyISAM table

Created_tmp_disk_tables/created_tmp_tables <=0.25--The corresponding variable: tmp_table_size,max_heap_table_size

Open_tables A large amount, you can adjust the parameters Table_cache

Thread_created is too large, please configure thread_cache_size=

Query caching (queries cache)

Qcache_free_blocks: The number of contiguous memory blocks in the cache. A large number indicates that there may be fragments. FLUSH QUERY Cache will defragment the cache to get a free block.

Qcache_free_memory: Free memory in cache.

Qcache_hits: Increases every time a query hits in the cache

Qcache_inserts: Increases each time you insert a query. The hit count divided by the number of inserts is an out of proportion.

Qcache_lowmem_prunes: The number of times the cache appears to be out of memory and must be cleaned up to provide space for more queries. This number is best for long periods of time; If the number is growing, it means that it may be very fragmented or that there is little memory. (The Free_blocks and free_memory above can tell you what kind of situation it belongs to)

Qcache_not_cached: The number of queries that are not suitable for caching, usually because these queries are not SELECT statements or functions such as now ().

Qcache_queries_in_cache: The number of queries (and responses) that are currently cached.

Qcache_total_blocks: The number of blocks in the cache.

Query Cache parameter configuration:

Query_cache_limit | 2097152 | Queries that exceed this size will not cache

| Query_cache_min_res_unit | 4096 | Cache block minimum size 4 K, too large, easy to cause fragmentation and waste

| Query_cache_size | 203423744 | Query Cache Size

| Query_cache_type | On | Cache type, which determines what queries are cached, and the example indicates that the Select Sql_no_cache query is not cached

| Query_cache_wlock_invalidate | Off | When other clients are writing to the MyISAM table, if the query is on the cache, whether to return the cache result or wait for the write operation to complete the reread table to obtain the results.

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%
----If the query cache fragmentation rate is more than 20%, you can use flush query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your query is small amount of data.

Query Cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%
---query cache utilization below 25% indicates that query_cache_size settings are too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a bit small, or too much fragmentation.

Query Cache Hit Ratio = (qcache_hits-qcache_inserts)/qcache_hits * 100%

Sort usage:

sort_merge_passes | 29 |

| Sort_range | 37432840 |

| Sort_rows | 9178691532 |

| Sort_scan | 1860569 |

Parameters adjusted: sort_buffer_size

File open Number (open_files): corresponding parameter Open_files_limit
More appropriate settings: Open_files/open_files_limit * 100% <= 75%

Table lock Condition:
Table_locks_immediate for immediate release of table Locks
Table_locks_waited represents the number of table locks that need to wait
Table_locks_immediate/table_ locks_waited > 5000, preferably with InnoDB engine