MySQL 最適化: キャッシュ最適化

何人かのブロガーが私の記事を評価してくれたのは嬉しいです。マークと知り合ってからは、私は彼をフォローすることはほとんどなくなりました。しかし、ブロガーがブログをクリックしたとき、そのブログは価値があり、自分の知識不足を補ってくれると感じていることが間接的に示されています。ブログで一番大切なことは、自分にとって役に立つことです。他の人にとっても役に立つなら、それが最高の結果です。私がブログを書くことを続ける理由は、何かの知識を忘れてしまったときに、できるだけ早く信頼できる解決策を見つけるためです。要約した知識を覚えていれば、忘れるのも遅くなります。長い時間が経つと、この知識は最終的に流暢に話せる言葉になり、忘れることを恐れなくなります。このブログでは引き続き MySQL についてお話しします。今回はキャッシュの最適化についてお話ししますが、お話しするプロセスは私自身の学習プロセスでもあります。

まずはMySQLのバージョンを見てみましょう。私のMacにインストールされているバージョンは5.7で、多くの内容が変更されています。ここで説明する主なバージョンは 5.6 です。

[root@roverliang ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.24、Linux (x86_64) 用、EditLine ラッパー使用

1. MySQLキャッシュの分類

MySQLの最適化は非常に大規模なシステムを指します。インタビュー中にSQL文の最適化についてお話しました。このような最適化も効果的ですが、論理的な観点からの最適化です。しかし、すべての論理的側面が最適化され、すべてのインデックスが追加され、テーブル構造が適切に設計されているのに、なぜ MySQL は高い同時実行性に耐えられないのでしょうか?もちろん、他の方法で MySQL への負荷を軽減することもできますが、ここではそれについては説明しません。 MySQL の場合、すべてのコンピューティング リソースが無駄にならず、最大限に活用できるように、マシンのパフォーマンスを可能な限り最大限に引き出す必要があります。 MySQL はサーバー、具体的には Linux サーバー上で実行されます。サーバーのハードディスク、CPU、メモリ、ネットワークはすべて MySQL のパフォーマンスに影響します。 MySQl はメモリを大量に消費します。オンライン サーバーの MySQL メモリは約 80% です。メモリが小さすぎると、他の最適化を行う余地がほとんどなくなります。

さらに、接続も MySQL のパフォーマンスに影響を与える重要な側面です。 MySQL クライアントと MySQL サーバー間の接続は、MySQL クライアントと MySQL サーバー間のハンドシェイクの繰り返しの結果です。各「ハンドシェイク」は、ID 検証、権限検証、およびその他のリンクを経由します。ハンドシェイクには、一定量のネットワーク リソースと MySQL サーバー メモリ リソースが必要です。

言及しなければならないことの 1 つは、ロック競合です。同時実行パフォーマンス要件が高いデータベースの場合、激しいロック競合が発生すると、データベースのパフォーマンスに大きな影響を与えます。ロックの競合により、スレッド コンテキストの切り替えのオーバーヘッドが大幅に増加する可能性がありますが、これは予想される需要とは関係ありません。

2. ステータスと変数を表示する

MySQL シリーズの以前のブログでは、これらのコマンドを頻繁に目にしてきました。では、これら 2 つのコマンドが MySQL システム管理者にどのような情報を表示するかを見てみましょう。

ステータスを表示

MySQL サービスが実行中の場合、MySQL サービス インスタンスのステータス情報は動的になります。このコマンドを使用すると、現在の MySQL サーバー接続のセッション ステータス変数情報を表示できます。デフォルトでは、変数名は大文字で始まります。

変数を表示

show variables は、MySQL サービス インスタンスのさまざまなシステム変数 (グローバル システム変数、セッション システム変数、静的変数など) を表示するために使用されます。これらの変数には、MySQL コンパイル時パラメータのデフォルト値または my.cnf で設定されたパラメータ値が含まれます。システム変数またはパラメータは静的な概念です。デフォルトでは、システム変数名はすべて小文字です。

MySQL コマンド show status または show session status を使用して、現在の MySQL サーバー接続のセッション変数情報を表示します。セッション ステータスの変数値は、現在の MySQL クライアントに対して有効です (例: Opened_tables、Opened_table_definitions ステータス変数)。

キャッシュメカニズム

キャッシュが有効な理由は、主にプログラム実行時のメモリや外部ストレージへのアクセスがローカルであり、ローカル特性が空間的局所性と時間的局所性であるためです。時間的局所性とは、直前にアクセスしたデータが近い将来に再度アクセスされる可能性があることを意味し、空間的局所性とは、特定の場所にアクセスした後、隣接する場所のデータにアクセスされる可能性が高いことを意味します。 MySQL のキャッシュ メカニズムは、直前にアクセスしたデータ (時間的局所性) と将来アクセスされるデータ (空間的局所性) をキャッシュ、または高速キャッシュに保存することです。これにより、I/O 効率が向上します。

さまざまなキャッシュの読み取りおよび書き込み機能に応じて、MySQL はキャッシュをバッファ キャッシュとキャッシュ キャッシュに分割します。

バッファキャッシュ。ハードディスクの書き込み速度が遅すぎたり、I/O が頻繁すぎると、ハードディスクの効率が大幅に低下します。その後、一定量のデータがキャッシュに保存されるまで待ってから、一度にハードディスクに書き込むことができます。バッファ キャッシュは主にデータの書き込みと I/O パフォーマンスの向上に使用されます。

キャッシュ キャッシュ。キャッシュには通常、頻繁にアクセスされるがほとんど変更されないデータが含まれます。キャッシュがいっぱいになると、LRU アルゴリズムが有効になり、データが削除されます。最も使用頻度の低いデータを削除して、新しいストレージ スペースを作成します。ただし、非常に大規模な Web サイトの場合、この戦略に頼って高頻度の読み取り要求を軽減することは困難です。通常、非常に頻繁にアクセスされるデータは静的化され、nginx によってユーザーに直接返されます。プログラムがデータベース I/O デバイスと対話する回数が少ないほど、効率が高くなります。

MySQL タイムアウト

MySQL を使用すると、接続タイムアウト、ロック待機など、さまざまなタイムアウト例外が発生する可能性があります。

タイムアウトの種類を確認します。

mysql> '%timeout%' のような変数を表示します。
+-----------------------------+----------+
| 変数名 | 値 |
+-----------------------------+----------+
| 接続タイムアウト | 10 |
| 遅延挿入タイムアウト | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | オフ |
| インタラクティブタイムアウト | 28800 |
| ロック待機タイムアウト | 31536000 |
| ネット読み取りタイムアウト | 30 |
| ネット書き込みタイムアウト | 60 |
| rpl_stop_slave_timeout | 31536000 |
| スレーブネットタイムアウト | 3600 |
| 待機タイムアウト | 28800 |
+-----------------------------+----------+

1. 接続タイムアウト（connect_timeout）

connect_timeout のデフォルトは 10 秒です。MySQL 接続の取得は、クライアントとサーバー間のハンドシェイクの結果であり、複数のハンドシェイクの結果です。各ハンドシェイクでは、アカウント名と ID 情報の検証に加えて、ホスト名とドメイン名の解決も検証する必要があります。クライアントとサーバーの間でネットワーク障害が発生した場合、connect_timeout パラメータを設定することで、両者の間でハンドシェイクが繰り返されるのを防ぐことができます。

interactive_timeout は、コマンド ラインに入力される対話型端末を指します。デフォルト値を超えると切断されます。

wait_timeout は、PHP によってインスタンス化された Mysql 接続などの非対話型ターミナルを参照します。このターミナルは常に占有されており、このパラメータで設定された値を超えると自動的に切断されます。

net_write_timeout MySQL サーバーは大きなデータセットを生成します。この値で設定された時間内に MySQL クライアントがそれを受信できない場合、接続は切断されます。

net_read_timeout MySQL クライアントが大量のデータを読み取り、設定された値内に読み取りを完了できない場合、接続は自動的に切断されます。

InnoDB ロック待機タイムアウト

mysql> 'innodb_lock_wait_timeout' のような変数を表示します。
+--------------------------+-------+
| 変数名 | 値 |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+

InnoDB のデフォルトのロック待機時間は 50 秒です。行レベルのロック待機値を設定します。ロック待機が発生したときに、待機時間がこの値を超えると、ロック待機の SQL がロールバックされます (トランザクション全体ではありません)。トランザクション全体をロールバックする場合は、innodb_rollback_on_timeout パラメータを有効にする必要があります。

mysql> '%rollback%' のような変数を表示します。
+----------------------------+-------+
| 変数名 | 値 |
+----------------------------+-------+
| innodb_rollback_on_timeout | オフ |
| innodb_rollback_segments | 128 |
+----------------------------+-------+

innodb_rollback_on_timeout が true に設定されている場合、トランザクションがタイムアウトするとトランザクション全体がロールバックされます。

レプリケーション接続タイムアウト

マスタースレーブ構成の場合、スレーブサーバー (slave) がマスターサーバー (master) からのバイナリログの読み取りに失敗した後、スレーブサーバーは slave_net_timeout を待機してから、マスターから再度バイナリログを取得します。 10秒に設定できます。

mysql> 'slave_net_timeout' のような変数を表示します。
+-------------------+------+
| 変数名 | 値 |
+-------------------+------+
| スレーブネットタイムアウト | 3600 |
+-------------------+------+

この部分の要約は日曜日の夜にまとめられるはずでしたが、今日まで延期されました。私の将来の計画はまた延期しなければなりません。私の先延ばし癖は本当に深刻です。