残暑お見舞い申し上げます。立秋も過ぎ、忍び寄る秋の気配から現実逃避すべく夏アリ〼的なところへ行って来ました。 まぁ～ちょっとした夏フェス的なイベントに参加した訳ですが、夏の雰囲気を味わうために、ちょっぴりウケ狙いで浴衣を持って行ってみました。

　以前から浴衣は持ってるのですが、今まで一回位しか着た記憶がなく、 着るような気温とシチュエーション(situation)に恵まれないままタンスの肥やしと化しており、 しかも最近ポッコリお腹が目立ってきてムダに貫禄も出てきたおかげで浴衣のような和装が似合う体型とお年頃になってきました…（泣
そんなこんなで一念発起（大袈裟な…）して必要な小物も買い揃え、浴衣を着ることにしました。

　浴衣の購入と一緒に下駄も購入したのですが、結構かさばるので持って行かず、現地で購入出来るかを探してみました。 すると宿泊先の近くに下駄屋さんがあり、素材やパーツ(parts)を選んでオリジナル(original)な下駄も作ってもらえるようなので、自分へのお土産としてonly oneな下駄を発注させて頂きました。

　浴衣や帯の色味に合うように下駄のデザイン(design)や素材、鼻緒の色などを組み合わせ、店主と一緒にイイ塩梅にコーディネート(coordinate)して会心の一足が5分程で出来上がりました。（日本円換算で1,700円くらいでした） 日本文化の浴衣を着て異国の夏フェスに参加して今年の良き良き夏の思い出になりました。（鼻緒で擦れた足の痛みもイイ思い出です）

　ってなことで最近、開発室ブログ(blog)の冒頭はよもやま話をするのがセオリー(theory)のようなので自分も例外に漏れず一筆したためてみました。

はじめに

閑話休題

　Webアプリケーション(application)の作成を始めて3年（この会社に来てから）ですが、 今までデータベース(database)のチューニング(tuning)とは無縁でした。 がしかし最近、新たなプロダクト(product)を担当することになり、DBのチューニングの検討を余儀なくさせることになりました。

Hello tuning world

　新プロダクトの機能試作をほぼ終えて、システム(system)の検証として1日当たり9万件程のデータを毎日蓄積しています。 データ量が100万件を超過し始めた頃から、ページネーション(pagination)による画面遷移がやたら遅くなってきました。

　原因を探るべくGoogle先生に聞いてみましたが、ページネーションにありがちな

とのことでした。 最近のフレームワーク(framework)ですらOFFSET&LIMITを想定したページネーション制御が多く、本プロダクトで使用しているフレームワークも例外ではありませんでした。

　そこで対応策をO’reilly先生やGoogle先生を使って探っていたらプライマリキー(primary key)として定義しているID値を使ってOFFISET制御を紹介している記事を見つけました。 DBのテーブルを定義するときオートインクリメント(auto increment)でユニークな連番をIDとするカラム(column)を定義することがありますが WHERE文にID値とLIMIT値を使いその計算結果で大小比較やBETWEEN句で範囲指定する記事を見かけましたが、 データを削除するとIDが連続せず歯抜けとなり、当該プロダクトでは有効な方法ではありませんでした。 （歯抜けでも実装方法はあるようですが実装コスト(cost)が高いので見送りました。）

実践ハイパフォーマンスMySQL 第3版

posted with ヨメレバ

Baron Schwartz,Peter Zaitsev,Vadim Tkachenko オライリージャパン 2013-11-25

Amazon

チューニングその１

　そこで色々探っているうちに、パーティション(partition)の設定を試すことにしました。

　これはDB側の設定だけなのでコード(source code) には一切、変更が発生しません。早速、DBに1日毎のパーティション設定を行うと、ナント90%以上の性能向上となりました!! なお、実際の設定についてはこちらを参考にさせて頂きました。

運用環境
DB:MariaDB 10.1.22(x84_64)
MEM:1GB
OS:CentOS7

評価時点のレコード数：1,452,969(ページネーション10件/頁)(単位:秒)

