読者です 読者をやめる 読者になる 読者になる

CA MOBILE エンジニアブログ

株式会社シーエー・モバイルのエンジニアブログです

【旧ブログ】サービスを停止せずにAlter Table

はじめまして。コマースシステムGのkkkwです。

サービス提供中のテーブルに対してカラム追加をする必要がありました。 諸事情により、サービスを停止せずにメンテナンスを入れたかったので いろいろと調べていったら便利そうなものがあったのでご紹介します。

Percona Toolkit 2.1について

MySQLコマンドラインツールで、 以前はMaatkitと呼ばれていた便利ツール集です。

ドキュメント

いくつかご紹介

pt-mysql-summary

mysqlの情報をサマリーで表示してくれます。 基本的なホスト情報、dataファイルのパス、config情報など 実行結果をwikiなどに集約しておくといろいろと便利そうです。

pt-duplicate-key-checker

各テーブルの重複しているインデックス、外部キーがあるかどうか調査してくれます。

pt-kill

特定の条件にしたがって、mysqlのクエリーをkillしてくれます。 使いどころが難しそうですが、テスト環境とかでは便利そうです。

pt-online-schema-change

今回紹介したいメインのツールです。 alterテーブルをlockなしで実行できます。

サービス提供中のテーブルに対してカラム追加をしたい

サービスの基幹に関わる重要なテーブルでした。 頻繁に読み取り、更新が走り、データ件数も数十万件以上の状態です。 このテーブルに対して、alter をかけると少なくとも数分間の共有ロックがかかります。 それはやりたくないということで、使用したのがpt-online-schema-changeでした。

最初、facebookが出しているOSCを使用しようかとも思ったのですが、 調べていくうちにPercona Toolkitの存在を知り検討しました。

下記引用

The “online schema change” concept was first implemented by Shlomi Noach in his tool oak-online-alter-table, part of >http://code.google.com/p/openarkkit/. Engineers at Facebook then built another version called OnlineSchemaChange.php as> explained by their blog post: http://tinyurl.com/32zeb86. This tool is a hybrid of both approaches, with additional features and functionality not present in either.

OSC、penarkkitのoak-online-alter-table両方の良いとこ取りして改良したよ ということだったので、 pt-online-schema-change を使用しました。

使用方法

実行コマンド

pt-online-schema-change \
--alter "ADD foo INT( 11 ) NULL COMMENT 'テスト用のカラム' AFTER id , ADD INDEX ( foo ) " \
h=hostname,u=username,D=database_name,t=table_name,P=XXXX\
--ask-pass \
--charset utf8 \
--lock-wait-timeout 55 \
--alter-foreign-keys-method auto \
--nocheck-replication-filters \
--execute

オプション解説

alter:実行するalter文です。通常のalter文と同様にalter table以降を記述します。
h,u,D,t:DNS情報です。Dは大文字です。
ask-pass:パスワードを標準入力で入れる場合に指定します。コマンドで指定するときはDNSのところにpオプションでパスワードを指定します。
alter-foreign-keys-method:変更対象のテーブルに外部キー設定がされている場合にどのように解決するかです。
auto,rebuild_constraints,drop_swap,noneを指定できます。
外部参照側のデータ件数が多いとdrop_swapになるようです。
too many rowsのメッセージと共にdrop_swapが使用されました。
nocheck-replication-filters:replicationのfilter設定をチェックするかどうかです。
今回は、スレイブが複数台存在していました。
このオプションを指定しないと
Replication filters are set on these hosts
slave_server_name
replicate_ignore_db = mysql
とエラーが発生しましたが、
対象のDBがmysqlなので今回はチェックなしとしました。
execute:これを指定しないと実行されません。また、--dry-runでテストすることもできます。

ドキュメント

実行結果

