【研究課題レポート】MySQL ストレージエンジン性能比較

※これは社員のOkadaさんによって執筆された、第1回研究課題レポートの優秀研究賞受賞作品の抜粋です。古い情報ですので、最新のバージョンを利用する方は、結果を参考程度にとどめてください。

１．使用するOS, HW, MySQL,サービス,ベンチマークについて

１．１　使用するOSとHW

• OS



OS	CnetOS4.6(Final)
Kernel	2.6.9-67.ELsmp
I/O scheduler	Cfq
File system	ext3



• HW



CPU	Quad Core Intel(R) Xeon(R) CPU X5460 @ 3.16GHz * 1
Memory	4G
RAID	ServeRAID-8k RAID5 Stripe-unit size:256KB(Default)
HDD	SAS 73GB 15krpm * 5本

１．２　比較を行うMySQLと各Engine

MySQL4.1.21	MyISAM InnoDB Memory
MySQL5.0.67	MyISAM InnoDB Memory
MySQL5.1.28rc	MyISAM InnoDB Memory
MySQL5.1.23a-maria-alpha	Maria
MySQL6.0.5-alpha	Falcon

１．３　今回の検証に利用するサービス

ブログ(mobile)のログイン管理用とスキン情報のデータベースを利用

１．４　使用するDBの詳細情報
DB構成

Master：1台
Slave：3台
　ブログモバイルで2台
　その他サービスで1台

各データ件数

モバイル登録ユーザー及びログイン情報：役2,000,000件
総スキン数：155件
スキン適用ユーザ数：約1,100,000会員

ピークタイム時のSQL発行回数(Slave1台あたりの発行回数)

ブログモバイルのみ
　Select：58,000/分程度

ブログモバイル以外のサービス
　Select：20,000/分程度

以下は共通
　Insert：60/分程度
　Update：900/分程度
　Delete：10/分程度

１．５　使用するベンチマークツール

super-smack-1.3

ベンチマークで使用するSQLの内容

1. ログイン状態チェックSQL

　1件問い合わせ　※パラレルで同時5スレッド実行
2. ブログ情報取得SQL

　1件問い合わせ
　　1スレッドにつき3回SQL実行
3. ブログ表示時のスキン情報取得SQL

　1件問い合わせ
　　1スレッドにつき3回SQL実行
4. ユーザ別スキン情報取得SQL

　1件問い合わせ
5. 現在有効なスキン情報全件取得SQL

　複数件問い合わせ
6. スキン詳細情報取得SQL

　1件問い合わせ
7. ジャンル別のスキン情報取得SQL

　複数件問い合わせ
　1スレッドにつき4回SQL実行

２．使用するEngineの設定と調査

２．１　Engine別 My.cnf設定

MyISAM	key_buffer_size = 1G read_buffer_size = 8M read_rnd_buffer_size = 16M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1
InnoDB	innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 3G innodb_data_file_path = ibdata1:300M:autoextend innodb_file_per_table innodb_flush_log_at_trx_commit = 0 innodb_file_io_threads = 4 innodb_thread_concurrency = 16 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
Maria	maria_checkpoint_interval = 90 maria_log_purge_type = immediate maria_pagecache_buffer_size = 2G maria_pagecache_division_limit = 100 maria_repair_threads = 5 maria_sort_buffer_size = 16M
Memory	max_heap_table_size = 3G
Falcon	falcon_record_chill_threshold = 1024 falcon_record_memory_max = 1073741824

２．２　Engine関する調査と問題点について

1. Memory
• 4.1 以降のバージョンで一部のSQLでレスポンスが著しく低下した。
  　→調査したところ、INDEXのデフォルトが「HASH」になってしまうため、「ORDER BY」句を含むSQLのレスポンスが悪化していた。
  　　→改善として「ORDER BY」で利用するINDEXを全て「BTREE」に変更した。


2. Maria
• 「maria_block_size」を変更しテーブルを使用するとクラッシュ(mysql デーモンが再起動)してしまう。
• 基本構造がMyISAMと同じだが、トランザクション管理をするファイル「maria_log_control」、「maria_log.xxxxxxxx」が作成される
• ファイル構成は「tablename.MAD」がデータ、「tablename.MAI」がインデックスになる
• MyISAMとMariaの比較
• DataFile
★MyISAM

-rw-rw---- 1 mysql mysql 74M 9月 23 15:02 tablename.MYI
-rw-rw---- 1 mysql mysql 100M 9月 23 15:02 tablename.MYD
-rw-rw---- 1 mysql mysql 57M 9月 23 15:02 tablename.MYI
-rw-rw---- 1 mysql mysql 82M 9月 23 15:02 tablename.MYD

