Month: January 2014

使用 pt-stalk 找出 MySQL 效能問題

pt-stalk 是 Percona Toolkit for MySQL 裡面的其中一項工具。它會連到 MySQL Server，監控指定的數值，當超過指定的 Threshold 時，收集當時資料庫執行的資訊（包含正在進行的 transaction、vmstat、lsof，甚至 gdb stack trace），方便分析解決問題。

pt-stalk 需要以 root 權限執行。執行的方法如下：

pt-stalk --daemonize --variable Threads_connected --threshold 400 -- --defaults-file=/etc/mysql/my.cnf

當同時有 400 以上的 Threads 時，便會收集資料，存到 /var/lib/pt-stalk 內。收集的資料範例：

2014_01_13_00_25_09-df              2014_01_13_00_25_09-lock-waits      2014_01_13_00_25_09-netstat         2014_01_13_00_25_09-ps
2014_01_13_00_25_09-disk-space      2014_01_13_00_25_09-log_error       2014_01_13_00_25_09-netstat_s       2014_01_13_00_25_09-slabinfo
2014_01_13_00_25_09-diskstats       2014_01_13_00_25_09-lsof            2014_01_13_00_25_09-opentables1     2014_01_13_00_25_09-sysctl
2014_01_13_00_25_09-hostname        2014_01_13_00_25_09-meminfo         2014_01_13_00_25_09-opentables2     2014_01_13_00_25_09-top
2014_01_13_00_25_09-innodbstatus1   2014_01_13_00_25_09-mpstat          2014_01_13_00_25_09-output          2014_01_13_00_25_09-transactions
2014_01_13_00_25_09-innodbstatus2   2014_01_13_00_25_09-mpstat-overall  2014_01_13_00_25_09-pmap            2014_01_13_00_25_09-trigger
2014_01_13_00_25_09-interrupts      2014_01_13_00_25_09-mutex-status1   2014_01_13_00_25_09-processlist     2014_01_13_00_25_09-variables
2014_01_13_00_25_09-iostat          2014_01_13_00_25_09-mutex-status2   2014_01_13_00_25_09-procstat        2014_01_13_00_25_09-vmstat
2014_01_13_00_25_09-iostat-overall  2014_01_13_00_25_09-mysqladmin      2014_01_13_00_25_09-procvmstat      2014_01_13_00_25_09-vmstat-overall

然後看 InnoDB status，可以看到很多 transaction 正在等 query cache lock：

MySQL thread id 688253701, OS thread handle 0x7f6a591c9700, query id
19004545452 10.1.1.94 pixblog Waiting for query cache lock
SELECT * FROM `blogarticle` WHERE (`blogarticle_blogid` = 3835626) AND
(`blogarticle_date` < 1388605724) AND (`blogarticle_status` IN (2,3,5,7))
ORDER BY `blogarticle_date` desc, `blogarticle_id` desc LIMIT 1
---TRANSACTION 144B7B32FC, not started
MySQL thread id 688253702, OS thread handle 0x7f6a787bc700, query id
19004544621 10.1.1.191 pixblog Waiting for query cache lock
SELECT * FROM `blog` WHERE `blog_id` = 3126963
---TRANSACTION 144B7B32FD, not started
MySQL thread id 688253706, OS thread handle 0x7f6a887e2700, query id
19004544620 10.1.1.166 pixblog Waiting for query cache lock
SELECT * FROM `bloglayout` WHERE `bloglayout_id` = 3063007
---TRANSACTION 144B7B33C7, not started starting index read
mysql tables in use 1, locked 0

發生問題的當時確實有一個跑統計報表的 Slow Query 正在執行，於是要求該 Query 不使用 Query Cache1 來解決問題。

Galera 3.x 的 Replication Relaying

Galera 3.x 為了多機房間的 Replication，設計了 gmcast.segment 這個參數。前陣子 Percona 的人出來介紹了這個參數背後的機制：Automatic replication relaying in Galera 3.x (available with PXC 5.6)。大致摘錄如下：

  1. 同一個機房內資料庫的 gmcast.segment 參數要設為相同。
  2. 機房與機房之間的 Replication 會自動找一個 node 進行 Relay，以降低 Replication 需要的頻寬。

    Image from: Automatic replication relaying in Galera 3.x (available with PXC 5.6)

如果沒有設定 gmcast.segment 參數的話，同樣是三個 Node，會耗用兩倍的頻寬：

Image from: Automatic replication relaying in Galera 3.x (available with PXC 5.6)

原文中另外有對作了 segment 與不作 segment 的 commit latency 進行比較，結果作了 segment 的 commit latency 並沒有比較高。如果有跨機房需求，應該要設定 gmcast.segment。

MySQL 5.6 的 Index Condition Pushdown

MySQL 5.6 以前的 Multi-column Index，當位於 index 中間的 column（如下例的 j）需要進行 range query 的時候，只能利用到部分的 index，需要另外讀取資料列的內容來進行判斷。舉例來說，如果有一個 Table 結構是這樣：

CREATE TABLE mytable (
id int not null auto_increment primary key,
i int(11) NOT NULL,
j int(11) NOT NULL,
k int(11) NOT NULL,
val char(10) NOT NULL,
KEY ijk (i,j,k)
) ENGINE=InnoDB;

在 MySQL 5.6 之前，SQL Query 「SELECT sum(length(val)) FROM T WHERE i=1 AND j<100 AND k=100」 會把所有 i=1 的資料列拉出來，逐一比較 j 與 k 的值。MySQL 5.6 的 Index Condition Pushdown (ICP) 功能，把這個動作改為比較 Index tuple 而非資料列內容，可避免拉出整個資料列，降低磁碟 IO（因為資料列通常比 Index 大）。

詳細的說明可以看 MySQL 的官方文件：Index Condition Pushdown Optimization 與 Percona 的測試：Multiple column index vs multiple indexes with MySQL 5.6。