In terms of implementation, MySQL Server is a multi-threaded structure, including background threads and customer service threads. Multithreading can effectively utilize server resources and improve the concurrency performance of database. In Mysql, the main parameters that control concurrent connections and threads are max_connections, back_log, thread_cache_size, and table_open_CAhce.

1. max_connections

Max_connections controls the maximum number of connections allowed to the MySQL database. The default value is 151. If the status variable connection_ERRORS_MAX_connections is not zero and keeps growing, then a number of connection requests are failing because the number of database connections has reached the maximum allowed value, which is a good reason to increase the value of max_connections.

The maximum number of connections Mysql can support depends on a number of factors, including the quality of thread libraries for a given operating system platform, memory size, load per connection, CPU processing speed, expected response time, etc. On Linux, it is not difficult to support 500-1000 connections on a server with good performance, which needs to be evaluated according to the server performance.

2. back_log

The back_log parameter controls the size of the backlog request stack set when MySQL listens on TCP ports. If the number of connections in MySql reaches max_connections, new requests will be stored in the stack waiting for a connection to release resources. The stack number is called back_log. If the number of connections exceeds back_log, the connection resources will not be granted and an error will be reported. The default value is 50 before version 5.6.6 and 50 + (max_connections / 5) for later versions, but the maximum value cannot exceed 900.

If you need your database to process a large number of connection requests in a short period of time, consider increasing the value of back_log appropriately.

3. table_open_cache

This parameter is used to control the number of table caches that can be opened by all SQL execution threads. Each SQL execution thread must have at least one table cache open during SQL statement execution. The value of this parameter should be set based on the maximum number of connections set to max_connections and the maximum number of tables involved in executing an associated query per connection:

Max_connections x N;

4. thread_cache_size

To speed up connections to the database, MySQL caches a certain number of customer service threads for reuse. The thread_cache_size parameter controls the number of customer service threads MySQL caches.

5. innodb_lock_wait_timeout

This parameter is used to set the time for InnoDB transactions to wait for row locks. The default value is 50ms, which can be set dynamically as required. For business systems that need quick feedback, the wait time for row locks can be reduced to avoid long suspended transactions. For batch processors running in the background, the wait time for row locks can be increased to avoid large rollback operations.