Take a look at MySQL concurrency parameter tuning

Free learning recommendation: mysql video tutorial

Mysql concurrency parameter adjustment

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

1. max_connections

Use max_connections to control 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, it means that there are continuous connection requests failing because the number of database connections has reached the maximum allowed value. This is why you can consider increasing the value of max_connections.
Description: When the number of simultaneous connection requests exceeds 151, there are no available connections to handle the client's request. These subsequent connections will be in a waiting state. Waiting for the MySQL connection to be released. If there is no idle connection, the request will time out

Mysql The maximum number of supported connections depends on many factors, including the quality of the thread library of the given operating system platform, Memory size, load per connection, CPU processing speed, expected response time, etc. Under the Linux platform, it is not difficult for a server with good performance to support 500-1000 connections. It needs to be evaluated and set based on the server performance.

2. back_log

The back_log parameter controls the backlog request stack size set when MySQL listens to the TCP port. If the number of MySql connections reaches max_connections, new requests will be stored in the stack to wait for a certain connection to release resources. The number of the stack is back_log. If the number of waiting connections exceeds back_log, connection resources will not be granted. An error will be reported. The default value before version 5.6.6 is 50, and the default value for subsequent versions is 50 (max_connections / 5), but the maximum does not exceed 900.
Description: When the concurrent number of requests sent by the client at the same time is greater than 151, subsequent requests will be in a waiting state. Then the number of waiting connections can reach back_log. These new requests will be stored in the stack. to wait for a connection to be released. The number of stacks is set through back_log.

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

3. table_open_cache

This parameter is used to control the number of table caches that can be opened by all SQL statement execution threads. When executing a SQL statement, each The SQL execution thread must open at least 1 table cache. The value of this parameter should be set according to the maximum number of connections max_connections set and the maximum number of tables involved in executing related queries for each connection :

max_connections x N;
Note: This is not for a certain session, this is for all client execution threads. The number of table caches is the number of tables operated in each SQL statement. For example, a SQL statement generally operates at least one table. If you operate one table, there will be one table cache. If you operate multiple tables, there will be multiple table caches.

mysql> show variables like 'table_open_cache%';+----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| table_open_cache           | 431   || table_open_cache_instances | 16    |+----------------------------+-------+2 rows in set (0.06 sec)

4, thread_cache_size

In order to speed up the connection to the database, MySQL will cache a certain number of customer service threads for reuse, which can be controlled through the parameter thread_cache_size The number of MySQL cache client service threads.
Description: This is equivalent to opening a thread pool on the MySQL server. When the client makes a request, we take out a thread in the thread pool to perform task processing.

mysql> show variables like 'thread_cache_size%';+-------------------+-------+| Variable_name     | Value |+-------------------+-------+| thread_cache_size | 8     |+-------------------+-------+1 row in set (0.00 sec)

A total of 8 thread information is cached.

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 and can be set dynamically as needed. For business systems that require quick feedback, the waiting time for row locks can be adjusted smaller to avoid long-term suspension of transactions; for batch processing programs running in the background, the waiting time for row locks can be adjusted larger to avoid A large rollback operation occurred.

Note: If in a business system that responds quickly, if the row lock is not obtained, just report an error directly, and there is no need to wait for the transaction to respond for a long time.
As you can see, the default timeout is 50ms

mysql> show variables like 'innodb_lock_wait_timeout%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| innodb_lock_wait_timeout | 50    |+--------------------------+-------+1 row in set (0.01 sec)

| Value | -------------------------- - ------- | innodb_lock_wait_timeout | 50 | -------------------------- ------- 1 row in set (0.01 sec)

More related free learning recommendations:mysql tutorial(Video)

The above is the detailed content of Take a look at MySQL concurrency parameter tuning. For more information, please follow other related articles on the PHP Chinese website!