★Maria

-rw-rw---- 1 mysql mysql 69M 9月 23 15:10 tablename.MAI
-rw-rw---- 1 mysql mysql 112M 9月 23 15:10 tablename.MAD
-rw-rw---- 1 mysql mysql 50M 9月 23 15:10 tablename.MAI
-rw-rw---- 1 mysql mysql 94M 9月 23 15:10 tablename.MAD

若干データファイルが大きく、インデックスファイルが小さくなる


• my.cnf



MyISAM	Maria
Key_buffer_size	maria_pagecache_buffer_size
sort_buffer_size	maria_sort_buffer_size
myisam_repair_threads	maria_repair_threads
myisam_max_sort_file_size	maria_max_sort_file_size



• 「SHOW STATUS」の値



MyISAM	Maria
Key_blocks_not_flushed	Maria_pagecache_blocks_not_flushed
Key_blocks_unused	Maria_pagecache_blocks_unused
Key_blocks_used	Maria_pagecache_blocks_used
Key_read_requests	Maria_pagecache_read_requests
Key_reads	Maria_pagecache_reads
Key_write_requests	Maria_pagecache_write_requests
Key_writes	Maria_pagecache_writes



値の構成がMyISAMと変わらないので、「mMeasure」で表示させるのも簡単




3. Falcon
• 現在のバージョンでは使用できるレベルではなかった。
  「falcon_record_chill_threshold」、「falcon_record_memory_max」等設定変更を行わないとErrorが発生し途中で落ちてしまう。(mysql デーモンが再起動またはスレッドが切れてしまう)
  また、設定が終了しても、レプリケーション設定行い更新処理が実行され始めるとTableLockが発生してしまう。
• FalconはTableSpace管理になるので、必ずTableSpaceを作成する必要がある。

　例）
　CREATE TABLE name ADD DATAFILE 'name.fts' ENGINE FALCON;
　　データディレクトの配下にファイルが作成される。
　　-rw-rw---- 1 mysql mysql 347M 9月 22 22:29 name.fts

　ALTER TABLE tablename TABLESCPCE name ENGINE FALCON;
　　スキーマティレクトの配下には「.frm」しか作成されず、データは全て作成したTableSpaceに入る。

また、テンポラリ用のTableSpace「falcon_temporary.fts」、デフォルトTableSpace「falcon_user.fts」、おそらくバイナリログを保有していると思われる「falcon_master.fl1」、「falcon_master.fl2」が存在する。
• その他問題点
  「DROP TABLE」を実行してもTableSpaceが肥大化していくだけで、削除できない。
  SELECTで「WHERE」および「LIMIT」をつけない場合は結果が戻ってこない。

３．ベンチマーク結果

テストケース

1. ログイン状態チェックSQL
2. ブログ情報取得SQL
3. ブログ表示スキン情報取得SQL
4. ユーザ別スキン情報取得SQL
5. 現在有効なスキン情報全件取得SQL
6. スキン詳細情報取得SQL
7. ジャンル別のスキン情報取得SQL

３．１　1秒間のクエリ発行回数結果

1スレッド実行

	A	B	C	D	E	F	G
MyISAM(4.1.21)	623	504	685	604	309	773	296
InnoDB(4.1.21)	618	497	680	599	310	776	289
Memory(4.1.21)	686	501	682	615	320	800	305
MyISAM(5.0.67)	604	447	614	581	307	768	282
InnoDB(5.0.67)	637	464	595	581	308	771	282
Memory(5.0.67)	1158	470	630	597	330	826	294
MyISAM(5.1.28rc)	575	453	604	572	311	779	273
InnoDB(5.1.28rc)	622	459	593	572	306	767	270
Memory(5.1.28rc)	1179	462	610	593	326	816	295
Maria(5.1.23a-maria-alpha)	420	463	667	604	232	582	271
Falcon(6.0.5-alpha) ※1	---	---	---	---	---	---	---

5スレッド実行

	A	B	C	D	E	F	G
MyISAM(4.1.21)	458	599	747	754	383	959	365
InnoDB(4.1.21)	443	477	577	650	355	838	313
Memory(4.1.21)	665	560	709	770	396	991	378
MyISAM(5.0.67)	419	585	719	749	384	961	363
InnoDB(5.0.67)	507	535	674	738	379	948	360
Memory(5.0.67)	1251	559	648	772	401	1002	380
MyISAM(5.1.28rc)	411	567	705	753	386	966	361
InnoDB(5.1.28rc)	419	530	672	725	373	934	351
Memory(5.1.28rc)	1252	561	684	773	403	1007	381
Maria(5.1.23a-maria-alpha)	405	595	709	745	383	959	358
Falcon(6.0.5-alpha) ※1	---	---	---	---	---	---	---

