MySQLブログ | 株式会社ビーグッド・テクノロジー／システム監視サーバ監視障害監視運用管理インフラ構築 MySQL

MySQLブログエントリ一覧

MySQL パフォーマンス検証（ベンチマーク環境）

MySQL パフォーマンス検証として、弊社検証環境でベンチマークをとってみました。5.0系と5.1系、MyISAM、InnoDBをそれぞれ計測しています。とりあえず今回は環境紹介で、各ベンチマーク結果は次回より。

■ベンチマーク環境

【サーバ環境】

Server　　： DELL PowerEdge860 (1U）
CPU　　　： Intel(R) Xeon(R) CPU 3050 @ 2.13GHz　2cores
MEM　　　： 4G
DISK　　　： SEAGATE Model: ST373455SS (SAS,15000rpm,キャッシュ16M）
※ローカルディスク、NO　RAIDで利用
OS　　： CentOS5.2　x86_64　（Red Hat Enterprise Linux 互換OS)
カーネル　： 2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008
FileSystem : ext3 (LVM )

【テーブル定義】
CREATE TABLE IF NOT EXISTS normal(`id` int(10) unsigned NOT NULL auto_increment,`name` varchar(64) NOT NULL default ”,`email` varchar(64) NOT NULL default ”,`password` varchar(64) NOT NULL default ”,`dob` date default NULL,`address` varchar(128) NOT NULL default ”,`city` varchar(64) NOT NULL default ”,`state_id` tinyint(3) unsigned NOT NULL default ‘0′,`zip` varchar(8) NOT NULL default ”,`country_id` smallint(5) unsigned NOT NULL default ‘0′,PRIMARY KEY (`id`),UNIQUE KEY `email` (`email`),KEY `country_id` (`country_id`,`state_id`,`city`)) ENGINE=innoDB; もしくは　MyISAM;

※ENGINE（ストレージエンジン）はInnoDBとMyISAMの2種類で計測

【MySQL バージョン】

　MySQL Enterprise Server 5.0.79 SP1　x86_64-glibc23
　MySQL Enterprise Server 5.1.30 x86_64-glibc23

上記２バージョンで計測

【MySQL　初期化パラメータ】　※一部抜粋
skip-locking
key_buffer = 1250M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 64
query_cache_size = 128M
query_cache_type = 0 (OFF)
thread_concurrency = 4
max_connections=1500
net_read_timeout=30
net_write_timeout=30
back_log=128
innodb_buffer_pool_size = 1250M
innodb_additional_mem_pool_size = 50M
innodb_log_buffer_size = 4M
innodb_thread_concurrency=8
sync_binlog=1

【計測方法】

100万件が登録されたテーブルに対して、1分間のアイドリング検索を実施後、1分間×3回のクエリ数計測を実施し、その平均値を取得した。
同時実行数（Concurrent Threads)別に計測を実施。複数同時実行の値は、それぞれThreadで実行したクエリの合計値となる。
ベンチマークプログラムはPHP製。MySQLクライアントは、PHPのmysqliエクステンションを利用。
純粋なクエリ実行数の計測であり、DBサーバへの接続コストは含まれていない。

5月 27

まずちゃんとした設計。チューニングはその後で。

1.適切な主キーを設定する

InnoDBの主キーはクラスターインデックスだということを意識しようから導き出されること

更新する可能性がある項目は主キーにしない（主キーの更新はコスト高い！）
主キーの項目長はなるべく小さく（全部のインデックスページの容量に悪影響！）
できる限り主キーでアクセスする（副次インデックスにくらべて倍は速い！）

トランザクション系テーブルに主キーとして、AUTO_INCREMENTを使うのは100%ではないが、安全策と言えます。
マスター系のテーブルには自然キーを使うほうが良いでしょう。

2.物理フォーマットの選択

　※show table statusでRow_formatの設定値は確認出来ます。

InnoDBの物理フォーマット

MySQL 5.0.3以前のバージョンでは、REDUNDANTフォーマット、以降のバージョンではCOMPACTフォーマットがデフォルトになっている

少なくともREDUNDANTフォーマットでは、固定長が有利な点は一つもない
固定長にたいしても、カラム数やカラム長といった冗長な情報を含む

COMPACTフォーマットで文字列型にすべて可変長を使用すれば、実際のデータ内容に応じて、20-30%程度は容量が節約できます。
これはディスク容量だけではなく、バッファプールの効率にも直結します。

MyISAMの物理フォーマット

静的テーブル（FIXED）
データ型にVARCHAR、TEXT、BLOBを含んでいない場合に選択される

なにより一番速い

データファイル中の行がディスク上で即時見つけられる
キャッシュ動作が単純になる

動的フォーマットテーブルよりもディスクの領域を消費する

CHARやVARCHARは不足部分に空白が埋められる
BINARYとVARBINARYカラムは不足部分にが0×00で埋められる

クラッシュした後も修復出来る確率が高い

動的テーブル（DYNAMIC）

可変長項目(VARCHAR、 VARBINARY、 BLOB、または TEXT)を含むとこれが採用される
BLOBやTEXTカラムが無いテーブルなら静的テーブル（FIXED）も指定可能

圧縮テーブル

これはmyisampackで作成するもの

結論

　　　　InnoDBにはCOMPACTフォーマット
　　　　MyISAMにはFIXED
　を使用しましょう。

　※MyISAMをログ出力系にだけ使用するような場合は別です。この場合、MyISAMは
　　INSERTの処理性能によって選択されたので検索を速くする必要がないわけですから。

3.適切なデータ型の選択(文字列型)

