Home » Posts tagged "mariadb" (Page 2)

從頭學一次 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

MySQL 上的 Thread Pool...

Percona 的人寫了一篇「Percona Server: Improve Scalability with Percona Thread Pool」,提到關於 MySQL 在連線數很多時的效能。

傳統的作法是一個連線使用一個 thread,這種方法實做起來很簡單,但當連線數超過一定程度時就會因為共用資源的限制而變慢。

其中一種解決方法是引入 Thread Pool 架構,也就是 M 個 thread 處理 N 個連線。

Oracle 有提供商用版本叫做 Thread Pool Plugin,就如同名字,是以 plugin 形式存在。這個功能在 5.55.6 都有。

MariaDB 也有 open source 實做的 Thread pool

而 Percona 則是使用 MariaDB 的版本。(原文是說有改善,不過 benchmark 並沒有列出來,我「猜」其實沒什麼改善...)

可以看到在多連線數時的效果相當好。在 MariaDB 的文件裡有提到會有改善的時機:

Threadpools are most efficient in situations where queries are relatively short and the load is CPU bound (OLTP workloads). If the workload is not CPU bound, you might still want to limit the number of threads to save memory for the database memory buffers.

query 相對短,而且是 CPU bound。

回頭看的時候發現 Percona Server 5.5 就有支援 Thread Pool 了,應該來測試看看效果如何...

維基百科英文版與德文版的資料庫 (條目最多的兩個語言) 從 MySQL 轉移到 MariaDB 了...

維基百科官方宣佈兩個條目最多的資料庫 (英文與德文) 已經從 MySQL (有 FB patch 的版本) 轉移到 MariaDB 5.5 了:「Wikipedia Adopts MariaDB」。

維基百科的資料庫從 MySQL 4.0 升級到 MySQL 5.1 時花了不少功夫轉換 (可以想像出來,這兩個版本的差距...),然後這次再到這次的 MariaDB 5.5 就輕鬆不少。

在文章內有提到因為維基百科是 read-heavy site,大多數前端的負荷都在 squid 層擋下來,實際到後端的量則是再透過 Redismemcached 打散負荷。不過即使做了這麼多層 cache,英文版資料庫在尖峰時間還是有 50k qps 的量在跑。

尖峰時間這 50k qps 的量有 80% (也就是 40k qps) 是打散到兩台不同地點的 slave 上,平均的 query response time 是 0.2ms,95% 則是 50ms (好高),其他的 20% 是寫入 master 需求,或是因寫入 master 時需要一致性 (也就是要避免 replication lag 造成的問題)。

這次升級到 MariaDB 5.5.30 是先準備一台新機器,然後在 load balancer 上換掉其中一台 slave (先建後拆),如果 MariaDB 真的有問題也可以馬上 rollback 回來。另外用 pt-query-digest 取樣分析 query 的狀況。

這是成果:

For our most common query type, 95th percentile times over an 8-hour period dropped from 56ms to 43ms and the average from 15.4ms to 12.7ms. 50th percentile times remained a bit better with the 5.1-facebook build over the sample period, 0.185ms vs. 0.194ms. Many query types were 4-15% faster with MariaDB 5.5.30 under production load, a few were 5% slower, and nothing appeared aberrant beyond those bounds.

第一台觀察一陣子沒問題後,接下來一台一台換掉。然後發公告慶祝 :p

MariaDB 10.0.1...

MariaDB 發表 MariaDB 10.0.1 了:「What does MariaDB 10.0.1 include – available now」。

公告的文章把一些資訊整理出來讓大家與 MySQL 5.6 比較... XD

  • MariaDB 10.0-only Features:只有 MariaDB 10.0 有的功能。
  • MariaDB 10.0 Merged Features:從 MySQL 5.6 拉回來的功能。
  • MariaDB 10.0 Reimplemented Features:兩邊都有的功能,不過 MariaDB 自己重新實作了 (重寫)。
  • MariaDB 5.x Features now in MySQL 5.6:MySQL 5.6 從 MariaDB 5.x 拉的功能
  • MariaDB 5.x Features Backported from MySQL 5.6:MariaDB 5.x 從 MySQL 5.6 拉出來的功能。

然後沒有列出 MySQL 5.6 有,但是 MariaDB 沒有的功能... XD

先不管這些東西,10.0.1 多了 Cassandra storage engine 看起來就很有趣啊,可以透過 MySQL server 當 Cassandra client 跑去 Cassandra Cluster 讀寫資料:

看說明是透過 CQL 要資料,所以仍然是受限於 CQL 的能力。

資料庫裡的浮點數:MySQL 5.1 到 MySQL 5.5 的差異...

