mysql 事务监控_mysql 监控长事务

mysql> desc information_schema.innodb_trx

-> ;

+----------------------------+---------------------+------+-----+---------------------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------------------------+---------------------+------+-----+---------------------+-------+

| trx_id | varchar(18) | NO | | | |

| trx_state | varchar(13) | NO | | | |

| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |

| trx_requested_lock_id | varchar(81) | YES | | NULL | |

| trx_wait_started | datetime | YES | | NULL | |

| trx_weight | bigint(21) unsigned | NO | | 0 | |

| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |

| trx_query | varchar(1024) | YES | | NULL | |

| trx_operation_state | varchar(64) | YES | | NULL | |

| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |

| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |

| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |

| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |

| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |

| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |

| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |

| trx_isolation_level | varchar(16) | NO | | | |

| trx_unique_checks | int(1) | NO | | 0 | |

| trx_foreign_key_checks | int(1) | NO | | 0 | |

| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |

| trx_adaptive_hash_latched | int(1) | NO | | 0 | |

| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |

| trx_is_read_only | int(1) | NO | | 0 | |

| trx_autocommit_non_locking | int(1) | NO | | 0 | |

+----------------------------+---------------------+------+-----+---------------------+-------+

24 rows in set (0.00 sec)

mysql> select now(),trx_started,(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(trx_started)) from information_schema.innodb_trx;

+---------------------+---------------------+-------------------------------------------------------+

| now() | trx_started | (UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(trx_started)) |

+---------------------+---------------------+-------------------------------------------------------+

| 2016-10-21 09:39:31 | 2016-10-21 09:38:34 | 57 |

+---------------------+---------------------+-------------------------------------------------------+

1 row in set (0.00 sec)

mysql -N -uroot -pnewja01 -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(trx_started)) diff_sec from information_schema.innodb_trx;" | while read A B C

do

echo $C

if [ "$C" -gt 20 ]

then

echo "事务持有时间--$C"

fi

done

mysql> select * from information_schema.innodb_trx\G;

*************************** 1. row ***************************

trx_id: 2438309

trx_state: RUNNING

trx_started: 2016-10-21 09:58:26

trx_requested_lock_id: NULL

trx_wait_started: NULL

trx_weight: 3

trx_mysql_thread_id: 48

trx_query: NULL

trx_operation_state: NULL

trx_tables_in_use: 0

trx_tables_locked: 0

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 7

trx_rows_modified: 1

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

1 row in set (0.00 sec)

ERROR:

No query specified

trx_mysql_thread_id: 48

wjdb3:/root# mysql -uroot -p'newja01' -e"show processlist"

Warning: Using a password on the command line interface can be insecure.

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+------+-----------+------+---------+------+-------+------------------+

| 48 | root | localhost | zjzc | Sleep | 205 | | NULL |

| 58 | root | localhost | NULL | Sleep | 141 | | NULL |

| 60 | root | localhost | NULL | Query | 0 | init | show processlist |

+----+------+-----------+------+---------+------+-------+------------------+

wjdb3:/root#

mysql> desc PROCESSLIST;

+---------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------+---------------------+------+-----+---------+-------+

| ID | bigint(21) unsigned | NO | | 0 | |

| USER | varchar(16) | NO | | | |

| HOST | varchar(64) | NO | | | |

| DB | varchar(64) | YES | | NULL | |

| COMMAND | varchar(16) | NO | | | |

| TIME | int(7) | NO | | 0 | |

| STATE | varchar(64) | YES | | NULL | |

| INFO | longtext | YES | | NULL | |

+---------+---------------------+------+-----+---------+-------+

8 rows in set (0.00 sec)

关联Processlist:

TRX_MYSQL_THREAD_ID：

MySQL thread ID. To obtain details about the thread, join this column with the ID column of the INFORMATION_SCHEMA PROCESSLIST table

select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db from information_schema.innodb_trx a inner join information_schema.PROCESSLIST b

on a.TRX_MYSQL_THREAD_ID=b.id;

wjdb3:/root# cat mon_lock.sh

mysql -N -uroot -pnewja01 -e "select now(),(UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,b.id,b.user,b.host,b.db from information_schema.innodb_trx a inner join

information_schema.PROCESSLIST b

on a.TRX_MYSQL_THREAD_ID=b.id;" | while read A B C D E F G

do

echo $C

if [ "$C" -gt 20 ]

then

echo "processid[$D] $E@$F in db[$G] hold transaction time $C"

fi

done

wjdb3:/root# sh ./mon_lock.sh

Warning: Using a password on the command line interface can be insecure.

670

processid[65] root@192.168.32.26:49153 in db[zjzc] hold transaction time 670