MySQL Partition Exchange 작업 테스트 내용 정리(작업 소요시간 산정)

1) 테이블 용량 파악

mysql> USE DATABASE_NAME;

mysql> 
SELECT TABLE_SCHEMA, TABLE_NAME, 
PARTITION_NAME, 
PARTITION_ORDINAL_POSITION AS PART_POS, 
TABLE_ROWS,
ROUND(data_length/1024/1024/1024,2) AS 'data_gb',
ROUND(index_length/1024/1024/1024,2) AS 'index_gb'
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'TABLE_NAME'
ORDER BY PART_POS;

2) exchange할 테이블 구조 생성

-- 테이블 구조 생성
mysql> CREATE TABLE TEMP_241220_1 LIKE TABLE_NAME;
mysql> CREATE TABLE TEMP_241220_2 LIKE TABLE_NAME;
mysql> CREATE TABLE TEMP_241220_3 LIKE TABLE_NAME;
mysql> CREATE TABLE TEMP_241220_4 LIKE TABLE_NAME;
mysql> CREATE TABLE TEMP_241220_5 LIKE TABLE_NAME;
mysql> CREATE TABLE TEMP_241220_6 LIKE TABLE_NAME;
mysql> CREATE TABLE TEMP_241220_7 LIKE TABLE_NAME;
mysql> CREATE TABLE TEMP_241220_8 LIKE TABLE_NAME;

-- 테이블 파티셔닝 제거

※ EXCHANGE 작업시 일반테이블로 작업 해야하기 때문에 PARTITIONING 제거를 해준다.

mysql> ALTER TABLE TEMP_241220_1 remove partitioning;
mysql> ALTER TABLE TEMP_241220_2 remove partitioning;
mysql> ALTER TABLE TEMP_241220_3 remove partitioning;
mysql> ALTER TABLE TEMP_241220_4 remove partitioning;
mysql> ALTER TABLE TEMP_241220_5 remove partitioning;
mysql> ALTER TABLE TEMP_241220_6 remove partitioning;
mysql> ALTER TABLE TEMP_241220_7 remove partitioning;
mysql> ALTER TABLE TEMP_241220_8 remove partitioning;

-- 테이블 정상 생성 확인
mysql> SHOW CREATE TABLE TEMP_241220_1;
mysql> SHOW CREATE TABLE TEMP_241220_2;
mysql> SHOW CREATE TABLE TEMP_241220_3;
mysql> SHOW CREATE TABLE TEMP_241220_4;
mysql> SHOW CREATE TABLE TEMP_241220_5;
mysql> SHOW CREATE TABLE TEMP_241220_6;
mysql> SHOW CREATE TABLE TEMP_241220_7;
mysql> SHOW CREATE TABLE TEMP_241220_8;

 

---

3) exchange partition

 

ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_1;

※ mysql 8.0version에서 lock=none 옵션을 걸고 테스트를 했지만.. 옵션을 거나 옵션을 걸지 않나 0.1초내로 작업이 끝나기 때문에 lock을 확인할 겨를이 없었다... 따라서 해당옵션이 제대로 먹힌건지는... 모르겠다..

mysql> ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_1;
mysql> ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_2;
mysql> ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_3;
mysql> ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_4;
mysql> ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_5;
mysql> ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_6;
mysql> ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_7;
mysql> ALTER TABLE TABLE_NAME algorithm=inplace, lock=none, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_8;
-- 소요시간 : exchange 테이블 하나당 0.1내로 완료 (테이블 하나당 용량 약 200GB)
-- TEST시 1초 내외로 완료
/*
ALTER TABLE TABLE_NAME ALGORITHM=inplace, LOCK=NONE, EXCHANGE PARTITION partition_name WITH TABLE TEMP_241220_1;
-- 0.041초
SELECT *
FROM TABLE_NAME PARTITION(partition_name);
-- 확인

*/

-- 대기세션 확인

mysql> 
SELECT * FROM PERFORMANCE_SCHEMA.DATA_LOCKS;
mysql> 
SELECT straight_join dl.THREAD_ID
, est.SQL_TEXT
, dl.OBJECT_SCHEMA
, dl.OBJECT_NAME
, dl.INDEX_NAME
, dl.LOCK_TYPE
, dl.LOCK_MODE
, dl.LOCK_STATUS
, dl.LOCK_DATA
FROM
  performance_schema.data_locks dl inner join performance_schema.events_statements_current est on dl.THREAD_ID = est.THREAD_ID
ORDER BY est.TIMER_START,dl.OBJECT_INSTANCE_BEGIN;

-- 실행중인 세션 확인
mysql> select * from information_schema.processlist where command !='Sleep';


-- 테이블 용량 파악
mysql> 
SELECT TABLE_SCHEMA, TABLE_NAME, 
PARTITION_NAME, 
PARTITION_ORDINAL_POSITION AS PART_POS, 
TABLE_ROWS,
ROUND(data_length/1024/1024/1024,2) AS 'data_gb',
ROUND(index_length/1024/1024/1024,2) AS 'index_gb'
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'TABLE_NAME';

4) exchange한 임시 테이블 DROP

-- EXCHANGE 테이블 DROP
mysql> DROP TABLE  TEMP_241220_1;
mysql> DROP TABLE  TEMP_241220_2;
mysql> DROP TABLE  TEMP_241220_3;
mysql> DROP TABLE  TEMP_241220_4;
mysql> DROP TABLE  TEMP_241220_5;
mysql> DROP TABLE  TEMP_241220_6;
mysql> DROP TABLE  TEMP_241220_7;
mysql> DROP TABLE  TEMP_241220_8;
-- 소요시간 : 1테이블당 1초 미만

5) BINLOG 용량 및 ERROR LOG 점검

 

-- binary log 용량 점검 및 error log 확인
$ show binary logs;

-- 동기화 확인
mysql> show slave status\