100 Gレベルsqlファイルインポートパラメータ最適化

目次

データベースを作成

MySqlログがハードディスクに書き込まれるタイミングを設定するパラメータ

InnoDBテーブルエンジン下mysql自動トランザクションコミット

を閉じる

Serverが受け入れるパケットサイズ

現在のセッションログを動的に閉じる

バッファプールバイトサイズ

CPUマルチコアの利用

データベースの作成

CREATE DATABASE yourDataBaseName;

MySqlログがハードディスク(HDD)に書き込まれるタイミングを設定するパラメータ

innodb_flush_log_at_trx_commit=0 log bufferは毎秒1回log fileに書き込まれ、log fileのflush(ディスクにブラシ)操作が同時に行われます.このモードでは、トランザクションのコミット時にディスクへの書き込みはアクティブにトリガーされません.0に設定すると、このモードは最も速くなりますが、安全ではありません.mysqldプロセスのクラッシュにより、前の秒にすべてのトランザクションデータが失われます.

innodb_flush_log_at_trx_commit=1トランザクションがコミットされるたびにMySQLはlog bufferのデータをlog fileに書き込み、flush(ディスクにブラシ)をシステムのデフォルトにします.1に設定すると、このモードは最も安全ですが、最も遅い方法です.mysqldサービスがクラッシュしたり、サーバホストcrashがクラッシュしたりした場合、binary logは最大1つの文またはトランザクションを失う可能性があります.

innodb_flush_log_at_trx_commit=2トランザクションがコミットされるたびにmysqlはlog bufferのデータをlog fileに書き込みますが、flush(ディスクにブラシ)操作は同時に行われません.このモードでは、MySQLは1秒に1回flush(ディスクにブラシ)操作を実行します.2に設定すると、モードの速度が速く、0よりも安全で、OSがクラッシュしたり、システムが電源が切れたりした場合にのみ、前の秒にすべてのトランザクションデータが失われる可能性があります.

show variables like "innodb_flush_log_at_trx_commit";
set GLOBAL innodb_flush_log_at_trx_commit=OFF;
show variables like "innodb_flush_log_at_trx_commit";

InnoDBテーブルエンジンの下でmysql自動トランザクションコミットを閉じる

MysqlのInnoDBストレージエンジンはトランザクションをサポートします.MySQLのデフォルトのデータコミット操作モードは自動コミットモード(autocommit)であり、明示的にトランザクションを開始しない限り、各クエリは個別のトランザクションとして自動的に実行されます.InnoDBテーブルエンジンの下でmysql自動トランザクションコミットをオフにすると、データ挿入の効率が大幅に向上します.

1000個のデータを挿入する必要がある場合、デフォルトではmysqlは1000回のデータ書き込み要求を自動的に開始(コミット)します.Autocommitをオフにし、プログラムで制御すれば、一度にcommitで済むので、レートが大幅に向上します.

show variables like "autocommit";
set autocommit=OFF;
show variables like "autocommit";

サーバが受け入れるパケットサイズ

MySQLは、プロファイルに基づいてサーバが受け入れるパケットのサイズを制限します.大きな挿入と更新がmax_を受ける場合があります.allowed_packetパラメータが制限され、書き込みまたは更新に失敗しました.

show variables like "max_allowed_packet";
set GLOBAL max_allowed_packet=4*1024*1024*10;
show variables like "max_allowed_packet";

現在のセッションログを動的に閉じる

mysql起動時にbinlogを開くかどうかをコマンドラインまたはプロファイルで決定し、log_bin変数は、現在のbinlogシステムの状態を報告するだけです(開くかどうか).binlogを閉じるには、sql_を変更します.log_bin、log_を変更することもできますbin、それからmysqlを再起動して、後者はもっと徹底的で、欠点は再起動する必要があります.

sql_log_binは動的変数であり,この変数を修正する際には,現在のセッション(Session)のみに対して有効であってもよいし,グローバル(Global)であってもよい.グローバルに変数を変更すると、新しいセッションのみが有効になります(現在のセッションでは有効になりません).したがって、一般的にグローバルに変数を変更すると、元のすべての接続killが削除されます.

元のデータベースの場合、バイナリ・ログを閉じなければ、リストアのプロセスはバイナリ・ログに記録されます.リソースの浪費だけでなく、ディスクの容量も増加します.(特にバイナリを利用してデータベースをリストアする場合)一般的にリストアする場合は、バイナリ・ログを閉じることを選択します.プロファイルを変更することで、バイナリ・ログを閉じることを再開できます.動的コマンドでsql_を閉じることもできます.log_bin、データベースをインポートします.

show variables like "sql_log_bin";
set sql_log_bin=OFF;
show variables like "sql_log_bin";

バッファ・プールのバイト・サイズ

MyISAMは、オペレーティングシステムキャッシュを使用してデータをキャッシュします.InnoDBはinnodb buffer poolでキャッシュを処理する必要がある.

innodb_buffer_pool_sizeパラメータは、バッファプールバイトサイズ、InnoDBキャッシュテーブル、インデックスデータのメモリ領域を表します.mysqlのデフォルト値は128 Mです.

専用mysqlサーバ設定のサイズ:オペレーティングシステムメモリの70～80%が最適です.

SELECT @@innodb_buffer_pool_size;
SET GLOBAL innodb_buffer_pool_size=5*1024*1024*1024;
SELECT @@innodb_buffer_pool_size;

CPUマルチコアの利用

show variables like "INNODB_WRITE_IO_THREADS";
show variables like "innodb_read_io_threads";