オンライン再定義(カラム追加)

更新待ち時間結果

MySQL
pt-online-schema-change -> 8秒
ALGORITHM=INSTANT -> ほぼ無し
ALGORITHM=INPLACE -> 1秒
ALGORITHM=COPY -> 5秒(カラム追加時間)

Oracle
dbms_redefinition -> 0.5秒
alter table -> ほぼ無し

PostgreSQL
pg-online-schema-change -> 8秒
alter table -> ほぼ無し

SQL Server
alter table -> ほぼ無し

• MySQL

(8.0.29)

pt-online-schema-change

https://www.s-style.co.jp/products/percona/toolkit/pt-online-schema-change/

-- pt-online-schema-changeのインストール

yum install -y https://www.percona.com/downloads/percona-toolkit/3.0.2/binary/redhat/7/x86_64/percona-toolkit-3.0.2-1.el7.x86_64.rpm

pt-online-schema-change --version

-- テーブル作成

drop table tab1;
create table tab1(col1 bigint ,col2 bigint ,col3 datetime(6) );
alter table tab1 add constraint tab1pk primary key(col1);
create index ind1 on tab1(col2);

-- 初期データセット
drop procedure proc1;

delimiter //
create procedure proc1(in x bigint)
begin
  declare i bigint;
  set i = 0;
  start transaction;
  while i < x do
    set i = i + 1;
    insert into tab1(col1,col2,col3) values(i,i,current_timestamp(6) );
  end while;
  commit;
end
//
delimiter ;

call proc1(1000000);

-- 継続的にデータ追加と更新
drop procedure proc2;

delimiter //
create procedure proc2()
begin
  declare i bigint;
  set i = (select max(col1) from tab1);
  start transaction;
  while true do
    set i = i + 1;
    insert into tab1(col1,col2,col3) values(i,i,current_timestamp(6) );
    update tab1 set col2 = floor(rand() * 1000000)+1 where col1 = floor(rand() * 1000000)+1;
    commit;
    do sleep(0.01);
    start transaction;
  end while;
  commit;
end
//
delimiter ;

call proc2();

-- ■[1]オンライン再定義

mysql -e "select count(*) from tab1;" test

pt-online-schema-change --execute  --alter "add column col4 varchar(10)" h=localhost,D=test,t=tab1

mysql -e "select count(*) from tab1;" test

No slaves found.  See --recursion-method if host mmm066 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`tab1`...
Creating new table...
Created new table test._tab1_new OK.
Altering new table...
Altered `test`.`_tab1_new` OK.
2022-09-04T09:24:03 Creating triggers...
2022-09-04T09:24:03 Created triggers OK.
2022-09-04T09:24:03 Copying approximately 997442 rows...
Copying `test`.`tab1`:  77% 00:08 remain
2022-09-04T09:24:35 Copied rows OK.
2022-09-04T09:24:35 Analyzing new table...
2022-09-04T09:24:35 Swapping tables...
2022-09-04T09:24:35 Swapped original and new tables OK.
2022-09-04T09:24:35 Dropping old table...
2022-09-04T09:24:35 Dropped old table `test`.`_tab1_old` OK.
2022-09-04T09:24:35 Dropping triggers...
2022-09-04T09:24:35 Dropped triggers OK.
Successfully altered `test`.`tab1`.

定義確認

show create table tab1;

更新まち時間確認

select col1,timediff(col3,lag(col3) over(order by col1) ) from tab1
where col1 > 1000001
order by timediff(col3,lag(col3) over(order by col1) ) desc 
limit 10
;

+---------+-----------------------------------------------+
| col1    | timediff(col3,lag(col3) over(order by col1) ) |
+---------+-----------------------------------------------+
| 1000056 | 00:00:08.215585                               |
| 1000055 | 00:00:01.018387                               |
| 1000052 | 00:00:00.785414                               |
| 1000060 | 00:00:00.757824                               |
| 1000053 | 00:00:00.745839                               |
| 1000063 | 00:00:00.737877                               |
| 1000054 | 00:00:00.725609                               |
| 1000057 | 00:00:00.713841                               |
| 1000065 | 00:00:00.711029                               |
| 1000059 | 00:00:00.710176                               |
+---------+-----------------------------------------------+

select * from tab1 where col1 > 1000001 order by col1;

テーブル切り替え時、8秒程度更新まちとなる

-- ■[2]カラム追加(INSTANT)

alter table tab1 add column col4 varchar(10) ,ALGORITHM=INSTANT;

→ (0.58 sec)

更新まち時間確認

