はじめに
Oracleが開催するMySQLセミナーに参加してきました。
MySQL 8.0入門セミナー講演資料 (チューニング基礎編、SQLチューニング編)
今回参加したセミナーは入門編ですが、難易度の目安として、
「エンジニア1年目の自分が何とかついていけるレベル感」
でした。
DBパフォーマンス向上等、上を見ればキリが無い領域ですが、お話頂いた内容から要点を簡潔にお伝えします。
要点を3行で
MySQLのパフォーマンスを向上させる為に全体最適と個別最適の2軸で考える。
- サーバー設定によるスループットの向上 ≒ 全体最適
- クエリチューニングによるレスポンスタイムの短縮 ≒ 個別最適
内容に入る前に
チューニングの目的
どんなチューニングにもコストが必ずかかるため、ビジネスにとって重要かどうかの指標で考え、場合によっては他の可能性も検討する。
- ハードウェア交換の方が安くないだろうか？
- 改善後のパフォーマンス、スケーラビリティ、信頼性は本当に必要か？
- 行う予定の全てのチューニングが必要か？ 無駄な部分は無いだろうか？
覚えていた方が良さそうな用語
-
スループット
単位時間あたりの処理能力 -
レスポンスタイム
処理を実行してから結果が返ってくるまでの時間 -
キューイング
複数ユーザによる利用等、同時リクエストがある場合に発生
実際のレスポンスタイムは「処理実行時間」 + 「キューイング時間」による遅延で算出される
MySQLサーバーの設定
システム変数およびパラメータ変更によりスループットを上げる。
オプションファイル my.cnf/my.ini にて設定を行う。
また、恒久的な設定ではなく一時的な変更も下記コマンドで行えるがサーバーを再起動した場合にリセットされる為注意が必要。
SET [GLOBAL] @variable = @value ;
システム変数と併せて、ステータス変数の確認を確認も有効。ステータス変数では下記コマンドで特定クエリを調査出来たり、定期的な確認が可能である。
クエリ状態を確認
mysql>FLUSH STATUS; <クエリ実行>;
ステータスの差分を定期的に確認
mysqladmin -u <ユーザ名> -p ex -i 15 -r | grep -v '0'
パフォーマンス・スキーマ
パフォーマンススキーマからシステム変数やステータス変数を確認し設定値を適切な値に変更していく。
パフォーマンススキーマでは、MySQLサーバ内で起きているイベントごとの処理時間や処理データ量、各種メタデータを記録している。
システム変数確認
mysql> SELECT * FROM performancee_schema.global_variables;
mysql> SELECT * FROM performancee_schema.session_variables;
ステータス変数確認
mysql> SELECT * FROM performancee_schema.global_status;
mysql> SELECT * FROM performancee_schema.session_status;
パフォーマンススキーマから現状を把握する際、以下のように本番環境の動作をしっかりと計測出来ているか留意する。
- データサイズ
- データのリクエストのばらつき
- 利用ユーザ数
計測完了、現状を把握できたら設定変更を行っていく。
サーバコネクション&スレッド
max_connections (デフォルト設定は151)
- 許容可能なサーバへのコネクション数
- この設定が高すぎる場合はサーバーのメモリを圧迫する可能性があるため注意
thread_cache_size(デフォルト設定は9)
- コネクション切断後もスレッドをキャッシュする数
- 「max_connection / 3」が適切値とされる
- max_connection同様、上げすぎによるメモリ圧迫への注意が必要
コネクションスレッド毎のバッファ
sort_buffer_size(デフォルト設定は256KB)
- ソート処理に使われるメモリサイズ
- 設定サイズを超えるものはディスク処理になる
目指すべきスキーマーデザイン
- 非正規系と正規形のメリット/デメリットを把握し、設計段階で考慮しておくこと
- プレフィックスインデックスの利用
- 適切なデータ型の定義
- 小さなデータ型(tinyint,smallint)を利用
- varchar > char
- NOT NULLを用いる
- INT > NUMERIC/DECIMAL
クエリのチューニング
次の順序でチューニングを行いレスポンスタイムを短縮させる。
1. 問題と思われるクエリ(SQL)を特定
スロークエリログ
実行時間が指定した時間以上のクエリをログ出力出来る。
SHOW FULL PROCESSLIST
MySQLで上記「SHOW FULL PROCESSLIST」コマンドを用いることで現在実行中クエリの状態(実行時間、データ量、ステータス)が参照可。
パフォーマンススキーマ
先述だが、上記の「SHOW FULL PROCESSLIST」よりもより詳しい情報(バックグラウンドスレッド情報、接続方式)の確認が可能。
2. 1で上げたSQL実行時の状態(稼働統計等)を確認
SQLの実行計画(処理手順)はオプティマイザが作成する。下記記載のようなのプロセスをどのように実行しているのか、適切な実行計画が行われているかを確認する。
- インデックススキャン
- テーブルスキャン
- JOINの順番
- サブクエリ処理
EXPLAINコマンド
対象クエリの実行計画を確認し、結果から下記カラムを確認する。
mysql > explain select * from extable where id = 1;
select_type カラム
「DEPENDENT UNION」「DEPENDENT SUBQUERY」を避ける。
typeカラム
「index」「ALL」を避ける。
Extraカラム
「Using where」「Using filesort」「Using temporary」を可能な限り避け、「Using Index」が入っていることを目指す。
3. 実際のチューニングを行いパフォーマンス改善
Indexを活用出来る余地が無いか検討
※大量データへのアクセス(テーブル全体取得等)を行う場合、パフォーマンスが落ちる。
また、レコード更新時はIndexの多さがオーバーヘッドになるため注意。
複数テーブル結合(Join)クエリでは結合する順番(アクセスパス)を考慮
取り出すレコードや抽出レコード数が少ないテーブルから結合すること。
サブクエリについて
MySQL5.6以降を利用している場合はJOINの書き換え等チューニング必要なし。
オプティマイザ制御
- コスト調整(MySQL5.7以降)
- バッファサイズ変更
- その他設定変更
※全体補足 MySQLツールやMySQLバージョン等
特定のバージョン以降でしか設定出来ない項目もあるため、ごちゃごちゃと中身を弄るよりもバージョンを上げることを推奨。
その他、MySQL公式のGUIツール「MySQL Workbench」を利用することで
- 視覚的なクエリ分析 (ビジュアル EXPLAIN)
- オブジェクト定義確認
- 実行中SQLのキャプチャ
等が可能。上記以外にも様々なサーバー設定やクエリ分析機能が利用できる。
おわりに
知らなかった、あるいは知ってたけどよく分からなかった設定等ありましたでしょうか。
自分は今回のセミナーで知らないことが沢山ありましたが、サーバー全体の設定やパフォーマンス改善までの手順等、普段はクエリとひたすらにらめっこしている自分が新たな視野を持つことができた貴重な機会となりました。
この記事はセミナーの全体像やパフォーマンス改善手順の概要をお伝えしたい意図で全体的に抽象的な書き方になっています。設定等詳細が気になった方は是非、下の資料から具体的な方法を参照下さい。
少しでもお役に立てましたら幸いです。
参考資料
今回のセミナー資料含め、公式サイトからイベント資料のDLが可能です。
※DLする際はOracleプロファイルアカウントへの登録が必要