MySQL の重要なパフォーマンス インデックスの計算と最適化方法の概要

MySQL の重要なパフォーマンス インデックスの計算と最適化方法の概要

1 QPS 計算 (1 秒あたりのクエリ数)

MyISAMエンジンベースのDBの場合

MySQL> 'questions' のようなグローバル ステータスを表示します。
+---------------+------------+
| 変数名 | 値 |
+---------------+------------+
| 質問 | 2009191409 |
+---------------+------------+
セット内の 1 行 (0.00 秒)

mysql> 'uptime' のようなグローバル ステータスを表示します。
+---------------+--------+
| 変数名 | 値 |
+---------------+--------+
| 稼働時間 | 388402 |
+---------------+--------+
セット内の 1 行 (0.00 秒)

QPS=questions/uptime=5172、MySQL が起動してからの平均 QPS。一定期間内の QPS を計算する場合は、ピーク期間中の間隔時間 t2-t1 を取得し、t2 と t1 でそれぞれ q 値を計算します。QPS=(q2-q1)/(t2-t1)

InnnoDBエンジンに基づくDBの場合

mysql> 'com_update' のようなグローバル ステータスを表示します。
+---------------+----------+
| 変数名 | 値 |
+---------------+----------+
| Com_update | 87094306 |
+---------------+----------+
セット内の 1 行 (0.00 秒)

mysql> 'com_select' のようなグローバル ステータスを表示します。
+---------------+------------+
| 変数名 | 値 |
+---------------+------------+
| Com_select | 1108143397 |
+---------------+------------+
セット内の 1 行 (0.00 秒)


mysql> 'com_delete' のようなグローバル ステータスを表示します。
+---------------+--------+
| 変数名 | 値 |
+---------------+--------+
| Com_delete | 379058 |
+---------------+--------+
セット内の 1 行 (0.00 秒)

mysql> 'uptime' のようなグローバル ステータスを表示します。

+---------------+--------+
| 変数名 | 値 |
+---------------+--------+
| 稼働時間 | 388816 |
+---------------+--------+
セット内の 1 行 (0.00 秒)

QPS=(com_update+com_insert+com_delete+com_select)/uptime=3076。一定期間内のQPS照会方法は上記と同じです。

2 TPS計算(1秒あたりのトランザクション数)

mysql> 'com_commit' のようなグローバル ステータスを表示します。

+---------------+---------+
| 変数名 | 値 |
+---------------+---------+
| コミット | 7424815 |
+---------------+---------+
セット内の 1 行 (0.00 秒)

mysql> 'com_rollback' のようなグローバル ステータスを表示します。
+---------------+---------+
| 変数名 | 値 |
+---------------+---------+
| Com_rollback | 1073179 |
+---------------+---------+
セット内の 1 行 (0.00 秒)

mysql> 'uptime' のようなグローバル ステータスを表示します。
+---------------+--------+
| 変数名 | 値 |
+---------------+--------+
| 稼働時間 | 389467 |
+---------------+--------+
セット内の 1 行 (0.00 秒)

TPS=(com_commit+com_rollback)/稼働時間=22

3 スレッド接続とヒット率

mysql> 'threads_%' のようなグローバル ステータスを表示します。
+-------------------+------+
| 変数名 | 値 |
+-------------------+------+
| Threads_cached | 480 | //現時点でスレッド キャッシュ内にあるアイドル スレッドの数を表します| Threads_connected | 153 | //現在確立されている接続の数を表します。1 つの接続には 1 つのスレッドが必要なので、現在使用中のスレッドの数とみなすこともできます| Threads_created | 20344 | //最後のサービス起動以降に作成されたスレッドの数を表します| Threads_running | 2 | //現在アクティブな (スリープ状態ではない) スレッドの数を表します+-------------------+-------+
セット内の 4 行 (0.00 秒)

