mySQL集群

05-11

mySQL集群(cluster)

在這一章為了不浪費讀者的寶貴時間，我只會列出mySQL集群的幾種比較方案，目前有一些第三方提供的mySQL集群方案還是不錯的選擇。

MySQL的cluster方案有很多官方和第三方的選擇，選擇多就是一種煩惱，因此，我們考慮MySQL資料庫滿足下三點需求並來考察市面上可行的解決方案：

高可用性：主伺服器故障後可自動切換到後備伺服器可伸縮性：可方便通過腳本增加DB伺服器負載均衡：支持手動把某公司的數據請求切換到另外的伺服器，可配置哪些公司的數據服務訪問哪個伺服器

這是我列出的時下市面上比較流行的幾種mySQL集群方案中一些核心功能的比較，供參考：

推薦第三方mySQL集群方案

綜合比較下來，筆者推薦採用MySQL Fabric和MySQL Cluster方案，以及另外一種較成熟的集群方案Galera Cluster。

幾種mySQL集群方案的比較

MySQLCluster

MySQL Cluster 是MySQL 官方集群部署方案，它的歷史較久。支持通過自動分片支持讀寫擴展，通過實時備份冗餘數據，是可用性最高的方案，聲稱可做到99.999%的可用性。

架構及實現原理：

mySQL cluster主要由三種類型的服務組成：

NDB Management Server：管理伺服器主要用於管理cluster中的其他類型節點（Data Node和SQL Node），通過它可以配置Node信息，啟動和停止Node。 SQL Node：在MySQL Cluster中，一個SQL Node就是一個使用NDB引擎的mysql server進程，用於供外部應用提供集群數據的訪問入口。Data Node：用於存儲集群數據；系統會盡量將數據放在內存中。

缺點及限制：

對需要進行分片的表需要修改引擎Innodb為NDB，不需要分片的可以不修改。NDB的事務隔離級別只支持Read Committed，即一個事務在提交前，查詢不到在事務內所做的修改；而Innodb支持所有的事務隔離級別，默認使用Repeatable Read，不存在這個問題。外鍵支持：雖然最新的Cluster版本已經支持外鍵，但性能有問題（因為外鍵所關聯的記錄可能在別的分片節點中），所以建議去掉所有外鍵。Data Node節點數據會被盡量放在內存中，對內存要求大。資料庫系統提供了四種事務隔離級別：

Serializable（串列化）：一個事務在執行過程中完全看不到其他事務對資料庫所做的更新（事務執行的時候不允許別的事務並發執行。事務串列化執行，事務只能一個接著一個地執行，而不能並發執行。）。Repeatable Read（可重複讀）：一個事務在執行過程中可以看到其他事務已經提交的新插入的記錄，但是不能看到其他其他事務對已有記錄的更新。Read Commited（讀已提交數據）：一個事務在執行過程中可以看到其他事務已經提交的新插入的記錄，而且能看到其他事務已經提交的對已有記錄的更新。Read Uncommitted（讀未提交數據）：一個事務在執行過程中可以看到其他事務沒有提交的新插入的記錄，而且能看到其他事務沒有提交的對已有記錄的更新。

MySQL Fabric

為了實現和方便管理MySQL 分片以及實現高可用部署，Oracle在2014年5月推出了一套為各方寄予厚望的MySQL產品 -- MySQL Fabric, 用來管理MySQL 服務，提供擴展性和容易使用的系統，Fabric當前實現了兩個特性：高可用和使用數據分片實現可擴展性和負載均衡，這兩個特性能單獨使用或結合使用。

MySQL Fabric 使用了一系列的python腳本實現。

應用案例：由於該方案在去年才推出，目前在網上暫時沒搜索到有大公司的應用案例。

架構及實現原理：

Fabric支持實現高可用性的架構圖如下

Fabric使用HA組實現高可用性，其中一台是主伺服器，其他是備份伺服器，備份伺服器通過同步複製實現數據冗餘。應用程序使用特定的驅動，連接到Fabric 的Connector組件，當主伺服器發生故障後，Connector自動升級其中一個備份伺服器為主伺服器，應用程序無需修改。

Fabric支持可擴展性及負載均衡的架構如下：

使用多個HA 組實現分片，每個組之間分擔不同的分片數據（組內的數據是冗餘的，這個在高可用性中已經提到）

