実録：CPUには余裕があるのにサーバーが「フリーズ」した話
以前、あるECサイトのトラッキングシステムを管理していた時のことです。ユーザー数が急増するまでは順調でした。クリック、商品閲覧、カート追加などの全アクションを分析用にログ出力していました。当初は毎分300〜500レコード程度で問題ありませんでしたが、秒間5,000レコードに達した時、I/O Wait（ディスク待機時間）が40〜50%に跳ね上がり、アラートが鳴り止まなくなりました。
当時、INSERT文の実行に2〜3秒もかかっていました。RAMやCPUには余裕があるにもかかわらず、システム全体が固まってしまったのです。MySQLのデフォルト設定は非常に安全重視ですが、その安全性が継続的な書き込みが発生する「Write-Heavy」なシステムにおいては、逆にボトルネックになっていることに気づきました。
解明：なぜMySQLの書き込みは遅くなるのか？
データを保護するため、MySQLのストレージエンジンであるInnoDBは非常に綿密な処理を行います。1行のデータをINSERTする際、すぐにデータファイルに書き込まれるわけではありません。MySQLはACID特性を保証するために、一連ের複雑な操作を実行します。
- Redo Log: 正式な更新の前に、変更内容を下書き帳に記録します。
- Doublewrite Buffer: 突然の停電などによるページ破損（torn pages）を防ぐため、データを2回書き込みます。
- ディスクフラッシュ: コミットのたびに、MySQLはハードディスクに物理的な書き込み操作（fsync）を強制します。
小さなトランザクションを処理するためにディスクの読み取りヘッドを頻繁に動かすことが、I/Oのボトルネックを引き起こす主な原因です。
MySQLの書き込みパフォーマンスを解放する4つのステップ
1. 「下書き帳」であるRedo Logを拡張する
Redo Log (innodb_log_file_size) は一時的なメモ帳のようなものです。このサイズが小さすぎると、MySQLは新しい書き込みを停止して古いデータを整理（チェックポイント）しなければなりません。このプロセスが、定期的なシステムの「カクつき（ラグ）」を引き起こします。
ログテーブルが5,000万行を超えた際、デフォルトの128MBでは小さすぎました。そこで1GBに増やしたところ、チェックポイントの頻度が劇的に減り、ピーク時でもシステムがスムーズに動作するようになりました。
# my.cnfファイルの設定
[mysqld]
innodb_log_file_size = 1G
innodb_log_files_in_group = 2注意: この変更を有効にするには、MySQLの再起動が必要です。
2. Doublewrite Bufferを無効化する（条件付き）
Doublewrite Bufferはデータの破損を防ぎますが、I/O帯域を2倍消費します。バッテリーバックアップ付きのキャッシュを持つ専用SSDや、ZFSのようなモダンなファイルシステムを使用している場合は、思い切って無効にしましょう。これにより、書き込み速度が即座に約30%向上する可能性があります。
[mysqld]
innodb_doublewrite = 03. Batch Insertের秘策
1,000個の独立したINSERT文を個別に実行してはいけません。1つのSQLコマンドごとにトランザクションの開始とディスクフラッシュのコストがかかります。これらを1つのバッチ（一括処理）にまとめましょう。
遅い方法: ループ内で1行ずつインサートする。リソースの無駄遣いです。
速い方法: 500〜1,000レコードを1つのコマンドにまとめます。これがバッファを過負荷にせず、書き込み速度を数十倍に高める「スイートスポット」です。
# Pythonのexecutemanyを使用して1000レコードを一括挿入
cursor.executemany("INSERT INTO logs (msg) VALUES (%s)", list_of_1000_items)
connection.commit()4. innodb_flush_log_at_trx_commitで安全基準を緩和する
これは最も「強力な」パラメータです。デフォルトは1（最も安全だが最も遅い）です。これを2に設定すると、MySQLはコミットのたびにOSキャッシュにログを書き込みますが、ディスクへの物理的なフラッシュは1秒に1回だけ行われます。
トラッキングシステムの場合、サーバーダウン時に最大1秒分のデータを失うリスクは、爆速な書き込み速度を得るための許容範囲内と言えるでしょう。
[mysqld]
innodb_flush_log_at_trx_commit = 2最適化後の結果
「Redo Logを2GBに増量」「flush_logを2に設定」「Batch Insertの活用」というコンボを適用した結果、サーバーのI/O Waitは40%から5%未満に低下しました。システムは10倍の負荷に耐えられるようになり、CPUも「余裕しゃくしゃく」です。データベースの最適化とは、単に綺麗なコードを書くことではなく、背後にあるハードウェアとの対話の仕方を理解することなのです。
