アジェンダ MySQLデータベースにおける Fusion-io 社 iodrive 使 用 時 の 優 位 性 について 事 例 紹 介 ~Too many connections 2012 Smart Style Co.,Ltd. 2 / 25

MySQLデータベースにおける Fusion-io 社 iodrive 使 用 時 の 優 位 性 について 2012/07/23@GMO yours 2012 Smart Style Co.,Ltd. 1 / 25

アジェンダ MySQLデータベースにおける Fusion-io 社 iodrive 使 用 時 の 優 位 性 について 事 例 紹 介 ~Too many connections 2012 Smart Style Co.,Ltd. 2 / 25

はじめに MySQLデータベースにおけるパフォーマンス 向 上 の 手 段 として ストレージ 媒 体 をハードディスクドライブ( 以 下 HDD)からFusionio 株 式 会 社 iodrive( 以 下 iodrive)に 変 更 する 選 択 肢 に 関 して 株 式 会 社 スマートスタイルがその 有 効 性 を 検 証 した 過 程 を 記 載 し たものです 本 書 に 記 載 された 測 定 記 録 は 同 様 の 構 成 を 取 った 場 合 でも 同 じ 結 果 を 保 証 するものではありません 本 書 の 執 筆 に 際 し 全 面 的 なご 協 力 をいただきましたGMOイン ターネット 株 式 会 社 様 Fusion-io 株 式 会 社 様 に この 場 を 借 りて 御 礼 申 し 上 げます 2012 Smart Style Co.,Ltd. 3 / 25

測 定 緒 元 CPU Intel Xeon CPU E5620 * 2 RAM 64GB OS Red Hat Enterprize Linux 5(2.6.18) MySQL 5.5.25-log Community Server (GPL) HDD SAS 146GB 6 台 (Hardware RAID1+0) iodrive iodrive Duo 320SLC 2 台 (Software RAID) 負 荷 テストツールpercona-tools tpcc-mysql 負 荷 計 測 の 直 前 にOSを 再 起 動 し バッファ キャッシュの 内 容 をクリアする データベーステーブル 数 9(tpcc-mysqlによる 作 成 ) 負 荷 クライアントからの 同 時 実 行 スレッド20(tpcc-mysqlによる 実 行 ) データベース 起 動 直 後 から 計 測 を 行 う tpcc-mysqlの 算 出 するtpmCの 値 ではなく 毎 分 のCom_queryの 増 分 を 計 測 し QPS(Query Per Second)の 推 移 を 記 録 する 測 定 は90 分 間 を3 回 行 い 値 は 記 録 された 数 値 の 平 均 を 採 用 する メモリ 関 連 のMySQLパラメータはHDD 使 用 時 iodrive 使 用 時 で 同 じものを 使 用 するが I/O 関 連 のパラメータに 関 してはそれぞれの 特 性 が 出 る 様 チューニングを 実 施 する 2012 Smart Style Co.,Ltd. 4 / 25

HDD + innodb_buffer_pool_size = 256M まずは 比 較 元 としてHDD 使 用 時 の 測 定 結 果 2012 Smart Style Co.,Ltd. 5 / 25

HDD + Innodb_buffer_pool_size=256M 5,000 4,500 QPS 4,000 3,500 3,000 2,500 0:09 0:12 0:15 0:18 0:21 0:24 0:27 0:30 0:33 0:36 0:39 0:42 0:45 0:48 0:51 0:54 0:57 1:00 1:03 1:06 1:09 1:12 1:15 1:18 1:21 1:24 1:27 1:30 90 分 の 平 均 4,200QPS 程 度 測 定 開 始 から10 分 程 度 で 急 激 に 処 理 量 が 増 加 し その 後 緩 やかに 低 下 する 2,000 1,500 1,000 500 0 0:00 0:03 0:06 2012 Smart Style Co.,Ltd. 6 / 25

メモリ 増 設 or iodrive 化 H/Wのスケールアップとして 1) メモリの 増 設 ( 今 回 は 割 り 当 て 量 増 加 で 代 替 ) 2) iodrive 化 のどちらがより 有 効 かを 比 較 します 2012 Smart Style Co.,Ltd. 7 / 25

