GA

2018/12/26

InnoDBのテーブル断片化を解消させたいけどもマスターにそんなに容量が残っていない！ 時のテクニック

TL;DR

  • トランスポータブルテーブル表領域を使う
  • メンテが必要になるのが大前提で、それでもストレージ容量がカツカツの場合の対策
    • 容量に余裕があって、 ALTER TABLE .. ENGINE = InnoDB がオンラインで実行できるならそうする
元ネタはこちら。
( ´-｀).oO（見られない人は Join mysql-casual on Slack! から参加していただければどなたでも
前提条件として、
  • OPTIMIZE TABLE, ALTER TABLE .. Engine = InnoDB で空き領域が回収できることがわかっている
    • SELECT table_schema, table_name, data_free FROM information_schema.tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ORDER BY data_free DESC; とかで調べましょう
  • 短時間（容量による）のメンテに入れられる
  • オンラインの ALTER TABLE .. Engine = InnoDB では容量があふれそうである（または負荷的に耐えられないのでメンテに入れた方がマシ）
  • 一時的に、元のデータベース全体と同じだけの容量を持ったサーバーが用意できる
    • レプリケーションフィルターを使いこなせば全体と同じだけじゃなくてもいいけどオススメしない
  • トランスポータブル表領域は5.6とそれ以降の機能
これを満たさない場合はオンラインDDLで OPTIMIZE TABLE, ALTER TABLE したり、 pt-online-schema-change で Engine = InnoDB を指定したりする方が良い。
やり方。
まずはマスターのデータを複製して一時利用のサーバーにスレーブを作る。
この時、断片化を解消してやらないといけないので、 mysqldump, mysqlpump, myloader などの論理バックアップを取ってリストアするか、物理バックアップや xtrabackup の場合はリストアした後にスレーブ側で OPTIMIZE TABLE をかけておく。
d2.t3が断片化しているテーブルだと思いねぇ。
そして断片化を解消させた状態のサーバーをレプリケーションにぶら下げる。
この間、レプリケーションで同期は取られるので、少しくらいメンテまで間が空いても大丈夫。
メンテに入れたら、マスターで read_only = 1 とか offline_mode= 1 とか、とにかくちゃんと書き込めない状態にして、スレーブとのレプリケーションを切り離す。
その後、 d2.t3 （肥大化しているテーブル）を DROP TABLE 。ただし、ファイル操作が混じるのでバイナリーログをOFFにしてレプリケーションで流れないようにすること。
この時、バイナリーログのOFFも忘れてレプリケーションを切り離すのも忘れて DROP TABLE すると大事故になるので注意。
master> SET GLOBAL offline_mode= 1;
slave> STOP SLAVE;
slave> RESET SLAVE ALL;
master> SHOW CREATE TABLE d2.t3\G -- あとで使うのでメモっておく、切り離したスレーブで調べても良い
master> SET SESSION sql_log_bin = 0;
master> DROP TABLE d2.t3;
ここでスレーブのサーバーからトランスポータブル表領域の機能を使って d2.t3 テーブルをマスターにコピーする。ここでコピーする.ibdファイルは既にOPTIMIZE済のため、さっきDROPした.ibdファイルよりも断片化が解消されている分小さくなっている。
/* masterのバイナリーログはOFFのまま！ */
master> CREATE TABLE d2.t3 (..) ; -- さっきメモしたテーブル定義でテーブルを作る
master> ALTER TABLE d2.t3 DISCARD TABLESPACE; -- .ibdファイルを消し飛ばして.frmだけ残す操作
slave> FLUSH TABLE d2.t3 FOR EXPORT; -- スレーブの.ibdファイルをサーバー間移動できるようにするためのクエリー
/* FLUSH TABLE .. FOR EXPORTの効果はmysqlコマンドラインクライアントを抜けると解除されてしまうので、ターミナルは別のものを起動する */
slave $ scp /var/lib/mysql/d2/t3.{ibd,cfg} my-server1:/var/lib/mysql/d2/ ### FLUSH TABLE .. FOR EXPORTのクエリーがOKを返してから
master> ALTER TABLE d2.t3 IMPORT TABLESPACE /* まだmasterのバイナリーログはOFFのまま！ */;
インポートできたらメンテを解除して、一時サーバーを破棄しておしまい。
ね、簡単でしょ？ :thinking_face: ([要出典] 簡単 #とは)
​
投稿者 時刻:
ラベル:

0 件のコメント :

コメントを投稿

登録: コメントの投稿 ( Atom )