How the MySQL OPS series monitors big business

background
Have you ever had a situation like this?

One SQL execution is particularly slow, causing the entire transaction to be in the running phase
A session of SQL has been executed, but delayed commit, has been in the sleep phase
A session is in the lock wait stage and is not over
Above, most of the reasons are caused by large business, then we have a good chat about the topic

Key words
Environment

1. MySQL5.7.22
   The low version of MySQL is no longer considered, as is the case with a SAS disk company, which is time consuming and mysql5.7+ standard
2. InnoDB Storage Engine

3. CentOS 6
   Related characteristics of large business

4. Transaction open to the end of a very long time, our side for example 10s
5. The transaction being executed
6. Non-committed transactions
   Actual combat
   How to monitor the transactions that are being executed
7. SELECT * FROM Sys.processlist
8. Show Processlist
9. SELECT * FROM Information_schema.processlist
10. SELECT * FROM Sys.session
11. SELECT * from Information_schema.innodb_trx;
12. Select from performance_schema.events_statements_current
    How to monitor those uncommitted transactions
    1
    Select from Information_schema.innodb_trx
    How to combine the two

Select trx_id,innodb_trx.trx_state,innodb_trx.trx_started,se.conn_id as Processlist_id,trx_lock_memory_bytes, Se.user,se.command,se.state,se.current_statement,se.last_statement from INFORMATION_SCHEMA. Innodb_trx,sys.session as SE where trx_mysql_thread_id=conn_id;
+---------+-----------+---------------------+----------------+-----------------------+------+---------+-------- --+-----------------------------------+-----------------------------------+
| trx_id | Trx_state | trx_started | processlist_id | Trx_lock_memory_bytes | user | Command | State | current_statement | last_statement |
+---------+-----------+---------------------+----------------+-----------------------+------+---------+-------- --+-----------------------------------+-----------------------------------+
| 1592104 | LOCK WAIT | 2018-06-26 11:51:17 | 3 | 1136 | NULL | Query | Updating | Update lc_1 set id=4 where id = 1 | NULL |
| 1592100 | RUNNING | 2018-06-26 11:49:08 | 2 | 1136 | NULL | Sleep | NULL | NULL | Update lc_1 set id=3 where id = 1 |
+---------+-----------+---------------------+----------------+-----------------------+------+---------+-------- --+-----------------------------------+-----------------------------------+
As you can see, by this we can immediately discover that the transaction statement is in the running phase, which transactions are in the lock wait stage, and if so, what should we do with it?
Smart you, will go to the trx_started to find clues, but if the production environment, this is a very complex and busy things
But it doesn't matter, we have artifacts to use.

How to quickly resolve lock wait issues
Dba:sys> SELECT * from Sys.innodb_lock_waits\g
1. Row
Wait_started:2018-06-26 11:49:58
wait_age:00:00:03
Wait_age_secs:3
Locked_table:lc.lc_1
Locked_index:gen_clust_index
Locked_type:record
waiting_trx_id:1592102
Waiting_trx_started:2018-06-26 11:49:58
waiting_trx_age:00:00:03
Waiting_trx_rows_locked:2
waiting_trx_rows_modified:0
Waiting_pid:3
Waiting_query:update lc_1 set id=4 where id = 1
Waiting_lock_id:1592102:32:3:4
Waiting_lock_mode:x
blocking_trx_id:1592100
Blocking_pid:2
Blocking_query:null
Blocking_lock_id:1592100:32:3:4
Blocking_lock_mode:x
Blocking_trx_started:2018-06-26 11:49:08
blocking_trx_age:00:00:53
Blocking_trx_rows_locked:1
Blocking_trx_rows_modified:1
Sql_kill_blocking_query:kill Query 2
Sql_kill_blocking_connection:kill 2
MySQL finally very intimate even kill SQL statements are produced, you only need to copy, paste can

Careful you will find that through the innodb_lock_waits you can only see the lock statement, but do not see which query statement has the lock, which is why?

Not suspense, because there may be multiple query statements in the transaction that owns the lock, it may have been executed, but there is no commit, so all query statements cannot be given.

What about that? Haha, if you are lucky, you can get the answer according to my case current_statement,last_statement.

In other words, even if you don't find that query, it doesn't prevent you from solving the current problem.

Summarize
MySQL5.7 silently provides a lot of practical tools and new features that require DBAs to dig and explore. By digging into black weapons with seemingly mundane features, you can become the Shining Top5 Mysqler
工欲善其事 its prerequisite

How the MySQL OPS series monitors big business