select col1,timediff(col3,lag(col3) over(order by col1) ) from tab1
where col1 > 1000001
order by timediff(col3,lag(col3) over(order by col1) ) desc 
limit 10
;

+---------+-----------------------------------------------+
| col1    | timediff(col3,lag(col3) over(order by col1) ) |
+---------+-----------------------------------------------+
| 1000030 | 00:00:00.767813                               |
| 1000034 | 00:00:00.721776                               |
| 1000031 | 00:00:00.719620                               |
| 1000042 | 00:00:00.714988                               |
| 1000032 | 00:00:00.710831                               |
| 1000049 | 00:00:00.708120                               |
| 1000036 | 00:00:00.707198                               |
| 1000046 | 00:00:00.706937                               |
| 1000033 | 00:00:00.706072                               |
| 1000041 | 00:00:00.703376                               |
+---------+-----------------------------------------------+

更新まちほぼなし

-- ■[3]カラム追加(INPLACE)

alter table tab1 add column col4 varchar(10) ,ALGORITHM=INPLACE;

→ (6.41 sec)

更新まち時間確認

select col1,timediff(col3,lag(col3) over(order by col1) ) from tab1
where col1 > 1000001
order by timediff(col3,lag(col3) over(order by col1) ) desc 
limit 10
;

+---------+-----------------------------------------------+
| col1    | timediff(col3,lag(col3) over(order by col1) ) |
+---------+-----------------------------------------------+
| 1000037 | 00:00:01.316871                               |
| 1000038 | 00:00:00.870233                               |
| 1000034 | 00:00:00.743590                               |
| 1000033 | 00:00:00.725118                               |
| 1000035 | 00:00:00.721162                               |
| 1000049 | 00:00:00.708549                               |
| 1000051 | 00:00:00.704501                               |
| 1000040 | 00:00:00.703096                               |
| 1000048 | 00:00:00.703080                               |
| 1000052 | 00:00:00.701528                               |
+---------+-----------------------------------------------+

1秒程度更新まちとなる

-- ■[4]カラム追加(COPY)

alter table tab1 add column col4 varchar(10) ,ALGORITHM=COPY;

→ (4.32 sec)

更新まち時間確認

select col1,timediff(col3,lag(col3) over(order by col1) ) from tab1
where col1 > 1000001
order by timediff(col3,lag(col3) over(order by col1) ) desc 
limit 10
;

+---------+-----------------------------------------------+
| col1    | timediff(col3,lag(col3) over(order by col1) ) |
+---------+-----------------------------------------------+
| 1000025 | 00:00:04.988967                               |
| 1000034 | 00:00:00.718311                               |
| 1000035 | 00:00:00.716203                               |
| 1000029 | 00:00:00.716112                               |
| 1000026 | 00:00:00.708156                               |
| 1000032 | 00:00:00.706651                               |
| 1000030 | 00:00:00.706439                               |
| 1000028 | 00:00:00.705421                               |
| 1000027 | 00:00:00.705292                               |
| 1000031 | 00:00:00.699694                               |
+---------+-----------------------------------------------+

カラム追加中更新まちとなる

• Oracle

(19c)
https://qiita.com/ora_gonsuke777/items/5754c18a6609ec295d8f

-- テーブル作成

drop table tab1 purge;
create table tab1(col1 int ,col2 int ,col3 timestamp);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind1 on tab1(col2);

-- 初期データセット
declare
begin
for i in 1..10000000 loop
  insert into tab1(col1,col2,col3) values(i,i,systimestamp);
end loop;
end;
/

commit;

-- 継続的にデータ追加と更新

declare
i number;
begin
select max(col1) into i from tab1;
while true loop
  i := i+1;
  insert into tab1(col1,col2,col3) values(i,i,systimestamp);
  update tab1 set col2 = floor(dbms_random.value(1, 10000000) ) where col1 = floor(dbms_random.value(1, 10000000) );
  commit;
  dbms_lock.sleep(0.01);
end loop;
end;
/

-- ■[1]オンライン再定義

-- 1. 仮表作成

drop table tab1_new purge;
create table tab1_new(col1 int ,col2 int ,col3 timestamp);
alter table tab1_new add constraint tab1pk_new primary key(col1);
create index ind1_new on tab1_new(col2);

select count(*) from tab1;
select count(*) from tab1_new;

set time on
set timing on

-- 2. オンライン再定義可能かチェック

exec dbms_redefinition.can_redef_table(user, 'TAB1', dbms_redefinition.cons_use_pk);

