(8.0.27)
https://dev.mysql.com/doc/refman/8.0/ja/temporary-files.html
-- 1. テストテーブル準備
drop table tab1;
create table tab1(
col1 char(85)
,col2 char(85)
,col3 char(85)
,col4 char(85)
,col5 char(85)
,col6 char(85)
,col7 char(85)
,col8 char(85)
,col9 char(85));
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(
cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, cast( (floor(rand() * 1000000000000)+1) as char)
, 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;
-- 2. temp使用量確認
別セッションから確認
watch -n 1 lsof +L1
watch -n 1 df
UNIX では、MySQL は一時ファイルを格納するディレクトリのパス名として、TMPDIR 環境変数の値を使用します。
TMPDIR が設定されていない場合、MySQL はシステムのデフォルトを使用します。
通常、これは /tmp、/var/tmp、または /usr/tmp です。
echo $TMPDIR
一時ファイルディレクトリがあるファイルシステムを満杯にしている大きい一時ファイルが表示されません。
(そのような場合は、mysqld に関連付けられている大きいファイルを識別するために、lsof +L1 が役に立つことがあります。)
-- 3. インデックス作成
show session status like '%tmp%';
drop index ind1 on tab1;
create index ind1 on tab1(col1,col2,col3,col4,col5,col6,col7,col8,col9);
show session status like '%tmp%';
Created_tmp_filesが8増える
インデックスサイズ確認
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'
;
→ 0.9G
temp使用量=1.5G
mysqld 9949 mysql 40u REG 253,0 182849536 0 18174495 /tmp/#18174495 (deleted)
mysqld 9949 mysql 41u REG 253,0 202539008 0 18174488 /tmp/#18174488 (deleted)
mysqld 9949 mysql 42u REG 253,0 200531968 0 18174489 /tmp/#18174489 (deleted)
mysqld 9949 mysql 43u REG 253,0 204353536 0 18174490 /tmp/#18174490 (deleted)
mysqld 9949 mysql 44u REG 253,0 184815616 0 18174493 /tmp/#18174493 (deleted)
mysqld 9949 mysql 45u REG 253,0 200384512 0 18223526 /tmp/#18223526 (deleted)
mysqld 9949 mysql 47u REG 253,0 198397952 0 18174494 /tmp/#18174494 (deleted)
mysqld 9949 mysql 48u REG 253,0 202178560 0 18239847 /tmp/#18239847 (deleted)
インデックスサイズと同じくらいのtempファイルを使用している
(19c)
-- 1. テストテーブル準備
drop table tab1 purge;
create table tab1(
col1 char(200)
,col2 char(200)
,col3 char(200)
,col4 char(200)
,col5 char(200)
,col6 char(200)
,col7 char(200)
,col8 char(200)
,col9 char(200));
declare
begin
for i in 1..1000000 loop
insert into tab1 values(
to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
,to_char(floor(dbms_random.value(1, 1000000000001) ) )
);
end loop;
end;
/
commit;
select count(*) from tab1;
select sys_context('userenv','sid') from dual;
-- 2. temp使用量確認
別セッションから確認
bash /u01/app/oracle/product/19.0.0/dbhome_1/sqldeveloper/sqldeveloper/bin/sql test/test@pdb1
select * from v$tempseg_usage where session_addr in ( select saddr from v$session where sid = 264 );
repeat 1000 1
-- 3. インデックス作成
drop index ind1;
create index ind1 on tab1(col1,col2,col3,col4,col5,col6,col7,col8,col9);
インデックスサイズ確認
select sum(bytes/1024/1024) mb from user_segments where segment_name = 'IND1';
→2.6G
temp使用量=BLOCKS x 8 x 1024 /1024/1024/1024 = 1.9G
インデックスサイズと同じくらいのtemp表領域を使用している
(14)
-- 1. テストテーブル準備
drop table tab1;
create table tab1(
col1 char(200)
,col2 char(200)
,col3 char(200)
,col4 char(200)
,col5 char(200)
,col6 char(200)
,col7 char(200)
,col8 char(200)
,col9 char(200));
insert into tab1 select
(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
,(floor(random() * 1000000000000)+1)::varchar
from generate_series(1,1000000);
select count(*) from tab1;
-- 2. temp使用量確認
別セッションから確認
select pg_ls_tmpdir(); \watch 1
cd /var/lib/pgsql/14/data/base/pgsql_tmp
watch -n 1 ls -lhR
-- 3. インデックス作成
drop index ind1;
create index ind1 on tab1(col1,col2,col3,col4,col5,col6,col7,col8,col9);
インデックスサイズ確認
SELECT pg_size_pretty(pg_relation_size('ind1'));
→2.7G
temp使用量=1.8G
※ディレクトリ内のファイルとして作成されるため、pg_ls_tmpdirでは確認できない模様
インデックスサイズと同じくらいのtemp領域を使用している
(2019)
https://docs.microsoft.com/ja-jp/sql/relational-databases/indexes/sort-in-tempdb-option-for-indexes?view=sql-server-ver15
https://docs.microsoft.com/ja-jp/sql/relational-databases/system-dynamic-management-views/sys-dm-db-session-space-usage-transact-sql?view=sql-server-ver15
https://atmarkit.itmedia.co.jp/ait/articles/2108/03/news001.html
-- 1. テストテーブル準備
use test
drop table tab1;
create table tab1(
col1 char(188)
,col2 char(188)
,col3 char(188)
,col4 char(188)
,col5 char(188)
,col6 char(188)
,col7 char(188)
,col8 char(188)
,col9 char(188));
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
insert into tab1
select cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
, cast( (floor(rand() * 1000000000000)+1) as varchar )
set @i = @i + 1;
end
commit;
select count(*) from tab1;
-- 2. temp使用量確認
SORT_IN_TEMPDB を ON に設定した場合は、
並べ替え実行結果を格納するのに十分な空き領域が tempdb に必要です
SORT_IN_TEMPDB を OFF に設定した場合(デフォルト)は、
並べ替え実行結果を格納するのに追加の領域は不要です
select * from sys.dm_db_session_space_usage where session_id = @@spid;
drop index ind1 on tab1;
create index ind1 on tab1(col1,col2,col3,col4,col5,col6,col7,col8,col9)
WITH (SORT_IN_TEMPDB = ON)
;
create index ind1 on tab1(col1,col2,col3,col4,col5,col6,col7,col8,col9)
;
select * from sys.dm_db_session_space_usage where session_id = @@spid;
インデックスサイズ確認
exec sp_spaceused 'dbo.tab1';
→ 2666784 KB = 2.5G
SORT_IN_TEMPDB = ONの場合
temp使用量=internal_objects_alloc_page_count x 8 x 1024 /1024/1024/1024 = 1.9G
インデックスサイズと同じくらいのtemp領域を使用している
SORT_IN_TEMPDB = OFF(デフォルト)の場合
temp使用量=0
