MySQLサーバのパフォーマンスプロファイル
11868 ワード
『高性能MySQL(第3版)』第3章の読書ノートです.
サービスについてよくある質問は次のとおりです.サーバが最大のパフォーマンスを発揮するかどうかを確認する方法 実行が遅い文を見つけ、なぜ実行が遅いのか は、なぜユーザ側で間欠的な停止が発生し、 を殺すのか.
パフォーマンスプロファイリング(profiling)によりサーバのパフォーマンスを分析し、最も時間がかかる場所を特定することで、上記の問題の解決に役立ちます.
1.パフォーマンス最適化の概要
性能は一般に応答時間(Latency,遅延)と考えられるが,性能最適化は応答時間を減らすことである.
パフォーマンスを最適化するには、まずパフォーマンスのボトルネックがどこにあるかを知る必要があります.これはテストに使う必要があります.
2.性能テスト
2.1アプリケーションのパフォーマンステスト
各言語には専門的なテストプログラムがあり、PHPに対して、使用できるのは以下の通りです. xhprof:軽量でコンパクトで、生成環境に導入できます.ここを参考に. xdebug:検出範囲が大きく、オーバーヘッドが大きく、テスト環境に適しています.
2.2データベースのパフォーマンステスト
2.2.1サーバー負荷の分析
サーバ負荷を分析することで、最適化が必要な単一のクエリーを特定します.
MySQLのクエリーをログファイルにキャプチャ
MySQLでは、スロー・クエリー・ログは、最もコストがかかり、最も精度の高いクエリー時間を測定するツールです.運転時間がlong_を超えたquery_time値文はスロークエリログに記録されます.long_query_time単位は秒、デフォルト値は10で、すべてのクエリーをキャプチャするために0に設定できます.スロー・クエリー・ログは手動で開く必要があり、オンにするとパフォーマンスにわずかに影響します.スロー・クエリー・ログは、ファイルまたはデータベースに書き込むことができますが、データベースに書き込むとパフォーマンスに影響し、時間の粒度が秒レベルに劣化します.
MySQLにはもう一つのクエリーログがあります.汎用ログと呼ばれていますが、利用可能な情報が少なく、リソース消費が大きく、ほとんど使いません.
スロー・クエリー・ログに関するパラメータは次のとおりです.ここを参照してください. slow_query_log:遅いクエリーログをオンにするかどうか、1オン、0オフにします. log-slow-queries:旧バージョン(5.6以降)MySQLデータベーススロークエリーログストレージパス.デフォルトはデフォルトのファイルhost_です.name-slow.log slow-query-log-file:新しいバージョン(5.6以降)MySQLデータベースの遅いクエリーログストレージパス.デフォルトはデフォルトのファイルhost_です.name-slow.log long_query_time:設定したしきい値よりもクエリー時間が多い場合にログを記録する遅いクエリーしきい値. log_queries_not_using_indexes:インデックスを使用していないクエリーもスロークエリーログに記録されます(オプション). log_output:ログストレージ方式.log_output=’FILE’はログをファイルに格納することを示し、デフォルト値は‘FILE’である.log_output='TABLE'は、データベースにログを格納ことを意味し、mysqlにログ情報が書き込まれる.slow_logテーブルにあります.MySQLデータベースは同時に2種類のログストレージ方式をサポートし、構成時にカンマで区切ることができる.例えば:log_output=’FILE,TABLE’.ログをシステムのプライベート・ログ・テーブルに記録すると、ファイルに記録するよりもシステム・リソースがかかります.したがって、遅いクエリー・ログを有効にする必要があり、より高いシステム・パフォーマンスを得る必要がある場合は、ファイルに優先的に記録することをお勧めします.
クエリー・ログの分析
スロー・クエリー・ログの分析には、MySQLに付属のmysqldumpslowツールを使用します.遅いクエリー・ログで分析レポートを生成するには、pt-query-digestツールを使用します.
2.2.2単一クエリーの分析
最適化が必要な単一クエリーにナビゲートした後、最適化を開始します.
SHOW PROFILE(performance_schemaに置き換えられる)
もう廃棄したから,研究しないでください.
-使用前にhave_を通過プロファイルパラメータ現在のMySQLがプロファイルをサポートしているかどうかを確認します:–
-デフォルトでは無効です.サーバ変数を使用してセッション・レベルで有効にできます.-
Performace Schema
ここを参考に
Performace Schemaは、現在のMySQL(5.7以降)がメインプッシュするパフォーマンステストツールで、デフォルトでオンになっています.MySQLを初めてインストールすると、デフォルトでいくつかのデータベースがインストールされています.performace_schemaは、Performace Schemaを構成するために使用されます.
SHOW STATUS
SHOW STATUSコマンドは、いくつかのサーバレベルのグローバルカウンタまたはセッションレベルのカウンタを返します.アクティビティの頻度は表示できますが、消費時間は指定できません.
2.3間欠的な問題の診断
時々停止し、データベースの間欠的な遅いクエリーを適用する理由は、次のとおりです.アプリケーションは、インタフェースを介して動作の遅い外部サービスからデータを取得する . memcachedの重要なキャッシュ・エントリが期限切れになったため、MySQLに大量のリクエストが送信され、キャッシュ・エントリ が再生成されます. DNSクエリはたまにタイムアウト 相互反発ロック競合または内部削除クエリーキャッシュのアルゴリズム効率が低すぎて、MySQLのクエリーキャッシュによりサービスが一時停止 同時性がしきい値を超える場合、InnoDBの拡張性の制限により、クエリ計画の最適化に時間がかかる .
間欠的な問題を解決するには、一般的な方法とツールがあり、問題を特定してから操作することができます.
2.3.1単一クエリーの問題またはサーバーの問題
SHOW GLOBAL STATUS
SHOW STATUSまたはSHOW SESSION STATUSは現在のセッションレベルの情報を表示し、SHOW GLOBAL STATUSはサーバレベルの情報を表示します.完全な違いはSHOW GLOBAL STATUS vs SHOW STATUSを参考にすることができます.
SHOW GLOBAL STATUSコマンドは、より高い頻度でデータを取得します.問題が発生した場合、いくつかのカウンタ(Threads_running、Threads_connected、Questions、Queries)の「スパイク」と「くぼみ」で問題を特定できます.SHOW GLOBAL STATUSコマンドの使い方が簡単で、実行時に特別な権限を必要とせず(データベースに登録したユーザーが使用できる)、サーバへの影響が少ない.
次の例では、
次の例では、
SHOW PROCESSLIST
SHOW PROSCESSLISTの出力をひっきりなしにキャプチャし、大量のスレッドが異常な状態にあるか、異常な特徴があるかを観察することができます.たとえば、クエリがstatictics状態になることはめったにありません.
例として、コマンドの末尾にセミコロンをGで置換することによって結果を垂直に出力し、
コマンドラインに加えて、INFORMATION_を直接問い合わせることもできます.SCHEMAデータベースのPROSCESSLISTテーブル.
クエリー・ログの使用
スロー・クエリー・ログを開き、グローバル・レベルでlong_を設定する必要があります.query_timeを0にし、すべての接続をリセットして新しいグローバル設定を有効にします.
良いツールは問題の診断に役立ちます.そうしないと、数百GBのログファイルで問題を検索します.次の例の1行のコードは、MySQLが現在の時刻をログに書き込むパターンに基づいて、1秒あたりのクエリ数を統計します.
発見された問題を理解する
問題の診断を開始するには、SHOW STATUSとSHOW PROSSSLISTを使用することをお勧めします.この2つの方法はオーバーヘッドが低く、shellまたはクエリーを繰り返してデータを収集できます.遅いクエリー・ログの分析は困難です.
2.3.2診断データの取得
間欠的な問題を診断するには、できるだけ多くのデータを収集する必要があります.2つのツールが必要です.フリップフロップ、すなわち問題を区別する方法 診断データ収集ツール 診断トリガ
トリガは「誤報」と「検査漏れ」を避ける必要があります.
通常、フリップフロップしきい値は通常の2倍程度に調整されます.例えばThreads_runningが通常10の場合、トリガのしきい値は20に設定できます.Threads_の場合connected正常値が150の場合、しきい値は300であってもよい.
また、継続時間の設定も必要です.例えばThreads_runningは3秒連続で20を超えると異常とみなされる.「pt-stalk」ツールを使用できます.
収集するデータ型
診断トリガを特定すると、プロセス収集データを開始できます.システム状態、CPU使用率、ディスク使用率、空き容量、メモリ使用率、psコマンドの出力サンプリング、SHOW STATUS、SHOW INNODB STATUS、SHOW PROCESLISTなどのMySQLから得られる情報をできるだけ多くする必要があります.
Linuxで使用可能なサーバ内部診断ツールには、oprofile、strace(本番環境での使用にリスクがある)があります.クエリーを解析するにはtcpdumpを使用します.
待機理由はGDBスタック追跡で分析できます.トレース時にGDBを起動し、mysqldプロセスにアタッチし、すべてのスレッドのスタックを格納します.次に,スクリプトを用いて類似のスタック情報をまとめ,sort|uniq|sortソートを再利用する.
結果データの解釈
2.3.3ケース
2.4その他の分析ツール
2.4.1 USER_STATISTICSテーブル
INFORMATION_SCHEMAライブラリ内のテーブルは,各データベースの情報およびすべてのアクティビティの統計情報を格納しており,どのデータベースのどのテーブル,どのインデックスが最も頻繁に使用されているかを知ることができる.テーブルインデックスの情報.SHOW INDEX FROM schemaname.tablename; コマンドは、このテーブルから結果を取得します.
5.まとめ
問題を解決するには,まず問題をはっきりさせなければならない.すべてのクエリー・レコードをログに記録し、pt-query-digestツールを使用してシステム・レベルのプロファイリング・レポートを生成します.
サービスについてよくある質問は次のとおりです.
パフォーマンスプロファイリング(profiling)によりサーバのパフォーマンスを分析し、最も時間がかかる場所を特定することで、上記の問題の解決に役立ちます.
1.パフォーマンス最適化の概要
性能は一般に応答時間(Latency,遅延)と考えられるが,性能最適化は応答時間を減らすことである.
パフォーマンスを最適化するには、まずパフォーマンスのボトルネックがどこにあるかを知る必要があります.これはテストに使う必要があります.
2.性能テスト
2.1アプリケーションのパフォーマンステスト
各言語には専門的なテストプログラムがあり、PHPに対して、使用できるのは以下の通りです.
2.2データベースのパフォーマンステスト
2.2.1サーバー負荷の分析
サーバ負荷を分析することで、最適化が必要な単一のクエリーを特定します.
MySQLのクエリーをログファイルにキャプチャ
MySQLでは、スロー・クエリー・ログは、最もコストがかかり、最も精度の高いクエリー時間を測定するツールです.運転時間がlong_を超えたquery_time値文はスロークエリログに記録されます.long_query_time単位は秒、デフォルト値は10で、すべてのクエリーをキャプチャするために0に設定できます.スロー・クエリー・ログは手動で開く必要があり、オンにするとパフォーマンスにわずかに影響します.スロー・クエリー・ログは、ファイルまたはデータベースに書き込むことができますが、データベースに書き込むとパフォーマンスに影響し、時間の粒度が秒レベルに劣化します.
MySQLにはもう一つのクエリーログがあります.汎用ログと呼ばれていますが、利用可能な情報が少なく、リソース消費が大きく、ほとんど使いません.
スロー・クエリー・ログに関するパラメータは次のとおりです.ここを参照してください.
クエリー・ログの分析
スロー・クエリー・ログの分析には、MySQLに付属のmysqldumpslowツールを使用します.遅いクエリー・ログで分析レポートを生成するには、pt-query-digestツールを使用します.
2.2.2単一クエリーの分析
最適化が必要な単一クエリーにナビゲートした後、最適化を開始します.
SHOW PROFILE(performance_schemaに置き換えられる)
もう廃棄したから,研究しないでください.
-使用前にhave_を通過プロファイルパラメータ現在のMySQLがプロファイルをサポートしているかどうかを確認します:–
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------+
| Warning | 1287 | '@@have_profiling' is deprecated and will be removed in a future release. |
+---------+------+---------------------------------------------------------------------------+
1 row in set (0.00 sec)
-デフォルトでは無効です.サーバ変数を使用してセッション・レベルで有効にできます.-
mysql> SET profiling = 1;Performace Schema
ここを参考に
Performace Schemaは、現在のMySQL(5.7以降)がメインプッシュするパフォーマンステストツールで、デフォルトでオンになっています.MySQLを初めてインストールすると、デフォルトでいくつかのデータベースがインストールされています.performace_schemaは、Performace Schemaを構成するために使用されます.
SHOW STATUS
SHOW STATUSコマンドは、いくつかのサーバレベルのグローバルカウンタまたはセッションレベルのカウンタを返します.アクティビティの頻度は表示できますが、消費時間は指定できません.
mysql> show global status;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name | Value |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients | 275 |
| Aborted_connects | 80872 |
| Binlog_cache_disk_use | 0 |
...
| Threads_running | 1 |
| Uptime | 6661773 |
| Uptime_since_flush_status | 6661773 |
| validate_password_dictionary_file_last_parsed | 2018-01-31 15:35:09 |
| validate_password_dictionary_file_words_count | 0 |
+-----------------------------------------------+--------------------------------------------------+
355 rows in set (0.03 sec)2.3間欠的な問題の診断
時々停止し、データベースの間欠的な遅いクエリーを適用する理由は、次のとおりです.
間欠的な問題を解決するには、一般的な方法とツールがあり、問題を特定してから操作することができます.
2.3.1単一クエリーの問題またはサーバーの問題
SHOW GLOBAL STATUS
SHOW STATUSまたはSHOW SESSION STATUSは現在のセッションレベルの情報を表示し、SHOW GLOBAL STATUSはサーバレベルの情報を表示します.完全な違いはSHOW GLOBAL STATUS vs SHOW STATUSを参考にすることができます.
SHOW GLOBAL STATUSコマンドは、より高い頻度でデータを取得します.問題が発生した場合、いくつかのカウンタ(Threads_running、Threads_connected、Questions、Queries)の「スパイク」と「くぼみ」で問題を特定できます.SHOW GLOBAL STATUSコマンドの使い方が簡単で、実行時に特別な権限を必要とせず(データベースに登録したユーザーが使用できる)、サーバへの影響が少ない.
次の例では、
-i1オプションで1秒間に1回のデータをスナップし、awkに出力して1秒間のクエリー数、Threads_を計算し、出力します.connected、Threads_running: [root@VM_120_242_centos ~]# mysqladmin -uroot -p ext -i1 | awk '/Queries/{q=$4-qp;qp=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%d %d %d
", q, tc, $4}'
Enter password:
1554866 1 1
1 1 1
1 1 1次の例では、
-i1オプションで1秒に1回のデータをスナップし、-rオプションで差を示し、awkに出力して1秒当たりのクエリー数を計算し、出力します.# mysqladmin -uroot -p ext -i1 -r | awk '/Queries/{q=$4} /Threads_connected/{tc=$4} /Threads_running/{printf "%d %d %d
", q, tc, $4}'
Enter password:
1554856 1 1
1 0 0
1 0 0
1 0 0SHOW PROCESSLIST
SHOW PROSCESSLISTの出力をひっきりなしにキャプチャし、大量のスレッドが異常な状態にあるか、異常な特徴があるかを観察することができます.たとえば、クエリがstatictics状態になることはめったにありません.
例として、コマンドの末尾にセミコロンをGで置換することによって結果を垂直に出力し、
sort|uniq|sortコマンドによってカラム値の出現回数を計算します.[root@VM_120_242_centos ~]# mysql -uroot -p -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -rn
Enter password:
2 State:
1 State: startingコマンドラインに加えて、INFORMATION_を直接問い合わせることもできます.SCHEMAデータベースのPROSCESSLISTテーブル.
クエリー・ログの使用
スロー・クエリー・ログを開き、グローバル・レベルでlong_を設定する必要があります.query_timeを0にし、すべての接続をリセットして新しいグローバル設定を有効にします.
良いツールは問題の診断に役立ちます.そうしないと、数百GBのログファイルで問題を検索します.次の例の1行のコードは、MySQLが現在の時刻をログに書き込むパターンに基づいて、1秒あたりのクエリ数を統計します.
awk '/^# Time:/{print $3, $4, c; c=0}/^# User/{c++}' slow-query.log発見された問題を理解する
問題の診断を開始するには、SHOW STATUSとSHOW PROSSSLISTを使用することをお勧めします.この2つの方法はオーバーヘッドが低く、shellまたはクエリーを繰り返してデータを収集できます.遅いクエリー・ログの分析は困難です.
2.3.2診断データの取得
間欠的な問題を診断するには、できるだけ多くのデータを収集する必要があります.2つのツールが必要です.
トリガは「誤報」と「検査漏れ」を避ける必要があります.
通常、フリップフロップしきい値は通常の2倍程度に調整されます.例えばThreads_runningが通常10の場合、トリガのしきい値は20に設定できます.Threads_の場合connected正常値が150の場合、しきい値は300であってもよい.
また、継続時間の設定も必要です.例えばThreads_runningは3秒連続で20を超えると異常とみなされる.「pt-stalk」ツールを使用できます.
収集するデータ型
診断トリガを特定すると、プロセス収集データを開始できます.システム状態、CPU使用率、ディスク使用率、空き容量、メモリ使用率、psコマンドの出力サンプリング、SHOW STATUS、SHOW INNODB STATUS、SHOW PROCESLISTなどのMySQLから得られる情報をできるだけ多くする必要があります.
Linuxで使用可能なサーバ内部診断ツールには、oprofile、strace(本番環境での使用にリスクがある)があります.クエリーを解析するにはtcpdumpを使用します.
待機理由はGDBスタック追跡で分析できます.トレース時にGDBを起動し、mysqldプロセスにアタッチし、すべてのスレッドのスタックを格納します.次に,スクリプトを用いて類似のスタック情報をまとめ,sort|uniq|sortソートを再利用する.
結果データの解釈
2.3.3ケース
2.4その他の分析ツール
2.4.1 USER_STATISTICSテーブル
INFORMATION_SCHEMAライブラリ内のテーブルは,各データベースの情報およびすべてのアクティビティの統計情報を格納しており,どのデータベースのどのテーブル,どのインデックスが最も頻繁に使用されているかを知ることができる.テーブルインデックスの情報.SHOW INDEX FROM schemaname.tablename; コマンドは、このテーブルから結果を取得します.
mysql> SHOW INDEX FROM szhuizhong.users;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users | 0 | PRIMARY | 1 | UserID | A | 1460 | NULL | NULL | | BTREE | | |
| users | 0 | Account_index | 1 | Account | A | 1460 | NULL | NULL | | BTREE | | |
| users | 1 | CorpID | 1 | FromID | A | 2 | NULL | NULL | YES | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)5.まとめ
問題を解決するには,まず問題をはっきりさせなければならない.すべてのクエリー・レコードをログに記録し、pt-query-digestツールを使用してシステム・レベルのプロファイリング・レポートを生成します.