[解決済み] ハウツー。mysql InnoDB ストレージエンジンのクリーンアップ？

2022-06-12 22:39:16

質問

削除されたテーブルからデータを保存しないように、mysql innodb ストレージエンジンをクリーンアップすることは可能ですか？

それとも、毎回新しいデータベースを再構築しなければならないのでしょうか？

どのように解決するのですか？

InnoDB に関するより完全な回答はこちらです。これは少し長いプロセスですが、努力する価値があります。

以下のことに留意してください。 /var/lib/mysql/ibdata1 はInnoDBインフラストラクチャの中で最も忙しいファイルであることに留意してください。それは通常6種類の情報を収容します。

テーブルデータ
テーブルインデックス
MVCC (マルチバージョニング同時実行制御) データ
- ロールバックセグメント
- アンドゥスペース
テーブル・メタデータ（データ・ディクショナリ）
ダブルライトバッファ（OSのキャッシュに依存しないバックグラウンドでの書き込み）
インサートバッファ (一意でないセカンダリインデックスへの変更を管理)
を参照してください。 Pictorial Representation of ibdata1

InnoDB アーキテクチャ

多くの人は、複数の ibdata ファイルを複数作成する人がいますが、これは間違いです。

私は `OPTIMIZE TABLE` ?

残念ながら OPTIMIZE TABLE 共有テーブル空間ファイルに格納された InnoDB テーブルに対して ibdata1 は2つのことを行います。

テーブルのデータとインデックスを ibdata1
を作成します。 ibdata1 が大きくなるのは、連続したデータページとインデックスページが が追加されます。 に追加されます。 ibdata1

しかし、テーブルデータとテーブルインデックスは、テーブルデータとテーブルインデックスから分離することができます。 ibdata1 から分離し、独立して管理することができます。

以下のような `OPTIMIZE TABLE` と `innodb_file_per_table` ?

仮に、あなたが innodb_file_per_table から /etc/my.cnf (my.ini) . それから OPTIMIZE TABLE をすべての InnoDB テーブルに適用しますか？

グッドニュース : 走るとき OPTIMIZE TABLE と innodb_file_per_table を有効にすると .ibd ファイルを生成します。例えば、テーブル mydb.mytable があり、データディールが /var/lib/mysql を指定した場合、以下のように表示されます。

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

は .ibd には、そのテーブルのデータページとインデックスページが含まれます。素晴らしい。

バッドニュース : のデータページとインデックスページを抽出しただけです。 mydb.mytable に住んでいるものから ibdata . を含むすべてのテーブルのデータ辞書のエントリは mydb.mytable を含むすべてのテーブルのデータ辞書のエントリは、まだデータ辞書の中に残っています ( ibdata1 の図解表現 ). を単純に削除することはできません。 ibdata1 を削除することはできません。 以下の点に注意してください。 ibdata1 は全く縮んでいないことに注意してください。

InnoDB インフラストラクチャのクリーンアップ

縮小するために ibdata1 をきっぱりと縮小するためには、以下のようにする必要があります。

ダンプする（例えば mysqldump で) 全てのデータベースを .sql テキストファイル ( SQLData.sql は以下で使用)
すべてのデータベースを削除します（ただし mysql と information_schema ) 注意事項 : 注意事項として、このスクリプトを実行して、すべてのユーザーグラントが適切に設定されていることを絶対に確認してください。
```
mkdir /var/lib/mysql_grants
cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
chown -R mysql:mysql /var/lib/mysql_grants
```
mysqlにログインし、以下のコマンドを実行します。 SET GLOBAL innodb_fast_shutdown = 0; (から残っている全てのトランザクションの変更を完全にフラッシュします）。 ib_logfile0 と ib_logfile1 )
MySQLをシャットダウンします。
に以下の行を追加します。 /etc/my.cnf (または my.ini Windowsの場合)
```
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
```
(補足:どのような設定であれ innodb_buffer_pool_size に設定するにしても、必ず innodb_log_file_size の25%であることを確認してください。 innodb_buffer_pool_size .

また innodb_flush_method=O_DIRECT は Windows では使用できません)
削除 ibdata* と ib_logfile* にあるすべてのフォルダを削除することができます。 /var/lib/mysql を除く /var/lib/mysql/mysql .
MySQL を起動します (これは ibdata1 [デフォルトでは 10MB]と ib_logfile0 と ib_logfile1 をそれぞれ1Gで）。
インポート SQLData.sql

今すぐ ibdata1 はまだ成長しますが、各 InnoDB テーブルは、テーブルのメタデータを含むだけです。 ibdata1 . ibdata1 は、他のテーブルの InnoDB データとインデックスを含まなくなります。

例えば、InnoDBテーブルの名前が mydb.mytable . もしあなたが /var/lib/mysql/mydb を見ると、テーブルを表す2つのファイルがあることがわかるでしょう。

mytable.frm (ストレージエンジンヘッダー)
mytable.ibd （テーブルデータおよびインデックス）

を使って innodb_file_per_table オプションで /etc/my.cnf を実行すると OPTIMIZE TABLE mydb.mytable を実行し、ファイル /var/lib/mysql/mydb/mytable.ibd は実際に縮小されます。

私は MySQL DBA としてのキャリアの中で、これを何度も行ってきました。実際、初めてこれを行ったとき、私はシュリンクした 50GB ibdata1 のファイルが500MBになりました。

試してみてください。これに関してさらに質問がある場合は、質問してください。これは短期的にも長期的にも有効です。

注意事項

ステップ 6 で、もし mysql が mysql スキーマが削除されたため、mysql が再起動できない場合、ステップ 2 を振り返ってください。あなたは物理的なコピーを作成し mysql スキーマの物理的なコピーを作成しました。次のようにリストアできます。

mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql

ステップ6に戻り、続ける

更新 2013-06-04 11:13 edt

設定に関して innodb_log_file_size を 25% に設定することで innodb_buffer_pool_size というブランケットルールは、ステップ5では、むしろ古い学校です。

戻る July 03, 2006 で、Percona がいい記事を書いていました。なぜ適切なinnodb_log_file_sizeを選択するのか？ . その後 Nov 21, 2008 で、Percona は別の記事でフォローアップしました。 1 時間分の変更を維持するピーク時の作業負荷に基づいて適切なサイズを計算する方法。 .

それ以来、私は DBA StackExchange でログサイズの計算に関する投稿を書き、その中で Percona の 2 つの記事を参照しました。

Aug 27, 2012 : 48GB RAM のサーバにおける 30GB InnoDB テーブルの適切なチューニング
Jan 17, 2013 : MySQL 5.5 - Innodb - innodb_log_file_size の合計が 4GB より大きいですか？

個人的には、最初のセットアップでは 25% 規則を使用します。その後、実運用で時間をかけてワークロードをより正確に決定することができます。 ログをリサイズすることができます。 を変更することができます。