MySQL 5.6 (file-per-table mode) 부터 undo 영역을 SYSTEM tablespace ( ibdata ) 에서 분리하여
별도의 파일로 생성할 수 있게 되었습니다.
이에대한 내용을 정리 및 테스트합니다.
| parameter | 설명 |
|---|---|
| innodb_undo_tablespaces |
|
| innodb_undo_directory |
|
| innodb_undo_logs |
|
-- ==============================================================================
-- 설정
-- ==============================================================================
1. 이미 설치된 MySQL 에서 undo 를 분리 할 수는 없다. 신규 설치 시 my.cnf 에 설정해야 한다.
$ vi my.cnf
...
innodb_undo_tablespaces=1
innodb_undo_directory=/data/mysql/undo
innodb_undo_logs=2 # default 128
...
2. my.cnf 를 참조하여 DB 설치하고 variable 확인
mysql> show variables like '%undo%'; +-------------------------+-----------------------+ | Variable_name | Value | +-------------------------+-----------------------+ | innodb_undo_directory | /data/mysql/undo | | innodb_undo_logs | 2 | | innodb_undo_tablespaces | 1 | +-------------------------+-----------------------+
3. 위와 같이 설정하면 undo tablespace file 이 생성된다.
ls -al /data/mysql/undo
-rw-rw---- 1 mysql mysql 10485760 Oct 31 18:39 undo001 <---
[주의사항] innodb_undo_logs ( undo segment 에 따른 차이 )
- innodb_undo_logs = 1 로 설정할 경우, 분리된 undo tablespace file 을 사용하지 않는다.
- undo 를 분리하는 parameter 를 설정 하더라도, system tablespace (ibdata) 에도 최소 1개의 undo segment 가 존재하기 때문에 innodb_undo_logs 가 1 일 경우 system tablespace 에 있는 undo 영역이 사용된다.
=> 최소 2 이상으로 설정해야 분리된 undo 영역이 사용된다.
-- ibdata1 = 1GB 로 할당해두고, innodb_undo_logs=1 로 설정할 경우, 큰 테이블 전체 update 수행 시 tablespace full 됨
mysql> update testhong set userid='testkikiundo' , xxx='testkikiundo', colname='testtest33' ; ERROR 1114 (HY000): The table 'testhong' is full -------------------------------------------------------------------------------------- -- mysql.err -------------------------------------------------------------------------------------- InnoDB: Error: Data file(s) ran out of space. Please add another data file or use 'autoextend' for the last data file. 2013-11-04 15:44:11 21803 [ERROR] /usr/local/mysql/bin/mysqld: The table 'testhong' is full
-- ==============================================================================
-- 테스트
-- ==============================================================================
목적 : 분리된 undo tablespace 를 사용할 경우 undo 가 재사용 되는지 확인
- 많은 양의 row 를 가진 table 을 전체 update 한다.
- 3 번의 트랜잭션으로 실행하는데, (innodb_undo_logs 를 2로 설정했기때문에) 2 번째 트랜잭션까지 종료된 후에 undo 영역을 재사용 하는지 확인한다.
- Oracle 의 undo retention 과 같은 개념은 없음..
1. 1000 만 row 의 테이블 생성
mysql> select count(*) from sbtest ;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
mysql> select * from sbtest limit 3 ;
+----+---+---+----------------------------------------------------+
| id | k | c | pad |
+----+---+---+----------------------------------------------------+
| 1 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 2 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
| 3 | 0 | | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |
+----+---+---+----------------------------------------------------+
2. update 쿼리 실행
mysql> start transaction ;
mysql> update sbtest set pad ='skwicwkjsfieocskafaaaaaaaaaaaaaesssssssssssssstttt' ;
mysql> commit ;
3. 쿼리를 실행할 때 마다 undo file 용량 증가 확인
-rw-rw---- 1 mysql mysql 10485760 Nov 04 19:28 undo001 <-- 생성 직후 용량 : 10 MB
-rw-rw---- 1 mysql mysql 905969664 Nov 04 19:37 undo001 <-- 첫 번째 쿼리 이후
-rw-rw---- 1 mysql mysql 1858076672 Nov 04 19:46 undo001 <-- 두 번째 쿼리 이후
-rw-rw---- 1 mysql mysql 2780823552 Nov 04 19:53 undo001 <-- 세 번째 쿼리 이후 ( 재사용안함 )
-rw-rw---- 1 mysql mysql 3703570432 Nov 04 19:59 undo001 <-- 네 번째 쿼리 이후 ( 재사용안함 ) 용량 : 3.7 GB 까지 증가함
-- 쿼리 실행 로그
: 현재 저장된 컬럼값과 다른 값으로 update 한다.
: 실행 시 마다 트랜잭션 명시적으로 시작하였다.
: 실행 시 마다 커넥션을 끊었다가 재접속하여 실행하였다.
( auto commit )
-- (1)
mysql> update sbtest set pad ='skwicwkjsfieocskafaaaaaaaaaaaaaesssssssssssssstttt' ;
Query OK, 10000000 rows affected (3 min 44.35 sec)
Rows matched: 10000000 Changed: 10000000 Warnings: 0
-- (2)
mysql> update sbtest set pad ='qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt';
Query OK, 10000000 rows affected (3 min 45.52 sec)
Rows matched: 10000000 Changed: 10000000 Warnings: 0
-- (3)
mysql> update sbtest set pad ='skwicwkjsfieocskafaaaaaaaaaaaaaesssssssssssssstttt' ;
Query OK, 10000000 rows affected (4 min 14.35 sec)
Rows matched: 10000000 Changed: 10000000 Warnings: 0
4. DB restart 후에는 재사용 하는지 확인
-rw-rw---- 1 mysql mysql 3703570432 Nov 04 19:59 undo001 <-- 위 테스트 후 아무 작업 하지 않고, DB restart 직후
------------------------------------------------------------------------------------
-- 아래와 같은 update 문을 내용을 다르게하여 update ( row 길이는 동일하게 update 함 )
mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)
mysql> update sbtest set pad ='skwicwkjsfieocskafaaaaaaaaaaaaaesssssssssssssstttt' ;
Query OK, 10000000 rows affected (4 min 13.56 sec)
Rows matched: 10000000 Changed: 10000000 Warnings: 0
mysql> commit ;
Query OK, 0 rows affected (0.06 sec)
------------------------------------------------------------------------------------
-rw-rw---- 1 mysql mysql 3703570432 Nov 04 20:10 undo001 <-- update 1번 후에 재사용됨을 확인 ( 용량의 변화 없음 )
-rw-rw---- 1 mysql mysql 3703570432 Nov 04 20:15 undo001 <-- 2 번째 update 후에도 용량 변화 없음
-rw-rw---- 1 mysql mysql 3703570432 Nov 04 20:19 undo001 <-- 3 번째 update 후에도 용량 변화 없음
-rw-rw---- 1 mysql mysql 3703570432 Nov 04 20:29 undo001 <-- 5 번째 update 후에도 용량 변화 없음
-rw-rw---- 1 mysql mysql 4613734400 Nov 04 20:34 undo001 <-- 6 번째 update 쿼리 실행 시 더이상 사용영역이 없으니 file 이 또 커짐
[결론]
- undo 를 재사용하려면 DB restart 해야함
- 지정한 undo segment 개수와 상관없이 startup 되어있는 상태에서는 재사용 하지 않음
- 많은양의 데이터가 한번에 변경되면 undo file size 가 계속해서 증가함 ( limit 은 없음 )
-- ==============================================================================
-- 번외 : 의문이 생겨 해본 테스트
-- ==============================================================================
- innodb logfile 처럼 file 삭제하면 재생성을하나 ? 결과 : 재생성안함. 에러나면서 DB startup 안됨
-- 파일명 변경해둠
$ mv undo001 bak.undo001
-- DB restart 후 err log
[Warning] The syntax 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead.
[Warning] option 'net_buffer_length': unsigned value 16777216 adjusted to 1048576
.....
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Compressed tables use zlib 1.2.3
[Note] InnoDB: Completed initialization of buffer pool
[ERROR] InnoDB: Unable to open undo tablespace '/data/mysql/undo/undo001'.
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: innodb
[ERROR] Aborting
[Note] Binlog end
- 이미 커져버린 undo file 사이즈를 줄이려면 어떻게 해야될까. undo file 을 dd 명령으로 헤더부터 10MB 만 생성하면, DB는 올라오지않을까 ?
- 결과 : DB Open 됨 !! ( 그런데 무슨일이 생길지 몰라.. 이렇게 까지 꼭 해야할지는..의문.. MySQL 에서 권고하는사항이 아닌 나혼자 해본 것)
- 내용추가) 이 상태에서 DB가 비정상 종료될 경우, recovery 동작 시 없는 page (삭제된 undo segment 의 page) 를 찾으면서 DB Shutdown 되므로 사용하면 안됨.
$ mv undo001 bak_undo001
-rw-rw---- 1 mysql mysql 4613734400 Nov 04 20:34 undo001
$ dd if=/data/mysql/undo/bak_undo001 of=/data/mysql/undo/undo001 bs=10485760 count=1
1+0 records in
1+0 records out
10485760 bytes (10 MB) copied, 0.040273 seconds, 260 MB/s
$ ls -al
total 4515852
-rw-rw---- 1 mysql mysql 4613734400 Nov 04 20:35 bak_undo001
-rw-rw-r-- 1 mysql mysql 10485760 Nov 04 20:36 undo001 <-- 초기 생성 시 byte 로 생성해둠
$ ./bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &
[Note] /db/mysql/bin/mysqld: ready for connections.
Version: '5.6.13-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)
InnoDB: Error: trying to access page number 277888 in space 1,
InnoDB: space name /data/mysql/undo/undo001,
InnoDB: which is outside the tablespace bounds.
InnoDB: Byte offset 0, len 16384, i/o type 10.
InnoDB: If you get this error at mysqld startup, please check that
InnoDB: your my.cnf matches the ibdata files that you have in the
InnoDB: MySQL server.
2014-02-14 23:59:54 41986940 InnoDB: Assertion failure in thread 1100507456 in file fil0fil.cc line 5423
InnoDB 의 Undo ( Rollback Segment ) 관리 가이드
https://dev.mysql.com/doc/refman/5.6/en/innodb-multi-versioning.html
( 5.1 문서와 동일하다. 5.1 , 5.6 차이 없음 )
일관된 읽기를 수행하기 위해 트랜잭션을 주기적으로 커밋하라. 그렇지 않으면, InnoDB 는 update undo log 로 부터 데이터를 삭제하지 못해서 rollback segment 가 점점 커지고 tablespace 를 가득채운다.
rollback segment 내에 물리적인 undo log 레코드의 사이즈는 일반적으로 입력되거나 업데이트되는 row 에 비해 작다.
InnoDB multi-version 구조에는, delete 쿼리가 실행되었을 때 row 가 database로부터 물리적으로 즉시 삭제되지 않는다.
InnoDB 는 삭제를 위해 쓰여진 update undo log 레코드를 삭제했을 때 그에 상응하는 row 와 index 레코드를 물리적으로 삭제한다.
이러한 삭제 작업을 purge 라 부르고, 이는 매우 빠르며 보통 삭제를 실행한 SQL문의 순서로 매우 빠르게 수행된다.
만일 당신이 작은 배치에서 테이블에 동일한 비율로 row를 insert 하고 delete 한다면
purge 스레드는 지연처리 이후에 시작되고, 모든 "죽은" row 들 때문에 테이블은 점점 커지게될 수 있고,
이 모든것으로 인해 디스크 바운드가 발생하고, 매우 느려진다.
이러한 경우, innodb_max_purge_lag 변수를 사용하여 새로운 row 연산을 조절하고, purge thread 에 더 많은 리소스를 할당한다.
* innodb_max_purge_lag 는 purge 연산이 지연될때, INSERT, DELETE, UPDATE 연산을 지연시키는 방법을 제어한다.
default 는 0 이며, 아무 지연 없음을 의미한다.
InnoDB 트랜잭션 시스템은 update, delete 연산에 의한 삭제가 표시된 인덱스 레코드를 가지고있는 트랜잭션 리스트를 관리한다.
이 리스트의 길이는 purge_lag 이며, purge_lag 가 innodb_max_purge_lag 를 초과하게 되면
각 DML 연산은 ((purge_lag/innodb_max_purge_lag) * 10 )-5 밀리 초 만큼 지연된다.
이러한 지연 시간은 퍼지 배치가 실행되는 시점에 매 초마다 계산된다.
퍼지가 열을 볼 수 있는 이전의 상수 읽기 뷰 (old consistent read view) 로 인해 퍼지가 실행되지 않으면 연산은 지연되지 않는다.
https://blogs.oracle.com/mysqlinnodb/entry/mysql_5_6_multi_threaded
// update 실행 전 innodb status
show engine innodb status \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2014-02-15 13:19:10 41504940 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 11 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 10 srv_idle
srv_master_thread log flush and writes: 11
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1
OS WAIT ARRAY INFO: signal count 1
Mutex spin waits 1, rounds 30, OS waits 1
RW-shared spins 2, rounds 1, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
Spin rounds per wait: 30.00 mutex, 0.50 RW-shared, 0.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 64261
Purge done for trx's n:o < 63828 undo n:o < 0 state: running but idle
History list length 20
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 5, OS thread handle 0x41504940, query id 4 localhost root init
show engine innodb status
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (write thread)
I/O thread 11 state: waiting for completed aio requests (write thread)
I/O thread 12 state: waiting for completed aio requests (write thread)
I/O thread 13 state: waiting for completed aio requests (write thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
40 OS file reads, 5 OS file writes, 2 OS fsyncs
1.55 reads/s, 16384 avg bytes/read, 0.09 writes/s, 0.09 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 8850461, node heap has 0 buffer(s)
0.00 hash searches/s, 3.91 non-hash searches/s
---
LOG
---
Log sequence number 2659042131
Log flushed up to 2659042131
Pages flushed up to 2659042131
Last checkpoint at 2659042131
0 pending log writes, 0 pending chkp writes
8 log i/o's done, 0.09 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 4395630592; in additional pool allocated 0
Dictionary memory allocated 55491
Buffer pool size 262143
Free buffers 262117
Database pages 26
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 26, created 0, written 1
1.27 reads/s, 0.00 creates/s, 0.09 writes/s
Buffer pool hit rate 933 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 26, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 2961, id 1308719424, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
-- 1번 update 실행 후 undo file
\! ls -al /data1/mydata/undo
total 917516
drwxrwx--- 2 mysql mysql 4096 Feb 15 00:31 .
drwxr-xr-x 3 mysql mysql 4096 Feb 15 00:31 ..
-rw-rw---- 1 mysql mysql 939524096 Feb 15 13:23 undo001
