關(guān)于INNODB存儲(chǔ)引擎體系結(jié)構(gòu)簡析_MySQL

bitsCN.com
一，后臺(tái)進(jìn)程INNODB存儲(chǔ)引擎 由4個(gè)I/O線程，1個(gè)master線程,1個(gè)鎖監(jiān)控線程，以1個(gè)錯(cuò)誤監(jiān)控線程。 下面說明innodb_file_io_threads參數(shù)值為8，系統(tǒng)默認(rèn)值為4，實(shí)際表明在linux下修改innodb_file_io_threads參數(shù)值無效。在innodb plugin中，不在使用innodb_file_io_threads參數(shù)，而使用innodb_read_io_threads and innodb_write_io_threads 兩個(gè)值代替。 //mysql5.1.50root@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 |+------------------------+-------+ //查看引擎狀態(tài)root@test 17:56>show engine innodb status/G;FILE顯示的關(guān)于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, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; buffer pool: 0323 OS file reads, 165433 OS file writes, 150609 OS fsyncs0.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顯示的關(guān)于mysql5.5 IO線程部分, 有四個(gè)讀線程和四個(gè)寫線程，一個(gè)插入線程和一個(gè)日志線程--------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 Pending flushes (fsync) log: 0; buffer pool: 0478 OS file reads, 3 OS file writes, 3 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s-------------------------------------二，內(nèi)存 INNODB內(nèi)存由三部分組成：緩沖池(buffer pool),重做日志緩沖池(redo log buffer)和額外的內(nèi)存池(additional memory pool) //緩沖池(buffer pool)，2GBroot@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)，16MBroot@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) //額外的內(nèi)存池(additional memory pool)，32MB,在innodb中默認(rèn)值為1MB，innodb plugin默認(rèn)值為8MB.用于存儲(chǔ)數(shù)據(jù)字典和內(nèi)部數(shù)據(jù)結(jié)構(gòu)。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) 有上可見，數(shù)據(jù)緩沖池站內(nèi)存塊絕大部分。關(guān)于數(shù)據(jù)緩沖池(innodb_buffer_pool)包括：數(shù)據(jù)頁(data page),索引頁(index page),undo頁(undo page),插入緩沖(insert buffer),自適應(yīng)哈希索引(adaptive hash index),鎖信息(lock info),數(shù)據(jù)字典(data dictionary)。 三、關(guān)于innodb的master thread線程 在mysql5.1中如果沒有系統(tǒng)編譯的innodb plugin插件引擎。系統(tǒng)默認(rèn)的innodb引擎，主要的工作都有由一個(gè)master thread線程來完成。在innodb plugin引擎中，有線程池來完成，但是在mysql5.5社區(qū)版，是沒有該功能，在mysql官方文檔說線程池的使用在商業(yè)版可以用。 每秒都會(huì)操作的內(nèi)容：
1,日志緩沖刷新到磁盤，即使這個(gè)事務(wù)還沒有提交，這種設(shè)計(jì)導(dǎo)致很大的事務(wù)提交(commit)時(shí)也會(huì)很快。2,合并插入緩沖,在判斷I/O次數(shù)少于5次時(shí)，可以執(zhí)行插入緩沖操作。3，INNODB存儲(chǔ)引擎最多每次只會(huì)刷新100個(gè)臟頁到磁盤，每秒是否刷新取決于臟頁的比例，如果超過innodb_max_dirty_pages_pct設(shè)置的值，就會(huì)將100個(gè)臟頁刷入文件。root@(none) 22:46>show variables like 'innodb_max_dirty_pages_pct';+----------------------------+-------+| Variable_name | Value |+----------------------------+-------+| innodb_max_dirty_pages_pct | 60 |+----------------------------+-------+ innodb存儲(chǔ)引擎的邏輯存儲(chǔ)結(jié)構(gòu)，默認(rèn)情況下存放砸ibdata1空間中稱之為表空間；當(dāng)定義innodb_file_per_table時(shí)，存放在“表名”.idb中，包括數(shù)據(jù)，索引和插入緩沖；undo文件，系統(tǒng)事物信息和二次寫緩沖任然保存在ibdata1中。 表空間由段(segment),區(qū)(extent),頁(page)組成.segment由數(shù)據(jù)段，索引段，回滾段組成。extent由64個(gè)連續(xù)的頁組成，每頁大小為16KB，即大小為1MB. page(頁)有數(shù)據(jù)頁(b-tree page),undo頁(undo page), 系統(tǒng)頁(system page),事物數(shù)據(jù)頁(transaction system page),插入緩沖位圖頁(insert buffer bitmap),插入緩沖空閑列表頁(insert buffer free list),未壓縮的二進(jìn)制大對(duì)象頁(uncompressed blob page),壓縮的二進(jìn)制大對(duì)象頁(compressed blob page) 作者 alang85 bitsCN.com

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com