|
从版本 3.23.42 开始，InnoDB 中就包含了 InnoDB Monitors，它可以显示出 InnoDB 的内部状态。从版本 3.23.52 和 4.0.3 开始，你可以使用一个新的 SQL 命令
SHOW INNODB STATUS
来读取标准 InnoDB Monitor 给 SQL client 的输出信息。这些信息对性能调整有益。
另外一个使用 InnoDB Monitors 方法就是让它在服务程序 mysqld 的标准输出上持续地写出信息。当开关打开时，InnoDB Monitors 大约每 15 秒显示一次数据(注意：MySQL 的客户端并不会显示任何东西)。一个简单地使用它的方法就是以一个命令行方式执行 mysqld 。否则输出将会定向到 MySQL 服务错误日志(error log file)中 'yourhostname'.err (在 Windows 下为 mysql.err)，在 Windows 系统中必须在 MS-DOS 使用提示符下以 --console 选项运行 mysqld-max 来指令信息输出在命令提示符窗口上。
显示的信息包含下列信息：
每一个活动的事务(active transaction)保持的表和记录锁定
事务的锁等待 (lock waits of a transactions)
线程的信号量等待 (semaphore waits of threads)
文件 I/O 的等待请求 (pending file i/o requests)
缓冲池(buffer pool)的统计信息
InnoDB 主线程的 purge buffer 和 insert buffer 归并活动(merge activity)
通过下列的 SQL 命令，可以使标准的 InnoDB Monitor 记录到标准的 mysqld 的输出上：
CREATE TABLE innodb_monitor(a int) type = innodb;
通过它来停止：
DROP TABLE innodb_monitor;
CREATE TABLE 句法只不过是为了通过 MySQL SQL 语法分析而提供给 InnoDB 引擎命令的一种方式：那个被创建的表根本与 InnoDB Monitor 无任何关系。如果你在监视器运行着的状态下关闭数据库，并且你需要再次启动监视器， 那么你不得不在发出一个新的 CREATE TABLE 来启动监视器之前先移除(drop)这个表。
与之相类似的，你可以启动 innodb_lock_monitor ，它在某些方面与 innodb_monitor 一致，但是它会显示更多的锁定信息。一个单独的 innodb_tablespace_monitor 将显示在现有表空间内所建立的文件段列表以及可以分配数据结构的有效表空间。从 3.23.44 开始，提供了 innodb_table_monitor ，通过它可以获得 InnoDB 内部数据字典的信息。
3.23.52 中 InnoDB 输出的示例：
=====================================
020805 22:07:41 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 194, signal count 193
--Thread 7176 has waited at ../include/btr0btr.ic line 28 for 0.00 seconds the s
emaphore:
X-lock on RW-latch at 44d980bc created in file buf0buf.c line 354
a writer (thread id 7176) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1
Last time read locked in file ../include/btr0btr.ic line 28
Last time write locked in file ../include/btr0btr.ic line 28
Mutex spin waits 0, rounds 0, OS waits 0
RW-shared spins 77, OS waits 33; RW-excl spins 188, OS waits 161
------------
TRANSACTIONS
------------
Trx id counter 0 657853517
Purge done for trx's n:o < 0 657853429 undo n:o < 0 80
Total number of lock structs in row lock hash table 22
020805 22:07:36 LATEST DETECTED DEADLOCK:
*** (1) TRANSACTION:
TRANSACTION 0 657853503, ACTIVE 0 sec, OS thread id 15373 inserting
LOCK WAIT 3 lock struct(s), heap size 336
MySQL thread id 6, query id 3741 localhost heikki update
insert into ibtest11b (D, B, C) values (5, 'khdkkkk' ,'khdkkkk')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 104865 n bits 208 table test/ibtest11b index PRI
MARY trx id 0 657853503 lock_mode X waiting
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc
supremum.;;
*** (2) TRANSACTION:
TRANSACTION 0 657853500, ACTIVE 0 sec, OS thread id 11275 setting auto-inc lock
19 lock struct(s), heap size 2672, undo log entries 5
MySQL thread id 2, query id 3750 localhost heikki update
insert into ibtest11b (D, B, C) values (5, 'khD' ,'khD')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 104865 n bits 200 table test/ibtest11b index PRI
MARY trx id 0 657853500 lock_mode X
Record lock, heap no 1 RECORD: info bits 0 0: len 9; hex 73757072656d756d00; asc
supremum.;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table test/ibtest11b trx id 0 657853500 lock_mode AUTO-INC waiting
*** WE ROLL BACK TRANSACTION (2)
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 657853516, ACTIVE 5 sec, OS thread id 15373 setting auto-inc lo
ck
LOCK WAIT 1 lock struct(s), heap size 336
MySQL thread id 6, query id 3895 localhost heikki update
insert into ibtest11b (D, B, C) values (5, 'khdkkkk' ,'khdkkkk')
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table test/ibtest11b trx id 0 657853516 lock_mode AUTO-INC waiting
------------------
---TRANSACTION 0 657853514, ACTIVE 5 sec, OS thread id 11275 inserting
LOCK WAIT 13 lock struct(s), heap size 2672, undo log entries 2
MySQL thread id 2, query id 3898 localhost heikki update
insert into ibtest11d (D, B, C) values (5, 'khdkkkk' ,'khdkkkk')
------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 104879 n bits 384 table test/ibtest11d index B t
rx id 0 657853514 lock_mode X gap type lock waiting
Record lock, heap no 130 RECORD: info bits 32 0: len 9; hex 6b48646b6b6b6b6b6b;
asc kHdkkkkkk;; 1:
------------------
---TRANSACTION 0 657853512, ACTIVE 5 sec, OS thread id 14348 updating or deletin
g
20 lock struct(s), heap size 2672, undo log entries 175
MySQL thread id 5, query id 3874 localhost heikki updating
delete from ibtest11a where A = 215
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request
I/O thread 1 state: waiting for i/o request
I/O thread 2 state: waiting for i/o request
I/O thread 3 state: waiting for i/o request
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
272 OS file reads, 56 OS file writes, 29 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 5, seg size 7,
0 inserts, 0 merged recs, 0 merges
Hash table size 124633, used cells 1530, node heap has 4 buffer(s)
2895.70 hash searches/s, 126.62 non-hash searches/s
---
LOG
---
Log sequence number 19 3267291494
Log flushed up to 19 3267283711
Last checkpoint at 19 3266545677
0 pending log writes, 0 pending chkp writes
30 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 82593970; in additional pool allocated 1406336
Buffer pool size 1920
Free buffers 1711
Database pages 205
Modified db pages 39
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 178, created 27, written 50
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 0 queries in queue; main thread: purging
Number of rows inserted 2008, updated 264, deleted 162, read 9
0.00 inserts/s, 0.00 updates/s, 14.66 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
输出信息的某些注意点：
如果 TRANSACTIONS 部分报告锁定等待(lock waits)，那么你的应用程序可能有锁争用(lock contention)。输出信息可以帮助跟踪事务死锁的原因。
SEMAPHORES 部分报告线程等待信号量以及统计出线程需要旋转(spin)或等待(wait)一个互斥(mutex)或 rw-lock 信号量的次数。一个较大的线程等待信号量的次数可能是由于磁盘 I/O 引起，或 InnoDB 内部的争用问题(contention problems)。争用(Contention)可能是由于比较繁重的并发性查询，或操作系统的线程调度的问题。 在这种情形下，可将 innodb_thread_concurrency 设置地小于默认的 8 。
FILE I/O 部分列出了文件 I/O 的等待请求。过大的值就意味着磁盘 I/O 瓶颈。
BUFFER POOL AND MEMORY 部分给出了页面读写的统计。通过这些值可以计算出你的查询通常所需的数据文件 I/O 量。
|
