MySQL Fabric - 為您的應用系統提供高可用、高擴充性的資料庫

MySQL Fabric

- 為您的應用系統提供高可用、高擴充性的資料庫

Oracle在今年5月初推出了一套為各方寄以厚望的在MySQL產品 – MySQL Fabric，單從字面上似乎不太能看出它是啥咪碗糕，但是由名稱上還是有跡可循的，”Fabric”是"織"或"織品"，這意味著它是用來"織"起"一片”MySQL資料庫。MySQL Fabric是一套資料庫伺服器場(Database Server Farm)的架構管理系統。

MySQL Fabric是什麼？

MySQL Fabric 能 『組織』多個MySQL資料庫,使應用系統能將大於幾TB的表分散到多個資料庫 – 做Data Shard(或資料分片)；在同一分片之內又可以含多個資料庫,並且由Fabric自動挑一個適合的(有最新的交易的資料)當主資料庫，其他的資料庫自動配置成從資料庫 - 做資料庫主從複製；進而在主資料庫當機時由各從資料中挑一個適合的提升為主資料庫，那麼其他的從資料庫自動轉向這個新的主資料庫找新的交易資料來複製。注意，這裡所說的 『自動』是指由MySQL Fabric在背後為您完成，您不需要手動參與這些更改配置的動作。最重要的MySQL Fabric是GPL的開源軟體，也就是在符合GPL的規範下您可以自由的使用及修改這個軟體。

 

MySQL Fabric要決解的問題

為什麼要做Data Shard？當您的應用需要處理的表有大於1TB的資料時，Data Shard常常就是一個不得不為之惡。這麼大的表不論在查詢、更新的效率上，或是備份、更改結構所需要的時間上都會造成窒礙難行的問題。然而當您將這樣大的表分散到多個資料庫伺服器上，又會使每一台資料庫伺服器都可能是單點固障點，只要有一台當機就會使整個系統的查詢都會有問題，另一方面應用端的程式也會因為每個查詢都要依其查詢條件(where子句的內容)分別指向不同的資料庫而變得更為複雜。再者，當Data Shard的結構改變時(例如增加一個資料庫)會使應用端所有的程式都必需修改，而使維護變得即為複雜。

為了解決應用程式複雜度增加的問題，有人在應用端和資料庫之間加了一個代理器(proxy)或者稱為switch，應用端所有對資料庫的指令先送到switch，再由switch判斷要轉到那一個資料庫，右圖是這個方案的示意架構。這也許可以解決應用端程式複雜度的問題，但是當應用端的數量增加，資料庫分片增加或系統的壓力增加時，這個switch會成為容量及性能的瓶頸和單點固障(當它當機時所有的應用端都找不到資料庫)，而且所有對資料庫的指令均要傳兩次(先到switch再到資料庫)，每個查詢都會造成額外的負荷，而能降低應用系統的效能。

MySQL Fabric的架構

MySQL Fabric則採取不一樣的做法，其架構如下圖所示，主要的特點是把switch合併到各應用端的connector(或是driver)中，以解決單一switch的單點固障和性能瓶頸的問題。。MySQL Fabric主要由三個部份所構成：

1.MySQL Fabric管理節點：

是一個Python脚本，由它整個架構的核心，MySQL Fabric管理節點主要的功能是管理整個資料庫伺服器場(server farm)，它開啟時會找/etc/mysql/fabric.cnf配置檔(或以—config參數指定配置檔)，由它指定fabric背後當成存放Server Farm架構和配置之repository的MySQL資料庫位置、所用的端口、和連線帳號等資訊，Fabric在初始化時(執行mysqlfabric manage setup命令)，會在MySQL資料庫上開一個schema (通常是名稱為fabric的database)，存放server farm配置相關的資料，例如那些伺服器組由那些MySQL 資料庫構成，各伺服器組中以那一個MySQL資料庫為主那些為從，各分庫的表要拆分到那些伺服器組中，各shard table要以那個欄位的內容做拆分，拆分的政策為何(RANGE,KEY)？如果是RANGE拆分，各組存放的是那些範圍(例如<1000 font="">的存第一個shard, >1000的存於第二個shard)？MySQL Fabric節點在設定配置時會對server farm中各MySQL資料庫下達建立主從複製的指令(下圖紅色線條)，在正常運時會定期pin各組的主資料庫，當它發現主資料庫沒有正運作，它會啟動固障移轉程序，在該server farm的從資料庫中找一個適合的提升為主，其他的從資料庫轉向新的主資料庫繼續複製交易資料。

2.資料庫伺服器場(database server farm)：

