JJON's StorY

-- =====================================================
-- == OGG 동기화 테이블 추가 
-- == 대상 : SCOTT.STB_PROD_STOCK_OPT
-- =====================================================
-- == 1. supplemental logging (ogg에서)
-- == 2. extract 파라미터 대상 테이블선언
-- == 3. extract 재기동
-- == 4. 테이블 exp/imp scn
-- == 5. replicate 생성
-- == 6. scn base replicat start
-- == 7. replicat 병합
-- =====================================================


-- =====================================================
-- 소스 OGG
-- =====================================================
-- 대상 테이블 : SCOTT.STB_PROD_STOCK_OPT


-- == 1. supplemental logging (ogg에서)
-- 디비 로그인
GGSCI> dblogin UserID oraogg, password oraogg01
GGSCI> add trandata SCOTT.STB_PROD_STOCK_OPT



-- == 2. extract 파라미터 대상 테이블선언
-- dext001 파라미터에 SCOTT.STB_PROD_STOCK_OPT 테이블 추가
GGSCI (testdb02p as oraogg@TEST2) 19> edit param dext001

TABLE SCOTT.STB_PROD_STOCK_OPT;



GGSCI (testdb02p as oraogg@TEST2) 27> send dext001 showtrans

Sending SHOWTRANS request to EXTRACT DEXT001 ...


------------------------------------------------------------
XID:                  0.3114.6.4282796      
Items:                0        
Extract:              DEXT001   
Redo Thread:          2      
Start Time:           2022-01-13:13:10:49  
SCN:                  8795.3898902885 (37778136271205)  
Redo Seq:             150728
Redo RBA:             8026364444          
Status:               Running        


-- ogg extract 빠른 재기동을 위한 체크포인트 생성
GGSCI (testdb02p as oraogg@TEST2) 31> send dext001 br brcheckpoint immediate
GGSCI (testdb02p as oraogg@TEST2) 34> SEND EXTRACT DEXT001, FORCESTOP



-- == 3. extract 재기동
-- ogg extract 시작
GGSCI (testdb02p as oraogg@TEST2) 43> start dext001

GGSCI (testdb02p as oraogg@TEST2) 60> sh tail -f ./dirrpt/DEXT001.rpt

GGSCI (testdb02p as oraogg@TEST2) 63> stats zpmp001 table SCOTT.STB_PROD_STOCK_OPT

stats dext001 table SCOTT.STB_PROD_STOCK_OPT





-- =====================================================
-- expdp/impdp 
-- =====================================================

-- == 4. 테이블 exp/imp scn
소스DB에서 해당 테이블을 scn 포함하여 expdp 수행
타겟DB에 remap_schema, remap_tablespace 사용하여 impdp 수행


-- TEST2 현재 scn 조회
SYS@TEST2> col CURRENT_SCN for 99999999999999999999999
SYS@TEST2> select current_scn from v$database;

             CURRENT_SCN
------------------------
          38106140088422

1 row selected.



-- TEST2 expdp
cd /DBMS/LFMP/BACKUP_PUMP_DIR

vi exp_ogg.par
-- ----------------------------------------------
DIRECTORY=BACKUP_PUMP_DIR
DUMPFILE=expogg.dmp
TABLES=SCOTT.STB_PROD_STOCK_OPT
FLASHBACK_SCN=38106140088422
-- ----------------------------------------------

expdp mon/mon PARFILE=exp_ogg.par

scp ./expogg.dmp oraREPLTEST@192.168.0.123:/DBMS/REPLTEST/dump/



-- REPLTEST impdp
cd /DBMS/REPLTEST/dump

vi imp_ogg.par
-- ----------------------------------------------
DIRECTORY=DIR1029
DUMPFILE=expogg.dmp
LOGFILE=impogg.log
REMAP_SCHEMA=(SCOTT:SCOTTREAL) 
REMAP_TABLESPACE=(TS_TEST_DAT:TS_REAL_DAT ,TS_TEST_IDX:TS_REAL_DAT )
-- ----------------------------------------------