HDD + Innodb_buffer_pool_size=40G 14,000 12,000 10,000 90 分 平 均 11,500QPS 8,000 6,000 0:09 0:12 0:15 0:18 0:21 0:24 0:27 0:30 0:33 0:36 0:39 0:42 0:45 0:48 0:51 0:54 0:57 1:00 1:03 1:06 1:09 1:12 1:15 1:18 1:21 メモリ 割 り 当 て 増 加 前 に 比 べて 実 に2.7 倍 4,000 2,000 0 0:00 0:03 0:06 2012 Smart Style Co.,Ltd. 8 / 25 1:24 1:27 1:30

iodrive + innodb_buffer_pool_size=256m 16,000 14,000 測 定 開 始 から2 分 間 で 処 理 量 がピークに 達 する 12,000 10,000 8,000 90 分 平 均 10,000QPS 程 度 6,000 4,000 HDDに 比 べて 2.4 倍 程 度 の 処 理 量 2,000 0:09 0:12 0:15 0:18 0:21 0:24 0:27 0:30 0:33 0:36 0:39 0:42 0:45 0:48 0:51 0:54 0:57 1:00 1:03 1:06 1:09 1:12 1:15 1:18 1:21 1:24 1:27 1:30 0 0:00 0:03 0:06 2012 Smart Style Co.,Ltd. 9 / 25

iodrive 化 よりもメモリ 増 設 が 有 効 なのか? 16,000 14,000 測 定 開 始 直 後 の 大 きなグラフの 違 い 12,000 10,000 8,000 平 均 だけを 見 るとそうも 考 えられる 6,000 0:09 0:12 0:15 0:18 0:21 0:24 0:27 0:30 0:33 0:36 0:39 0:42 0:45 0:48 0:51 0:54 0:57 1:00 1:03 1:06 1:09 1:12 1:15 1:18 1:21 1:24 1:27 1:30 4,000 2,000 0 0:00 0:03 0:06 2012 Smart Style Co.,Ltd. 10 / 25

起 動 直 後 の 優 位 性 メンテナンスやH/WダウンでMySQL 停 止 起 動 後 しばらくの 間 レスポンスが 悪 く アプリのタイムアウトや Too many connection 発 生 mysqld 起 動 直 後 はバッファが 空 なので バッファが 暖 まるまでは ストレージから 読 み 込 むしかない 起 動 直 後 はかなり 処 理 量 が 低 下 する 2012 Smart Style Co.,Ltd. 11 / 25

起 動 直 後 の 優 位 性 16,000 14,000 立 ち 上 がり 直 後 から 十 分 な 処 理 量 12,000 10,000 8,000 6,000 4,000 2,000 0 0:00 0:03 mysqld 再 起 動 直 後 の 処 理 量 に 悩 んでいる 環 境 にはとても 有 効 と 思 える 0:06 0:09 0:12 0:15 0:18 0:21 0:24 0:27 0:30 0:33 0:36 0:39 0:42 0:45 0:48 0:51 0:54 0:57 1:00 1:03 1:06 1:09 1:12 1:15 1:18 1:21 1:24 1:27 1:30 2012 Smart Style Co.,Ltd. 12 / 25

データベースサイズごとの 平 均 処 理 量 データベースが 肥 大 化 していくほど 処 理 量 は 低 下 していく HDDとioDriveで 処 理 量 の 低 下 具 合 に 差 があるのかどうかを 計 測 2012 Smart Style Co.,Ltd. 13 / 25

データベースサイズごとの 平 均 処 理 量 18,000 16,000 26%ダウン 14,000 17%ダウン 12,000 10,000 8,000 6,000 4,000 2,000 6%ダウン 10%ダウン 15%ダウン 22%ダウン 0 HDD HDDメモリ 追 加 iodrive HDD HDDメモリ 追 加 iodrive HDD HDDメモリ 追 加 iodrive DB 8GB DB 16GB DB 24GB 2012 Smart Style Co.,Ltd. 14 / 25

データベースサイズごとの 平 均 処 理 量 HDDを 使 っている 場 合 は DBサイズが 増 えるにつれ かなりの 速 度 で 処 理 量 が 減 少 していく iodriveを 使 用 しているケースは DBサイズの 増 加 と 比 較 して 処 理 量 減 少 が 圧 倒 的 に 穏 やか DBサイズ16GBのケースでメモリの 増 強 と 比 較 して ほぼ 同 程 度 DBサイズ24GBのケースでは メモリの 増 強 よりも 処 理 量 向 上 率 は 高 い 2012 Smart Style Co.,Ltd. 15 / 25