是整個架構中的工作引擎，在傳統的資料庫應用中這是單一MySQL資料庫，MySQL Fabric則是以多個資料庫支持大資料量表(TB級以上的表)和高可用資料庫的需求。這些資料庫分成幾個高可用組(HA Group或稱為server group)，每個組包含一個以上的資料庫伺服器，上圖中最下面幾個灰色和淺藍色的圓角方塊代表高可用組，如果高可用組中有多個資料庫，MySQL Fabric會(以mysqlfabric group promote 指令)自其中挑一個提升為主資料庫，其他資料庫則成為從資料庫，從資料庫複製主資料庫的變化 - 完成設定同一高可用組內的主從複製。自此以後Fabric會定期監看這個主資料庫，當主資料庫當機時，Fabric會自該高可用組內的從資料庫中挑一個提升為主資料庫，其他的資料庫會轉向這個新的主資料庫繼續複製。另一方面MySQL Fabric也會指示應用端的connector對這些主從資料庫做讀寫分離，當應用程式對資料庫做讀寫兼具的交易性操作，connector會將交易性指令送到主資料庫，如果某一段應用程式只會對資料庫下讀的指令，且連線的read_only參數調為”ON”，則所有的查詢均輪流送到這幾個從資料庫。藉由讀寫分離，應用系統的資料處理能量得以增加。此外，如前一段所描述的MySQL Fabric還能處理需要拆分到多個資料庫服務器的表(sharding tables)，每一個高可用組(上圖灰色圓角方塊)都可能存放shard table的部份資料，應用端的connector會將對shard table的指令依MySQL Fabric管理節點的設定送到不同的高可用組，如此可使資料庫的容量隨著高可用組的數量呈同比例的增長。同時，對非拆分的表所下的指令和所有的DDL會由Connector送到全域性高可用組(Global Group，上圖淺藍色的圓角方塊)，全域性組的主資料庫又被MySQL Fabric設為其他高可用組的主資料庫，所有存拆分表的高可用組的主資料庫會複製global group的變異，這麼一來其他高可組都有一份非拆分表的資料，而使SQL中拆分表對非拆分表的JOIN操作(由於所需的資料都在同一資料庫中)變得更簡單。例如在一個訂單系統中有ORDER、ORDER_LINE、PRODUCT三個表，ORDER和ORDER_LINE依客戶代號(customer_id)做KEY shard，PRODUCT表不拆分，則每個高可用組的資料庫有一部份ORDER和ORDER_LINE表的資料，在做過正規化的ORDER_LINE表只有參照PRODUCT的外鍵加在product_id欄位上不會有product_name欄位，如果要查各ORDER_LINE買的是什麼產品和產品名稱，必需在SQL指令中JOIN PRODUCT表，由於PRODUCT表的操作都先打到Global Group，再複製到各高可用組(Global Group的從資料庫)，如此使得ORDER_LINE JOIN PRODUCT的指令可找到所有需要的PRODUCT之資料。

3.Connector：

應用系統在運作時，每個SQL指令會經由connector送到資料庫。MySQL Fabric所搭配的connector和一般使用單機的MySQL資料庫一樣，只是在較新版的connector是fabric aware connector多了一些能處理資料庫伺服器場(database server farm)的功能，使它們能在建立資料庫連線時,以XML-RPC協定查一下MySQL Fabric的管理節點中server farm的配置，然後透過該連線所下的查詢可依Fabric的指示送到適當的資料庫，如此一來就常見的database shard方案中可能造成效能瓶頸和單點固障的proxy(或圖一中的switch)收到Connector裡面，從而解決這些問題(由各應用端的database driver決定要將指令送到那一個資料庫)。目前MySQL Fabric支援的技術有Java、Python、PHP，即Connector/J、Connector/Python和Connecotr/PHP都是Fabric-aware。以Java為例，JDBC driver 必需是Connector/J 5.1.30以後的版本(可自網址dev.mysql.com/download下載)，Fabirc的Java程式和一般對單機MySQL的查詢的Java程式差不多，只是在建database connection object時database conneciton URL不是指向資料庫，改為指向MySQL Fabric管理節點(伺服器的IP和端口-通常是32274)。當查詢的表是全域性表(不做table shard)或DDL(例如建表或改表結構)時，建立connection object的要加上 “fabricServerGroup=”參數 ，之後透過這個connection object所下的SQL 指令會送到Global Group的主資料庫，再由資料庫複製到其他的高可用組(shard)中。如果SQL指令所要操作的表是分區表(shard table)，則建立connection object時要在參數加上 “fabricShardTable=” ，之後透過這個connection object所下的SQL 指令會依MySQL Fabirc所設定的分表(shard)原則送到各分區(shard)的高可用組。這樣一來應用程式對這些shard table下SQL指令時不需在SQL中判斷要送到那個資料庫，完全由Connector在建立資料庫連線(connection)時向MySQL Fabric 所查到的server farm的配置資訊(那個資料庫屬於那個shard group、各shard table的拆分原則為何...)所決定，而且這個配置在建主連線後就緩存(cache)在Connector所在的應用端(Java memory heap或Python或PHP的cache中)，如此在每次下SQL指令時不需要重復查MySQL Fabric管理節點，而依存於應用端的分表配置直接送到正確的資料庫而使應用程式的效能不會因為要做表拆分(table shard)而有任何降低。

結論

MySQL Fabric推出正式發行版才兩個多月，已經引起許多重量級MySQL用戶的注意和試用，而Oracle也不吝於加大對它投資，以加速使其功能更趨完善，在現在已推出了一個更新版，在最新版的MySQL Fabric 加上了對SSL連線的支持，近期內對Fabric改良的重點將著重於使Fabric對應用程式更透明化(例如單一個SQL對shard table的查詢條件可以跨shard)、支援更多的高可用方案(例如高可用組之內的MySQL可以DRBD做高可用或用MySQL Cluster)、提供更友善易用的使用介面等。在此建議關心MySQL發展的朋友可以留意這個產品的發展，進一步試用它，將您的意見和心得(或發現的bug)反應給Oracle，如果您滿意它所提供的功能和穏定度，可以將它放入您的投產系統正式營運，Fabric的開發團會很歡迎大家對這個MySQL家族的新成員所做的任何貢獻。本篇文章的目的在于介紹MySQL Fabric要解決的問題和Fabric的架構，至於詳細的設定和操作步驟 ，容我在下一個文章中以一個範例和各位分享，敬請祈待。

MySQL Utility的新功能

