MySQL Study之--MySQL體繫結構深入解析，mysql--mysql

MySQL Study之--MySQL體繫結構深入解析，mysql--mysql
MySQL Study之--MySQL體繫結構深入解析

MySQL體系架構

 

    由串連池組件、管理服務和⼯工具組件、sql介面組件、查詢分析器組件、最佳化器組件、緩衝組件、外掛程式式儲存引擎、物理⽂檔案組成。mysql是專屬的外掛程式式體繫結構,各個儲存引擎有自己的特點。

MySQL記憶體結構：

Mysql 進程結構

Mysql不像oracle那樣是通過多進程來完成其功能的。預設情況下，InnoDB儲存引擎的後台線程有7個：

4個IO thread，

1個master thread，

1個鎖（lock）監控線程，

1個錯誤監控線程；

在InnoDB Plugin版本開始增加了預設IO thread的數量，預設的read thread和write thread分別增大到了4個，並且不再使用innodb_file_ io_threads參數，而是分別使用innodb_read_io_threads和innodb_write_io_threads參數。

查看mysql thread：

<strong><span style="font-size:14px;">mysql> show variables like 'innodb_%version%';</span></strong>+----------------+-------+| Variable_name  | Value |+----------------+-------+| innodb_version | 1.2.4 |+----------------+-------+1 row in set (0.00 sec)

<strong><span style="font-size:14px;">mysql> show engine innodb status;</span></strong>=====================================150709 15:26:40 INNODB MONITOR OUTPUT=====================================Per second averages calculated from thelast 12 seconds-----------------BACKGROUND THREAD-----------------srv_master_thread loops: 0 srv_active, 0srv_shutdown, 804 srv_idlesrv_master_thread log flush and writes: 804----------SEMAPHORES----------OS WAIT ARRAY INFO: reservation count 2OS WAIT ARRAY INFO: signal count 2Mutex spin waits 0, rounds 0, OS waits 0RW-shared spins 2, rounds 60, OS waits 2RW-excl spins 0, rounds 0, OS waits 0Spin rounds per wait: 0.00 mutex, 30.00RW-shared, 0.00 RW-excl------------TRANSACTIONS------------Trx id counter 5377Purge done for trx's n:o < 4872 undo n:o< 0History list length 1LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 3, OS thread handle0x7f1656c75700, query id 5 localhost root initshow engine innodb status--------FILE I/O--------<strong>I/O thread 0 state: waiting for completedaio requests (insert buffer thread)I/O thread 1 state: waiting for completedaio requests (log thread)I/O thread 2 state: waiting for completedaio requests (read thread)I/O thread 3 state: waiting for completedaio requests (read thread)I/O thread 4 state: waiting for completedaio requests (read thread)I/O thread 5 state: waiting for completedaio requests (read thread)I/O thread 6 state: waiting for completedaio requests (write thread)I/O thread 7 state: waiting for completedaio requests (write thread)I/O thread 8 state: waiting for completedaio requests (write thread)I/O thread 9 state: waiting for completedaio requests (write thread)Pending normal aio reads: 0 [0, 0, 0, 0] ,aio writes: 0 [0, 0, 0, 0] ,</strong> ibufaio reads: 0, log i/o's: 0, sync i/o's: 0Pending flushes (fsync) log: 0; bufferpool: 0283 OS file reads, 5 OS file writes, 5 OSfsyncs0.00 reads/s, 0 avg bytes/read, 0.00writes/s, 0.00 fsyncs/s-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 0, seg size 2,0 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 276707, node heap has 0buffer(s)0.00 hash searches/s, 0.00 non-hashsearches/s---LOG---Log sequence number 1611537Log flushed up to   1611537Pages flushed up to 1611537Last checkpoint at  16115370 pending log writes, 0 pending chkp writes8 log i/o's done, 0.00 log i/o's/second----------------------BUFFER POOL AND MEMORY----------------------Total memory allocated 137363456; inadditional pool allocated 0Dictionary memory allocated 39010Buffer pool size   8192Free buffers       8040Database pages     152Old database pages 0Modified db pages  0Pending reads 0Pending writes: LRU 0, flush list 0 singlepage 0Pages made young 0, not young 00.00 youngs/s, 0.00 non-youngs/sPages read 152, created 0, written 10.00 reads/s, 0.00 creates/s, 0.00 writes/sNo buffer pool page gets since the lastprintoutPages read ahead 0.00/s, evicted withoutaccess 0.00/s, Random read ahead 0.00/sLRU len: 152, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]--------------ROW OPERATIONS--------------0 queries inside InnoDB, 0 queries in queue0 read views open inside InnoDBMain thread process no. 2461, id139733873489664, state: sleepingNumber of rows inserted 0, updated 0,deleted 0, read 00.00 inserts/s, 0.00 updates/s, 0.00deletes/s, 0.00 reads/s----------------------------END OF INNODB MONITOR OUTPUT

