1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
| select * from information_schema.innodb_trx
show processlist
Warning: Using a password on the command line interface can be insecure.
+------+------+----------------------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+----------------------+------+---------+------+-------+------------------+
| 1367 | root | 192.168.11.186:40366 | NULL | Sleep | 78 | | NULL |
线程号为1367
zabbix:/root/mysql# sh ./mon_mysql_all.sh
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
mysql[192.168.11.185] processid[1367] root@192.168.11.186:40366 in db[DEVOPS] hold transaction time 141
centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"select * from INFORMATION_SCHEMA.INNODB_TRX\G "
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
trx_id: 5451
trx_state: RUNNING
trx_started: 2016-11-22 13:48:24
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 1367
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: 4
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
INNODB_TRX 表包含信息关于每个事务(不包含只读事务) 当前在InnoDB内执行的,
包含交易是否是等待一个lock,当 事务开始后,和SQL 语句正在执行的
INNODB_TRX Columns:
TRX_ID：唯一的事务ID表示,针对于InnoDB内部的(从MySQL5.6开始,那些IDs是不会被创建用于事务 只读的或者是非锁定的)
TRX_WEIGHT：一个事务的权重,反映(但不一定是确切的计数) 影响的行的数量,和被事务锁定的行的数量。
为了解决死锁,InnoDB 选择一个小的权重的事务来回滚。
TRX_STATE 事务执行状态,允许值为RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.
TRX_STARTED： 事务开始时间
centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"show processlist"
Warning: Using a password on the command line interface can be insecure.
+------+------+----------------------+--------+---------+------+----------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+----------------------+--------+---------+------+----------+------------------------------------------+
| 1367 | root | 192.168.11.186:40366 | DEVOPS | Sleep | 46 | | NULL |
| 1404 | root | 192.168.11.186:46149 | DEVOPS | Query | 3 | updating | delete from test where username='admin' |
| 1405 | root | 192.168.11.185:43080 | NULL | Query | 0 | init | show processlist |
+------+------+----------------------+--------+---------+------+----------+------------------------------------------+
1367 持有行锁
1404 被堵塞
centos6.5:/root#mysql -uroot -p'kjk123123' -h192.168.11.185 -e"select * from INFORMATION_SCHEMA.INNODB_TRX\G "
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
trx_id: 5458
trx_state: LOCK WAIT
trx_started: 2016-11-22 14:01:57
trx_requested_lock_id: 5458:14:3:2
trx_wait_started: 2016-11-22 14:01:57
trx_weight: 2
trx_mysql_thread_id: 1404
trx_query: delete from test where username='admin'
trx_operation_state: starting index read
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 1
trx_rows_modified: 0
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
*************************** 2. row ***************************
trx_id: 5453
trx_state: RUNNING
trx_started: 2016-11-22 14:01:14
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 1367
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: 4
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
TRX_REQUESTED_LOCK_ID: 持有者为trx_requested_lock_id: NULL
被堵塞者trx_requested_lock_id: 5458:14:3:2
事务当前等待的锁的ID,如果TRX_STATE 是LOCK WAIT; 否则值为NULL。
得到 lock的详细信息,关联这个列和 INNODB_LOCKS 表的LOCK_ID列
SELECT
NOW(),
(UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(a.trx_started)) diff_sec,
b.id,
b.user,
b.host,
b.db,
c.lock_type,
c.lock_table,
c.lock_index
FROM
information_schema.innodb_trx a
INNER JOIN
information_schema.PROCESSLIST b ON a.TRX_MYSQL_THREAD_ID = b.id
INNER JOIN
information_schema.INNODB_LOCKS c
on a.trx_requested_lock_id=c.lock_id;
INFORMATION_SCHEMA.INNODB_LOCKS 这个表只有在堵塞的时候才有数据
TRX_WAIT_STARTED： 事务开始等待锁的时间,只有是TRX_STATE 是LOCK WAIT; 否则为空
TRX_MYSQL_THREAD_ID: MySQL 事务ID 得到thread 的详细信息,关联这个列和INFORMATION_SCHEMA PROCESSLIST table的ID列。
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;
TRX_QUERY 语句当前事务执行的
TRX_OPERATION_STATE: 事务的当前的操作,如果any 否则NULL
TRX_TABLES_IN_USE InnoDB tables 的数量当前用于处理SQL语句
TRX_TABLES_LOCKED: InnoDB 表的数量
|