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

This article mainly explains "how to solve the MySQL too many connections problem". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how to solve the MySQL too many connections problem".

I. failure situation

Brother Dib received an alarm from a continuous database on a cozy weekend, with the following alarm message:

Second, the difficult process of exploration 1. General thinking

When you see the error message from too many connection, you can basically locate the problem as follows:

(1) the machine load soars, resulting in a decline in SQL execution efficiency and connection accumulation.

(2) sudden increase in business visits (or SQL injection), resulting in a full number of connections

(3) "deadlock" or serious lock competition, resulting in a large number of SQL accumulation.

2. Investigation process

(1) all the performance indicators of the machine are normal, and there is no high load phenomenon. This reason is ruled out for the time being.

(2) check the monitoring information and find that there is no sudden increase in traffic before the time when the number of connections is full. At the same time, no work order has been injected by checking the alarm information.

(3) finally, go to the server to check the execution of SQL:

3.1) View show full processlist

A large number of requests are in "Waiting for table metadata lock" and can be divided into three types of requests:

A. Select request

B. Rename request

C. Sleep request

3. 2) analyze Waiting for table metadata lock

Generally speaking, the common "Waiting for table metadata lock" will appear on DDL operations or transactions with uncommitted transactions. From the information_ schema.processlist table, no DDL operations are found, and only rename is left for operations that can generate MDL locks, but according to the state of SQL execution, rename operations are also waiting for MDL locks, so rename operations should be blocked operations, not operations that generate MDL locks.

Then let's take a look at the deadlocks and transaction metrics:

A. there is no deadlock information in show engine innodb status;

B. information_schema.innodb_trx, information_schema.innodb_locks, information_schema.innodb_lock_waits also do not have any form of lock information.

Now basically ruled out the display deadlock problem, that is from the show full processlist can not catch any request, here is more confused, when looking at the structure of the table below, it is found that this table is the myisam engine, so there is no value in the above two statistics can be explained.

In fact, the problem focuses on unfinished transactions. In fact, there is a misunderstanding. At that time, when communicating with the developer that there were unclosed transactions, the developer always thought it impossible, because the myisam table does not support transactions, only innodb supports transactions. But for MDL locks, the introduction of MDL transaction-level locks after 5.5is effective for both myisam and innodb.

3.3) View uncommitted transactions

After looking at the transaction auto-commit variable under the system, the value of autocommit is ON, which means that if the transaction is not committed, it is only possible for the business to actively open a transaction without commit.

To verify this conjecture, general log is opened, and it is found in log that after the business starts the transaction, the value of autocommit is set to 0, resulting in the commit that must be displayed before the transaction can be committed.

At this time, let's look at the connection request with a host of 10.49.84.70. Because the execution speed of select is very fast and visits are not frequent, in the sampled show processlist, the status value is "Sleep" most of the time, which brings some confusing interference to the location of the problem. Then we kill lost this process, and sure enough, the accumulated request was executed in an instant, which confirmed the above inference.

3. Problem solving

In the process of communicating with the developer, the developer said that the myisam table in the library would not open the transaction actively, and there was no code for autocommit=0 in the code, so what is the root cause?

When we locate that the requests on this server are all timed scripts from python, we use its pymysql module when using python to operate mysql, but we must use committed transactions when inserting. The pymysql module of Python sets autocommit=0 by default.

Let's compare other normal connection requests that also use python access, which will manually commit before being disconnected.

After finding out the reason, I thought about it. Is it possible to set up autocommit=1 after the establishment of the company? In this way, the manual commit can no longer be considered for the newly changed SQL, but can be set for each connection when initializing the connection pool, that is,

Third, extended thinking 1. Metadata lock

(1) brief introduction of MDL

In order to maintain the data consistency of table metadata in a concurrent environment, metadata cannot be written when there are active transactions (explicit or implicit) on the table. Therefore, from the MySQL5.5 version, the MDL lock (metadata lock) is introduced to protect the metadata information of the table, and to solve or ensure the consistency between the DDL operation and the DML operation.

For the introduction of MDL, it mainly solves two problems, one is transaction isolation, for example, under the repeatable isolation level, session A during two queries, session B makes changes to the table structure, the results of the two queries will be inconsistent and can not meet the requirements of repeatable reading. Another is the problem of data replication. For example, when session An executes multiple update statements, another session B changes the table structure and submits it first, which will cause slave to redo alter first, and then redo update. There will be replication errors. So when you do the above on the table, if there is an active transaction on the table (uncommitted or rolled back), the session requesting to write will wait at the Metadata lock wait.

Metadata Lock Wait wait occurs in both InnoDB engine tables that support transactions and MyISAM engine tables that do not support transactions. Once the Metadata Lock Wait wait occurs, all subsequent access to the table will be blocked on the wait, resulting in connection accumulation and business impact.

(2) Common MDL lock scenarios

2.1) currently, DDL operations are performed when DML operations are performed.

2.2) the use of mysqldump/mysqlpump will be blocked when there is a long query on the table or when using alter

2.3) if the transaction is not committed or rolled back after the transaction is opened implicitly, such as uncommitted or rolled back after the query is completed, the DDL will be blocked

If there are failed query transactions on the table, such as query columns that do not exist, the statement failure returns, but the transaction is not committed, the DDL will still be blocked.

2. Support for transactions by myisam and innodb

Myisam does not support transactions, and innodb supports transactions. In fact, there is no problem with this concept, but here we only support transactional operations of data, which can be clearly understood through the following simple experiments (I will not elaborate on the relevant concepts and explanations of transactions. I just want to distinguish that mysiam does not support transactions, but the operation on Myisam in the active transaction will still generate MDL locks):

When the isolation level is RC:

(1) myisam table

CREATE TABLE `tb2` (`a` int (11) DEFAULT NULL) ENGINE=MyISAM

1.2) Session 1:

Mysql > begin

Mysql > insert into tb2 (a) value (1)

(after session2's update)

Mysql > select * from tb2

+-+

| | a |

+-+

| | 3 |

+-+

Session 2:

Mysql > select * from tb2

+-+

| | a |

+-+

| | 1 |

+-+

Mysql > update tb2 set axi3 where axi1

Mysql > select * from tb2

+-+

| | a |

+-+

| | 3 |

+-+

Mysql > alter table tb2 add b int (11)

... Hangs...

(2) innodb table

2.1) CREATE TABLE `tb3` (`a` int (11) DEFAULT NULL) ENGINE=INNODB

2.2) Session 1:

Mysql > begin

Mysql > insert into tb3 (a) value (1)

Session 2:

Mysql > select * from tb3

Empty set (0.00 sec)

3. Another BUG of the myisam table

(1) scenario

CREATE TABLE `tb2` (`a` int (11) DEFAULT NULL) ENGINE=MyISAM

1.2) Session 1:

Mysql > begin

Mysql > select * from tb2

Session 2:

Mysql > create table if not exists tb2 (an int)

... Hangs...

1.3) View show processlist

Session 1:Sleep

Session 2:Waiting for table metadata lock

(2) solution

Commit or rollback on ① session 1

② opens another session3, and kill drops a suspicious connection

Thank you for your reading, the above is the content of "how to solve the MySQL too many connections problem", after the study of this article, I believe you have a deeper understanding of how to solve the MySQL too many connections problem, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.