窮舉 PostgreSQL 的 LOCK 機制

PostgreSQL Lock Conflicts 這份資料窮舉了 PostgreSQL 的 lock 機制,分成兩種方式呈現:

  • 依照 Lock:文內列出 12 種 lock。
  • 依照 Command:文內列出了 67 種指令。

可以交叉查,用 lock 查出有哪些 command 有用到,或是反過來用 command 查會產生那些 lock:

This tool shows all commands and locks in postgres. If you select a command, it lists the locks that it acquires, commands that conflicts with it and commands that are allowed to run concurrently with it (with no conflict or blocking). If you select a lock, it lists commands that acquire the lock and what are the other conflicting locks.

舉個例子來說,Lock 列表裡的第一個,AccessShareLock,這個點進去後可以看到有三個指令會有使用到 AccessShareLock 的情境,分別是 SELECTCOPY TO 以及 ALTER TABLE ADD FOREIGN KEY (PARENT)

是個更熟悉 PostgreSQL 的路徑?

SQLite 的 HC-tree 計畫

Hacker News 首頁上看到的新計畫:「HC-tree is an experimental high-concurrency database back end for SQLite (sqlite.org)」,SQLite 弄了一個實驗性質的 backend,叫做 HC-tree

The HC-tree (hctree) project is an attempt to develop a new database backend that improves upon regular SQLite as follows:

他列了幾個重點,其中「Improved concurrency」這點題到了可以讓多個 writer 同時寫入運作,這點算是 SQLite 很大的改變,目前希望可以做到在 single-threaded 情況下不輸現有的 SQLite:

An implicit goal is that hctree must be as fast or faster than stock SQLite for all single-threaded cases. There is no point in running dozens of concurrent writers if each of them is an order of magnitude slower than a single writer writing to a legacy database.

另外一方面,這算是 SQLite 真正要面對資料庫的 isolation 的問題了,比起現在的版本,同時間從只有一個 writer 的架構要變成支援多個 writer 的架構,所以在「Concurrency Model」這邊也帶了一下他預期可以做到的事情。

然後這邊可以看到在解釋裡面有提到 table 與 index 還是 b-tree,這樣應該可以猜測 hctree 的實做方式應該還是在市場上已經很成熟的 MVCC 那套方法:

If no other client has modified any b-tree (table or index) entry or range that the transaction being committed accessed by a range or stabbing query, then the transaction is valid.

另外一個蠻大的改變是「Support for replication」,現有的 SQLite 可以透過 extension 的方式加掛支援 replication 功能,現在則是讓底層的 backend 直接支援。

底層支援新的 backend 以後看起來會有不少變化可以玩,第一個想到的當然是變成 server 類型的服務,也就是像 MySQL 或是 PostgreSQL 這樣的方式,另外一種方向是包裝成 distributed database,讓應用程式可以簡單跨機器使用。

目前還不知道會往什麼方向走就是了,也有可能 SQLite 這邊只實做 backend,上面讓大家發揮...

EnterpriseDB 打算推出的 zheap,想要解 VACUUM 問題...

前天被問到「DO or UNDO - there is no VACUUM」這篇,回家後仔細看一看再翻了一些資料,看起來是要往 InnoDB 的解法靠...

PostgreSQL 與 InnoDB 都是透過 MVCC 的概念實做 transaction 之間的互動,但兩者實際的作法不太一樣。其中帶來一個明顯的差異就是 PostgreSQL 需要 VACUUM。這點在同一篇作者八年前 (2011) 的文章就有提過兩者的差異以及優缺點:「MySQL vs. PostgreSQL, Part 2: VACUUM vs. Purge」。

UPDATE 時,InnoDB 會把新資料寫到表格內,然後把可能會被 rollback 的舊資料放到表格外:

In InnoDB, only the most recent version of an updated row is retained in the table itself. Old versions of updated rows are moved to the rollback segment, while deleted row versions are left in place and marked for future cleanup. Thus, purge must get rid of any deleted rows from the table itself, and clear out any old versions of updated rows from the rollback segment.

而被 DELETE 清除的資料則是由 purge thread 處理:

All the information necessary to find the deleted records that might need to be purged is also written to the rollback segment, so it's quite easy to find the rows that need to be cleaned out; and the old versions of the updated records are all in the rollback segment itself, so those are easy to find, too.

所以可以在 InnoDB 看到 purge thread 相關的設定:「MySQL :: MySQL 5.7 Reference Manual :: 14.6.11 Configuring InnoDB Purge Scheduling」,負責處理這些東西。

而在 PostgreSQL 的作法則是反過來,舊的資料放在原來地方,新資料另外存:

PostgreSQL takes a completely different approach. There is no rollback tablespace, or anything similar. When a row is updated, the old version is left in place; the new version is simply written into the table along with it.