　上記の物理フォーマットのことからも
　　InnoDBなら必ず可変長を選ぶ！
　　　MyISAMなら固定長を選ぶ！
　と言い切ってよいと思います。

4.適切なデータ型の選択(数値型)

　できる限り効率性の高い（最小）の型を使用する！

　MySQLのデータ型についてはこちらをどうぞ
　このページの下の方に記載されている「初期化パラメータsql_mode」についてはMySQLのSQL_MODEとストリクトモードのほうが詳しく書いてあります。

関連会社インターオフィスのブログ記事を転載しています。

7月 14

InnoDBの主キーはクラスターインデックスだということを意識しよう

InnoDBについて一番大事なことは主キーがクラスターインデックスだということです。

クラスターインデックスでは、主キー(B-tree)のリーフページにデータが直接格納されています。

以下の図のようなイメージです。

一方、主キー以外のインデックス（副次インデックス）はリーフページに主キーの値を格納
していて、データにアクセスするためにそれを使用します。

以下の図のようなイメージです。

主キーがクラスターインデックスであることの必然的結果　NO1

副次インデックスでデータにアクセスする場合に、
　1)副次インデックスのB-treeより主キーを取得する
　2)その主キーからデータをアクセスする
という２段階のアクセス経路になるというこです。

よって、副次キーでのアクセスは、たとえそれがユニークキーであったとしても、

主キーでのアクセスに比べて、倍近くのI/Oが発生することになります。

以下が計測した結果です。
InnoDB vs MyISAM パフォーマンス比較 PrimaryKEY、UniqueIndex、非UniqueIndex

「1.PrimaryKeyで一件検索」と「2.UniqueIndexで一件検索」のInnoDBどうしの結果を比較すると主キーに比べると、ユニークな副次インデックスは55%程度の性能しか出ていません。

主キーがクラスターインデックスであることの必然的結果　NO2

データの格納場所が主キーの値でクラスター化されているので、
　・データを連続してアクセスする場合（同一ページに存在する確率が高いので）、
　　バッファが有効に使用される
　・同じ理由から主キーの昇順等でアクセスする場合に性能が高い

ということになります。

これも
「3.PrimaryKeyで範囲検索」と「4.非UniqueIndexで範囲検索」のInnoDBどうしの結果を比較すると
5倍程度の違いが出ています。

主キーがクラスターインデックスであることの必然的結果 NO3

主キーでアクセスすると、必然的にデータにアクセスしてしまう。
よく、InnoDBはMyISAMに比較して、
SELECT COUNT(*) FROM テーブル　※where条件なし

　が圧倒的に遅いと言われますが、その原因の一部が上記にあたります。
　MyISAMの条件無しカウント処理が速いのは統計情報から結果を
　取得するので当たり前なのですが、InnoDBがこの処理に関して
　遅すぎ！なのは、条件無しカウント処理を主キーでアクセスするため
　全データページにアクセスしてしまうためです。
　SELECT COUNT(*) FROM テーブルの後ろに副次インデックスの
　ヒントを付ければ速くなります。

主キーがクラスターインデックスであることの必然的結果 NO4

主キーの値を変更する場合に、データ自体の格納場所を変更するためにコストが高い

主キーがクラスターインデックスであることの必然的結果 NO5

副次インデックスのリーフページのすべてに主キーが格納されるため
主キーの項目長が長くなった場合の悪影響が大きい。

その他 InnoDBのクラスターインデックスについて重要なこと

明示的に主キーを指定しない場合は以下の暗黙の主キーがMySQLによって設定されます
　　　1)NOT NULL指定のユニークキーを指定している場合、それが主キーになる

　　　2)それも無ければ、6byteのROWIDが主キーになる

関連会社インターオフィスのブログ記事を転載しています。

7月 13

更新があるシステムにはInnoDBを選ぼう。MyISAMを選択するならそれなりの理由が必要。それにInnoDBのパフォーマンスはそんなに悪くないよ。

基本はＩｎｎｏＤＢです。

MyISAMを選択できるようなケースを考えてみます。
・完全に検索Onlyの場合（基幹系とかから一定間隔で検索用テーブルを再構築する。それ以外の時間は検索のみのようなケース。）
・ログ系のテーブルを出力のみする場合（insertは3～15倍程度MyISAMが高速）
正直、これくらいなのかなと思います。

パフォーマンスについては（5.0.37以上を選択すれば）InnoDBはMyISAMと比べてほとんど同じです。

以下は計測結果です。

InnoDB vs MyISAM パフォーマンス比較 PrimaryKEY、UniqueIndex、非UniqueIndex
InnoDB vs MyISAM パフォーマンス比較取得件数が多い場合
 InnoDB vs MyISAM パフォーマンス比較 SELECT ・・・ LIMIT N
InnoDB vs MyISAM パフォーマンス比較副問い合わせ
 InnoDB vs MyISAM パフォーマンス比較 Inner Join
InnoDB vs MyISAM パフォーマンス比較 Left Join

ただし、更新系性能についてはトランザクション管理がある(InnoDB)とない(MyISAM)とでは当然段違いに性能が違います。
InnoDB vs MyISAM パフォーマンス比較 UPDATE
InnoDB vs MyISAM パフォーマンス比較 DELETEとINSERT

UPDATEで7倍程度、deleteで4～7倍程度、insertでは前述した通りに3～15倍程度MyISAMが高速です。
ただ、トランザクションがないDBMSで正しく？更新処理を実行することは原則不可能なので、どうしようもありません。

関連会社インターオフィスのブログ記事を転載しています。

6月 28