MYSQL大規模書き込み問題の最適化の詳細な説明

概要: MySQL のパフォーマンス最適化について話すとき、誰もがクエリ パフォーマンスを向上させるために SQL とインデックスを最適化することに焦点を当てます。ほとんどの製品や Web サイトは、同時実行性の高いデータ読み取りの問題に直面しています。しかし、大量のデータを書き込むシナリオを最適化するにはどうすればよいでしょうか?

今日は主に、書き込み回数が多いシナリオ向けの最適化ソリューションを紹介します。

一般的に、MYSQL データベースの書き込みパフォーマンスは、主にデータベース自体の構成、オペレーティング システムのパフォーマンス、およびディスク IO のパフォーマンスによって制限されます。主な最適化方法は次のとおりです。

1. データベースパラメータを調整する

(1) innodb_flush_log_at_trx_commit

デフォルト値は1です。これはデータベースのトランザクションコミット設定パラメータです。オプションの値は次のとおりです。

0: ログ バッファーは 1 秒ごとにログ ファイルに書き込まれ、ログ ファイルはディスクにフラッシュされますが、トランザクションのコミット時に操作は実行されません。

1: 各トランザクションがコミットされると、ログ バッファーがログ ファイルに書き込まれ、ディスク操作によってログ ファイルが更新されます。

2: コミットごとにログ バッファーがファイルに書き込まれますが、ログ ファイルに対するディスク操作は実行されません。ログ ファイルは 1 秒ごとにフラッシュされます。

値を 1 以外に変更すると安全ではないと言う人もいるかもしれません。 セキュリティの比較は次のとおりです。

MySQL マニュアルでは、トランザクションの永続性と一貫性を確保するために、このパラメータを 1 に設定することを推奨しています。工場出荷時のデフォルトは 1 で、これが最も安全な設定です。

innodb_flush_log_at_trx_commit と sync_binlog が両方とも 1 の場合、最も安全です。mysqld サービスまたはサーバー ホストがクラッシュした場合、バイナリ ログで失われる可能性があるのは、最大で 1 つのステートメントまたは 1 つのトランザクションのみです。

ただし、この場合、頻繁な IO 操作が発生するため、このモードも最も遅くなります。

• innodb_flush_log_at_trx_commit が 0 に設定されている場合、mysqld プロセスがクラッシュすると、最後の 1 秒間のすべてのトランザクション データが失われます。
• innodb_flush_log_at_trx_commit が 2 に設定されている場合、オペレーティング システムがクラッシュするか、システムの電源が失われた場合にのみ、最後の 1 秒間のすべてのトランザクション データが失われる可能性があります。

同じテーブルに対して、システムのビジネス プロセスに従って C# コードを通じてバッチ挿入を実行します。パフォーマンスの比較は次のとおりです。

