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

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

さて、現実に戻りましょう。AWS RDSはProvisioned I/Oとある通り、EBSボリュームではPCI-Express SSDは使っていないようです。 そのため、 ディスク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](http://dev.mysql.com/doc/mysql-reslimits-excerpt/5.5/en/partitioning-limitations.html) 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()

おわり

SQL力が低いです。とりあえず、外部システムから定期実行させるよりは、 DB自体で行えた方がいいのはそう。 上記の例は1日分しか追加しませんが、てけとーに一週間分追加とかにすれば抜けリスクが減ってよりベターでしょう。