mysql> '接続' のようなグローバル ステータスを表示します。
+---------------+-----------+
| 変数名 | 値 |
+---------------+-----------+
| 接続 | 381487397 |
+---------------+-----------+
セット内の 1 行 (0.00 秒)

スレッド キャッシュ ヒット率 = 1-Threads_created/Connections = 99.994%

スレッド キャッシュの数を設定します。mysql> show variables like '%thread_cache_size%';
+-------------------+------+
| 変数名 | 値 |
+-------------------+------+
| スレッドキャッシュサイズ | 500 |
+-------------------+------+
セット内の 1 行 (0.00 秒)

Threads_connected に基づいて、thread_cache_size 値をどの程度の大きさに設定する必要があるかを見積もることができます。一般的に、250 が適切な上限です。メモリが十分に大きい場合は、thread_cache_size 値を thread_connected 値と同じに設定することもできます。

または、threads_created 値を観察して、値が大きいか増加し続ける場合は、thread_cache_size 値を適切に増やすことができます。休止状態では、各スレッドは約 256 KB のメモリを占有するため、メモリが十分にある場合は、値が数千を超えない限り、値を小さく設定しすぎてもメモリをあまり節約できません。

4 テーブルキャッシュ

mysql> 'open_tables%' のようなグローバル ステータスを表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| オープンテーブル | 2228 |
+---------------+-------+
セット内の 1 行 (0.00 秒)

開いているテーブルのキャッシュとテーブル定義のキャッシュを設定します

mysql> 'table_open_cache' のような変数を表示します。
+------------------+-------+
| 変数名 | 値 |
+------------------+-------+
| テーブルオープンキャッシュ | 16384 |
+------------------+-------+
セット内の 1 行 (0.00 秒)

mysql> 'table_defi%' のような変数を表示します。
+------------------------+-------+
| 変数名 | 値 |
+------------------------+-------+
| テーブル定義キャッシュ | 2000 |
+------------------------+-------+
セット内の 1 行 (0.00 秒)

MyISAMの場合:

MySQL がテーブルを開くたびに、一部のデータが table_open_cache キャッシュに読み込まれます。MySQL がこのキャッシュで対応する情報を見つけられない場合、ディスクから直接読み取ります。したがって、この値は、テーブル定義を再度開いて再解析する必要がないように、十分に大きく設定する必要があります。通常は max_connections の 10 倍に設定されますが、10000 以内に抑えるのが最適です。

もう 1 つの基準は、ステータス open_tables の値に応じて設定することです。open_tables の値が毎秒大きく変化することがわかった場合は、table_open_cache の値を増やす必要がある可能性があります。

table_definition_cache は通常、テーブルが数万個ある場合を除き、サーバー内に存在するテーブルの数に設定されます。

InnoDBの場合:

MyISAM とは異なり、InnoDB では開いているテーブルと開いているファイルの間に直接的な接続はありません。つまり、frm テーブルが開かれると、対応する ibd ファイルが閉じられる可能性があります。

したがって、InnoDB は table_definiton_cache のみを使用し、table_open_cache は使用しません。

frm ファイルは table_definition_cache に保存されますが、idb は innodb_open_files によって決定されます (innodb_file_per_table が有効になっていると仮定)。サーバーがすべての .ibd ファイルを同時に開いたままにできるように、innodb_open_files を十分に大きく設定するのが最適です。

5 最大接続数

mysql> 'Max_used_connections' のようなグローバル ステータスを表示します。
+----------------------+-------+
| 変数名 | 値 |
+----------------------+-------+
| 最大使用接続数 | 1785 |
+----------------------+-------+
セット内の 1 行 (0.00 秒)

max_connectionsのサイズを設定します

mysql> 'max_connections%' のような変数を表示します。
+-----------------+-------+
| 変数名 | 値 |
+-----------------+-------+
| 最大接続数 | 4000 |
+-----------------+-------+
セット内の 1 行 (0.00 秒)

