Percona 分析在 AWS 上跑 Percona XtraDB Cluster 的效能 (I/O bound)

Percona 的人分析了在 Amazon EC2 上跑 Percona XtraDB Cluster (PXC) 效能 (I/O bound):「Best Practices for Percona XtraDB Cluster on AWS」。

先看他們做出來的圖:

直接跳到結論的地方。如果資料可以掉,用 i3 本地 storage 的效能是最好的,如果要資料不能掉,用 EBS 的 Provisioned IOPS SSD (io1) 的效能會比 General Purpose (gp2) 好很多。

另外 instance type 的選擇上,避免用 {i3,r4}.large,因為測試出來發現 {i3,r4}.xlarge 的效能好不只一倍。

不過 Aurora 的 Multi-master 已經在 Preview 了啊,如果 Percona 的人拿到帳號的話,應該會有單位成本的效能比較可以看...

Percona XtraDB Cluster 解釋最近的版本為什麼可以再度提昇效能

Percona XtraDB Cluster 最近的一個版本宣稱效能大幅提昇,這篇試著解釋原因:「How We Made Percona XtraDB Cluster Scale」。

裡面提了三個情境,解釋了這些效能問題以及解法,進而帶出這次的效能提昇:

With these three main optimizations, and some small tweaks, we have tuned Percona XtraDB Cluster to scale better and made it fast enough for the growing demands of your applications. All of this is available with the recently released Percona XtraDB Cluster 5.7.17-29.20. Give it a try and watch your application scale in a multi-master environment, making Percona XtraDB Cluster the best fit for your HA workloads.

查看 Percona XtraDB Cluster 在 IST 時的進度

Percona 的「Tracking IST Progress in Percona XtraDB Cluster」這篇介紹了 PXC 在 IST 時的進度。

是個新功能,很簡單的可以看到目前進度,也可以拿來當作 health check 的一部分,避免開機剛起來時開始提供服務:

mysql> show status like 'wsrep_ist_receive_status';
+--------------------------+--------------------------------------------------------+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------+
| wsrep_ist_receive_status | 3% complete, received seqno 1421771 of 1415410-1589676 |
+--------------------------+--------------------------------------------------------+
1 row in set (0.00 sec)
....
mysql> show status like 'wsrep_ist_receive_status';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| wsrep_ist_receive_status | 52% complete, received seqno 1506799 of 1415410-1589676 |
+--------------------------+---------------------------------------------------------+
1 row in set (0.00 sec)
....
mysql> show status like 'wsrep_ist_receive_status';
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| wsrep_ist_receive_status | 97% complete, received seqno 1585923 of 1415410-1589676 |
+--------------------------+---------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show status like 'wsrep_ist_receive_status';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| wsrep_ist_receive_status | |
+--------------------------+-------+
1 row in set (0.00 sec)

Percona XtraDB Cluster 5.7 的效能改善

在「Percona XtraDB Cluster 5.7.17-29.20 is now available」這邊提到了:

This release is focused on performance and scalability with increasing workload threads. Tests show up to 10 times increase in performance.

不過這邊沒提到是 MySQL 本身還是 Galera Cluster 實做的修正,或是 PXC 本身特有的 bug 被修正... (通常都是有 bug 造成效能很慢,然後修正後拉上來)

Galera Cluster 不必要的 SST 行為的改善

Percona 的人解釋了 Galera Cluster 為什麼在某些情況下會需要 SST (整份重傳) 而不是 IST (傳最後造成差異的部分),以及開發商在 3.19 版對應的改進:「Galera Cache (gcache) is finally recoverable on restart」。

原因在於 IST 所需要的記錄會在重開機時消失,所以當其他節點加入時沒辦法給,只好給 SST:

The DONOR node caches missing write-sets in Galera cache, but on restart this cache is wiped clean and restarted fresh. So the DONOR node doesn’t have a Galera cache to donate missing write-sets.

gcache.recover 這個參數打開後就會啟用記錄,另外也可以挑著開就好:

The user can set this option for all nodes, or selective nodes, based on disk usage.