Mozilla 最近在升級 MySQL 採「先建後拆」的步驟,發現用 pt-table-checksum 檢查時不一致:「MySQL 5.1 vs. MySQL 5.5: Floats, Doubles, and Scientific Notation」。

後來發現,在 MySQL 5.1 (5.1.65-rel14.0-log Percona Server (GPL), 14.0, Revision 475) 的查詢結果是:(Mozilla 的範例)

mysql> select float_field from db.tbl where id=218964;
+-------------+
| float_field |
+-------------+
| 9.58084e-05 |
+-------------+
1 row in set (0.04 sec)

而在 MySQL 5.5 (5.5.28a-MariaDB-log MariaDB Server) 的查詢結果是:

MariaDB [(none)]> select float_field from db.tbl where id=218964;
+--------------+
| float_field |
+--------------+
| 0.0000958084 |
+--------------+
1 row in set (0.24 sec)

最後是讓 pt-table-checksum 把 float/double 欄位忽略掉。在 comment 有人提出來是在 MySQL 5.5.3 的時候改變的,不過作者蠻意外沒什麼人提到...

Percona 說明關於為什麼花這麼多時間修正 MySQL 5.5.28 安全問題...

Percona 的 Stewart Smith 解釋為什麼花這麼多時間才修正這個安全漏洞:「CVE-2012-4414 strikes back in MySQL 5.5.29 (and what we’re doing in Percona Server 5.5.29)

MariaDB 的人在得知問題後先建立了 MDEV-382 這個 issue,然後建立了 rpl_mdev382.test 這份測試資料,讓其他參與者可以測試手上的 MySQL 是否有問題。

Oracle 在修正問題時並沒有使用 MariaDB 這份測試資料測過 (或是測過但因為種種因素...) 就推出 MySQL 5.5.29,但 MySQL 5.5.29 用 rpl_mdev382.test 測試會失敗,而實際確認發現 Oracle 並沒有把問題解乾淨。所以本來 Percona 預定要使用官方 MySQL 5.5.29 版本為基礎推出 Percona Server 5.5.29 (可以降低 Percona 維護成本),變成將會改用 MariaDB 的 patch。

讓 Percona 的人特地寫一篇出來,看起來對 Oracle 不太爽... XD

MySQL 內 JOIN 的應用...

Common use cases for the MySQL Join statement」這篇給的範例與把 MySQL 上常用到的幾種 JOIN 提出來分析,包括 index 與 explain。另外在「Managing hierarchical data with MySQL」也提到了要怎麼處理階層式資料。

對於 JOIN 大概分幾個階段:

  • 在使用 MyISAM 的時候會儘量避免 JOIN,因為當 SQL 執行時間久的時候會有好幾個 table 同時卡住無法寫入。
  • 改用 InnoDB 後一直用 JOIN,不論是報表或是 web SQL query,造成 CPU bound (雖然不是 table lock,但在 MVCC 架構下,讀取也還是有 lock 成本存在)。
  • 把 JOIN 形式的 web SQL query 拆成多個 SQL query 以降低 lock 成本;針對計算成本很高的結果 cache,再針對 cache 效果不佳的表格逆正規化。只有報表不受限使用 JOIN。

後面的階段則是 data sharding 與 NoSQL。不過每個階段的界線不是那麼明顯,有時候會重疊在一起...

說到逆正規化,MariaDBVirtual Columns + PERSIST 好像是逆正規化的好工具,再來測仔細一點...

測試 MariaDB 後的一些感想...

Monty Program ABMySQL 的發起人 Monty 在離開 Sun 之後所創辦的公司 (他同時也是 MySQL AB 的創辦人),這家公司目前以 MariaDB 為發展主力。

先說對 MariaDB 目前的看法:暫時還是會用 Percona 所提供的版本,以及 XtraDB (基於 InnoDB 的產品)。

MariaDB 發展的重點在於 Aria storage Engine,但目前的 1.5 版只支援 crash-safe,要到 2.0 才會支援 InnoDB 主要功能,而到了 2.5 才會針對效能調整,看起來要到「能用」必須要到 2.5 之後... (參考「Aria FAQ」的說明)

InnoDB 控制權在 Oracle 手上,XtraDB 控制權在 Percona 手上,而 MyISAM 拿不上檯面。所以 Monty 想要一個控制權在他自己手上的 storage engine...

對於社群來說,因為 XtraDB 還是基於 InnoDB 的分支,所以當 Oracle 從 InnoDB 抽手後大家會擔心 Percona 能夠自己發展到什麼程度。這使得對於 Aria 有所期待,也趁著現在 Oracle 被歐盟盯著不致於做的太明顯趕快發展。

這也可能是 WikimediaMozilla 選 MariaDB 當 slave 測試的原因?當然也有可能只是「看名字比較順眼」之類的原因而選... XD

Archives