Shulou(Shulou.com)05/31 Report--

This article mainly introduces the example analysis of MySQL concurrent parameter adjustment, which has a certain reference value, and interested friends can refer to it. I hope you will gain a lot after reading this article.

Mysql concurrent parameter adjustment

In terms of implementation, MySQL Server is a multithreaded structure, including background threads and customer service threads. Multithreading can make effective use of server resources and improve the concurrent performance of the database. In Mysql, the main parameters that control concurrent connections and threads include max_connections, back_log, thread_cache_size, and table_open_cahce.

1 、 max_connections

Use max_connections to control the maximum number of connections allowed to the MySQL database, and the default value is 151s. If the state variable connection_errors_max_connections is not zero and keeps growing, it means that connection requests continue to fail because the number of database connections has reached the maximum allowed, which can be considered to increase the value of max_connections.

Note: when more than 151 requests are connected at the same time, there is no connection available to process client requests. The connections that come in later will be in a waiting state. Wait for the MySQL connection to be released, and if there is no free connection, a timeout will be requested.

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

2 、 back_log

The back_log parameter controls the backlog request stack size that MySQL sets when it listens on the TCP port. 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 number of connections in the stack is back_log. If the number of connections waiting for exceeds back_log, connection resources will not be granted and an error will be reported. Before version 5.6.6, the default value is 50, and the later version defaults to 50 + (max_connections / 5), but the maximum is no more than 900.

Note: when the concurrent number of requests sent by the client at the same time is greater than 151, the subsequent requests are in a waiting state. Then the number of waiting connections can reach back_log. These new requests are stored on 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, 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 statement execution threads, while each SQL execution thread must open at least one table cache when executing SQL statements. The value of this parameter should be set based on the maximum number of connections set max_connections and the maximum number of tables involved in each connection executing the association query:

Max_connections x N

Description: this is not for a session, this is for all client execution threads. The number of table caches, that is, the number of operating tables in each SQL statement. For example, a SQL statement generally operates on at least one table. If you operate a table, you will have 1 table cache, and if you operate multiple tables, you will have 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 speed of connecting to the database, MySQL caches a certain number of customer service threads for reuse. The number of customer service threads cached by MySQL can be controlled by the parameter thread_cache_size.

Description: this is equivalent to opening a thread pool on the server side of MySQL. When a request comes from the client, 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, which can be set dynamically as needed. For business systems that need quick feedback, the waiting time of row locks can be reduced to avoid long suspensions of transactions; for batch processors running in the background, the waiting time of row locks can be increased to avoid large rollback operations.

Description: if in a relatively fast response business system, if you do not get the row lock, you can report an error directly, and there is no need to let the transaction 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) |

Thank you for reading this article carefully. I hope the article "sample Analysis of MySQL concurrent Parameter Adjustment" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.