Galera Cluster (Percona XtraDB Cluster) 同步速度的改善

Percona 的「State Snapshot Transfer (SST) at a glance」這篇給了 Galera Cluster (也就是 Percona XtraDB Cluster) 在同步速度的改善方案,整篇文章一步一步改善,從 51 分鐘降到 18 分鐘。

劃幾個重點。

首先是同步時的設定可以放到系統 my.cnf[sst] 內,像是這樣:

[sst]
inno-apply-opts="--use-memory=20G"

其中改善最大的也就是 --use-memory,依照作者測試的數據,光這步就從 51 分鐘降到 30 分鐘。不過這邊要小心本來就有跑的 mysqld,如果 OOM 就慘了...

再來講的是 wsrep_slave_threads,不同於上面的 sst only 設定,這是在一般性的 tuning (平常在跑的參數,放在 [mysqld] 內),改完後速度 30 分鐘再提升到 25:32。

然後是壓縮的方式改用支援多 CPU 的 pigz

[sst]
inno-apply-opts="--use-memory=20G"
compressor="pigz"
decompressor="pigz -d"

很明顯是個 shell command 類的設定,所以如果真的想要測的話,可以再從 -1 測到 -9,作者在這邊沒測,不過效果也很明顯了,從 25:32 降到 21 分鐘。

最後一個大的改變是建議有專門同步的節點 (Donor node),這個節點上不會有 SQL query 來影響效能,這樣可以讓 pigz 的效率更高:

Since node2 is not getting application traffic, moving into the Donor state and doing an expensive SST with pigz compression should be relatively safe for the rest of the cluster (in this case, node1).

時間最後降到 18:33。

Relational Database System (RDBMS) 運作的方式

在「How does a relational database work」這篇文章用了很長的篇幅講「資料庫如何把 SQL query 轉換為實際的操作」:

I’ll focus on what I think is essential: the way a database handles an SQL query.

資料庫也是人寫出來的,資料結構與演算法也是人設計出來的。你現在手上有資料,要怎麼把 SQL query 變成有效率的查詢操作行為,就是這篇文章在描述的。

看起來連 JOIN 的機制也講了不少...

InnoDB 的各種枚枚角角

Baron Schwartz 的 blog 上看到「An Outline for a Book on InnoDB」這篇文章,作者因為被 InnoDB 所驚嘆而想要寫一本書探討 InnoDB 的設計:(因為雖然複雜,但試著將這些複雜的東西隱藏起來,不讓使用的人暈頭轉向)

Years ago I pursued my interest in InnoDB’s architecture and design, and became impressed with its sophistication. Another way to say it is that InnoDB is complicated, as are all MVCC databases. However, InnoDB manages to hide the bulk of its complexity entirely from most users.

I decided to at least outline a book on InnoDB. After researching it for a while, it became clear that it would need to be a series of books in multiple volumes, with somewhere between 1000 and 2000 pages total.

作者還沒開始寫,不過 outline 已經先列出來了:

I did not begin writing. Although it is incomplete, outdated, and in some cases wrong, I share the outline here in case anyone is interested. It might be of particular interest to someone who thinks it’s an easy task to write a new database.

這篇文章的重點在這些 outline,即使沒有寫成書,這些 outline 也讓你知道要可以朝什麼方向研究...

因颱風延期的「COSCUP 2015 Hands-on【妙生活】MySQL 入門」剛好可以 review 這邊的 outline 來修正一些主題。

關於要不要使用 MySQL 這件事情...

前陣子 ant 將在 5xRuby 演講的投影片放出來:「技術演講:淺入淺出 MySQL & PostgreSQL」,另外在 kaif.io 上也有討論:「淺入淺出 MySQL & PostgreSQL // Speaker Deck」。

而國外剛好也有好幾篇文章都在討論 MySQL (InnoDB),其中「how innodb lost its advantage」這篇講到對 InnoDB 的壓縮感到悲觀...

另外 Pinterest 的「Learn to stop using shiny new things and love MySQL」這篇的時間點感覺上就是在回應上面某些想法。

下面是我的整理 (以及想法)。

MySQLPostgreSQL 都是很成熟的 RDBMS。

如果你對其中一種有經驗,那麼就用你熟悉的 RDBMS。如果你對兩者都有經驗,那麼你就憑自己的判斷選擇。

如果都沒有經驗呢?看你身邊的人用什麼就選什麼。

我在 5xRuby 時回答的比較輕鬆,但這是很實際的回答:你既然都不會用這些進階功能,那麼兩套其實對你都差不多。選一個可以問的到答案的就好。

反正真的夠大的時候,拿錢出來總是有方案可以解決問題。初期把力氣花在怎麼搞定產品吧,如果你不熟悉,這通常都不是你在這個時間應該花時間去研究的問題。

關於 RDBMS 的 Schema Migration...

在「NoSQL 大腸花」這份投影片裡面的 Page 12 有提到關於 RDBMS 的 Schema Migration:

以目前 open source 的兩個專案,MySQLPostgreSQL 來看,裡面提到的 lock 應該都不是問題...