Child tables:
`database_name`.`child_table1` (approx. XXXXXXXrows)
`database_name`.`child_table2` (approx. XXXXXXX rows)
`database_name`.`child_table3` (approx. XXXXXXX rows)
`database_name`.`child_table4` (approx. XXXXXXX rows)
`database_name`.`child_table5` (approx. XXXXXXX rows)
`database_name`.`child_table6` (approx. XXXXXXX rows)
Will automatically choose the method to update foreign keys.
Altering `database_name`.`table_name`...
Creating new table...
Created new table database_name._table_name_new OK.
Altering new table...
Altered `database_name`.`_table_name_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately XXXXXXX rows...
Copying `database_name`.`table_name`:  14% 03:03 remain
Copying `database_name`.`table_name`:  21% 03:44 remain
Copying `database_name`.`table_name`:  28% 03:49 remain
Copying `database_name`.`table_name`:  29% 04:45 remain
Copying `database_name`.`table_name`:  38% 04:06 remain
Copying `database_name`.`table_name`:  47% 03:21 remain
Copying `database_name`.`table_name`:  57% 02:37 remain
Copying `database_name`.`table_name`:  66% 01:59 remain
Copying `database_name`.`table_name`:  76% 01:22 remain
Copying `database_name`.`table_name`:  84% 00:54 remain
Copying `database_name`.`table_name`:  99% 00:01 remain
Copied rows OK.
Max rows for the rebuild_constraints method: XXXX
Determining the method to update foreign keys...
`database_name`.`child_table1`: too many rows: XXXXXXX; must use drop_swap
Drop-swapping tables...
Dropped and swapped tables OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `database_name`.`table_name`.

外部参照をしている子テーブルを自動で認識してくれます。 新しいテーブルはtable_name_newと、先頭に「」末尾に「_new」を付与した名前になっています。 データのコピーの進捗を表してくれています。残り時間は目安です。 見て分かるとおり増減します。 実際には5分54秒かかっていました。

残念ながら上記を実行している間、対象テーブルに対して書き込みがなかったので 本番環境で本当に大丈夫かは検証できませんでした。 (テストで試したときは大丈夫でした。)

発行されるSQL

テスト環境にてテストをしていた時のものですが 実際には下記のようなSQLが発行されています。

Connect  user_name@localhost on daatabase_name
Query   set autocommit=1
Query   SELECT @@SQL_MODE
Query   SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO'*/
Query   /*!40101 SET NAMES utf8*/
Query   SET wait_timeout=10000
Query   SELECT @@hostname, @@server_id
Query   SET SESSION innodb_lock_wait_timeout=55
Query   SHOW SESSION VARIABLES LIKE 'innodb_lock_wait_timeout'
Query   SELECT VERSION()
Query   SELECT @@SERVER_ID
Query   SHOW GRANTS FOR CURRENT_USER()
Query   SHOW PROCESSLIST
Query   SHOW SLAVE HOSTS
Query   SHOW TABLES FROM `daatabase_name` LIKE 'table_name'
Query   SHOW TRIGGERS FROM `daatabase_name` LIKE 'table_name'
Query   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
Query   USE `daatabase_name`
Query   SHOW CREATE TABLE `daatabase_name`.`table_name`
Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
Query   EXPLAIN SELECT * FROM `daatabase_name`.`table_name` WHERE 1=1
Query   SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='daatabase_name' AND referenced_table_name='table_name'
Query    /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
Query   USE `daatabase_name`
Query   SHOW CREATE TABLE `daatabase_name`.`table_name`
Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
Query   CREATE TABLE `daatabase_name`.`_table_name_new` () ENGINE=InnoDB DEFAULT CHARSET=utf8 ※1
Query   ALTER TABLE `daatabase_name`.`_table_name_new` ADD test INT( 11 ) NULL COMMENT 'pt-online-schema-changeのテスト'
Query   /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := REPLACE(REPLACE(@@SQL_MODE, 'ANSI_QUOTES', ''), ',,', ','), @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
Query   USE `daatabase_name`
Query   SHOW CREATE TABLE `daatabase_name`.`_table_name_new`
Query   /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
Query   CREATE TRIGGER `pt_osc_daatabase_name_table_name_del` AFTER DELETE ON `daatabase_name`.`table_name` FOR EACH ROW DELETE IGNORE FROM `daatabase_name`.`_table_name_new` WHERE `daatabase_name`.`_table_name_new`.`id` <=> OLD.`id`
Query   CREATE TRIGGER `pt_osc_daatabase_name_table_name_upd` AFTER UPDATE ON `daatabase_name`.`table_name` FOR EACH ROW REPLACE INTO `daatabase_name`.`_table_name_new` (****) VALUES (****)
Query   CREATE TRIGGER `pt_osc_daatabase_name_table_name_ins` AFTER INSERT ON `daatabase_name`.`table_name` FOR EACH ROW REPLACE INTO `daatabase_name`.`_table_name_new` (****) VALUES (****)
Query   EXPLAIN SELECT * FROM `daatabase_name`.`table_name` WHERE 1=1
Query   INSERT LOW_PRIORITY IGNORE INTO `daatabase_name`.`_table_name_new` (****) SELECT **** FROM `daatabase_name`.`table_name` /*pt-online-schema-change 29989 copy table*/
Query   SHOW WARNINGS
Query   SHOW GLOBAL STATUS LIKE &#039;Threads_running&#039;
Query   RENAME TABLE `daatabase_name`.`table_name` TO `daatabase_name`.`_table_name_old`, `daatabase_name`.`_table_name_new` TO `daatabase_name`.`table_name`
Query   DROP TABLE IF EXISTS `daatabase_name`.`_table_name_old`
Query   DROP TRIGGER IF EXISTS `daatabase_name`.`pt_osc_daatabase_name_table_name_del`
Query   DROP TRIGGER IF EXISTS `daatabase_name`.`pt_osc_daatabase_name_table_name_upd`
Query   DROP TRIGGER IF EXISTS `daatabase_name`.`pt_osc_daatabase_name_table_name_ins`
Query   SHOW TABLES FROM `daatabase_name` LIKE &#039;\_table_name\_new&#039;
Quit

※1 table_nameのCREATE文がかいてあります。 ※2 ****部分はそのテーブルに応じたカラム名が列挙されています。

参考サイト

zp8497586rq