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

This article mainly tells you how to find MySQL long-term transactions. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope that this article on how to find MySQL long-term affairs can bring you some practical help.

1. What is a long transaction?

First of all, we need to know what is a long transaction. As the name implies, a transaction that runs for a long time and has not been committed for a long time can be called a big transaction. Such transactions often cause a lot of blocking and lock timeouts, which are easy to cause master-slave delay, so long transactions should be avoided as far as possible.

Next, I will demonstrate how to open a transaction and simulate a long transaction:

# suppose we have a stu_tb table The structure and data are as follows: mysql > show create table stu_tb\ gateway * 1. Row * * Table: stu_tbCreate Table: CREATE TABLE `stu_ tb` (`increment_ id` int (11) NOT NULL AUTO_INCREMENT COMMENT 'self-increasing primary key', `stu_ id`int (11) NOT NULL COMMENT 'student ID' `Name` varchar (20) DEFAULT NULL COMMENT 'student name', `create_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'creation time', `update_ time`timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (`increment_ id`), UNIQUE KEY `uk_stu_ id` (`stu_ id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT=' test student table '1row in set (0.1 sec) mysql > select * from stu_tb +-+ | increment_id | stu_id | stu_name | create_time | update_time | +- -- + | 1 | 1001 | from1 | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 2 | 1002 | dfsfd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 3 | 1003 | fdgfg | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 4 | 1004 | sdfsdf | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | 5 | 1005 | dsfsdg | 2019-09-15 14:27:34 | 2019-09-15 14:27: 34 | | 6 | 1006 | fgd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 7 | 1007 | fgds | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | | 8 | 1008 | dgfsa | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | +- -+ 8 rows in set (0.00 sec) # explicitly start a transaction Begin or start transactionmysql > start transaction is available Query OK, 0 rows affected (0.00 sec) mysql > select * from stu_tb where stu_id = 1006 for update +-+ | increment_id | stu_id | stu_name | create_time | update_time | +- -- + | 6 | 1006 | fgd | 2019-09-15 14:27:34 | 2019-09-15 14:27:34 | +-- -+ 1 row in set (0.01 sec) # if we don't commit the last transaction in time Then the transaction becomes a long transaction and waits when other sessions want to manipulate the data. two。 How to find long transactions

When faced with the problem of transaction waiting, the first thing we need to do is to find the transaction that is being executed. The information_schema.INNODB_TRX table contains the information about the current running transactions within the innodb. This table gives the start time of the transaction, and we can get the running time of the transaction with a little calculation.