impdp mon/mon PARFILE=imp_ogg.par



-- =====================================================
-- 타겟 OGG
-- =====================================================

-- == 5. replicate 생성
-- 기존 replicate (이름 zrep011) 복제 하여 신규 replicate (이름 ztmp) 생성
retestdt:/DBMS/REPLTEST/ogg19c/dirprm> cp zrep011.prm ztmp.prm

GGSCI (retestdt) 4> add rep ztmp exttrail ./dirdat/zw
REPLICAT added.


-- 신규 replicate (이름 ztmp) 파라미터 수정
GGSCI (retestdt) 6> edit param ztmp

MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT;



GGSCI (retestdt) 34> sh ls -lrt ./dirdat/

total 22524288
-rw-r----- 1 oraREPLTEST dba 1023999912 Apr  3 17:43 zw000006421
-rw-r----- 1 oraREPLTEST dba 1023999687 Apr  3 17:43 zw000006422
-rw-r----- 1 oraREPLTEST dba 1023999977 Apr  3 18:43 zw000006423
-rw-r----- 1 oraREPLTEST dba 1023999820 Apr  3 18:44 zw000006424
-rw-r----- 1 oraREPLTEST dba 1023999944 Apr  3 19:44 zw000006425
-rw-r----- 1 oraREPLTEST dba 1023999839 Apr  3 19:51 zw000006426
-rw-r----- 1 oraREPLTEST dba 1023999759 Apr  3 20:03 zw000006427
-rw-r----- 1 oraREPLTEST dba 1023999979 Apr  3 21:40 zw000006428
-rw-r----- 1 oraREPLTEST dba 1023999760 Apr  3 22:05 zw000006429
-rw-r----- 1 oraREPLTEST dba 1023999941 Apr  4 00:33 zw000006430
-rw-r----- 1 oraREPLTEST dba 1023999979 Apr  4 01:53 zw000006431
-rw-r----- 1 oraREPLTEST dba 1023999866 Apr  4 02:50 zw000006432
-rw-r----- 1 oraREPLTEST dba 1023999646 Apr  4 04:13 zw000006433
-rw-r----- 1 oraREPLTEST dba 1023999758 Apr  4 07:43 zw000006434
-rw-r----- 1 oraREPLTEST dba 1023999824 Apr  4 07:43 zw000006435
-rw-r----- 1 oraREPLTEST dba 1023999767 Apr  4 08:02 zw000006436
-rw-r----- 1 oraREPLTEST dba 1023999924 Apr  4 08:03 zw000006437
-rw-r----- 1 oraREPLTEST dba 1023999962 Apr  4 08:39 zw000006438
-rw-r----- 1 oraREPLTEST dba 1023999749 Apr  4 08:43 zw000006439
-rw-r----- 1 oraREPLTEST dba 1023999985 Apr  4 09:46 zw000006440
-rw-r----- 1 oraREPLTEST dba 1023999864 Apr  4 09:49 zw000006441
-rw-r----- 1 oraREPLTEST dba 1023999722 Apr  4 09:56 zw000006442
-rw-r----- 1 oraREPLTEST dba  407555339 Apr  4 10:34 zw000006443



-- 리스트 중 expdp 시점보다 이전에 생성된 번호 사용
GGSCI (retestdt) 35> alter ztmp extseqno 6439 extrba 0

2022-04-04 10:36:30  INFO    OGG-06594  Replicat ZTMP has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start ZTMP with NOFILTERDUPTRANSACTIONS option.

REPLICAT altered.


-- == 6. scn base replicat start
-- expdp시 사용한 scn값 사용하여 ztmp 시작
GGSCI> start ztmp aftercsn 38106140088422


GGSCI> dblogin UserID oraogg, password oraogg01
GGSCI (retestdt as ORAOGG@REPLTEST) 115> stats ztmp total






-- == 7. replicat 병합

-- =====================================================
-- 소스 OGG
-- =====================================================
-- 소스 전송프로세스 중지
GGSCI (testdb02p as oraogg@TEST2) 69> stop zpmp001

