MySQL configuration optimization (multiple parameters) _mysql

The main variables that have a greater impact on performance optimization are listed below, which are mainly divided into the variables of connection requests and buffer variables.

1. Variable for connection request:

1) max_connections
The maximum number of connections to MySQL, increasing this value to increase the number of file descriptors required by mysqld. If the server has a large concurrent connection request, it is recommended to increase this value. To increase the number 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 the appropriate adjustment of the value can not blindly improve the set value.

The value is too small will often appear error 1040:too many connections errors, you can cross the ' conn% ' wildcard to see the current state of the number of connections, to decide the size of the values.

Show variables like ' max_connections ' Maximum number of connections
Show status like ' Max_used_connections ' the number of connections in response

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 the max_connections setting is too low or exceeding the server load limit, and less than 10% is set too large.

2) Back_log
The number of connections that can be staged by MySQL. This works when the main MySQL thread gets a lot of connection requests in a very short time. If MySQL's connection data reaches Max_connections, the new request will be on the stack to wait for a connection to release the resource, the number of which is back_log, and the connection resource will not be granted if the number of pending connections exceeds Back_log.

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

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

The default value is 50, which can be tuned to 128, and an integer with a Linux system set to a range of less than 512.

3) Interactive_timeout
The number of seconds an interactive connection waits for action before it is closed 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 can be tuned to 7200.

2. Buffer variable

Global buffering:

4) Key_buffer_size
KEY_BUFFER_SIZE Specifies the size of the index buffer, which determines the speed at which indexing is processed, especially the speed at which 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 state values can be obtained using show status like ' key_read% ').

Key_buffer_size only works on the MyISAM table. Even if you don't use the MyISAM table, the internal temporary disk table is the MyISAM table, and you also use this value. You can use the Check status value created_tmp_disk_tables to know the details.

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 take a look at the use of key_buffer_size:

Mysql> show global status like ' key_read% ';

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

| variable_name | Value |

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

| key_read_requests| 27813678764 |

|  Key_reads | 6798830 |

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

There are 27,813,678,764 index read requests, 6,798,830 requests are not found in memory read the index directly from the hard disk, the probability of the index misses the cache is computed:

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

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

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

By checking the status value qcache_*, you can see if the Query_cache_size setting is reasonable (the above status value can be obtained using 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 qcache_hits is very large, it indicates that query buffering is used very frequently, at this time need to increase the buffer size; If the value of qcache_hits is small, It shows that your query is very low repetition rate, in this case, the use of query buffering will affect efficiency, then you can consider not to query buffer. In addition, adding sql_no_cache to a SELECT statement makes it clear that query buffering is not used.

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 size of the buffer that a single query can use, and the default is 1M.

Query_cache_min_res_unit was introduced after version 4.1, which specifies the smallest unit of allocated buffer space, which defaults to 4K. Checking the status value qcache_free_blocks, if the value is very large, indicates that there are a lot of fragments 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 amounts 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%

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

Buffering for each connection

6) Record_buffer_size
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), which can be changed to 16773120 (16M)

7) Read_rnd_buffer_size
Random read buffer size. When rows are read in any order (for example, in sorted order), a random read buffer is assigned. When sorting queries, MySQL first scans the buffer to avoid disk search, improve query speed, if you need to sort large amounts of data, you can adjust the value appropriately. However, MySQL will release this buffer space for each client connection, so try to set this value as appropriate to avoid excessive memory overhead.

General can 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 action.

The default value is 2097144 (2M) and can be changed to 16777208 (16M).

9) Join_buffer_size
The size of the buffer that the Federated query operation can use

Record_buffer_size,read_rnd_buffer_size,sort_buffer_size,join_buffer_size is exclusive to 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 placed in it, so that the table content can be accessed more quickly. By checking the status values of peak time open_tables and Opened_tables, you can decide whether to increase the Table_cache value. If you find that open_tables equals Table_cache, and Opened_tables is growing, then you need to add Table_cache values (the above status values can be used show status like ' open% Tables ' Get). Note that Table_cache can not be blindly set to a large value. If set too high, the file descriptor may be insufficient, causing performance instability or connection failure.

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 (memory table) that the user can create. This value is used to calculate the maximum row value of 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 an in-memory heap table to a MyISAM table based on your needs.

) tmp_table_size
Increase the size of a temporary table by setting the Tmp_table_size option, such as a temporary table generated by an advanced group by operation. If you raise this value, MySQL will also increase the size of the heap table, to improve the speed of the join query, we recommend optimizing the query, to ensure that the temporary table generated during the query in memory, to avoid the temporary table is too large, resulting in the generation of MyISAM table based on the hard disk.

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 you create a temporary table, the 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 added, 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% For example the server above Created_tmp_disk_tables/created_tmp_tables * 100% =1.20%, it should be pretty good.

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

Thread_cache_size)
The number of threads stored in that can be reused. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the client's line is placed in the cache. If there are a lot of new threads, in order to improve performance you can have this variable value.

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

The default value is 110, which can be tuned to 80.

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

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

3. Configure several variables for InnoDB

Innodb_buffer_pool_size

For the InnoDB table, the Innodb_buffer_pool_size function is the same as 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, this value can be set to 80% of the physical memory.

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

Innodb_flush_log_at_trx_commit

The main control of InnoDB log buffer data to log files and flush disk Time point, the value is 0, 1, 23 respectively. 0, which means that when a transaction is committed, instead of log write operations, the data in log buffer is written to the log file every second and flush disk once; 1, then every second or every time a commit of things causes the log file write, flush disk operation, ensure the transaction acid; set to 2, Each transaction commit causes the action to write to the log file, but completes the flush disk operation every second.

The actual test found that this value has a very large impact on the speed at which the data was inserted, set to 2 o'clock insert 10,000 records only need 2 seconds, set to 0, only 1 seconds, and 1 seconds when set to 229. As a result, the MySQL manual also recommends merging inserts into a single transaction as much as possible, which can dramatically increase speed.

According to the MySQL manual, this value can be set to 0 or 2, subject to the risk of losing some of the most recent transactions.

Innodb_log_buffer_size

Log cache size, generally 1-8m, the default is 1M, 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 data structures. The default value is 1M. Usually not too big, as long as sufficient on the line, should be related to the complexity of the table structure. If not enough, MySQL writes a warning message in 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 to 2* (numcpus+numdisks), default is generally 8

Article is original, reproduced please indicate the source contact Author: email:zhangbolinux@sina.com qq:513364476