MySQL & Maria

[ MySQL ] MyISAM to InnoDB

오늘은 MySQL MyISAM 엔진의 테이블들을 InnoDB 엔진의 테이블로 변경하는 내용에 대해서 포스팅할 예정입니다.

MySQL Dev에 작성된 지침들을 확인해 보면서 진행하도록 하겠습니다.

https://dev.mysql.com/doc/refman/8.0/en/converting-tables-to-innodb.html

MySQL :: MySQL 8.0 Reference Manual :: 15.6.1.5 Converting Tables from MyISAM to InnoDB

15.6.1.5 Converting Tables from MyISAM to InnoDB If you have MyISAM tables that you want to convert to InnoDB for better reliability and scalability, review the following guidelines and tips before converting. Adjusting Memory Usage for MyISAM and InnoDB

dev.mysql.com

■ guidelines

Memory 사용

MyISAM의 경우 Data는 Buffer에 넣지 못하지만 Index의 경우 Key_buffer_size로 메모리 공간 조절
InnoDB의 경우 Data, Index 모두 Buffer에 적재가능하며 Innodb_buffer_pool_size로 메모리 공간 조절
상대적으로 key_buffer_size를 줄이고 Innodb_buffer_pool_size를 늘려야 한다.

Transaction

MyISAM의 경우 Transaction을 지원하지 않았지만 InnoDB의 경우 Transaction의 지원하게 된다.
InnoDB의 경우 Transaction이 존재하기 때문에 너무 긴 Tranasction의 경우 오버헤드가 발생할 수 있다. 따라서 너무 오래 실행되는 트랜잭션을 피하도록 주의
해당 Tranasction 부분은 크게 문제가 될 것으로 보이지 않는다

교착 상태 처리 ( Deadlock )

InnoDB의 경우 Tranasction이 존재하기 때문에 Deadlock이 발생할 수 있다.
Deadlock 관련 Parameter
- innodb_deadlock_detect : Deadlock 모니터링할지 ( Default : ON )
- innodb_lock_wait_timeout: InnoDB Record Lck Timeout ( Default : 50 Sec )

스토리지 요구 사항

InnoDB의 경우 MyISAM보다 저장 공간을 더 사용하게 된다.
Alter / Create Table을 하든 저장공간이 기존보다 더 필요하기 때문에 주의해야 한다.
- Alter → Copy to Tmp → 해당 Datafile의 Directory에 임시 테이블 생성됨
디스크 용량 부족시 Rollback 된다.

Priimary Key

InnoDB의 경우 Primary Key로 테이블이 클러스터링 되기 때문에 중요하다. ( MyISAM의 경우 클러스터링 X )

■ Migration

이관 방법

이관의 경우 아래 3가지 방법으로 진행할 수 있습니다.

CTAS
- CREATE TABLE table_name ENGINE=InnoDB AS SELECT * FROM table_name
- CREATE TABLE EMPLOYEES3 ENGINE=INNODB AS SELECT * FROM EMPLOYEES;
Alter
- ALTER TABLE table_name ENGINE=InnoDB;
- Online DDL 불가
Mysqldump
- Mysqldump후 sed로 Engine=MyISAM ⇒ Engine=InnoDB로 변경

이관 테스트 ( Alter )

이번 포스팅에서는 Alter문을 이용한 이관 방법을 테스트해보도록 하겠습니다.

■ ASIS 테이블 조회

mysql> show create table test2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `a` int NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `test` varchar(100) DEFAULT NULL,
  `Address` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

## Size
8.0K    ./test2_459.sdi
23M     ./test2.MYD
4.2M    ./test2.MYI

■ Pimary Key로 테이블정령

InnoDB로 변경하기 전에 Primary Key로 테이블을 클러스티링 작업해 줍니다.

mysql -uroot -p -N -e "
SELECT concat(alt, pky, ';') 
FROM (SELECT concat('ALTER TABLE ',TABLE_NAME, ' ORDER BY ') AS alt, group_concat(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS pky 
      FROM INFORMATION_SCHEMA.STATISTICS 
      WHERE INDEX_NAME= 'PRIMARY' 
      AND TABLE_NAME in (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA = 'employees')
      GROUP by TABLE_NAME) 
      AS sub;" > sort.sql


[root@pmm-client ~]# cat sort.sql 
ALTER TABLE test2 ORDER BY a;

[root@pmm-client ~]# mysql -uroot -proot employees;

mysql> source sort.sql

### Show processlist => copy to tmp table 발생 
+-----+-----------------+-----------+-----------+---------+-------+------------------------+------------------------------+
| Id  | User            | Host      | db        | Command | Time  | State                  | Info                         |
+-----+-----------------+-----------+-----------+---------+-------+------------------------+------------------------------+
| 5   | event_scheduler | localhost |           | Daemon  | 62736 | Waiting on empty queue |                              |
| 171 | root            | localhost |           | Query   | 0     | init                   | show processlist             |
| 191 | root            | localhost | employees | Query   | 4     | copy to tmp table      | ALTER TABLE test2 ORDER BY a |
+-----+-----------------+-----------+-----------+---------+-------+------------------------+------------------------------+

###DATADIR  -> tmp Table이 생기는 것을 확인할 수 있다.
112K    ./departments.ibd
1005M   ./dept_emp2.ibd
25M     ./dept_emp.ibd
112K    ./dept_manager.ibd
23M     ./employees.ibd
104M    ./salaries.ibd
8.0K    ./test2_460.sdi
23M     ./test2.MYD
6.5M    ./test2.MYI
27M     ./titles.ibd

112K    ./departments.ibd
1005M   ./dept_emp2.ibd
25M     ./dept_emp.ibd
112K    ./dept_manager.ibd
23M     ./employees.ibd
104M    ./salaries.ibd
8.0K    ./test2_459.sdi
23M     ./test2.MYD
4.2M    ./test2.MYI
27M     ./titles.ibd
8.0K    ./@0023sql@002d872_bf_460.sdi
(생략)
112K    ./departments.ibd
1005M   ./dept_emp2.ibd
25M     ./dept_emp.ibd
112K    ./dept_manager.ibd
23M     ./employees.ibd
104M    ./salaries.ibd
32M     ./#sql-872_bf.MYD
1.6M    ./#sql-872_bf.MYI
8.0K    ./test2_459.sdi
23M     ./test2.MYD
4.2M    ./test2.MYI
27M     ./titles.ibd


112K    ./departments.ibd
1005M   ./dept_emp2.ibd
25M     ./dept_emp.ibd
112K    ./dept_manager.ibd
23M     ./employees.ibd
104M    ./salaries.ibd
8.0K    ./test2_460.sdi
23M     ./test2.MYD
6.5M    ./test2.MYI
27M     ./titles.ibd

■ Alter Table

copy to tmp table 방생

MYD, MYI 확장자가 ibd 확장자로 변경

SDI의 경우 mysql.ibd로 합쳐지게 됨

mysql> select concat('alter table ',table_name,' engine=innodb;') from information_schema.tables where table_schema='employees' and engine='MYISAM';

+-----------------------------------------------------+
| concat('alter table ',table_name,' engine=innodb;') |
+-----------------------------------------------------+
| alter table test2 engine=innodb;                    |
+-----------------------------------------------------+


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


### SHOW PROCESSLIST
+-----+-----------------+-----------+-----------+---------+-------+------------------------+---------------------------------+
| Id  | User            | Host      | db        | Command | Time  | State                  | Info                            |
+-----+-----------------+-----------+-----------+---------+-------+------------------------+---------------------------------+
| 5   | event_scheduler | localhost |           | Daemon  | 63003 | Waiting on empty queue |                                 |
| 191 | root            | localhost | employees | Query   | 1     | copy to tmp table      | alter table test2 engine=innodb |
| 193 | root            | localhost |           | Query   | 0     | init                   | show processlist                |
+-----+-----------------+-----------+-----------+---------+-------+------------------------+---------------------------------+

+-----+-----------------+-----------+-----------+---------+-------+------------------------+---------------------------------+
| Id  | User            | Host      | db        | Command | Time  | State                  | Info                            |
+-----+-----------------+-----------+-----------+---------+-------+------------------------+---------------------------------+
| 5   | event_scheduler | localhost |           | Daemon  | 63004 | Waiting on empty queue |                                 |
| 191 | root            | localhost | employees | Query   | 2     | copy to tmp table      | alter table test2 engine=innodb |
| 193 | root            | localhost |           | Query   | 0     | init                   | show processlist                |
+-----+-----------------+-----------+-----------+---------+-------+------------------------+---------------------------------+


### DATADIR
[root@pmm-client employees]# while true; do du -sh ./*; sleep 2; echo "###################"; done
###################
112K    ./departments.ibd
1005M   ./dept_emp2.ibd
25M     ./dept_emp.ibd
112K    ./dept_manager.ibd
23M     ./employees.ibd
104M    ./salaries.ibd
8.0K    ./test2_460.sdi
23M     ./test2.MYD
6.5M    ./test2.MYI
27M     ./titles.ibd
###################
112K    ./departments.ibd
1005M   ./dept_emp2.ibd
25M     ./dept_emp.ibd
112K    ./dept_manager.ibd
23M     ./employees.ibd
104M    ./salaries.ibd
22M     ./#sql-872_bf.ibd
8.0K    ./test2_460.sdi
23M     ./test2.MYD
6.5M    ./test2.MYI
27M     ./titles.ibd
###################
112K    ./departments.ibd
1005M   ./dept_emp2.ibd
25M     ./dept_emp.ibd
112K    ./dept_manager.ibd
23M     ./employees.ibd
104M    ./salaries.ibd
32M     ./test2.ibd
27M     ./titles.ibd



mysql> show create table test2;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `a` int NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `test` varchar(100) DEFAULT NULL,
  `Address` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

저작자표시 비영리 변경금지

'MySQL & Maria' 카테고리의 다른 글

AND / OR 논리 연산자 우선 순위 (0)	2023.12.08
[ MySQL ] CPU 부하 Query 모니터링 (10)	2023.03.10
[ MySQL ] innodb_onlin_alter_log_max_size 중요성 (15)	2023.02.27
[ MySQL ] MySQL 데이터 디렉토리 변경 (22)	2023.02.21
[ MySQL ] Openging Table 상태 (3)	2023.02.17

Contents

새소식

인기 검색어

[ MySQL ] MyISAM to InnoDB

■ guidelines

■ Migration

'MySQL & Maria' 카테고리의 다른 글

당신이 좋아할만한 콘텐츠

티스토리툴바