MySQLのパフォーマンスチューニング

フルスタックの場合、データベーススキルは不可欠であり、リレーショナルデータベースまたはnosql、インメモリデータベースまたはディスクベースのデータベース、オブジェクトストレージデータベースまたはグラフデータベース...たくさんありますが、最初のものは必須です。準備スキルも MySQL である必要があります。 LAMP の台頭から Mariadb の出現、さらには PG の登場に至るまで、熟練した MySQL スキルは非常に役に立ちます。

MySQL データベース テクノロジには多くの側面があります。ここでは、主に動作環境、構成パラメータ、SQL パフォーマンス、システム アーキテクチャ設計のチューニングを含む、ボトムアップのパフォーマンス チューニングのみを取り上げます。

動作環境チューニング

これは Linux の世界ですが、MySQL 実行環境のチューニングは Linux カーネルのチューニングと一緒に完了することがよくあります。もちろん、クラウドサービスRDSのリファレンス的な役割も担っています。

Linux のデフォルトの IO スケジューリング アルゴリズムを調整します。

IO スケジューラの全体的な目標は、磁気ヘッドを常に一方向に移動し、最後まで移動してから反対方向に移動することです。実際のエレベーター モデルは、IO スケジューラとも呼ばれ、対応するアルゴリズムもエレベーター アルゴリズムとも呼ばれます。Linux の IO スケジューリングにはいくつかのエレベーター アルゴリズムがあり、1 つは (Anticipatory) と呼ばれ、もう 1 つはエレベーター アルゴリズムと呼ばれます。 cfq (Complete Fairness) Queueing と呼ばれ、1 つはデッドラインと呼ばれ、もう 1 つは noop (No Operation) と呼ばれます。Linux のデフォルトの IO スケジューリング アルゴリズムは cfq です。 SSD または PCIe-SSD デバイスの場合、noop に変更する必要がある場合は、次の 2 つの変更方法を使用できます。

1. 再起動後、オンラインの動的変更は失敗します。

echo “deadline” > /sys/block/sda/queue/scheduler

2. /etc/grub.conf を変更して永続化します。

/etc/grub.conf 設定ファイルを変更し、カーネル行に設定を追加します。例:

elevator=deadline

カーネルを設定した場合、有効にするためにシステムを再起動する必要があります。

NUMA 機能を無効にする

NUMA の新世代アーキテクチャはデータベースの実行には適していません。NUMA はメモリ使用率を向上させるためのものですが、一方の CPU にメモリが残っているのにもう一方の CPU が足りなくなり、スワップが発生する可能性があります。問題が発生した場合は、通常、NUMA スケジュールをオフにするか変更することをお勧めします。

numa=off

2. /etc/init.d/mysql または mysqld_safe スクリプトを変更して、mysqld プロセスの開始時に NUMA スケジューリング メカニズムを設定します (umactl –interleave=all など)。

スワッピー設定を変更する

swappiness は Linux のカーネル パラメータで、物理メモリのスワップ アウト戦略を制御するために使用されます。最小値は 0、最大値は 100、デフォルト値は 60 です。この設定値はどのような影響を及ぼしますか?

vm.swappiness を 0 に設定すると、スワップの使用を最小限に抑え、100 に設定すると、非アクティブなメモリ ページをスワップ キャッシュまたはリリース キャッシュにスワップしようとすることを意味します。非アクティブなメモリとは、プログラムによってマップされているものの、「長期間」使用されていないメモリを意味します。 vmstat を使用すると、システム内の非アクティブなメモリの量を確認できます。

# vmstat -a 1

この値は1に設定することを推奨します。設定方法は/etc/sysctl.confファイルに以下の行を追加します。

vm.swappiness = 1

ファイル記述子を展開します

これは頻繁に変更されるパラメータであり、同時実行性の高いプログラムによって変更されます。

ulimit -n 51200

2. 構成ファイルを変更すると、永続的に有効になります。

在/etc/security/limits.conf配置文件中增加

* hardnofile 51200
 
* softnofile 51200

面向session的进程文件描述符的修改稍有不同，在云上的修改也略有差异，可以参见一样的“open too many files”

优化文件系统挂载参数。

对于文件系统，如无特殊要求，最好采用ext4.

文件系统挂载参数是在/etc/fstab文件中修改,重启时候生效。

noatime表示不记录访问时间,nodiratime不记录目录的访问时间。

barrier=0,表示关闭barrier功能.

barrier的主要目的是为了保证磁盘写数据的安全性,但是会降低性能。如果有BBU之类的电池备份电源保证控制卡不瞬间掉电,那么这个功能就可以放心大胆的关闭。

配置参数调优

