bokumin.org

RSS Sitemap Mail
ritual-clearcut

FreeBSDでZFSをMySQL向けにチューニングする

Category: FreeBSD, MySQL, ZFS
Tag:

Tuning MySQL on ZFS with FreeBSD

はじめに

私のWebサーバはFreeBSDのZFSを使っていて、atime=offくらいはやっていたものの、正直MySQLまわりは何もチューニングしておらず、デフォルトのrecordsize(128k)のままInnoDBを動かしていた状態でした。
いい加減見直そうと思って調べていたら、DB用チューニングをかなり具体的にまとめている良記事を見つけたので、そこで紹介されている考え方を参考に自分の環境にも当てはめてみることにしました。

環境

  • FreeBSD 14.4-RELEASE-p7
  • MariaDB 10.11.18 (mysqlコマンドで叩いているが実体はMariaDB。InnoDBまわりの話なので基本的にMySQLと共通)
mysql --version
# mysql  Ver 15.1 Distrib 10.11.18-MariaDB, for FreeBSD14.3 (amd64) using  EditLine wrapper

現状確認

まず現在のデータセットの構成を確認していきます。

zfs list -r zroot/var

NAME                 USED  AVAIL  REFER  MOUNTPOINT
zroot/var           1.06G   433G    96K  /var
zroot/var/audit       96K   433G    96K  /var/audit
zroot/var/crash       96K   433G    96K  /var/crash
zroot/var/db        1.06G   433G  1.06G  /var/db
zroot/var/log       7.29M   433G  7.29M  /var/log
zroot/var/mail       360K   433G   360K  /var/mail
zroot/var/tmp         96K   433G    96K  /var/tmp

/var/db自体が専用データセットになっておらず、zroot/varにそのまま同居している状態でした。
これだとMySQL用にrecordsizeなどを個別設定できないので、データセットとして切り出すところから始める必要がある感じです。

/var/db を専用データセットへ切り出し

/var/db関連のサービス停止

service mysql-server stop
# 他に/var/dbを使っているサービスがあれば合わせて停止

念のためバックアップ

tar -czf ~/var_db_backup_$(date +%Y%m%d).tar.gz -C /var -- db

既存の/var/dbを退避

親データセット内でのmvなので実体コピーは発生せず一瞬で終わります。

mv /var/db /var/db_orig

新しい空のデータセットを作成

空のデータセットを2つまとめて作っておきます。この段階でmysql用も分けておきます。

zfs create zroot/var/db
zfs create -o mountpoint=/var/db/mysql zroot/var/db/mysql

ZFSプロパティの適用

データセットが切り出せたので、ここからが本題のチューニングです。InnoDBのデータ用として以下を設定していきます。

zfs set recordsize=16k zroot/var/db/mysql
zfs set primarycache=metadata zroot/var/db/mysql
zfs set logbias=throughput zroot/var/db/mysql
zfs set compression=lz4 zroot/var/db/mysql

適用後の確認。

zfs get recordsize,primarycache,logbias,compression zroot/var/db/mysql

NAME                PROPERTY      VALUE           SOURCE
zroot/var/db/mysql  recordsize    16K             local
zroot/var/db/mysql  primarycache  metadata        local
zroot/var/db/mysql  logbias       throughput      local
zroot/var/db/mysql  compression   lz4             local

ログ用(ib_logfile等)は今回は分けずに同じデータセットのままとなっています。
※ログを分けるかどうか、innodb_flush_log_at_trx_commitの値とセットで考えるべきで、=2運用中の自分にとってrecordsizeの不一致以外はさほど効かないと思い今回は見送っています。

退避していた中身を戻す

データセットが先に分かれているので、rsyncは1回で済みます。所有権も念のため最後に揃えておきます。

rsync -aAXH /var/db_orig/ /var/db/
chown -R mysql:mysql /var/db/mysql

起動確認

zfs list -r zroot/var
NAME                 USED  AVAIL  REFER  MOUNTPOINT
zroot/var           1.06G   433G    96K  /var
zroot/var/audit       96K   433G    96K  /var/audit
zroot/var/crash       96K   433G    96K  /var/crash
zroot/var/db        1.05G   433G  45.6M  /var/db
zroot/var/db/mysql  1.01G   433G  1.01G  /var/db/mysql
zroot/var/log       7.29M   433G  7.29M  /var/log
zroot/var/mail       360K   433G   360K  /var/mail
zroot/var/tmp         96K   433G    96K  /var/tmp

無事にzroot/var/db、zroot/var/db/mysqlがマウントされているのがわかります。

service mysql-server start
service mysql-server status
mysql -e "SHOW DATABASES;"

mysql -eは接続ユーザーによっては権限エラーになったので、確認はrootユーザで行いました。

問題なければ退避分を削除

起動とDB一覧確認が取れてからしばらくはmysql_origとtarバックアップを残しておいて、問題なさそうだと思ったら削除しましょう。

rm -rf /var/db_orig/
rm -rf /var/db/mysql_orig
rm ~/var_db_backup_*.tar.gz

my.cnf側の変更

[mysqld]セクション、innodb_flush_log_at_trx_commitの下あたりに追加していきます。

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_doublewrite = 0

サービスの再起動・設定確認を行います。

service mysql-server restart
mysql -e "SHOW VARIABLES LIKE 'innodb_doublewrite';"   # OFFになっていればOK

各設定の理由