-- 3. オンライン再定義を開始

exec dbms_redefinition.start_redef_table(user, 'TAB1', 'TAB1_NEW');

→ 経過: 00:00:30.58

-- 4. 仮表にカラム追加

alter table tab1_new add col4 varchar2(10) ;

→ 経過: 00:00:00.03

-- 5. 対象表と仮表の依存オブジェクト関連付け

begin
  dbms_redefinition.register_dependent_object(
      user                         -- uname
    , 'TAB1'                       -- orig_table
    , 'TAB1_NEW'                   -- int_table
    , dbms_redefinition.cons_index -- dep_type
    , user                         -- dep_owner
    , 'TAB1PK'                     -- dep_orig_name
    , 'TAB1PK_NEW'                 -- dep_int_name
  );
end;
/

begin
  dbms_redefinition.register_dependent_object(
      user                              -- uname
    , 'TAB1'                            -- orig_table
    , 'TAB1_NEW'                        -- int_table
    , dbms_redefinition.cons_constraint -- dep_type
    , user                              -- dep_owner
    , 'TAB1PK'                          -- dep_orig_name
    , 'TAB1PK_NEW'                      -- dep_int_name
  );
end;
/

begin
  dbms_redefinition.register_dependent_object(
      user                         -- uname
    , 'TAB1'                       -- orig_table
    , 'TAB1_NEW'                   -- int_table
    , dbms_redefinition.cons_index -- dep_type
    , user                         -- dep_owner
    , 'IND1'                       -- dep_orig_name
    , 'IND1_NEW'                   -- dep_int_name
  );
end;
/

-- 6. オンライン再定義をキャンセル

exec dbms_redefinition.abort_redef_table(user, 'TAB1', 'TAB1_NEW');

→ 経過: 00:00:00.60

-- 7. 対象表と仮表の同期
select count(*) from tab1;
select count(*) from tab1_new;

exec dbms_redefinition.sync_interim_table(user, 'TAB1', 'TAB1_NEW');

→ 経過: 00:00:01.03

-- 8. オンライン再定義を完了
select count(*) from tab1;
select count(*) from tab1_new;

exec dbms_redefinition.finish_redef_table(user, 'TAB1', 'TAB1_NEW');

select count(*) from tab1;
select count(*) from tab1_new;

→ 経過: 00:00:03.63

このプロシージャの実行時は、元の表が一時的にロックされます。

-- 9. 更新まち時間確認

select col1, col3 - lag(col3) over(order by col1)  from tab1
where col1 > 10000001
order by col3 - lag(col3) over(order by col1) desc nulls last
fetch first 10 rows only
;

      COL1 COL3-LAG(COL3)OVER(ORDERBYCOL1)
---------- ---------------------------------------------------------------------------
  10006247 +000000000 00:00:00.517028  -> finish_redef_table実行時の更新まち★
  10001800 +000000000 00:00:00.294029
  10001849 +000000000 00:00:00.244878
  10006324 +000000000 00:00:00.243451
  10001883 +000000000 00:00:00.231692
  10006323 +000000000 00:00:00.215534
  10002692 +000000000 00:00:00.205420
  10002688 +000000000 00:00:00.199196
  10001998 +000000000 00:00:00.196793
  10002686 +000000000 00:00:00.190752

テーブル切り替え時の更新まちは1秒以内

-- 10. 定義確認

select * from user_tables where table_name in ('TAB1','TAB1_NEW');

select * from user_constraints where table_name in ('TAB1','TAB1_NEW');

select * from user_indexes where table_name in ('TAB1','TAB1_NEW');

-- ■[2]カラム追加

alter table tab1 add col4 varchar2(10) ;

→ 経過: 00:00:00.14

更新まち時間確認
select col1, col3 - lag(col3) over(order by col1)  from tab1
where col1 > 10000001
order by col3 - lag(col3) over(order by col1) desc nulls last
fetch first 10 rows only
;

      COL1 COL3-LAG(COL3)OVER(ORDERBYCOL1)
---------- ---------------------------------------------------------------------------
  10001821 +000000000 00:00:00.045169
  10001082 +000000000 00:00:00.032013
  10001513 +000000000 00:00:00.028966
  10001486 +000000000 00:00:00.028494
  10001282 +000000000 00:00:00.028007
  10001503 +000000000 00:00:00.026638
  10001610 +000000000 00:00:00.026011
  10001532 +000000000 00:00:00.025983
  10000519 +000000000 00:00:00.025061
  10001523 +000000000 00:00:00.024440

