InnoDB 的 Isolation Level 以及 Performance Schema 對效能的影響

雖然 Mark Callaghan 現在的主力都在 MyRocks 上,但他還是對 InnoDB 上的效能頗關注 (畢竟是個成熟而且競爭的產品)。而這篇「Sysbench, InnoDB, transaction isolation and the performance schema」講到 MySQL 5.6.26 裡的 InnoDB,了解 isolation level 與 performance schema 對效能的差異。結果可以在這邊翻到。

關掉 performance schema 會讓效能變好是預期的,不過看起來比預期小很多。另外某些情況下 RR (REPEATABLE-READ) 的效能會比 RC (READ-COMMITTED) 好倒是頗意外,這邊也有給出原因:

Using repeatable-read boosts performance because it reduces the mutex contention from getting a consistent read snapshot as that is done once per transaction rather than once per statement.

不過看了看數據,純粹讀取的部份 RC 會在某些地方快一些,不過整體來說在 MySQL 5.6.26 上的 RR 與 RC 差異真的不算太明顯了...

Amazon Aurora 支援 Reader Endpoint

Amazon Aurora 支援 Reader Endpoint,讓讀的部份可以打散掉:「New Reader Endpoint for Amazon Aurora – Load Balancing & Higher Availability」。

讀的部份比較容易 scale (常見的方式是透過 replication 做到),而現在很多 database framework (包括各類的 ORM framework) 都支援讀寫分離,這個支援對於系統的 scale 來說幫忙頗大。

不過不知道會不會有 replication lag 的問題,我猜是會有...

Amazon Aurora 支援跨區域 Read Replica

傳統的作法 (自己擁有機房時) 就是在兩個機房中間建立 site-to-site VPN (通常是 IPsec),然後再用 MySQL 的 replication 接起來,而現在 Amazon Aurora 直接在界面上支援這件事情,可以在網頁上點一點開起來:「New – Cross-Region Read Replicas for Amazon Aurora」。

不過好像沒講到跨區的 VPC 對接設定問題?所以是透過他們自己的 network 接起來?這樣的話流量費用要怎麼算?

MariaDB 讀寫分離的工具:MaxScale

MariaDBMaxScale 軟體提供 MySQL 相容的 proxy interface,可以將後端一群 MySQL server 架構隱藏起來,讓應用程式不需要處理這部份。

Percona 的人則介紹 MaxScale 作為讀寫分離的工具:「High availability with asynchronous replication… and transparent R/W split」。

如果你是用有支援讀寫分離的 ORM (像是 Laravel 中的 Illuminate::Database),由於 ORM library 幫你處理好了,你可以省掉這個工作。

但在其他的情況,像是應用程式沒有原始程式碼,或是只能設一組 server,你就必須透過像 MaxScale 這種軟體來幫你打散負荷量。

Percona 給的範例提供了很多設定檔,應該是改一改就可以動 (當然效能調校是另外要花功夫的事情了),對於有興趣的人應該可以丟人研究?

PCIe 的 SSD 與 SATA 的比較

LogicMonitor 的人比較了 PCIe SSD 與 SATA SSD,他們在意的重點是 read/write latency 非單純的 throughput:「Device Utilization of PCIe and SATA SSDs」。

文章裡講得很長,把他們找原因的過程寫出來,從 latency 的影響改變到 queue service 的變化:

後來換成 PCIe SSD 後 write latency 從 1.8ms 掉到 0.02ms 左右,大約是兩個零的差距。

另外文章裡也提到了 fio 這個測試工具,找時間來測試看看,熟悉一下...

繼續學 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 的要求。

測試 MariaDB 上 Galera Cluster 的 Isolation

雖然人還在大阪員旅,不過看到這篇很精彩的測試還是先寫下來:「Call me Maybe: MariaDB Galera Cluster」,作者在 Stripe 花了很多時間在測試各種資料庫的資料正確性。

由於 PerconaPercona XtraDB Cluster (PXC) 是基於 Galera Cluster 改出來的,再加上敝公司裡面大量使用 PXC,決定還是趕緊花時間看完整篇文章確認到底講了什麼。