另外最重要的是,之後的版本才會有,所以得繼續等...:

And yes, Percona XtraDB Cluster inherits this feature in its upcoming release.

Percona XtraDB Cluster 5.7 (Galera Cluster)

Percona 放出 PXC 5.7 了:「Percona XtraDB Cluster 5.7.14-26.17 GA is now available」。

主要的效能提昇還是基於 MySQL 5.7 上面,不過要注意的是有些預設值有改變 (尤其是 5.6 -> 5.7 最有名的 sync_binlog 設定),如果當初沒有特別指定的話,效能會掉不少,這點 Percona 整理了不少資料出來,可以翻一下 Percona 網站得到結果。

不過整體上改善了不少,在 5.6 需要一些手動設定的值以確保在 heavy loading 時不會拖垮系統效能 (也就是常說的「卡 query」),在 5.7 變得自動化了。像是 innodb_thread_concurrency 之前的建議都是設在 CPU logical threads 數量以確保當 threads 數量破千時還是有慢慢消化的能力 (讓 DBA 可以不用介入),在 5.7 不需要設定就跑得不錯了。

MySQL 5.7 中 InnoDB 的 innodb_page_size 在 SSD 硬碟上對效能的巨大影響

Percona 的「Small innodb_page_size as a performance boost for SSD」這篇文章裡提到了 MySQL 5.7 的 innodb_page_size 在 SSD 上對效能的差異,主要是這張圖的解釋:

先講一下標示的部份,有三個產品線 (都是 Samsung 的 SSD),中間的 sam850 是消費級的 SSD 硬碟 (所以不是本次重點),而 sam863 是企業級 SATA SSD,pm1725 則是企業級 PCI-e SSD。下方的 BP 指的是 Buffer Pool 大小,單位是 GB。左邊是速度,數字愈大愈好。

InnoDB 預設 16KB 的 page size,配合 SSD 大多都是 4KB 的 block size 後,效能的提昇非常巨大 (70% 的提昇),雖然既有的 InnoDB 要換過去會花不少功夫,但作者還是很建議評估:

I think a 70% performance gain is too significant to ignore, even if manipulating innodb_page_size requires extra work. I think it is worthwhile to evaluate if using different innodb_page_size settings help a fast SSD under your workload.

不過 comment 有不少額外重要的資訊。

有提到 Galera Cluster 目前有 bug,無法使用 4KB page size,可以在「Restarting a cluster with innodb_page_size=4096 segfaults」這邊看到 bug report。

另外有提到,使用 InnoDB Compression 的前提下,4KB 也許不是個好主意,用 8KB 也許是個方向:

You’re right – if you’re using Innodb Compression 4K base page is unlikely to be the good choice. Though we do not see Innodb compression (any of them) being used too frequently.

If using compression 8K base page size with 4K compressed page size might be good idea if 2x compression is routinely reached

不過我覺得應該還是有幫助才對 (可能提昇不高,但想了一下應該不會有負面的影響),之後有機會再測試看看吧 :o

Percona 宣佈把 Galera Cluster 納入 Enterprise 與 Premium Support 內

Percona 的「MySQL High Availability: The Road Ahead for Percona and XtraDB Cluster」這篇文章的前面講了很多東西,最主要就是要宣佈把 Galera Cluster 納入 Enterprise/Premium Support:

Today we’re taking the next natural step – we will no longer require customers to purchase Percona XtraDB Cluster as a separate add-on. Percona will include support for XtraDB Cluster and other Galera-based replication technologies in our Enterprise and Premier support levels, as well as our Percona Care and Managed Services subscriptions.

先前是分開賣的,現在包進去了。

繼續學 Isolation...

在「測試 MariaDB 上 Galera Cluster 的 Isolation」這邊與「從頭學一次 Isolation level 的 REPEATABLE-READ 與 SERIALIZABLE」對應了 Stripe 的「Call me Maybe: MariaDB Galera Cluster」以及 Percona 的「Clarification on “Call me Maybe: MariaDB Galera Cluster”」這兩篇文章,兩邊在空中交火...

而「Call me Maybe: Percona XtraDB Cluster」這篇是目前最新的一篇 (文章日期有點怪怪的,不應該是 2015/09/04)。

畢竟 Galera Cluster 是目前現在在用的產品,雖然跑起來也沒什麼大問題,但預先多了解一點總是比較保險。而要參與這場 Isolation 戰爭,又花了不少時間看幾份資料。(包括重頭讀的)

第一個當然是 1992 年發表的 SQL-92:「ISO/IEC 9075:1992, Database Language SQL」,在 SQL-92 定義了四個 Isolation 的層級,分別是 SERIALIZABLEREPEATABLE READREAD COMMITTEDREAD UNCOMMITTED

另外一篇常被拿出來討論的是 1995 年由微軟帶頭發表的「A Critique of ANSI SQL Isolation Levels」,主要是提出 Snapshot Isolation 的定義。

後來讀到覺得真的很怪的時候到 Stack Exchange 上的 Database Administrators 問:「Does SQL92's REPEATABLE-READ preclude Write Skew (A5B)?」,確認了我的想法。

這篇算是讀書心得吧,留給自己用的 :p

首先是 REPEATABLE READ 的定義是透過「現象」定義,只要符合這些條件就可以被稱作 REPEATABLE READ,這包括了保證不會出現 P1 ("Dirty read"):

1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-transaction T2 then reads that row before T1 performs a COMMIT. If T1 then performs a ROLLBACK, T2 will have read a row that was never committed and that may thus be considered to have never existed.

以及保證不會出現 P2 ("Non-repeatable read"):

2) P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

並且保證 Atomicity 以及 no updates will be lost:

The four isolation levels guarantee that each SQL-transaction will be executed completely or not at all, and that no updates will be lost.

只要做到這些條件,就可以說符合 SQL-92 的 REPEATABLE READ 要求。


另外一方面,在「A Critique of ANSI SQL Isolation Levels」定義的 SQL-92 認為 REPEATABLE READ 是透過 locking 機制實作的 (整篇的寫法的確是朝這個方向走),並且基於 locking mechanism 來推論後續的行為,所以這篇 paper 裡面很多說明是帶著這個大前提的。

而當時已經有資料庫是使用 MVCC 架構在實作,可以透過 read snapshot 的方式找出另外一條路。

舉例來說,InnoDB 對 REPEATABLE READ 實作的方式是透過 snapshot 實作:(出自「MySQL :: MySQL 5.6 Reference Manual :: 13.3.6 SET TRANSACTION Syntax」)

For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.

這樣避開了 P1、P2 (甚至多做避開到了 P3,不過這邊不重要),並且 Atomicity。而 Update Lost 問題則再透過 lock 機制避免。所以 InnoDB 的 REPEATABLE READ 其實比 SQL-92 的要求多做到了很多事情...


到這邊再回頭看原來的討論文章,才會發現 Isolation 裡有滿滿的地雷可以採 XD

從頭學一次 Isolation level 的 REPEATABLE-READ 與 SERIALIZABLE

在「測試 MariaDB 上 Galera Cluster 的 Isolation」這篇提到了 StripeKyle Kingsbury 寫了「Call me Maybe: MariaDB Galera Cluster」這篇文章,在討論 Galera Cluster 的 Isolation 問題。

Percona 的 CTO Vadim Tkachenko 寫了回應的文章:「Clarification on “Call me Maybe: MariaDB Galera Cluster”」。

看完後再跑去翻了一些資料,首先是 SQL 92 對 Isolation 的原始定義,尤其是對 REPEATABLE-READ 的定義。

REPEATABLE-READ 是透過 P2 ("Non-repeatable read") 來定義的,只要保證不會發生 P2 就是 REPEATABLE-READ 了:

P2 ("Non-repeatable read"): SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value or discover that the row has been deleted.

也就是說,T1 讀過的 row 只要在 T1 transaction 的期間內都保持一樣就可以。由於沒有多餘其他保證,所以有機會產生 P3 ("Phantom") 問題:

P3 ("Phantom"): SQL-transaction T1 reads the set of rows N that satisfy some . SQL-transaction T2 then executes SQL-statements that generate one or more rows that satisfy the used by SQL-transaction T1. If SQL-transaction T1 then repeats the initial read with the same <search condition>, it obtains a different collection of rows.

SERIALIZABLEREPEATABLE-READ 的定義方式不同,並不是避免 P3 就可以叫做 SERIALIZABLE。SQL 92 定義說明必須與序列執行的結果一樣才能叫做 SERIALIZABLE

The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins.

所以依照定義,SERIALIZABLE 一定可以避免 P3 發生,但避免了 P3 不代表做到 SERIALIZABLE

另外一份資料是「MySQL :: MySQL 5.6 Reference Manual :: 13.3.6 SET TRANSACTION Syntax」對於 MySQL 5.6 中 InnoDB 實作 REPEATABLE-READ 的細節:

This is the default isolation level for InnoDB. For consistent reads, there is an important difference from the READ COMMITTED isolation level: All consistent reads within the same transaction read the snapshot established by the first read. This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other. See Section 14.2.2.2, “Consistent Nonlocking Reads”.

InnoDB 的 REPEATABLE-READ 會在第一次讀取時建立一份 snapshot,所以 MySQL 的 REPEATABLE-READ 有達到不產生 P2 的要求,但也因為 snapshot 的關係而不會產生 P3,不過也就僅此而已,寫入的部份還是沒有達到 SERIALIZABLE 的要求。

回到原來的文章所整理出來的測試資料 (我稍微排版過),在這兩個不同的 transaction 同時發生時:(不同人都轉帳給 id = 8)

BEGIN; -- T1
SELECT balance FROM accounts WHERE account_id = 5;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = 75 WHERE account_id = 5;
UPDATE account SET balance = 125 WHERE account_id = 8;
COMMIT;
BEGIN; -- T2
SELECT balance FROM accounts WHERE account_id = 6;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = 80 WHERE account_id = 6;
UPDATE account SET balance = 120 WHERE account_id = 8;
COMMIT;

SERIALIZABLE 保證執行結果會與某種循序的結果相符 (可能是 T1 -> T2,也可能是 T2 -> T1)。但 REPEATABLE-READ 只保證在 transaction 裡面看到的 row 不會變化。

在 MySQL 裡,如果你想要跟 MySQL 告知「變更的結果會收到某些欄位的值的影響」,你至少要用 LOCK IN SHARE MODE 來做 (或是用 FOR UPDATE 會更清楚表示出意思),也就是:

BEGIN; -- T1
SELECT balance FROM accounts WHERE account_id = 5 LOCK IN SHARE MODE;
SELECT balance FROM accounts WHERE account_id = 8 LOCK IN SHARE MODE;
UPDATE account SET balance = 75 WHERE account_id = 5;
UPDATE account SET balance = 125 WHERE account_id = 8;
COMMIT;
BEGIN; -- T2
SELECT balance FROM accounts WHERE account_id = 6 LOCK IN SHARE MODE;
SELECT balance FROM accounts WHERE account_id = 8 LOCK IN SHARE MODE;
UPDATE account SET balance = 80 WHERE account_id = 6;
UPDATE account SET balance = 120 WHERE account_id = 8;
COMMIT;

這樣至少會有一個 transaction 會出現 deadlock 訊息,於是就會確保是正確的結果:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

值得一提的是,Percona 文章提到的第二種解法是有問題的:

BEGIN;
SELECT balance FROM accounts WHERE account_id = 5;
SELECT balance FROM accounts WHERE account_id = 8;
UPDATE account SET balance = balance - 25 WHERE account_id = 5;
UPDATE account SET balance = balance + 25 WHERE account_id = 8;
COMMIT;

這樣「總和」會因為保證原子性 (atomicity) 所以不會產生問題,但 balance 可能會產生出負數。

所以結論是 Kyle Kingsbury 那篇文章測試的方法本來就有問題,在 SELECT 時少了 LOCK IN SHARE MODE 或是 FOR UPDATE 的要求。