[root@mysrv ~]# mysql -u root -pEnter password:Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.6.4-m7-log Sourcedistribution Copyright (c) 2000, 2011, Oracle and/or itsaffiliates. All rights reserved. Oracle is a registered trademark of OracleCorporation and/or itsaffiliates. Other names may be trademarksof their respectiveowners. Type 'help;' or '\h' for help. Type '\c' toclear the current input statement. <strong><span style="font-size:14px;">mysql> show variables like'innodb_%io%';</span></strong>+---------------------------------+---------+| Variable_name                   | Value   |+---------------------------------+---------+| innodb_additional_mem_pool_size | 8388608|| innodb_io_capacity              | 200     || innodb_read_io_threads          | 4       || innodb_replication_delay        | 0       || innodb_use_native_aio           | ON      || innodb_version                  | 1.2.4   || innodb_write_io_threads         | 4       |+---------------------------------+---------+7 rows in set (0.01 sec)

後台線程主要作用有三個：

1. 重新整理記憶體池中的資料，保證緩衝池中的記憶體緩衝的是最近的資料

2. 將修改的資料檔案重新整理到磁碟檔案

3. 在資料庫發生異常情況下，保證InnoDB能恢複到正常運行狀態

    master thread的線程優先順序別最高。其內部由幾個迴圈（loop）組成：主迴圈（loop）、後台迴圈（backgroundloop）、重新整理迴圈（flush loop）、暫停迴圈（suspend loop）。master thread會根據資料庫啟動並執行狀態在loop、background loop、 flush loop和suspend loop中進行切換。loop稱為主迴圈，因為大多數的操作都在這個迴圈中，其中有兩大部分操作：每秒鐘的操作和每10秒的操作.

    Loop為主迴圈，如果沒有使用者活動，就切換到backgroundloop中，然後跳轉會loop，如果還需要flush重新整理，那就轉到flush loop，如果在flush loop中也空閑，那就切換到suspend loop中，將master thread掛起等待事件的發生。

 

Mysql 進程結構

Loop：

每秒一次的操作包括：

   日誌緩衝重新整理到磁碟，即使這個事務還沒有提交（總是）。

   合并插入緩衝（可能）。

   至多重新整理100個InnoDB的緩衝池中的髒頁到磁碟（可能）。

   如果當前沒有使用者活動，切換到background loop（可能）。

接著來看每10秒的操作，包括如下內容：

   重新整理100個髒頁到磁碟（可能）。

   合并至多5個插入緩衝（總是）。

   將日誌緩衝重新整理到磁碟（總是）。

   刪除無用的Undo頁（總是）。

   重新整理100個或者10%髒頁到磁碟（總是）。

   產生一個檢查點（總是）。

Background loop會執行以下操作

   刪除無用的Undo頁（總是）。

   合并20個插入緩衝（總是）。

   跳回到主迴圈（總是）。

   不斷重新整理100個頁，直到符合條件（可能，跳轉到flush loop中完成）。