Sending STOP request to EXTRACT ZPMP001 ...
Request processed.


-- =====================================================
-- 타겟 OGG
-- =====================================================
-- 타켓 프로세스 중지
GGSCI (retestdt as ORAOGG@REPLTEST) 124> stop ztmp

Sending STOP request to REPLICAT ZTMP ...
Request processed.


GGSCI (retestdt as ORAOGG@REPLTEST) 125> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     ZREP001     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP002     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP003     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP004     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP005     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP006     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP007     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP008     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP009     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP010     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREP011     00:00:00      00:00:06    
REPLICAT    RUNNING     ZREPMK      00:00:00      00:00:06    
REPLICAT    STOPPED     ZTMP        00:00:00      00:00:01    


-- 병합 할 타켓 프로세스 중지
GGSCI (retestdt as ORAOGG@REPLTEST) 126> stop zrep001

Sending STOP request to REPLICAT ZREP001 ...
Request processed.


GGSCI (retestdt as ORAOGG@REPLTEST) 127> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     ZREP001     00:00:00      00:00:01    
REPLICAT    RUNNING     ZREP002     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP003     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP004     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP005     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP006     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP007     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP008     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP009     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP010     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP011     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREPMK      00:00:00      00:00:00    
REPLICAT    STOPPED     ZTMP        00:00:00      00:00:15    


GGSCI (retestdt as ORAOGG@REPLTEST) 128> view param ztmp

replicat ztmp

--# Setenv
USERID ORAOGG, PASSWORD oraogg01


DISCARDFILE ./dirout/ztmp.dsc, APPEND, MEGABYTES 2000
DISCARDROLLOVER AT 00:01 ON MONDAY

GETTRUNCATES

--ERROR SKIP PARAMETER
--HANDLECOLLISIONS
REPERROR 1403 DISCARD
REPERROR    1 DISCARD

--# Tune
EOFDELAYCSECS 10


MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT;



GGSCI (retestdt as ORAOGG@REPLTEST) 129> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     ZREP001     00:00:00      00:00:10    
REPLICAT    RUNNING     ZREP002     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP003     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP004     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP005     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP006     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP007     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP008     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP009     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP010     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP011     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREPMK      00:00:00      00:00:00    
REPLICAT    STOPPED     ZTMP        00:00:00      00:00:24    


-- 병합 할 타겟 프로세스 파라미터 변경
-- MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT; 추가
GGSCI (retestdt as ORAOGG@REPLTEST) 130> edit param zrep001
replicat zrep001

--# Setenv
USERID ORAOGG, PASSWORD oraogg01


DISCARDFILE ./dirout/zrep001.dsc, APPEND, MEGABYTES 2000
DISCARDROLLOVER AT 00:01 ON MONDAY

GETTRUNCATES

--ERROR SKIP PARAMETER
--HANDLECOLLISIONS
REPERROR 1403 DISCARD
REPERROR    1 DISCARD

--# Tune
EOFDELAYCSECS 10
--22EA
... 중략 ...
MAP SCOTT.STB_PROD_STOCK_OPT, TARGET LFMREAL.STB_PROD_STOCK_OPT;

"dirprm/zrep001.prm" 43L, 2067C written


 

GGSCI (retestdt as ORAOGG@REPLTEST) 131> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    STOPPED     ZREP001     00:00:00      00:00:30    
REPLICAT    RUNNING     ZREP002     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREP003     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREP004     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREP005     00:00:00      00:00:00    
REPLICAT    RUNNING     ZREP006     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREP007     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREP008     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREP009     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREP010     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREP011     00:00:00      00:00:09    
REPLICAT    RUNNING     ZREPMK      00:00:00      00:00:10    
REPLICAT    STOPPED     ZTMP        00:00:00      00:00:44    



-- 병합 대상 타겟 프로세스의 체크포인트 일치 확인
GGSCI (retestdt as oraogg@REPLTEST) 30> info zrep001

REPLICAT   ZREP001   Last Started 2022-03-31 07:00   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:24 ago)
Log Read Checkpoint  File /DBMS/REPLTEST/ogg19c/dirdat/zw000006444
                     2022-04-04 10:49:13.001638  RBA 990214542