應用程序只需向connector發送query和insert等語句，Connector通過MasterGroup自動分配這些數據到各個組，或從各個組中組合符合條件的數據，返回給應用程序。

缺點及限制：

影響比較大的兩個限制是：

自增長鍵不能作為分片的鍵；事務及查詢只支持在同一個分片內，事務中更新的數據不能跨分片，查詢語句返回的數據也不能跨分片。

分片：如何支持可擴展性和負載均衡

當一台機器或一個組承受不了服務壓力後，可以添加伺服器分攤讀寫壓力，通過Fabirc的分片功能可以將某些表中數據分散存儲到不同伺服器。我們可以設定分配數據存儲的規則，通過在表中設置分片key設置分配的規則。另外，有些表的數據可能並不需要分片存儲，需要將整張表存儲在同一個伺服器中，可以將設置一個全局組（Global Group）用於存儲這些數據，存儲到全局組的數據會自動拷貝到其他所有的分片組中。

Galera Cluster

Galera Cluster號稱是世界上最先進的開源資料庫集群方案。

主要優點及特性：

真正的多主服務模式：多個服務能同時被讀寫，不像Fabric那樣某些服務只能作備份用同步複製：無延遲複製，不會產生數據丟失熱備用：當某台伺服器當機後，備用伺服器會自動接管，不會產生任何當機時間自動擴展節點：新增伺服器時，不需手工複製資料庫到新的節點支持InnoDB引擎對應用程序透明：應用程序不需作修改。

架構及實現原理：

首先，我們看看傳統的基於mysql Replication（複製）的架構圖：

Replication方式是通過啟動複製線程從主伺服器上拷貝更新日誌，讓後傳送到備份伺服器上執行，這種方式存在事務丟失及同步不及時的風險。Fabric以及傳統的主從複製都是使用這種實現方式。

而Galera則採用以下架構保證事務在所有機器的一致性。

客戶端通過Galera Load Balancer訪問資料庫，提交的每個事務都會通過wsrep API 在所有伺服器中執行，要不所有伺服器都執行成功，要不就所有都回滾，保證所有服務的數據一致性，而且所有伺服器同步實時更新。

缺點及限制：

由於同一個事務需要在集群的多台機器上執行，因此網路傳輸及並發執行會導致性能上有一定的消耗。所有機器上都存儲著相同的數據，全冗餘。若一台機器既作為主伺服器，又作為備份伺服器，出現樂觀鎖導致rollback的概率會增大，編寫程序時要小心。不支持的SQL：LOCK / UNLOCK TABLES / GET_LOCK(), RELEASE_LOCK()…不支持XA Transaction

目前基於Galera Cluster的實現方案有三種：Galera Cluster for MySQL、Percona XtraDB Cluster、MariaDB Galera Cluster。

我們採用較成熟、應用案例較多的Percona XtraDB Cluster。

應用案例：

超過2000多家外國企業使用

Fabric對比Galera

mySQL連接數優化

我們如果經常遇見MySQL：ERROR1040：Too many connections的情況，一種情況是訪問量確實很高，MySQL伺服器扛不住了，這個時候就要考慮增加從伺服器分散讀壓力，從架構層面。另外一種情況是MySQL配置文件中max_connections的值過小。來看一個例子。

mysql> show variables like max_connections;

+-----------------+-------+

Variable_name | Value |

+-----------------+-------+

max_connections | 800 |

+-----------------+-------+

#### 這台伺服器最大連接數是256，然後查詢一下該伺服器響應的最大連接數；

mysql> show global status like Max_used_connections;

+----------------------+-------+

Variable_name | Value |

+----------------------+-------+

Max_used_connections | 245 |

+----------------------+-------+

#### MySQL伺服器過去的最大連接數是245，沒有達到伺服器連接數的上線800，不會出現1040錯誤。

#### Max_used_connections /max_connections * 100% = 85%

#### 最大連接數占上限連接數的85%左右,如果發現比例在10%以下，則說明MySQL伺服器連接數的上限設置得過高了。

key_buffer_size

key_buffer_size是設置MyISAM表索引緩存空間的大小，此參數對MyISAM表性能影響最大。下面是一台MyISAM為主要存儲引擎伺服器的配置：

mysql> show variables like key_buffer_size;

+-----------------+-----------+

Variable_name | Value |

+-----------------+-----------+

key_buffer_size | 536870912 |