新舊資料的位置其實還好,主要是因為沒有類似的地方可以記錄哪些要清:

Lacking a centralized record of what must be purged, PostgreSQL's VACUUM has historically needed to scan the entire table to look for records that might require cleanup.

這也使得 PostgreSQL 裡需要 autovacuum 之類的程序去掃,或是手動跑 vacuum。而在去年 (2017) 的文章裡也有提到目前還是類似的情況:「MVCC and VACUUM」。

而在今年 (2018) 的文章裡,EnterpriseDB 就提出了 zheap 的想法,在 UPDATE 時寫到 table 裡,把可能被 rollback 的資料放到 undo log 裡。其實就是把 InnoDB 那套方法拿過來用,只是整篇都沒提到而已 XD:

That brings me to the design which EnterpriseDB is proposing. We are working to build a new table storage format for PostgreSQL, which we’re calling zheap. In a zheap, whenever possible, we handle an UPDATE by moving the old row version to an undo log, and putting the new row version in the place previously occupied by the old one. If the transaction aborts, we retrieve the old row version from undo and put it back in the original location; if a concurrent transaction needs to see the old row version, it can find it in undo. Of course, this doesn’t work when the block is full and the row is getting wider, and there are some other problem cases as well, but it covers many useful cases. In the typical case, therefore, even bulk updates do not force a zheap to grow. Instead, the undo grows. When a transaction commits, all row versions that will become dead are in the undo, not the zheap.

不過馬上就會想到問題,如果要改善問題,不是個找地方記錄哪些位置要回收就好了嗎?順便改變方法是為了避免 fragment 嗎?

等著看之後變成什麼樣子吧...

InnoDB 的 MVCC 繁忙時的效能問題

Facebook 上看到 Percona 的人修正了 InnoDB 的 MVCC 在繁忙時會有 O(n^2) 的效能問題:

MySQL 官方的 bug tracking system 是「InnoDB's MVCC has O(N^2) behaviors」這個,可以看到給的重製範例是在 transaction 內大量塞 INSERT 進去後,另外一個 transaction 使用 secondary index 就會受到影響。

裡面也有提到「Secondary index updates make consistent reads do O(N^2) undo page lookups」,雖然修正了,但看起來跟當時實做的規劃有關?所以導致許多地方都是 O(n^2)...

這個 bug 感覺是批次作業的行為?因為批次作業可能會用 transaction 包起來,一次寫入萬筆資料後再 COMMIT 進去。而這個行為很有機會觸發這個 bug,導致影響到線上的服務...

Datomic 以及 RethinkDB...

Baron Schwartz (Percona 的 Chief Performance Architect) 寫了一篇「Immutability, MVCC, and garbage collection」狂酸 DatomicRethinkDB (喔,還稍微提到 CouchDB)。

裡面提到了 append-only B-tree 這的資料結構,優點以及會遇到的問題。(而這些問題都是致命的...)

下面的 comment 就看到 Datomic 的人跑出來反擊了,不過我懶的看了... XD

RDBMS 這麼多人發展這麼久了,不太有機會有萬靈丹突然出現解決一切問題... (這表示之前的人都是笨蛋?)

新出來一個 RDBMS 系統,官網做的很漂亮,是由一個商業公司拿錢發展出來,號稱可以解決很多問題,大概都可以先跳過去... 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 好像是逆正規化的好工具,再來測仔細一點...

長野雅廣 (Masahiro Nagano) 的 MySQL Beginners Talk

長野雅廣的「MySQL Beginners Talk で LT してきました」這篇 slide 對不熟悉 MySQL 的人講了兩個幾乎不會錯的觀念:

先討論後面這點,算是任何 database 都通用的法則:當你遇到效能問題時,監控機制可以提供毛線球的線頭,讓你知道慢在哪裡:什麼時間滿載 (於是可以猜測是 cron job 造成,或是對應 MRTG 圖時知道是一般使用者造成的流量造成),另外可以知道瓶頸是在 CPU (是單顆 CPU 滿載,還是整台機器都被吃滿),I/O (是讀取滿載,還是寫入造成滿載),或是網路。

前面這點解釋成「如果你不知道你在做什麼,就用 InnoDB Plugin 吧」,對於初學者 (slide 的標題),就簡化成「既然你是初學者,你就用 InnoDB Plugin 吧」。原因是:

  • InnoDB 是 crash safe engine,MyISAM 不是。
  • 常被用到的 table 其實會被 cache 在記憶體內,用 MyISAM 與 InnoDB 差異不大。
  • 最重要的一點是,InnoDB 有支援 transaction (MVCC),在大量寫入時比起 MyISAM 比較不會產生 table lock。由於 InnoDB 支援 transaction,所以功能也比 MyISAM 多。

slide 不長,但這兩個重點還蠻重要的...