GGSCI (retestdt as oraogg@REPLTEST) 31> info ztmp

REPLICAT   ZTMP      Last Started 2022-04-04 10:36   Status STOPPED
Checkpoint Lag       00:00:00 (updated 00:01:50 ago)
Log Read Checkpoint  File /DBMS/REPLTEST/ogg19c/dirdat/zw000006444
                     2022-04-04 10:49:13.001638  RBA 990214542




-- 병합 된 타겟 프로세스 기동
GGSCI (retestdt as ORAOGG@REPLTEST) 135> start zrep001


-- 병합 이전 타겟 프로세스 삭제
GGSCI (retestdt as ORAOGG@REPLTEST) 138> delete ztmp
Deleted REPLICAT ZTMP.




-- =====================================================
-- 소스 OGG
-- =====================================================
-- 소스 전송프로세스 기동
GGSCI (testdb02p as oraogg@TEST2) 43> start zpmp001

Sending START request to MANAGER ...
EXTRACT ZPMP001 starting

-- 특정 시간 active session 학인
select SAMPLE_ID, SAMPLE_TIME, SESSION_ID, SESSION_SERIAL#, SESSION_TYPE, USER_ID, SQL_ID, SQL_OPNAME, SQL_EXEC_START, PROGRAM, MODULE, MACHINE
from v$active_session_history
where SAMPLE_TIME >= to_date('2022-01-30 11:59:00','YYYY-MM-DD HH24:MI:SS')
and SAMPLE_TIME <= to_date('2022-01-30 12:02:00','YYYY-MM-DD HH24:MI:SS')
;

CREATE TABLE PLAN_TABLE -- 테이블 이름
(
 STATEMENT_ID     VARCHAR2 (30),
 TIMESTAMP DATE,
 REMARKS          VARCHAR2 (80),
 OPERATION        VARCHAR2 (30),
 OPTIONS          VARCHAR2 (255),
 OBJECT_NODE      VARCHAR2 (128),
 OBJECT_OWNER     VARCHAR2 (30),
 OBJECT_NAME      VARCHAR2 (30),
 OBJECT_INSTANCE  NUMBER,
 OBJECT_TYPE      VARCHAR2 (30),
 OPTIMIZER        VARCHAR2 (255),
 SEARCH_COLUMNS   NUMBER,
 ID               NUMBER,
 PARENT_ID        NUMBER,
 POSITION         NUMBER,
 COST             NUMBER,
 CARDINALITY      NUMBER,
 BYTES            NUMBER,
 OTHER_TAG        VARCHAR2 (255),
 PARTITION_START  VARCHAR2 (255),
 PARTITION_STOP   VARCHAR2 (255),
 PARTITION_ID     NUMBER,
 OTHER            LONG,
 DISTRIBUTION     VARCHAR2 (30),
 CPU_COST         NUMBER,
 IO_COST          NUMBER,
 TEMP_SPACE       NUMBER,
 ACCESS_PREDICATES VARCHAR2 (4000),
 FILTER_PREDICATES VARCHAR2 (4000)
);

일반적으로 데이터베이스 사용자는 DELETE 문을 사용한 테이블 단편화에 대하여 알고 있습니다. 대량 삭제를 수행할 때마다 대부분의 경우 디스크 공간을 회수하기 위해 항상 테이블을 다시 작성합니다. 그러나 DELETE만 테이블 단편화를 일으킬 수 있지 않습니다. INSERT 문으로 테이블 조각화가 어떻게 발생하는지 확인 해 보겠습니다.

MySQL에는 두 가지 종류의 단편화가 있습니다.

• 테이블 내에서 일부 InnoDB 페이지가 완전히 비어 있는 단편화.
• 일부 InnoDB 페이지가 완전히 채워지지 않은 조각화(페이지에 여유 공간이 있음).

INSERT를 사용한 테이블 조각화에는 세 가지 사례가 있습니다.