Mysql > select t.* To_seconds (now ())-to_seconds (t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t\ row * 1. Row * * trx_id: 6168 trx_state: RUNNING Trx_started: 2019-09-16 11:08:27 trx_requested_lock_id: NULL trx_wait_started: NULL trx_weight: 3 trx_mysql_thread_id: 11 trx_query: NULL trx_operation_state: NULL trx_tables_in_use: 0 trx_tables_locked: 1 trx_lock_ Structs: 3 trx_lock_memory_bytes: 1136 trx_rows_locked: 2 trx_rows_modified: 0trx_ concurrency_tickets: 0trx_ isolation_level: REPEATABLE READ trx_unique_checks: 1trx_ foreign_key_checks: 1trx_last_foreign_key_error: NULL trx_adaptive_hash_latched: 0trx_ adaptive_hash_timeout: 0trx_ is_read_only: 0trx_autocommit _ non_locking: 0 idle_time: 170

In the result, the idle_time is computationally generated and is also the duration of the transaction. But the trx_query of the transaction is NUL, which does not mean that the transaction does not execute anything. A transaction may contain multiple SQL, which is no longer displayed if the SQL execution is complete. The current transaction is executing, and innodb does not know whether there is any sql in the transaction and when it will be commit. Therefore, trx_query cannot provide meaningful information.

What if we want to see the SQL that this transaction executed and see if we can kill the long transaction? We can query it with other system tables. The specific query SQL is as follows:

Mysql > select now (), (UNIX_TIMESTAMP (now ())-UNIX_TIMESTAMP (a.trx_started)) diff_sec,b.id,b.user,b.host,b.db D.SQL_TEXT from information_schema.innodb_trx an inner join-> information_schema.PROCESSLIST b-> on a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'-> inner join performance_schema.threads c ON b.id = c.PROCESSLIST_ID-> inner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID +- + | now () | diff_sec | id | user | host | db | SQL_TEXT | +- -+ | 2019-09-16 14:06:26 | 54 | 17 | root | localhost | testdb | select * from stu_tb where stu_id = 1006 for update | +- -+-+

In the above results, diff_sec and the above idle_time have the same meaning, and both represent the number of seconds the transaction lasts. SQL_TEXT indicates the SQL that the transaction has just executed. However, the above statement can only find the SQL executed at the end of the transaction. We know that a transaction may contain multiple SQL, so we want to query which SQL has been executed by this uncommitted transaction, and whether it can be satisfied. The answer is that the combination of events_statements_history system tables can also meet the requirements. The following statement will query all SQL executed by the transaction:

Mysql > SELECT-> ps.id 'PROCESS ID',-> ps.USER,-> ps.HOST,-> esh.EVENT_ID,-> trx.trx_started,-> esh.event_name' EVENT NAME',-> esh.sql_text 'SQL' -> ps.time-> FROM-> PERFORMANCE_SCHEMA.events_statements_history esh-> JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id-> JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id-> LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id-> WHERE-> trx.trx_id IS NOT NULL-> AND ps.USER! = 'SYSTEM_USER'-> ORDER BY-> esh.EVENT_ID +- -- +-+ | PROCESS ID | USER | HOST | EVENT_ID | trx_started | EVENT NAME | SQL | time | +- -+ -+-+ | 20 | root | localhost | 1 | 2019-09-16 14:18:44 | statement/sql/select | select @ @ version_comment limit 1 | 60 | | 20 | root | localhost | 2 | 2019-09-16 14:18:44 | statement/sql/begin | start transaction | | 60 | | 20 | root | localhost | 3 | 2019-09-16 14:18:44 | statement/sql/select | SELECT DATABASE () | 60 | | 20 | root | localhost | 4 | 2019-09-16 14:18:44 | statement/com/Init DB | NULL | | | 60 | | 20 | root | localhost | 5 | 2019-09-16 14:18:44 | statement/sql/show_databases | show databases | 60 | | 20 | root | localhost | 6 | 2019-09-16 14:18:44 | statement/sql/show_tables | show tables | | | 60 | | 20 | root | localhost | 7 | 2019-09-16 14:18:44 | statement/com/Field List | NULL | 60 | | 20 | root | localhost | 8 | 2019-09-16 14:18:44 | statement/com/Field List | NULL | | | 60 | | 20 | root | localhost | 9 | 2019-09-16 14:18:44 | statement/sql/select | select * from stu_tb | 60 | | 20 | root | localhost | 10 | 2019-09-16 14:18:44 | statement/sql/select | select | * from stu_tb where stu_id = 1006 for update | 60 | +-+-- -+

From the above results, we can see all the SQL that the transaction has executed from the beginning to the present. When we query all the relevant information about the transaction, we can determine whether the transaction can be killed or not, so as not to affect other transactions and cause waiting.

With a slight expansion here, long transactions can easily cause blocking or deadlock. Usually, we can first query the sys.innodb_lock_waits view to determine whether there is transaction blocking:

# suppose one transaction executes select * from stu_tb where stu_id = 1006 for update# and another transaction executes update stu_tb set stu_name = 'wang' where stu_id = 1006mysql > select * from sys.innodb_lock_waits\ gateway * 1. Row * * Wait_started: 2019-09-16 14:34:32 wait_age: 00:00:03 wait_age_secs: 3 locked_table: `testdb`.`stu _ tb` locked_index: uk_stu_id locked_type: RECORD waiting_trx_id: 6178 waiting_trx_started : 2019-09-16 14:34:32 waiting_trx_age: 00:00:03 waiting_trx_rows_locked: 1 waiting_trx_rows_modified: 0 waiting_pid: 19 waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006 waiting_lock_id: 6178 waiting_trx_rows_modified 47 waiting_lock_mode : X blocking_trx_id: 6177 blocking_pid: 20 blocking_query: NULL blocking_lock_id: 6177 blocking_query 47 NULL blocking_lock_id 7 blocking_lock_mode: X blocking_trx_started: 2019-09-16 14:18:44 blocking_trx_age: 00:15:51 blocking_trx_rows_locked: 2 blocking_trx_rows_modified: 0sql_kill_blocking_ query: KILL QUERY 20sql_kill_blocking_connection: KILL 20

The above results show the blocked SQL and the type of lock, and more powerfully, the statement to kill the session is also given. However, the SQL blocking session execution was not found. If we want to find out more details, we can use the following statement:

Mysql > SELECT-> tmp.*,-> c.SQL_Text blocking_sql_text,-> p.HOST blocking_host-> FROM-> (- > SELECT-> r.trx_state wating_trx_state,-> r.trx_id waiting_trx_id,-> r.trx_mysql_thread_Id waiting_thread,-> r.trx_query waiting_query -> b.trx_state blocking_trx_state,-> b.trx_id blocking_trx_id,-> b.trx_mysql_thread_id blocking_thread -> b.trx_query blocking_query-> FROM-> information_schema.innodb_lock_waits w-> INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id-> INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id->) tmp,-> information_schema.PROCESSLIST p,-> PERFORMANCE_SCHEMA.events_statements_current c -> PERFORMANCE_SCHEMA.threads t-> WHERE-> tmp.blocking_thread = p.id-> AND t.thread_id = c.THREAD_ID-> AND t.PROCESSLIST_ID = p.id\ gateway * 1. Row * * * wating_trx_state: LOCK WAIT waiting_trx_id: 6180 waiting_thread: 19 waiting_query: update stu_tb set stu_name = 'wang' where stu_id = 1006blocking_trx_state: RUNNING blocking_trx_id: 6177 blocking_thread: 20 blocking_query: NULL blocking_sql_text: select * from stu_tb where stu_id = 1006 for update blocking_host: localhost

The above results are clearer, and we can clearly see the statements executed by the transactions on the blocking side and the blocked side, which helps us to troubleshoot and confirm whether the blocked session can be killed.

3. Chief Supervisor transaction

In real work, we need to monitor long transactions and define a threshold. For example, transactions with an execution time of more than 30 seconds are long transactions, which are required to be recorded and alerted to remind managers to deal with them. The following monitoring script is given. You can refer to it and change it according to your needs:

#! / bin/bash#-# FileName: long_trx.sh# Describe: monitor long transaction# Revision: 1.The Date: Wang/usr/local/mysql/bin/mysql-N-uroot-pxxxxxx-e "select now () (UNIX_TIMESTAMP (now ())-UNIX_TIMESTAMP (a.trx_started)) diff_sec,b.id,b.user,b.host,b.db,d.SQL_TEXT from information_schema.innodb_trx an inner joininformation_schema.PROCESSLIST bon a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'inner join performance_schema.threads c ON b.id = c.PROCESSLIST_IDinner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID | | while read A B C D E F G Hdo if ["$C"-gt 30] then echo $(date + "% Y-%m-%d% H:%M:%S") echo "processid [$D] $in db [$G] hold transaction time $C SQL:$H" fidone > > / tmp/longtransaction.txt

To put it simply, the-gt 30 here means 30 seconds. As long as it exceeds 30 seconds, it is considered to be a long transaction, and can be customized according to the actual needs. Add the script to the scheduled task and execute it.

# query all running transactions and running time select t. Running seconds (now ())-to_seconds (t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t\ G# query transaction details and executed SQLselect now (), (UNIX_TIMESTAMP (now ())-UNIX_TIMESTAMP (a.trx_started)) diff_sec,b.id,b.user,b.host,b.db D.SQL_TEXT from information_schema.innodb_trx an inner join information_schema.PROCESSLIST bon a.TRX_MYSQL_THREAD_ID=b.id and b.command = 'Sleep'inner join performance_schema.threads c ON b.id = c.PROCESSLIST_IDinner join performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID # query all historical SQL records executed by the transaction SELECT ps.id 'PROCESS ID', ps.USER, ps.HOST, esh.EVENT_ID, trx.trx_started, esh.event_name' EVENT NAME', esh.sql_text 'SQL' Ps.time FROM PERFORMANCE_SCHEMA.events_statements_history esh JOIN PERFORMANCE_SCHEMA.threads th ON esh.thread_id = th.thread_id JOIN information_schema.PROCESSLIST ps ON ps.id = th.processlist_id LEFT JOIN information_schema.innodb_trx trx ON trx.trx_mysql_thread_id = ps.id WHERE trx.trx_id IS NOT NULL AND ps.USER! = 'SYSTEM_USER' ORDER BY esh.EVENT_ID # simple query transaction lock select * from sys.innodb_lock_waits\ G # query transaction lock details SELECT tmp.*, c.SQL_Text blocking_sql_text, p.HOST blocking_host FROM (SELECT r.trx_state wating_trx_state, r.trx_id waiting_trx_id, r.trx_mysql_thread_Id waiting_thread, r.trx_query waiting_query, b.trx_state blocking_trx_state B.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id) tmp, information_schema.PROCESSLIST p, PERFORMANCE_SCHEMA.events_statements_current c PERFORMANCE_SCHEMA.threads t WHERE tmp.blocking_thread = p.id AND t.thread_id = c.THREAD_ID

AND t.PROCESSLIST_ID = p.id\ G

How to find MySQL long-term business will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.