MySQL Configuration optimization

First, the global configuration

(1) max_connections
Maximum number of connections. The default value is 151, up to 2000. If the server has a large number of concurrent connection requests, it is recommended that you increase this value to add a parallel connection. However, if the number of connections is greater than MySQL provides a connection buffer for each connection, it will cost more memory, so adjust the value appropriately.
View the maximum number of connections

mysql> SHOW VARIABLES like ' max_connections ';

View the number of connections to a response

mysql> SHOW STATUS like ' max%connections ';

Max_used_connections/max_connections * 100% (ideal value ≈85%)
If the max_used_connections is the same as max_connections, then it is max_connections set too low or exceed the server load limit, less than 10% is set too large.
(2) Back_log
MySQL can stage the number of connections, the default value is 80, up to 512, can be set to 128. If MySQL's connection data reaches Max_connections, the new request will be present in the stack, waiting for a connection to release the resource, and the number of the stack is back_log. If the number of pending connections exceeds Back_log, the connection resource will not be granted. This works when the primary MySQL thread gets very many connection requests in a very short period of time.
(3) Key_buffer_size
The size of the index buffer, which determines the speed of index processing, especially the speed of index reads.
By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable.

mysql> SHOW STATUS like ' key_read% '; +-------------------+----------+| Variable_name     | Value    |+-------------------+----------+| key_read_requests | 90585564 | | Key_reads         | 97031    |+-------------------+----------+

Calculate the probability of an index miss cache:
Key_cache_miss_rate = key_reads/key_read_requests * 100%, set at 1/1000 or better
Key_buffer_ Size only works on MyISAM tables. Even if you do not use the MyISAM table, the internal temporary disk table is the MyISAM table and this value is used.
The default configuration value is 8388608 (8M), the host has 4GB memory, can be changed to 268435456 (256M)
(4) query_cache_size
using query cache, MySQL stores the query results in a buffer and will read the results directly from the buffer for the same SELECT statement (case-sensitive) in the future. The
best option is to deactivate it from the start, set it to 0 (now the default for MySQL 5.6) and use other methods to speed up the query: Optimize the index, increase the copy spread load, or enable additional caches (such as memcache or Redis).
by checking the status value qcache_*, you can know if the query_cache_size setting is reasonable

mysql> SHOW STATUS like ' qcache% '; +-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Qcache_free_blocks      | 1        | | Qcache_free_memory      | 1031360 |  | Qcache_hits             | 0        | | Qcache_inserts          | 0        | | Qcache_lowmem_prunes    | 0        | | qcache_not_cached       | 10302865 | | Qcache_queries_in_cache | 0        | | Qcache_total_blocks     | 1        |+-------------------------+----------+

Query Cache Fragmentation Rate = Qcache_free_blocks/qcache_total_blocks * 100%
If the query cache fragmentation rate exceeds 20%, you can use flush query cache to defragment the cache, or try to reduce query_cache_min_res_unit if your queries are small data volumes.
Query Cache utilization = (query_cache_size–qcache_free_memory)/query_cache_size * 100%
Query cache utilization below 25% indicates that the query_cache_size setting is too large to be appropriately reduced; query cache utilization is above 80% and Qcache_lowmem_prunes > 50 query_cache_ Size may be a little bit small, or too much fragmentation.
Query Cache Hit Ratio = (qcache_hits–qcache_inserts)/qcache_hits * 100%
If the value of Qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering, if the value of Qcache_hits is also very large, it indicates that the query buffer is used very frequently, the buffer size needs to be increased, and if the value of qcache_hits is small, Indicates that your query repetition rate is very low, in which case the use of query buffering will affect efficiency, then you can consider not querying the buffer. In addition, adding sql_no_cache in the SELECT statement can make it clear that query buffering is not used.
The parameters related to query buffering are Query_cache_type, Query_cache_limit, Query_cache_min_res_unit.
QUERY_CACHE_TYPE Specifies whether to use query buffering, which can be set to 0, 1, 2, which is a variable at the session level.
QUERY_CACHE_LIMIT Specifies the buffer size that can be used by a single query, which defaults to 1M.
QUERY_CACHE_MIN_RES_UNIT Specifies the smallest unit of allocation buffer space, which defaults to 4K. Checking the status value qcache_free_blocks, if the value is very large, indicates that there is a lot of fragmentation in the buffer, which indicates that the query results are relatively small and you need to reduce query_cache_min_res_unit.
(5) Read_buffer_size
Is the size of the MySQL read-in buffer, the request to sequentially scan the table allocates a read buffer, and MySQL allocates a memory buffer for it, and the read_buffer_size variable controls the size of the buffer, and if the order of the table is scanned very frequently, And you think that frequent scans are too slow to improve their performance by increasing the value of the variable and the size of the memory buffer.
The default value is 131072 (128K) and can be changed to 16773120 (16M)
(6) Read_rnd_buffer_size
The random read buffer size. When rows are read in any order (for example, in sort order), a random read buffer is allocated. When you sort a query, MySQL scans the buffer first to avoid disk searches, improve query speed, and, if you need to sort large amounts of data, raise the value appropriately. However, MySQL will issue this buffer space for each client connection, so you should set this value as appropriately as possible to avoid excessive memory overhead.
The default value is 262144 (256K) and can be changed to 16777208 (16M)
(7) Sort_buffer_size
Each thread that needs to be sorted allocates a buffer of that size. Increase this value to accelerate the order by or group by operation.
The default value is 10485760 (1M) and can be changed to 16777208 (16M)
(8) Join_buffer_size
The size of the buffer that can be used by the Federated query operation
Read_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size is exclusive for each thread, that is, if there are 100 threads connected, it is occupied as 16m*100
(9) Table_open_cache
The size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents.
By checking the status values of peak time open_tables and Opened_tables, you can determine whether you need to increase the value of Table_cache.