設定理由
recordsize=16kInnoDBのI/O単位に合わせて、128kのままだった無駄な読み書きを減らす
primarycache=metadataInnoDBバッファプール側で十分キャッシュしているので、ARC側は二重キャッシュせず節約
logbias=throughputREDOログはInnoDB側にあるので、ZFS側の同期書き込み最適化(latency寄り)は不要
innodb_doublewrite=0Copy on Writeなので部分書き込みが起きず、doublewriteによる保護が冗長になる

ベンチマーク結果

mysqlslapを使い、以下の条件で負荷をかけて計測しました。

  • 同時接続数(concurrency): 8
  • 1接続あたりのクエリ数: 2000
  • 上記を1セットとして、各設定で3回ずつ実行
  • テーブル・データは--auto-generate-sqlで自動生成した独立テスト用スキーマ

今回は「変更前」「ZFS元設定のままmy.cnfだけ変更」「ZFS・my.cnf両方変更」の3パターンを比較しました。

変更前(ZFS元設定 + my.cnf元設定)

回数平均実行時間最小最大fsyncs増分read_requests増分data_writes増分
13.312秒3.282秒3.389秒2,12294,18766
23.982秒3.816秒4.236秒2,11996,60265
37.785秒3.885秒13.644秒2,142105,43765
平均5.026秒3.661秒7.090秒2,127.798,74265.3

ZFS元設定 + my.cnfだけ変更後

回数平均実行時間最小最大fsyncs増分read_requests増分data_writes増分
10.154秒0.139秒0.185秒20768,89566
20.151秒0.144秒0.172秒20475,70765
30.154秒0.145秒0.179秒20268,47565
平均0.153秒0.143秒0.179秒204.371,02665.3

変更後(ZFS変更後 + my.cnf変更後、本来のチューニング設定)

回数平均実行時間最小最大fsyncs増分read_requests増分data_writes増分
10.137秒0.125秒0.177秒20467,54865
20.192秒0.136秒0.352秒20669,99366
30.136秒0.125秒0.172秒20268,21965
40.187秒0.125秒0.344秒20568,41566
平均0.163秒0.128秒0.261秒204.368,54465.5

※Innodb_buffer_pool_readsの増分はすべての設定で0でした。

考察

結果、「ZFS元設定 + my.cnfだけ変更」が「ZFS変更後 + my.cnf変更後」とほぼ同じ数字になりました。

平均実行時間fsyncs増分data_writes増分
変更前5.026秒2,127.765.3
my.cnfだけ変更0.153秒204.365.3
ZFS+my.cnf両方変更0.163秒204.365.5

fsyncsの増分はどちらも204台で一致し、平均実行時間も0.153秒 対 0.163秒とほぼ誤差の範囲です。つまり今回の「約32倍」という差は、ほぼ全てがinnodb_flush_log_at_trx_commitを1から2に変えたこと(およびinnodb_doublewrite=0)によるもので、recordsizeやprimarycache、logbias、compressionといったZFS側のプロパティ変更は、この負荷テストにおいては測定できるほどの差を生んでいなかった、ということになります。

今回使ったテストデータはInnoDBバッファプールに十分収まるサイズだったため、primarycache=metadataのようにARC側の二重キャッシュを削る設定の効果はそもそも出づらい条件だったと考えられます。またrecordsizeの効果も、既にバッファプールでヒットしている読み書きが中心だと表に出にくいはずです。ZFS側のチューニングがどれだけ効くかを正しく見るなら、バッファプールに乗り切らない規模のデータセットで、実際にディスクへの読み込みが発生するような負荷で計測し直す必要があると思います。

なおinnodb_flush_log_at_trx_commit=2は、コミット毎のfsyncをやめて1秒ごとのフラッシュに変える設定なので、OSクラッシュや電源断が起きた場合、直近最大1秒分のコミット済みトランザクションが失われる可能性があります(mysqldプロセスだけがクラッシュした場合は問題ありません)。今回の検証で速度の主要因がこの設定だとわかった以上、性能と引き換えにこのリスクを許容できるかどうかは、それぞれの環境で改めて判断してもらえればと思います。

ZFS側のプロパティ変更(recordsize=16k、primarycache=metadata、logbias=throughput、compression=lz4)自体は、InnoDBのI/O特性に沿った理にかなった設定だと今も思っていますし、compression=lz4はディスク使用量の削減にもつながるので、今回の結果だけで意味がなかったということでにはならないです。ただ「32倍速くなった」という数字の解釈には注意が必要で、その大部分はmy.cnf側の変更によるものだった、というのが今回の追加検証での結論です。

まとめ

これまではZFSはatime切っておけばOKくらいの雑な理解で使っていましたが、実際に/var/dbをデータセットとして切り出すところからやってみると、思っていたより手順自体はシンプルだったなと感じました。mv→zfs create→rsyncの簡単な手順で、途中でデータを壊す心配もほぼないので良きです。

パラメータ側も、recordsizeやprimarycacheはなんとなくデフォルトのままでしたが、一つずつ理由を確認しながら当てはめるのが納得できてよかったと思いました。特にinnodb_doublewriteをオフにできる理屈(CoWだから部分書き込みが起きない)は、ファイルシステム側の仕組みとDB側の仕組みがちゃんと噛み合っている感じがして地味に感動しました。

ZFSはデータセット単位でこういう細かい調整ができるのが本当に楽ですね。パーティション全体で一律の設定にせざるを得ないファイルシステムだと、こうはいかないです。用途ごとにデータセットを切って、それぞれに最適なプロパティを当てる、という運用がすんなりできるのはやっぱり便利だと再認識しました。

おわり

参考にした記事