iodrive + innodb_buffer_pool_size=40g 60,000 50,000 iodrive + メモリ スレッドチューニング 後 40,000 30,000 20,000 10,000 0 0:00 0:03 iodrive + メモリ スレッドチューニングなし 0:06 0:09 0:12 0:15 0:18 0:21 0:24 0:27 0:30 0:33 0:36 0:39 0:42 0:45 0:48 チューニングなしだと 性 能 は 半 分 しか 出 ない 0:51 0:54 0:57 1:00 1:03 1:06 1:09 1:12 1:15 1:18 1:21 1:24 1:27 1:30 2012 Smart Style Co.,Ltd. 16 / 25

まとめ こんな 方 々に 特 にioDriveが 有 効 だと 思 います メンテナンス 明 けのレスポンス 低 下 物 理 的 にこれ 以 上 メモリが 増 設 できない データベースサイズが 大 きくなりすぎて 導 入 当 初 に 比 べて 性 能 劣 化 が 大 きい アプリケーションの 修 正 に 時 間 が 割 けない とにかく 速 さを 追 求 したい 2012 Smart Style Co.,Ltd. 17 / 25

事 例 紹 介 最 近 ソーシャル 関 連 のお 客 様 から お 問 い 合 わせのあった 事 例 として アクセス 数 の 増 加 に 伴 いWEBサーバを 増 設 MySQLへの 接 続 数 が 増 えた イベント 開 催 に 伴 いアクセスがバーストし MySQLへの 接 続 数 が 増 えた 結 果 Too many connectionsというエラーが 発 生 2012 Smart Style Co.,Ltd. 18 / 25

事 例 紹 介 Too many connectionsとは アプリケーションからMySQLに 接 続 できる 数 はmy.cnfで 設 定 されている(max_connections) max_connections 以 上 の 接 続 をしようとすると 出 るエ ラー max_connectionsの 値 を 増 やすことで エラーの 回 避 は 可 能 だが 全 体 的 なスループットは 低 下 する 傾 向 にある (アプリ 側 でリトライ 処 理 を 実 装 しなくて 良 いという メリットはある) 2012 Smart Style Co.,Ltd. 19 / 25

Too many connectionsのメカニズム threads max_connections time 2012 Smart Style Co.,Ltd. 20 / 25

max_connectionsを 増 やすと スレッドバッファ 分 メモリ 使 う 検 証 環 境 でstraceを 追 ってmmap 関 数 を 調 べたところ 1スレッド 生 成 あたり64MB 程 度 メモリを 確 保 している スレッドが100 本 生 成 されるなら 6GBにもなる スレッド 生 成 時 にオーバーヘッドがかかる 検 証 環 境 でstraceを 追 った 時 の 待 機 状 態 になるまでの 時 間 スレッドキャッシュなし.. 50ms~150ms 程 度 スレッドキャッシュあり.. 15ms~35ms 程 度 これらがもともとのクエリの 速 度 を 圧 迫 する 2012 Smart Style Co.,Ltd. 21 / 25

理 想 的 な 解 決 法 threads max_connections time 2012 Smart Style Co.,Ltd. 22 / 25

実 験 ローカルホストにLinux,Apache,MySQL,Perlの 環 境 を 作 成 abクライアントからapache 越 しにPerlスクリプトを 叩 く PerlスクリプトはMySQL10 多 重 アクセスをして 結 果 を 返 す そのターンアラウンドタイムを 計 測 ab ab ab abは3 多 重 で 起 動 Apache Perl Perl Perl MySQL Perlは10 多 重 で MySQLへアクセス 2012 Smart Style Co.,Ltd. 23 / 25

結 果 Too many connections 発 生 回 数 平 均 レスポンスタイム クエリチューニングなし max_connections=10 19 回 /30 回 6.2 秒 クエリチューニングなし max_connections=151 0 回 /30 回 17 秒 クエリチューニングあり max_connections=10 0 回 /30 回 0.5 秒 2012 Smart Style Co.,Ltd. 24 / 25

まとめ max_connectionsを 増 やして レスポンスが 向 上 するケースは メモリ,CPU,NIC,I/Oいずれも 余 裕 があり connection 数 だけがボトルネックになっている ケースのみ max_connectionsを 増 やすことで 全 体 のスループットが 悪 くなるケースもままある アプリ 側 でリトライ 処 理 を 実 装 しなくて 良 いという メリットはあるがスループット 低 下 のリスクも 高 い 全 体 的 なスループットを 保 ちつつ 状 況 を 改 善 する 為 には スキーマやSQLのチューニングが 必 須 2012 Smart Style Co.,Ltd. 25 / 25