パーテーション   無し   有り   差分    削減率
トップページ     0.8294 0.8368 -0.0074  -1%
次ページ         0.5847 0.8357 -0.2510 -43%
次ページ         0.6228 0.8345 -0.2117 -34%
最終ページ       3.6627 3.9204 -0.2577  -7%
前ページ         2.8252 3.3458 -0.5206 -18%
カレント日による  1.3345 0.0769  1.2576  94%
絞込みON
次ページ         1.8399 0.0757  1.7642  96%
次ページ         1.2776 0.1260  1.1516  90%
最終ページ       1.2597 0.1168  1.1429  91%
前ページ         1.9348 0.1175  1.8173  94%

パーテーション無し

DEBUG - 2018-08-06 11:28:49 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:28:49 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:28:50 --> Total execution time: 0.8294
DEBUG - 2018-08-06 11:28:54 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:28:54 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:28:55 --> Total execution time: 0.5847
DEBUG - 2018-08-06 11:28:57 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:28:57 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:28:58 --> Total execution time: 0.6228
DEBUG - 2018-08-06 11:29:01 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:29:01 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:29:04 --> Total execution time: 3.6627
DEBUG - 2018-08-06 11:29:12 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:29:12 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:29:14 --> Total execution time: 2.8252
DEBUG - 2018-08-06 11:29:24 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:29:24 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:29:25 --> Total execution time: 1.3345
DEBUG - 2018-08-06 11:29:30 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:29:30 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:29:32 --> Total execution time: 1.8399
DEBUG - 2018-08-06 11:29:34 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:29:34 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:29:35 --> Total execution time: 1.2776
DEBUG - 2018-08-06 11:29:39 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:29:39 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:29:40 --> Total execution time: 1.2597
DEBUG - 2018-08-06 11:29:42 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:29:42 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:29:44 --> Total execution time: 1.9348

パーテーション有り

DEBUG - 2018-08-06 11:11:40 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:11:40 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:11:41 --> Total execution time: 0.8368
DEBUG - 2018-08-06 11:13:06 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:13:06 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:13:07 --> Total execution time: 0.8357
DEBUG - 2018-08-06 11:13:11 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:13:11 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:13:12 --> Total execution time: 0.8345
DEBUG - 2018-08-06 11:13:41 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:13:41 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:13:45 --> Total execution time: 3.9204
DEBUG - 2018-08-06 11:13:50 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:13:50 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:13:53 --> Total execution time: 3.3458
DEBUG - 2018-08-06 11:14:06 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:14:06 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:14:06 --> Total execution time: 0.0769
DEBUG - 2018-08-06 11:14:15 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:14:15 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:14:16 --> Total execution time: 0.0757
DEBUG - 2018-08-06 11:14:19 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:14:19 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:14:19 --> Total execution time: 0.1260
DEBUG - 2018-08-06 11:14:23 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:14:23 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:14:23 --> Total execution time: 0.1168
DEBUG - 2018-08-06 11:14:27 --> UTF-8 Support Enabled
DEBUG - 2018-08-06 11:14:27 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-06 11:14:27 --> Total execution time: 0.1175

チューニングその２

　上記の表をみてお分かりのように日付による絞り込み後には有効ですが、絞り込み前の全件表示では有効ではなく少々残念な結果となりました…。 また、運用中にパーティションを追加することが出来ず、あらかじめ将来を見越してパーティションを設定する必要があるようです。 パーティション定義については仕方が無いので数年先までの定義で乗り切るとして、 絞り込み前の全件表示の問題を解決すべく、色々と調べていくとSQLを工夫することで性能向上となりました。

改善前

SELECT * FROM `hoge` LIMIT 100000 10 ORDER BY `id` DESC;

改善後

id配列 = SELECT `id` FROM hoge LIMIT 100000, 10 ORDER BY `id` DESC;
SELECT * FROM `hoge` WHERE `id` IN(id配列) ORDER BY `id` DESC;

　カラムidはプライマリキーでインデックス(index)生成済です。

補足：SELETC文のカラム指定で*とせずインデックス化しているカラム（この場合id）とした方がインデックスサーチ(index search)となり早いようです。

例1

EXPLAIN SELECT * FROM hoge;

id select_type table type possible_keys key  key_len ref  rows    Extra
1  SIMPLE      hoge  ALL  NULL          NULL NULL    NULL 1719332 NULL

typeがALL(テーブルフルスキャン)となり、検索速度が遅い

例2

EXPLAIN SELECT id FROM hoge;（カラム`id`はインデックス名`fuga`で生成済）

id select_type table type  possible_keys key  key_len ref  rows    Extra
1 SIMPLE       hoge  index NULL          fuga 7       NULL 1719332 Using index