除了MySQL數据庫，自dev.mysql.com/downloads還能找到不少的好東西，包括了各種connector，proxy等...。其中MySQL Workbench是較少為人注意到的一個軟體，然而對DBA或資料庫開發者，MySQL Workbench是一個能為我們增加生產力的一個利器。用過MySQL Workbench的人大概都知道它有三大功能－SQL Development､Data Modeling､Server Administrator,分別支援以圖形介面自資料庫查詢/修改資料，以正向工程或反向工程,以圖形化介面做資料庫設計，並以此建立資庫的Data Schema和將Data Dictionary轉為圖形化的資料庫設計模型，以及以圖形化介面協助DBA管理MySQL資料庫，包括開､關､調整､授權､調校資料庫等工作。但是我在這裡想要和各位分享的却是MySQL Workbench自5.2版以後的新功能 － MySQL Utility。

MySQL Utility是內嵌於MySQL Workbench的一套以Python開發的script，使我們能不用花工夫寫script，以一道指令執行一連串的操作，例如在不資料庫間抄資料、比對資料、故障移轉、設定主從複製等。除了透過MySQL Workbench，它也可以自launch pad(https://launchpad.net/mysql-utilities)下載。

如果透過MySQL Workbench使用MySQL Utility可自Workbench上列之工具列最右邊的一個工具(如下圖所示)帶出來，或在Workbench的選單中選 'Plugins'，再選'Start Shell for MySQL Utilities'即可帶出一個command mode 的terminal。

MySQL Utility提供的script有以下種類：
1. 資料庫操作，包括－

• mysqldbcompare – 比對兩個database間的差異，包括table definition和table其中的資料 ，在比對table中的資料時會lock該table；另外還可以加上  '--changes-for' 參數，以一個資料庫為主去改另一個資料庫，使兩個資料庫一致化。這個工具在主從複製時，查是否有資料還有資料沒有自主資料庫複製到從資料庫相當有用。

範例如下：

$ mysqldbcompare --server1=root:welcome1@localhost:3308 --server2=root:welcome1@localhost:3306 world:world --run-all-test --show-reverse
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases world on server1 and world on server2
#
# WARNING: Objects in server1.world but not in server2.world:
#        TABLE: tt
#
#                                                   Defn    Row     Data  
# Type      Object Name                             Diff    Count   Check 
# -------------------------------------------------------------------------
# TABLE     City                                    pass    pass    pass   
# TABLE     Country                                 pass    pass    pass   
# TABLE     CountryLanguage                         pass    FAIL    FAIL   
#
# Row counts are not the same among world.CountryLanguage and world.CountryLanguage.
#

# Rows in world.CountryLanguage not in world.CountryLanguage
+--------------+-----------+-------------+-------------+
| CountryCode  | Language  | IsOfficial  | Percentage  |
+--------------+-----------+-------------+-------------+
| TWN          | Taiya     | F           | 0.2         |
| TWN          | Lukai     | F           | 0.2         |
+--------------+-----------+-------------+-------------+

#
# Rows in world.CountryLanguage not in world.CountryLanguage
# +--------------+-----------+-------------+-------------+
# | CountryCode  | Language  | IsOfficial  | Percentage  |
# +--------------+-----------+-------------+-------------+
# | TWN          | Taiya     | F           | 0.2         |
# | TWN          | Lukai     | F           | 0.2         |
# +--------------+-----------+-------------+-------------+
#
# Database consistency check failed.
#
# ...done
上例顯示比對 在同一個server上，用不同port（3306,3308）的兩個database instance的內容，用3308的資料庫多了一個table – tt，另外用3308的資料庫在CountryCode還多了兩個rows.

• mysqldbcopy – 指定database object，將database自來源資料庫instance抄到目的資料庫instance，是一個𨗴輯層的抄寫，以登入來源資料庫，下SELECT指令，再INSERT到目的資料庫的方式進行抄寫。但是現在的問題(MySQL 5.6.9 搭配MySQL Workbench 5.2.45)是執行mysqldbcopy時會有 ‘ERROR: Query failed. 1840: GTID_PURGED can only be set when GTID_EXECUTED is empty.’ 的錯誤訊息' ，bug report上說會在5.6.11解決此問題，參考http://bugs.mysql.com/bug.php?id=68038。

• mysqldbexport – 倒出資料及資料詞典資料，和mysqldump類似，但能有更多的選項，可導出更多種的格式，以 '--format' 指定產出的格式，可產出sql﹑grid﹑csv﹑tab﹑vertical (各column垂直列示) 。如果沒指定 '--export=data' 則只會傳出建table的SQL DDL

範例如下：
$ mysqldbexport --server=root:welcome1@127.0.0.1:3306 --export=data -v world

• mysqldbimport – 倒入資料及資料詞典資料

• mysqldiff – 比對不同資料庫物件的定義的差異，和mysqldbcompare不同，它不會比對table內各行的值，只會看table况stored procedure是否有差異。

範例如下：
$mysqldiff --server1=root:welcome1@127.0.0.1:3306 --server2=root:welcome1@127.0.0.1:3308 --difftype=context  world.CountryLanguage:world.CountryLanguage
2. 一般性的作業，包括 －

• mysqldiskusage – 顯示資料庫對磁碟空間的使用，大致上等同在OS下 ‘du' 的指令。

• mysqlindexcheck – 檢查是否有多餘的索引。

範例如下：
$ mysqlindexcheck --server=root:welcome1@127.0.0.1:3306 world
# Source on 127.0.0.1: ... connected.
# The following indexes are duplicates or redundant for table world.CountryLanguage:
#
CREATE INDEX CountryCode ON world.CountryLanguage (CountryCode) USING BTREE
#     may be redundant or duplicate of:
ALTER TABLE world.CountryLanguage ADD PRIMARY KEY (CountryCode, Language)

• mysqlmetagrep – 找尋指定(可加wild card到搜尋字串)的資料物件的定義，包括stored program。

範例如下：

$ mysqlmetagrep --pattern="t%" --server=root:welcome1@127.0.0.1 --format=vertical world
*************************      81. row *************************
  Connection: root:*@127.0.0.1:3306
 Object Type: TABLE
 Object Name: table_io_waits_summary_by_table
    Database: performance_schema
  Field Type: TABLE
     Matches: table_io_waits_summary_by_table
*************************      82. row *************************
  Connection: root:*@127.0.0.1:3306
 Object Type: TABLE
 Object Name: table_lock_waits_summary_by_table
    Database: performance_schema
  Field Type: TABLE
     Matches: table_lock_waits_summary_by_table

• mysqlprocgrep – 指定類型找show processlist的資訊。

3. 高可用

• mysqlfailover – 為MySQL 5.6.5以後的MySQL資料庫做自動故障移轉，我個人認為這是MySQL Utilities最有趣的一部份，它能監測主從複製架構中各資料庫的健康狀況(特別是主資料庫)，在主資料庫不能使用時自動故障移。要使該程式發生作用，必需將GTID(Global Transaction Identifier)開啟，也就是在my.cnf檔串加上GTID_MODE=ON的參數配置，用戶還以設定—interval參數以秒為單位指定多久去測一次主數据庫的狀況，再以—ping參數設定多少次測試沒回應，就以—failover-mode參數所指定的故障移轉方式進行故障移轉。--failover-mode可指定的故障移轉方式有：

1. auto：依mysqlfailover所指定的故障移轉侯選資料庫順序自動做故障移轉，如果在故障移轉序列中沒有找到可用的資料庫，則到所有的從服務器中找故障移轉的資料庫；當故障移轉到從資料庫，且將之升級為主資料庫，其他的從資料庫會將此新的(剛升級的)主資料庫設為主資料庫。如果完全找不到可以作故障移轉的從資料庫，則會產生一個錯誤訊息再結束該命令。
2. elect：和auto類似，只是當主資資料庫當掉時，自故障移轉侯選資料庫序列中找不到適合的資料庫，則不會找其他的從資料庫，直接丟錯誤訊息，然後結束該命令。
3.  fail：主資料庫當掉時，不做故障移轉，直接丟錯誤訊息，然後結束該命令。

mysqlfailover的使用範例如下：

$ mysqlfailover --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1
# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
# WARNING: You may be mixing host names and IP addresses. This may result in negative status reporting if your DNS services do not support reverse name lookup.
#
# Failover console will start in 10 seconds.

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Mon Jan 21 11:34:06 2013

Master Information
------------------
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB  
ivan-ubuntu-bin.0000  231                                       

當mysqlfailover成功開啟後，在console上可看到以下訊息，它顯示mysqlfailover定期(此處為10秒)ping主資料庫和從資料庫。

GTID Executed Set
CE495FF3-5965-11E2-8479-0023184A0390:1-10 [...]

Replication Health Status
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1  | 3306  | MASTER  | UP     | ON         | OK      |
| localhost  | 3308  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3310  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

上列指令會自動找連上該指定的主資料庫之從資料庫，當成故障移轉序列資料庫；在mysqlfailover的console上可觀查到有一個主資料庫在IP為127.0.0.1，佔用端口為3306，兩個從資料庫分別在localhost，佔用端口為3308及3310。但是要注意的是，在該指令生效前必需在my.cnf中加上以下參數：

report-host=name>
report-port=#>
report-user=>

report-password=
relay-log-info-repository=table
master-info-repository=table
對上列參數的說明請參考http://dev.mysql.com/doc/refman/5.6/en/slave-logs.html

當故障移轉發生時－例如關掉主資料庫，可在mysqlfailover console上看到下列訊息，它顯示mysqlfailover開始做故障移轉，且挑在localhost用3308的從資料庫升級成主資料庫。
Failover starting in 'auto' mode...
# Candidate slave localhost:3308 will become the new master.
# Preparing candidate for failover.

MySQL Replication Failover Utility
Failover Mode = auto     Next Interval = Mon Jan 21 11:47:40 2013

Master Information
------------------
Binary Log File       Position  Binlog_Do_DB  Binlog_Ignore_DB  
ivan-ubuntu-bin.0000  271                                       

當故障移轉完成時， console上可看到以下訊息，它顯示新的主資料庫在localhost，所用的端口為3308；

GTID Executed Set
68E65F0B-5A40-11E2-8A0B-0023184A0390:1-4 [...]

Replication Health Status
+------------+-------+---------+--------+-----------+---------------------------------------------------------------------+
| host       | port  | role    | state  | gtid_mode |health                                                              |
+------------+-------+---------+--------+-----------+---------------------------------------------------------------------+
| localhost  | 3308  | MASTER  | UP     | ON   |OK                                                                  |
| localhost  | 3310  | SLAVE   | UP     | ON         | SQL thread is not running., Slave has 1 transactions behindmaster. |
+------------+-------+---------+--------+-----------+---------------------------------------------------------------------+

mysqlreplicate – 配置主從複製，該指令可使我們不需登入MySQL客戶端直接設定主從複製架構，範例如下：

$ mysqlreplicate --master=root:welcome1@127.0.0.1:3306 --slave=root:welcome1@127.0.0.1:3308 --master-log-file=ivan-ubuntu-bin.000014 --master-log-pos=231 –rpl-user=root

# master on 127.0.0.1: ... connected.
# slave on 127.0.0.1: ... connected.
# Checking for binary logging on master...
# Setting up replication...

# ...done.

• mysqlrpladmin – MySQL 5.6.5 為MySQL 5.6.5做主從複製管理、切換、故障移轉；這是除了mysqlfailover之外另一個很用的工具。它使我們能簡化主從複製資料庫的管理工作，能應用系統的交易自主資料庫切換到指定的從資料，同時在切換時會鎖定資料庫，使它不會丟失任何交易；在把交 易切回原主資料庫時，會等原主資料庫的資料都補齊時才會切回來。

    本工具的指令如下:

• elect：根据GTID自一序列從資料庫中選一個資料庫當成switchover或failover的對象(找GTID跟得最近的)

•  failover：依elect或指定的從資料庫做故障移轉

範例如下:
$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1 failover

# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
# Performing failover.
# Candidate slave localhost:3308 will become the new master.
# Preparing candidate for failover.
# Creating replication user if it does not exist.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Starting slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Failover complete.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| localhost  | 3308  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

• gtid：找出指定主資料庫及其從資料庫目前的GTID

範例如下：
$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1 gtid
# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
#
# UUIDS for all servers:
+------------+-------+---------+---------------------------------------+
| host       | port  | role    | uuid                                  |
+------------+-------+---------+---------------------------------------+
| 127.0.0.1  | 3306  | MASTER  | ce495ff3-5965-11e2-8479-0023184a0390  |
| localhost  | 3308  | SLAVE   | df6e1330-5965-11e2-847a-0023184a0390  |
| localhost  | 3310  | SLAVE   | 68e65f0b-5a40-11e2-8a0b-0023184a0390  |
+------------+-------+---------+---------------------------------------+
#
# Transactions executed on the server:
+------------+-------+---------+----------------------------------------------+
| host       | port  | role    | gtid                                         |
+------------+-------+---------+----------------------------------------------+
| 127.0.0.1  | 3306  | MASTER  | CE495FF3-5965-11E2-8479-0023184A0390:1-11    |
| 127.0.0.1  | 3306  | MASTER  | DF6E1330-5965-11E2-847A-0023184A0390:1-2     |
| localhost  | 3308  | SLAVE   | 68E65F0B-5A40-11E2-8A0B-0023184A0390:1-4     |
| localhost  | 3308  | SLAVE   | CE495FF3-5965-11E2-8479-0023184A0390:1-11    |
| localhost  | 3308  | SLAVE   | DF6E1330-5965-11E2-847A-0023184A0390:1-17    |
| localhost  | 3310  | SLAVE   | 68E65F0B-5A40-11E2-8A0B-0023184A0390:1-5313  |
| localhost  | 3310  | SLAVE   | CE495FF3-5965-11E2-8479-0023184A0390:1-11    |
| localhost  | 3310  | SLAVE   | DF6E1330-5965-11E2-847A-0023184A0390:1-4     |
+------------+-------+---------+----------------------------------------------+

• health：列示指定的主資料庫和其從資料庫的狀況

範例如下:
$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1 health
# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1  | 3306  | MASTER  | UP     | ON         | OK      |
| localhost  | 3308  | SLAVE   | UP     | ON         | OK      |
| localhost  | 3310  | SLAVE   | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

• reset：清掉所有的主從複製的設定，相當於在所有的從資料庫上下'stop slave; reset slave all;'的指令

範例如下：
$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --discover-slaves-login=root:welcome1 reset
# Discovering slaves for master at 127.0.0.1:3306
# Checking privileges.
# Performing STOP on all slaves.
#   Executing stop on slave localhost:3308 Ok
#   Executing stop on slave localhost:3310 Ok
# Performing RESET on all slaves.
#   Executing reset on slave localhost:3308 Ok
#   Executing reset on slave localhost:3310 Ok
# ...done.

• start：開始主從複製，相當於在所有的從資料庫下'start slave;'

• stop：關掉主從複製，相當於在所有的從資料庫下'stop slave;'

• switchover：主從複製架構中，主從角色對掉

範例如下:

$ mysqlrpladmin --master=root:welcome1@127.0.0.1:3306 --new-master=root:welcome1@127.0.0.1:3308 switchover
# Checking privileges.
# Performing switchover from master at 127.0.0.1:3306 to slave at 127.0.0.1:3308.
# Checking candidate slave prerequisites.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------+-------+---------+--------+------------+---------+
| host       | port  | role    | state  | gtid_mode  | health  |
+------------+-------+---------+--------+------------+---------+
| 127.0.0.1  | 3308  | MASTER  | UP     | ON         | OK      |
+------------+-------+---------+--------+------------+---------+

在執行前要確認複製的設定都正，例如，配置成兩個主資料庫，要清乾淨原來的從資料庫設定要用'reset slave all;'指令

• mysqlrplcheck – 檢查主從複製的配置，範例如下：

$ mysqlrplcheck --master=root:welcome1@127.0.0.1:3308 --slave=root:welcome1@127.0.0.1:3310 –show-slave-status

# master on 127.0.0.1: ... connected.
# slave on 127.0.0.1: ... connected.
Test Description                                                     Status
---------------------------------------------------------------------------
Checking for binary logging on master                                [pass]
Are there binlog exceptions?                                         [pass]
Replication user exists?                                             [pass]
Checking server_id values                                            [pass]
Checking server_uuid values                                          [pass]
Is slave connected to master?                                        [pass]
Check master information file                                        [pass]
Checking InnoDB compatibility                                        [pass]
Checking storage engines compatibility                               [pass]
Checking lower_case_table_names settings                             [pass]
Checking slave delay (seconds behind master)                         [pass]

#
# Slave status: 
#
                Slave_IO_State : Waiting for master to send event
                   Master_Host : localhost
                   Master_User : root
                   Master_Port : 3308
                 Connect_Retry : 60
               Master_Log_File : ivan-ubuntu-bin.000010
           Read_Master_Log_Pos : 271
                Relay_Log_File : ivan-ubuntu-relay-bin.000001
                 Relay_Log_Pos : 4
         Relay_Master_Log_File : 
              Slave_IO_Running : Yes
             Slave_SQL_Running : No
               Replicate_Do_DB : 
           Replicate_Ignore_DB : 
            Replicate_Do_Table : 
        Replicate_Ignore_Table : 
       Replicate_Wild_Do_Table : 
   Replicate_Wild_Ignore_Table : 
                    Last_Errno : 1593
                    Last_Error : Failed during slave workers initialization
                  Skip_Counter : 0
           Exec_Master_Log_Pos : 0
               Relay_Log_Space : 7705
               Until_Condition : None
                Until_Log_File : 
                 Until_Log_Pos : 0
            Master_SSL_Allowed : No
            Master_SSL_CA_File : 
            Master_SSL_CA_Path : 
               Master_SSL_Cert : 
             Master_SSL_Cipher : 
                Master_SSL_Key : 
         Seconds_Behind_Master : None
 Master_SSL_Verify_Server_Cert : No
                 Last_IO_Errno : 0
                 Last_IO_Error : 
                Last_SQL_Errno : 1593
                Last_SQL_Error : Failed during slave workers initialization
   Replicate_Ignore_Server_Ids : 
              Master_Server_Id : 2
                   Master_UUID : df6e1330-5965-11e2-847a-0023184a0390
              Master_Info_File : mysql.slave_master_info
                     SQL_Delay : 0
           SQL_Remaining_Delay : None
       Slave_SQL_Running_State : 
            Master_Retry_Count : 86400
                   Master_Bind : 
       Last_IO_Error_Timestamp : 
      Last_SQL_Error_Timestamp : 130121 11:42:31
                Master_SSL_Crl : 
            Master_SSL_Crlpath : 
            Retrieved_Gtid_Set : DF6E1330-5965-11E2-847A-0023184A0390:5-17
             Executed_Gtid_Set : 68E65F0B-5A40-11E2-8A0B-0023184A0390:1-5313,
CE495FF3-5965-11E2-8479-0023184A0390:1-10,
DF6E1330-5965-11E2-847A-0023184A0390:1-4
# ...done.

• mysqlrplshow – 列示複製結構的拓樸圖，範例如下：

$ mysqlrplshow --master=root:welcome1@127.0.0.1:3308 --discover-slaves-login=root:welcome1
# master on 127.0.0.1: ... connected.
# Finding slaves for master: 127.0.0.1:3308

# Replication Topology Graph
127.0.0.1:3308 (MASTER)
   |
   +--- 127.0.0.1:3310 - (SLAVE)

• mysqlserverclone – 在空的資料庫中以現有的資料庫為範本建一個新的資料庫，範例如下：

$ mysqlserverclone --server=root:welcome1@localhost:3306 --new-data=./data4 --new-port=3312 --new-id=4 --root-password=welcome1
# Cloning the MySQL server running on localhost.
# Creating new data directory...
# Configuring new instance...
# Locating mysql tools...
# Setting up empty database and mysql tables...
# Starting new instance of the server...
# Testing connection to new instance...
# Success!
# Setting the root password...
# Connection Information:
#  -uroot -pwelcome1 --socket=/home/ivan/data4/mysql.sock
#...done.

我原本以為會以指定的資料庫instance為範本－在localhost上用3306端口，完全複製同樣內容的資料庫instance到./data4，同時用3312為端口；但是出乎我意料之外的是，我只看到一個空的資料庫instance，只有預訊的資料庫 －如mysql,information_schema，先前我在第一個資料庫instance以手動方式建的資料庫在新的資料庫完全看不到。

• mysqlserverinfo – 列示資料庫伺服器的資訊，範例如下：

$ mysqlserverinfo --server=root:welcome1@127.0.0.1:3308 --format=vertical
# Source on 127.0.0.1: ... connected.

*************************       1. row *************************
         server: 127.0.0.1:3308
        version: 5.6.9-rc-log
        datadir: /mysql56/data2/
        basedir: /home/ivan/mysql-5.6.9-rc-linux-glibc2.5-i686
     plugin_dir: /home/ivan/mysql-5.6.9-rc-linux-glibc2.5-i686/lib/plugin/
    config_file: /etc/mysql/my.cnf, /home/ivan/mysql-5.6.9-rc-linux-glibc2.5-i686/my.cnf
     binary_log: ivan-ubuntu-bin.000010
 binary_log_pos: 271
      relay_log: ivan-ubuntu-relay-bin.000001
  relay_log_pos: 4
1 rows.
#...done.
 

Oracle MySQL/Linux/Virtual Machine (LVM) Integrated Demo Script

Objective
To show that MySQL runs on top of Oracle Virtual Machine and Oracle Enterprise Linux would maximize the value for the customers by offering the lowest total cost of ownership, the highest performance, scalability and productivity. This Oracle LVM integrated demo includes several software/techinques from Oracle, they are MySQL Enterprise Monitor, MySQL Database, MySQL replication, MySQL Proxy, Oracle Virtual Machine, Oracle Virtual Machine Template and Oracle Linux. Those products would bring the following the users:
MySQL Enterprise Monitor helps MySQL DBA managing the database, improving performance, and increase DBA's skill. It maximize the SLA of the database and help the DBAs increase their productivity.
MySQL Proxy enable scale out solution by offering read-write splitting features in the most cost effective manner.
MySQL replication helps the user increase availability and capacity of the database in a lowest cost.
The user would enjoin the best performance by adopting MySQL on top of Oracle Enterprise Linux.
Oracle Virtual Machine template for MySQL increase the productivity of the DBA by per-configured/optimized MySQL database, and decrease the resource needed for deploying MySQL.
Oracle Virtual Machine would help the users with maximize HA from virtual machine layer, it helps MySQL application get zero down time, when migrating the virtual machine between different servers.

Demo Environment

The Servers of the Demo Environment
Install and Configure the Demo Environment
As above graph shows, we apply 3 physical servers – ovmm, ov1, ovs2, ovmm contains OVM Mangaer, PHP pages that act as MySQL client application, and MySQL Enterprise Monitor, ovs1 and ovs2 need to be registered on Oracle Virtual Machine manager as virtual machine server, ovs2 contains one virtual machine for master database, and one virtual machine for first slave, ovs2 contains one virtual machine for 2nd slave database, and ovs2 also act as the backup server for the master database, we will demo the fail-over features by live migration on the Virtual Machine layer. The details of install OVM with HA please refer the document http://www.rachelp.nl/userfiles/file/Create_oraclevm_haserverpool.pdf and. There are several points need to take care of while installing the environment, I list them as below:
For high availability from the virtual machine layer, we need to configure HA options with OVM servers pool on OVM manager, and the servers that support HA need to attach to a shared storage, in this case, it is NFS, and we need to add read_write, sync, and no_root_squash options when doing NFS export, refer http://download.oracle.com/docs/cd/E15458_01/doc.22/e15440/toc.htm#BABDEFDG for the details.
MySQL virtual machine template will runs firewall when ever is started, we need to turned if off manually.
To enable replication and MySQL Enterprise Monitor Agent, the time lag between each virtual machine servers could not larger than a certain period, for say 15 seconds.
The available space on the shared storage need to larger than 70 GB.
For some cases when restart virtual machine the option file - /etc/my.cnf could reset to the default value.
Demo Flow
1 Show MySQL, OVM, Oracle Linux, Oracle Virtual Machine, Oracle Virtual Machine Template for MySLQ download address on Oracle eDelivery.
2 OVM/OEL Demo

• Navigate OVM interface.

• Create/Initiate virtual machine with Oracle Virtual Machine Template for MySQL (the template was imported on the “Resource” tab).

OVM template for MySQL offers the users with a configured/tuned MySQL environment, we do not need to tune MySQL options and it can be a base for customized template to foster the deploying speed. For example, we can create template for slave servers from the standard OVM template for MySQL, so we can easily extend the capacity by adding more slave servers from the customized OVM MySQL slave template.3 MySQL Demo

• Show slide 17 of MySQL technical presentation, explain the demo scenario to the audience

• Show MySQL master on 10.186.122.69

ssh root@10.186.122.69, password: “oracle”
login MySQL master server, with username “root” and pssword “oracle” by
mysql -h 10.186.122.69 -P 3306 -u root -p oracle
use world;
show tables;
select code, name, continent, population, gnp from Country;

• Show MySQL slave 1 on 10.186.122.72 and slave 2 on 10.186.122.174

login MySQL slave 1 with usrname “root”, password “oracle”
mysql -h 10.186.122.72 -P3306 -u root -p oracle
show slave status\G
use world;
show tables;
select code, name, continent, population, gnp from Country;
login MySQL slave 1 with usrname “root”, password “oracle”
mysql -h 10.186.122.174 -P3306 -u root -p oracle
show slave status\G
use world;
show tables;
select code, name, continent, population, gnp from Country;

• Show MySQL Proxy on ovmm.sg.oracle.com

ssh root@ovmm.sg.oracle.com, password “Pass0rd”
ps -ef | grep proxy (show MySQL Proxy command)

• Show PHP demo application source code on ovmm.sg.oracle.com

vi /var/www/html/worldApp/application/phpQueryPage.php and /var/www/html/worldApp/application/phpUpdatePage.php
high light the code of
$con = mysql_connect("127.0.0.1:4040", "root", "oracle");
show the audience that the database connection is through MySQL Proxy, and read-write splitting is accomplished by this service.

• Display phpQueryPage page from browser, the URL is below:

http://ovmm.sg.oracle.com/worldApp/application/phpQueryPage.php
show audience the host name on the page, it will change to the other slave when the current slave server is down (it shows slave 1 at this moment)
shutdown slave 1 (shutdown virtual machine – slave 1 is an approach that close to the real case, but it take more time, shutdown database would be fast, it should be easier to keep the pace of demo)
show audience the host name on the page, it had changed to the slave 2 at this moment

• Demo the master slave replication feature by:

Show phpUpdatePage.php
Query the data by type “HKG” in the “Code” field, and press enter
Insert one record by changing the “Code” field form “HKG” to “ABX”, and press “Insert” button, and query the new record out by type “ABX” on the “Code” filed then press “Query” button.
Back to phpQueryPage.php, the country code “ABX” shows on the query page

• Demo the live migration feature, and MySQL HA supported by the feature

Ping 10.186.122.69 from ovmm.sg.oracle.com, show the response and message sequence numbers displayed on the terminal
Live migration from OVM interface, from OVM manager by pick “Live Migration” from the “More Action” list.

Show “ping ”, there is a gap with the message sequence number from the ping output after live migration, but the interruption is very small (less than 2 seconds)
Show the message sequence number leap, but the phpUpdatePage.php works fine after live migration, the user interface has no interruption for the migration on the virtual machine layer.

Benchmark MySQL 5.5.9 on Oracle Sun X4170 M2

As I posted the testing result from MySQL on HP server (refer to the previous benchmark), my colleagues from Oracle System Practice are very interesting in MySQL performance on Sun's x86 servers (of course we hope we can proof the servers from Oracle are superior than HP). With the help from our VAD partner - Bestcom, we got a Sun X7140 M2 installed in our data center, thanks for the endeavor form my colleague Cano Lai, the server was setup and and ready for testing very soon.

Technical Specification

The configuration of the system as below:

Server

• CPU: 6 Core Intel E5640 * 2, 2 threads with each core, 12 Cores/24 Threads in total
• Memory: 16G Bytes
• Hard Disk: 146GB * 2

Operating System

Red Hat Enterprise Linux 5.6, with base kernel ( Linux Kernel 2.6.18-238.el5 ) 1st run, Oracle Enterprise Kernel ( Linux Kernel 2.6.32-100.26.2 el5) for the 2nd run.

Database

MySQL 5.5.9 Community Edition with InnoDB storage engine

Tasks

• Setup Sun X4170 M2
• Install Red Hat Linux 5.6
• Install MySQL 5.5.9

We downloaded MySQL for Red Hat & Oracle Enterprise Linux 5 (x86, 64-bit) from MySQL download site, apart from MySQL server, shared component is needed for compiling Sysbench.

• Install Sysbench 0.4.12

Following the installion steps on Sysbench web site http://sysbench.sourceforge.net/docs/#install, would get things done easily, the only thing that need to take care of is to install MySQL shared component in advanced, and let the software and libraries should be accessible.

• Tune MySQL

Some of tuned options in my.cnf file are list as below:

# The number of threads might be more than 1000, set to 1500 to accommodate it
max_connections = 1500
# The server has 16 GB RAM, evaluate the overhead from OS and Sysbench,
# 12 GB innodb buffer pool, could be fully utilize the available memory
innodb_buffer_pool_size = 12G
# Multiple innodb buffer pools could reduce contention for some of resources such as
# free lists, flush lists, LRUs and buffer pool mutex
innodb_buffer_pool_instance=3
# New file format from MySQL 5.5
innodb_file_format=Barracuda
innodb_file_per_table
# Increase the number of threads for writing dirty pages
innodb_write_io_threads = 8
# Increase the number of I/O threads for read operations
innodb_read_io_threads = 8
# Increase the number of innodb_thread_concurrency to fully utilize the multiple
# cores/threads environment
innodb_thread_concurrency = 24
# innodb_flush_log_at_trx set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. This a conservative approach.
# If set this to 0 or 2 might reduce disk I/O and increase the measurements of the test.
innodb_flush_log_at_trx_commit = 1

• Test against MySQL on Red Hat 5.6 based kernel with Sysbench

The following command would generate data for the test, we had 100 million rows in the largest table.

nohup sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000000 --mysql-user= --mysql-password= prepare

Execute the test with the following command, and collect the test results from the outputs of nohup

nohup sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=100000000 --num-threads= --mysql-user= --mysql-password= run

• Substitute Red Hat 5.6 based kernel with Oracle Enterprise Linux Kernel

Apart from MySQL performance on Oracle server, we were curious if there is any difference between the Based Kernel and Oracle Unbreakable Kernel. There was time available after the 1st run, we decided to have the same benchmark on the 2nd run which had the same configuration with the previous run, but replace the OS kernel with Oracle UEK. To upgrade the kernel is not a difficulty job, the required command as below:

# cd /etc/yum.repos.d/
# wget http://public-yum.oracle.com/public-yum-ol6.repo
# wget http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6
# yum -y install kernel-*

After reboot the server, you will see the kernel had been upgraded with the following command:

# uname -a
# yum info kernel-uek

• Test against MySQL on Oracle Enterprise Kernel

The steps are the same as task 6.

• Consolidate the testing results Collect the number of read/write and transactions with each run, and generate line charts.

Testing Results

The benchmark result summarized as chart 1 and Chart 2, for the scale of transaction per second was too small, I created the 3rd chart to depict it.

• Chart 1 is the measurement for read-only transaction, MySQL on OEL got more than 54,000 operations at 900 threads while with everything the same except Red Hat, we got around 20,000 read-only operations and down to 11,000 at 900 threads.
• In Chart 2, the red line is number of read/write operations with Red Hat based Kernel, the number of average read/write was 15,426 compare the measurement from the previous benchmark of average 4,033 read/write operations, this 382% improvement is a very one improvement (although the power of the server was better the previous one).
• In Chart 2, the purple line shows the number of read/write operations with Oracle Unbreakable Kernel, it performed much better than the original kernel, Oracle UEK start from 32,110 and reach to the peak of 39,941 at 700 threads. On the contractually, Red Hat Based Kernel got 20,493 at the smallest number of threads and slipped down onward. Oracle UEK get 36,200 operations which is 234% improvement! And if we compare the measure from the previous benchmark, with technical stack from Oracle (hardware and OS kernel) we can enjoin near 9 times throughput.
• The number of transaction shows in chart 3, it is consistent to the chart 1, UEK start from 1,690 transaction per second, creep to 2,102 at 700 threads, however Based Kernel start from 1,078 and slip down there after, the gap extended to 1,343 at 700 threads.

Chart 1. Read-only benchmark

Chart 2. Read-Write Benchmark

Chart 3. Read-write benchmark with transaction per second measurement

Conclusion
In the passed year, MySQL had lots of improvements, they enable MySQL to be the database that support mission critical applications. We hope the testing would help us figure out the right technical combination (from hardware, operating system to the database) for our customers. The result of this benchmark shows Oracle technical stack would offer superior performance for the user. With Oracle Linux we got near 300% performance improvement when the number of concurrent connection is 900, and compare with the result form previous benchmark, we got almost 9 times throughput, although the configuration of the hardware is better, it's still a significant improvement.
However I did not apply any performance analyze tool (for example sar) to collect the performance related information from the operating system layer, thus I have no idea what makes this huge difference between Red Hat based Kernel and Oracle UEK, hope I could have the opportunity to run the same benchmark with performance analyze tool.