Work Table設定

データの集計処理をしていると、再実行すれば良くてリカバリーは不要なので早く処理したい!! というニーズがあるかと思います。そんな時はワークテーブルを活用したりして、時間を短縮されている方も多いかと思います。ワークテーブルは複雑なSQLになりそうな場合にも、予め複数のテーブルからデータをまとめておいてから処理する中間テーブルとしても非常に便利なので、ニーズによって使い分ければ色々な処理を利用者のニーズに合わせて提供出来る様になるかと思います。

PostgreSQLでのワークテーブル

以下の例では、WALを生成しないテーブルのパフォーマンスを確認しています。データ量やその他ボトルネックによってもレスポンスは変わってきますが、大量にデータを扱い、且つリカバリーが不要なデータを扱う場合はUNLOGGEDを設定したワークテーブルは有用だと考えられます。ここでは、14％程度程レスポンスが改善されています。

POC=# select 272.330/316.061 * 100 as ratio;
          ratio
-------------------------
 86.16374687164819449400
(1 row)

• UNLOGGED TABLE

POC=# ALTER TABLE trains SET UNLOGGED;
ALTER TABLE
Time: 20.156 ms

do $$
begin
for r in 1..50000 loop
insert into public.trains(name) values('sample data for unlogged table');
end loop;
end;
$$;


Time: 272.330 ms

• LOGGED TABLE

POC=# ALTER TABLE trains SET LOGGED;
ALTER TABLE
Time: 10.799 ms

do $$
begin
for r in 1..50000 loop
insert into public.trains(name) values('sample data for unlogged table');
end loop;
end;
$$;

Time: 316.061 ms

1,000,000件のデータを処理した場合は以下の通り。約20％程のパフォーマンス改善が確認出来ます。

PostgreSQLでメモリーにオブジェクトを作成する場合は、以下のtemp tablespaceを利用して、tmpfsにデータが載るように作成してあげれば、データを高速に処理する事が出来ます。事前にtmpfsとしてフォルターをマウントしてあげる必要があります。レプリケーション環境で利用する場合は、事前にマニュアル等を確認してください。

CREATE TABLESPACE worktablespace LOCATION '/mnt/memdisk/data';
SET temp_tablespaces='worktablespace';
CREATE TEMP TABLE worktable1 AS SELECT * FROM trains;

参照：temp_tablespaces (string)

• TEMPORARYまたはTEMPテーブル

PostgeSQLでは、MySQL同様にセッション単位で利用可能なTEMPORARY TABLEを利用する事が出来ます。