• INSERT ROLLBACK
• INSERT 실패
• 페이지 분할을 통한 단편화

테스트 환경

아래와 같이 테스트 환경을 만들었습니다.

• DB: test
• Tables : frag, ins_frag, frag_page_spl
• Table Size: 2G

사례 1: INSERT ROLLBACK

처음에는 " ins_frag " 라는 새 테이블을 만듭니다 . 그런 다음 BEGIN을 사용하여 트랜잭션을 생성하고 아래와 같이 " frag " 테이블에서 " ins_frag " 테이블로 데이터를 복사합니다 .

mysql> create table ins_frag like frag;
Query OK, 0 rows affected (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ins_frag select * from frag;
Query OK, 47521280 rows affected (3 min 7.45 sec)
Records: 47521280  Duplicates: 0  Warnings: 0

#Linux shell
sakthi-3.2# ls -lrth
total 8261632
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 03:00 ins_frag.ibd

위에서 INSERT가 실행되었지만 INSERT를 커밋/롤백하지 않았습니다. 두 테이블 모두 2GB 의 디스크 공간을 차지했습니다 . 

이제 INSERT를 ROLLBACK 하겠습니다.

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
| 47521280 |
+----------+
1 row in set (1.87 sec)

mysql> rollback;
Query OK, 0 rows affected (5 min 45.21 sec)

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)


#Linux shell
sakthi-3.2# ls -lrth
total 8261632
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 03:09 ins_frag.ibd

INSERT를 롤백한 후 " ins_frag " 테이블 은 여전히 ​​동일한 2GB 디스크 공간을 차지합니다 . MySQL 클라이언트를 통해 단편화 공간을 찾아봅니다.

mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 * 1024 ), 2), 'G') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='test' and table_name='ins_frag';
+----------+----------+-------+----------+
| DATABASE | TABLE.   | TOTAL | DATAFREE |
+----------+----------+-------+----------+
| test     | ins_frag | 0.00G | 1.96G    |
+----------+----------+-------+----------+
1 row in set (0.01 sec)

INSERT를 롤백하면 단편화가 됩니다. 디스크 공간을 회수하려면 테이블을 다시 작성해야 합니다.

mysql> alter table ins_frag engine=innodb;
Query OK, 0 rows affected (2.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 03:11 ins_frag.ibd

사례 2: INSERT 실패

테스트를 위해 두 개의 MySQL 클라이언트 세션(세션 1 및 세션 2)을 만들었습니다. 

세션 1에서 트랜잭션 내에서 동일한 INSERT 문을 실행하고 있습니다. 그러나 이번에는 세션 2에서 INSERT 쿼리를 중단하고 종료했습니다.

세션 1

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 04:02 ins_frag.ibd

#MySQL shell

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ins_frag select * from frag;   #is running

세션 2

mysql> pager grep -i insert ; show processlist;
PAGER set to 'grep -i insert'
| 33 | root            | localhost | percona | Query   |    14 | executing              | insert into ins_frag select * from frag |
4 rows in set (0.00 sec)

mysql> kill 33;
Query OK, 0 rows affected (0.00 sec)

INSERT가 중단되어 실패했습니다.

세션 1

mysql> insert into ins_frag select * from frag;
ERROR 2013 (HY000): Lost connection to MySQL server during query

#Linux shell

sakthi-3.2# ls -lrth
total 4591616
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   212M Jun 17 04:21 ins_frag.ibd

#MySQL shell

mysql> select count(*) from ins_frag;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.10 sec)

INSERT가 완료되지 않았으며 테이블에 데이터가 없습니다. 그러나 여전히 테이블 .ibd 파일은 2GB 까지 늘어났습니다. MySQL 클라이언트를 통해 단편화 공간을 살펴보자.

mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'M') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='test' and table_name='ins_frag';
+----------+----------+---------+----------+
| DATABASE | TABLE    | TOTAL   | DATAFREE |
+----------+----------+---------+----------+
| test     | ins_frag | 0.03M   | 210.56M  |
+----------+----------+---------+----------+
1 row in set (0.01 sec)

