The number of mysql connections refers to the maximum number of customer connections that the database can accept at the same time. The number of MySQL connections is a component that must be considered. It can help the database run safely while maximizing the current performance of the database. The number of MySQL connections is an important configuration that can affect the client transmission and processing in the database. The amount of data. When the number of connections is too few, connection requests may be blocked and time out; when there are too many connections, a large amount of memory will be consumed, resulting in reduced performance.
MySQL connection number introduction
The number of database connections is the maximum number of customer connections that the database can accept at the same time.
The number of MySQL connections is a component that must be considered. It can help the database run safely while maximizing the current performance of the database. "Maximum Connection" is another term for controlling which clients can connect to the database..
The importance of configuring the number of MySQL connections is that it will directly affect the efficiency of database processing and transmission of client data. If the number of MySQL connections is too small, the connection request may be blocked and timeout may occur; if the number of MySQL connections is too large, a large amount of memory will be occupied, resulting in low performance. Therefore, the number of MySQL connections must be set reasonably.
The number of MySQL connections can be adjusted through the MySQL configuration file my.cnf, or dynamically adjusted using the following MySQL commands:
SET GLOBAL max_connections=300;
The above command can set the maximum number of MySQL connections to 300. This parameter can also be set in the corresponding my.cnf file:
max_connections=300
The number of MySQL connections can be checked with the following MySQL command:
SHOW STATUS LIKE 'Max_used_connections'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Max_used_connections | 123 | +-------------------------+-------+
This sentence can be rewritten as: In VW In the system, up to 123 connections are allowed to access MySQL at the same time. It also helps to evaluate whether the current maximum number of connections in the system is reasonable:
If the value is high, it indicates that the system may have a large number of clients accessing MySQL at the same time. At this time, it is best to consider adjusting MySQL The maximum number of connections;
If the value is small, it indicates that the available resources of the system cannot be fully utilized. At this time, you can consider increasing the maximum number of MySQL connections.
You need to pay attention to the number of MySQL connections because it has an important impact on the security and performance of the system. Therefore, it needs to be adjusted appropriately to maximize MySQL performance.
How to check the number of connections in Mysql (total number of connections, active number, maximum concurrent number)
show variables like '%max_connection%'; 查看最大连接数 set global max_connections=1000; 重新设置最大连接数
<div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:bash;gutter:true;">mysql> show status like &#39;Threads%&#39;;
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 32 |
| Threads_connected | 10 |
| Threads_created | 50 |
| Threads_rejected | 0 |
| Threads_running | 1 |
+-------------------+-------+
5 rows in set (0.00 sec)</pre><div class="contentsignin">Copy after login</div></div><ul class=" list-paddingleft-2"><li><p>Threads_connected: This value refers to the number of open connections.</p></li><li><p>Threads_running: This value refers to the number of activated connections. This value is generally much lower than the connected value.</p></li><li><p>If the results of Threads_connected and show processlist are the same, it indicates how many connections there are currently. To be precise, Threads_running represents the current number of concurrency</p></li></ul><p>Query the maximum number of connections currently set in the database</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:bash;gutter:true;"><code>mysql> show variables like &#39;%max_connection%&#39;;<br/>+-----------------------+-------+<br/>| Variable_name | Value |<br/>+-----------------------+-------+<br/>| extra_max_connections | |<br/>| max_connections | 2512 |<br/>+-----------------------+-------+<br/>2 rows in set (0.00 sec)<br/></code></pre><div class="contentsignin">Copy after login</div></div><p>Set the maximum number of databases in /etc/my.cnf Number of connections</p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:js;toolbar:false">[mysqld]
max_connections = 100
</pre><div class="contentsignin">Copy after login</div></div><p>In order to ensure the robust operation of the MySQL server, the number of threads should be controlled within a reasonable range. Threads_created indicates the number of threads created. By viewing Threads_created, you can view the process status of the MySQL server. </p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:bash;gutter:true;"><code>mysql> show global status like &#39;Thread%&#39;;<br/>+-------------------------+-------+<br/>| Variable_name | Value |<br/>+-------------------------+-------+<br/>| Threadpool_idle_threads | 0 |<br/>| Threadpool_threads | 0 |<br/>| Threads_cached | 29 |<br/>| Threads_connected | 13 |<br/>| Threads_created | 50 |<br/>| Threads_rejected | 0 |<br/>| Threads_running | 1 |<br/>+-------------------------+-------+<br/>7 rows in set (0.00 sec)<br/></code></pre><div class="contentsignin">Copy after login</div></div><p>If we set thread_cache_size in the MySQL server configuration file, when the client disconnects, the server's thread processing this client will be cached to respond to the next client instead of being destroyed (provided that the cache number has not been reaches the upper limit). </p><p>Threads_created indicates the number of threads created. If the Threads_created value is found to be too large, it indicates that the MySQL server has been creating threads, which is also relatively resource-intensive. You can appropriately increase the thread_cache_size value in the configuration file and query the server thread_cache_size value. : </p><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:bash;gutter:true;"><code>mysql> show variables like &#39;thread_cache_size&#39;;<br/>+-------------------+-------+<br/>| Variable_name | Value |<br/>+-------------------+-------+<br/>| thread_cache_size | 100 |<br/>+-------------------+-------+<br/>1 row in set (0.00 sec)<br/></code></pre><div class="contentsignin">Copy after login</div></div><p>Command: <code>show processlist; If it is a root account, you can see the current connections of all users. If it is another ordinary account, you can only see the connections occupied by you.
The show processlist command only lists the first 100 items. If you want to list them all, please use show full processlist;
mysql> show processlist;
Command: show status;
mysql>show status like '%变量名%';
The variable names are as follows:
Aborted_clients The number of connections that have been abandoned because the client did not close the connection correctly and has died.
Aborted_connects The number of attempts to connect to the MySQL server that have failed.
Connections Number of attempts to connect to the MySQL server.
Created_tmp_tables The number of implicit temporary tables that have been created when the statement is executed.
Delayed_insert_threads The number of delayed insertion processor threads in use.
Delayed_writes The number of rows written with INSERT DELAYED.
Number of rows where errors (such as duplicate key values) occurred during INSERT DELAYED writing.
Flush_commands The number of times the FLUSH command is executed.
Handler_delete The number of times a row is requested to be deleted from a table.
Handler_read_first requests the number of times to read the first row in the table.
Handler_read_key Requests a number to read rows based on the key.
Handler_read_next The number of times requested to read a row based on a key.
Handler_read_rnd Requests the number of times to read a row based on a fixed position.
Handler_update The number of times a row in the table is requested to be updated.
Handler_write The number of times a row is requested to be inserted into the table.
Key_blocks_used Number of blocks used for key caching.
Key_read_requests The number of times a key value is requested to be read from the cache.
Key_reads The number of times a key value is physically read from disk.
Key_write_requests The number of times a keyword block is requested to be written to the cache.
Key_writes The number of times a key-value block is physically written to disk.
Max_used_connections The maximum number of connections used simultaneously.
Not_flushed_key_blocks Key blocks that have changed in the key cache but have not yet been flushed to disk.
Re-expression of this statement: "Not_flushed_delayed_rows" indicates the number of rows that have not been written in the INSERT DELAY queue.
Open_tables Number of open tables.
Open_files Number of open files.
Open_streams The number of open streams (mainly used for logging)
Opened_tables The number of tables that have been opened.
Questions The number of queries sent to the server.
Slow_queries The number of queries that take longer than long_query_time.
Threads_connected The number of currently open connections.
Threads_running The number of threads that are not sleeping.
Uptime How long the server has been working, in seconds.
The above is the detailed content of What does the number of mysql connections refer to?. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
