MySQL DBA
分类： Mysql/postgreSQL
2013-03-07 13:10:15
1. standard stutas #
2. lock status
3. tablespace status
4. table status
详见：http://dev.mysql.com/doc/refman/5.1/en/innodb-monitors.html
standard status
show innodb status \G 输出结果：
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
111230 11:42:41 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 3 seconds
----------
SEMAPHORES
--------
OS WAIT ARRAY INFO: reservation count 1528, signal count 1528
Mutex spin waits 0, rounds 10617, OS waits 88
RW-shared spins 2880, OS waits 1439; RW-excl spins 3, OS waits 1
------------
TRANSACTIONS
------------
Trx id counter 0 78047411
Purge done for trx's n:o < 0 78044473 undo n:o < 0 0
History list length 10
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 7358, OS thread id 1200228672
MySQL thread id 1957, query id 246600 localhost root
show innodb status
---TRANSACTION 0 0, not started, process no 7358, OS thread id 1196235072
MySQL thread id 1944, query id 243042 192.168.200.18 user
---TRANSACTION 0 78047410, not started, process no 7358, OS thread id 1186117952
MySQL thread id 1879, query id 246552 122.115.32.91 user
---TRANSACTION 0 78047407, not started, process no 7358, OS thread id 1186384192
MySQL thread id 7, query id 243181 219.232.61.157 user
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
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
3818 OS file reads, 16567 OS file writes, 5507 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: size 1, free list len 0, seg size 2,
287 inserts, 287 merged recs, 70 merges
Hash table size 8850487, node heap has 1248 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 3 2478272132
Log flushed up to 3 2478272132
Last checkpoint at 3 2478272132
0 pending log writes, 0 pending chkp writes
2802 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4748206808; in additional pool allocated 14758656
Dictionary memory allocated 2398936
Buffer pool size 262144
Free buffers 249905
Database pages 10991
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10789, created 202, written 13888
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 7358, id 1185052992, state: waiting for server activity
Number of rows inserted 8746, updated 0, deleted 0, read 1817326
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
各部分说明：
BACKGROUND THREAD 后台进程 只在innodb plugin中显示
SEMAPHORES 线程等待互斥锁或独占锁的次数统计。磁盘IO性能和大量的并发操作会出现较大数值。innodb_thread_concurrency参数调节innodb的并发数，一般定义为cpu*2。从5.1.12开始，可以设置为0（默认）即innodb会创建足够多的并发来满足并发访问。
LATEST FOREIGN KEY ERROR
最近出现的外键错误，如果有外键的错误信息，这一列将列出详细的相关信息，如相关的表、索引，sql语句等。
LATEST DETECTED DEADLOCK
最近的死锁信息
TRANSACTIONS 事务信息，显示锁的等待情况，分析死锁时用的上
FILE I/O io信息。 显示innodb的各线程的状态，后半部分显示被挂起的线程信息和io使用统计
INSERT BUFFER AND ADAPTIVE HASH INDEX
前半部分，列出insert buffer的使用情况，后面为自适应hash 索引使用情况
BUFFER POOL AND MEMORY 缓存 详细显示缓存的配置和使用情况
Total memory allocated 4748206808; 总缓存4.7G
in additional pool allocated 14758656 额外缓存14.7M，比设置的小
Dictionary memory allocated 2398936 系统字典内存2M
Buffer pool size 262144 主缓存
Free buffers 249905 空缓存
Database pages 10991 数据页
Modified db pages 0 修改的数据页
Pending reads 0 挂起的sql
Pending writes: LRU 0, flush list 0, single page 0
Pages read 10789, created 202, written 13888 分类统计
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
ROW OPERATIONS 显示当前线程的执行情况，以及各种操作的统计。
