(8.0.27)
LOAD DATA文がダイレクトパスの動作をしている可能性がないか調査
-- テストテーブル作成
drop table tab1;
create table tab1(
col1 int
,col2 char(100)
,col3 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
,cast( (floor(rand() * 1000000000000)+1) as char)
,cast( (floor(rand() * 1000000000000)+1) as char)
);
end while;
commit;
end
//
delimiter ;
call proc1(1000000);
select count(*) from tab1;
analyze table tab1;
drop table tab2;
create table tab2(
col1 int
,col2 char(100)
,col3 char(100)
);
select * into outfile '/var/lib/mysql-files/tab1.csv'
fields terminated by ',' optionally enclosed by '"'
from tab1;
load data infile '/var/lib/mysql-files/tab1.csv' into table tab2
fields terminated by ',' optionally enclosed by '"'
;
-- 確認
show session status like 'Innodb_buffer_pool_read_requests';
show session status like 'Innodb_pages_read';
show session status like 'Innodb_buffer_pool_write_requests';
show session status like 'Innodb_pages_written';
LOAD DATA文実行で「Innodb_buffer_pool_write_requests」の値が増えているので
ダイレクトパスインサートの動作はない模様
(19c)
https://tech-oracle.blog.ss-blog.jp/2020-12-22
-- テストテーブル作成
drop table tab1 purge;
create table tab1(
col1 int
,col2 varchar(100)
,col3 varchar(100)
);
declare
begin
for i in 1..1000000 loop
insert into tab1 values
(i
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
);
end loop;
end;
/
commit;
select count(*) from tab1;
exec dbms_stats.gather_table_stats(user,'TAB1');
drop table tab2 purge;
create table tab2(
col1 int
,col2 varchar(100)
,col3 varchar(100)
);
-- パラレルダイレクトパスリード
select /*+ parallel */ count(*) from tab1;
※パラレル実行の場合は、"_serial_direct_read" を neverにしてもダイレクトパスリードとなる
-- シリアルダイレクトパスリード
alter session set "_serial_direct_read" = always;
show parameter "_serial_direct_read";
select count(*) from tab1;
-- パラレルダイレクトパスインサート
alter session force parallel dml;
insert /*+ append parallel */ into tab2 select * from tab1;
commit;
※パラレル実行はデフォルトでダイレクトパスとなるのでappendヒントはなくてもよい
-- シリアルダイレクトパスインサート
alter session disable parallel dml;
insert /*+ append */ into tab2 select * from tab1;
commit;
※ダイレクトパスインサート実行後、インサートしたテーブル(tab2)に対してselectやupdate等を実行すると下記エラーとなる。
ORA-12838: オブジェクトは、パラレルで変更された後は読取り/変更できません。
-- 確認
select a.name,b.value
from v$statname a inner join v$mystat b
on a.statistic# = b.statistic#
where a.name in( 'physical reads','physical reads direct','physical writes direct','physical writes');
(14)
COPY文がダイレクトパスの動作をしている可能性がないか調査
-- pg_stat_statementsの有効化
vim postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 2000
pg_stat_statements.track = top
pg_stat_statements.save = on
sudo systemctl restart postgresql-14
create extension pg_stat_statements;
-- テストテーブル作成
drop table tab1;
create table tab1(
col1 int
,col2 char(200)
,col3 char(200)
);
insert into tab1 select
g
,(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
from generate_series(1,1000000) g;
select count(*) from tab1;
analyze tab1;
drop table tab2;
create table tab2(
col1 int
,col2 char(200)
,col3 char(200)
);
\COPY tab1 TO '/tmp/tab1.csv' WITH CSV NULL AS '';
\COPY tab2 FROM '/tmp/tab1.csv' WITH CSV NULL AS '';
-- 確認
select pg_stat_statements_reset();
select
queryid, query, calls ,
shared_blks_hit ,
shared_blks_read ,
shared_blks_dirtied ,
shared_blks_written
from pg_stat_statements
where query like '%tab2%'
;
COPY文実行で「shared_blks_dirtied」の値が増えているので
ダイレクトパスインサートの動作はない模様
------
pg_bulkload
https://github.com/ossc-db/pg_bulkload
https://qiita.com/mkyz08/items/afac974a3d19d12729a8
dnf -y install pg_bulkload_14
CREATE EXTENSION pg_bulkload;
vim pg_bulkload.ctl
OUTPUT = tab2
TYPE = CSV
INPUT = /tmp/tab1.csv
LOADER = DIRECT
SKIP = 0
LIMIT = INFINITE
CHECK_CONSTRAINTS = NO
PARSE_ERRORS = 0
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
LOGFILE = ./pg_bulkload.log
PARSE_BADFILE = ./pg_bulkload.bad
DUPLICATE_BADFILE = ./pg_bulkload.dup
TRUNCATE = NO
MULTI_PROCESS = NO
DELIMITER = ","
QUOTE = "\""
ESCAPE = \
/usr/pgsql-14/bin/pg_bulkload -d test -h 127.0.0.1 -U postgres pg_bulkload.ctl
/tmp/tab1.csvのサイズ = 434 MB
COPYコマンドの場合 -> 6.69 sec
pg_bulkloadの場合 -> 3.55 sec
------
(2019)
bcp文がダイレクトパスの動作をしている可能性がないか調査
-- テストテーブル作成
drop table tab1;
create table tab1(
col1 int
,col2 char(100)
,col3 char(100)
);
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
insert into tab1 values(
@i
,cast( (floor(rand() * 1000000000000)+1) as varchar )
,cast( (floor(rand() * 1000000000000)+1) as varchar )
);
set @i = @i + 1;
end
commit;
select count(*) from tab1;
update statistics dbo.tab1;
drop table tab2;
create table tab2(
col1 int
,col2 char(100)
,col3 char(100)
);
bcp test.dbo.tab1 out "C:\tab1.csv" -T -c -t,
bcp test.dbo.tab2 in "C:\tab1.csv" -T -c -t,
-- 確認
select t1.sql_handle ,t2.text ,
t1.last_logical_reads ,
t1.last_physical_reads ,
t1.last_logical_writes
from sys.dm_exec_query_stats t1
cross apply sys.dm_exec_sql_text(t1.sql_handle) t2
where t2.text like '%tab2%'
;
bcp文実行で「last_logical_writes」の値が変化ないため
ダイレクトパスインサートの動作をしている可能性あり