my.cnf中的配置参数调优取决于业务，负载或硬件，在慢内存和快磁盘、高并发和写密集型负载情况下，都需要特殊的调整。

基本配置

query_cache_size

query cache是一个众所周知的瓶颈，甚至在并发并不多时也如此。 最 好是一开始就停用，设置query_cache_size = 0，并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果已经启用了query cache并且还没有发现任何问题，query cache可能有用。如果想停用它，那就得小心了。

innodb_buffer_pool_size

缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

innodb_log_file_size

redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，可以同时拥有较高的写入性能和崩溃恢复性能。在MySQL 5.6里可以被提高到4GB以上。如果应用程序需要频繁的写入数据，可以一开始就把它这是成4G。

max_connections

max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

back_log

要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间检查连接并且启动一个新线程。back_log指明在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，需要增加它，换句话说，该值对到来的tcp/ip连接的侦听队列的大小。

Innodb配置

innodb_file_per_table

この設定は、すべてのテーブルのデータとインデックスを共有テーブルスペースに保存する必要があるか (innodb_file_per_table = OFF)、各テーブルのデータを個別の .ibd ファイルに配置する必要があるか (innodb_file_per_table = ON) を InnoDB に指示します。テーブルごとに 1 つのファイルを使用すると、テーブルの削除、切り捨て、または再構築時にディスク領域を再利用できます。これは、データ圧縮などの一部の高度な機能にも必要です。しかし、それによってパフォーマンスが向上することはありません。 MySQL 5.6 では、このプロパティのデフォルト値は ON です。

innodb_flush_log_at_trx_commit

デフォルト値は 1 で、これは InnoDB が ACID 機能を完全にサポートすることを意味します。この値は、マスター ノードなどのデータ セキュリティが重要な場合に最も適切です。ただし、ディスク (読み取りおよび書き込み) 速度が遅いシステムの場合、REDO ログへの変更をフラッシュするたびに追加の fsync が必要になるため、大きなオーバーヘッドが発生します。値を 0 にすると高速になりますが、システムクラッシュが発生すると一部のデータが失われる可能性があるため、1 つのパスはバックアップ ノードにのみ適用されます。

innodb_flush_method

この構成は、データとログをハードディスクに書き込む方法を決定します。一般に、ハードウェア RAID コントローラがあり、その独立したキャッシュがライトバック メカニズムを使用し、バッテリ電源障害保護機能を備えている場合は、O_DIRECT に設定する必要があります。それ以外の場合は、ほとんどの場合、fdatasync (デフォルト値) に設定する必要があります。 sysbench は、こ​​のオプションを決定するのに役立つ優れたツールです。

innodb_log_buffer_size

この構成は、まだ実行されていないトランザクションに割り当てられるキャッシュを決定します。ただし、トランザクションにバイナリの大きなオブジェクトまたは大きなテキスト フィールドが含まれている場合は、Innodb_log_waits ステータス変数を確認し、それが 0 でない場合は、innodb_log_buffer_size を増やします。

その他の構成

log_bin

データベースサーバーがマスターノードのバックアップノードとして機能する場合は、バイナリログを有効にする必要があります。サーバーが 1 つしかない場合でも、これはポイントインタイムのデータ回復を行う場合に便利です。バイナリ ログは作成されると永久に保存されます。ディスク容量を使い果たしたくない場合は、PURGE BINARY LOGS を使用して古いファイルを削除するか、expire_logs_days を設定してログが自動的に削除されるまでの日数を指定できます。バイナリ ログにはオーバーヘッドがないわけではないため、プライマリ ノードではないレプリカ ノードで必要ない場合は、このオプションをオフにすることをお勧めします。

interactive_timeout

サーバーが対話型接続を閉じる前に、対話型接続でのアクションを待機する秒数。インタラクティブ クライアントは、mysql_real_connect() の client_interactive オプションを使用するクライアントとして定義されます。 デフォルト値は 28800 ですが、7200 に変更することをお勧めします。

table_open_cache

MySQL はテーブルを開くたびに、一部のデータを table_open_cache キャッシュに読み込みます。このキャッシュ内で対応する情報が見つからない場合は、ディスクからデータを読み込みます。システムに 200 の同時接続があると仮定すると、システムがそれほど多くのファイルを処理できない場合、table_open_cache が大きな値に設定されている場合は、このパラメータを 200*N (N は各接続に必要なファイル記述子の数) に設定する必要があります。 descriptors シンボルを使用すると、クライアントは失敗し、接続を確立できません。

max_allowed_pa​​cket

