4.MySQL效能優化

引數優化--innodb_buffer_pool_size

單例項效能
例項需求
例項標準化

注：原始碼中在innodb儲存引擎層搜尋srv_buf_pool_size（在srv0srv.c、srv0start.c檔案中）。

---

引數優化--innodb_buffer_pool_instances

將innodb_buffer_pool劃分為不同的instance
每個instance獨立的LRU、FLUSH、FREE
獨立的mutex控制

注：在innodb儲存引擎層搜尋srv_buf_pool_instances（主要集中在的buf0buf.c檔案）

---

引數優化--innodb_log_file_size

先寫入innodb_log_buffer
buffer寫滿或事務提交，重新整理資料
大事務頻繁，增加innodb_log_buffer_size大小

注：在innodb儲存引擎層搜尋srv_log_buffer_size（主要在log0log.c檔案中）

---

引數優化--innodb_thread_concurrency

innodb_thread_concurrency = 0，innodb內部自己控制
kernel_mutex競爭
CPU上下文切換
innodb_thread_concurrency設定為cpu的核心數

注：在innodb儲存引擎層搜尋srv_thread_concurrency（主要在srv0srv.c檔案中）

---

引數優化--innodb_io_capacity

innodb每秒後臺程式處理IO操作的資料頁上限
innodb_buffer_pool_size總的io處理能力上限
innodb_buffer_pool_instances分割成多個記憶體塊時，每個記憶體塊的IO處理能力為：innodb_io_capacity/innodb_buffer_pool_instances

注：在innodb儲存引擎層搜尋srv_io_capacity（主要在srv0srv.c檔案中）

---

引數優化--innodb_max_dirty_pages_pct

innodb從innodb buffer中重新整理髒頁的比例
重新整理髒頁，產生checkpoint
髒頁重新整理innodb_max_dirty_pages_pct * innodb_io_capacity

注： 在innodb儲存引擎層搜尋srv_max_buf_pool_modified_pct（主要在srv0srv.c檔案中）

---

引數優化--innodb_flush_method

O_DSYNC：使用O_SYNC開啟和重新整理log檔案，使用fsync()重新整理資料檔案。
O_DIRECT：使用O_DIRECT開啟資料檔案，使用fsync()重新整理日誌檔案和資料檔案。

在raid裝置上，為了避免資料被innodb_buffer和raid多次cache，設定為O_DIRECT方式。

注：在innodb儲存引擎層搜尋srv_unix_file_flush_method（主要在log0log.c、os0file.c檔案中）

---

引數優化--innodb_file_per_table

不同的表空間可以靈活設定資料目錄的地址
避免共享表空間產生的IO競爭

---

引數優化--innodb_flush_log_at_trx_commit

0：每秒將log buffer的內容寫事務日誌並且重新整理到磁碟；
1：每個事務提交後，將log_buffer的內容寫事務日誌並資料磁碟；
2：每個事務提交，將log_buffer內容寫事務日誌，但不進行資料刷盤

原始碼:

 if (trx->flush_log_later) {
  /* Do nothing yet */
  trx->must_flush_log_later = TRUE;
} else if (flush_log_at_trx_commit == 0) {
  /* Do nothing */
} else if (flush_log_at_trx_commit == 1) {
  if (srv_unix_file_flush_method == SRV_UNIX_NOSYNC) {
    /* Write the log but do not flush it to disk */
    log_write_up_to(lsn, LOG_WAIT_ONE_GROUP, FALSE);
  } else {
    /* Write the log to the log files AND flush them to disk */
    log_write_up_to(lsn, LOG_WAIT_ONE_GROUP, TRUE);
  }
} else if (flush_log_at_trx_commit == 2) {
  /* Write the log but do not flush it to disk */
  log_write_up_to(lsn, LOG_WAIT_ONE_GROUP, FALSE);
} else {
  ut_error;
}

---

引數優化--sync_binlog

