一，後台進程
INNODB存儲引擎 由4個I/O線程，1個master線程,1個鎖監控線程，以1個錯誤監控線程。
下面說明innodb_file_io_threads參數值為8，系統默認值為4，實際表明在linux下修改innodb_file_io_threads參數值無效。在innodb plugin中，不在使用innodb_file_io_threads參數，而使用innodb_read_io_threads and innodb_write_io_threads 兩個值代替。 www.2cto.com
//mysql5.1.50
root@test 17:54>select version();
+------------+
| version() |
+------------+
| 5.1.50-log |
+------------+
root@test 17:54>show variables like 'innodb_file_io_threads';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| innodb_file_io_threads | 8 |
+------------------------+-------+
//查看引擎狀態
root@test 17:56>show engine innodb status\G;
FILE顯示的關於IO線程部分
--------
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, www.2cto.com
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
323 OS file reads, 165433 OS file writes, 150609 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 6.20 writes/s, 3.40 fsyncs/s
-------------------------------------
//mysql5.5文件I/O如下
mysql> select version();
+------------+
| version() |
+------------+
| 5.5.21-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_version';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| innodb_version | 1.1.8 |
+----------------+-------+
FILE顯示的關於mysql5.5 IO線程部分, 有四個讀線程和四個寫線程，一個插入線程和一個日志線程
--------
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 (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 www.2cto.com
Pending flushes (fsync) log: 0; buffer pool: 0
478 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
-------------------------------------
二，內存
INNODB內存由三部分組成：緩沖池(buffer pool),重做日志緩沖池(redo log buffer)和額外的內存池(additional memory pool)
//緩沖池(buffer pool)，2GB
root@test 18:13>show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 2147483648 |
+-------------------------+------------+
1 row in set (0.00 sec)
// 重做日志緩沖池(redo log buffer)，16MB
root@test 18:13>show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name | Value |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)
//額外的內存池(additional memory pool)，32MB,在innodb中默認值為1MB，innodb plugin默認值為8MB.用於存儲數據字典和內部數據結構。
root@test 18:14>show variables like 'innodb_additional_mem_pool_size';
+---------------------------------+----------+
| Variable_name | Value |
+---------------------------------+----------+
| innodb_additional_mem_pool_size | 33554432 |
+---------------------------------+----------+
1 row in set (0.00 sec) www.2cto.com
有上可見，數據緩沖池站內存塊絕大部分。
關於數據緩沖池(innodb_buffer_pool)包括：數據頁(data page),索引頁(index page),undo頁(undo page),插入緩沖(insert buffer),自適應哈希索引(adaptive hash index),鎖信息(lock info),數據字典(data dictionary)。
三、關於innodb的master thread線程
在mysql5.1中如果沒有系統編譯的innodb plugin插件引擎。系統默認的innodb引擎，主要的工作都有由一個master thread線程來完成。在innodb plugin引擎中，有線程池來完成，但是在mysql5.5社區版，是沒有該功能，在mysql官方文檔說線程池的使用在商業版可以用。
每秒都會操作的內容：
1,日志緩沖刷新到磁盤，即使這個事務還沒有提交，這種設計導致很大的事務提交(commit)時也會很快。
2,合並插入緩沖,在判斷I/O次數少於5次時，可以執行插入緩沖操作。
3，INNODB存儲引擎最多每次只會刷新100個髒頁到磁盤，每秒是否刷新取決於髒頁的比例，如果超過innodb_max_dirty_pages_pct設置的值，就會將100個髒頁刷入文件。
root@(none) 22:46>show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_max_dirty_pages_pct | 60 |
+----------------------------+-------+
innodb存儲引擎的邏輯存儲結構，默認情況下存放砸ibdata1空間中稱之為表空間；當定義innodb_file_per_table時，存放在“表名”.idb中，包括數據，索引和插入緩沖；undo文件，系統事物信息和二次寫緩沖任然保存在ibdata1中。 www.2cto.com
表空間由段(segment),區(extent),頁(page)組成.
segment由數據段，索引段，回滾段組成。
extent由64個連續的頁組成，每頁大小為16KB，即大小為1MB.
page(頁)有數據頁(b-tree page),undo頁(undo page), 系統頁(system page),事物數據頁(transaction system page),插入緩沖位圖頁(insert buffer bitmap),插入緩沖空閒列表頁(insert buffer free list),未壓縮的二進制大對象頁(uncompressed blob page),壓縮的二進制大對象頁(compressed blob page)
作者 alang85
