MySQL maximum number of connections, if the server's concurrent connection request volume is large, it is recommended to increase the number of concurrent connections, of course, this is based on the machine can support the case, because if the more connections, MySQL will provide a connection buffer for each connection, it will cost more memory, the number of connections is too large, The more memory the server consumes, the more it affects server performance, so adjust the value appropriately based on the configuration of the server and not blindly increase the setting value. The ' conn% ' wildcard character can be used to view the number of connections in the current state to decide the size of the value.
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_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, then Max_ The connections is set too low or exceeds the server load limit, which is too large for less than 10%.
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.
The Back_log setting in MySQL depends on the operating system, and under Linux the value of this parameter cannot be greater than the value of the system parameter Tcp_max_syn_backlog. Use the following command to view the current value of the Tcp_max_syn_backlog cat/proc/sys/net/ipv4/tcp_max_syn_backlog.
The default value is 50, which is adjustable to 128. I changed it to 500.
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.
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).
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 query is a small amount of data.
Query Cache utilization = (query_cache_size-qcache_free_memory)/query_cache_size * 100%, query cache utilization below 25% Description query_cache_size settings are too large, can be appropriately reduced; the query cache utilization is above 80% and Qcache_lowmem_prunes > 50 indicates that query_cache_size may be a little small, or too many fragments.
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.
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).
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.
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).
The size of the buffer that can be used by the union 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 occupies 16m*100.
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. 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.
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.
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%, should be pretty good.
The default is 16M, adjustable to 64-256 best, thread exclusive, too large may not have enough memory I/O jams.
The number of threads that can be reused for saving in memory. If so, the new thread is made from the cache, and if there is space when disconnected, the customer's line will be saved in the cache. If there are many new threads, this variable value 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, as follows:
Mysql> show global status like ' thread% ';
Threads_cached0threads_connected51threads_created655068threads_running48Threads_cached: Represents how many idle threads are currently in the thread cache at this moment.
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.
The default value is 110, which is adjustable to 80.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%).
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.
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.
The number of thread concurrency is recommended to be set to Numcpus+numdisks, which is typically 8 by default.
(i) connection
Connections typically come from a Web server, and the following lists some of the connection-related parameters and how to set them up.
1, Max_connections
This is the maximum number of connections allowed by the Web server, remembering that each connection uses session memory (about session memory, which is covered later in the article).
2, max_packet_allowed
The maximum packet size, usually equal to the size of the largest data set you need to return in a chunk, if you are using remote mysqldump, then its value needs to be larger.
3, Aborted_connects
Check the system Status counter to determine that it has not grown if the number of increases indicates that the client has encountered an error while connecting.
4, Thread_cache_size
An inbound connection creates a new thread in MySQL because it's cheap and fast to open and close connections in MySQL, and it doesn't have as many persistent connections as other databases, such as Oracle, but thread pre-creation doesn't save time, which is why MySQL threads are cached.
If you are growing, pay close attention to the threads that are created, and let your thread cache larger, and for 2550 or 100 of thread_cache_size, memory consumption is not much.
(ii) query caching
(iii) temporary tables
The memory speed is pretty fast, so we want all the sort operations to be done in memory, and we can adjust the query to make the result set smaller to sort the memory, or set the variable to a larger size.
Tmp_table_size
Max_heap_table_size
Whenever you create a temporary table in MySQL, it uses the minimum value of both variables as the threshold value, and in addition to building temporary tables on disk, many sessions are created that preempt restricted resources, so it's best to tune the query instead of setting these parameters higher, and Note that tables with a BLOB or text field type are written directly to disk. MySQL bidirectional replication technology
(iv) session memory
Each session in MySQL has its own memory, which is the memory allocated to the SQL query, so you want to make it as large as possible to meet your needs. But you have to balance the number of consistent sessions in the database at the same time. The black art here is that MySQL is cached on demand, so you can't just add them and multiply the number of sessions, which is a much larger estimate than MySQL typically uses.
The best practice is to start MySQL, connect all the sessions, and then continue to focus on the Virt column of the top-level session, the number of mysqld rows is usually relatively stable, this is the actual total memory usage, minus all the static MySQL memory area, get the actual all session memory, Then divide by the number of sessions to get an average.
1, Read_buffer_size
Cache a block of contiguous scans, which is a cross-storage engine, not just a myisam table.
2, Sort_buffer_size
The size of the sort buffer is performed, preferably set to 1m-2m, and then set in the session, setting a higher value for a particular query.
3, Join_buffer_size
Execute the buffer size allocated by the federated query, set it to 1m-2m size, and then set it individually on demand in each session.
4, Read_rnd_buffer_size
For sorting and order by operations, it is best to set it to 1M and then set it as a session variable to a larger value in the session.
(v) Slow query log
A slow query log is a useful feature of MySQL.
1, Log_slow_queries
MySQL parameter in the log_slow_queries parameter set it in the My.cnf file, set it to on, by default, MySQL will put the file into the data directory, the file is named "Hostname-slow.log", But you can also specify a name for this option when you set it.
2, Long_query_time
The default value is 10 seconds, you can set it dynamically, the value from 1 to set it to ON, if the database is started, by default, the log will be closed. As of 5.1.21 and the version of Google patch installed, this option can be set in microseconds, which is an amazing feature, because once you have eliminated all queries that have been queried for more than 1 seconds, the adjustment is very successful, which helps you to eliminate the problem SQL before the problem gets bigger.
3, Log_queries_not_using_indexes
It's a good idea to turn on this option, which really records the query that returns all rows.
Summary
We introduced the MySQL parameters of the five major categories of settings, usually we rarely touch them, in the MySQL performance tuning and troubleshooting when these parameters are very useful.
Cached queries in MySQL include two resolution query plans, as well as the returned datasets, which will invalidate items in the query cache if the underlying table data or structure changes.
1, Query_cache_min_res_unit
MySQL parameters in the query_cache_min_res_unit query cache block is allocated at this size, using the following formula to calculate the average size of the query cache, based on the calculation results set this variable, MySQL will be more efficient use of query cache, cache more queries, Reduce the waste of memory.
2, Query_cache_size
This parameter sets the total size of the query cache.
3, Query_cache_limit
This parameter tells MySQL to drop a query larger than this size, generally large queries are relatively rare, such as running a batch to perform a large report statistics, so those large result sets should not be filled with the query cache.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
