MySQL ストアドプロシージャでテストデータを流し込む

by

MySQLのストアドプロシージャを書いたことがなかったので、メモを残しておく。

背景

  • 負荷テストを行うために本番相当の大量のデータをデータベースに流し込みたかった。
  • クエリをGitHubでバージョン管理していて、ファイルサイズの制限で単純にCSVファイルをロードする方法は使えなかった。
  • データの流し込みにあまり時間を長くかけたくなかった

やったこと

  • テストデータをINSERTするストアドプロシージャを作成した。
    • 地域ごとに書き込むレコード数を変えたかったので、任意の数のレコードを生成する処理をストアドプロシージャに切り出した
  • 書き込むレコード数が多い時は複数のレコードを一括挿入することでパフォーマンスを向上させた
    • 下の例では5件ずつにしているが、実際には500件ずつにした。
    • クエリの実行時間を24時間から1時間に短縮できた。

必要な権限

  • CREATE ROUTINE
    • ストアドプロシージャの作成に必要
  • EXECUTE
    • ストアドプロシージャの実行に必要
  • ALTER ROUTINE
    • ストアドプロシージャの削除に必要
  • INSERT
    • データの書き込みに必要

SQL

以下のテーブルを例にしてSQLを書いています。

CREATE TABLE IF NOT EXISTS address (
    user_id VARCHAR(255),  
    postal_code CHAR(16),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

n レコード書き込むストアドプロシージャを定義して、それを呼び出してテストデータを書き込むようにしました。

一括挿入では一度に書き込む分のレコードを列挙しないといけないところが微妙な感じです。

# ストアドプロシージャの本体でセミコロンを使うのでデリミタを変更する
DELIMITER //

CREATE PROCEDURE insert_dummy_records(IN postal_code CHAR(16), IN n INTEGER)
BEGIN
    DECLARE i INTEGER DEFAULT 0;
    WHILE i < n DO
        # 更新パフォーマンスを上げるため、複数レコードをまとめて更新する
        IF i + 5 < n THEN
            INSERT INTO address(user_id, postal_code)
            VALUES (UUID(), postal_code),
                   (UUID(), postal_code),
                   (UUID(), postal_code),
                   (UUID(), postal_code),
                   (UUID(), postal_code);
            SET i = i + 5;
        ELSE
            INSERT INTO address(user_id, postal_code) VALUE (UUID(), postal_code);
            SET i = i + 1;
        END IF;
    END WHILE;
END//

# デリミタを戻す
DELIMITER ;

# テストデータの書き込む
CALL insert_dummy_records("103-0000", 200000);
CALL insert_dummy_records("351-0016", 200000);

# ストアドプロシージャを削除する
DROP PROCEDURE insert_dummy_records;

別の方法として、書き込み済みのレコードを使って倍々にレコード数を増やしていく方法もありますが、クエリの重さがレコードを倍にするたびに重くなって、”Lost connection to MySQL server during query”エラーが発生して、必要なレコード数まで書き込めなかったのでやめました。

参考

コメントする