更新まちほぼなし

• PostgreSQL

(14)
https://github.com/shayonj/pg-osc

pg-osc

Requirements
 PostgreSQL 9.6 and later
 Ruby 2.6 and later
 Database user should have permissions for TRIGGER and/or a SUPERUSER

-- rubyのインストール

dnf update
dnf -y install git bzip2 gcc gcc-c++ openssl-devel readline-devel zlib-devel
dnf -y install epel-release
dnf -y install nodejs
git clone https://github.com/rbenv/rbenv.git /usr/local/rbenv
git clone https://github.com/rbenv/ruby-build.git /usr/local/rbenv/plugins/ruby-build

vim /etc/profile.d/rbenv.sh

export RBENV_ROOT=/usr/local/rbenv
export PATH=${RBENV_ROOT}/bin:$PATH
eval "$(rbenv init --no-rehash -)"

source /etc/profile.d/rbenv.sh

visudo

Defaults env_keep += "RBENV_ROOT"
Defaults secure_path = /sbin:/bin:/usr/sbin:/usr/bin:/usr/local/rbenv/bin:/usr/local/rbenv/shims

rbenv install -l
rbenv install 3.1.2
rbenv global 3.1.2

rbenv rehash
ruby --version

-- pg-oscのインストール

su - postgres

vim Gemfile

source 'https://rubygems.org'
gem 'pg_online_schema_change'

export PATH=$PATH:/usr/pgsql-14/bin
bundle install

bundle info pg_online_schema_change

export PATH=$PATH:/usr/local/rbenv/versions/3.1.2/bin
pg-online-schema-change help perform

-- テーブル作成

drop table tab1;
create table tab1(col1 bigint ,col2 bigint ,col3 timestamp);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind1 on tab1(col2);

-- 初期データセット

insert into tab1(col1,col2,col3)  select g,g,clock_timestamp() from generate_series(1,10000000) g;

-- 継続的にデータ追加と更新

do $$
declare
i bigint;
begin
  select max(col1) into STRICT i from tab1;
  while true loop
    i := i+1;
    insert into tab1(col1,col2,col3) values(i,i,clock_timestamp() );
    update tab1 set col2 = floor(random() * 10000000)+1 where col1 = floor(random() * 10000000)+1;
    commit;
    PERFORM pg_sleep(0.01);
  end loop;
end;
$$;

-- ■[1]オンライン再定義

export PGPASSWORD="postgres"
pg-online-schema-change perform \
--alter-statement 'alter table tab1 add col4 varchar(10)' \
--dbname "test" \
--host "localhost" \
--username "postgres" \
--drop

