Shulou(Shulou.com)06/02 Report--

This article mainly explains the "detailed introduction of MYSQL LOCKS monitoring". The content of 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 "the detailed introduction of MYSQL LOCKS monitoring".

The performance problem of MYSQL must include the problem of LOCKS, I think no one has any objection, but how to monitor him, to be honest, I don't see many good solutions. There are two extremes, one is that the program itself on MYSQL has a good foundation, so MYSQL basically has very little DEAD LOCKS, and the other, I am afraid, is that people who simply use MYSQL do not know how to monitor DEAD LOCK, so they are not aware of this problem.

First of all, deadlock detection is turned on by default, and INNODB automatically detects transaction private talk, automatic clubhouse or automatic termination private talk, innodb will try to select smaller transactions to roll back and judge according to the size of the transaction in the DML operation.

Relatively speaking, if your innodb_table_locks = 1 and autocommit = 0, then the only way to solve the private problem is to use the parameter innodb_lock_wait_timeout, or pray for GOD help.

Of course, if you turn off the parameter innodb_deadlock_detect, then your deadlock can not be detected, in addition, the setting of the parameter innodb_lock_wait_timeout, you can see whether the people who master the database system are experienced.

If the cause of deadlock is explained, the simple explanation is that session a has mastered resource a, but when dealing with transactions, it needs not only a, but also resource b, while session b needs resource b, and processing needs not only b, but also a, but unfortunately, at the same time Session mastered a to extract b, while session b mastered b to extract a. I don't know whether session a finally got the resource b and unlocked it, or session b got an and unlocked it, and finally the MYSQL system ticked the trumpet. Well, don't hit me, the end result is that a transaction is rolled back and the system logs an error message.

So monitoring dead locks or locks is actually a very important thing.

You know that statement and that statement have a deadlock

Do you know if there is a possibility of salvation

If you've been in the business for a long time, the immediate response is for show engine innodb status to check deadlocks, which is the way mysql 5.x (before 5.6.6) works. I don't know, then you'd better try Baidu.

Now to monitor LOCKS (not only deadlocks, including blocked-like information), you generally need to use performance_schema. (what performance loss, how short of money you have, add a little more CPU, will you die of memory, waste 5-10% of your performance? when your boss asks you why, you can write a report happily, or you can only make it up, it's up to you).

How to do it? there are many ways. Let's talk about the traditional methods first.

1 innodb_print_all_deadlocks

You have to turn on this parameter, otherwise you can only watch show engine innodb sttatus foolishly, and if there are a lot of deadlocks, you will miss a lot of information. At the same time, turn on this parameter and you can see all the relevant deadlock information in ERROR log.

2 make sure your wait/lock/metadata/sql/mdl is open, and then you can check it.

Your metadata_locks table, of course, if you still know threads, then you can combine these two tables to carry on between those two statements, the contradiction between that session and that session, and the related statements will be clear at a glance.

SELECT

Performance_schema.threads.PROCESSLIST_ID

Performance_schema.metadata_locks.*

FROM

Performance_schema.threads

Performance_schema.metadata_locks

WHERE

Performance_schema.threads.THREAD_ID = performance_schema.metadata_locks.OWNER_THREAD_ID

3 the PT tool or write a script to automatically record any deadlocks that may occur.

For example, we can do related deadlock identification through pt-deadlock-logger.

4 dynamically check the deadlock number of innodb, you can carry out dynamic monitoring, especially after the new project, or after the hardware change, you can find the problem as soon as possible. Of course, you need to figure out the innodb_locks and innodb_lock_waits tables first.

Thank you for your reading. The above is the content of "detailed introduction of MYSQL LOCKS Monitoring". After the study of this article, I believe you have a deeper understanding of the detailed introduction of MYSQL LOCKS monitoring, 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.