MySQL 8.0 的 performance_schema 加上 index 了...

MySQL 8.0 是 MySQL 5.7 的後續版本,中間的 6.0 與 7.0 都有一些故事,就被跳過去了,跟 PHP 的情況有點像。

在 8.0 版將會把 performance_schamea 加上 index,讓查詢的速度變快:「MySQL 8.0: Performance Schema, now with indexes!」:

In MySQL 8.0, performance_schema tables are now indexed to speed up data retrieval.

A total of 115 indexes have been added in the performance schema in MySQL 8.0.0, to support better data access patterns in general.

有用過 performance_schema 的人都會有種「這好慢啊」的感覺,總算要改善了... 而且這幾乎是沒什麼成本的改善:

Question: How much overhead was just added by this new feature?
Answer: Absolutely zero

並不是用 index 加快速度,而是加了一些資訊,修正 optimizer 的行為:

It does — not — maintain a physical index internally, be it on file or memory.
It does, however, — pretend — to the optimizer that it has indexes, so that the optimizer is coerced into using the most efficient access pattern.

在有些情況下可以看到會快非常的多:

The performance improvements from indexes can be very easily seen in many of the sys schema queries. With 1000 idle threads, the query SELECT * FROM sys.session drops from 34.70 seconds down to 1.01 seconds (a 30x improvement!):

不知道 Percona 會不會 backport 回來,這看起來對於爆炸中的 server 找問題會很有幫助,可以在短時間翻出是哪個部份爆炸...

用 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 效能時應該是很有用...

關於 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 領域裡比起來的確是不怎樣,不過沒有這麼糟糕啊...

利用 pt-online-schema-change 同步 master 與 slave 的資料

在「Syncing MySQL slave table with pt-online-schema-change」這篇看到 master 與 slave 的資料不同步時,強制性同步的方法:

pt-online-schema-change --alter 'ENGINE=INNODB' D=dbname,t=tblname

由於 pt-online-schema-change 的作法是建一個新的表格,然後把舊表格的資料寫過去,而這些行為會被 replicate 到新機器上,於是就同步了...

這招有趣 XDDD