首先是 MySQL 的部份,真的量大的網站都應該是往 InnoDB 投靠,而 pt-online-schema-change 在這個領域則是處理的很好。

Facebook 的 Mark Callaghan 曾經在 2010 年寫過一篇關於 InnoDB 的 online schema change 的原理:「Online Schema Change for MySQL」,主要是利用 Trigger 的機制,用七個步驟架構出沒有 downtime 的 online scheme change。

就算不考慮 pt-online-schema-change 這種工具,在 MySQL 5.6 開始,就有愈來愈多 ALTER TABLE 的行為是不會影響到 read/write 了:「Avoiding MySQL ALTER table downtime」。

而 PostgreSQL 的情況也差不多,常見的 ALTER TABLE (新增與刪除 column 與 index) 也都不會影響 read/write。

這些在 Stack Overflow 上有不少討論:「ALTER TABLE without locking the table?」。

MySQL 在 RDBMS 領域裡比起來的確是不怎樣,不過沒有這麼糟糕啊...

InnoDB 的 isolation mode

Percona 老大 Peter Zaitsev 寫了一篇關於 InnoDB 的 isolation mode 與效能的關係:「MySQL performance implications of InnoDB isolation modes」。

關於 isolation level 的介紹,也可以參考維基百科上的說明:「Isolation (database systems)」。

其中有個我也認同的的建議:

In general I think good practice is to use READ COMITTED isolation mode as default and change to REPEATABLE READ for those applications or transactions which require it.

需要 REPEATABLE READ 需求的 transaction 可以在 session 裡面開,不需要平常就開著。

而剛從 MyISAM 轉過去的因為本來就沒有 transaction,換到 InnoDB 時強烈建議就直接設上去 (剛好在註解也有提到):

I think READ-COMMITTED is better default yet it can break some applications. Though so changing to default storage engine from MyISAM to Innodb did.

資料結構、RDBMS、ORM

欠了很久的雜記。既然是雜記,只是把一些事情記錄下來,許多句子的主題會跳來跳去,請多見諒。

先解釋標題的三個詞彙。這邊要講的是三種存取資料的方式:

  • 資料結構:直接操作最底層的資料結構。
  • RDBMS (Relational Database Management System,關聯式資料庫):透過 RDBMS 存取資料的方式,在 open source 領域比較常遇到 MySQLPostgreSQL。由於與下面的 ORM 比較,這一條指的是透過 SQL query 去存取資料。
  • ORM (Object-Relational Mapping):透過程式語言的 object 以及 object 之間的關聯性存取資料。

彈性最高、效能也最好的是直接的資料存取,但寫起來也最複雜;而 ORM 大致上就是反過來。

現代的 RDBMS 大多都有實做 ACID,在自己操作資料結構時考慮這塊會比較辛苦。兩個層級之間有一些 library 試著解決這個問題 (像是 BerkeleyDB 或是 LevelDB),不過這篇文章暫時跳過。

MySQL 與其他的 RDBMS 比較起來欠了許多東西,但 High Availability 的成熟度以及效能而成為 open source 的第一選項。而也因為許多人使用,大家都知道 MySQL 的先天限制,也有許多 workaround 出現,所以大多數的狀況下這不是問題。

MySQL 的 InnoDB 其實寫的相當不錯,但 MySQL 的 SQL parser 一直都是 MySQL 的痛處,所以許多人使用 MySQL 時會儘量使用 simple query,而 ORM 的特性剛好可以搭上風。

使用 ORM 時最常見要避免的是 N+1 的問題,其他常見到的問題大多都不是 ORM 專有的。

先整理到這邊。

避免用 SQL 的 OFFSET 實做 Pager

發現以前沒有提到過...?

WordPress.com Developer Resources 寫的這篇「An efficient alternative to paging with SQL OFFSETs」提到了用 OFFSET 實做 Pager 的效率問題。

因為 RDBMS 是人寫的,所以人想不到的方法,程式也做不到,像是這樣的 SQL query 效率不會好:

SELECT * FROM my_table ORDER BY pk LIMIT 8000000, 100;

如果這是一般以 B+tree 儲存的 RDBMS (或是類似的資料結構),會先把 pk 拿出來,從最小的開始計算,掃過八百萬次後再去抓一百筆 pk 的值,最後再回到 my_table 內把所有資料拉出來。

這個方法當 offset 小的時候不會有感覺,但大了以後就會爆炸。就算 primary key 都在記憶體內,仍然是狂操 CPU L2/L3 以及記憶體的存取速度。

目前常見的分頁作法是在 url 上妥協,只提供「下一頁」或「下 n 頁」的功能。如此一來,url 變成:

https://www.example.com/blog?page=10&started_at=12345678
https://www.example.com/blog?page=11&started_at=12345690
https://www.example.com/blog?page=12&started_at=12345710

started_at 變成 unix timestamp,而資料庫對時間欄位 index。如此一來,資料庫在查詢的時候就可以先 B+tree 找到 started_at 的節點 (或是小於他最近的節點),然後連續抽出一百筆。

另外一種則是在產品面上「妥協」,也就是方法保持不變,但限制「一個 thread 最多只能回 1000 篇」,這在 2ch 或是論壇上很常看到。