typeがindex(インデックスサーチ)となり、検索速度が早い

効果測定

　上記を踏まえ、試作した結果を下記に示します。

評価時点のレコード数：1,719,332(単位:秒)

クエリ         シングル サブ   差分   削減率
ページジャンプ 2.8214   2.0625 0.7589 27%
最終ページ     6.1009   2.4526 3.6483 60%
前ページ       5.2847   2.2243 3.0604 58%

シングルクエリ

DEBUG - 2018-08-09 09:57:58 --> UTF-8 Support Enabled
DEBUG - 2018-08-09 09:57:58 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-09 09:57:59 --> SQL=SELECT * FROM `hoge` ORDER BY `id` DESC  LIMIT 10
DEBUG - 2018-08-09 09:58:01 --> Total execution time: 2.8214
DEBUG - 2018-08-09 09:58:07 --> UTF-8 Support Enabled
DEBUG - 2018-08-09 09:58:07 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-09 09:58:11 --> SQL=SELECT * FROM `hoge` ORDER BY `id` DESC  LIMIT 1721960, 10
DEBUG - 2018-08-09 09:58:13 --> Total execution time: 6.1009
DEBUG - 2018-08-09 09:58:24 --> UTF-8 Support Enabled
DEBUG - 2018-08-09 09:58:24 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-09 09:58:28 --> SQL=SELECT * FROM `hoge` ORDER BY `id` DESC  LIMIT 1721950, 10
DEBUG - 2018-08-09 09:58:30 --> Total execution time: 5.2847

サブクエリ

DEBUG - 2018-08-09 10:15:26 --> UTF-8 Support Enabled
DEBUG - 2018-08-09 10:15:26 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-09 10:15:27 --> SQL1=SELECT `id`
FROM `hoge`
ORDER BY `id` DESC
 LIMIT 10
DEBUG - 2018-08-09 10:15:27 --> SQL2=SELECT *
FROM `hoge`
WHERE `id` IN('1812134', '1812133', '1812136', '1812135', '1812142', '1812141', '1812140', '1812139', '1812138', '1812137')
ORDER BY `id` DESC
DEBUG - 2018-08-09 10:15:28 --> Total execution time: 2.0625
DEBUG - 2018-08-09 10:16:10 --> UTF-8 Support Enabled
DEBUG - 2018-08-09 10:16:10 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-09 10:16:11 --> SQL1=SELECT `id`
FROM `hoge`
ORDER BY `id` DESC
 LIMIT 1721960, 10
DEBUG - 2018-08-09 10:16:11 --> SQL2=SELECT *
FROM `hoge`
WHERE `id` IN('38', '37', '36', '35', '34', '33', '32', '31', '30', '29')
ORDER BY `id` DESC
DEBUG - 2018-08-09 10:16:13 --> Total execution time: 2.4526
DEBUG - 2018-08-09 10:17:02 --> UTF-8 Support Enabled
DEBUG - 2018-08-09 10:17:02 --> Global POST, GET and COOKIE data sanitized
DEBUG - 2018-08-09 10:17:03 --> SQL1=SELECT `id`
FROM `hoge`
ORDER BY `id` DESC
 LIMIT 1721950, 10
DEBUG - 2018-08-09 10:17:03 --> SQL2=SELECT *
FROM `hoge`
WHERE `id` IN('48', '47', '46', '45', '44', '43', '42', '41', '40', '39')
ORDER BY `id` DESC
DEBUG - 2018-08-09 10:17:05 --> Total execution time: 2.2243

ユーザビリティの考慮

　これで一応の解決策を見出すことが出来ましたが、 ユーザビリティ(usability)の観点から初期表示を全件表示からカレント(current)日だけに限定しました。 これは初期表示において常に全データを読み込む必要性がないと判断し、表示範囲をカレント日に限定しました。 これにより初期表示における表示時間の短縮となり利便性の向上を図れたと思います。

おわりに

　DBのチューニング関して小職はまだまだ初心者でもっといい方法があると思います。 以前、弊社に在籍したスタッフ(staff)がDBに明るく、コードの変更箇所は1～2行でしたが、かなりマニアック(maniac)な箇所でそのチューニングによって格段に早くなったそうです。 やはりDBのチューニングって奥が深いと思い、今回の施策についても更に効率の良い方法を模索中です。