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并不一定能提高速度.