MySQL最適化ソリューション: スロークエリログを有効にする

序文

このソリューションは、小規模なプロジェクト、オンラインではないプロジェクト、または緊急時にのみ適しています。スローログクエリを有効にすると、データベースへの負荷が増加します。そのため、通常はバックグラウンドを使用してデータ操作時間をログファイルに書き込み、毎週定期的にログをクリアします。

MySQL 最適化プラン: 低速クエリログを有効にする(クエリ SQL の実行に 1 秒以上かかるなど)

スロークエリログを有効にする: MySQL は、指定された時間を超えるクエリステートメントを記録できます。パフォーマンスのボトルネックを特定して分析することで、データベースシステムのパフォーマンスをより最適化できます。

パラメータの説明:

slow_query_log スロークエリの有効化ステータス。ON で有効化、OFF で無効化

slow_query_log_file スロークエリログが保存される場所（このディレクトリには、MySQL 実行アカウントの書き込み権限が必要で、通常は MySQL データストレージディレクトリに設定されます）

long_query_time クエリが記録されるまでに何秒かかりますか?

重要なポイント: スローログのバージョンは高くする必要があり、それより低いバージョンではサポートされません。このバージョンは 5.7.20 です。

SELECT VERSION();クエリバージョン番号

このバージョンでは、スローログがデフォルトで有効になっています。

mysql> データベースを表示します。

mysql> use test; //データベースを指定しますmysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| 変数名 | 値 |
+-----------------+-----------+
| slow_query_log | オン |
+-----------------+-----------+
| slow_query_log_file | YH-20161209QIZC-slow.log |
+-----------------+-----------+
mysql> 'long_query_time' のような変数を表示します。
+-----------------+-----------+
| 変数名 | 値 |
+-----------------+-----------+
| 長いクエリ時間 | 10.000000 |
+-----------------+-----------+

//デフォルトでは、クエリは10秒を超えた場合にのみ記録されます

スロークエリログの設定

方法 1: グローバル変数の設定 (この方法はデータベースを再起動すると失敗し、再構成する必要があります)

slow_query_logグローバル変数を「ON」状態に設定する

mysql> グローバル slow_query_log を 'ON' に設定します。

スロークエリログを保存する場所を設定する

mysql> グローバル slow_query_log_file を '/usr/local/mysql/data/slow.log' に設定します。 //linux
mysql> グローバル slow_query_log_file を 'D:\\mysq\data\slow.log' に設定します。 //windows

1秒を超えるとクエリがログに記録されるように設定します（コマンドが時々機能しない場合は、オフにしてから再度オンにすることができます）

mysql> グローバル long_query_time を 1 に設定します。

方法2: 構成ファイルの設定（サーバーの再起動は影響しません）

設定ファイルmy.cnfを変更し、[mysqld]の下に以下を追加します。

[mysqld]
slow_query_log = オン
slow_query_log_file = /usr/local/mysql/data/slow.log //linux
長いクエリ時間 = 1

3. MySQLサービスを再起動する

サービスmysqldを再起動

テスト

1. 低速クエリSQL文を実行する

mysql> sleep(2) を選択します。

2. スロークエリログが生成されているかどうかを確認する

ここではSQLとクエリ時間を確認できます

ログが存在する場合、MySQL スロークエリ設定が正常に有効化されています。

付録: ログ解析ツール mysqldumpslow

本番環境では、手動でログを分析し、SQLを見つけて分析したい場合、それは明らかに手作業です。MySQLはログ分析ツールmysqldumpslowを提供しています。

mysqldumpslow のヘルプ情報を表示します。

[root@DB-Server ~]# mysqldumpslow --help

使用方法: mysqldumpslow [ OPTS... ] [ LOGS... ]

 

MySQLのスロークエリログを解析して要約します。オプションは次のとおりです。

 

  --verbose 詳細

  --debug デバッグ

  --help このテキストを標準出力に書き込む

 

  -v 詳細

  -d デバッグ

  -s ORDER 何を基準に並べ替えるか (al、at、ar、c、l、r、t)、デフォルトは 'at'

                al: 平均ロック時間

                ar: 送信された行の平均数

                at: 平均クエリ時間

                 c: カウント

                 l: ロック時間

                 r: 送信された行数

                 t: クエリ時間  

  -r ソート順を逆にする（最初ではなく最後が最大）

  -t NUM 上位n個のクエリのみを表示

  -a すべての数値をNに、文字列を'S'に抽象化しない

  -n NUM 名前に少なくとも n 桁の数字が含まれる抽象番号

  -g PATTERN grep: この文字列を含む文のみ考慮する

  -h HOSTNAME *-slow.log ファイル名の db サーバーのホスト名 (ワイルドカードも使用可能)、

               デフォルトは「*」で、すべてに一致します

  -i NAME サーバーインスタンス名（mysql.server 起動スクリプトを使用している場合）

  -l 合計時間からロック時間を減算しない

-s はソート方法を示します。

c: 訪問回数
l: ロック時間
r: レコードを返す
t: クエリ時間
al: 平均ロック時間
ar: 返されたレコードの平均数
at: 平均クエリ時間

-t は上位 n を意味し、返されるレコードの数を意味します。

-g に続いて正規表現のマッチングパターンが続きます (大文字と小文字は区別されません)。

例えば

最も多くのレコードを返す上位 10 個の SQL ステートメントを取得します。

mysqldumpslow -sr -t 10 /database/mysql/mysql06_slow.log

最もアクセス数の多いSQLトップ10を取得する

mysqldumpslow -sc -t 10 /database/mysql/mysql06_slow.log

時間順に並べられた左結合を含む最初の 10 個のクエリステートメントを取得します。

mysqldumpslow -st -t 10 -g "左結合" /database/mysql/mysql06_slow.log

これらのコマンドを | などと組み合わせて使用することもお勧めします。そうしないと、画面が更新される可能性があります。

mysqldumpslow -sr -t 20 /mysqldata/mysql/mysql06-slow.log | 詳細

要約する

これで、スロークエリログを有効にするための MySQL 最適化ソリューションに関するこの記事は終了です。MySQL スロークエリログを有効にすることに関する関連コンテンツの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: