AWS RDS で MySQL の Add Partition を Event_Scheduler で実行する

昨今 の メモリ増大により、MySQL の 性能はI/O と CPU で依存度が高まってきました。 さらに SSD, PCI-Express SSD など Fusion I/O 系の NAND フラッシュの台頭で Disk I/O も場合によっては！という声もちらほら聞こえます。

さて、現実に戻りましょう。 Amazon の cloud service = AWS ですが RDS は Provisioned I/O とある通り、EBS ボリュームでは PCI-Express SSD は使っていないようです。

そのため、 Disk I/O に掛かるような処理 や 大量の DELETE は非常にコストが大きい処理です。 Index を効かせない 億レコード の 全件走査 とか詰みます。正直万単位でもコストが大きいものです。

そこで MySQL でも Partition を使って刈込したり、DELETE を Partition DROP とする訳です。 今回は、 ADD Partition を RDS単独で自動実行するように Event_Schedulerでやってみようというお話です。

Add ADD Partitionなの

クラウドサービスのように 大量のデータが 未来にわたって / 不確定に 追加される状況では 予め Partition RANGEを決める事は危険です。

特に、RANGE Partition されているテーブルで Partitionを追加するには、ADD Partition するわけですが、 ADD は既存パーティションの「後」にしかパーティションの追加ができません。

つまり、以下の状況で20000 と MAXVALUEの間にPartitionを追加しようと思っても ADD Partition が出来ず Reorganize が必要となります。

ALTER TABLE hoge
PARTITION BY RANGE columns(type) (
PARTITION p10000 VALUES LESS THAN (10000),
PARTITION p20000 VALUES LESS THAN (20000),
PARTITION pmax VALUES LESS THAN MAXVALUE
);

なぜ Reorganize を嫌うのかはお分かりになるかと思います。

公式にも書かれていますが、データ量により再配置に時間がかかり、またTableをロックするのですから使う選択肢にすら上がりません。

Chapter 9. Restrictions and Limitations on Partitioning Performance considerations. Some affects of partitioning operations on performance are given in the following list: File system operations. Partitioning and repartitioning operations (such as ALTER TABLE with PARTITION BY ..., REORGANIZE PARTITIONS, or REMOVE PARTITIONING) depend on file system operations for their implementation. This means that the speed of these operations is affected by such factors as file system type and characteristics, disk speed, swap space, file handling efficiency of the operating system, and MySQL server options and variables that relate to file handling. In particular, you should make sure that large_files_support is enabled and that open_files_limit is set properly. For partitioned tables using the MyISAM storage engine, increasing myisam_max_sort_file_size may improve performance; partitioning and repartitioning operations involving InnoDB tables may be made more efficient by enabling innodb_file_per_table. See also Maximum number of partitions. Table locks. The process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending INSERT and UPDATE operations are performed as soon as the partitioning operation has completed. Storage engine. Partitioning operations, queries, and update operations generally tend to be faster with MyISAM tables than with InnoDB or NDB tables. Use of indexes and partition pruning. As with nonpartitioned tables, proper use of indexes can speed up queries on partitioned tables significantly. In addition, designing partitioned tables and statements using these tables to take advantage of partition pruning can improve performance dramatically. See Partition Pruning, for more information. Performance with LOAD DATA. In MySQL 5.5, LOAD DATA uses buffering to improve performance. You should be aware that the buffer uses 130 KB memory per partition to achieve this.

一方の Add Partition は、 未来の空のレコードを入れるための枠を作るだけなので、元のDBがどれほど巨大でも操作は一瞬です。

実操作への影響をほぼカットすることが可能です。 ただし、勿論、データを格納する枠を欠かすことは出来ず、必ずAdd Partition で テーブルデータの格納先を作っておく必要があります。

ADD Partitionのサンプル

ただ SQL でADD Partition を流し込むならこのようなイメージです。

今回は、MySQL 5.5 で日付をキーにしてみましょう。

ALTER TABLE TableName
    ADD PARTITION( 
		 PARTITION p20130823 VALUES LESS THAN (TO_DAYS('2013-08-24 00:00:00')) COMMENT = '2013-08-23'
);

このようにすると、 2013/8/24 以前のデータ を対象に p20130823 というパーティションを作ります。

あとは、纏めてどーんと作るか、毎日作っていくか等です。

RDS の Event Scheduler でAdd Partitionを実行する

なかなか上手くいかなかったのです、どうも Procedure や Event Scheduler では、 Declare して実行というのが難しいようです。 頑張れば出来るのでしょうが、正直そこに頑張りたくないわけで。 (PowerShell で追加は出来ていたのを SQL で実行しようとしていたので......)

最終的には、 CONCAT で文字列を生成し、 SET で @sql 変数に代入、 PREPARE で宣言して実行という方式にしました。

どうも海外の事例を見ても CONCAT でやっている例がほとんどのようで、SQL萎えます。

BEGIN
SET @sql := CONCAT('ALTER TABLE Table_Name', 
	    				' ADD PARTITION(',
	    				' PARTITION p',
							replace(CAST(DATE_ADD(jst_now(),INTERVAL +日数 day) as char(10)),"-",""),
						' VALUES LESS THAN (',
							TO_DAYS(CONCAT(CAST(DATE_ADD(jst_now(),INTERVAL +(日数+1) day) as char(10)),' 00:00:00')),
						')',
						' COMMENT = \'',
							CAST(DATE_ADD(jst_now(),INTERVAL +日数 day) as char(10)),
						'\');'
	);

	PREPARE stmt FROM @sql;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END

面倒になって DATE_FORMAT も使いませんでしたが。

上記の Event Scheduler が実行されると、Add Partitionの SQLが生成、実行されます。 例えばこんな感じです。

ALTER TABLE Table_Name ADD PARTITION( PARTITION p20131014 VALUES LESS THAN (735521) COMMENT = '2013-10-14');

検証は、実行文を外して Procedure として行えばいいでしょう。以下で、生成された SQLが見れます。

BEGIN
SET @sql := CONCAT('ALTER TABLE Table_Name', 
	    				' ADD PARTITION(',
	    				' PARTITION p',
							replace(CAST(DATE_ADD(jst_now(),INTERVAL +日数 day) as char(10)),"-",""),
						' VALUES LESS THAN (',
							TO_DAYS(CONCAT(CAST(DATE_ADD(jst_now(),INTERVAL +(日数+1) day) as char(10)),' 00:00:00')),
						')',
						' COMMENT = \'',
							CAST(DATE_ADD(jst_now(),INTERVAL +日数 day) as char(10)),
						'\');'
	);

SELECT @sql;

END

呼び出しはCALL ですね。

-- 上記のProcedureに付けた名前で呼び出す
CALL Procedure_Name()

おわり

最近はもっぱら PowerShell なヒトだったので、 SQL 書くと辛いです。

SQL 力が低いですね..... とりあえず、外部システムから定期実行させるよりは、 DB 自体で行えた方がいいのはそうかと思います。

上記の例は 1日分しか追加しませんが、てけとーに 一週間分追加とかにすれば抜けが発生するリスクが減ってよりベターでしょう。