Mysql進程結構--控制磁碟io的參數

  innodb_io_capacity

  用來表示磁碟IO的輸送量，預設值為200。對於重新整理到磁碟的數量（髒緩衝），會按照innodb_io_capacity的百分比來重新整理相對數量的頁

  在合并插入緩衝時，合并插入緩衝的數量為innodb_io_capacity數值的5%。

  在從緩衝區重新整理髒頁時，重新整理髒頁的數量為innodb_io_capacity。

  innodb_max_dirty_pages_pct

    預設值為75%。加快髒頁重新整理頻率，減少恢復，也可保證磁碟IO負載。

  innodb_adaptive_flushing：

  在innodbplugin中。該值影響每1秒重新整理髒頁的數量。原來的重新整理規則是：如果髒頁在緩衝池所佔的比例小於innodb_max_dirty_pages_pct時，不重新整理髒頁。大於innodb_max_dirty_pages_pct時，重新整理100個髒頁，而innodb_adaptive_flushing參數的引入，InnoDB儲存引擎會通過一個名為buf_flush_get_desired_flush_rate的函數判斷產生重做日誌的速度來判斷最合適的重新整理髒頁的數量。因此，當髒頁的比例小於innodb_max_dirty_pages_pct時，也會重新整理一定量的髒頁。

  還有兩個和mysql啟動和關閉有關的參數

  innodb_fast_shutdown

  這個參數影響著innodb表的行為，該參數可設定為0，1，2

  0: 表示當MySQL關閉時，InnoDB需要完成所有的full purge和merge insert buffer操作。耗時

     比較長。

  1: 是預設值。表示不需要完成full purge和merge insert buffer操作，但是在緩衝池中的一

     些資料髒頁會重新整理到磁碟。

  2: 表示不完成full purge和merge insert buffer操作，也不將緩衝池中的資料髒頁寫回到磁

     盤，而是將日誌寫入到記錄檔。MySQL下次啟動時，會執行恢複操作。

  innodb_force_recovery

      影響InnoDB的恢複狀況。預設為0，表示需恢複時執行所有的恢複操作。若不能有效恢複，則MySQL有可能宕機，錯誤資訊會被寫入錯誤記錄檔檔案。還有 1~6等不同值，根據

  需要可以設定。

  0 :正常的關閉和啟動，不會做任何強迫恢複操作；

  1 :跳過錯誤頁，讓mysqld服務繼續運行。跳過錯誤索引記錄和儲存頁，可以執行備份操作

  2 :阻止InnoDB的主線程運行。清理操作時出現mysqld服務崩潰，則會阻止資料恢複操作；

  3 :恢複的時候，不進行交易回復；

  4 :阻止INSERT緩衝區的合併作業。不做合併作業，為防止出現mysqld服務崩潰。不計算表的統計資訊

  5 :mysqld服務啟動的時候不檢查復原日誌：InnoDB引擎對待每個不確定的事務就像提交的事務一樣；

  6 :不做交易記錄前滾恢複操作；

 

Mysql記憶體結構—buffer pool

    InnoDB儲存引擎記憶體主要由幾個部分組成：緩衝池（bufferpool）、重做日誌緩衝池（redo log buffer）以及額外的記憶體池（additional memory pool）

緩衝池是占記憶體最大的部分，用來存放各種資料的緩衝。因為InnoDB的儲存引擎的工作方式總是將資料庫檔案按頁（每頁16K）讀取到緩衝池，然後按最近最少使用（LRU）的演算法來保留在緩衝池中的快取資料。如果資料庫檔案需要修改，總是首先修改在緩衝池中的頁（發生修改後，該頁即為髒頁），然後再按照一定的頻率將緩衝池的髒頁重新整理（flush）到檔案。

buffer pool是通過三種list來管理的：

