(8.0.22)
https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html
UNDO表領域に格納される
UNDO表領域は足りなければ拡張する
UNDO枯渇時は更新側がエラーとなる
UNDO枯渇挙動確認(OSディスク枯渇)
-- 1.UNDO表領域変更
500MBのディスクを追加しマウント
このディスクにundoログを格納する
fdisk /dev/sdb
mkfs.xfs /dev/sdb1
mkdir /var/lib/mysqlundo
mount -t xfs /dev/sdb1 /var/lib/mysqlundo
vim /etc/my.cnf
innodb_max_undo_log_size=1073741824
innodb_undo_directory="/var/lib/mysqlundo"
innodb_undo_log_truncate=OFF
chown mysql:mysql /var/lib/mysqlundo
cd /var/lib/mysql
systemctl stop mysqld
systemctl status mysqld.service
rm -rf undo_00*
systemctl start mysqld.service
systemctl status mysqld.service
select tablespace_name, file_name from information_schema.files
where file_type like 'undo log';
-- 2.テストデータ準備
use test
drop table tab1;
create table tab1(col1 int);
insert into tab1 values(1);
select * from tab1;
-- 3.明示カーソルオープン
drop procedure proc1;
delimiter //
create procedure proc1(in p1 integer)
begin
declare done int default 0;
declare c1 int;
declare cur1 cursor for select count(*) from tab1;
open cur1;
do sleep(p1);
fetch cur1 into c1;
select c1;
close cur1;
end
//
delimiter ;
call proc1(900);
-- 4.別セッションから更新実行
drop procedure proc2;
delimiter //
create procedure proc2()
begin
loop
insert into tab1 select * from tab1;
end loop;
end
//
delimiter ;
call proc2();
※ログに出力されたUNDO枯渇関連のメッセージ
2021-03-11T13:47:05.374177Z 10 [ERROR] [MY-012144] [InnoDB] posix_fallocate(): Failed to preallocate data for file /var/lib/mysqlundo/undo_002, 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-11T13:47:05.524781Z 10 [Warning] [MY-012638] [InnoDB] Retry attempts for writing partial data failed.
2021-03-11T13:47:05.543543Z 10 [ERROR] [MY-012639] [InnoDB] Write to file /var/lib/mysqlundo/undo_002 failed at offset 182452224, 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-11T13:47:05.600081Z 10 [ERROR] [MY-012640] [InnoDB] Error number 28 means 'No space left on device'
2021-03-11T13:47:05.600255Z 10 [Warning] [MY-012145] [InnoDB] Error while writing 4194304 zeroes to /var/lib/mysqlundo/undo_002 starting at offset 180355072
2021-03-11T13:47:05.793983Z 10 [ERROR] [MY-013132] [Server] The table 'tab1' is full!
※クライアント側で出力されたメッセージ
(更新側)
ERROR 3019 (HY000): Undo Log error: No more space left over in undo tablespace for allocating UNDO log pages. Please add new data file to the tablespace or check if filesystem is full or enable auto-extension for the tablespace
(参照側)
正しい結果が返る
(19c)
UNDO表領域に格納される
使用中のUNDOは上書きされない
使用済のUNDOは上書きされる
UNDO枯渇時は、
(1)更新側トランザクション単位が大きく、UNDOが足りない場合は更新側でエラーとなる
(2)使用済のUNDOが上書きされて必要なUNDOが残っていない場合は参照側でエラーとなる
UNDO枯渇挙動確認
-- 1.テストデータ準備
conn test/test@pdb1
drop table tab1 purge;
create table tab1(col1 int primary key);
insert into tab1 values(1);
commit;
select * from tab1;
-- 2.UNDO表領域縮小
conn test/test@pdb1
create undo tablespace undo2 datafile '/u01/app/oracle/oradata/ORCL/pdb1/undo2.dbf' size 1M reuse autoextend off ;
show parameter undo
alter system set undo_tablespace='undo2' scope=spfile;
conn / as sysdba
alter pluggable database pdb1 close;
alter pluggable database pdb1 open;
conn test/test@pdb1
show parameter undo
-- 3.明示カーソルオープン
conn test/test@pdb1
set serveroutput on size 20000
declare
cursor cur1 is select col1 from tab1 where col1 = 1;
c1 cur1%rowtype;
begin
open cur1;
dbms_lock.sleep(60);
fetch cur1 into c1;
dbms_output.put_line(c1.col1);
close cur1;
end;
/
-- 4.別セッションから更新実行
conn test/test@pdb1
declare
begin
while true loop
update tab1 set col1 = col1 * (-1) where ABS(col1) = 1;
commit;
end loop;
end;
/
※アラートログに出力されたUNDO枯渇関連のメッセージ
2021-03-07T18:22:38.910735+09:00
PDB1(3):ORA-01555 caused by SQL statement below (SQL ID: 36awbhv48qwds, Query Duration=60 sec, SCN:
PDB1(3):0x0000000002e009c5
PDB1(3):):
2021-03-07T18:22:38.920433+09:00
PDB1(3):SELECT COL1 FROM TAB1 WHERE COL1 = 1
※クライアント側で出力されたメッセージ
行1でエラーが発生しました。:
ORA-01555: スナップショットが古すぎます: ロールバック・セグメント番号17、名前"_SYSSMU17_348650562$"が小 さすぎます ORA-06512: 行7
<<補足>>
更新側トランザクション単位が大きい場合
クライアント側に下記メッセージが表示される。アラートログにはなにも記録されない
ORA-30036: 8(UNDO表領域'UNDO2'内)でセグメントを拡張できません
-- 5.UNDO表領域もどし
conn test/test@pdb1
alter system set undo_tablespace='undotbs1' scope=spfile;
conn / as sysdba
alter pluggable database pdb1 close abort;
alter pluggable database pdb1 open;
conn test/test@pdb1
show parameter undo
drop tablespace undo2 including contents and datafiles;
(12)
https://www.slideshare.net/iakio/jpug-ezo20110809
http://tyawan080.hatenablog.com/entry/2018/01/28/075031
追記方式のため、
undoのための専用領域はなく、通常のデータファイルと同じ場所に格納される
UNDO枯渇はないが、似た概念としてXID周回問題がある
-- XID周回挙動確認
DBスナップショット取得
drop table tab1;
create table tab1(col1 int primary key);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
select xmin,xmax,col1 from tab1;
select xmin,xmax,oid from pg_class where relname = 'tab1';
select xmin,xmax,attrelid,attname from pg_attribute where attrelid = (select oid from pg_class where relname = 'tab1');
select datname,datfrozenxid,age(datfrozenxid) from pg_database;
select relfrozenxid,relname from pg_class where relname in ('tab1','pg_class','pg_attribute');
vacuum freeze pg_class,pg_attribute;
sudo systemctl stop postgresql-12
sudo systemctl status postgresql-12
/usr/pgsql-12/bin/pg_resetwal -x 0x80000000 $PGDATA
/usr/pgsql-12/bin/postgres --single test
update pg_database set datfrozenxid = '2147483648' where datname in ('test');
update pg_class set relfrozenxid = '2147483648' where relname in ('tab1','pg_class','pg_attribute');
select datname,datfrozenxid,age(datfrozenxid) from pg_database;
select relfrozenxid,relname from pg_class where relname in ('tab1','pg_class','pg_attribute');
シングルユーザモードを終了するには、EOF（通常Control+D）を入力します
sudo systemctl start postgresql-12
sudo systemctl status postgresql-12
テストデータ作成時のxidの値だけトランザクションを進める
for i in `seq 3436`
do
psql -c 'select txid_current();' test
psql -c 'select xmin,xmax,col1 from tab1;' test
done
→現xidとtab1のxidの差が2の31乗(2147483648)以上になるとデータが見えなくなる
確認後、DBはスナップショットからリストア
※ログに出力されたXID周回関連のメッセージ
2021-03-07 14:53:59.249 JST [2178] WARNING: database "template1" must be vacuumed within 477 transactions
2021-03-07 14:53:59.249 JST [2178] HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
2021-03-07 15:12:13.171 JST [4296] ERROR: database is not accepting commands to avoid wraparound data loss in database "template0"
2021-03-07 15:12:13.171 JST [4296] HINT: Stop the postmaster and vacuum that database in single-user mode.
------------------------------------------------------------
-- freeze関連パラメータ
①autovacuum_freeze_max_age
前回凍結年代からどれくらい経過したら、強制vacuumによる凍結を開始するかを指定する
デフォルトは2億トランザクション
②vacuum_freeze_min_age
いつ以前を凍結するかを指定する
デフォルトは5千万トランザクション
③vacuum_freeze_table_age
vacuum時、前回凍結年代からどれくらい経過したら凍結もするかを指定する
デフォルトは1.5億トランザクション
------------------------------------------------------------
楽観ロックモードにした場合、UNDOデータはtempdbデータベースに格納される
使用中のUNDOは上書きされない
使用済のUNDOは上書きされる
UNDO枯渇時は、
(1)更新側トランザクション単位が大きく、UNDOが足りない場合は更新側でエラーとなる
(2)使用済のUNDOが上書きされて必要なUNDOが残っていない場合は参照側でエラーとなる
UNDO枯渇挙動確認
-- 1.READ COMMITTED SNAPSHOT分離レベルへ変更
use test
dbcc useroptions
alter database test set read_committed_snapshot on
dbcc useroptions
-- 2.tempdbのファイル自動拡張無効化
use master
go
alter database tempdb modify file ( name = 'tempdev', filegrowth = 0)
go
-- 3.テストデータ準備
use test
drop table tab1;
create table tab1(col1 int primary key);
insert into tab1 values(1);
select * from tab1;
-- 4.別セッションから更新実行
use test
while 1=1
begin
update tab1 set col1 = col1 * (-1) where ABS(col1) = 1;
end
-- 5.明示カーソルオープン
use test
declare @col1 int;
declare cur1 INSENSITIVE cursor for select col1 from tab1 where col1 = 1;
open cur1;
waitfor delay '00:01:00';
fetch next from cur1 into @col1;
print '@col1=' + cast(@col1 as varchar);
close cur1;
deallocate cur1;
エラーが出るまで繰り返し実行
※ログに出力されたUNDO枯渇関連のメッセージ
2021-03-07 19:00:39.02 spid63 エラー: 1101、重大度: 17、状態: 1。
2021-03-07 19:00:39.02 spid63 Could not allocate a new page for database 'tempdb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
2021-03-07 19:05:19.21 spid53 Version store is full. New version(s) could not be added. A transaction that needs to access the version store may be rolled back. Please refer to BOL on how to configure tempdb for versioning.
※クライアント側で出力されたメッセージ
メッセージ 3958、レベル 16、状態 1、行 5
データベース 'test' のテーブル 'dbo.tab1' のバージョン管理された行にアクセスするときにトランザクションが中止されました。要求されたバージョン管理された行が見つかりませんでした。tempdb の領域が不足している可能性があります。バージョン管理用に tempdb を構成する方法については、BOL を参照してください。
<<補足>>
更新側トランザクション単位が大きい場合
アラートログに下記メッセージが表示される。
2021-03-27 15:57:10.92 spid62 エラー: 1105、重大度: 17、状態: 2。
2021-03-27 15:57:10.92 spid62 Could not allocate space for object '<temporary system object: 422212517232640>' 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、行 4
データベース 'tempdb' にオブジェクト '<temporary system object: 422212517232640>' の領域を割り当てられませんでした。'PRIMARY' ファイル グループがいっぱいです。不要なファイルの削除、ファイル グループ内のオブジェクトの削除、ファイル グループへの新しいファイルの追加、またはファイル グループの既存のファイルの自動拡張の設定のいずれかを行ってディスク領域を作成してください。
-- 6.tempdbのファイル自動拡張戻し
use master
go
alter database tempdb modify file ( name = 'tempdev', filegrowth = 65536kb )
go
-- 7.READ COMMITTED分離レベルへ戻し
use test
dbcc useroptions
alter database test set read_committed_snapshot off;
dbcc useroptions
