MySQL道普請便り

第22回 特定のSQL文が原因で発生したレプリケーション遅延の調査方法

この記事を読むのに必要な時間:およそ 5 分

マスター,スレーブ構成のMySQL環境でサービス稼動してる時には,レプリケーションの遅延がよく発生します。MySQLのスレーブはマスターの更新をシングルスレッドで処理するため,特定のSQL文(1つのDELETE文で大量のレコードの削除や,1つのUPDATE文で大量のレコードの更新など)がレプリケーションの遅延原因となることが多いです。

今回は現在進行形で遅延しているSQL文の調査方法と,過去に遅延したSQL文の調査方法をご紹介します。

はじめに環境構築

MySQL5.6インストール

今回はマスター/スレーブ構成にするため2台のサーバを用意し,yumでMySQLをインストールしてみます(OSはCentOS6.6)⁠

MySQL Yum Repositoryをインストールします。

wget http://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm
rpm -i mysql57-community-release-el6-8.noarch.rpm

デフォルトではMySQL5.6ではなくMySQL5.7をインストールしてしまうので,mysql-community.repoの[mysql57-community]のenabledを0へ変更し,[mysql56-community]のenabledを1へ変更します。yumでMySQL5.6をインストールします。

vim /etc/yum.repos.d/mysql-community.repo

# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

yum install  mysql-community-server

レプリケーションを行うためにMySQLの設定ファイル(my.cnf)を修正します。

  1. バイナリログを有効にします。
  2. マスター側はserver-id=1,スレーブ側はserver-id=2と追記します。
 vim /etc/my.cnf

 [mysqld]
 log-bin=mysql-bin
 server-id=1

MySQLを起動します。

/etc/init.d/mysqld start

構成は以下の通りです。

IPMySQL PORTmaster/slave
192.168.0.13306master
192.168.0.23306slave

レプリケーション設定

今回はMySQL5.6からの新機能GTIDなどは使用せずに,従来のログポジションを指定するレプリケーション設定で構成します。

マスターの設定

まずはレプリケーション用のユーザと権限を設定します(ユーザ名:repl,パスワード:replication)⁠

mysql> CREATE USER  'repl'@'192.168.0.2' IDENTIFIED BY 'replication';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.2';

そして,現在のバイナリログの情報を取得しておきましょう。

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      439 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
スレーブの設定

スレーブ側でレプリケーションを開始します。

CHANGE MASTER TO
 MASTER_PORT= 3306,
 MASTER_HOST='192.168.0.1',
 MASTER_USER='repl',  
 MASTER_PASSWORD='replication',
 MASTER_LOG_FILE='mysql-bin.000003',
 MASTER_LOG_POS=439;

mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
オプション値説明
MASTER_PORT3306マスターのポート番号
MASTER_HOST192.168.0.1マスターのIPアドレス
MASTER_USERreplレプリケーション用のユーザ
MASTER_PASSWORDreplicationレプリケーション用のユーザのパスワード
MASTER_LOG_FILEmysql-bin.000003マスターで確認したバイナリログ
MASTER_LOG_POS439マスターで確認したバイナリログポジション

正常にレプリケーションが開始されているかは「SHOW SLAVE STATUS\G」の以下の値がYESになっているか確認します。

  • Slave_IO_Running
  • Slave_SQL_Running
mysql> SHOW SLAVE STATUS\G
 :
 :
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
 :
 :

以上でレプリケーションの設定は完了です。

仮データの準備

マスターでデータベースを作成します。ここではreplというデータベースを作成しています。

mysql> create database repl;
Query OK, 1 row affected (0.00 sec)

mysql> use repl;
Database changed

テーブルを作成します。

mysql> create table repl_test (id serial,id2 int ,tx text);
Query OK, 0 rows affected (0.26 sec)

仮データを投入します。今回は簡単なプロシージャを作ってデータをインサートします。

delimiter //
DROP PROCEDURE IF EXISTS  ins_repl_test//
CREATE PROCEDURE ins_repl_test ( num int)
BEGIN
    DECLARE cnt int DEFAULT 1;
    START TRANSACTION;
    WHILE cnt <= num DO
        INSERT INTO repl_test(id2,tx) values (mod(cnt,5),'TEST_TEXT');
        SET cnt = cnt + 1;
    END WHILE;
    COMMIT;
END
//
delimiter ;

プロシージャの内容として,引数で指定された値だけ,テーブルにINSERTします。投入するデータのうち,ID列はオートインクリメントが設定されているため指定していません。ID2列には,ループを実行した回数の5で割った余りを挿入します。TX列はTEST_TEXTという文字列を挿入します。

プロシージャを実行して1000000行のデータを登録します。

mysql> CALL ins_repl_test(1000000);
Query OK, 0 rows affected (8.96 sec)

CALL句の後に,先ほど作成したプロシージャ名と引数に挿入するデータ件数を指定しています。

スレーブにデータがレプリケーションされていることを確認します。スレープでSELECT文を実行します。

mysql> select count(*) from repl_test;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.03 sec)

これで初期設定は完了です。

著者プロフィール

北川健太郎(きたがわけんたろう)

LINE株式会社所属のデータベースエンジニア。担当はMySQLとOracle Database。好きなMySQLの機能はレプリケーションで,好きなOracleDatabaseの機能はログオントリガー。

Twitter:@keny_lala

コメント

コメントの記入