10スレッド実行

	A	B	C	D	E	F	G
MyISAM(4.1.21)	361	576	727	756	385	964	368
InnoDB(4.1.21)	361	366	420	478	245	613	237
Memory(4.1.21)	521	560	712	772	398	997	379
MyISAM(5.0.67)	317	571	712	744	384	960	360
InnoDB(5.0.67)	336	454	559	616	318	795	305
Memory(5.0.67)	1255	562	686	776	401	1002	381
MyISAM(5.1.28rc)	304	562	681	751	388	970	346
InnoDB(5.1.28rc)	305	432	554	597	302	757	290
Memory(5.1.28rc)	1243	564	685	773	403	1008	381
Maria(5.1.23a-maria-alpha)	302	574	691	740	383	959	344
Falcon(6.0.5-alpha) ※1	---	---	---	---	---	---	---

100スレッド実行

	A	B	C	D	E	F	G
MyISAM(4.1.21)	235	390	694	769	392	982	371
InnoDB(4.1.21)	356	329	379	436	213	533	219
Memory(4.1.21)	267	516	706	778	378	947	379
MyISAM(5.0.67)	220	365	665	784	399	1000	361
InnoDB(5.0.67)	340	321	368	423	208	521	213
Memory(5.0.67)	1388	564	786	805	388	971	400
MyISAM(5.1.28rc)	209	349	654	788	405	1013	369
InnoDB(5.1.28rc)	338	320	390	457	209	524	215
Memory(5.1.28rc)	1406	563	780	810	387	969	406
Maria(5.1.23a-maria-alpha)	210	346	655	771	408	1020	354
Falcon(6.0.5-alpha) ※1	---	---	---	---	---	---	---

※1　Falcon自体安定しておらず、負荷をかけることができなかった。

３．２　スレッド増加ごとの各SQLグラフ

ログイン状態チェックSQL



ブログ情報取得SQL



ブログ表示時のスキン情報取得SQL



ユーザ別スキン情報取得SQL



現在有効なスキン情報全件取得SQL



スキン詳細情報取得SQL



ジャンル別のスキン情報取得SQL



※2　Falcon自体安定しておらず、負荷をかけることができなかった。

４．Engine別の評価

MyISAM

ユニークインデックス、プライマリキー検索は、スレッド数を上げるとファイルロックが頻発してしまうため、大幅にレスポンスが悪化してしまった。
ただ、それ以外の全件検索やレンジ検索については、ある程度のレスポンスを維持することが出来ていた。

InnoDB

全体的に悪いレスポンス結果になってしまった。
ただ、「ログイン状態チェックSQL」は、10スレッドと100スレッドでのレスポンスは大きく変わらなかった。
InnoDB独自の行ロックがうまく機能していると思われる。
他の検証でFileSystemの「ext3」と「InnoDB」の組み合わせに問題が見られるので、一概にInnoDBの性能が悪いとは言えない。
今後は、他のFileSystemでの検証を行う必要がある。

Memory

スレッド数を上げれば上げるほどより多くのSQLを返すことができたので、性能は今回検証した全てのEngineで一番よい結果になった。
ただ、INDEXの動作として「HASH」の方が良いのか「BTREE」の方が良いのかは検討する必要がある。
また、Memoryテーブルは可変長カラムを全て固定長に変換されるため、1レコードのバイト数が大きくなる。
　→場合によっては、より多くのメモリーを必要とするか、利用できない可能性が出てきてしまう。

Maria

まだ、alpha版なのでなんとも言えないが、レスポンスは全ての結果においてよかったといえる。
特に、全件検索、レンジ検索については、スレッド数を上げてもレスポンスは悪化しなかった。Indexのキャッシュ方式などがMyISAMとは違うのか、今後調査を行っていきたい。
MyISAMにかわる次世代のEngineとしては期待できる結果になった。

５．今後の課題

MySQLのバージョン別、Engine別の評価は出来たが、InnoDB等でレスポンスが悪化した件について、調べていかなくてはいけない。

今後は、FileSystem、Kernelの設定等を組み合わせることで、よりよいパフォーマンスが出せるか検証を行う。

また、新EngineのMariaについても、今後どのようにキャッシュを利用しているのか調査する。