POC=# CREATE TEMP TABLE temp_table_on_pg (
POC(# id  bigint not null,
POC(# note varchar(100) not null default 'pg temp table',
POC(# PRIMARY KEY (id)
POC(# ) ;
CREATE TABLE
POC=# \dt temp_table_on_pg
                List of relations
  Schema   |       Name       | Type  |  Owner
-----------+------------------+-------+----------
 pg_temp_4 | temp_table_on_pg | table | postgres
(1 row)

POC=# \d temp_table_on_pg
                             Table "pg_temp_4.temp_table_on_pg"
 Column |          Type          | Collation | Nullable |              Default
--------+------------------------+-----------+----------+------------------------------------
 id     | bigint                 |           | not null |
 note   | character varying(100) |           | not null | 'pg temp table'::character varying
Indexes:
    "temp_table_on_pg_pkey" PRIMARY KEY, btree (id)

POC=# insert into temp_table_on_pg(id,note) values(1,'pg temp table in this session only');
INSERT 0 1
POC=# insert into temp_table_on_pg(id,note) values(2,'pg temp table in this session only');
INSERT 0 1
POC=# insert into temp_table_on_pg(id,note) values(3,'pg temp table in this session only');
INSERT 0 1
POC=# select * from temp_table_on_pg;
 id |                note
----+------------------------------------
  1 | pg temp table in this session only
  2 | pg temp table in this session only
  3 | pg temp table in this session only
(3 rows)

POC=# \x
Expanded display is on.

POC=# SELECT * FROM pg_tables where tablename like 'temp_table%';
-[ RECORD 1 ]-----------------
schemaname  | pg_temp_4
tablename   | temp_table_on_pg
tableowner  | postgres
tablespace  |
hasindexes  | t
hasrules    | f
hastriggers | f
rowsecurity | f

• セッション終了後には消えている事が確認出来ます。

• Transaction単位のTEMPORARY TABLE

POC=# begin transaction;
BEGIN
POC=*# CREATE TEMP TABLE temp_table_on_pg (
id  bigint not null,
note varchar(100) not null default 'pg temp table',
PRIMARY KEY (id)
) ON COMMIT DROP;
CREATE TABLE
POC=*# \dt temp_table_on_pg
                List of relations
  Schema   |       Name       | Type  |  Owner
-----------+------------------+-------+----------
 pg_temp_4 | temp_table_on_pg | table | postgres
(1 row)

POC=*# insert into temp_table_on_pg(id,note) values(1,'pg temp table in this session only');
INSERT 0 1
POC=*# insert into temp_table_on_pg(id,note) values(2,'pg temp table in this session only');
INSERT 0 1
POC=*# insert into temp_table_on_pg(id,note) values(3,'pg temp table in this session only');
INSERT 0 1
POC=*# select * from temp_table_on_pg;
 id |                note
----+------------------------------------
  1 | pg temp table in this session only
  2 | pg temp table in this session only
  3 | pg temp table in this session only
(3 rows)

POC=*# commit;
COMMIT
POC=# select * from temp_table_on_pg;
ERROR:  relation "temp_table_on_pg" does not exist
LINE 1: select * from temp_table_on_pg;
                      ^
POC=# \dt temp_table_on_pg
Did not find any relation named "temp_table_on_pg".
POC=#

詳細は以下のマニュアルを参照下さい

参照：CREATE TABLE

備考: p = permanent table, u = unlogged table, t = temporary table

pg_classからTEMPテーブル、UNLOGGEDテーブルをリストアップする方法

Chapter 51. System Catalogs

POC=# select oid,relname,relowner from pg_class where relpersistence='t';
  oid  |        relname        | relowner
-------+-----------------------+----------
 18966 | temp_table_on_pg      |       10
 18970 | temp_table_on_pg_pkey |       10
(2 rows)

POC=# select oid,relname,relowner from pg_class where relpersistence='u';
 oid | relname | relowner
-----+---------+----------
(0 rows)

POC=# select oid,relname,relowner from pg_class where relpersistence='p' limit 2;
  oid  |     relname      | relowner
-------+------------------+----------
 18595 | sample_data_id   |       10
 18259 | geometry_columns |       10
(2 rows)

POC=#

MySQLでのワークテーブル

MySQLでも同様にバイナリーログをON/OFFにして、パフォーマンスを確認してみました。I/O処理が遅いDocker環境でしたので、バイナリーログをOFFにした方が70％程度高速でした。I/O処理に課題を抱えている環境ではワークテーブルを利用する場合はバイナリーログをOFFにした方が良いですね。勿論、リカバリー等は気にしないでも良い環境での利用です。

• 検証用テーブル

• Loop用処理スクリプト

drop procedure if exists insert_loop;
delimiter //
create procedure insert_loop(in i int)
begin
  declare cnt int default 0;
  while cnt < i do
    set cnt = cnt + 1;
    insert into t_autoincrement values(cnt,'This is test loop for log on off');
  end while;
end
//
delimiter ;

• BinglogがONの場合とOFFの場合のパフォーマンスの差を確認
• 2回ずつ確認しましたが、ログをOFFにすると大体3割程度の時間で実行する事が出来ました。

mysql> SET @@SESSION.sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call insert_loop(10000);
Query OK, 1 row affected (1 min 7.77 sec)

mysql> SET @@SESSION.sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t_autoincrement;
Query OK, 0 rows affected (0.05 sec)

mysql> call insert_loop(10000);
Query OK, 1 row affected (17.94 sec)

メモリーストレージエンジン

メモリーストレージエンジンでテーブルを作成し、且つログをOFFにした環境では1秒未満で処理が終わりました。やはり、I/O処理が弱い環境ではメモリーで全て完結出来ると高速ですね。ワークテーブルなのでこれもオプションの一つとしては有りです。

※　注意点としては、レプリケーション環境で実施する場合は必ず、バイナリーログはセッション単位でOFFにして実施するか、レプリケーションのオプションで対象のメモリーテーブルがレプリケーションに影響を及ぼさない様にするために、IGNOREオプションで予め除外しておく必要があります。自分はメモリーテーブルを利用する場合はオペレーションミスも考慮して、常にIGNOREオプションで指定していました。

CREATE TABLE `t_autoincrement_mem` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'PKとなるIDで連番',
  `note` varchar(100) NOT NULL DEFAULT '-' COMMENT 'メモ書き',
  PRIMARY KEY (`id`)
) ENGINE=MEMORY AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ROW_FORMAT=DYNAMIC;


drop procedure if exists insert_loop_mem;
delimiter //
create procedure insert_loop_mem(in i int)
begin
  declare cnt int default 0;
  while cnt < i do
    set cnt = cnt + 1;
    insert into t_autoincrement_mem values(cnt,'This is test loop for log on off');
  end while;
end
//
delimiter ;

参考値として、バイナリーログをON/OFFにした場合は以下の様な感じになりました。通常のInnoDBを利用する場合と比較して、バイナリーログがONでも32秒で実行出来ているので約1/2の処理時間です。バイナリーログをOFFにした場合は1秒未満で実行出来ています。

Session単位でのワークテーブル

CREATE TEMPORARY TABLE構文でセッション単位で利用可能なワークテーブルを作成する事が出来ます。

こちらの場合もレプリケーションでBINLOG_FORMAT=STATEMENTの場合には注意が必要です。

CREATE TEMPORARY TABLE temp_worktable (
id INT,
note varchar(256)
);

• セッション単位での利用が可能なワークテーブルとして利用可能

mysql> CREATE TEMPORARY TABLE temp_worktable (
    -> id INT,
    -> note varchar(256)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> select * from information_schema.INNODB_TEMP_TABLE_INFO;
+----------+-----------+--------+------------+
| TABLE_ID | NAME      | N_COLS | SPACE      |
+----------+-----------+--------+------------+
|     1093 | #sql1_8_3 |      5 | 4243767290 |
+----------+-----------+--------+------------+
1 row in set (0.01 sec)

mysql> insert into temp_worktable(id,note) values(1,'Temporary table under single session');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp_worktable(id,note) values(2,'Temporary table under single session');
Query OK, 1 row affected (0.00 sec)

mysql> insert into temp_worktable(id,note) values(3,'Temporary table under single session');
Query OK, 1 row affected (0.00 sec)

mysql> select * from temp_worktable;
+------+--------------------------------------+
| id   | note                                 |
+------+--------------------------------------+
|    1 | Temporary table under single session |
|    2 | Temporary table under single session |
|    3 | Temporary table under single session |
+------+--------------------------------------+
3 rows in set (0.00 sec)

mysql> drop temporary table temp_worktable;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TEMPORARY TABLE temp_worktable ( id INT, note varchar(256) );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into temp_worktable(id,note) values(3,'Temporary table under single session');
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye
shinya@DESKTOP-8BDL7KA:~/git/rdbms-docker/mysql$ mysql -h 127.0.0.1 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use POC
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from temp_worktable;
ERROR 1146 (42S02): Table 'POC.temp_worktable' doesn't exist
mysql>

参照：13.1.20.2 CREATE TEMPORARY TABLE ステートメント