mysql> SHOW STATUS like ' open%tables '; +---------------+-------+| variable_name | Value |+---------------+-------+| Open_tables | |  Opened_tables | 0     |+---------------+-------+

If Open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_cache. Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.
1G memory machine, the recommended value is 128-256. Server with memory around 4GB This parameter can be set to 256M or 384M.
(Ten) Max_heap_table_size
The size of the memory table that the user can create. This value is used to calculate the maximum row value for the memory table.
This variable, together with Tmp_table_size, limits the size of the internal memory table. If an internal heap (stacked) table is larger than Tmp_table_size,mysql, you can automatically change the in-memory heap table to the hard disk-based MyISAM table as needed.
(one) tmp_table_size
The size of the staging table, such as the temporary table generated by the advanced group by operation. If this value is raised, MySQL will also increase the size of the heap table to improve the speed of the join query, it is recommended to optimize the query as far as possible, to ensure that the temporary table generated during the query in memory, to avoid the temporary table is too large to generate a hard disk-based MyISAM table.

MySQL GLOBAL STATUS like ' created_tmp% '; +-------------------------+----------+| Variable_name           | Value    |+-------------------------+----------+| Created_tmp_disk_tables | 2884297  | | Created_tmp_files       | 870 |      | Created_tmp_tables      | 15899696 |+-------------------------+----------+

Each time a temporary table is created, created_tmp_tables increases, and if the temporary table size exceeds tmp_table_size, the temporary table is created on disk, and Created_tmp_disk_tables is also incremented.
Created_tmp_files represents the number of temporary file files created by the MySQL service, and the ideal configuration is:
Created_tmp_disk_tables/created_tmp_tables * 100% <= 25%
(thread_cache_size)
Thread caching. When the client disconnects, the server processes the client's threads and caches them in response to the next customer instead of destroying them (provided the cache count is not up to the limit).

mysql> SHOW STATUS like ' threads% '; +-------------------+---------+| Variable_name     | Value   |+-------------------+---------+| threads_cached    | 5       | | threads_connected |      | | threads_created   | 1095313 | | Threads_running   | 1       |+-------------------+---------+

Threads_cached: Represents how many idle threads are currently in the thread cache at this moment. If too large, indicating that the MySQL server has been creating threads, which is also a relatively resource-intensive, can be appropriately increased thread_cache_size
Threads_connected: Represents the number of currently established connections, because a connection requires a thread, so it can also be considered as the number of threads currently being used.
Threads_created: Represents the number of threads that have been created since the most recent service was started.
Threads_running: Represents the number of currently active (non-sleep) threads. Does not represent the number of threads in use, sometimes the connection is established, but the connection is in the sleep state, where the corresponding thread is the sleep state.
It is recommended to set the approximate threads_connected value and then combine the physical memory: 1g->8;2g->16;3g->32 Consider the value synthetically.
(interactive_timeout)
The number of seconds an interactive connection waits for an action before being shut down by the server. The default value is 28800 (8 hours) and can be set to 7200.
(wait_timeout)
The number of seconds a non-interactive connection waits for an action before being shut down by the server. To set Interactive_timeout and wait_timeout at the same time will not take effect.
Second, INNODB configuration
(1) innodb_buffer_pool_size
Buffer pool size, cache data and index, INNODB overall performance impact, the equivalent of MyISAM key_buffer_size. If you use only InnoDB, you can set this value to 70%-80% of memory. The larger the better, this guarantees that you will use memory instead of the hard disk for most of the read operations.
(2) Innodb_log_buffer_size the cache size of a transaction that has not yet been performed, the default value is 8M, General 8m-16m. If you have many transaction updates, insert or delete operations, this parameter will save a lot of disk I/O. But if your transaction contains a binary large object or a large text field, this cache will quickly fill up and trigger additional I/O operations. Look at the innodb_log_waits state variable, and if it is not 0, increase the value. But too big is a waste of memory, because 1 seconds will always flush once, so do not need to set to more than 1 seconds of demand.
(3) Innodb_flush_log_at_trx_commit
A policy that writes data from log buffer to a log file and flush the disk, which has a significant effect on the speed at which data is inserted. Values are 0, 1 (default), 2 (recommended), respectively
0: When a transaction commits, it does not write to disk, but writes the data of log buffer to the log file every second and flush (swipe to disk). The fastest, but not safe. A crash of the mysqld process results in the loss of all transactional data for the last second.
1: Writes log buffer data to the log file each time the transaction commits, and flush (swipe to disk). The safest, but also the slowest. The acid of the transaction is ensured.
2: Writes log buffer data to the log file each time the transaction commits, flush (swipe to disk) per second. Faster and more secure than 0. An operating system crash or a system power outage can result in the loss of all transactional data for the last second.
(4) Innodb_log_file_size
The size of each log file in a log group is used to ensure that the write operation is fast and reliable and recovers when it crashes. Generally use 64m-512m, depending on the space of the server. Large files provide higher performance, but the database is restored with more time.
(5) Innodb_additional_mem_pool_size
The memory pool size for storing data dictionaries and other internal structures. The default is 1M, for 2G memory machines, the recommended value is 20M, usually not too large, should be related to the complexity of the table structure. If not enough, MySQL writes a warning message to the error log.
(6) Innodb_buffer_pool_instances
You can open multiple memory buffers so that you can read and write parallel memory. The default is 8, which is typically 1-8. The most often 1s will be refreshed once, it is not too big. For larger transactions, you can increase the cache size. If the InnoDB cache pool is divided into multiple regions, it is recommended that each zone be less than 1GB in space.

MySQL optimization

MySQL Configuration optimization