How to set and get the number of Mysql connections

Get the number of connections

--- Get the maximum number of connections SHOW VARIABLES LIKE '%max_connections%'; 

--- Get the connection list SHOW PROCESSLIST; 
--- Get the connection list SHOW FULL PROCESSLIST; 

--- Get the current connection information Threads_connected is the current number of connections SHOW STATUS LIKE 'Threads%';

--- Get connection statistics such as the maximum number of historical connections and the maximum connection duration, etc. SHOW STATUS LIKE '%Connection%';

mysql> SHOW STATUS LIKE 'Threads%';
+-------------------+------+
| Variable_name | Value |
+-------------------+------+
| Threads_cached | 58 |
| Threads_connected | 57 | --- This value refers to the number of open connections | Threads_created | 3676 |
| Threads_running | 4 | ---This value refers to the number of activated connections, which is generally much lower than the connected value+-------------------+-------+

Threads_connected is the same as the result of show processlist, indicating the current number of connections. To be precise, Threads_running represents the current number of concurrent

Setting the number of connections

Temporary settings

mysql>show variables like 'max_connections'; --- Check the current maximum number of connectionsmsyql>set global max_connections=1000; --- Set the maximum number of connections to 1000 and check again to see if the setting is successfulmysql>exit --- Exit

Permanent settings can set the maximum number of database connections in /etc/my.cnf

[mysqld]
max_connections = 1000

Connection pool settings in the project

The following formula is provided by PostgreSQL, but the underlying principle remains unchanged and is applicable to most database products on the market. Also, you should simulate the expected traffic and set a reasonable value using the formula below. Then, in actual testing, fine-tune the value to find the most appropriate connection size.

Number of connections = ((Number of cores * 2) + Number of available disks)

The number of cores should not include hyperthreading, even if hyperthreading is turned on. If all hot data is cached, the actual number of effective disks is 0. As the cache hit rate decreases, the effective number of disks gradually approaches the actual number of disks. Also note that it is not clear how well this formula works on SSDs.
Well, according to this formula, if your server CPU is a 4-core i7, the connection pool size should be ((4*2)+1)=9.

Let's round it up to 10. Is this okay for you? 10 is too small!

If you think it's not working, you can run a performance test. We guarantee that it can easily support 3,000 users concurrently executing simple queries at a rate of 6,000 TPS. You can also increase the connection pool size to more than 10, at which point you will see the response time start to increase and the TPS start to drop.

What you need is a small connection pool, and a queue of threads waiting for connections

Suppose you have 10,000 concurrent accesses, and you set the connection pool size to 10,000, you are probably wasting your time.

Change it to 1000, too high? Change to 100? Still too many.

You only need a database connection pool of size 10, and then let the remaining business threads wait in the queue.

The number of connections in the connection pool should be set to the number of query tasks that the database can effectively perform simultaneously (usually no more than 2*number of CPU cores).

You should often see some web applications with not a large number of users, in which the database connection pool is set to 100 or 200 to cope with about ten concurrent users. Please do not over-size your database connection pool.

Is it true that the closer you are, the better?

Simulate 9600 concurrent threads to operate the database, and sleep for 550ms between each database operation. Note that the thread pool size set at the beginning of the video is 2048.

Let's take a look at the performance test results when the database connection pool size is 2048:

Each request has to wait 33ms in the connection pool queue. After obtaining the connection, it takes 77ms to execute SQL, and the CPU consumption is maintained at around 95%;

Next, we will reduce the size of the connection pool to 1024, and keep other test parameters unchanged. What is the result?

"Here, the waiting time for obtaining a connection remains basically unchanged, but the SQL execution time is reduced!"

Oh, there has been progress!

Next, we set it smaller, reduce the connection pool size to 96, and keep other parameters such as the number of concurrent connections unchanged to see what the result is:

The average waiting time for each request in the connection pool queue is 1ms, and the SQL execution time is 2ms.

Oh my god! What the hell?

We didn't tweak anything, just reduced the size of the database connection pool, and that brought the average response time down from 100ms to 3ms. The throughput increases exponentially!

You are so good!

Why does this happen?

Let's think about it, why Nginx only uses 4 threads internally, but its performance far exceeds that of Apache HTTPD with 100 processes? If we think about why, the answer is pretty obvious if we think back to the basics of computer science.

You should know that even a computer with a single-core CPU can run hundreds of threads "simultaneously". But we all know that this is just a trick played by the operating system on us by quickly switching time slices.

A CPU core can only execute one thread at a time. Then the operating system switches the context, and the CPU core is quickly scheduled to execute the code of another thread, repeating this process over and over again, giving us the illusion that all processes are running at the same time.

In fact, on a machine with a single-core CPU, executing A and B sequentially is always faster than executing A and B "simultaneously" through time slicing switching. Those who have studied the operating system course should be very clear about the reason. Once the number of threads exceeds the number of CPU cores, adding more threads will only make the system slower, not faster, because of the extra performance involved in context switching.

At this point, it should be clear...

The above is the detailed content of the method of setting and obtaining the number of Mysql connections. For more information about setting and obtaining the number of Mysql connections, please pay attention to other related articles on 123WORDPRESS.COM!