用 MySQL 5.6 的 Performance Schema 觀察系統效能

Percona 寫的「MySQL query digest with Performance Schema」這篇提到了 MySQL 5.6 的 Performance Schema 裡的 events_statements_summary_by_digest 相當好用,實際在系統上翻了翻發現算是非常實用的資料。

首先先看這個表格實際的內容,由於文字塞不動,就改用圖片了:

可以試著用 SELECT * FROM performance_schema.events_statements_summary_by_digest LIMIT 1 \G 之類的指令看到裡面的值,像是這樣:(裡面有些欄位名稱我換掉了,換掉的部份用刪節號標示)

                SCHEMA_NAME: kkbox
                     DIGEST: 490a2e363ba7840843733e219175e2a7
                DIGEST_TEXT: SELECT * FROM `table1` WHERE TYPE = ? AND `column1` IN (?) AND STATUS IN (...) ORDER BY STATUS DESC , `created_at` DESC , `id` DESC 
                 COUNT_STAR: 299179761
             SUM_TIMER_WAIT: 215069693134746000
             MIN_TIMER_WAIT: 130241000
             AVG_TIMER_WAIT: 718864000
             MAX_TIMER_WAIT: 54442047235000
              SUM_LOCK_TIME: 21915487179000000
                 SUM_ERRORS: 0
               SUM_WARNINGS: 0
          SUM_ROWS_AFFECTED: 0
              SUM_ROWS_SENT: 1240784631
          SUM_ROWS_EXAMINED: 2499118409
SUM_CREATED_TMP_DISK_TABLES: 0
     SUM_CREATED_TMP_TABLES: 0
       SUM_SELECT_FULL_JOIN: 0
 SUM_SELECT_FULL_RANGE_JOIN: 0
           SUM_SELECT_RANGE: 0
     SUM_SELECT_RANGE_CHECK: 0
            SUM_SELECT_SCAN: 0
      SUM_SORT_MERGE_PASSES: 2630
             SUM_SORT_RANGE: 299196698
              SUM_SORT_ROWS: 1240808755
              SUM_SORT_SCAN: 0
          SUM_NO_INDEX_USED: 0
     SUM_NO_GOOD_INDEX_USED: 0
                 FIRST_SEEN: 2015-09-17 20:41:15
                  LAST_SEEN: 2015-10-15 01:06:10

其中 DIGEST_TEXT 是 SQL query,可以看到 IN 裡面的東西會被整合起來,而 COUNT_STAR 是次數,後面的 AVG_TIMER_WAIT 單位是 10-12 秒,除以 109 後才會變成 ms。

裡面的資訊對於 DBA 在 tune 效能時應該是很有用...

PostgreSQL 9.5 預定提供的 Row Locking 改善

在「More Concurrency: Improved Locking In PostgreSQL」這邊提到 PostgreSQL 的 Row Locking 的改善,也就是 SELECT ... FOR UPDATESELECT ... FOR SHARE

查了一下 SELECT 的文件,在 7.2 開始提供 FOR UPDATE (PostgreSQL: Documentation: 7.2: SELECT),在 8.1 開始提供 FOR SHARE (PostgreSQL: Documentation: 8.1: SELECT),以維基百科上的紀錄來看,7.2 是 2002 年二月,8.1 是 2005 年十一月,都是已經提很久的功能了。

FOR UPDATEFOR SHARE 可以降低對 transaction 的依賴程度,PostgreSQL 的預設值是 READ COMMITTED,配合 Row Locking 就已經可以做到不少效果了,不需要用到 SERIALIZABLE 等級。

而在最新的 PostgreSQL 9.5 (目前還是開發版),則又多提供了 FOR UPDATE SKIP LOCKED 功能,以官方提供的範例來說,就可以直接避開選位造成的 lock 問題了:

This makes sense because 100 users checking for a free seat concurrently will get 100 different rows. The consequence is that you are not stuck with 1 CPU but you can nicely scale out to all CPUs in the system. As conflicts cannot happen anymore, nobody has to wait on somebody else.

對 locking 控制的更細微。

Amazon RDS 支援 MariaDB

這次 AWS re:Invent 2015 宣佈的新服務,Amazon RDS 支援 MariaDB:「Amazon RDS Update – MariaDB is Now Available」。

這表示可以使用 Aria Storage Engine。不過目前支援的 instance 有限:

You can launch RDS database instances running MariaDB today in all AWS regions. Supported database instance types include M3 (standard), R3 (memory optimized), and T2 (standard).

MySQL 5.7 將會有新的備份工具 mysqlpump

在「Introducing mysqlpump」這邊提到了 MySQL 5.7 將會有 mysqlpump 這個新工具,主要是避免影響 mysqldump,但又可以產生容易平行處理的 dump data:

The goal of mysqlpump is to have a modern utility that is extendable and has native support for parallelization.

看了一下範例還蠻簡單的 (目標也很明確),應該是之後的參考工具...

Berkeley DB 的介紹

在滿滿都是 NoSQL 的世代中,意外在「Berkeley DB: Architecture」這邊看到 Berkeley DB 的介紹...

2006 年 Berkeley DB 的公司 SleepycatOracle 收購。在收購後 Oracle 改變了 open source 授權部份,從之前的 Sleepycat License 改成了 AGPLv3

Berkeley DB 算是早期功能很完整的 database library,由於 page level locking、crash-safe 加上有 transaction,也曾經被 MySQL 拿去當作 engine,不過在 MySQL 5.1 被拔掉:「14.5 The BDB (BerkeleyDB) Storage Engine」。

文章裡講了很多底層設計上的想法 (而非單純只說明「做了什麼」),以四個面向來討論。Buffer、Lock、Log 以及 Transaction,並且圍繞著 ACID 需求討論。

算是懷念的考古文?Google 弄出來的 LevelDBFacebook 接著改善的 RocksDB 的走向也不太一樣了,現在大家對 ACID 需求因為 NoSQL 盛行的關係又重新在檢視...

跑步王在 COSCUP 2015 的 PostgreSQL、JSON、GIS

剛剛看到跑步王COSCUP 2015 的「COSCUP 2015 - 使用 PostgreSQL, NoSQL 和 GIS 一次滿足 - Ronny Wang」這份錄影資料:

前半段講 JSON、JSONB (JSON Types) 以及 PostgreSQLIndexes on Expressions 以及 Partial Indexes

後半段講 GIS 的部份也很讚,不過就偏地圖應用了 :p

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 的機制也講了不少...

PHP 5.5 的 Generators

在「Save memory by switching to generators」這邊提到了 PHP 5.5 開始提供的 Generators...

由於其他的程式語言有 Generators 的觀念,其實不會太難了解...

不過比較大的問題是,資料庫的查詢操作用 Generators 會把效能壓力壓回資料庫:因為資料庫需要把結果 buffering 在資料庫端,如果不趕快吐出去就是要找記憶體放... 也因此,比較常見到的解法是不要用 Generators。(因為 web 與 application 端相較於資料庫端,比較容易 scale)

後來用 Generators 比較多的印象中還是 filter 類的應用吧,Python 這邊的東西有陣子沒看了 :o

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 來修正一些主題。