+-----------------+-----------+

#### 從上面可以看出，分配了512MB內存給key_buffer_size。再來看key_buffer_size的使用情況：

mysql> show global status like key_read%;

+-------------------+--------------+

Variable_name | Value |

+-------------------+-------+

Key_read_requests | 27813678766 |

Key_reads | 6798830|

+-------------------+--------------+

一共有27813678766個索引讀取請求，有6798830個請求在內存中沒有找到，直接從硬碟讀取索引。

key_cache_miss_rate = key_reads / key_read_requests * 100%

比如上面的數據，key_cache_miss_rate為0.0244%，4000%個索引讀取請求才有一個直接讀硬碟，效果已經很好了，key_cache_miss_rate在0.1%以下都很好，如果key_cache_miss_rate在0.01%以下的話，則說明key_buffer_size分配得過多，可以適當減少。

mySQL的臨時表

當執行語句時，關於已經被創建了隱含臨時表的數量，我們可以用如下命令查詢其具體情況：

mysql> show global status like created_tmp%;

+-------------------------+----------+

Variable_name | Value |

+-------------------------+----------+

Created_tmp_disk_tables | 21119 |

Created_tmp_files | 6 |

Created_tmp_tables | 17715532 |

+-------------------------+----------+

#### MySQL伺服器對臨時表的配置：

mysql> show variables where Variable_name in (tmp_table_size,max_heap_table_size);

+---------------------+---------+

Variable_name | Value |

+---------------------+---------+

max_heap_table_size | 2097152 |

tmp_table_size | 2097152 |

+---------------------+---------+

每次創建臨時表時，Created_tmp_table都會增加，如果磁碟上創建臨時表，Created_tmp_disk_tables也會增加。Created_tmp_files表示MySQL服務創建的臨時文件數，比較理想的配置是：

Created_tmp_disk_tables / Created_tmp_files *100% <= 25%

比如上面的伺服器：

Created_tmp_disk_tables / Created_tmp_files *100% =1.20%，這個值就很棒了。

mySQL打開表的情況

Open_tables表示打開表的數量，Opened_tables表示打開過的表數量，我們可以用如下命令查看其具體情況：

mysql> show global status like open%tables%;

+---------------+-------+

Variable_name | Value |

+---------------+-------+

Open_tables | 351 |

Opened_tables | 1455 |

#### 查詢下伺服器table_open_cache;

mysql> show variables like table_open_cache;

+------------------+-------+

Variable_name | Value |

+------------------+-------+

table_open_cache | 2048 |

+------------------+-------+

如果Opened_tables數量過大，說明配置中table_open_cache的值可能太小。

比較合適的值為：

open_tables / opened_tables* 100% > = 85%

open_tables / table_open_cache* 100% < = 95%

mySQL的進程使用情況

如果我們在MySQL伺服器的配置文件中設置了thread_cache_size，當客戶端斷開時，伺服器處理此客戶請求的線程將會緩存起來以響應一下客戶而不是銷毀(前提是緩存數未達上線)Thread_created表示創建過的線程數，我們可以用如下命令查看：

mysql> show global status like thread%;

+-------------------+-------+

Variable_name | Value |

+-------------------+-------+

Threads_cached | 40|

Threads_connected | 1 |

Threads_created | 330 |

Threads_running | 1 |

+-------------------+-------+

#### 查詢伺服器thread_cache_size配置如下：

mysql> show variables like thread_cache_size;

+-------------------+-------+

Variable_name | Value |

+-------------------+-------+

thread_cache_size | 100 |

+-------------------+-------+

如果發現Threads_created的值過大的話，表明MySQL伺服器一直在創建線程，這也是比較耗費資源的，可以適當增大配置文件中thread_cache_size的值。

查詢緩存(query cache)

它主要涉及兩個參數，query_cache_size是設置MySQL的Query Cache大小，query_cache_type是設置使用查詢緩存的類型，我們可以用如下命令查看其具體情況：

mysql> show global status like qcache%;

+-------------------------+-----------+

Variable_name | Value |

+-------------------------+-----------+

Qcache_free_blocks | 22756 |

Qcache_free_memory | 76764704 |

Qcache_hits | 213028692 |

Qcache_inserts | 208894227 |

Qcache_lowmem_prunes | 4010916 |

Qcache_not_cached | 13385031 |

Qcache_queries_in_cache | 43560 |

