レプリケーションの遅延を計るシンプルな方法

MySQL

MySQLでレプリケーションをするときに、必ず気になるのが master-slave間の遅延だと思います。
そこで、先日のDeNAさんのテクノロジーセミナーで開示していただいたネタをもとに簡単な遅延監視方法を実装してみました。

  • MySQLバージョン 5.1.23
    • binlog_format=MIXED


まずmasterに以下のようなテーブルを作ります。databaseは適当に作ってください。

CREATE TABLE `replitimes` (
  `No` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ldate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`No`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

当然slaveにも同じものが作成されたと思います。作成されてない人は遅延監視とか以前の問題ですね^^;


次に、master側で以下のようなSQLを実行してみましょう。

master> INSERT INTO replitimes (ldate) value (sysdate());


次にslaveで以下のようなクエリを投げて見ましょう。

slave > select * from replitimes;
+----+---------------------+---------------------+
| No | date                | ldate               |
+----+---------------------+---------------------+
|  1 | 2008-04-11 17:57:12 | 2008-04-11 17:57:12 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

きちんと反映されたでしょうか？
さて、それでは遅延をわざと発生させてどう変化するか確かめてみましょう。

slave > stop slave;
master> INSERT INTO replitimes (ldate) value (sysdate());
〜数秒待ってください〜
slave > start slave;
mysql> select *,ldate - date as delay from replitimes;
+----+---------------------+---------------------+-------+
| No | date                | ldate               | delay |
+----+---------------------+---------------------+-------+
|  1 | 2008-04-11 17:57:12 | 2008-04-11 17:57:12 |     0 |
|  2 | 2008-04-11 18:02:15 | 2008-04-11 18:02:24 |     9 |
+----+---------------------+---------------------+-------+
2 rows in set (0.00 sec)

[date]にはmasterでnow()を実行したときと同じTIMESTAMP値が、[ldate]にはslaveでsysdate()が実行されたときの時間が記録されたかと思います。


sysdate()を使うのがコツです。理由はbinlogを眺めてもらえれば自明ですね。

bash3.0 # ./bin/mysqlbinlog /usr/local/mysql_64/data/mysql-bin.000018
〜色々と中略〜
# at 4988971
#080411 18:02:15 server id 1  end_log_pos 4989094       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1207904535/*!*/;
INSERT INTO replitimes (ldate) value (sysdate())/*!*/;
DELIMITER ;
# End of log file


ここで一つ注意なのが、パラメータ[binlog_format]です。
5.1.23では、以下の3つが選べます。

  • MIXED(Default)
  • STATEMENT
  • ROW

これを ROW にして行ベースレプリケーション(RBR)にしてしまうと、今回の手法は使えません。
MySQL5.1.12以降では、標準で MIXED が設定されているので敢えて変えない限りはこの手法で遅延を検出できます。(STATEMENTでも同じ動作を得ることが可能です)。詳細は・・・まあマニュアルを読んでください。
5.0系はいまのところステートメントベースレプリケーション(SBR)であり、5.1系で言うところの STATEMENT と同じ状況なので、この手法が使えると思います。