先講目前看到的結論:

  • 作者認為 Galera Cluster 官方宣稱的 SNAPSHOT-ISOLATION (SI) > REPEATABLE-READ (RR) 是有問題的講法,在學理上 SI 與 RR 都低於 SERIALIZABLE (1SR),也都高於 READ-COMMITTED (RC),但兩者之前沒有直接包含。
  • Galera Cluster 的測試結果看起來沒有做到 SI 的要求,不過測試中看起來有達到 READ-UNCOMMITTED (RU),在 RC 的測試中也沒找到問題,不過作者還是懷疑系統有達到 RC 的要求。

另外從其他討論串看起來,當所有人寫入都到同一台就沒有這些狀況,可以達到 1SR 的要求,這也是目前還蠻常見的系統設計,所以這次的問題看起來沒有這麼大。

後面也提到裝 MySQL Cluster 裝了好幾個禮拜還是裝不起來的事情:

Galera is easy to install–I spent weeks trying to set up MySQL Cluster to no avail, and got Galera Cluster running in a matter of hours.

有種,不是很意外的感覺...

Anyway,原文下面的 comment 可以看到 Baron Schwartz 也跑出來討論,另外在 GitHub 上有開始有討論了:「Snapshot Isolation may not be as isolated as one would like」,看起來直接戳破 Galera Cluster 支援 SI 的宣傳詞了,接下來拉板凳繼續看戲吧... XD

Percona 對 mysql_query_cache 的測試 (以 Magento 為例)

Percona 的人以現在的觀點來看 mysql_query_cache:「The MySQL query cache: Worst enemy or best friend?」。

起因主要也是懷疑 query cache 是 global mutex 在現在的硬體架構 (主要是 CPU 數量成長) 應該是個負面的影響,但不確定影響多少:

The query cache is well known for its contentions: a global mutex has to be acquired for any read or write operation, which means that any access is serialized. This was not an issue 15 years ago, but with today’s multi-core servers, such serialization is the best way to kill performance.

這邊就有點怪了,PK search 應該是個位數 ms 等級才對 (一般 EC 網站的資料量都應該可以 memory fit),不知道他是怎麼測的:

However from a performance point of view, any query cache hit is served in a few tens of microseconds while the fastest access with InnoDB (primary lookup) still requires several hundreds of microseconds. Yes, the query cache is at least an order of magnitude faster than any query that goes to InnoDB.

anyway,他實際測試兩個不同的 configuration,首先是打開 query cache 的:

再來是關閉 query cache 的:

測試的方式在原文有提到,這邊就不抄過來了。測試的結果可以看到關閉 query cache 得到比較好的 thoughput:

Throughput scales well up to somewhere between 10 and 20 threads (for the record the server I was using had 16 cores). But more importantly, even at the higher concurrencies, the overall throughput continued to increase: at 20 concurrent threads, MySQL was able to serve nearly 3x more queries without the query cache.

跟預期的差不多,硬體的改變使得演算法也必須跟著改,不然就會遇到問題...

Amazon S3 的改善

在「Amazon S3 Introduces New Usability Enhancements」這邊提到了 Amazon S3 的兩個改善。

第一個是業務面的改善,以前應該是開 support ticket 請人調整 S3 bucket 數量上限,現在則是可以直接透過界面申請?(沒有遇過瓶頸,不知道以前是不是在界面上看不到...)

第二個才是重頭戲:Read-after-write Consistency。

With this enhancement, Amazon S3 now supports read-after-write consistency in all regions for new objects added to Amazon S3.

也就是說,Amazon S3 現在保證「新增的 object」「可以在建立後馬上被讀取」。之前還沒修正前,這個問題有多嚴重呢?可以看 2014 年時「Netflix 對 S3 的 Eventually Consistency 的補強...」這邊 Netflix 在跑 PigHive 遇到的問題。

Netflix 這邊舉的例子是兩個 Pig cluster 在跑,其中 Pig-2 需要 Pig-1 跑出來的資料,在這次公告前,如果 Pig-1 的資料寫回 Amazon S3 時不會馬上出現,那麼 Pig-2 就會拿不完整的資料執行:

Pig-2 is activated based on the completion of Pig-1 and immediately lists the output directories of the previous task. If the S3 listing is incomplete when the second job starts, it will proceed with incomplete data.

而現在總算是保證新的 object 可以馬上被讀取,所以 Netflix 可以利用一個檔案列出所有的 filename,確保知道所有的檔案名稱... (LIST 指令還是 eventually consistent,所以這部份還是要自己處理)