MySQL
mysqldumpの場合、最初にcreate tableするタイミングでcreate indexも出力。
そのためデータのみインポートと定義含めてインポートの差はない
Oracle
定義含めてインポートの場合、ダイレクトパスロードとなるので速い
PostgreSQL
定義含めてインポートの場合、pg_dumpはインデックス作成を最後に出力するので速い
(8.0.27)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 int primary key
,col2 bigint
,col3 datetime
,col4 varchar(100)
,col5 char(100)
);
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
,floor(rand() * 1000000000000)+1
,date_add('2001-01-01', interval floor(365*20*24*3600 * rand()) second)
,substring(md5(rand()), 1, 30)
,substring(md5(rand()), 1, 30)
);
end while;
commit;
end
//
delimiter ;
call proc1(10000000);
select count(*) from tab1;
select * from tab1 order by rand() limit 20;
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
analyze table tab1;
select
table_name
,table_rows
,avg_row_length
,data_length/1024/1024/1024 tableGB
,index_length/1024/1024/1024 indexGB
from information_schema.tables
where table_schema = database()
and table_name = 'tab1'
;
件数: 1千万
データサイズ: 1.7G
インデックスサイズ: 0.8G
mysqldump -uroot -p -t test tab1 > tab1_data.sql
mysqldump -uroot -p test tab1 > tab1_create_and_data.sql
-- 2. インポート実行
-- 2.1 データのみインポートの場合
mysql test -e "truncate table tab1";
time mysql test < tab1_data.sql
real 5m59.430s
user 0m9.433s
sys 0m0.683s
-- 2.2 定義も含めてインポートの場合
mysql test -e "drop table tab1";
time mysql test < tab1_create_and_data.sql
real 6m2.267s
user 0m9.449s
sys 0m0.673s
-- 3. undo使用量確認
while true;do
mysql test -e "select page_type, count(*) from information_schema.innodb_buffer_page
where page_type='UNDO_LOG';select now()"
sleep 5
done > undo.log
-- 3.1 データのみインポートの場合
undo使用量= 85 x 16 x 1024 /1024/1024 = 1.3M
-- 3.2 定義も含めてインポートの場合
undo使用量= 85 x 16 x 1024 /1024/1024 = 0.7M
-- 4. temp使用量確認
cd /tmp
while true;do lsof +L1|grep mysqld ;date;sleep 5;done > temp.log
-- 4.1 データのみインポートの場合
temp使用量=0.7M
-- 4.2 定義も含めてインポートの場合
temp使用量=0.7M
分割してコミットしているため、tempやundoの使用量は小さい
(19c)
自動拡張ONで小さいサイズのUNDO表領域とTEMP表領域を作成し、
インポート後の表領域サイズを確認する
-- 1. テストデータ作成
drop table tab1 purge;
create table tab1(
col1 int primary key
,col2 int
,col3 timestamp
,col4 varchar2(100)
,col5 char(100)
);
declare
begin
for i in 1..10000000 loop
insert into tab1 values
(i
,floor(dbms_random.value(1, 1000000000001) )
,to_date('20010101','yyyymmdd') + floor(dbms_random.value(0, 365*20*24*3600))/24/3600
,substr(standard_hash(dbms_random.value(), 'MD5'),1,30)
,substr(standard_hash(dbms_random.value(), 'MD5'),1,30)
);
end loop;
end;
/
commit;
select count(*) from tab1;
select * from tab1 order by dbms_random.value() fetch first 20 rows only;
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
select sum(bytes/1024/1024/1024) bytes from user_segments where segment_name ='TAB1';
select sum(bytes/1024/1024/1024) bytes from user_segments where segment_name in (select index_name from user_indexes where table_name = 'TAB1');
件数: 1千万
データサイズ: 1.75G
インデックスサイズ: 1.03G
expdp test/test@pdb1 directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.log tables=TAB1 reuse_dumpfiles=yes
-- 2. UNDO表領域とTEMP表領域の設定
conn test/test@pdb1
create undo tablespace undo2 datafile '/u01/app/oracle/oradata/ORCL/pdb1/undo2.dbf' size 1M autoextend on maxsize unlimited ;
create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ORCL/pdb1/temp2.dbf' size 10M autoextend on maxsize unlimited;
show parameter undo
alter system set undo_tablespace='undo2' scope=spfile;
select property_name, property_value
from database_properties
where property_name like '%TABLESPACE%';
alter database default temporary tablespace temp2;
conn / as sysdba
alter pluggable database pdb1 close immediate;
alter pluggable database pdb1 open;
-- 3. インポート実行
conn test/test@pdb1
-- 3.1 データのみインポートの場合
time impdp test/test@pdb1 directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.log tables=TAB1 content=data_only table_exists_action=truncate
real 3m13.244s
user 0m0.022s
sys 0m0.052s
-- 3.2 定義も含めてインポートの場合
time impdp test/test@pdb1 directory=ORA_DIR dumpfile=tab1.dmp logfile=tab1.log tables=TAB1 content=all table_exists_action=replace
real 2m6.253s
user 0m0.014s
sys 0m0.023s
★データのみインポートの場合と比べて60%程度の所要時間
-- 4. UNDO表領域とTEMP表領域のサイズ確認
select FILE_NAME,bytes/1024/1024/1024 from dba_data_files;
select FILE_NAME,bytes/1024/1024/1024 from dba_temp_files;
-- 4.1 データのみインポートの場合
UNDO表領域→2.75G
TEMP表領域→1.6G
-- 4.2 定義も含めてインポートの場合
UNDO表領域→0.002G
TEMP表領域→0.45G
★データのみインポートの場合と比べてUNDO生成量は激減する
ダイレクトパスロードになっているためと思われる
-- 5. UNDO表領域とTEMP表領域の設定もどし
conn test/test@pdb1
show parameter undo
alter system set undo_tablespace='undo' scope=spfile;
select property_name, property_value
from database_properties
where property_name like '%TABLESPACE%';
alter database default temporary tablespace temp;
conn / as sysdba
alter pluggable database pdb1 close immediate;
alter pluggable database pdb1 open;
conn test/test@pdb1
drop tablespace undo2 including contents and datafiles;
drop tablespace temp2 including contents and datafiles;
(14)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 int primary key
,col2 bigint
,col3 timestamp
,col4 varchar(100)
,col5 char(100)
);
start transaction;
insert into tab1 select
g
,floor(random() * 1000000000000)+1
,'2001-01-01'::date + CAST( floor(365*20*24*3600*random()) || 'second' AS interval)
,substring(md5(random()::text), 1, 30)
,substring(md5(random()::text), 1, 30)
from generate_series(1,10000000) g;
commit;
select count(*) from tab1;
\pset pager 0
select * from tab1 order by random() limit 20;
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
select pg_size_pretty(pg_relation_size('tab1'));
select pg_size_pretty(pg_total_relation_size('tab1'));
件数: 1千万
データサイズ: 1.77G
インデックスサイズ: 1.09G
pg_dump -Fp -a -t tab1 test > tab1_data.sql
pg_dump -Fp -t tab1 test > tab1_create_and_data.sql
-- 2. インポート実行
-- 2.1 データのみインポートの場合
psql test -c "truncate table tab1"
time psql test < tab1_data.sql
real 16m8.919s
user 0m1.500s
sys 0m1.128s
-- 2.2 定義も含めてインポートの場合
psql test -c "drop table tab1"
time psql test < tab1_create_and_data.sql
real 1m21.625s
user 0m1.408s
sys 0m0.906s
-- 3. temp使用量確認
別セッションから確認
cd /var/lib/pgsql/14/data/base/pgsql_tmp
while true;do ls -lhR ;date;sleep 5;done > ~postgres/temp.log
-- 3.1 データのみインポートの場合
temp使用量=0
-- 3.2 定義も含めてインポートの場合
temp使用量=422M
(2019)
-- 1. テストデータ作成
drop table tab1;
create table tab1(
col1 int primary key
,col2 bigint
,col3 datetime2
,col4 varchar(100)
,col5 char(100)
);
set nocount on
declare @i int;
set @i = 1;
begin transaction;
while @i <= 10000000
begin
insert into tab1 values(
@i
,floor(rand() * 1000000000000)+1
,dateadd(second,floor(365*20*24*3600 * rand()),'2001-01-01')
,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)
);
set @i = @i + 1;
end
commit;
select count(*) from tab1;
select top 20 * from tab1 order by newid();
create index ind12 on tab1(col2);
create index ind13 on tab1(col3);
create index ind14 on tab1(col4);
exec sp_spaceused 'dbo.tab1';
go
件数: 1千万
データサイズ: 1.56G
インデックスサイズ: 0.76G
bcp test.dbo.tab1 out "C:\a.csv" -T -c -t,
-- 2. トランザクション分離レベルの変更
use test
dbcc useroptions
alter database test set read_committed_snapshot on with rollback after 1 seconds;
dbcc useroptions
-- 3. tempdb圧縮
use tempdb
go
dbcc shrinkdatabase(N'tempdb' )
go
use tempdb
go
select * from sys.dm_db_file_space_usage;
-- 4. インポート実行
sqlcmd -d test -b -Q "truncate table tab1"
bcp test.dbo.tab1 in "C:\a.csv" -T -c -t,
10000000 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計 : 90047 平均 : (111053.12 行/秒)
-- 5. undo使用量確認
notepad get_undo.bat
@echo off
del get_undo.log >NUL 2>&1
:LOOP
sqlcmd -d test -b -Q "select getdate(), count(*) , sum(record_length_first_part_in_bytes)/1024/1024 mb from sys.dm_tran_version_store" >>get_undo.log
timeout 5 >NUL 2>&1
goto :LOOP
exit /b 0
.\get_undo.bat
undo使用量=0
-- 6. temp使用量確認
notepad get_temp.bat
@echo off
del get_temp.log >NUL 2>&1
:LOOP
sqlcmd -d test -b -Q "select getdate(), * from sys.dm_db_session_space_usage where user_objects_alloc_page_count > 0 or internal_objects_alloc_page_count > 0" >>get_temp.log
sqlcmd -d tempdb -b -Q "select getdate(), * from sys.dm_db_file_space_usage" >>get_temp.log
timeout 5 >NUL 2>&1
goto :LOOP
exit /b 0
.\get_temp.bat
temp使用量=internal_objects_alloc_page_count x 8 x 1024 /1024/1024/1024 = 1.6G
「dm_db_session_space_usage」ではtempを使用しているセッションが見えないが、tempdbファイルは拡張する
-- 7.READ COMMITTED分離レベルへ戻し
use test
dbcc useroptions
alter database test set read_committed_snapshot off;
dbcc useroptions
