MySQL Configuration Optimization

The following is a list of major variables that have a large impact on performance optimization, which are divided into connection request variables and buffer variables.

1. Connect the requested variable:

1) max_connections

The maximum number of MySQL connections, increasing this value increases the number of file descriptors required by the mysqld. If the server has a large number of concurrent connection requests, it is recommended that this value be increased to increase the amount of concurrent connections, of course, this is based on the machine can support the case, because if the number of connections between MySQL will provide a connection buffer for each connection, it will cost more memory, so to adjust the value appropriately, can not blindly increase the value.

If the value is too small, error 1040:too Many connections errors, you can check the number of connections in the current state through the ' conn% ' wildcard to decide the size of the values.

Show variables like ' max_connections ' Maximum number of connections

Show status like ' Max_used_connections ' in response to the number of connections

As follows:

Mysql> Show variables like ' max_connections ';

+ ——————— –+ ——-+

| variable_name | Value |

+ ——————— –+ ——-+

| max_connections | 256 |

+ ——————— –+ ——-+

Mysql> Show status like ' Max%connections ';

+ ——————— –+ ——-+

| variable_name | Value |

+ —————————-+ ——-+

| max_used_connections | 256|

+ —————————-+ ——-+

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

The number of connections that can be staged by MySQL. This works when the primary MySQL thread gets very many connection requests in a very short period of time. If the MySQL connection data reaches max_connections, the new request will be present in the stack, waiting for a connection to release the resource, the number of that stack is back_log, and if the number of waiting connections exceeds back_log, the connection resource will not be granted.

The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. Only if you expect to have a lot of connections in a short period of time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections.

When observing your Host process list (mysql> show full processlist), discover a lot of 264084 | Unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | NULL to connect the process, it is necessary to increase the value of Back_log.

The default value is 50, which can be adjusted to 128, for Linux systems with an integer range of less than 512.

3) Interactive_timeout

The number of seconds an interactive connection waits for an action before being shut down by the server. An interactive customer is defined as a customer who uses the client_interactive option for Mysql_real_connect ().

The default value is 28800, which is adjustable to 7200.

2. Buffer variables

Global buffering:

4) Key_buffer_size

KEY_BUFFER_SIZE Specifies 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. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% ').

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. You can use the Check status value created_tmp_disk_tables to learn more.

Examples are as follows:

Mysql> Show variables like ' key_buffer_size ';

+ ——————-+ ———— +

| variable_name | Value |

+ ——————— + ———— +

| Key_buffer_size | 536870912 |

+ ———— ———-+ ———— +

Key_buffer_size is 512MB, let's look at the usage of key_buffer_size:

Mysql> show global status like ' key_read% ';

+ ———————— + ————-+

| variable_name | Value |

+ ———————— + ————-+

| key_read_requests| 27813678764 |

|  Key_reads | 6798830 |

+ ———————— + ————-+

A total of 27,813,678,764 index read requests, with 6,798,830 requests not found in memory directly from the hard disk to read the index, calculate the probability of index misses cache:

Key_cache_miss_rate =key_reads/key_read_requests * 100%, set at about 1/1000 better

The default configuration value is 8388600 (8M), the host has 4GB memory and can be tuned to 268435456 (256MB).

5) Query_cache_size

Using query buffering, MySQL stores the query results in a buffer and will read the results directly from the buffer in the future for the same SELECT statement (case sensitive).

By checking the status value qcache_*, you can know whether the Query_cache_size setting is reasonable (the above status values can be obtained using the show status like ' qcache% '). 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 was introduced after the 4.1 release, which 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.

Examples are as follows:

Mysql> show global status like ' qcache% ';

+ ——————————-+ ————— –+

| variable_name | Value |

+ ——————————-+ ————— –+

| Qcache_free_blocks | 22756 |

| Qcache_free_memory | 76764704 |

| Qcache_hits | 213028692 |

| Qcache_inserts | 208894227 |

| Qcache_lowmem_prunes | 4010916 |

| qcache_not_cached | 13385031 |

| Qcache_queries_in_cache | 43560 |

| Qcache_total_blocks | 111212 |

+ ——————————-+ ————— –+

Mysql> Show variables like ' query_cache% ';

+ ———————————— –+ ———— –+

| variable_name | Value |

+ ———————————— –+ ——— –+

| Query_cache_limit | 2097152 |

| Query_cache_min_res_unit | 4096 |

| Query_cache_size | 203423744 |

