Slide 1

Slide 1 text

MySQLのALTER TABLEについて少々 ほんの1時間ばかり(少々とは) 2020/05/11 yoku0825 GMOテクノロジーブートキャンプ 2020

Slide 2

Slide 2 text

ここまでのあらすじ(?) web基礎 コーディング基礎 プログラミング基礎 オブジェクト指向 DB設計 SQL MySQL <- イマココ 1/77

Slide 3

Slide 3 text

( ´-`).oO( 初めて具体的な製品名が出て来たぞ DB設計できてSQLが書ければ特定のRDBMSに対する更なるインプット要る? 2/77

Slide 4

Slide 4 text

よし 3/77

Slide 5

Slide 5 text

ここでしか聞けない ような話をしよう ( ・ㅂ・)و ̑̑ 4/77

Slide 6

Slide 6 text

ここでしか 聞け 耳に する機会がないような 話をしよう( ・ㅂ・)و ̑̑ 5/77

Slide 7

Slide 7 text

具体的に言うと ORDER BY のはな しをしたい 6/77

Slide 8

Slide 8 text

??? 7/77

Slide 9

Slide 9 text

詳細は終わりご ろに出てきます 8/77

Slide 10

Slide 10 text

いいわけ 最初は「MySQLと正規化」みたいな話をしようと思っていた でもそういう話、昔やったから またやるのめんどくさい どうせなら世に出てない情報の方が 面白い 有益かなって思った ‐ MySQLと正規形のはなし ‐ 設計段階から正規化できてればいいけど、そうでない場合は運用中に直さないとい けない あるいは完全に正規化されていようと、仕様が変わればスキーマは変わるもの ‐ となると必要なものは何だ? ALTER TABLEだ! ってなった 多少反省はしている 9/77

Slide 11

Slide 11 text

\こんにちは/ yoku0825@GMOメディア株式会社 オラクれない ‐ ポスグれない ‐ マイエスキューエる ‐ 生息域 Twitter: @yoku0825 ‐ Blog: 日々の覚書 ‐ 日本MySQLユーザ会 ‐ MySQL Casual ‐ 10/77

Slide 12

Slide 12 text

MySQLの尻に敷かれる毎日 (c)tomo 11/77

Slide 13

Slide 13 text

おことわり この時間のレベル感はデザイナーも駆け出しエンジニアも等しく「ちょっと何言っ てるかわからない」を目指しています たかだか1時間MySQLの話を聞いたところでMySQLができるようになるわけもあ りませんし、何一つ記憶しなければならないことはありません いつかALTER TABLEで障害を起こした時に「そういえばなんか聞いたような…」 と思い出してくれたりすれば幸いですがこれデザイナーさんも聞いてるんですよね 12/77

Slide 14

Slide 14 text

はい 13/77

Slide 15

Slide 15 text

ALTER TABLE テーブルの定義変更に使うステートメント RDBMSによってバリエーションがあるので飽くまでMySQLのはなし MySQL :: MySQL 8.0 Reference Manual :: 13.1.9 ALTER TABLE Statement 14/77

Slide 16

Slide 16 text

基本構文 ALTER TABLE tbl_name [alter_option [, alter_option] ...] [partition_options] alter_option は特定の組み合わせを除いて列挙可能 インデックスとかまとめて複数張った方がI/Oのインプット側的に有利 追加したカラムにそのままインデックス張るとか、新しいインデックス張るついでに古いイン デックスを消すとか ‐ 15/77

Slide 17

Slide 17 text

table_option(抜粋) table_option: { AUTO_INCREMENT [=] value | [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'} | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory' | ENCRYPTION [=] {'Y' | 'N'} | ENGINE [=] engine_name | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1} | STATS_PERSISTENT [=] {DEFAULT | 0 | 1} | STATS_SAMPLE_PAGES [=] value | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}] } 16/77

Slide 18

Slide 18 text

alter_option(1) alter_option: { | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name] | ADD [COLUMN] (col_name column_definition,...) | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name] | ALTER [COLUMN] col_name {SET DEFAULT {literal | (expr)} | DROP DEFAULT} | DROP [COLUMN] col_name | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | RENAME COLUMN old_col_name TO new_col_name 17/77

Slide 19

Slide 19 text

カラム操作系 データ型の変更を する場合だけ ALTER TABLE中の書き込みがブロックされる それ以外は書き込みしながらテーブルの定義変更ができるのでメンテに入れなくて も全然いける カラムの追加(変更じゃない) ‐ カラムの削除(変更じゃない) ‐ カラムの並べ替え(データ型は変更されない) ‐ カラムのリネーム(データ型は変更されない) ‐ 18/77

Slide 20

Slide 20 text

ただし この3つはテーブル全体が再構築されるのでコストは高い カラムの追加(変更じゃない) ‐ カラムの削除(変更じゃない) ‐ カラムの並べ替え(データ型は変更されない) ‐ 19/77

Slide 21

Slide 21 text

たとえば こんなテーブルがあるじゃろ? num は PRIMARY KEY ‐ mysql80 66> SELECT * FROM t1; +------+-------+ | num | val | +------+-------+ | 1 | one | | 2 | two | | 3 | three | +------+-------+ 3 rows in set (0.00 sec) 20/77

Slide 22

Slide 22 text

その時 ibd ファイルの中身はこんなんなってるじゃろ? 0200200 \0 \0 001 \0 \0 \0 * Z 232 201 \0 \0 001 \v 001 020 0200220 o n e 003 \0 \0 \0 030 \0 033 200 \0 \0 002 \0 \0 <-- one 0200240 \0 * Z 232 201 \0 \0 001 \v 001 035 t w o 005 \0 <-- two 0200260 \0 \0 377 273 200 \0 \0 003 \0 \0 \0 * Z 232 201 0200300 \0 \0 001 \v 001 * t h r e e \0 \0 \0 \0 \0 <-- three 21/77

Slide 23

Slide 23 text

カラムを足すじゃろ? mysql80 68> ALTER TABLE t1 ADD COLUMN val_j varchar(32) DEFAULT 'hoge', ALGORITHM= INPLAC E; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql80 68> SELECT * FROM t1; +------+-------+-------+ | num | val | val_j | +------+-------+-------+ | 1 | one | hoge | | 2 | two | hoge | | 3 | three | hoge | +------+-------+-------+ 3 rows in set (0.00 sec) 22/77

Slide 24

Slide 24 text

こうなるんじゃよ 0240200 200 \0 \0 001 \0 \0 \0 * Z 232 201 \0 \0 001 \v 001 0240220 020 o n e h o g e 004 003 \0 \0 \0 030 \0 <-- onehoge 0240240 200 \0 \0 002 \0 \0 \0 * Z 232 201 \0 \0 001 \v 001 0240260 035 t w o h o g e 004 005 \0 \0 \0 377 260 <-- twohoge 0240300 200 \0 \0 003 \0 \0 \0 * Z 232 201 \0 \0 001 \v 001 0240320 * t h r e e h o g e \0 \0 \0 \0 \0 \0 <-- threehoge 23/77

Slide 25

Slide 25 text

行指向(row- oriented) 24/77

Slide 26

Slide 26 text

行指向(row-oriented) 25/77

Slide 27

Slide 27 text

行指向(row-oriented) 26/77

Slide 28

Slide 28 text

なんじゃよ 27/77

Slide 29

Slide 29 text

そこに颯爽と現れるINSTANT ADD COLUMN しかしそこに考える若者(かどうかは知らんけど)がおったのじゃ 「行指向で格納されたデータはそのままにして、『追加した列が見当たらなかった 時だけデフォルト値返せばいいんじゃね?』『UPDATEやINSERTが走ったやつは その列もデータに格納すればいいんじゃね?』」 頭いいんだこれが 28/77

Slide 30

Slide 30 text

INSTANT ADD COLUMN カラムを足す前のt1と同じ構造のt2にALTER TABLEするじゃろ mysql80 69> ALTER TABLE t2 ADD COLUMN val_j varchar(32) DEFAULT 'hoge', ALGORITHM= INSTAN T; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql80 70> SELECT * FROM t2; +------+-------+-------+ | num | val | val_j | +------+-------+-------+ | 1 | one | hoge | | 2 | two | hoge | | 3 | three | hoge | +------+-------+-------+ 3 rows in set (0.00 sec) 29/77

Slide 31

Slide 31 text

INSTANT ADD COLUMN ないんじゃよ 0200200 \0 \0 001 \0 \0 \0 * Z 301 202 \0 \0 \0 216 001 020 0200220 o n e 003 \0 \0 \0 030 \0 033 200 \0 \0 002 \0 \0 <-- one 0200240 \0 * Z 301 202 \0 \0 \0 216 001 035 t w o 005 \0 <-- two 0200260 \0 \0 377 273 200 \0 \0 003 \0 \0 \0 * Z 301 202 0200300 \0 \0 \0 216 001 * t h r e e \0 \0 \0 \0 \0 <-- three 30/77

Slide 32

Slide 32 text

INSTANT ADD COLUMN INSERTした時に初めて mysql80 76> INSERT INTO t2 VALUES (4, 'four', 'yon'); Query OK, 1 row affected (0.01 sec) mysql80 76> SELECT * FROM t2; +-----+-------+-------+ | num | val | val_j | +-----+-------+-------+ | 1 | one | hoge | | 2 | two | hoge | | 3 | three | hoge | | 4 | four | yon | +-----+-------+-------+ 4 rows in set (0.00 sec) 31/77

Slide 33

Slide 33 text

INSTANT ADD COLUMN 出てくる 0200200 \0 \0 001 \0 \0 \0 * Z 353 202 \0 \0 \0 235 001 020 0200220 o n e 003 \0 \0 \0 030 \0 033 200 \0 \0 002 \0 \0 <-- one 0200240 \0 * Z 353 202 \0 \0 \0 235 001 035 t w o 005 \0 <-- two 0200260 \0 \0 \0 037 200 \0 \0 003 \0 \0 \0 * Z 353 202 0200300 \0 \0 \0 235 001 * t h r e e 003 004 \0 005 200 <-- three 0200320 \0 ( 377 234 200 \0 \0 004 \0 \0 \0 * [ \0 201 \0 0200340 \0 \0 242 001 020 f o u r y o n \0 \0 \0 \0 <-- four, yon 32/77

Slide 34

Slide 34 text

INSTANT ADD COLUMN 33/77

Slide 35

Slide 35 text

INSTANT ADD COLUMN 34/77

Slide 36

Slide 36 text

あったまい いなー… 35/77

Slide 37

Slide 37 text

カラム操作系 データ型の変更を する場合だけ ALTER TABLE中の書き込みがブロックされる それ以外は書き込みしながらテーブルの定義変更ができるのでメンテに入れなくて も全然いける カラムの追加(変更じゃない) ‐ カラムの削除(変更じゃない) ‐ カラムの並べ替え(データ型は変更されない) ‐ カラムのリネーム(データ型は変更されない) ‐ 36/77

Slide 38

Slide 38 text

カラム操作系 カラムを増やす、減らすは当然ある 当然あるんだから、そのために書き込みブロックをしなくても良いようにMySQLは進化してき た ‐ 「スキーマレス」をフル活用するつもりでなければ、「ブロックレススキーマ」は十分良いと こ取りができる ‐ アプリケーション側でカラムの増減に弱い構造にしてはいけない INSERT INTO のカラムリストを省略する ‐ SELECT * でselect_listの順番に依存している ‐ などなど ‐ 37/77

Slide 39

Slide 39 text

はい次 38/77

Slide 40

Slide 40 text

alter_option(2) alter_option: { | ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...) [index_option] ... | ADD [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY] [index_name] [index_type] (key_part,...) [index_option] ... | ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name,...) reference_definition | ADD {INDEX | KEY} [index_name] [index_type] (key_part,...) [index_option] ... | ALTER INDEX index_name {VISIBLE | INVISIBLE} | DROP {INDEX | KEY} index_name | DROP PRIMARY KEY | DROP FOREIGN KEY fk_symbol | RENAME {INDEX | KEY} old_index_name TO new_index_name 39/77

Slide 41

Slide 41 text

インデックス操作系 InnoDBのファイル構造 レコード < ページ < インデックス < テーブル ‐ 40/77

Slide 42

Slide 42 text

インデックス操作系 1つのテーブルスペースファイルの中にインデックスはどんな順番で並んでいても いい 効率のためにある程度連続した領域を取ろうとはするけど、それはその時のファイルの中身の 状況で決まるだけでルールではない ‐ ということは、「新しいインデックスだけ空いているスペースで構築して、最後に 「この領域は新しいインデックスが使っている」とマークしなおせばいい」 fast index creationと呼ばれる手法 ‐ 41/77

Slide 43

Slide 43 text

インデックス操作系 fast index creationから更に発展して、「ALTER TABLEが始まった時点のデータ (これはMVCCで保証できる)をfast index creationして」「ALTER TABLEが始まっ てから終わるまでの間の更新差分だけ新しいインデックスに反映させればいいん じゃ?」 後者を記録するための領域を「(ALTER)オンラインログ」と呼ぶ ‐ ALTER TABLEが「始まった時点のデータを保証する」のと「最後の差分のマージ」はロックで 保護する ‐ InnoDBは行のデータもインデックスの一種なので「カラム操作系」もこのテク ニックを使っている 「別の領域にクラスターインデックスを新しく作ってそれをすり替え」ている ‐ 42/77

Slide 44

Slide 44 text

インデックス操作系 カラムの増減(= 行データを管理するインデックスの改変)よりも更にお手軽 足りなければ足す、を当たり前に ‐ InnoDBのネクストキーロックは名前の通り「インデックスに対するロック+α」な ので、インデックスが効かないと UPDATE や DELETE のロック範囲が異常に広くな る InnoDBのロック競合は基本は「適切なインデックスを足して」やればほとんどの場合上手くい く ‐ 43/77

Slide 45

Slide 45 text

インデックス操作系 MySQLの「ユニーク制約」「外部キー制約」はインデックスに基づいて制約され る 制約をかける ≒ インデックスを作る ‐ 制約の追加、修正も比較的楽。そう、マイエスキューエル(5.6とそれ以降)ならね 44/77

Slide 46

Slide 46 text

alter_option(2.5) alter_option: { | ADD {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...) [index_option] ... | ADD [CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED] | DROP {CHECK | CONSTRAINT} symbol | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED 45/77

Slide 47

Slide 47 text

インデックス操作系? FULLTEXT(全文検索用)インデックス、SPATIAL(空間)インデックスはちょっと別 口 コイツらはB+Treeの形をしていないのでフツーのインデックスと同じ扱いが出来ない ‐ CONSTRAINT のキーワード (だけ) は一緒だけど、CHECK制約もインデックスに基づ かない テーブル内の全データが制約に沿うかどうか確認しないといけない関係上、テーブルへの書き 込みをブロックするしテーブルコピーする ‐ 46/77

Slide 48

Slide 48 text

alter_option(3) alter_option: { | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY} | {DISCARD | IMPORT} TABLESPACE | FORCE | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE} | ORDER BY col_name [, col_name] ... | RENAME [TO | AS] new_tbl_name | {WITHOUT | WITH} VALIDATION } 47/77

Slide 49

Slide 49 text

(つд⊂)ゴシ ゴシ 48/77

Slide 50

Slide 50 text

alter_option(3) alter_option: { | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY} | {DISCARD | IMPORT} TABLESPACE | FORCE | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE} | ORDER BY col_name [, col_name] ... | RENAME [TO | AS] new_tbl_name | {WITHOUT | WITH} VALIDATION } 49/77

Slide 51

Slide 51 text

ORDER BY 50/77

Slide 52

Slide 52 text

alter_option(3) alter_option: { | ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY} | {DISCARD | IMPORT} TABLESPACE | FORCE | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE} | ORDER BY col_name [, col_name] ... | RENAME [TO | AS] new_tbl_name | {WITHOUT | WITH} VALIDATION } 51/77

Slide 53

Slide 53 text

制御系のオプション ORDER BY を指定すると、データ本体の並び順を ORDER BY オプションで指定した通 りに並べ替えてくれる 「は?」って思ったでしょ。俺も8年位前にそう思った。 ‐ ただしプライマリーキー(厳密にはクラスターインデックス)がない場合に限る というかこういうのを真似してはいけない ‐ 52/77

Slide 54

Slide 54 text

ORDER BY PRIMARY KEYなしで作る mysql80 8> CREATE TABLE t3 (num int, val varchar(32)); Query OK, 0 rows affected (0.02 sec) mysql80 8> INSERT INTO t3 VALUES (1, 'one'), (2, 'two'), (3, 'three'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql80 8> SELECT * FROM t3; -- ORDER BYがないSELECTは行が見つかった順に返す +------+-------+ | num | val | +------+-------+ | 1 | one | | 2 | two | | 3 | three | +------+-------+ 3 rows in set (0.00 sec) 53/77

Slide 55

Slide 55 text

ORDER BY とすると、内部的に6バイトの「暗黙の行ID」が作られてその通りに並ぶ 0200220 001 020 200 \0 \0 001 o n e 003 \0 \0 \0 030 \0 ! <-- one 0200240 \0 \0 \0 002 302 001 \0 \0 \0 J h N 201 \0 \0 \0 0200260 215 001 037 200 \0 \0 002 t w o 005 \0 \0 \0 377 <-- two 0200300 257 \0 \0 \0 002 302 002 \0 \0 \0 J h N 201 \0 \0 0200320 \0 215 001 . 200 \0 \0 003 t h r e e \0 \0 \0 <-- three 54/77

Slide 56

Slide 56 text

ORDER BY ORDER BY val DESC にしてみる mysql80 8> ALTER TABLE t3 ORDER BY val DESC; Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql80 8> SELECT * FROM t3; -- ORDER BYがないSELECTは行が見つかった順に返す +------+-------+ | num | val | +------+-------+ | 2 | two | | 3 | three | | 1 | one | +------+-------+ 3 rows in set (0.00 sec) 55/77

Slide 57

Slide 57 text

ORDER BY ibdファイルの中身はこうなってる 0200220 \0 \0 200 \0 \0 002 t w o 005 \0 \0 \0 030 \0 # <-- two 0200240 \0 \0 \0 002 302 004 \0 \0 \0 J h Y 200 \0 \0 \0 0200260 \0 \0 \0 200 \0 \0 003 t h r e e 003 \0 \0 \0 <-- three 0200300 377 255 \0 \0 \0 002 302 005 \0 \0 \0 J h Y 200 0200320 \0 \0 \0 \0 \0 \0 200 \0 \0 001 o n e \0 \0 \0 <-- one 56/77

Slide 58

Slide 58 text

ORDER BY 古の時代、「1日1回くらい ALTER TABLE .. ORDER BY updated_at DESC ってやれ ばSELECTに ORDER BY 書かなくても(=ソートのためのコストを払わなくても)それ なりに最新のものが返ってくるんじゃ?」と思って作られたんではなかろうか 当然ながらこんなものを使ってはいけない。 だがこのへんがMySQLっぽくて楽し い MyISAMでできるのは知ってたけどまさかInnoDBにも対応しているとは思わなかった… ‐ 57/77

Slide 59

Slide 59 text

忘れましょ う 58/77

Slide 60

Slide 60 text

制御系のオプション ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY} テーブルコピーまたはfast index creation型(=インプレース)またはINSTANT ADD COLUMN ‐ LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE} 読み書きを許可する(= NONE)、読み取りだけ許可する(= SHARED) ‐ 59/77

Slide 61

Slide 61 text

制御系のオプション おまじない代わりに ALGORITHM = INPLACE, LOCK = NONE を付けておくと「オンラ インでできないまたはテーブルコピーが必要なALTER TABLEはエラーになる」 mysql80 8> ALTER TABLE t1 ADD COLUMN c1 int, ALGORITHM= INPLACE, LOCK= NONE; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql80 8> ALTER TABLE t1 MODIFY c1 bigint, ALGORITHM= INPLACE, LOCK= NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY. mysql80 8> ALTER TABLE t1 ADD FULLTEXT KEY(val), ALGORITHM= INPLACE, LOCK= NONE; ERROR 1846 (0A000): LOCK=NONE is not supported. Reason: Fulltext index creation requires a lock. Try LOCK=SHARED. 60/77

Slide 62

Slide 62 text

ALTER TABLEのツボ ALTER TABLEが「始まった時点のデータを保証する」のと「最後の差分の マージ」はロックで保護する この2か所が詰まりがち ALTER TABLE開始時のロックは「テーブルメタデータロック」 本来時間は短いが「ロックの粒度」はかなりゴツい ‐ シンプルな「接続してクエリー投げて切断」みたいなやつなら大体大丈夫なんだけど、トラン ザクションかけて他所のAPI叩いて戻りを待って…みたいな作りになっていると相性が悪い というか他所のAPIを叩くケース、失敗したように見えて応答が返ってきてないだけの可能性がたくさんあるのでシ ンプルなトランザクションじゃなくて「二度書き」のトランザクションにすると良い ‐ あとは集計バッチとかかな… ‐ 61/77

Slide 63

Slide 63 text

テーブルメタデータロック mysql80 9> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql80 9> SELECT * FROM t1; -- 単なるSELECTは行ロックは取らないが Empty set (0.00 sec) mysql80 10> ALTER TABLE t1 Engine = InnoDB; -- 9のトランザクションに阻まれてロック待ち mysql80 11> SELECT * FROM t1; -- 10のALTER TABLEに阻まれてロック待ち 62/77

Slide 64

Slide 64 text

テーブルメタデータロック mysql80 9> SHOW PROCESSLIST; +----+-----------------+-----------+------+---------+-------+---------------------------- -----+--------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-----------------+-----------+------+---------+-------+---------------------------- -----+--------------------------------+ | 5 | event_scheduler | localhost | NULL | Daemon | 34966 | Waiting on empty queue | NULL | | 9 | root | localhost | d1 | Query | 0 | starting | SHOW PROCESSLIST | | 10 | root | localhost | d1 | Query | 16 | Waiting for table metadata lock | ALTER TABLE t1 Engine = InnoDB | | 11 | root | localhost | d1 | Query | 4 | Waiting for table metadata lock | SELECT * FROM t1 | +----+-----------------+-----------+------+---------+-------+---------------------------- -----+--------------------------------+ 4 rows in set (0.00 sec) 63/77

Slide 65

Slide 65 text

ALTER TABLEのツボ 少なくともALTER TABLE開始時と終了時は innotop とかで監視を innotop/innotop: A realtime terminal-based top-like monitor for MySQL ‐ 万一ブロッキングALTER TABLEになっても早期発見が可能なので ‐ 開始時に詰まったら止められるけど、終了時に詰まっても「止めてはいけない」 (し、だいたいCtrl+Cくらいじゃ「止まらない」) 最後の差分マージはロックで詰まるわけではなくて単に負荷が異様に高くなるだけだけ (InnoDBログにぐわっと書き込むから) ‐ 64/77

Slide 66

Slide 66 text

ALTER TABLEのツボ 「書き込みながらALTER TABLEできる」のはマスターの話で、スレーブはまた別 の制約を受ける レプリケーションスレーブは(原則)同時にそんなに複数の更新を捌かない ‐ ALTER TABLEのリプレイ中はスレーブはどんづまる マスターで1時間かかったALTER TABLEはスレーブでもおそらく1時間かかる ‐ 65/77

Slide 67

Slide 67 text

ALTER TABLEのツボ 「レプリケーションを介してALTER TABLEを流すとどんづまる」 「じゃあレプリケーションを介さずにALTER TABLEすればいいんでは? 」 「えっ」 「えっ」 66/77

Slide 68

Slide 68 text

レプリケーションの基本的な仕組み 67/77

Slide 69

Slide 69 text

レプリケーションの基本的な仕組み 68/77

Slide 70

Slide 70 text

レプリケーションの基本的な仕組み 69/77

Slide 71

Slide 71 text

レプリケーションの基本的な仕組み 70/77

Slide 72

Slide 72 text

レプリケーションの基本的な仕組み 71/77

Slide 73

Slide 73 text

レプリケーションが構成されていない3台の別のサーバーでも 72/77

Slide 74

Slide 74 text

レプリケーションの基本的な仕組み ふしぎなちからではない 本質的には「マスターに適用された更新のSQLをバイナリログで伝達し、スレーブでリプレイ する」 ‐ MySQLには接続単位でバイナリログをOFFにする特権(Super権限が必要)がある バイナリログ経由で二重にALTER TABLEを適用すると名前が衝突して死んじゃうことがあるか ら ‐ 73/77

Slide 75

Slide 75 text

Rolling Schema Upgrade Rollingとはいうけど順番じゃなくて一斉にやっても別に良い とはいえ先にスレーブやって様子を見てからマスターに流すよね ‐ mysql> SET sql_log_bin = OFF; mysql> ALTER TABLE t1 ADD KEY idx_test(val); 74/77

Slide 76

Slide 76 text

Rolling Schema Upgrade カラムの増減でこれをやると往々にして死ぬ これはアプリケーションが吐くSQLがカラムの増減に強く && binlog_format = STATEMENT でなくてはならないから ‐ 後者を設定すると大体別のところで死ぬので、binlog_format= ROWが推奨される世の中 ‐ 上手く使いきれる人はニンジャ ‐ だからこそINSTANT ADD COLUMNが生まれたとも考えられる 75/77

Slide 77

Slide 77 text

まとめ MySQLはサービスを止めなくてもALTER TABLEができるように進化してきた サービス側でそれを享受するにはアプリケーションでのSQLの書き方もだいじ 定石はググれば案外出てくるので上手く使い分ける 76/77

Slide 78

Slide 78 text

Any Questions and/or Suggestions? 77/77