MySQLバックアップ頂上決戦！ - ニフティクラウドのサーバーで最適なバックアップ手法はどれだ？ -
こんにちは、ちなつです。
さて今回は、株式会社ドリーム・アーツで小売事業者向けSaaS「Shopらん」を企画、開発しているライター・石田健亮氏の記事をご紹介します。
（＊本記事の最後に石田氏のプロフィールを掲載しています）
*********************************************************************
こんにちは。どうも最近本番環境のMySQLでバックアップを取得している最中のDBサーバーの性能の低下に悩まされている株式会社ドリーム・アーツの石田です。
皆さん、MySQLデータベースのバックアップはどのように取得してますでしょうか？
私のチームで運用している多店舗運営企業向けのコミュニケーションサービス「Shopらん」では、LVMを利用して週に一度データディレクトリのスナップショットを作成し、ディレクトリ全体をバックアップ用のサーバーに転送してフルバックアップを取得しています。
また、残りの6日はMySQLが生成するバイナリログをファイルとして転送することでフルバックアップからの差分を保存しています。
このLVMスナップショット方式を採用する以前は、mysqldumpコマンドを利用してSQLベースでバックアップしていました。mysqldump方式の難点はとにかくレストアに時間がかかることです。データベースをレストアしないといけないような悲惨な状況に追い込まれた時、レストアに時間がかかるのは致命的です。
LVMスナップショット方式だと、データディレクトリそのままですからファイルのコピーだけでレストアが完了します。移行当時はそれは魅力的に見えたものです。
ところが、LVMスナップショット方式も万能ではありません。
この方式の最大の弱点は、スナップショットを取得している間、元のファイルシステムに対する書き込み性能がガタ落ちになってしまう点です。
現在は、トラフィックが少ない深夜にフルバックアップを取得しているので、性能が落ちてもあまり問題はないのですが、そろそろフルバックアップが朝までに完了しない時のことを考えなくてはいけない時期になりました。
今回は、MySQLバックアップ頂上決戦と称して、ニフティクラウドのLinuxサーバーにMySQLをインストールして、mysqldumpとLVMのスナップショットというMySQLのフルバックアップのツートップで、それぞれバックアップにかかる時間、そしてバックアップ中のオンライン性能の低下具合について調査して、バックアップを取るならコレだ！というベストプラクティスを研究していきたいと思います。
テスト環境の作成
データベースの性能を計測するならば TPC-C でしょうということで、TPC-C のサブセットを MySQL 向けに実装した tpcc-mysql (https://code.launchpad.net/~percona-dev/perconatools/tpcc-mysql)を使います。
tpcc-mysql では、テストの規模を warehouse という単位で指定します。この数値が大きいとテストデータのレコード数も大きくなります。今回は何度もテストを繰り返す必要があるのでやや小さめの warehouse=100 でテストします。このときの MySQL のデータディレクトリのサイズはおよそ 9.2GB です。
DBサーバーにするサーバーは、以下の環境です。
| OSイメージ | CentOS 5.6 Plain |
| サーバータイプ | large16 (4CPU/16GB) |
| ディスク | disk100 100GB |
また、バックアップデータを保存するバックアップサーバーは、以下の環境です。
| OSイメージ | CentOS 5.6 Plain |
| サーバータイプ | small (1CPU/1GB) |
| ディスク | disk40 100GB |
DBサーバーのデータをバックアップサーバーに転送してディスクで保存するというよくある構成です。先日リリースされたばかりのCentOS 5.6のイメージを使って作成してみました。
それでは早速、テストする環境を構築していきましょう。
DBサーバーの構築
LVMのスナップショットを使いたいため、disk100のディスクのパーティションテーブルは以下のように設定しました。
# fdisk /dev/sdb
コマンド (m でヘルプ): p
Disk /dev/sdb: 107.3 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 cylinders
Units = シリンダ数 of 16065 * 512 = 8225280 bytes
デバイス Boot Start End Blocks Id System
/dev/sdb1 1 13054 104856223+ 8e Linux LVM
このディスクから90GBを /data として切り出し、残りの10GBはスナップショット取得時の更新データの記録用に残しておきます。
# partprobe
# pvcreate /dev/sdb1
# vgcreate vg0 /dev/sdb1
# lvcreate --size 90G -n data vg0
# mkfs.ext3 -m 0 /dev/vg0/data
作成したファイルシステムを /data としてマウントします。
# tune2fs -c 0 -i 0 -L /data /dev/vg0/data
# mkdir /data
# vi /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SWAP-sda2 swap swap defaults 0 0
LABEL=/data /data ext3 defaults 0 0
# mount -a
# df -kh
Filesystem サイズ 使用 残り 使用% マウント位置
/dev/sda3 27G 1.8G 24G 8% /
/dev/sda1 494M 17M 452M 4% /boot
tmpfs 2.0G 0 2.0G 0% /dev/shm
/dev/mapper/vg0-data 89G 184M 89G 1% /data
ディスクの準備ができたので、MySQLをインストールします。
# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.5/MySQL-client-5.5.15-1.rhel5.x86_64.rpm
# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.5/MySQL-devel-5.5.15-1.rhel5.x86_64.rpm
# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.5/MySQL-server-5.5.15-1.rhel5.x86_64.rpm
# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.5/MySQL-shared-5.5.15-1.rhel5.x86_64.rpm
# rpm -ivh MySQL-*
large16のサーバーは、4CPUでメモリも16GBもあるので、/etc/my.cnf ファイルは以下のような設定にしてみました。
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
basedir = /usr
datadir = /data/mysql
tmpdir = /data/tmp
plugin_dir = /usr/lib64/mysql/plugin
port = 3306
socket = /var/lib/mysql/mysql.sock
key_buffer_size = 16M
max_allowed_packet = 64M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 512K
join_buffer_size = 256K
open_files_limit = 5500
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
log-bin = mysql-bin
binlog_format = MIXED
server-id = 1
character-set-server=utf8
skip-character-set-client-handshake
default-storage-engine=InnoDB
max-binlog-size=1G
sync-binlog=1
expire-logs-days=10
max_connections = 120
table_cache = 200
table_open_cache = 4000
table_definition_cache = 400
innodb_buffer_pool_size =14G
innodb_additional_mem_pool_size = 16M
innodb_file_per_table
innodb_autoextend_increment = 64M
innodb_log_files_in_group = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 16
innodb_commit_concurrency = 10
[mysqld-safe]
log-error=/var/log/mysqld.log
pid-file=/data/mysql/mysqld.pid
データディレクトリを初期化して、MySQLサーバーをスタートします。
# mkdir /data/tmp && chown mysql.mysql /data/tmp
# mysql_install_db --user=mysql
# service mysql start
# mysql_secure_installation
MySQLのインストールが完了したら、次に tpcc-mysql をインストールします。 tpcc-mysql は、バイナリパッケージが無いのでBazaarでソースコードリポジトリからソースコードを取得してビルドします。
# rpm -Uvh http://download.fedora.redhat.com/pub/epel/5/i386/epel-release-5-4.noarch.rpm
# yum -y install bzr
# bzr branch lp:~percona-dev/perconatools/tpcc-mysql
# cd tpcc-mysql/src
# make all
# cd ..
tpcc-mysql 用のデータベースとテーブルを作成します。
# mysql -uroot -p
mysql> grant all privileges on tpcc.* to tpcc@'%' identified by 'password';
# mysqladmin -utpcc -p create tpcc
# mysql -utpcc -p tpcc < create_table.sql
# mysql -utpcc -p tpcc < add_fkey_idx.sql
それでは、テスト用のデータをデータベースにロードします。
# ./tpcc_load localhost tpcc tpcc password 100
最後の 100 の部分が、テストデータのサイズを決める warehouse パラメータです。100を指定した時、このサーバーでは78分かかりました。
バックアップサーバーの構築
次にバックアップを保存するためのバックアップサーバーを構築します。
バックアップサーバーは、アーカイブ用途で提供されるdisk40を接続してDBサーバーと同様にLVMでパーティションを作成し、/backup にマウントします。
# fdisk /dev/sdb
コマンド (m でヘルプ): p
Disk /dev/sdb: 107.3 GB, 107374182400 bytes
255 heads, 63 sectors/track, 13054 cylinders
Units = シリンダ数 of 16065 * 512 = 8225280 bytes
デバイス Boot Start End Blocks Id System
/dev/sdb1 1 13054 104856223+ 8e Linux LVM
# partprobe
# pvcreate /dev/sdb1
# vgcreate vg0 /dev/sdb1
# lvcreate --extent 100%FREE -n backup vg0
# mkfs.ext3 -m 0 /dev/vg0/backup
# tune2fs -c 0 -i 0 -L /backup /dev/vg0/backup
# mkdir /backup
# vi /etc/fstab
LABEL=/ / ext3 defaults 1 1
LABEL=/boot /boot ext3 defaults 1 2
tmpfs /dev/shm tmpfs defaults 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
LABEL=SWAP-sda2 swap swap defaults 0 0
LABEL=/backup /backup ext3 defaults 0 0
# mount -a
DBサーバーからの転送には、scpを使って転送するため、DBサーバーからバックアップサーバーにSSHでパスワードなしでログインできるように設定します。
一番簡単なのは、DBサーバーで ssh-keygen コマンドを使って ~/.ssh/id_rsa.pub という公開鍵を作成し、このファイルの中身をバックアップサーバーの、~/.ssh/authorized_keys に追記すればOKです。
それでは、さっそくベンチマークを取得します。
基準となるデータの計測
まず、tpcc-mysql を使って、バックアップとは関係なく TPC-C の計測を行います。
今回は、TPC-C の計測自体が目的ではなく、あくまでもバックアップの実行によるオンライン性能の低下を計測したいだけなので、同時接続数を変えて限界性能を探るようなことはせず、同時接続数は常に10で計測しています。
また、tpcc-mysql は、計測の度にデータセットが更新されてしまうので、毎回ロード直後のデータに戻して実施しています。
tpcc-mysql を実行するときのコマンドは以下のパラメータで行いました。
# ./tpcc_start localhost tpcc tpcc password 100 10 300 1200
| 同時接続数: | 10 |
| ランプアップ時間: | 300秒 (5分) |
| 計測時間: | 1200秒 (20分) |
結果は、
| 1回目 | 2235.2 |
| 2回目 | 2218.9 |
| 3回目 | 2192.7 |
| 平均 | 2215.6 |
まあこんなもんでしょう。
エントリーNo.1 mysqldump
① 負荷なしでバックアップ
MySQLに接続しているプログラムが存在しない状態で、mysqldumpコマンドを使ってデータベースのデータをすべてSQLで出力し、それをDBサーバーで圧縮してバックアップサーバーに転送します。
バックアップ時のコマンドはこれ。IPアドレスはご自身の環境に置き換えてください。
# mysqldump -uroot -p'password' --quick \
--single-transaction --master-data=2 --flush-logs \
--hex-blob --all-databases | gzip -c | ssh 10.xxx.xxx.xxx 'cat > /backup/all-data.sql.gz'
3回実行したときの結果は以下の通りです。
| 1回目 | 12m02s |
| 2回目 | 11m22s |
| 3回目 | 12m09s |
なお、バックアップにかかる時間のボトルネックはバックアップサーバーのdisk40のディスクへの書き込みでした。
② tpcc-mysql を実行しながらバックアップ
それでは、バックアップを取得している最中でのオンライン性能を測定するため tpcc-mysql を実行している最中にバックアップを取得します。
tpcc_start コマンドを開始して5分経過しランプアップが完了したら mysqldump コマンドをループで実行して、そのときの TPC-C 値を計測してみます。計測中の15分間は常にバックアップ処理が行われていることになります。
| 1回目 | 1366.6 |
| 2回目 | 1495.2 |
| 3回目 | 1398.1 |
| 平均 | 1419.9 |
厳しい結果です。
tpcc-mysql を実行している最中でも、バックアップ処理のボトルネックはバックアップサーバー側のdisk40のディスクでした。バックアップにかかる時間は負荷なしの状態と変わりません。しかし、ご覧のようにmysqldumpの実行によりオンライン性能はかなり低下してしまっています。
また、計測している15分間、性能が安定しません。1回目のバックアップ時にはI/Oが大量に発生し10秒あたり10トランザクションもこなせない瞬間がありますが、2回目になるとlarge16のサーバーのメモリサイズが効果を発揮して常に300トランザクションを超えるようになります。
通常はDBの全てのデータがメモリに載ることは無いでしょうから、1回目のバックアップ時の性能が実際の利用に近いはずです。そのため先ほどのTpmCの数値は実際よりも高く計測されています。
24時間オンライン処理が必要なサービスで、mysqldumpでバックアップを取得するのはあきらめた方がいいでしょう。
エントリーNo.2 LVMスナップショット
次に、LVMを使ってMySQLのデータディレクトリのスナップショットを作成して、それを読み込み専用でマウントしてバックアップを取ります。
スナップショットを生成するには、まずLVMのボリュームグループでスナップショット用に確保できるPE(物理エクステント)の数を調べます。これは次のコマンドで知ることができます。
# vgdisplay | awk '/Free PE/ {print $5}'
2559
この場合、2559が空きPEの数です。
/data のスナップショットを作成するには、デバイスとして /dev/vg0/data を指定して lvcreate コマンドを呼び出します。このとき先ほどの空きPE数を入力します。
# /usr/sbin/lvcreate -s -l 2559 -n data_snap /dev/vg0/data
Logical volume "data_snap" created
lvcreate が完了すると、/dev/vg0/data_snap として /dev/vg0/data のスナップショットが作られます。このデバイスを読み込み専用としてマウントします。
# mount -o ro /dev/vg0/data_snap /data_snap
マウントした後は、tar コマンドでディレクトリの内容をまとめて、ssh でバックアップサーバーに転送してバックアップを保存します。
# cd /data_snap && tar zcv mysql | ssh 10.xxx.xxx.xxx "cat > /backup/data-snap.tgz"
バックアップが完了したら、アンマウントして lvremove でスナップショットを解除します。
# umount /data_snap
# lvremove /dev/vg0/data_snap
これらの手順に、MySQLのテーブルの一時的なロックなどを追加したフルバックアップのスクリプトは以下の通りです。
IPアドレスなどをご利用の環境に書き換えて利用してください。
#!/bin/bash
export LANG=C
TODAY=`date +%Y%m%d%H%M`
MYHOST=`hostname -s`
TARGET="/backup/${MYHOST}_full_${TODAY}"
BACKUP_HOST=10.xxx.xxx.xxx
DEV=/dev/vg0/data_snap
SNAP_DIR=/data_snap
CURRENT_DIR=`pwd`
on_exit() {
cd $CURRENT_DIR
umount $SNAP_DIR
/usr/sbin/lvremove -f $DEV
}
SQL=$(cat </dev/null
/usr/sbin/lvremove -f $DEV
ssh $BACKUP_HOST "mkdir -p $TARGET" &&
sync &&
sync &&
mysql -u root -p'password' -e "$SQL" 2> /dev/null | ssh $BACKUP_HOST "cat > $TARGET/hotbackup.log" &&
mkdir -p $SNAP_DIR &&
mount -o ro $DEV $SNAP_DIR
trap on_exit EXIT
cd $SNAP_DIR &&
tar zcv mysql | ssh $BACKUP_HOST "cat > $TARGET/mysql_${MYHOST}_${TODAY}.tgz"
① 負荷なしでバックアップ
ではこのスクリプトを使って mysqldump と同様に負荷が無い状態でバックアップを取得してみます。
| 1回目 | 15m 24s |
| 2回目 | 15m 33s |
| 3回目 | 15m 18s |
こちらもバックアップサーバーのdisk40への書き込みがボトルネックです。データファイルそのままなのでmysqldumpよりも若干長い時間がかかっています。
② tpcc-mysqlを実行しながらバックアップ
tpcc-mysql を実行して、バックアップ中のオンライン性能を計測してみます。
条件は、mysqldump と同じです。
結果、TPC-Cが既定する時間内に終了しないトランザクションが発生しています。
<Constraint Check> (all must be [OK])
[transaction percentage]
Payment: 43.49% (>=43.0%) [OK]
Order-Status: 4.35% (>= 4.0%) [OK]
Delivery: 4.33% (>= 4.0%) [OK]
Stock-Level: 4.37% (>= 4.0%) [OK]
[response time (at least 90% passed)]
New-Order: 92.71% [OK] ※ NG
Payment: 93.98% [OK] ※ NG
Order-Status: 100.00% [OK]
Delivery: 99.62% [OK] ※ NG
Stock-Level: 100.00% [OK]
すでにこの時点でダメですが、一応TpmCを載せておきます。
| 1回目 | 375.4 |
| 2回目 | 130.4 |
| 3回目 | 136.6 |
| 平均 | 214.1 |
あまりにも悲惨な結果です。mysqldump にも惨敗の90%ダウンです。
「Shopらん」では今はまさにこの方式でバックアップを取得しています。どおりでバックアップが動いているとレスポンスタイムが劇的に悪化するわけです。
LVMでスナップショットを取ってバックアップしてはいけません。
エントリーNo.3 バックアップサーバーにレプリケーション
よく使われている mysqldump も LVM もバックアップ中のオンライン性能はガタ落ちで頂上決戦というには不甲斐なさすぎます。
最終兵器としてバックアップサーバーにMySQLをインストールして、レプリケーションを構成しレプリケーション側からバックアップしてみます。
DBサーバーはバックアップとは一切無縁になるので、理論上パフォーマンスへの影響はゼロになるはずです。
レプリケーション側は、アプリでは一切参照しないDBになるのでメモリも同時接続数も大した数はいりません。マスター側の /etc/my.cnf と比較して以下の値を調整します。
server-id = 999 ※マスターと異なる数値に変更します
datadir = /backup/mysql ※disk40のマウントポイントが/backupなのでそれにあわせます
tmpdir = /tmp ※一時テーブルの作成先を /tmp に変更します
innodb_buffer_pool_size =320M ※メモリサイズが1GBなのでそれに合わせて縮小します
レプリケーションの開始の仕方は、多くの本やWebサイトで解説されていますので、そちらを参照してください。
バックアップは以下のように、レプリケーションを一時的に停止して行います。
# mysqladmin -uroot -p'password' stop-slave
# mysql -A -ustores -p'password' storesdb -e 'show slave status\G';
# mysqldump -uroot -p'password' --quick --single-transaction \
--master-data=2 --flush-logs --hex-blob --all-databases | gzip -c > /backup/all-data.sql.gz
# mysqladmin -uroot -p'password' start-slave
この方法でだいたいちゃんとバックアップできているようです。「だいたい」と歯切れが悪いのは、まだこの方法は私自身が実戦投入していないためで、このあたりの続報はまた改めて書きたいと思います。
早速、tcpp-mysql を実行しながらバックアップしました。
| 1回目 | 2070.9 |
| 2回目 | 2127.1 |
| 3回目 | 2098.1 |
| 平均 | 2098.7 |
(TpmC)
レプリケーションがある分若干性能が落ちてますが、予想通りとなりました。
オンラインのトランザクション性能を落とさずに、フルバックアップを取得するにはレプリケーションを利用するほかなさそうです。
結果のまとめ
mysqldump は、バックアップ取得中におよそ40%のパフォーマンスの低下があります。しかし、DBのサイズがあまり大きくなくバッファプールにすべてが載ってしまうようなサイズのときは悪くない選択です。この場合はほとんど性能低下なしにバックアップ可能です。
LVM でのスナップショットは、レストアが高速というメリットはあるものの、バックアップ取得中のパフォーマンスの低下は強烈です。本番環境では利用しない方がいいでしょう。
レプリケーションでのバックアップは、オンライントランザクションに一切影響を与えずにバックアップを取得できます。副次的な効果としてアプリレベルでのトラブル時にオンラインに影響を与えずにデータベースの調査を行うことも可能なのでお勧めです。
というわけで、仕事が増えてしまいました。早速バックアップの方法を変更します。
それではまた。
------------------------------------
プロフィール
ライターネーム：石田 健亮
株式会社ドリーム・アーツで小売事業者向けSaaS「Shopらん」
を企画、開発。
メインの仕事はプログラマーだがサーバー管理や営業もこなす
ユーティリティプレイヤー。
最近好きな事はパフォーマンスチューニング。
特に並列化プログラミングがマイブーム。
キライなことはデータセンターでの作業。騒音と乾燥が弱点。
ニフティクラウドでデータセンターに行く必要が無くなったことが
本当の利点だと思っている。
------------------------------------