通常、max_connections のサイズは、max_used_connections 状態値よりも大きく設定する必要があります。max_used_connections 状態値は、サーバー接続が一定期間内に急増したかどうかを反映します。この値が max_connections 値よりも大きい場合、クライアントが少なくとも 1 回拒否されたことを意味します。次の条件を満たすように設定するだけで済みます: max_used_connections/max_connections=0.8

6 Innodb キャッシュヒット率

mysql> 'innodb_buffer_pool_read%' のようなグローバル ステータスを表示します。
+---------------------------------------+--------------+
| 変数名 | 値 |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 268720 | // 事前読み取りページの数 | Innodb_buffer_pool_read_ahead_evicted | 0 |   
| Innodb_buffer_pool_read_requests | 480291074970 | // バッファプールからの読み取り数 | Innodb_buffer_pool_reads | 29912739 | // 物理ディスクから読み取られたページ数を示します+---------------------------------------+--------------+
セット内の行数は 5 です (0.00 秒)

バッファプールヒット率 = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads) = 99.994%

この値が 99.9% 未満の場合は、innodb_buffer_pool_size の値を増やすことをお勧めします。この値は通常、総メモリ サイズの 75% ~ 85% に設定されます。または、オペレーティング システムに必要なキャッシュ + 各 MySQL 接続に必要なメモリ (ソート バッファや一時テーブルなど) + MyISAM キー キャッシュを計算し、残りのメモリを innodb_buffer_pool_size に割り当てます。ただし、あまり大きく設定しないでください。メモリ スワップが頻繁に発生したり、ウォームアップやシャットダウンに時間がかかるなどの問題が発生します。

7 MyISAM キーバッファヒット率とバッファ使用量

mysql> 'key_%' のようなグローバル ステータスを表示します。
+------------------------+------------+
| 変数名 | 値 |
+------------------------+------------+
| キーブロックがフラッシュされていません | 0 |
| 未使用のキーブロック | 106662 |
| 使用されたキーブロック | 107171 |
| キー読み取りリクエスト | 883825678 |
| キー読み取り | 133294 |
| キー書き込みリクエスト | 217310758 |
| キー書き込み | 2061054 |
+------------------------+------------+
セット内の行数は 7 です (0.00 秒)

mysql> '%key_cache_block_size%' のような変数を表示します。
+----------------------+-------+
| 変数名 | 値 |
+----------------------+-------+
| キーキャッシュブロックサイズ | 1024 |
+----------------------+-------+
セット内の 1 行 (0.00 秒)


mysql> '%key_buffer_size%' のような変数を表示します。
+-----------------+-----------+
| 変数名 | 値 |
+-----------------+-----------+
| キーバッファサイズ | 134217728 |
+-----------------+-----------+
セット内の 1 行 (0.00 秒)

バッファ使用量 = 1-(Key_blocks_unused*key_cache_block_size/key_buffer_size) = 18.6%

読み取りヒット率 = 1-Key_reads /Key_read_requests = 99.98%

書き込みヒット率 = 1-Key_writes / Key_write_requests = 99.05%

バッファの使用率が高くないことがわかります。長時間経過してもすべてのキー バッファが使い果たされていない場合は、バッファ サイズを小さくすることを検討できます。

主要なキャッシュ ヒット率はアプリケーションに依存するため、あまり意味がないかもしれません。アプリケーションによっては 95% のヒット率で問題なく動作するものもありますが、99.99% を必要とするものもあります。したがって、経験上、1 秒あたりのキャッシュ ミスの数の方が重要です。スタンドアロン ディスクが 1 秒あたり 100 回のランダム読み取りを実行できると仮定すると、1 秒あたり 5 回のキャッシュ ミスでは I/O ビジー状態は発生しないかもしれませんが、1 秒あたり 80 回になると問題が発生する可能性があります。

1秒あたりのキャッシュミス数 = Key_reads/uptime = 0.33

8 一時テーブルの使用

