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 をよろしくお願いいたします。