オンライン再定義(インデックス追加)

更新待ち時間結果

MySQL
pt-online-schema-change -> 8秒
ALGORITHM=INPLACE -> ほぼ無し
ALGORITHM=COPY -> 5秒(インデックス作成時間)

Oracle
dbms_redefinition -> 0.5秒
onlineあり -> ほぼ無し
onlineなし -> 8秒(インデックス作成時間)

PostgreSQL
concurrentlyあり -> 3秒
concurrentlyなし -> 6秒(インデックス作成時間)

SQL Server
online=on -> 1秒
online=off -> 5秒(インデックス作成時間)

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);

-- 初期データセット
 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 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 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 index ind1(col2)" 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-03T17:02:30 Creating triggers...
2022-09-03T17:02:30 Created triggers OK.
2022-09-03T17:02:30 Copying approximately 997442 rows...
Copying `test`.`tab1`: 97% 00:00 remain
2022-09-03T17:03:00 Copied rows OK.
2022-09-03T17:03:00 Analyzing new table...
2022-09-03T17:03:00 Swapping tables...
2022-09-03T17:03:00 Swapped original and new tables OK.
2022-09-03T17:03:00 Dropping old table...
2022-09-03T17:03:00 Dropped old table `test`.`_tab1_old` OK.
2022-09-03T17:03:00 Dropping triggers...
2022-09-03T17:03:00 Dropped triggers OK.

定義確認

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) ) |
+---------+-----------------------------------------------+
| 1000055 | 00:00:08.432278 |
| 1000053 | 00:00:00.826683 |
| 1000054 | 00:00:00.801023 |
| 1000052 | 00:00:00.774917 |
| 1000014 | 00:00:00.721277 |
| 1000056 | 00:00:00.717380 |
| 1000049 | 00:00:00.716973 |
| 1000080 | 00:00:00.708578 |
| 1000051 | 00:00:00.704677 |
| 1000048 | 00:00:00.703581 |
+---------+-----------------------------------------------+

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

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

-- ■[2]オンラインインデックス追加

create index ind1 on tab1(col2) ALGORITHM=INPLACE;

→ (3.74 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 1
;

更新まちほぼなし

-- ■[3]オフラインインデックス追加

create index ind1 on tab1(col2) ALGORITHM=COPY;

→ (4.79 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 1
;

インデックス作成中更新まちとなる

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);

-- 初期データセット
declare
begin
for i in 1..10000000 loop
insert into tab1 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 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);

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:22.15

-- 4. 仮表にインデックス追加

create index ind1 on tab1_new(col2);

→ 経過: 00:00:06.38

-- 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;
/

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

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

→ 経過: 00:00:00.39

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

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

→ 経過: 00:00:00.51

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

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

→ 経過: 00:00:03.73

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

-- 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)
---------- ---------------------------------------------------------------------------
10001233 +000000000 00:00:02.801778
10001170 +000000000 00:00:01.068741
10001215 +000000000 00:00:00.774017
10002173 +000000000 00:00:00.476989
10008749 +000000000 00:00:00.467898 -> finish_redef_table実行時の更新まち★
10001139 +000000000 00:00:00.443043
10001259 +000000000 00:00:00.368031
10005137 +000000000 00:00:00.311968
10001280 +000000000 00:00:00.304046
10001116 +000000000 00:00:00.296901

テーブル切り替え時の更新まちは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]オンラインインデックス追加

create index ind1 on tab1(col2) online;

→ 経過: 00:00:08.52

更新まち時間確認
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 1 rows only
;

COL1 COL3-LAG(COL3)OVER(ORDERBYCOL1)
---------- ---------------------------------------------------------------------------
10000410 +000000000 00:00:00.034991

更新まちほぼなし

-- ■[3]オフラインインデックス追加

create index ind1 on tab1(col2);

→ 経過: 00:00:08.25

更新まち時間確認
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 1 rows only
;

COL1 COL3-LAG(COL3)OVER(ORDERBYCOL1)
---------- ---------------------------------------------------------------------------
10007704 +000000000 00:00:08.253500

インデックス作成中更新まちとなる

PostgreSQL

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

pg-osc

pg-osc supports when a column is being added, dropped or renamed with no data loss.

インデックス追加はサポートされていない模様だが、念のため、実施してみる

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);

-- 初期データセット

insert into tab1 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 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 rename column col3 to col3_new' \
--dbname "test" \
--host "localhost" \
--username "postgres" \
--drop

→ OK
ただしインデックス名が変化する

export PGPASSWORD="postgres"
pg-online-schema-change perform \
--alter-statement 'create index ind1 on tab1(col2)' \
--dbname "test" \
--host "localhost" \
--username "postgres" \
--drop

→ エラー
handle_validations': Not a valid ALTER statement: create index ind1 on tab1(col2) (PgOnlineSchemaChange::Error)

-- ■[2]オンラインインデックス追加

\timing 1

create index concurrently ind1 on tab1(col2);

→ 時間: 12550.023 ミリ秒(00:12.550)

更新まち時間確認
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?
----------+-----------------
10000022 | 00:00:02.923435
10000023 | 00:00:02.045207
10000026 | 00:00:01.993001
10000027 | 00:00:01.900054
10000008 | 00:00:01.769081
10000014 | 00:00:01.514806
10000024 | 00:00:01.502795
10000009 | 00:00:01.444517
10000025 | 00:00:01.306995
10000028 | 00:00:01.222762

3秒程度の更新まちとなる

concurrentlyを付与してもそれなりのまちが発生する

-- ■[3]オフラインインデックス追加

\timing 1

create index ind1 on tab1(col2);

→ 時間: 5753.329 ミリ秒(00:05.753)

更新まち時間確認
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?
----------+-----------------
10000005 | 00:00:06.484222
10000011 | 00:00:00.921378
10000004 | 00:00:00.915249
10000006 | 00:00:00.913362
10000003 | 00:00:00.899362
10000012 | 00:00:00.895983
10000008 | 00:00:00.893974
10000009 | 00:00:00.893885
10000010 | 00:00:00.893478
10000013 | 00:00:00.89159

6秒程度の更新まちとなる

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

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);

-- 初期データセット

set nocount on
declare @i bigint;
set @i = 1;
begin transaction;
while @i <= 10000000
begin
insert into tab1 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 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

create index ind2 on tab1(col2) with ( online=on );

→ 経過時間 = 12408 ミリ秒

更新まち時間確認
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

10000374 1050
10000462 364
10000373 227
10000381 203
10000505 154
10000419 153
10000392 130
10000418 130
10000522 117
10000460 107

online=on でも1秒程度のまちは発生

-- ■[3]オフラインインデックス追加

set statistics time on

create index ind2 on tab1(col2) with ( online=off );

→ 経過時間 = 4746 ミリ秒

10000459 4736
10000117 37
10000082 30
10000375 30
10000434 30
10000464 30
10000481 30
10000512 30
10000539 30
10000711 30

インデックス作成中更新まちとなる