(8.0.22)
https://dev.mysql.com/doc/refman/8.0/en/innodb-init-startup-configuration.html#innodb-startup-session-temporary-tablespaces
https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
https://yoku0825.blogspot.com/2020/06/mysql.html
-- 格納場所
①innodb_temp_tablespaces_dir デフォルトは/var/lib/mysql/#innodb_temp
②tmpdir デフォルトは/tmp
③innodb_tmpdir デフォルトは/tmp
④innodb_temp_data_file_path デフォルトは/var/lib/mysql
-- 用途
①セッション一時テーブルで使用される
②内部で使用するワークテーブル(tmp_files)で使用される
③online ALTER TABLE で使用される
④ユーザー作成一時テーブルに対する変更のロールバックセグメントが格納されます
-- 枯渇時挙動確認
-- 1.temp表領域変更
500MBのディスクを追加しマウント
このディスクにundoログを格納する
fdisk /dev/sdb
mkfs.xfs /dev/sdb1
mkdir /var/lib/mysqltemp
mount -t xfs /dev/sdb1 /var/lib/mysqltemp
vim /etc/my.cnf
innodb_temp_tablespaces_dir="/var/lib/mysqltemp"
chown mysql:mysql /var/lib/mysqltemp
systemctl restart mysqld.service
systemctl status mysqld.service
select @@innodb_temp_tablespaces_dir;
-- 2.テストデータ準備
use test
drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1);
select * from tab1;
drop procedure proc1;
delimiter //
create procedure proc1(in x int)
begin
declare i int;
set i = 0;
while i < x do
set i = i + 1;
insert into tab1 select * from tab1;
end while;
end
//
delimiter ;
call proc1(23);
select count(*) from tab1;
-- 3.テンポラリテーブルにデータ格納
drop temporary table ttab1;
create temporary table ttab1(col1 int);
drop procedure proc2;
delimiter //
create procedure proc2()
begin
loop
insert into ttab1 select * from tab1;
end loop;
end
//
delimiter ;
call proc2();
※ログに出力されたtemp枯渇関連のメッセージ
2021-03-11T14:01:17.196684Z 8 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file /var/lib/mysqltemp/temp_10.ibt, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Refer to your operating system documentation for operating system error code information.
2021-03-11T14:01:17.244178Z 8 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2021-03-11T14:01:17.244231Z 8 [ERROR] [MY-012639] [InnoDB] Write to file /var/lib/mysqltemp/temp_10.ibt failed at offset 492830720, 1048576 bytes should have been written, only 0 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded.
2021-03-11T14:01:17.261395Z 8 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'
2021-03-11T14:01:17.261462Z 8 [Warning] [MY-012145] [InnoDB] Error while writing 4194304 zeroes to /var/lib/mysqltemp/temp_10.ibt starting at offset 490733568
2021-03-11T14:01:17.273179Z 8 [ERROR] [MY-013132] [Server] The table 'ttab1' is full!
※クライアント側で出力されたメッセージ
ERROR 1114 (HY000): The table 'ttab1' is full
(19c)
-- 格納場所
一時表領域
-- 用途
一時テーブルや内部で使用するワークテーブルで使用
-- 枯渇時挙動確認
-- 1.temp表領域変更
select property_name, property_value
from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ORCL/pdb1/temp2.dbf' size 10M reuse autoextend off ;
alter database default temporary tablespace temp2;
select property_name, property_value
from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
select file_name,bytes/1024/1024,autoextensible from dba_temp_files;
-- 2.テストデータ準備
drop table tab1 purge;
create table tab1(col1 int);
insert into tab1 values(1);
select * from tab1;
declare
begin
for i in 1..23 loop
insert into tab1 select * from tab1;
commit;
end loop;
end;
/
select count(*) from tab1;
-- 3.テンポラリテーブルにデータ格納
drop table ttab1 purge;
create global temporary table ttab1(col1 int) on commit preserve rows;
declare
begin
loop
insert into ttab1 select * from tab1;
commit;
end loop;
end;
/
※アラートログに出力されたtemp枯渇関連のメッセージ
PDB1(3):ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2 [PDB1]
※クライアント側で出力されたメッセージ
行1でエラーが発生しました。:
ORA-01652: 一時セグメントを128(表領域TEMP2)で拡張できません ORA-06512: 行4
-- 4.temp表領域もどし
select property_name, property_value
from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
alter database default temporary tablespace temp;
select property_name, property_value
from database_properties
where property_name='DEFAULT_TEMP_TABLESPACE';
alter pluggable database pdb1 close immediate;
alter pluggable database pdb1 open;
(13)
-- 格納場所
$PGDATA/base/pgsql_tmpにファイル作成
※pg_default以外のテーブル空間が指定されていた場合はテーブル空間ディレクトリ下のpgsql_tmpサブディレクトリ内
-- 用途
メモリ内に収まりきらないデータのソートなどの操作用
-- 枯渇時挙動確認
-- 1.temp表領域変更
500MBのディスクを追加し、base/pgsql_tmpにマウント
fdisk /dev/sdb
mkfs.xfs /dev/sdb1
systemctl stop postgresql-13
mount -t xfs /dev/sdb1 /var/lib/pgsql/13/data/base/pgsql_tmp
chown postgres:postgres /var/lib/pgsql/13/data/base/pgsql_tmp
systemctl start postgresql-13
systemctl status postgresql-13
-- 2.テストデータ準備
drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1);
select * from tab1;
do $$
declare
i int;
begin
i = 0;
while i < 23 loop
i = i + 1;
insert into tab1 select * from tab1;
end loop;
end
$$
;
select count(*) from tab1;
-- 3. Hash JoinのSQLを大量実行
psql -c 'select count(*) from tab1 t1 inner join tab1 t2 on t1.col1 = t2.col1;' test &
※ログに出力されたtemp枯渇関連のメッセージ
2021-03-12 00:38:32.910 JST [2082] LOG: background worker "parallel worker" (PID 2677) exited with exit code 1
※クライアント側で出力されたメッセージ
ERROR: could not write to file "base/pgsql_tmp/pgsql_tmp2676.0.sharedfileset/i313of512.p0.0": No space left on device
(2019)
-- 格納場所
tempdbデータベース
-- 用途
一時テーブルや内部で使用するワークテーブル、行バージョンで使用
-- 枯渇時挙動確認
-- 1.テストデータ準備
use test
drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1);
select * from tab1;
declare @i int;
set @i = 0;
while @i < 23
begin
set @i = @i + 1
insert into tab1 select * from tab1;
end
select count(*) from tab1;
-- 2.tempdbのファイル自動拡張無効化
use master
go
alter database tempdb modify file ( name = 'tempdev', filegrowth = 0)
go
-- 3.テンポラリテーブルにデータ格納
use test
drop table #ttab1;
create table #ttab1(col1 int);
while 1=1
begin
insert into #ttab1 select * from tab1;
end
※ログに出力されたtemp枯渇関連のメッセージ
2021-03-12 00:54:21.42 spid53 エラー: 1105、重大度: 17、状態: 2。
2021-03-12 00:54:21.42 spid53 Could not allocate space for object '<temporary system object: 2089670228258390016>' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
※クライアント側で出力されたメッセージ
メッセージ 1105、レベル 17、状態 2、行 8
データベース 'tempdb' にオブジェクト '<temporary system object: 2089670228258390016>' の領域を割り当てられませんでした。'PRIMARY' ファイル グループがいっぱいです。不要なファイルの削除、ファイル グループ内のオブジェクトの削除、ファイル グループへの新しいファイルの追加、またはファイル グループの既存のファイルの自動拡張の設定のいずれかを行ってディスク領域を作成してください。
-- 4.tempdbのファイル自動拡張戻し
use master
go
alter database tempdb modify file ( name = 'tempdev', filegrowth = 65536kb )
go