Qcache_total_blocks | 111212 |

+-------------------------+-----------+

MySQL查詢緩存變數的相關解釋如下：

Qcache_free_blocks：緩存中相領內存快的個數。數目大說明可能有碎片。flush query cache會對緩存中的碎片進行整理，從而得到一個空間塊。Qcache_free_memory：緩存中的空閑空間。Qcache_hits：多少次命中。通過這個參數可以查看到Query Cache的基本效果。Qcache_inserts：插入次數，沒插入一次查詢時就增加1。命中次數除以插入次數就是命中比率。Qcache_lowmem_prunes：多少條Query因為內存不足而被清楚出Query Cache。通過Qcache_lowmem_prunes和Query_free_memory相互結合，能夠更清楚地了解到系統中Query Cache的內存大小是否真的足夠，是否非常頻繁地出現因為內存不足而有Query被換出的情況。 Qcache_not_cached：不適合進行緩存的查詢數量，通常是由於這些查詢不是select語句或用了now()之類的函數。Qcache_queries_in_cache：當前緩存的查詢和響應數量。Qcache_total_blocks：緩存中塊的數量。

query_cache的配置命令：

mysql> show variables like query_cache%;

+------------------------------+---------+

Variable_name | Value |

+------------------------------+---------+

query_cache_limit | 1048576 |

query_cache_min_res_unit | 2048 |

query_cache_size | 2097152 |

query_cache_type | ON |

query_cache_wlock_invalidate | OFF |

+------------------------------+---------+

欄位解釋如下：

query_cache_limit：超過此大小的查詢將不緩存。query_cache_min_res_unit：緩存塊的最小值。query_cache_size：查詢緩存大小。query_cache_type：緩存類型，決定緩存什麼樣的查詢，示例中表示不緩存select sql_no_cache查詢。query_cache_wlock_invalidat：表示當有其他客戶端正在對MyISAM表進行寫操作，讀請求是要等WRITE LOCK釋放資源後再查詢還是允許直接從Query Cache中讀取結果，默認為OFF（可以直接從Query Cache中取得結果。）query_cache_min_res_unit的配置是一柄雙刃劍，默認是4KB，設置值大對大數據查詢有好處，但如果你的查詢都是小數據查詢，就容易造成內存碎片和浪費。查詢緩存碎片率 = Qcache_free_blocks /Qcache_total_blocks * 100%如果查詢碎片率超過20%，可以用 flush query cache 整理緩存碎片，或者試試減少query_cache_min_res_unit，如果你查詢都是小資料庫的話。查詢緩存利用率 = (Qcache_free_size – Qcache_free_memory)/query_cache_size * 100% 。查詢緩存利用率在25%一下的話說明query_cache_size設置得過大，可適當減少;查詢緩存利用率在80%以上而且Qcache_lowmem_prunes > 50的話則說明query_cache_size可能有點小，不然就是碎片太多。查詢命中率 = (Qcache_hits - Qcache_insert)/Qcache)hits * 100%，比如説：伺服器中的查詢緩存碎片率等於20%左右，查詢緩存利用率在50%，查詢命中率在2%，說明命中率很差，可能寫操作比較頻繁，而且可能有些碎片。

mySQL排序使用情況

它表示系統中對數據進行排序時所用的Buffer，我們可以用如下命令查看：

mysql> show global status like sort%;

+-------------------+----------+

Variable_name | Value |

+-------------------+----------+

Sort_merge_passes | 10 |

Sort_range | 37431240 |

Sort_rows | 6738691532 |

Sort_scan | 1823485 |

+-------------------+----------+

Sort_merge_passes包括如下步驟：MySQL首先會嘗試在內存中做排序，使用的內存大小由系統變數sort_buffer_size來決定，如果它不夠大則把所有的記錄都讀在內存中，而MySQL則會把每次在內存中排序的結果存到臨時文件中，等MySQL找到所有記錄之後，再把臨時文件中的記錄做一次排序。這次再排序就會增加sort_merge_passes。實際上，MySQL會用另外一個臨時文件來存儲再次排序的結果，所以我們通常會看sort_merge_passes增加的數值是建臨時文件數的兩倍。因為用到了臨時文件，所以速度可能會比較慢，增大sort_buffer_size會減少sort_merge_passes和創建臨時文件的次數，但盲目地增大sort_buffer_size並不一定能提高速度。