search
HomeDatabaseMysql TutorialWhat does the number of mysql connections refer to?

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;        重新设置最大连接数
<pre class='brush:php;toolbar:false;'>mysql&gt; show status like &amp;#39;Threads%&amp;#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><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><pre class='brush:php;toolbar:false;'>&lt;code&gt;mysql&gt; show variables like &amp;#39;%max_connection%&amp;#39;;&lt;br/&gt;+-----------------------+-------+&lt;br/&gt;| Variable_name | Value |&lt;br/&gt;+-----------------------+-------+&lt;br/&gt;| extra_max_connections | |&lt;br/&gt;| max_connections | 2512 |&lt;br/&gt;+-----------------------+-------+&lt;br/&gt;2 rows in set (0.00 sec)&lt;br/&gt;&lt;/code&gt;</pre><p>Set the maximum number of databases in /etc/my.cnf Number of connections</p><pre class='brush:php;toolbar:false;'>[mysqld] max_connections = 100 </pre><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><pre class='brush:php;toolbar:false;'>&lt;code&gt;mysql&gt; show global status like &amp;#39;Thread%&amp;#39;;&lt;br/&gt;+-------------------------+-------+&lt;br/&gt;| Variable_name | Value |&lt;br/&gt;+-------------------------+-------+&lt;br/&gt;| Threadpool_idle_threads | 0 |&lt;br/&gt;| Threadpool_threads | 0 |&lt;br/&gt;| Threads_cached | 29 |&lt;br/&gt;| Threads_connected | 13 |&lt;br/&gt;| Threads_created | 50 |&lt;br/&gt;| Threads_rejected | 0 |&lt;br/&gt;| Threads_running | 1 |&lt;br/&gt;+-------------------------+-------+&lt;br/&gt;7 rows in set (0.00 sec)&lt;br/&gt;&lt;/code&gt;</pre><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><pre class='brush:php;toolbar:false;'>&lt;code&gt;mysql&gt; show variables like &amp;#39;thread_cache_size&amp;#39;;&lt;br/&gt;+-------------------+-------+&lt;br/&gt;| Variable_name | Value |&lt;br/&gt;+-------------------+-------+&lt;br/&gt;| thread_cache_size | 100 |&lt;br/&gt;+-------------------+-------+&lt;br/&gt;1 row in set (0.00 sec)&lt;br/&gt;&lt;/code&gt;</pre><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 &#39;%变量名%&#39;;

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!

Statement
This article is reproduced at:亿速云. If there is any infringement, please contact admin@php.cn delete
How to Grant Permissions to New MySQL UsersHow to Grant Permissions to New MySQL UsersMay 09, 2025 am 12:16 AM

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

How to Add Users in MySQL: A Step-by-Step GuideHow to Add Users in MySQL: A Step-by-Step GuideMay 09, 2025 am 12:14 AM

ToaddusersinMySQLeffectivelyandsecurely,followthesesteps:1)UsetheCREATEUSERstatementtoaddanewuser,specifyingthehostandastrongpassword.2)GrantnecessaryprivilegesusingtheGRANTstatement,adheringtotheprincipleofleastprivilege.3)Implementsecuritymeasuresl

MySQL: Adding a new user with complex permissionsMySQL: Adding a new user with complex permissionsMay 09, 2025 am 12:09 AM

ToaddanewuserwithcomplexpermissionsinMySQL,followthesesteps:1)CreatetheuserwithCREATEUSER'newuser'@'localhost'IDENTIFIEDBY'password';.2)Grantreadaccesstoalltablesin'mydatabase'withGRANTSELECTONmydatabase.TO'newuser'@'localhost';.3)Grantwriteaccessto'

MySQL: String Data Types and CollationsMySQL: String Data Types and CollationsMay 09, 2025 am 12:08 AM

The string data types in MySQL include CHAR, VARCHAR, BINARY, VARBINARY, BLOB, and TEXT. The collations determine the comparison and sorting of strings. 1.CHAR is suitable for fixed-length strings, VARCHAR is suitable for variable-length strings. 2.BINARY and VARBINARY are used for binary data, and BLOB and TEXT are used for large object data. 3. Sorting rules such as utf8mb4_unicode_ci ignores upper and lower case and is suitable for user names; utf8mb4_bin is case sensitive and is suitable for fields that require precise comparison.

MySQL: What length should I use for VARCHARs?MySQL: What length should I use for VARCHARs?May 09, 2025 am 12:06 AM

The best MySQLVARCHAR column length selection should be based on data analysis, consider future growth, evaluate performance impacts, and character set requirements. 1) Analyze the data to determine typical lengths; 2) Reserve future expansion space; 3) Pay attention to the impact of large lengths on performance; 4) Consider the impact of character sets on storage. Through these steps, the efficiency and scalability of the database can be optimized.

MySQL BLOB : are there any limits?MySQL BLOB : are there any limits?May 08, 2025 am 12:22 AM

MySQLBLOBshavelimits:TINYBLOB(255bytes),BLOB(65,535bytes),MEDIUMBLOB(16,777,215bytes),andLONGBLOB(4,294,967,295bytes).TouseBLOBseffectively:1)ConsiderperformanceimpactsandstorelargeBLOBsexternally;2)Managebackupsandreplicationcarefully;3)Usepathsinst

MySQL : What are the best tools to automate users creation?MySQL : What are the best tools to automate users creation?May 08, 2025 am 12:22 AM

The best tools and technologies for automating the creation of users in MySQL include: 1. MySQLWorkbench, suitable for small to medium-sized environments, easy to use but high resource consumption; 2. Ansible, suitable for multi-server environments, simple but steep learning curve; 3. Custom Python scripts, flexible but need to ensure script security; 4. Puppet and Chef, suitable for large-scale environments, complex but scalable. Scale, learning curve and integration needs should be considered when choosing.

MySQL: Can I search inside a blob?MySQL: Can I search inside a blob?May 08, 2025 am 12:20 AM

Yes,youcansearchinsideaBLOBinMySQLusingspecifictechniques.1)ConverttheBLOBtoaUTF-8stringwithCONVERTfunctionandsearchusingLIKE.2)ForcompressedBLOBs,useUNCOMPRESSbeforeconversion.3)Considerperformanceimpactsanddataencoding.4)Forcomplexdata,externalproc

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools