インデックス作成時のredo生成量

Oracle、PostgreSQL、SQL Serverではインデックス作成時、
インデックスと同じぐらいのサイズのREDOログが生成される

• MySQL

(8.0.26)

drop table tab1;
create table tab1(col1 int ,col2 int,col3 int);
drop procedure proc1;

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

call proc1(1000000);
select count(*) from tab1;

create index ind1 on tab1(col2);

インデックスサイズ確認
analyze table tab1;
select  table_name, floor( (index_length)/1024/1024) as imb
from  information_schema.tables  
where table_schema=database()
and table_name = 'tab1'
;

→ 18MB

redo生成量確認

show status where Variable_name like 'Innodb_os_log_written';

→ 0.15MB

redoはインデックスサイズよりかなり小さい

• Oracle

(19c)

drop table tab1 purge;
create table tab1(col1 int primary key,col2 int,col3 int);
declare
begin
for i in 1..1000000 loop
  insert into tab1 values(i,i,i);
end loop;
end;
/

commit;
select count(*) from tab1;

create index ind1 on tab1(col2);
create index ind1 on tab1(col2) nologging;

インデックスサイズ確認
select sum(bytes/1024/1024) mb from user_segments where segment_name = 'IND1';

→ 18MB

redo生成量確認

select b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and a.name = 'redo size'
;

nologgingオプション有の場合

→ 0.3MB

nologgingオプションなしの場合

→ 18MB

redoはインデックスと同じぐらいのサイズ

• PostgreSQL

(14)

drop table tab1;
create      table tab1(col1 int primary key,col2 int,col3 int);
insert into tab1 select g,g,g from generate_series(1,1000000) g;
select count(*) from tab1;

create index ind1 on tab1(col2);

インデックスサイズ確認
SELECT pg_size_pretty(pg_relation_size('ind1') );

→ 21MB

redo生成量確認
select pg_current_wal_lsn();
select pg_wal_lsn_diff('0/29235818', '0/27EBB240')/1024/1024 mb;

UNLOGGEDオプション有でcreate tableした場合
→ 0MB

UNLOGGEDオプションなしでcreate tableした場合
→ 19.5MB

redoはインデックスと同じぐらいのサイズ

• SQL Server

(2019)
drop table tab1;
create table tab1(col1 int,col2 int,col3 int);

begin
set nocount on;
declare @i int;
set @i = 1;
begin transaction;
while (@i <= 1000000)
  begin
    insert into tab1 values(@i,@i,@i);
    set @i = @i + 1;
  end
end
commit;
go

select count(*) from tab1;

begin transaction;
create index ind1 on tab1(col2);

インデックスサイズ確認
exec sp_spaceused 'dbo.tab1';
go

→ 17.5MB

redo生成量確認
select transaction_id, database_id,database_transaction_log_bytes_used
from sys.dm_tran_database_transactions
where transaction_id = CURRENT_TRANSACTION_ID()
;

→ 17.7MB

redoはインデックスと同じぐらいのサイズ