• (a. 同じ条件: innodb_flush_log_at_trx_commit=0 の場合、500,000 行のデータを挿入するのに 25.08 秒かかります。
• (b. 同じ条件: innodb_flush_log_at_trx_commit=1 の場合、500,000 行のデータを挿入するのに 17 分 21.91 秒かかります。
• (c. 同じ条件: innodb_flush_log_at_trx_commit=2 の場合、500,000 行のデータを挿入するのに 1 分 0.35 秒かかります。

結論: 0 に設定すると、データの書き込みが最も速くなり、データベースの書き込みパフォーマンスがすぐに向上しますが、最後の 1 秒間のデータが失われる可能性があります。

(2) temp_table_size、heap_table_size

これら 2 つのパラメータは、主に一時テーブルとメモリ エンジン テーブルの書き込みに影響します。設定値が小さすぎると、「テーブルがいっぱいです」というエラー メッセージが表示されることもあります。

実際の業務状況に応じて、書き込むデータ量よりも占有する領域を大きく設定する必要があります。

（３）max_allowed_pa​​cket=256M、net_buffer_length=16M、autocommit=0に設定

バックアップと復元時にこれら 3 つのパラメータを正しく設定すると、バックアップと復元の速度が飛躍的に向上します。

(4) innodb_data_file_path=ibdata1:1G;ibdata2:64M:自動拡張

明らかに、テーブルスペースの後の自動拡張はテーブルスペースを自動的に拡張するためのものですが、デフォルトでは 10 MB しかありません。大規模なデータ書き込みのシナリオでは、このパラメータを増やす必要がある場合があります。

テーブルスペースが拡大したときに、一度にできるだけ多くのテーブルスペースを割り当て、大量のバッチ書き込み時に頻繁なファイル拡張を回避する

(5) innodb_log_file_size、innodb_log_files_in_group、innodb_log_buffer_size

トランザクション ログのサイズ、ログ グループの数、およびログ バッファーを設定します。デフォルト値は非常に小さいです。innodb_log_file_size のデフォルト値は数十 MB しかなく、innodb_log_files_in_group のデフォルト値は 2 です。

ただし、InnoDB では、通常、データは最初にキャッシュに書き込まれ、次にトランザクション ログに書き込まれ、最後にデータ ファイルに書き込まれます。値が小さすぎると、大量のデータが書き込まれるシナリオでは、必然的にデータベース チェックポイントが頻繁にトリガーされ、ログ内のデータがディスク データ ファイルに書き込まれることになります。バッファの更新とログの切り替えが頻繁に行われると、大量のデータを書き込むときにパフォーマンスが低下します。

もちろん、大きすぎる値に設定してはいけません。サイズが大きすぎると、データベースが異常クラッシュする可能性があります。データベースを再起動すると、データファイルに書き込まれていないログ内のダーティデータを読み取り、再実行してデータベースを復元します。サイズが大きすぎると、回復時間が長くなります。回復時間がユーザーの予想回復時間を大幅に超えると、必然的にユーザーからの苦情が発生します。

この設定については、Huawei Cloud のデータベースのデフォルト設定を参照できます。Huawei Cloud の 2 コア 4G 環境では、デフォルト構成は buffer: 16M、log_file_size: 1G のようです。これは、MySQL が推奨する総メモリの約 25% です。また、ログ グループ files_in_group は 4 グループに設定されています。

2 コアと 4G という低いハードウェア構成でも、適切なパラメータ設定により、1 秒あたり数千件の読み取りおよび書き込み要求、1 分あたり 80,000 件を超える読み取りおよび書き込み要求に耐えることができます。

書き込まれるデータの量が読み取られるデータの量よりはるかに大きい場合、またはパラメータを任意に変更したい場合は、大量のデータをインポートしてから、log_file_size をより大きな値に調整することができます。これは、innodb_buffer_pool_size の 25% ～ 100% に達する可能性があります。

（6）innodb_buffer_pool_sizeはMySQL Innodbの利用可能なキャッシュサイズを設定します。理論上、設定できる最大値はサーバー全体のメモリの 80% です。

もちろん、値を大きくすると、値を小さくするよりも書き込みパフォーマンスが向上します。たとえば、上記のパラメータ innodb_log_file_size は、innodb_buffer_pool_size のサイズを参照して設定されます。

(7) innodb_thread_concurrency=16

名前が示すように、同時スレッドの数を制御します。理論的には、スレッドの数が多いほど、書き込みが速くなります。もちろん、あまり大きく設定しすぎることはできません。公式の推奨値はCPUコア数の2倍程度です。

(8) 書き込みバッファサイズ

単一セッション書き込みのキャッシュ サイズを制御します。デフォルト値は約 4K で、通常は調整する必要はありません。ただし、大量のデータが頻繁に書き込まれるシナリオでは、2M に調整してみると、書き込み速度がある程度向上することがわかります。

(9) innodb_buffer_pool_instance

デフォルト値は 1 で、主にメモリ バッファ プールの数を設定します。簡単に言うと、innodb_buffer_pool の同時読み取りと書き込みの数を制御します。

大規模な書き込みが発生するシナリオでは、このパラメータを増やすこともできます。これにより、パフォーマンスが大幅に向上します。

(10) bin_log

バイナリ ログには通常、データベースのすべての追加、削除、および変更操作が記録されます。ただし、データベースの復元など、大量のデータをインポートする場合は、bin_log を一時的に閉じ、バイナリ ログへの書き込みをオフにし、データ ファイルにのみデータを書き込み、データの回復をすばやく完了してから、完了したら再度開くことをお勧めします。

2. ディスクIOを削減し、ディスクの読み取りと書き込みの効率を向上

以下のメソッドが含まれます。

（１）データベースシステムアーキテクチャの最適化

a: マスタースレーブレプリケーションを実行します。

たとえば、デュアルマスタースレーブモードを展開します。デュアルマスタースレーブモードは、相互にバックアップし、データのセキュリティを確保するために展開されます。異なる業務システムを異なるデータベースサーバーに接続し、ngnix または keepalive の自動切り替え機能を組み合わせて、負荷分散と障害発生時の自動切り替えを実現します。

このアーキテクチャの最適化により、分散ビジネス システムの同時読み取りおよび書き込み IO が 1 つのサーバーから複数のサーバーに移動され、単一のデータベースの書き込み速度も向上します。

b: 読み書きを分離する

1 で考慮すべき問題と同様に、単一サーバーのディスク IO を削減し、サーバー上のバックアップ操作をスタンバイ サーバーに移動することで、プライマリ サーバーの IO 負荷を軽減し、書き込みパフォーマンスを向上させることができます。

（２）ハードウェアの最適化

a: リソースが限られている場合、インストールおよび展開時にオペレーティング システムに複数のディスクが必要です。アプリケーション、データベース ファイル、ログ ファイルなどを異なるディスクに分散して保存することで、各ディスクの IO が削減され、単一ディスクの書き込みパフォーマンスが向上します。

b: ソリッドステートドライブSSDを使用する

リソースが十分であれば、SSD ストレージを使用できます。SSD は高速書き込みの特性があり、すべてのディスク IO 操作を大幅に改善することもできます。

もちろん、ハードウェアやソフトウェアを最適化する方法は他にもありますが、ここでは一つ一つ挙げていません。

MYSQL 大量書き込み問題の詳細な最適化に関するこの記事はこれで終わりです。より関連性の高い MYSQL 大量書き込みコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM を応援していただければ幸いです。