show innodb status 总结:
=====================================
120809 17:04:58 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 23 seconds
说明MONITOR OUTPUT 是一段时间的平均值
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4, signal count 4
Mutex spin waits 0, rounds 21, OS waits 1
RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 1
说明:innodb 有个多阶段等待策略,首先会试着对锁进行循环等待,如果经过预设的循环次数innodb_sync_spin_lopps后没有获得临界区,那么就会退出到系统的等待阵列里面。然后
伺机进入innodb inside 里面。
reservation count" 和 "signal count" 显示了 innodb 使用内部同步阵列的活跃程度及线程信号使用同步阵列的频繁程度,暂时对这两个概念很模糊。
Mutex spin waits 0 自旋锁等待线程数量
rounds 21 自旋锁回转线程数量
OS waits 1 os 系统同步阵列等待线程数量
------------
TRANSACTIONS
------------
Trx id counter 0 1280
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 27926, OS thread id 1168947552
MySQL thread id 3, query id 17 localhost root
show innodb status
Trx id counter 0 1280 事务id,创建事务时创建。
Purge done for trx's n:o < 0 0 undo n:o < 0 0 是指净化(purge)线程已经完成的事务数,undo n:o" 显示了净化线程当前正在处理的回滚日志号。
History list length 0 指回滚空间的事务数,暂且理解为回滚空间。
Total number of lock structs in row lock hash table 是指事务分配过行锁结构总数
----开始直到后面试一个具体的线程的状态,线程id ,执行的文本信息等。
MySQL thread id 3 mysql 进程id
query id 17 内部查询信息
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
25 OS file reads, 3 OS file writes, 3 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
插入缓冲合并,异步日志刷新,预读以及刷新脏数据4个线程
Pending normal aio reads: 0, aio writes: 0, 表示正在判定的平均读写io次数。
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 表示在 insert buffer 中的平均读,日志同步i/o,及同步次数
Pending flushes (fsync) log: 0; buffer pool: 0 表示 待决的刷新日子的大小 和buffer 大小
25 OS file reads, 3 OS file writes, 3 OS fsyncs 表示系统中多少个文件在写,读,同步数据
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s msyql读,写,同步的平均大小
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 44233
Log flushed up to 0 44233
Last checkpoint at 0 44233
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
显示insert buffer 和 adapitave hash index 的信息
Ibuf: size 1, free list len 0, seg size 2, ibuf 大小和剩余大小及段大小
0 inserts, 0 merged recs, 0 merges 插入ib 多少,合并了多少
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20358952; in additional pool allocated 653312
Dictionary memory allocated 33320
Buffer pool size 512
Free buffers 493
Database pages 19
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 19, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Innodb分配的所有内存,以及额外的内存池分配情况,缓冲池总共有多少个内存页,有多少空闲内存页,
数据库分配了多少个内存页以及有多少个脏内存页
Pending writes: LRU 0 说明缓存采用的指环策略是LRU。
Pages read 19, created 0, written 0 显示innodb已经读,写和创建了多少页。
下面行显示的读,写,创建的速度。
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 22962, id 1168148832, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
0 queries inside InnoDB, 0 queries in queue 是innodb内核里面的线程数以及在等待队列中的线程数,因为innnodb对限制了并发线程的数目。
1 read views open inside InnoDB 显示已经在内核中打开了多少个读视图。
Main thread process no. 22962, id 1168148832, state: waiting for server activity 显示innodb中主线程的状态.
Number of rows inserted 0, updated 0, deleted 0, read 0 显示了读取,更新和删除的行的信息,最后一行显示了执行速度。
=====================================
120809 17:04:58 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 23 seconds
说明MONITOR OUTPUT 是一段时间的平均值
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4, signal count 4
Mutex spin waits 0, rounds 21, OS waits 1
RW-shared spins 4, OS waits 2; RW-excl spins 1, OS waits 1
说明:innodb 有个多阶段等待策略,首先会试着对锁进行循环等待,如果经过预设的循环次数innodb_sync_spin_lopps后没有获得临界区,那么就会退出到系统的等待阵列里面。然后
伺机进入innodb inside 里面。
reservation count" 和 "signal count" 显示了 innodb 使用内部同步阵列的活跃程度及线程信号使用同步阵列的频繁程度,暂时对这两个概念很模糊。
Mutex spin waits 0 自旋锁等待线程数量
rounds 21 自旋锁回转线程数量
OS waits 1 os 系统同步阵列等待线程数量
------------
TRANSACTIONS
------------
Trx id counter 0 1280
Purge done for trx's n:o < 0 0 undo n:o < 0 0
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 27926, OS thread id 1168947552
MySQL thread id 3, query id 17 localhost root
show innodb status
Trx id counter 0 1280 事务id,创建事务时创建。
Purge done for trx's n:o < 0 0 undo n:o < 0 0 是指净化(purge)线程已经完成的事务数,undo n:o" 显示了净化线程当前正在处理的回滚日志号。
History list length 0 指回滚空间的事务数,暂且理解为回滚空间。
Total number of lock structs in row lock hash table 是指事务分配过行锁结构总数
----开始直到后面试一个具体的线程的状态,线程id ,执行的文本信息等。
MySQL thread id 3 mysql 进程id
query id 17 内部查询信息
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
25 OS file reads, 3 OS file writes, 3 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
插入缓冲合并,异步日志刷新,预读以及刷新脏数据4个线程
Pending normal aio reads: 0, aio writes: 0, 表示正在判定的平均读写io次数。
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 表示在 insert buffer 中的平均读,日志同步i/o,及同步次数
Pending flushes (fsync) log: 0; buffer pool: 0 表示 待决的刷新日子的大小 和buffer 大小
25 OS file reads, 3 OS file writes, 3 OS fsyncs 表示系统中多少个文件在写,读,同步数据
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s msyql读,写,同步的平均大小
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 44233
Log flushed up to 0 44233
Last checkpoint at 0 44233
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.00 log i/o's/second
----------------------
显示insert buffer 和 adapitave hash index 的信息
Ibuf: size 1, free list len 0, seg size 2, ibuf 大小和剩余大小及段大小
0 inserts, 0 merged recs, 0 merges 插入ib 多少,合并了多少
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 20358952; in additional pool allocated 653312
Dictionary memory allocated 33320
Buffer pool size 512
Free buffers 493
Database pages 19
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 19, created 0, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Innodb分配的所有内存,以及额外的内存池分配情况,缓冲池总共有多少个内存页,有多少空闲内存页,
数据库分配了多少个内存页以及有多少个脏内存页
Pending writes: LRU 0 说明缓存采用的指环策略是LRU。
Pages read 19, created 0, written 0 显示innodb已经读,写和创建了多少页。
下面行显示的读,写,创建的速度。
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 22962, id 1168148832, state: waiting for server activity
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
0 queries inside InnoDB, 0 queries in queue 是innodb内核里面的线程数以及在等待队列中的线程数,因为innnodb对限制了并发线程的数目。
1 read views open inside InnoDB 显示已经在内核中打开了多少个读视图。
Main thread process no. 22962, id 1168148832, state: waiting for server activity 显示innodb中主线程的状态.
Number of rows inserted 0, updated 0, deleted 0, read 0 显示了读取,更新和删除的行的信息,最后一行显示了执行速度。