MariaDB ストアドプロシージャ：ループ処理の速度を改善してパフォーマンスを向上させる
2024-04-14
MariaDB ストアドプロシージャにおけるループ反復ごとの実行時間計測
NOW() 関数と TIMESTAMP() 関数を利用する
この方法は、ループ開始前に開始時刻を NOW() 関数で取得し、ループ終了後に終了時刻を NOW() 関数で取得し、その差を TIMESTAMP() 関数で計算することで実現します。
-- 開始時刻を取得
SET @stime := NOW();
-- ストアドプロシージャを実行
CALL myproc(@id, @params);
-- 終了時刻を取得
SET @etime := NOW();
-- 実行時間を計算
SET @exectime := TIMESTAMP(@etime) - TIMESTAMP(@stime);
-- 実行時間を保存
INSERT INTO mytable (id, execution_time, procedure)
VALUES (@id, @exectime, "myproc");
PERFORMANCE_SCHEMA テーブルを利用する
この方法は、MariaDB 5.5以降で利用可能な PERFORMANCE_SCHEMA テーブルに格納されている情報を利用することで実現します。
まず、EVENTS_STATS_SUMMARY_BY_DIGEST テーブルからループ開始時点のイベントIDを取得します。
SELECT EVENT_NAME, SUM(COUNT_STAR) AS EVENT_COUNT
FROM PERFORMANCE_SCHEMA.EVENTS_STATS_SUMMARY_BY_DIGEST
WHERE EVENT_NAME LIKE 'stored_procedure:myproc%'
ORDER BY START_TIME DESC
LIMIT 1;
SELECT EVENT_NAME, SUM(COUNT_STAR) AS EVENT_COUNT
FROM PERFORMANCE_SCHEMA.EVENTS_STATS_SUMMARY_BY_DIGEST
WHERE EVENT_NAME LIKE 'stored_procedure:myproc%'
ORDER BY START_TIME DESC
LIMIT 1;
最後に、取得したイベントIDを利用して EVENTS_STATS_BY_DIGEST テーブルから実行時間を計算します。
SELECT
EVENT_NAME,
SUM(TIMER_WAIT + TIMER_NET_WAIT + TIMER_FETCH_WAIT + TIMER_IO_WAIT + TIMER_CPU_WAIT + TIMER_MISC_WAIT) AS TOTAL_TIME
FROM PERFORMANCE_SCHEMA.EVENTS_STATS_BY_DIGEST
WHERE EVENT_NAME LIKE 'stored_procedure:myproc%'
AND EVENT_ID BETWEEN @start_event_id AND @end_event_id
GROUP BY EVENT_NAME;
sys.sysprocesses ビューを利用する
この方法は、Windows Server上のMariaDBで利用可能な sys.sysprocesses ビューに格納されている情報を利用することで実現します。
まず、ループ開始前に sys.sysprocesses ビューからループを実行しているプロセスIDを取得します。
SELECT spid
FROM sys.sysprocesses
WHERE command LIKE 'CALL myproc(@id, @params)'
ESCAPE '\';
SELECT spid, cpu_time
FROM sys.sysprocesses
WHERE spid = @spid;
最後に、取得した実行時間をループ反復数で除算することで、ループ反復ごとの実行時間を計算します。
これらの方法はそれぞれ利点と欠点があります。
sys.sysprocessesビューを利用する方法: Windows Server上のMariaDBでのみ利用可能。
状況に応じて適切な方法を選択してください。
DELIMITER $$
CREATE PROCEDURE myproc(@id INT, @params VARCHAR(255))
BEGIN
-- 開始時刻を取得
SET @stime := NOW();
-- ループ処理
FOR i IN 1..10 DO
-- 処理内容
UPDATE mytable
SET value = value + 1
WHERE id = @id;
END FOR;
-- 終了時刻を取得
SET @etime := NOW();
-- 実行時間を計算
SET @exectime := TIMESTAMP(@etime) - TIMESTAMP(@stime);
-- 実行時間を保存
INSERT INTO mytable (id, execution_time, procedure)
VALUES (@id, @exectime, "myproc");
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE myproc(@id INT, @params VARCHAR(255))
BEGIN
-- イベントIDを取得
SELECT @start_event_id := EVENT_ID
FROM PERFORMANCE_SCHEMA.EVENTS_STATS_SUMMARY_BY_DIGEST
WHERE EVENT_NAME LIKE 'stored_procedure:myproc%'
ORDER BY START_TIME DESC
LIMIT 1;
-- ループ処理
FOR i IN 1..10 DO
-- 処理内容
UPDATE mytable
SET value = value + 1
WHERE id = @id;
END FOR;
-- イベントIDを取得
SELECT @end_event_id := EVENT_ID
FROM PERFORMANCE_SCHEMA.EVENTS_STATS_SUMMARY_BY_DIGEST
WHERE EVENT_NAME LIKE 'stored_procedure:myproc%'
ORDER BY START_TIME DESC
LIMIT 1;
-- 実行時間を計算
SELECT
EVENT_NAME,
SUM(TIMER_WAIT + TIMER_NET_WAIT + TIMER_FETCH_WAIT + TIMER_IO_WAIT + TIMER_CPU_WAIT + TIMER_MISC_WAIT) AS TOTAL_TIME
FROM PERFORMANCE_SCHEMA.EVENTS_STATS_BY_DIGEST
WHERE EVENT_NAME LIKE 'stored_procedure:myproc%'
AND EVENT_ID BETWEEN @start_event_id AND @end_event_id
GROUP BY EVENT_NAME;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE myproc(@id INT, @params VARCHAR(255))
BEGIN
-- spidを取得
DECLARE @spid INT;
SELECT @spid = spid
FROM sys.sysprocesses
WHERE command LIKE 'CALL myproc(@id, @params)'
ESCAPE '\';
-- ループ処理
FOR i IN 1..10 DO
-- 処理内容
UPDATE mytable
SET value = value + 1
WHERE id = @id;
END FOR;
-- 実行時間を取得
DECLARE @cpu_time BIGINT;
SELECT @cpu_time = cpu_time
FROM sys.sysprocesses
WHERE spid = @spid;
-- 実行時間を計算
DECLARE @exectime BIGINT;
SET @exectime = @cpu_time / 1000000;
-- 実行時間を保存
INSERT INTO mytable (id, execution_time, procedure)
VALUES (@id, @exectime, "myproc");
END$$
DELIMITER ;
注意事項
- 上記のサンプルコードはあくまで例であり、状況に合わせて修正する必要があります。
- ループ処理の内容や実行時間は、使用する環境やデータ量によって異なります。
改善点
- サンプルコードにコメントを追加して、コードの意味を分かりやすくしました。
- 各方法の利点と欠点を説明しました。
- MariaDB ストアドプロシージャの性能を向上させる方法は他にもあります。
- 詳細については、MariaDB の公式ドキュメントを参照してください。
MariaDB ストアドプロシージャにおけるループ反復ごとの実行時間計測：その他の方法
EXPLAIN ステートメントは、ストアドプロシージャの実行計画を分析するのに役立ちます。実行計画には、各ループ反復の推定実行時間が含まれています。
EXPLAIN PROCEDURE myproc(@id INT, @params VARCHAR(255));
BENCHMARK ステートメントは、ストアドプロシージャの実行時間を計測するのに役立ちます。
BENCHMARK
SELECT
@loop_no,
@exec_time
FROM (
SELECT
@loop_no := @loop_no + 1,
NOW() AS @start_time
FROM DUAL
WHERE @loop_no <= 10
) AS t1
CROSS JOIN (
CALL myproc(@id, @params)
) AS t2
SELECT
@loop_no,
@exec_time - @start_time AS @exec_time
FROM t1
CROSS JOIN t2;
プロファイラを利用する
MariaDB には、さまざまなプロファイラが用意されています。プロファイラは、ストアドプロシージャの実行時間だけでなく、CPU 使用率やメモリ使用量などの他のパフォーマンス指標も計測できます。
カスタムロギングを利用して、ループ反復ごとに開始時刻と終了時刻を記録することができます。
DELIMITER $$
CREATE PROCEDURE myproc(@id INT, @params VARCHAR(255))
BEGIN
DECLARE @loop_no INT;
DECLARE @start_time DATETIME;
DECLARE @end_time DATETIME;
-- ループ処理
FOR @loop_no IN 1..10 DO
SET @start_time := NOW();
-- 処理内容
UPDATE mytable
SET value = value + 1
WHERE id = @id;
SET @end_time := NOW();
-- 実行時間を記録
INSERT INTO mylog (loop_no, start_time, end_time, procedure)
VALUES (@loop_no, @start_time, @end_time, "myproc");
END FOR;
END$$
DELIMITER ;
- 上記の方法は、いずれもストアドプロシージャの実行速度に影響を与える可能性があります。
- 使用する前に、それぞれの方法の利点と欠点を考慮する必要があります。
MariaDB ストアドプロシージャにおけるループ反復ごとの実行時間計測には、さまざまな方法があります。状況に合わせて適切な方法を選択してください。
mariadb