1) free list

2) lru list

3) flush list

buffer pool中的最小單位是page，在innodb中定義三種page

1) free page :此page未被使用，此種類型page位於free鏈表中

2) clean page:此page被使用，對應資料檔案中的一個頁面，但是頁面沒有被修改，此種類型

  page位於lru鏈表中

3) dirty page:此page被使用，對應資料檔案中的一個頁面，但是頁面被修改過，此種類型page

   位於lru鏈表和flush鏈表中

 

插入緩衝：

      不是緩衝池的一部分，InsertBuffer是物理頁的一個組成部分,它帶來InnoDB效能的提高。根據B+演算法(下文會提到)的特點，插入資料的時候會主鍵索引是順序的，不會造成資料庫的隨機讀取，而對於非叢集索引(即輔助索引)，葉子節點的插入不再是順序的了，這時需要離散地訪問非叢集索引，插入效能在這裡變低了。InnoDB引入插入緩衝，判斷非叢集索引頁是否在緩衝池中，如果在則直接插入;不在，則先放在插入緩衝區中。然後根據上述master thread中介紹的，會有一定的頻率將插入緩衝合并。此外，輔助索引不能是唯一的，因為插入到插入緩衝時，並不去尋找索引頁的情況，否則仍然會造成隨機讀，失去插入緩衝的意義了。插入緩衝可能會佔緩衝池中記憶體，預設也能會佔到1/2，所以可以將這個值調小點，到1/3。通過IBUF_POOL_SIZE_PER_MAX_SIZE來設定，2表示1/2,3表示1/3。

兩次寫： 

     它帶來InnoDB資料的可靠性。如果寫失效，可以通過重做日誌進行恢複，但是重做日誌中記錄的是對頁的物理操作，如果頁本身損壞，再對其進行重做是沒有意義的。所以，在應用重做日誌前，需要一個頁的副本，當寫入失效發生時，先通過頁的副本來還原該頁，再進行重做，這就是doublewire。
恢複資料=頁副本+重做日誌 

 

Mysql記憶體結構—log buffer與管理池

日誌緩衝：

     日誌緩衝將重做日誌資訊先放入這個緩衝區，然後按一定頻率將其重新整理到重做記錄檔。該值一般不需要設定為很大，因為一般情況下每一秒鐘就會將重做日誌緩衝重新整理到記錄檔，因此我們只需要保證每秒產生的事務量在這個緩衝大小之內即可。

額外的記憶體池：

      額外的記憶體池同樣十分重要。在InnoDB儲存引擎中，對記憶體的管理是通過一種稱為記憶體堆（heap）的方式進行的，他是用於緩衝InnoDB引擎的資料字典資訊和內部資料結構，在對一些資料結構本身分配記憶體時，需要從額外的記憶體池中申請，當該地區的記憶體不夠時，會從緩衝池中申請，若是mysqld服務上的表對象數量較多，InnoDB引擎資料量很大，且innodb_buffer_pool_size的值設定較大，則應該適當地調整innodb_additional_mem_pool_size的值。若是出現緩衝區的記憶體不足，則會直接向作業系統申請記憶體配置，並且會向MySQL的error log檔案寫入警告資訊。

binlog和redolog區別：

1) binary log,會記錄所有與MySQL有關的日誌記錄,包括 Innodb、MyISAM、Heap等其他儲存引

    擎的日誌而 innodb儲存引擎的重做日誌(redolog),只記錄有關其本身的交易記錄   

   
2) 記錄內容不同：
      binary log:不管你將二進位記錄檔的記錄格式設定為STATEMENT，還是ROW,又或是

                 MIXED, 其記錄的都是關於一個事務的具體操作內容.
      redo log:  記錄innodb 每個頁的更改的物理情況

3）寫入時間不同：
      binary log：事務提交前進行記錄
      redo log ：事物進行過程中，不斷寫入。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。