{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":40,"time":"2022-09-04T11:01:10.898+09:00","v":0,"msg":"DEPRECATED: -w is deprecated. Please pass PGPASSWORD environment variable instead.","version":"0.7.1"}
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:01:12.645+09:00","v":0,"msg":"Setting up audit table","audit_table":"pgosc_at_tab1_570ebd","version":"0.7.1"}
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:01:13.849+09:00","v":0,"msg":"Setting up triggers","version":"0.7.1"}
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
WARNING:  there is no transaction in progress
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:01:13.864+09:00","v":0,"msg":"Setting up shadow table","shadow_table":"pgosc_st_tab1_570ebd","version":"0.7.1"}
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:01:14.032+09:00","v":0,"msg":"Running alter statement on shadow table","shadow_table":"pgosc_st_tab1_570ebd","parent_table":"tab1","version":"0.7.1"}
WARNING:  there is already a transaction in progress
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:01:14.033+09:00","v":0,"msg":"Clearing contents of audit table before copy..","shadow_table":"pgosc_st_tab1_570ebd","parent_table":"tab1","version":"0.7.1"}
WARNING:  there is already a transaction in progress
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:01:14.033+09:00","v":0,"msg":"Copying contents..","shadow_table":"pgosc_st_tab1_570ebd","parent_table":"tab1","version":"0.7.1"}
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
WARNING:  there is no transaction in progress
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:02:29.510+09:00","v":0,"msg":"Performing ANALYZE!","version":"0.7.1"}
INFO:  analyzing "public.tab1"
INFO:  "tab1": scanned 30000 of 63696 pages, containing 4709845 live rows and 36 dead rows; 30000 rows in sample, 9999943 estimated total rows
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:02:31.154+09:00","v":0,"msg":"Replaying rows, count: 105","version":"0.7.1"}
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:02:32.990+09:00","v":0,"msg":"Remaining rows below delta count, proceeding towards swap","version":"0.7.1"}
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:02:32.990+09:00","v":0,"msg":"Performing swap!","version":"0.7.1"}
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:02:33.770+09:00","v":0,"msg":"Replaying rows, count: 4","version":"0.7.1"}
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
WARNING:  there is already a transaction in progress
WARNING:  there is no transaction in progress
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:02:33.777+09:00","v":0,"msg":"Performing ANALYZE!","version":"0.7.1"}
INFO:  analyzing "public.tab1"
INFO:  "tab1": scanned 30000 of 63696 pages, containing 4709840 live rows and 23 dead rows; 30000 rows in sample, 9999932 estimated total rows
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:02:39.754+09:00","v":0,"msg":"Validating constraints!","version":"0.7.1"}
{"name":"pg-online-schema-change","hostname":"mmm070","pid":3995,"level":30,"time":"2022-09-04T11:02:40.124+09:00","v":0,"msg":"All tasks successfully completed","version":"0.7.1"}

更新まち時間確認
select col1, col3 - lag(col3) over(order by col1)  from tab1
where col1 > 10000001
order by col3 - lag(col3) over(order by col1) desc nulls last
limit 10
;

   col1   |    ?column?
----------+-----------------
 10000075 | 00:00:07.901357
 10000032 | 00:00:02.002951
 10000042 | 00:00:01.979979
 10000069 | 00:00:01.942204
 10000057 | 00:00:01.933931
 10000043 | 00:00:01.930007
 10000041 | 00:00:01.888046
 10000068 | 00:00:01.886958
 10000049 | 00:00:01.873002
 10000052 | 00:00:01.853888

8秒程度の更新待ち発生

インデックス名が変化するので再設定必要

alter index pgosc_st_tab1_570ebd_pkey rename to tab1pk;
alter index pgosc_st_tab1_570ebd_col2_idx rename to ind1;

-- ■[2]カラム追加

\timing 1

select count(*) from tab1;
alter table tab1 add col4 varchar(10);
select count(*) from tab1;

→ 時間: 673.829 ミリ秒

更新まち時間確認
select col1, col3 - lag(col3) over(order by col1)  from tab1
where col1 > 10000001
order by col3 - lag(col3) over(order by col1) desc nulls last
limit 10
;

   col1   |    ?column?
----------+-----------------
 10000012 | 00:00:01.366635
 10000010 | 00:00:01.126939
 10000011 | 00:00:01.070448
 10000004 | 00:00:00.897543
 10000006 | 00:00:00.841607
 10000007 | 00:00:00.839127
 10000008 | 00:00:00.832665
 10000005 | 00:00:00.826167
 10000009 | 00:00:00.826099
 10000017 | 00:00:00.826012
 

select  col1, col3 - lag(col3) over(order by col1) from tab1
where col1 > 10000001
order by col1;

更新待ちはほぼなし
※カラム追加実施後に0.5秒程度更新待ちの増加がある

• SQL Server

(2019)

-- テーブル作成

drop table tab1;
create table tab1(col1 bigint not null,col2 bigint ,col3 datetime2);
alter table tab1 add constraint tab1pk primary key(col1);
create index ind2 on tab1(col2);

-- 初期データセット

set nocount on
declare @i bigint;
set @i = 1;
begin transaction;
while @i <= 10000000
begin
  insert into tab1(col1,col2,col3) values(@i,@i,getdate() );
  set @i = @i + 1;
end
commit;

-- 継続的にデータ追加と更新

set nocount on
declare @i bigint;
set @i = (select max(col1) from tab1);
begin transaction;
while 1 = 1
begin
  set @i = @i + 1;
  insert into tab1(col1,col2,col3) values(@i,@i,getdate() );
  update tab1 set col2 = floor(rand() * 10000000)+1 where col1 = floor(rand() * 10000000)+1;
  commit;
  waitfor delay '00:00:00.01';
  begin transaction;
end
commit;

-- ■[1]オンライン再定義
調べた限り、オンライン再定義のツールなし

-- ■[2]カラム追加

set statistics time on

alter table tab1 add col4 varchar(10) ;

→ 経過時間 = 1 ミリ秒。

更新まち時間確認
select top 10 
col1,datediff(millisecond, lag(col3) over(order by col1), col3 )  from tab1
where col1 > 10000001
order by datediff(millisecond, lag(col3) over(order by col1), col3 )  desc

10002885    94
10002886    90
10002551    63
10000735    60
10002187    60
10000737    57
10001194    47
10000736    43
10001188    43
10000048    40

更新まちほぼなし