connector_20190202.pdf

第26回 中国地方DB勉強会 in 岡山 （2019.2.2）LT予定

Transcript

1. MySQL Connector/Jでパラメータチューニング （第26回 中国地⽅DB勉強会 in 岡⼭ 2019.02.02） hmatsu47（松久 裕保）
2. 今回のネタ 本家 Oracle の MySQL 接続⽤ドライバのうち、Java ⽤の Connector/J にはパラメータ（プロパティ）がある https://dev.mysql.com/doc/connector-j/8.0/en/connector- j-reference-configuration-properties.html これを調整すると性能がどの程度変わる︖ 結果を先にいうと︓結構変わる 実は MySQL Casual Advent Calendar 2017 ネタの再検証版 https://qiita.com/advent-calendar/2017/mysql-casual （10・17・21 ⽇⽬） 前回は Windows。今回は Linux で試した
3. 検証環境 on AWS（1/2） クライアント MySQL Connector/J 8.0.13 Java 8u191 EC2 c5.large 2vCPU / 4GiB Mem サーバと同⼀ AZ 内 Amazon Linux 2
4. 検証環境 on AWS（2/2） サーバ MySQL Community Server 5.7.24 バッファプール 4G / ログ 1G EC2 r4.large 2vCPU / 15.25GiB Mem CentOS 7 ※Connector/J 8.0.14 / Server 5.7.25 でもほぼ同じ結果
5. 検証１︓ rewriteBatchedStatements （1/2） INSERT 時 addBatch() → executeBatch() するとバッチ処理が 可能 MySQL Connector/J で rewriteBatchedStatements=true を指定 すると、 addBatch() した複数の INSERT が executeBatch() 時 に VALUES() のカンマ区切りで⾃動連結される /* rewriteBatchedStatements無指定 INSERT INTO `table` (`column1`,…) VALUES(…); INSERT INTO `table` (`column1`,…) VALUES(…); INSERT INTO `table` (`column1`,…) VALUES(…); /* rewriteBatchedStatements=true INSERT INTO `table` (`column1`,…) VALUES(…),(…),(…);
6. 検証１︓ rewriteBatchedStatements （2/2） 1 スレッド / 全 20 万⾏ / INSERT 100 ⾏単位 / COMMIT 1,000 ⾏ 単位で以下 3 パターンを⽐較 通常の INSERT バッチ INSERT （ rewriteBatchedStatements 無指定） バッチ INSERT （ rewriteBatchedStatements=true ）
7. 検証１︓結果 検証パターン 所要時間 (ms) ⾮バッチ 86,782 バッチ・ rewriteBatchedStatements 無指定 46,378 バッチ・ rewriteBatchedStatements=true 4,019 バッチ化だけでも 2 倍近く⾼速化 rewriteBatchedStatements=true で 20 倍以上⾼速化 ちょっと副作⽤も（後述︓まとめにて）
8. 検証２︓制御⽤のクエリ／コマンドを減らす MySQL Connector/J で DB 接続するときには SET ・ SELECT @@ など制御⽤のクエリ／コマンドが多数発⾏される 3 つのプロパティを変えることでそれらを減らし、どの程度の 変化があるかを検証（太字︓デフォルト） alwaysSendSetIsolation トランザクション分離レベルを 常に送信する（ true ）／しない（ false ） elideSetAutoCommits サーバ側の状態と違う時にだけ SET autocommit=n を送る（ true ）／常に送る（ false ） useLocalSessionState オートコミット・トランザクショ ン分離レベルをサーバに問い合わせずに判断する （ true ）／しない（ false ）
9. 検証２︓結果 ※バッチ 2 スレッド / ⾮バッチ 4 スレッドで計 120 万⾏ INSERT 検証パターン 所要時間合計 (ms) 3 つのパラメータ︓デフォルト 491,821 3 つのパラメータ︓⾮デフォルト 286,623 70% 程度⾼速化した こちらも副作⽤の可能性あり（クラスタのフェイルオーバー とか）
10. 検証３︓Prepared Statement キャッシュ（1/2） MySQL では Prepared Statement をクライアント側で処理す るケースがよくある クライアント側で Prepared Statement をキャッシュする設定 がある（太字︓デフォルト） cachePrepStmts Prepared Statement のキャッシュを使う （ true ）／使わない（ false ） prepStmtCacheSize キャッシュする Prepared Statement の最⼤数（ 25 ） prepStmtCacheSqlLimit キャッシュする Prepared Statement の最⼤⽂字数（ 256 ）
11. 検証３︓Prepared Statement キャッシュ（2/2） テスト条件 8 スレッド 1 スレッド あたり 100,000 ⾏ 10,000 ⾏単位で COMMIT 70 種類のプリペアードステートメントを発⾏ これを以下の 3 パターンで⽐較 キャッシュ無効 キャッシュ有効 / 最⼤ 50 種類（＜ 70）× 2,048 ⽂字 キャッシュ有効 / 最⼤ 100 種類（＞ 70）× 2,048 ⽂字
12. 検証３︓結果 検証パターン 所要時間合計 (ms) キャッシュ無効 515,922 キャッシュ有効 / 最⼤ 50 種類× 2,048 ⽂字 510,388 キャッシュ有効 / 最⼤ 100 種類× 2,048 ⽂字 516,780 ほぼ誤差の範囲で効果が⾒られない これとは別に、サーバ上で実⾏した場合は、最⼤ 100 種類 （キャッシュ上限＞実際のプリペアードステートメント数） のときに 20% ⾼速化した サービスを最⼩構成（仮想マシン 1 台 に Web と DB）で ローンチする場合に限って有効︖
13. まとめ addBatch() を使うときは rewriteBatchedStatements=true を忘 れずに その他、設定すると効果のあるパラメータ（プロパティ）が いくつかある 実際に試してみて判断したほうが良さそう 副作⽤もあるので気を付ける rewriteBatchedStatements=true は INSERT .. ON DUPLICATE KEY UPDATE のときに注意（マニュアル参照） サーバをクラスタ化しているとき、フェイルオーバーが ⾛ると Connector 側で保持している情報とサーバ側に不 整合が⽣じる可能性も…
14. 参考（MySQL Advent Calendar 2017 の記事） ※検証コードは検証１・３の記事で公開（MySQL Connector/J の バージョンのみ 8.0.13 / 8.0.14 に変更） 検証１ https://qiita.com/hmatsu47/items/0a3b48c86abdcd2cc884 検証２ https://qiita.com/hmatsu47/items/90bbe12a3082ca31201 7 検証３ https://qiita.com/hmatsu47/items/d3ce39577fb383ec21d2
15. ありがとうございました