MYSQL企業常用架構與調優經驗分享

標籤：

一、選擇Percona Server、MariaDB還是MYSQL

 mysql應用源碼：http://www.jinhusns.com/Products/Download/?type=xcj

1、Mysql三種儲存引擎

 

MySQL提供了兩種儲存引擎：MyISAM和 InnoDB，MySQL4和5使用預設的MyISAM儲存引擎。從MYSQL5.5開始，MySQL已將預設儲存引擎從MyISAM更改為InnoDB。

MyISAM沒有提供事務支援，而InnoDB提供了事務支援。

 

XtraDB是InnoDB儲存引擎的增強版本，被設計用來更好的使用更新電腦硬體系統的效能，同時還包含有一些在高效能環境下的新特性。

2、Percona  Server分支

 

Percona Server由領先的MySQL諮詢公司Percona發布。

Percona Server是一款獨立的資料庫產品，其可以完全與MySQL相容，可以在不更改代碼的情況了下將儲存引擎更換成XtraDB。是最接近官方MySQL Enterprise發行版的版本。

Percona提供了高效能XtraDB引擎，還提供PXC高可用解決方案，並且附帶了percona-toolkit等DBA管理工具箱，

3、MariaDB

MariaDB由MySQL的創始人開發，MariaDB的目的是完全相容MySQL，包括API和命令列，使之能輕鬆成為MySQL的代替品。

MariaDB提供了MySQL提供的標準儲存引擎，即MyISAM和InnoDB，10.0.9版起使用XtraDB（名稱代號為Aria）來代替MySQL的InnoDB。

 

4、如何選擇

 

綜合多年使用經驗和效能對比，首選Percona分支，其次是MariaDB，如果你不想冒一點風險，那就選擇MYSQL官方版本。

 

二、常用的MYSQL調優策略

 

1、硬體層相關最佳化

修改伺服器BIOS設定

選擇Performance Per Watt Optimized(DAPC)模式，發揮CPU最大效能。

Memory Frequency（記憶體頻率）選擇Maximum Performance（最佳效能）

記憶體設定菜單中，啟用Node Interleaving，避免NUMA問題

2、磁碟I/O相關

使用SSD硬碟

如果是磁碟陣列儲存，建議陣列卡同時配備CACHE及BBU模組，可明顯提升IOPS。

raid層級盡量選擇raid10，而不是raid5.

3、檔案系統層最佳化

使用deadline/noop這兩種I/O調度器，千萬別用cfq

使用xfs檔案系統，千萬別用ext3；ext4勉強可用，但業務量很大的話，則一定要用xfs；

檔案系統mount參數中增加：noatime, nodiratime, nobarrier幾個選項（nobarrier是xfs檔案系統特有的）；

 

4、核心參數最佳化

修改vm.swappiness參數，降低swap使用率。RHEL7/centos7以上則謹慎設定為0，可能發生OOM

調整vm.dirty_background_ratio、vm.dirty_ratio核心參數，以確保能持續將髒資料重新整理到磁碟，避免瞬間I/O寫。產生等待。

調整net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse都設定為1，減少TIME_WAIT，提高TCP效率。

5、Mysql參數最佳化建議

建議設定default-storage-engine=InnoDB，強烈建議不要再使用MyISAM引擎。

調整innodb_buffer_pool_size的大小，如果是單一實例且絕大多數是InnoDB引擎表的話，可考慮設定為實體記憶體的50% -70%左右。

設定innodb_file_per_table = 1，使用獨立資料表空間。

調整innodb_data_file_path = ibdata1:1G:autoextend，不要用預設的10M,在高並發情境下，效能會有很大提升。

設定innodb_log_file_size=256M，設定innodb_log_files_in_group=2，基本可以滿足大多數應用情境。

調整max_connection（最大串連數）、max_connection_error（最大錯誤數）設定，根據業務量大小進行設定。

另外，open_files_limit、innodb_open_files、table_open_cache、table_definition_cache可以設定大約為max_connection的10倍左右大小。

key_buffer_size建議調小，32M左右即可，另外建議關閉query cache。

mp_table_size和max_heap_table_size設定不要過大，另外sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size等設定也不要過大。

三、   MYSQL常見的應用架構分享1、主從複製解決方案

這是MySQL自身提供的一種高可用解決方案，資料同步方法採用的是MySQL replication技術。MySQL replication就是從伺服器到主伺服器拉取二進位記錄檔，然後再將記錄檔解析成相應的SQL在從伺服器上重新執行一遍主伺服器的操作，通過這種方式保證資料的一致性。

為了達到更高的可用性，在實際的應用環境中，一般都是採用MySQL replication技術配合高可用叢集軟體keepalived來實現自動failover，這種方式可以實現95.000%的SLA。

2、MMM/MHA高可用解決方案

MMM提供了MySQL主主複製配置的監控、容錯移轉和管理的一套可伸縮的指令碼套件。在MMM高可用方案中，典型的應用是雙主多從架構，通過MySQL replication技術可以實現兩個伺服器互為主從，且在任何時候只有一個節點可以被寫入，避免了多點寫入的資料衝突。同時，當可寫的主節點故障時，MMM套件可以立刻監控到，然後將服務自動切換到另一個主節點，繼續提供服務，從而實現MySQL的高可用。

3、Heartbeat/SAN高可用解決方案

在這個方案中，處理failover的方式是高可用叢集軟體Heartbeat，它監控和管理各個節點間串連的網路，並監控叢集服務，當節點出現故障或者服務不可用時，自動在其他節點啟動叢集服務。在資料共用方面，通過SAN（Storage Area Network）儲存來共用資料，這種方案可以實現99.990%的SLA。

4、Heartbeat/DRBD高可用解決方案

此方案處理failover的方式上依舊採用Heartbeat，不同的是，在資料共用方面，採用了基於塊層級的資料同步軟體DRBD來實現。

DRBD是一個用軟體實現的、無共用的、伺服器之間鏡像塊裝置內容的儲存複製解決方案。和SAN網路不同，它並不共用儲存，而是通過伺服器之間的網路複製資料。

 

四、MYSQL經典應用架構

 

其中：

         Dbm157是mysql主，dbm158是mysql主的備機，dbs159/160/161是mysql從。

MySQL寫操作一般採用基於heartbeat+DRBD+MySQL搭建高可用叢集的方案。通過heartbeat實現對mysql主進行狀態監測，而DRBD實現dbm157資料同步到dbm158。

讀操作普遍採用基於LVS+Keepalived搭建高可用高擴充叢集的方案。前端AS應用通過提高的讀VIP串連LVS，LVS有keepliaved做成高可用模式，實現互備。

         最後，mysql主的從節點dbs159/160/161通過mysql主從複製功能同步mysql主的資料，通過lvs功能提供給前端AS應用進行讀操作，並實現負載平衡。

本文出自 “愛維Linux” 部落格，請務必保留此出處http://ixdba.blog.51cto.com/2895551/1751377

MYSQL企業常用架構與調優經驗分享