重新整理binlog的數目
雙1模式，即：innodb_flush_log_at_trx_commit = 1，sync_binlog = 1，這樣主備的資料是一致的，不會丟失資料。

---

系統優化--NUMA

在os層numa關閉時，開啟bios層的numa會影響效能，QPS會下降15-30%；
在bios層面numa關閉是，無論os層面的numa是否開啟，都不會影響效能。

---

系統優化--malloc

1、下載jemalloc原始碼包
wget http://www.canonware.com/download/jemalloc/jemalloc-3.6.0.tar.bz2
tar -xjf jemalloc-3.6.0.tar.bz2
2、編譯安裝
cd jemalloc-3.6.0; ./configure;make &make install
3、配置MySQL
[mysqld_safe]
malloc-lib=$PATH/libjemalloc.so

---

系統優化--網路卡

RSS: Receive Side Scaling。網路卡多佇列，需要硬體支援。網路卡接收到網路資料包之後，要傳送一個硬體中斷，通知CPU取資料包。預設配置，都是由CPU0去做。
RPS: Receive Packet Steering。向某CPU傳送一個軟中斷，來接收資料包，並遞交給應用程式。
RFS: Receive Flow Steering。維護兩張hash表，實現將軟中斷分散到多顆CPU去處理。

結論:
減少CPU之間的cache互動； hash計算精準定位到目標CPU。
CPU空閒時，RT效能優化； CPU忙碌時，RT反而惡化。
RFS的hash表條數對效能有較大影響。

---

系統優化--記憶體插法

6根記憶體在4通道里的插法為：2/2/1/1，簡稱42插法
6根記憶體在4通道里的插法為：2/2/2/0，簡稱33插法
HP/DELL/華為/英業達對比：

• HP/DELL/華為保持42插法效能會比33插法效能高。NUMA開啟，QPS提升8-20%；NUMA關閉，QPS值能提升12-38%。
• 英業達無論何種插法都表現良好

---

參考資料

1、《innodb parameters》
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html
2、《Choosing innodb_buffer_pool_size》
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
3、《The InnoDB Buffer Pool》
http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
4、《Configuring the Rate of InnoDB Buffer Pool Flushing》
http://dev.mysql.com/doc/refman/5.5/en/innodb-performance-adaptive_flushing.html
5、《Optimizing InnoDB Disk I/O》
http://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html
6、《jemalloc》
http://www.canonware.com/jemalloc/
7、《scalable memory allocation using jemalloc》
http://www.facebook.com/notes/facebook-engineering/scalable-memory-allocation-using-jemalloc/480222803919
8、《MySQL performance: Impact of memory allocators》
http://www.mysqlperformanceblog.com/2012/07/05/impact-of-memory-allocators-on-mysql-performance/
9、《MySQL performance: Impact of memory allocators (Part 2)》
http://www.mysqlperformanceblog.com/2013/03/08/mysql-performance-impact-of-memory-allocators-part-2/
10、《MySQL效能測試--jemalloc記憶體管理》
http://blog.chinaunix.net/uid-26896862-id-3865087.html
11、《A High-Performance Nehalem iDataPlex Cluster and DDN S2A9990 Storage for Texas A&M University》
http://sc.tamu.edu/systems/eos/
12、《Non-uniform memory access》
http://en.wikipedia.org/wiki/Non-Uniform_Memory_Access
13、《MySQL單機多例項方案》
http://www.hellodb.net/tag/numa
14、《Introduction to NUMA on xSeries Servers》
http://www.redbooks.ibm.com/abstracts/tips0476.html?Open
15、《Numa對MySQL多例項效能測試報告 》
http://blog.chinaunix.net/uid-26896862-id-3278913.html
16、《MySQL Blob Compression performance benefits》
http://www.mysqlperformanceblog.com/2008/01/11/mysql-blob-compression-performance-benefits/
17、《Scaling in the Linux Networking Stack》
https://www.kernel.org/doc/Documentation/networking/scaling.txt