mysql> 'Created_tmp%' のようなグローバル ステータスを表示します。
+-------------------------+-----------+
| 変数名 | 値 |
+-------------------------+-----------+
| tmp_disk_tables が作成されました | 19226325 |
| 作成された tmp ファイル | 117 |
| 作成された tmp テーブル | 56265812 |
+-------------------------+-----------+
セット内の 3 行 (0.00 秒)

mysql> '%tmp_table_size%' のような変数を表示します。
+----------------+----------+
| 変数名 | 値 |
+----------------+----------+
| tmp_table_size | 67108864 |
+----------------+----------+
セット内の 1 行 (0.00 秒)

合計 56,265,812 個の一時テーブルが作成され、そのうち 19,226,325 個がディスク IO に関係し、約 0.34 を占めていることがわかります。これは、データベース アプリケーションでのソートや結合ステートメントに関係するデータ量が大きすぎることを証明しており、SQL を最適化するか、tmp_table_size の値を増やす必要があります。私は 64M に設定しました。比率は0.2以内に制御する必要があります。

9 Binlogキャッシュの使用

mysql> 'Binlog_cache%' のようなステータスを表示します。 
+----------------------+----------+
| 変数名 | 値 |
+----------------------+----------+
| Binlog_cache_disk_use | 15 |
| Binlog_cache_use | 95978256 |
+----------------------+----------+
セット内の 2 行 (0.00 秒)

mysql> 'binlog_cache_size' のような変数を表示します。
+-------------------+---------+
| 変数名 | 値 |
+-------------------+---------+
| binlog_cache_size | 1048576 |
+-------------------+---------+
セット内の 1 行 (0.00 秒)

Binlog_cache_disk_use は、binlog_cache_size によって設計されたメモリ不足のために、バイナリ ログをキャッシュするために一時ファイルが使用された回数を示します。

Binlog_cache_useは、binlog_cache_sizeがキャッシュに使用された回数を示します。

対応するBinlog_cache_disk_use値が比較的大きい場合は、対応するbinlog_cache_sizeの値を適切に増やすことを検討できます。

10 Innodb ログバッファサイズ設定

mysql> '%innodb_log_buffer_size%' のような変数を表示します。
+------------------------+----------+
| 変数名 | 値 |
+------------------------+----------+
| innodb_log_buffer_size | 8388608 |
+------------------------+----------+
セット内の 1 行 (0.00 秒)
mysql> 'innodb_log_waits' のようなステータスを表示します。
+------------------+-------+
| 変数名 | 値 |
+------------------+-------+
| Innodb_log_waits | 0 |
+------------------+-------+
セット内の 1 行 (0.00 秒)

innodb_log_buffer_size を 8M に設定しましたが、これは十分な大きさです。innodb_log_waits は、ログ バッファー不足による待機回数を示します。この値が 0 でない場合は、innodb_log_buffer_size の値を適切に増やすことができます。

11. テーブルスキャンステータスの判定

mysql> 'Handler_read%' のようなグローバル ステータスを表示します。
+----------------------+--------------+
| 変数名 | 値 |
+----------------------+--------------+
| ハンドラー_read_first | 19180695 |
| ハンドラー読み取りキー | 30303690598 |
| ハンドラー_read_last | 290721 |
| ハンドラー_read_next | 51169834260 |
| ハンドラー_read_prev | 1267528402 |
| ハンドラー読み取りrnd | 219230406 |
| ハンドラー_read_rnd_next | 344713226172 |
+----------------------+--------------+
セット内の行数は 7 です (0.00 秒)

Handler_read_first: インデックス スキャンが使用される回数。この値から、システム パフォーマンスが良好か悪いかを判断することはできません。

Handler_read_key: キーを介したクエリの数。値が大きいほど、システムのパフォーマンスが向上します。

Handler_read_next: インデックスを使用してソートする回数
Handler_read_prev: このオプションは、インデックススキャンを実行するときに、データファイルからインデックスの逆順にデータを取得する回数を示します。通常は ORDER BY ... DESC です。