| Query_cache_type | On |

| Query_cache_wlock_invalidate | OFF |

+ ———————————— –+ ————— +

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%

Sample server query cache fragmentation rate =20.46%, query cache utilization =62.26%, query cache hit ratio =1.94%, the hit ratio is poor, may write more frequently, and may be some fragments.

Buffering for each connection

6) Record_buffer_size

Each thread that makes a sequential scan allocates a buffer of that size for each table it scans. If you do a lot of sequential scans, you may want to increase the value.

The default value is 131072 (128K) and can be changed to 16773120 (16M)

7) 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.

Can generally be set to 16M

8) 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 2097144 (2M) and can be changed to 16777208 (16M).

9) Join_buffer_size

The size of the buffer that can be used by the Federated query operation

record_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

) Table_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 value of the peak time Open_tables and opened_tables, you can decide whether you need to increase the value of Table_cache. If you find that open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_cache (the above status values can use Show status like ' open% Tables ' obtained). 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.

One) 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 supports dynamic change, that is, set @max_heap_table_size =#

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.

Tmp_table_size)

Increase the size of a temporary table by setting the Tmp_table_size option, such as a 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> show global status like ' created_tmp% ';

+ —————————— –+ ——— +

| variable_name | Value |

+ ———————————-+ ——— +

| Created_tmp_disk_tables | 21197 |

| Created_tmp_files | 58 |

| Created_tmp_tables | 1771587 |

+ —————————— –+ ——— –+

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, Created_tmp_disk_tables also increases, CREATED_TMP_ Files represent 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% such as the server above Created_tmp_disk_tables/created_tmp_tables * 100% =1.20%, it should be pretty good.

Default is 16M, adjustable to 64-256 best, thread exclusive, too large may not have enough memory I/O blocking

Thread_cache_size)

The number of threads that can be reused for saving in. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the customer's line is placed in the cache. If there are many new threads, the value of this variable can be increased in order to improve performance.

By comparing the variables of the connections and threads_created states, you can see the effect of this variable.

The default value is 110, which is adjustable to 80.

thread_concurrency)

The recommended setting is twice times the number of server CPU cores, such as a dual-core CPU, then the thread_concurrency should be 4, and 2 dual-core CPUs, thread_concurrency should be a value of 8. Default is 8

Wait_timeout)

Specifies the maximum connection time for a request, which can be set to 5-10 for a server with about 4GB of memory.

3. Configure several variables for InnoDB

Innodb_buffer_pool_size

For the InnoDB table, the role of innodb_buffer_pool_size is equivalent to key_buffer_size for the MyISAM table. InnoDB uses this parameter to specify the size of memory to buffer data and indexes. For a separate MySQL database server, the maximum value can be set to 80% of physical memory.

According to the MySQL manual, the recommended value for 2G memory machines is 1G (50%).

Innodb_flush_log_at_trx_commit

The main control is InnoDB to write the data in log buffer and flush the disk at a point in time, with values of 0, 1, and 23 respectively. 0, that is, when the transaction commits, do not do the log write operation, but every second writes the data in the log buffer to the logfile and flush the disk once, 1, in every second or every time the commit will cause the log file write, flush disk operations, to ensure that the transaction acid; set to 2, Each transaction commit causes the action to be written to the log file, but the flush disk operation is completed once per second.

The actual test found that this value has a very large effect on the speed at which the data is inserted, that it takes only 2 seconds to insert 10,000 records at 2 o'clock, 0 for 1 seconds, and 1 seconds when set to 229. Therefore, the MySQL manual also recommends merging inserts into one transaction as much as possible, which can greatly increase the speed.

Depending on the MySQL manual, you can set the value to 0 or 2 if the risk of losing the most recent transaction is allowed.

Innodb_log_buffer_size

The log cache size, typically 1-8m, defaults to 1M, and for larger transactions, you can increase the cache size.

Can be set to 4M or 8M.

Innodb_additional_mem_pool_size

This parameter specifies the size of the memory pool used by InnoDB to store data dictionaries and other internal structures. The default value is 1M. Usually not too big, as long as enough on the line, should be related to the complexity of the table structure. If not enough, MySQL writes a warning message to the error log.

According to the MySQL manual, for 2G memory machines, the recommended value is 20M, can be appropriately increased.

Innodb_thread_concurrency=8

Recommended setting is Numcpus+numdisks, which is typically 8 by default

MySQL Configuration Optimization