Some important parameters in mysql configuration need to know some configuration items that have a greater impact on the performance of mysql configuration. Max_connectionsMySql is the maximum number of connections. If the number of concurrent connection requests on the server is large, we recommend that you increase the number of concurrent connections. Of course, this is based on the support of the machine, because the more connections, MySql will
Some important parameters in mysql configuration need to know some configuration items that have a greater impact on the performance of mysql configuration. Max_connections the maximum number of connections of MySql. If the number of concurrent connection requests on the server is large, we recommend that you increase the number of concurrent connections. Of course, this is based on the support of the machine, because the more connections, MySql will
Important parameters in mysql Configuration
You need to know some configuration items that have a greater impact on the performance of mysql configurations.
Max_connections
The maximum number of connections of MySql. If the number of concurrent connection requests on the server is large, we recommend that you increase this value to increase the number of parallel connections. Of course, this is based on the support of the machine, if the number of connections is higher, MySql will provide a connection buffer for each connection. The larger the memory overhead, the larger the number of connections, and the more memory the server consumes, thus affecting the server performance, therefore, you must adjust the value based on the server configuration, and do not blindly increase the value. You can use the 'conn' % 'wildcard to view the number of connections in the current status to determine the value size.
Show variables like 'max _ connections 'maximum number of connections and the number of connections returned by show status like 'max _ used_connections. As follows:
Mysql> show variables like 'max _ connections ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ----------------------- + ------- +
| Max_connections | 256 |
+ ----------------------- + ------- +
Mysql> show status like 'max _ used_connections ';
+ ----------------------- + ------- +
| Variable_name | Value |
+ ---------------------------- + ------- +
| Max_used_connections | 256 |
+ ---------------------------- + ------- +
Max_used_connections/max_connections * 100% (Ideal Value: ≈ 85%). If max_used_connections is the same as max_connections, max_connections is set too low or exceeds the server load limit. If it is less than 10%, it is too large.
Back_log
The number of connections that can be saved by MySQL. This takes effect when the main MySQL thread receives many connection requests in a short time. If the connection data of MySQL reaches max_connections, the new request will be stored in the stack to wait for a connection to release resources. The number of stacks is back_log, if the number of connections waiting exceeds back_log, connection resources are not granted.
The back_log value indicates how many requests can be stored in the stack in a short time before MySQL temporarily stops answering new requests. Only if you want to have many connections in a short period of time, you need to increase it. In other words, this value is the size of the listener queue for the incoming TCP/IP connection.
In mysql, the setting of back_log depends on the operating system. In linux, the value of this parameter cannot be greater than the value of the system parameter tcp_max_syn_backlog. Run the following command to view the current value of tcp_max_syn_backlog cat/proc/sys/net/ipv4/tcp_max_syn_backlog.
The default value is 50, which is 128.
Interactive_timeout
The number of seconds that an interactive connection is waiting for action before the server closes. 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 7200.
Key_buffer_size
Key_buffer_size specifies the size of the index buffer, which determines the index processing speed, especially the index reading speed. Check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable. The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above STATUS values can be obtained using show status like 'key _ read % ).
Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value created_tmp_disk_tables to learn the details. Example:
Mysql> show variables like 'key _ buffer_size ';
+ ------------------- + ------------ +
| Variable_name | Value |
+ --------------------- + ------------ +
| Key_buffer_size | 536870912 |
+ ------------ ---------- + ------------ +
Key_buffer_size is 512 MB. 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 |
+ ------------------------ + ------------- +
There are a total of 27813678764 index read requests, 6798830 of which are not found in the memory to directly read the index from the hard disk, calculate the probability that the index does not hit the cache:
Key_cache_miss_rate = Key_reads/Key_read_requests * 100%. It is better to set it to around 1/1000.
The default value is 8388600 (8 M). The host has 4 GB of memory and can be adjusted to 268435456 (256 MB ).
Query_cache_size
Using the Query Buffer, MySQL stores the query results in the buffer. In the future, the results of the same SELECT statement (case sensitive) will be directly read from the buffer.
Check the STATUS value Qcache _ * to check whether the query_cache_size setting is reasonable (the preceding STATUS value can be obtained using show status like 'qcache % ). If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the Qcache_hits value is also very large, it indicates that the query buffer is frequently used. In this case, you need to increase the buffer size; if the Qcache_hits value is small, it indicates that your query repetition rate is very low. In this case, the Query Buffer will affect the efficiency, so you can consider not to use the query buffer. In addition, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that no Query Buffer is used.
Parameters related to query buffering include query_cache_type, query_cache_limit, and query_cache_min_res_unit.
Query_cache_type specifies whether to use the Query Buffer. It can be set to 0, 1, and 2. This variable is a SESSION-level variable.
Query_cache_limit specifies the buffer size that can be used by a single query. The default value is 1 MB.
Query_cache_min_res_unit is introduced after version 4.1. It specifies the minimum unit for allocating the buffer space. The default value is 4 K. Check the status value Qcache_free_blocks. If the value is very large, it indicates that there are many fragments in the buffer. This indicates that the query results are relatively small. In this case, reduce query_cache_min_res_unit.
Example:
Mysql> show global status like 'qcache % ';
+ ------------------------------- + ----------------- +
| Variable_name | Value |
+ ------------------------------- + ----------------- +
| Qcache_free_blocks | 1, 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 | 1, 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 the flush query cache to sort out the CACHE fragmentation, or try to reduce query_cache_min_res_unit, if your queries contain a small amount of data.
Query cache utilization = (query_cache_size-Qcache_free_memory)/query_cache_size * 100%. If the query cache utilization is lower than 25%, the query_cache_size setting is too large and can be appropriately reduced; if the query Cache Usage is above 80% and Qcache_lowmem_prunes> 50, the query_cache_size may be small, or there may be too many fragments.
Query cache hit rate = (Qcache_hits-Qcache_inserts)/Qcache_hits * 100%. The sample server queries cache fragmentation rate = 20.46%, query cache utilization = 62.26%, query cache hit rate = 1.94%, And the hit rate is very low, write operations may be frequent, and there may be some fragments.
Record_buffer_size
Each thread that performs an ordered scan allocates a buffer of this size to each table it scans. If you perform many sequential scans, you may want to increase the value. The default value is 131072 (128 K), which can be changed to 16773120 (16 M ).
Read_rnd_buffer_size
Random read buffer size. When a row is read in any order (for example, in the sorting order), a random read cache is allocated. During sorting query, MySQL first scans the buffer to avoid disk search and increase the query speed. If you need to sort a large amount of data, you can increase the value accordingly. However, MySQL will issue this buffer space for each client connection. Therefore, set this value as much as possible to avoid excessive memory overhead. Generally, it can be set to 16 Mb.
Sort_buffer_size
Each thread that needs to be sorted allocates a buffer of this size. Add this value to accelerate the order by or group by operation. The default value is 2097144 (2 M), which can be changed to 16777208 (16 M ).
Join_buffer_size
The buffer size that can be used by the Joint query operation.
Record_buffer_size, read_rnd_buffer_size, sort_buffer_size, and join_buffer_size are exclusive to each thread. That is to say, if there are 100 thread connections, 16 MB * 100 is occupied.
Table_cache
The cache size of a table. When MySQL accesses a table, if there is space in the table buffer, the table is opened and put into it, so that the table content can be accessed more quickly. Check the status values Open_tables and Opened_tables of the peak time to determine whether to increase the value of table_cache. If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (the preceding STATUS values can be obtained using show status like 'open % tables ). Note that you cannot blindly set table_cache to a large value. If it is set too high, the file descriptor may be insufficient, resulting in unstable performance or connection failure.
1 GB memory machine. The recommended value is 128-256. A server with around 4 GB memory can be set to 384 Mb or MB.
Max_heap_table_size
Memory table size that can be created by the user. This value is used to calculate the maximum row value of the memory table. This variable supports dynamic changes, that is, set @ max_heap_table_size = #.
This variable and tmp_table_size limit the size of the internal memory table. If the size of an internal heap (stacked) Table exceeds tmp_table_size, MySQL can automatically change the heap table in memory to a hard disk-based MyISAM table as needed.
Tmp_table_size
You can set the tmp_table_size option to increase the size of a temporary table, for example, a temporary table generated BY the advanced group by operation. If you increase the value, MySQL will also increase the size of the heap table, which can improve the join query speed. We recommend that you optimize the query as much as possible, make sure that the temporary tables generated during the query process are in the memory. do not generate a hard disk-based MyISAM table because the temporary tables are too large.
Mysql> show global status like 'created _ tmp % ';
+ -------------------------------- + --------- +
| Variable_name | Value |
+ ---------------------------------- + --------- +
| Created_tmp_disk_tables | 21197 |
| Created_tmp_files | 58 |
| Created_tmp_tables | 1, 1771587 |
+ -------------------------------- + ----------- +
Created_tmp_tables increases each time a temporary table is created. If the size of a temporary table exceeds tmp_table_size, a temporary table is created on the disk. Created_tmp_disk_tables increases, and Created_tmp_files indicates the number of temporary files created by the MySQL service, the ideal configuration is: Created_tmp_disk_tables/Created_tmp_tables * 100% <= 25%. For example, the above server Created_tmp_disk_tables/Created_tmp_tables * 100% = 1.20%, which should be quite good.
The default value is 16 MB, which can be adjusted to-. The thread is exclusive and the memory size may not be sufficient for I/O congestion.
Thread_cache_size
The number of threads that can be reused in the memory. If yes, the new thread is obtained from the cache. If there is space when the connection is disconnected, the customer's thread is stored in the cache. If there are many new threads, this variable value can be increased to improve performance. By comparing variables in Connections and Threads_created states, you can see the functions of this variable as follows:
Mysql> show global status like 'thread % ';
Threads_cached 0
Threads_connected 51
Threads_created 655068
Threads_running 48
Threads_cached: the number of Idle threads in the thread cache at this moment.
Threads_connected: indicates the number of established connections. Because a connection requires a thread, it can also be viewed as the number of currently used threads.
Threads_created: indicates the number of threads created since the last time the service was started.
Threads_running: indicates the number of currently activated (non-sleep) threads. It does not represent the number of threads in use. Sometimes the connection has been established, but the connection is in sleep state. The corresponding thread is also in sleep state.
The default value is 110, and the optimization value is 80.
Wait_timeout
Specify the maximum connection time of a request. For servers with around 4 GB of memory, you can set it to 5-10.
Innodb_buffer_pool_size
For InnoDB tables, innodb_buffer_pool_size serves the same purpose as key_buffer_size for MyISAM tables. InnoDB uses this parameter to specify the memory size to buffer data and indexes. For a separate MySQL database server, you can set this value to 80% of the physical memory.
According to the MySQL manual, for 2 GB memory machines, the recommended value is 1G (50% ).
Innodb_flush_log_at_trx_commit
It mainly controls the time when innodb writes data in the log buffer to the log file and flush the disk. The values are 0, 1, and 2. 0 indicates that when a transaction is committed, no log write operation is performed, but the data in log buffer is written to the log file every second and flushed the disk once. 1, the commit of every second or every transaction will cause the log file write and flush disk operations, ensuring the ACID of the transaction; set to 2, each transaction commit will cause the write action of the log file, however, the flush disk operation is completed every second.
In actual tests, it is found that this value has a great impact on the speed of data insertion. If it is set to 2, it takes only 2 seconds to insert 10000 records, and if it is set to 0, it only takes 1 second, it takes 229 seconds to set it to 1. Therefore, we recommend that you merge the insert operation into a transaction as much as possible in the MySQL manual, which can greatly increase the speed.
According to the MySQL manual, this value can be set to 0 or 2 if the risk of losing the most recent transaction is allowed.
Innodb_log_buffer_size
Log cache size, generally 1-8 M, the default value is 1 M. For large transactions, you can increase the cache size.
It can be set to 4 M or 8 M.
Innodb_additional_mem_pool_size
This parameter specifies the memory pool size that InnoDB uses to store data dictionaries and other internal data structures. The default value is 1 MB. Generally, it doesn't need to be too large. It only needs to be enough. It should be related to the complexity of the table structure. If not, MySQL will write a warning message in the error log.
According to the MySQL manual, for 2 GB memory machines, the recommended value is 20 MB, which can be increased as appropriate.
Innodb_thread_concurrency
Thread concurrency. It is recommended to set it to 2 * (NumCPUs + NumDisks). The default value is generally 8.
From: http://bbs.csdn.net/topics/390569375