Handler_read_rnd: 値が大きいほど、システム内でソートにインデックスを使用しない操作が多数あるか、結合中にインデックスが使用されない可能性が高くなります。

Handler_read_rnd_next: データ ファイルがスキャンされる回数。値が大きいほど、テーブル全体のスキャンが多くなったり、インデックスが不適切に作成され、確立されたインデックスが十分に使用されていない可能性が高くなります。

12 Innodb_buffer_pool_wait_free

mysql> 'Innodb_buffer_pool_wait_free' のようなグローバル ステータスを表示します。
+------------------------------+-------+
| 変数名 | 値 |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0 |
+------------------------------+-------+
セット内の 1 行 (0.00 秒)

値が 0 でない場合は、バッファ プールに空き領域がないことを意味します。考えられる原因は、innodb_buffer_pool_size が大きすぎる値に設定されていることです。この値を適切に減らすことができます。

13 結合操作情報

mysql> 'select_full_join' のようなグローバル ステータスを表示します。
+------------------+-------+
| 変数名 | 値 |
+------------------+-------+
| フル結合を選択 | 10403 |
+------------------+-------+
セット内の 1 行 (0.00 秒)

この値は、結合操作でインデックスが使用されなかった回数を示します。値が大きい場合、結合ステートメントに問題があることを示します。

mysql> 'select_range' のようなグローバル ステータスを表示します。
+---------------+----------+
| 変数名 | 値 |
+---------------+----------+
| 範囲を選択 | 22450380 |
+---------------+----------+
セット内の 1 行 (0.00 秒)

この値は、最初のテーブル内の範囲を使用した結合の数を示します。値が大きい場合、結合が適切に記述されていることを示します。通常、select_full_join と select_range の比率をチェックして、システム内の結合ステートメントのパフォーマンスを判断できます。

mysql> 'Select_range_check' のようなグローバル ステータスを表示します。
+--------------------+-------+
| 変数名 | 値 |
+--------------------+-------+
| 選択範囲チェック | 0 |
+--------------------+-------+
セット内の 1 行 (0.00 秒)

値が 0 でない場合は、テーブルのインデックスが適切かどうかを確認する必要があります。つまり、テーブル n の各行のインデックスをテーブル n+1 で再評価して、コストが最小であるかどうかを確認する必要があります。行われた結合の数は、テーブル n+1 にこの結合に役立つインデックスがないことを意味します。

mysql> 'select_scan' のようなグローバル ステータスを表示します。
+---------------+-----------+
| 変数名 | 値 |
+---------------+-----------+
| スキャンを選択 | 116037811 |
+---------------+-----------+
セット内の 1 行 (0.00 秒)

select_scan は、最初のテーブルをスキャンする接続の数を示します。最初のテーブルのすべての行が結合に関係する場合は、これで問題ありません。すべての行を返す必要がなく、必要な行を見つけるためにインデックスを使用しない場合は、大きな数は不適切です。

14 遅いクエリ

mysql> 'Slow_queries' のようなグローバル ステータスを表示します。
+---------------+--------+
| 変数名 | 値 |
+---------------+--------+
| 遅いクエリ | 114111 |
+---------------+--------+
セット内の 1 行 (0.00 秒)

この値は、MySQL が起動してからの遅いクエリの数、つまり実行時間が long_query_time を超えた回数を示します。単位時間あたりの遅いクエリの数は、Slow_queries/uptime の比率に基づいて判断でき、システムのパフォーマンスを判断できます。

15 テーブルロック情報

mysql> 'table_lock%' のようなグローバル ステータスを表示します。
+----------------------+-------------+
| 変数名 | 値 |
+----------------------+-------------+
| テーブルロック即時 | 1644917567 |
| テーブルロック待機 | 53 |
+----------------------+-------------+
セット内の 2 行 (0.00 秒)

これら 2 つの値の比率: Table_locks_waited / Table_locks_immediate は 0 に近づきます。値が大きい場合、システムが深刻にブロックされていることを意味します。