테이블에 단편화된 공간이 있고 공간을 회수하기 위해 테이블을 다시 작성해야 함을 보여줍니다.

mysql> alter table ins_frag engine='innodb';
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#Linux shell

sakthi-3.2# ls -lrth
total 4131040
-rw-r-----  1 _mysql  _mysql   2.0G Jun 17 02:43 frag.ibd
-rw-r-----  1 _mysql  _mysql   112K Jun 17 04:32 ins_frag.ibd

사례 3: 페이지 분할을 통한 단편화

우리는 내부적으로 InnoDB 레코드가 InnoDB 페이지에 저장된다는 것을 알고 있습니다. 각 페이지 크기는 기본적으로 16K이지만 페이지 크기를 변경할 수 있는 옵션이 있습니다.

InnoDB 페이지에 새 레코드 또는 인덱스 항목을 수용할 공간이 충분하지 않은 경우 두 페이지로 분할되어 각각 약 50%가 차게 됩니다. 즉, rollback이나 delete가 없는 insert 전용 워크로드의 경우에도 평균 페이지 사용률이 75%에 불과할 수 있으므로 이러한 종류의 내부 페이지 조각화에서는 25%의 손실이 발생할 수 있습니다.

인덱스가 정렬 방식으로 구축되면 혼잡이 더 심해지며 테이블에 인덱스의 임의 위치로 이동하는 삽입이 많으면 페이지 분할이 발생합니다.

내림차순 인덱스가 있는 테이블을 만들었습니다.

mysql> show create table frag_page_spl\G
*************************** 1. row ***************************
Table: frag_page_spl
Create Table: CREATE TABLE `frag_page_spl` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL,
`messages` varchar(600) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_spl` (`messages` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.07 sec)

INFORMATION_SCHEMA.INNODB_METRICS 테이블에서 페이지 분할 활동을 모니터링할 수 있습니다. 이를 위해서는 InnoDB 모니터를 활성화해야 합니다.

mysql> SET GLOBAL innodb_monitor_enable=all;
Query OK, 0 rows affected (0.09 sec)

그런 다음 6개의 병렬 스레드로 INSERT를 무작위로 진행하는 스크립트를 수행 하였습니다.

mysql> select name,count,type,status,comment from information_schema.innodb_metrics where name like '%index_page_spl%'\G
*************************** 1. row ***************************
name: index_page_splits
count: 52186
type: counter
status: enabled
comment: Number of index page splits
1 row in set (0.05 sec)

mysql> SELECT
-> table_schema as 'DATABASE',
-> table_name as 'TABLE',
-> CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 ), 2), 'M') 'TOTAL',
-> CONCAT(ROUND(data_free / ( 1024 * 1024 ), 2), 'M') 'DATAFREE'
-> FROM information_schema.TABLES
-> where table_schema='test' and table_name='frag_page_spl';
+----------+---------------+----------+----------+
| DATABASE | TABLE.        | TOTAL    | DATAFREE |
+----------+---------------+----------+----------+
| test     | frag_page_spl | 2667.55M | 127.92M  |
+----------+---------------+----------+----------+
1 row in set (0.00 sec)

INNODB_METRICS에서 페이지 분할 카운터가 증가한 것을 볼 수 있습니다. 127.92MB 의 단편화를 생성한 52186 개의 페이지 분할 작업이 발생 했음을 보여줍니다 .

분할 페이지가 생성되면 뒤로 이동할 수 있는 유일한 방법은 생성된 페이지를 병합 임계값 아래로 떨어뜨리는 것입니다. 이 경우 InnoDB는 병합 작업으로 분할 페이지에서 데이터를 이동합니다. MERGE_THRESHOLD는 테이블 및 특정 인덱스에 대해 구성할 수 있습니다.

데이터를 재구성하는 다른 방법은 테이블을 OPTIMIZE하는 것입니다. 테이블 OPTIMIZE는 매우 무겁고 긴 프로세스일 수 있지만 너무 많은 페이지가 단편화 되어 있는 상황에서 복구하는 유일한 방법입니다.