mysql 基础 show innodb status

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 显示了读取，更新和删除的行的信息，最后一行显示了执行速度。