上記のいくつかの重要なMySQLパフォーマンス指標の計算と最適化方法の要約は、編集者が皆さんと共有するすべての内容です。参考になれば幸いです。また、123WORDPRESS.COMを応援していただければ幸いです。

以下もご興味があるかもしれません:
  • mysql 時間差計算関数
  • MySQL で誕生日から年齢を計算する複数の方法
  • PHP と MySql で時間差を計算する方法
  • MySQL 文字列長計算実装コード (gb2312+utf8)
  • MySQLデータベースのQPSとTPSの意味と計算方法
  • mysqlのkey_lenの計算方法についての簡単な説明
  • MySQLクエリプランでken_lenの値を計算する方法
  • MySQLの日付と時刻の間隔計算の分析例
  • MySQLの日付加算と減算関数の詳細な説明
  • mysql トリガーの作成と使用例
  • MySQL トリガーの基本的な使い方(作成、表示、削除など)の詳細な説明
  • MySQL累積計算実装方法の詳しい説明

<<:  Vue は Ctrip のカルーセル効果を模倣します (スライディング カルーセル、以下は高度に適応)

>>:  Vue シングルページ アプリケーションで Markdown レンダリングを実装する

推薦する

Reactにおけるコンポーネント通信の詳細な説明

目次親コンポーネントは子コンポーネントと通信します子コンポーネントは親コンポーネントと通信しますコン...

JavaScript ベースで年・月・日の 3 階層連携を実現

この記事では、年、月、日の3段階のリンクを実現するためのJavaScriptの具体的なコードを参考ま...

一定期間の日ごと、時間ごとの統計データを取得するMySQLの詳しい説明

毎日の統計情報を取得するプロジェクトを実行する際、プロジェクト ログを分析する必要があります。要件の...

JavaScript の組み込み Date オブジェクトの詳細な説明

目次日付オブジェクト日付オブジェクトの作成新しい日付()日付を取得する()取得日()月を取得する()...

Mac VMware Fusion CentOS7 静的 IP 構成チュートリアル図

目次CentOS7をインストールする静的IPの設定viを使用してファイルを編集するCentOS7をイ...

MySQLデータベースのロック機構の分析

同時アクセスの場合、非反復読み取りやその他の読み取り現象が発生する可能性があります。高い同時実行性に...

Nginxの書き換えモジュールの詳細な説明

書き換えモジュールは ngx_http_rewrite_module モジュールです。その主な機能は...

MySQL 8.0.18 のインストールと設定方法のグラフィックチュートリアル

この記事は、参考のためにMySQL 8.0.18のインストールと設定のグラフィックチュートリアルを記...

ECMAScript のイテレータの詳細な説明

目次序文以前のバージョンイテレータパターンイテレータファクトリ関数イテレータプロトコル最後に序文多く...

Ubuntuで余分なカーネルを削除する方法

ステップ1: 現在のカーネルを表示する 読み取る $ uname -a Linux rew 4.15...

支払いカウントダウンを実現し、ホームページに戻るためのjs

ホーム ページに戻るための支払いカウントダウン ケースの概要: シンプルな js 構文、getEle...

インターネットウェブデザインにおけるバイオニックデザインの簡単な紹介

バイオニックデザインといえば、飛行機の発明、ドバイのブルジュ・アル・アラブ、平泳ぎなどを思い浮かべる...

JavaScript を学ぶときに知っておくべき 3 つのヒント

目次1. 魔法の拡張演算子1. 配列をコピーする2. 配列を結合する3. オブジェクトを展開する2....

MySQL 5.7.33 インストール プロセスの詳細な図解

目次インストールパッケージのダウンロードインストール環境変数の設定インストールが成功したか確認する記...

Samba を使用して Linux サーバー上で共有ファイル サービスを構築する方法

最近、私たちの小さなチームは、サーバー上の共有フォルダーを共有して、全員がパブリックリソースドキュメ...