受け入れられるパケット サイズ。追加のメモリは必要な場合にのみ割り当てられるため、この変数の値を増やしても安全です。たとえば、MySQLd は、長いクエリを発行する場合、または MySQLd が大きな結果行を返す必要がある場合にのみ、より多くのメモリを割り当てます。この変数のデフォルト値が小さい理由は、クライアントとサーバー間のエラー パケットをキャプチャし、大きなパケットの誤使用によってメモリ オーバーフローが発生しないようにするための予防措置です

skip_name_resolve

クライアントがデータベースサーバーに接続し、DNS が遅い場合、接続の確立も遅くなります。したがって、DNS ルックアップを行わずにサーバーを起動する場合は、skip_name_resolve オプションをオフにすることをお勧めします。

SQL ステートメントのチューニング

アプリケーション層では、pt ツールとスロークエリログの組み合わせにより、フルテーブルスキャンのステートメントを簡単に識別できます。

基本原則

• フルテーブルスキャンを避ける

• インデックスを作成する

• データの量が多すぎる場合は、クライアントに大量のデータを返さないようにしてください。対応する需要は合理的です

• 大規模なトランザクション操作を避け、システムの同時実行性を向上させるようにしてください。カーソルベースの方法または一時テーブル方法を使用する前に、まずセットベースのソリューションを探して問題を解決する必要があります。通常は、ベースの方法の方が効果的です。カーソルは効率が悪いため、使用しないようにしてください。

• ヒント

where の後の条件について

where 句では != または <> 演算子を使用しないようにしてください。そうしないと、エンジンはインデックスの使用を放棄し、完全な演算子を実行します。テーブルスキャン。

• 条件を接続するために where 句で or を使用することは避けるべきです。代わりに Union を使用することを検討してください。連続値の場合は、 between を使用できる場合は注意が必要です。 in は使用しないでください。in の代わりに

• where 句内のフィールドに対する式演算や関数演算を避けるようにしてください

• データ型について

• フィールドに数値情報のみが含まれる場合は、数値フィールドを使用するようにしてください。文字型として設計しないようにしてください。クエリと結合のパフォーマンスが低下し、ストレージのオーバーヘッドが増加します。

可変長フィールドの記憶域は小さいため、クエリの場合は、比較的小さなフィールドで検索する方が明らかに効率的であるため、char/nchar の代わりに varchar/nvarchar をできるだけ使用してください。

• データベースには NULL を残さないことが最善であり、メモ、説明、コメントなどは NULL に設定できますが、それ以外の場合は使用しないのが最善です。ヌル。

• どこでも select * from t を使用せず、「*」を特定のフィールド リストに置き換え、未使用のフィールドを返さないでください。

• 一時テーブルについて

• システムテーブルのリソースの消費を減らすために、一時テーブルの頻繁な作成と削除を避けてください。 1 回限りのイベントの場合は、エクスポート テーブルを使用するのが最適です。

一時テーブルを作成するとき、一度に挿入されるデータの量が多い場合、create table の代わりに select into を使用すると、大量のログが発生して速度が向上することを避けることができます。システム テーブルのリソースを軽減するために、テーブルを最初に作成してから挿入する必要があります。

• 一時テーブルが使用されている場合、最後にすべての一時テーブルが明示的に削除されるときに、最初にテーブルを切り捨ててからテーブルを削除することで、システムテーブルの長期的なロックを回避できます。

• インデックスについて

• まず、where と order by に関係する列にインデックスを作成することを検討する必要があります。

インデックス フィールドを条件として使用する場合、インデックスが複合インデックスの場合、システムが確実にインデックスを使用するようにインデックスの最初のフィールドを条件として使用する必要があります。そうでない場合、インデックスは使用されません。また、フィールドの順序はインデックスの順序とできる限り一致する必要があります。

• インデックスは多ければ多いほど良いのですが、インデックスは対応する選択の効率を向上させますが、インデックスは挿入または更新中に再構築される可能性があるため、挿入と更新の効率も低下します。状況。 。テーブルに 7 つを超えるインデックスを持たないことをお勧めします。多すぎる場合は、一般的に使用されない一部の列にインデックスを構築する必要があるかどうかを検討する必要があります。

データベース アーキテクチャのチューニング

最下位層からアプリケーション層、そして最終的にはアーキテクチャ層に至るまで、しかし、ビジネス ロジックなしでアーキテクチャについて語るのは単なる悪法です。データベースのアーキテクチャも業務システムに依存しており、業務システムを安定かつ柔軟に提供することが鍵となります。アーキテクチャのチューニングの方向性は次のとおりです。

• パーティションとテーブル

• ビジネスサブライブラリ

• マスター/スレーブ同期と読み書き分離

• データキャッシュ

• マスタースラホットスタンバイおよび HA active-active

• …..

上記は MySQL パフォーマンス チューニングの内容です。さらに関連する内容については、PHP 中国語 Web サイト (www.php.cn) に注目してください。