データベースサーバを新規に増設するために、最新の MySQL を調査していたところ、現時点での公式な 5.0 系の最新版はなぜかバイナリが公開されていない 5.0.75 ということが分かりましたが、同時に Percona Inc. という会社が独自にパッチをあててビルドしている Percona MySQL というものがあることを知りましたので、実際に試しつつベンチマークをとってみました。
Percona は、MySQL Performance Blog を書いていることで有名です。
まず、Percona MySQL とは、Percona Lab によると次のような説明があります。
Percona builds binaries that contain recent versions of the MySQL database server, plus additional popular patches not included in the official binaries from MySQL/Sun. Some patches improve InnoDB performance under high loads. Others provide diagnostic tools useful to DBAs. The patches are authored by MySQL users like Google http://code.google.com/p/google-mysql-tools, Proven Scaling, Open Query, and Percona itself.
翻訳すると、次のとおりです。
Percona のビルドバイナリは、最近の MySQL データベースサーバにバージョンに加えて MySQL/Sun からの公式ビルドには含まれていない人気のあるパッチを追加したものです。いくつかのパッチは、高負荷の状況下で InnoDB のパフォーマンスを向上するものです。他社などから提供されている DBA のための計測ツールです。これからのパッチは Google、Proven Scaling、Open Query、と Percona 自身のような MySQL ユーザが作成したものです。
今回、新規に構築したいデータベースサーバはすべて InnoDB の構成なので、Percona MySQL を使う選択肢もありかなと考えました。さらに RHEL5 x86_64 用の RPM が公開されているので、すぐに試すことができたのも幸いしていました。
まず、Percona MySQL にはどんなパッチがあたっているかは、wiki にまとまっています。Percona MySQL には、通常版と highperf 版の二つのリリースがあり、詳しい情報はリリース情報にまとめっています。MySQL 5.0.75 をベースにした build 11 は、次のようなパッチが適応されています。
- innodb_check_defrag.patch by Percona
- userstatsv2.patch by Google
- show_patches.patch by Jermey Cole
- innodb_io_patches.patch by Percona
- innodb_show_hashed_memory.patch by Percona
- innodb_io_pattern.patch by Percona
- microsec_process.patch by Percona
- innodb_locks_helpd.patch by Percona
- microslow_innodb.patch by Percona
- mysqld_safe_syslog.patch by Percona
- innodb_fsync_source.patch by Google
- innodb_show_bp.patch by Percona
さらに HighPerf 版では、さらに次の二つのパッチが適応されています。
- split_buf_pool_mutex_fixed_optimistic_safe.patch by Yasufumi Kinoshita
- innodb_rw_lock.patch by Yasufumi Kinoshita
- innodb_check_defrag(innodb_check_fragmentation): このパッチは、MySQL サーバにいくつかのセッションスコープのステータス変数を追加します。自分のデータベース接続によって実行された最後のクエリーでスキャンされた InnoDB データのフラグメンテーションを計算できるための変数を使うことができます。これからの変数は、クエリーごとに初期化されるのでデータベース接続しつづけても増え続けることはありません。そのかわりもっとも最近のクエリーしか計測できません。一つのテーブルやインデックスに対する単純なクエリーに役に立ちます。これからはリーフページに対してのみ計測することができます。具体的な例は、wiki に掲載されています。
- userstatsv2.patch: show user/table/index の状態に INFORMATION_SCHEMA.*_STATISTICS を追加する。
- show_patches.patch: show patches コマンドを追加する
- innodb_io_patches.patch: このパッチは、木下さんのパッチを含んでいて、次の設定パラメータを追加します
- innodb_read_io_threads: 読み込みリクエスト用のバックエンドのスレッド数(デフォルトは1)
- innodb_write_io_threads: バッファプールからの書き込み用のバックエンドのスレッド数(デフォルトは1)
- innodb_read_ahread: InnoDB のネイティブ read-ahead を制御するかどうか
- innodb_io_capacity: サーバが処理できる秒間あたりの i/o の数
- innodb_adaptive_checkpoint: adaptive checkpointing 機能を追加するかどうか、adaptive checkpointing 機能については wiki に説明されています
- innodb_show_hashed_memory.patch: このパッチは、show innodb status コマンドの出力の BUFFER POOL AND MEMORY セクションに InnoDB の内部的なハッシュテーブルサイズを表示します
- innodb_io_pattern.patch: このパッチは、INFORMATION_SCHEMA に新しい innodb_io_pattern テーブルを追加します。このテーブルは、データページごとの入出力数を表示します。
- microsec_process.patch: このパッチは、PROCESSLIST に新しい TIME_MS という新しいカラムを追加したテーブルを追加します。TIME_MS は TIME と同じで TIME をマイクロ秒単位で表示します。
- innodb_locks_helpd.patch: このパッチは、InnoDB の状態出力にさらにコンパクトにしてデッドロックが起きても一杯にならずに見つけるのに困難な役に立つ情報となります。innodb_show_locks_held、innodb_show_verbose_locks という変数を追加します。
- microslow_innodb.patch: 時間のかかったクエリーを記録する slow query log の出力にマイクロ秒単位の情報を追加します。
- mysqld_safe_syslog.patch: error.log への出力を syslog-ng へのリダイレクトを許可するパッチ
- innodb_fsync_source.patch: このパッチは、Google からのパッチです。このパッチは、fsync 読み出しによって初期化されたフォームについての情報を表示します。この情報は、SHOW INNODB STATUS の出力で利用可能です。これらは BACKGROUND THREAD セクションにあります。
- innodb_show_bp.patch: InnoDB のバッファプールの内容を表示するパッチ
show patches コマンドの出力例
$ mysql -u root -e ‘show paches’
File Name Version Author License Comment
innodb_io_patches.patch Cluster of past InnoDB IO patches 1.0 Percona
GPL This patch contains fixed (control_flush_and_merge_and_read, control_io-threads, adaptive_flush)
innodb_show_bp.patch show innodb buffer pool content 1.0 Percona <info@percona.com> GPL
userstatsv2.patch SHOW USER/TABLE/INDEX statistics V2 Google
GPL Added INFORMATION_SCHEMA.*_STATISTICS
mysqld_safe_syslog.patch Patch allows redirect output of error.log to syslog-ng 1.0 Percona <info@percona.com> GPL Ported from Debian
microsec_process.patch Adds INFOMATION_SCHEMA.PROCESSLIST with TIME_MS column 1.0 Percona <info@percona.com> GPL
innodb_show_hashed_memory.patch Adds additional information of InnoDB internal hash table memories in SHOW INNODB STATUS 1.0 Percona <info@percona.com> GPL
さらに HighPerf 版に適応されているパッチは、次のとおりです。
- split_buf_pool_mutex_fixed_optimistic_safe.patch: InnoDB のバッファプールのスケーラビリティを修正するパッチ。
- innodb_rw_lock.patch: InnoDB のスケーラビリティで分かっている項目を修正するパッチです。このパッチは、rw_locks の実装したものです。SMP のパフォーマンスを向上させるパッチに似ている。
この二つのパッチは、MySQL ユーザ会の木下さんという方が作成されたようで、Percona にジョインして MySQL Performance Blog にも投稿されています。
さっそく、mysqlbench でベンチマークをとってみました。
ハードウェア環境
- CPU: Intel(R) Xeon(R) CPU X3210 @ 2.13GHz
- メモリ 8GB
ソフトウェア環境
- OS: CentOS 5.2 x86_64
- 対象の MySQL
- MySQL 5.0.51b 設定なし
- MySQL 5.0.51b 設定済み
- MySQL 5.0.75
- Percona MySQL 5.0.75 b11
- Percona MySQL 5.0.75 b11 HighPerf
設定済みとは、MySQL のソースコードに含まれている my-innodb-heavy-4G.cnf を元に InnoDB のパラメータを設定しました。
- max_connections = 500
- innodb_additional_mem_pool_size = 32M
- innodb_buffer_pool_size = 4G
- innodb_data_file_path = ibdata1:10M:autoextend
- innodb_file_io_threads = 4
- innodb_thread_concurrency = 16
- innodb_flush_log_at_trx_commit = 1
- innodb_log_buffer_size = 8M
- innodb_log_file_size = 256M
- innodb_log_files_in_group = 3
- innodb_max_dirty_pages_pct = 90
- innodb_lock_wait_timeout = 120
ベンチマークの方法
- mysqlbench には、libmyclient_r という MySQL のスレッドセーフなクライアントライブラリが必要なので、念のため MySQL のバージョンを変えるごとにコンパイルする
- それぞれの MySQL に対して、mysqlbench でテスト用のテーブルを作ってから、負荷のない状態(ロードアベレージ(0.00)でベンチマークを計測する
- ./mysqlbench -i -U test -P test -E innodb -s 100 testdb
- ./mysqlbench -U test -P test testdb -c 50 -t 500 testdb
ベンチマーク結果
<MySQL 5.0.51b 設定なし>
transaction type . . . : TPC-B (sort of)
scaling factor . . . : 100
number of clients . . : 50
number of transactions per client : 500
number of transactions actually processed : 25000/25000
tps (include connections establishing) . : 244.858755
tps (exclude connections establishing) . : 244.890391
<MySQL 5.0.51b 設定済み>
transaction type . . . : TPC-B (sort of)
scaling factor . . . : 100
number of clients . . : 50
number of transactions per client : 500
number of transactions actually processed : 25000/25000
tps (include connections establishing) . : 5994.555505
tps (exclude connections establishing) . : 6012.193691
<MySQL 5.0.75 設定済み>
transaction type . . . : TPC-B (sort of)
scaling factor . . . : 100
number of clients . . : 50
number of transactions per client : 500
number of transactions actually processed : 25000/25000
tps (include connections establishing) . : 5928.151749
tps (exclude connections establishing) . : 5947.056920
transaction type . . . : TPC-B (sort of)
scaling factor . . . : 100
number of clients . . : 50
number of transactions per client : 500
number of transactions actually processed : 25000/25000
tps (include connections establishing) . : 5913.270301
tps (exclude connections establishing) . : 5930.991256
<Percona MySQL 5.0.75 b11 設定済み>
transaction type . . . : TPC-B (sort of)
scaling factor . . . : 100
number of clients . . : 50
number of transactions per client : 500
number of transactions actually processed : 25000/25000
tps (include connections establishing) . : 6037.890419
tps (exclude connections establishing) . : 6053.547258
<Percona MySQL 5.0.75 b11 HighPerf 設定済み>
transaction type . . . : TPC-B (sort of)
scaling factor . . . : 100
number of clients . . : 50
number of transactions per client : 500
number of transactions actually processed : 25000/25000
tps (include connections establishing) . : 6024.277840
tps (exclude connections establishing) . : 6041.377637
ベンチマークをとってみた結果、このベンチマークではそれほど変わらないなという印象。いくつか InnoDB の Status が追加されていてモニタリングするには便利だとは思うけれど、長所と短所をまとめてみる。
長所:
- 木下さんのエントリをみると、かなり InnoDB のパフォーマンス改善されている
- status 系のコマンドが追加されていて、MySQL をモニタリングするときに便利
短所:
- 公式ビルドではない
- /etc/init.d/mysql スクリプトが若干使いにくい(具体的には、/var/lib/mysql を削除したあと start しても mysql_install_db をしてくれない)
- mysql-server、mysql、mysql-devel といったパッケージとコンフリクトしてしない管理がすこし複雑になる
今回はパフォーマンスより、管理コストなどが大きいと判断して MySQL 5.0.75 公式のものを自分でビルドしたものを使うことにします。この他にも、
max_connections や thread_cache のチューニングなどがあって、まず本番投入して MySQL の show status や show innodb status の値を Ganglia でグラフにしながらチューニングしていきたいと思います。
また、最近
OutDelta という Percona MySQL よりさらにいろいろなパッチがあたっている MySQL の派生バージョンも登場していたので、同じマシンでベンチマークをとってみた。OurDelta の
パッチとプラグインの一覧を見ると、サードパーティ製のストレージエンジンも使えるようになっている。
<MySQL-OurDelta-server-5.0.67.d7-44>
transaction type . . . : TPC-B (sort of)
scaling factor . . . : 100
number of clients . . : 50
number of transactions per client : 500
number of transactions actually processed : 25000/25000
tps (include connections establishing) . : 5576.523283
tps (exclude connections establishing) . : 5595.084204
OurDelta の方は、若干パフォーマンスが出ていない様子。
データベースサーバは、インフラの中でもかなり重要なコンポーネントなので、ただ MySQL をインストールするだけでなくてしっかりと設定してパフォーマンスを向上させてサーバのリソースを有効活用したいものです。
Tags: percona mysql
Subscribe RSS Feed
