アーカイブログサイズ調査

(8.0.21)

-- テストテーブル作成
drop table tab1;
drop table tab2;

create table tab1(
col1 numeric(10,0),
col2 numeric(10,0),
col3 numeric(10,0),
col4 numeric(10,0),
col5 numeric(10,0),
col6 varchar(30),
col7 varchar(30),
col8 varchar(30),
col9 varchar(30),
col10 varchar(30),
col11 timestamp,
col12 timestamp,
col13 timestamp,
col14 timestamp,
col15 timestamp
);

alter table tab1 add constraint tab1pk primary key(col1);
create table tab2 as select * from tab1 where 1=2;

-- テストデータ投入

drop procedure proc1;

delimiter //
create procedure proc1()
begin
declare i bigint;
set i = 1;
while i <= 1000000 do
insert into tab1 values(
i,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
substring(md5(rand()), 1, 30),
substring(md5(rand()), 1, 30),
substring(md5(rand()), 1, 30),
substring(md5(rand()), 1, 30),
substring(md5(rand()), 1, 30),
date_add('2001-01-01', interval floor(3650 * rand()) day),
date_add('2001-01-01', interval floor(3650 * rand()) day),
date_add('2001-01-01', interval floor(3650 * rand()) day),
date_add('2001-01-01', interval floor(3650 * rand()) day),
date_add('2001-01-01', interval floor(3650 * rand()) day)
);
set i = i + 1;
end while;
end
//

delimiter ;

call proc1();

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

select floor( (data_length)/1024/1024 )
from information_schema.tables
where table_schema= 'test'
and table_name = 'tab1';

analyze table tab2;
select floor( (data_length)/1024/1024 )
from information_schema.tables
where table_schema= 'test'
and table_name = 'tab2';


-- アーカイブログサイズ確認

reset master;

cd /var/lib/mysql
du -sh arc

-- アーカイブログ生成

truncate table tab2;
insert into tab2 select * from tab1;

truncate table tab2;
start transaction;
insert into tab2 select * from tab1;
commit;

 

-- 結果

---- ログフォーマット=ROW、毎回コミットの場合
元データサイズ:233MB
アーカイブログサイズ:194MB

 

---- ログフォーマット=ROW、まとめてコミットの場合
元データサイズ:233MB
アーカイブログサイズ:256MB

アーカイブログサイズは元データとほぼ同じサイズとなる

※ログフォーマット=STATEMENTの場合、アーカイブログサイズはほぼ0

 

(12cR1)

-- テストテーブル作成
drop table tab1 purge;
drop table tab2 purge;

create table tab1(
col1 number(10,0),
col2 number(10,0),
col3 number(10,0),
col4 number(10,0),
col5 number(10,0),
col6 varchar2(30),
col7 varchar2(30),
col8 varchar2(30),
col9 varchar2(30),
col10 varchar2(30),
col11 timestamp,
col12 timestamp,
col13 timestamp,
col14 timestamp,
col15 timestamp
);

create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key(col1) using index ind1;

create table tab2 as select * from tab1 where 1=2;


-- テストデータ投入

declare
begin
for i in 1..1000000 loop
insert into tab1 values(
i,
floor(dbms_random.value(1, 1000000001)),
floor(dbms_random.value(1, 1000000001)),
floor(dbms_random.value(1, 1000000001)),
floor(dbms_random.value(1, 1000000001)),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
substr(standard_hash(dbms_random.value(), 'MD5'),1,30),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650)),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650)),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650)),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650)),
to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 3650))
);
commit;
end loop;
end;
/

select count(*) from tab1;
select count(*) from tab2;
select sum(bytes/1024/1024) MB from user_segments where segment_name ='TAB1';
select sum(bytes/1024/1024) MB from user_segments where segment_name ='TAB2';

 

-- アーカイブログサイズ確認
delete noprompt archivelog all;

cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs
du -sh .


-- アーカイブログ生成

truncate table tab2;
declare
cursor cur1 is select * from tab1;
begin
for c1 in cur1 loop
insert into tab2 values(
c1.col1,
c1.col2,
c1.col3,
c1.col4,
c1.col5,
c1.col6,
c1.col7,
c1.col8,
c1.col9,
c1.col10,
c1.col11,
c1.col12,
c1.col13,
c1.col14,
c1.col15
);
commit;
end loop;
end;
/


truncate table tab2;
declare
cursor cur1 is select * from tab1;
begin
for c1 in cur1 loop
insert into tab2 values(
c1.col1,
c1.col2,
c1.col3,
c1.col4,
c1.col5,
c1.col6,
c1.col7,
c1.col8,
c1.col9,
c1.col10,
c1.col11,
c1.col12,
c1.col13,
c1.col14,
c1.col15
);
end loop;
end;
/
commit;


-- 結果

---- 毎回コミットの場合
元データサイズ:256MB
アーカイブログサイズ:819MB


---- まとめてコミットの場合
元データサイズ:256MB
アーカイブログサイズ:550MB


アーカイブログサイズは元データのおよそ2~3倍となる

 

(11)

-- テストテーブル作成
drop table tab1;
drop table tab2;

create table tab1(
col1 numeric(10,0),
col2 numeric(10,0),
col3 numeric(10,0),
col4 numeric(10,0),
col5 numeric(10,0),
col6 varchar(30),
col7 varchar(30),
col8 varchar(30),
col9 varchar(30),
col10 varchar(30),
col11 timestamp,
col12 timestamp,
col13 timestamp,
col14 timestamp,
col15 timestamp
);

create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key using index ind1;

create table tab2 as select * from tab1 where 1=2;


-- テストデータ投入

do $$
declare
begin
for i in 1..1000000 loop
insert into tab1 values(
i,
floor(random() * 1000000000)+1,
floor(random() * 1000000000)+1,
floor(random() * 1000000000)+1,
floor(random() * 1000000000)+1,
substring(md5(random()::text), 1, 30),
substring(md5(random()::text), 1, 30),
substring(md5(random()::text), 1, 30),
substring(md5(random()::text), 1, 30),
substring(md5(random()::text), 1, 30),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval),
'2001-01-01'::date + CAST( floor(3650 * random()) || 'days' AS interval)
);
end loop;
end
$$
;

select count(*) from tab1;
select count(*) from tab2;
select pg_size_pretty(pg_relation_size('tab1'));
select pg_size_pretty(pg_relation_size('tab2'));


-- アーカイブログサイズ確認

cd /var/lib/pgsql/11/data
rm -rf arc/*
du -sh arc
du -sh pg_wal

-- アーカイブログ生成

truncate table tab2;
insert into tab2 select * from tab1;

truncate table tab2;
start transaction;
insert into tab2 select * from tab1;
commit;


-- 結果

---- 毎回コミットの場合
元データサイズ:260MB
アーカイブログサイズ:289MB


---- まとめてコミットの場合
元データサイズ:260MB
アーカイブログサイズ:401MB

アーカイブログサイズは元データのおよそ1~1.5倍となる

 

(2019)

-- テストテーブル作成
drop table tab1;
drop table tab2;

create table tab1(
col1 numeric(10,0) not null,
col2 numeric(10,0),
col3 numeric(10,0),
col4 numeric(10,0),
col5 numeric(10,0),
col6 varchar(30),
col7 varchar(30),
col8 varchar(30),
col9 varchar(30),
col10 varchar(30),
col11 datetime2,
col12 datetime2,
col13 datetime2,
col14 datetime2,
col15 datetime2
);

create unique index ind1 on tab1(col1);
alter table tab1 add constraint tab1pk primary key(col1);

select * into tab2 from tab1 where 1=2;


-- テストデータ投入


declare @counter int;
set @counter = 1;

while (@counter <= 1000000)
begin
insert into tab1 values (
@counter,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
floor(rand() * 1000000000)+1,
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
substring(master.dbo.fn_varbintohexstr(HASHBYTES('MD5',cast(rand() as varchar))),3,30),
dateadd(day,floor(3650 * rand()),'2001-01-01'),
dateadd(day,floor(3650 * rand()),'2001-01-01'),
dateadd(day,floor(3650 * rand()),'2001-01-01'),
dateadd(day,floor(3650 * rand()),'2001-01-01'),
dateadd(day,floor(3650 * rand()),'2001-01-01')
);
set @counter = @counter + 1;
end


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

exec sp_spaceused 'dbo.tab1';
go

exec sp_spaceused 'dbo.tab2';
go

-- アーカイブログサイズ確認


use test;
go
backup log test
to disk = 'C:\backup\bk01.bak'
with format, medianame = 'bk01', name = 'log backup test';

cd C:\backup
dir

 

 

-- アーカイブログ生成

truncate table tab2;
insert into tab2 select * from tab1;

truncate table tab2;
begin transaction;
insert into tab2 select * from tab1;
commit;

-- 結果

---- 毎回コミットの場合
元データサイズ:252MB
アーカイブログサイズ:360MB

 

---- まとめてコミットの場合
元データサイズ:252MB
アーカイブログサイズ: 360MB


アーカイブログサイズは元データのおよそ1.5倍となる