SQL 튜닝하려고 개발 DB를 multi instance로 구축해놓고 필요할 때
migration 해서 사용하는데 mirgration 에러가 나서 확인해보니 디스크용량이 부족하다고 한다..
근데 디스크 빵빵한 걸로 알고 있는데???
일단 파티션별로 디스크 용량을 확인해본다
# df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/sda2 ext4 20G 4.0G 15G 22% /
tmpfs tmpfs 16G 0 16G 0% /dev/shm
/dev/sda1 ext4 477M 142M 310M 32% /boot
/dev/sda5 ext4 20G 9.7G 9.0G 52% /usr
/dev/sda7 ext4 3.9G 927M 2.8G 26% /var
/dev/sda6 ext4 3.9G 8.0M 3.7G 1% /tmp
/dev/sdb1 ext4 1.1T 938G 107G 90% /data
/dev/sda8 ext4 212G 136G 66G 68% /backup
xxx.xxx.xxx.xxx:/backup2 nfs 2.5T 1.9T 515G 79% /backup2
# du -h --max-depth=1
11M ./xxDB
1.1G ./xxDB
6.5G ./xxxTDB
22M ./mysql
31G ./zzDB
1.1M ./performance_schema
156K ./test
17G ./zzz
8.0K ./backup
617M ./vvv
676K ./sys
35G ./xxDB
829G .
//뭐야 이거; 현재경로에 829G 파일이 있다고??
어떤 파일인지 좀 더 확인해본다.
# ls -lh
합계 738G
drwxr-x--- 2 mysql mysql 12K 2020-10-13 13:43 xxDB
drwxr-x--- 2 mysql mysql 20K 2020-02-28 17:35 xxxDB
drwxr-x--- 2 mysql mysql 52K 2021-02-22 18:49 xxxx
drwxr-x--- 2 mysql mysql 20K 2021-02-16 14:08 xxx
drwxr-x--- 2 mysql mysql 12K 2021-02-16 15:05 zzDB
drwxr-x--- 2 mysql mysql 4.0K 2021-02-02 18:56 ttDB
drwxr-x--- 2 mysql mysql 40K 2021-02-23 10:07 xxxxDB
-rw-r----- 1 mysql mysql 56 2020-02-28 17:45 auto.cnf
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:34 backup
-rw-r----- 1 mysql mysql 36K 2021-02-10 15:22 ddl_log.log
-rw-r----- 1 mysql mysql 558K 2020-11-23 11:23 ib_buffer_pool
-rw-r----- 1 mysql mysql 256M 2021-02-23 10:10 ib_logfile0
-rw-r----- 1 mysql mysql 256M 2021-02-23 10:10 ib_logfile1
-rw-r----- 1 mysql mysql 256M 2021-02-23 10:07 ib_logfile2
-rw-r----- 1 mysql mysql 500M 2021-02-23 10:10 ibdata1
-rw-r----- 1 mysql mysql 500M 2021-02-23 10:10 ibdata2
-rw-r----- 1 mysql mysql 500M 2021-02-23 10:08 ibdata3
-rw-r----- 1 mysql mysql 5.9G 2021-02-23 10:10 ibdata4
-rw-r----- 1 mysql mysql 730G 2021-02-23 10:06 ibtmp1
-rw-r----- 1 mysql mysql 2.8K 2020-02-28 17:43 mvno-my.cnf
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:35 mysql
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:34 performance_schema
-rw-r----- 1 mysql mysql 6 2020-11-23 11:23 privacy-db.pid
drwxr-x--- 2 mysql mysql 12K 2020-02-28 17:43 sys
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:43 test
-rw-r----- 1 mysql mysql 22 2020-02-28 17:43 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 578 2020-02-28 17:43 xtrabackup_info
-rw-r----- 1 mysql mysql 730G 2021-02-23 10:06 ibtmp1
//헐 이게 뭐냐?? ibtmp1에 대해 알아봐야겠다.
* ibtmp1 이란?
[MySQL Internals] Temporary Tablespace
압축되지 않고 사용자가 생성한 임시 테이블과 디스크에 생성되는 내부적인 임시 테이블들이 shared temporary tablespace 에 생성됩니다.
innodb_temp_data_file_path 옵션으로 상대 경로, 이름, 사이즈, 데이터파일의 속성을 설정할 수 있습니다.
아무것도 설정하지 않으면 기본적으로 innodb_data_home_dir 경로에 ibtmp1:12M:authextend 속성으로 생성됩니다.
[Note]
MySQL 5.6에서는 압축되지 않은 테이블에 대한 임시 테이블 스페이스는 개별 file-per-table 테이블 스페이스에 생성되었었습니다.
또는 innodb_file_per_table 설정이 안되어 있다면 시스템 테이블 스페이스에 생성됩니다.
5.7의 Temporary Tablespace 는 기존 개별 file-per-table 테이블 스페이스를 생성하고 삭제할 필요가 없기 때문에 성능 이점을 가집니다.
또한 전용 Temporary Tablespace가 있기 때문에 temp table 에 대한 metadata 를 시스템 테이블에 생성할 필요가 없어집니다.
[Mysql 5.7에서 임시테이블에 대한 성능개선]
Mysql ver 5.7.2에서 일반 임시 테이블과 압축 임시테이블 그리고 거기에 연관된 오브젝트들을 위한 새로운 타입의 Undo Log가 소개되었다. 임시 테이블의 내용은 Crash Recovery에서 사용되지 않기 때문에 redo log가 필요하지 않다. 즉, 임시테이블의 정보는 서버가 운영 중일때, 롤백해야 하는 상황에서만 필요하다. 리두로그를 만들지 않는 Undo Log는 해당 임시테이블과 거기에 관련된 오브젝트를 위한 redo logging으로 인해 발생하는 Disk I/O 를 피할수 있기 때문에 성능에 도움을 준다. 임시테이블에 대한 Undo log는 임시테이블 스페이스에 위치한다. 기본으로 생성되는 임시테이블 스페이스 파일은 ibtmp1이라는 이름을 가진다. 이것은 따로 지정하지 않으면 Data Directory에 위치하게 되고, 이것은 Mysql이 Startup 될 때 자동으로 재성생된다. 사용자의 요구에 따라 위치를 변경할 수 있는데 이때 사용하는 시스템 변수는 innodb_temp_data_file_path이다.
--> 아~ 임시파일을 저장하는 파일이군..
redo log를 쓰지않으니 Disk I/O 이슈는 없었던 것이고, DB를 재시작하게 되면 삭제하고 다시 생성하는 것으로 확인했다.
mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend |
+------------------------------+
1 row in set (0.00 sec)
#사용량 확인
mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G;
*************************** 1. row ***************************
FILE_ID: 3293
FILE_NAME: ./ibtmp1
FILE_TYPE: TEMPORARY
TABLESPACE_NAME: innodb_temporary
TABLE_CATALOG:
TABLE_SCHEMA: NULL
TABLE_NAME: NULL
LOGFILE_GROUP_NAME: NULL
LOGFILE_GROUP_NUMBER: NULL
ENGINE: InnoDB
FULLTEXT_KEYS: NULL
DELETED_ROWS: NULL
UPDATE_COUNT: NULL
FREE_EXTENTS: 736959
TOTAL_EXTENTS: 747404
EXTENT_SIZE: 1048576
INITIAL_SIZE: 12582912
MAXIMUM_SIZE: NULL
AUTOEXTEND_SIZE: 67108864
CREATION_TIME: NULL
LAST_UPDATE_TIME: NULL
LAST_ACCESS_TIME: NULL
RECOVER_TIME: NULL
TRANSACTION_COUNTER: NULL
VERSION: NULL
ROW_FORMAT: NULL
TABLE_ROWS: NULL
AVG_ROW_LENGTH: NULL
DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
INDEX_LENGTH: NULL
DATA_FREE: 772806803456
CREATE_TIME: NULL
UPDATE_TIME: NULL
CHECK_TIME: NULL
CHECKSUM: NULL
STATUS: NORMAL
EXTRA: NULL
1 row in set (0.05 sec)
* 그렇다면 너무 커진 Temporary Tablespace 를 줄이기 위한 방법은 ?
--> DB를 재시작하여 기본설정으로 Tablespace 를 재생성하도록 하는 방법밖에 없다고 한다
하지만 다행이다; 이건 개발 DB다.
따라서 설정 시에 디스크 사이즈를 고려하여 너무 크게 설정하지 않도록 max 를 제한할 수 있는 방법도 있다
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
주의할 점은..
쿼리가 수행 도중에 MAX 에 도달하게 되면 table is full 에러를 내면서 쿼리는 실패됩니다. --> 잠깐만요..; 뭐라고요?
하지만 무제한으로 tablespace 를 제공할 수는 없기 때문에 적절한 사이즈를 정해야 합니다.
--> DB는 분기별로 정기 PM이 있어서 굳이 넣을 필요가 있을까 싶다.. 그냥 재시작한다.
* DB shutdown 후 파일확인
# ls -lh
합계 8.1G
drwxr-x--- 2 mysql mysql 12K 2020-10-13 13:43 xxDB
drwxr-x--- 2 mysql mysql 20K 2020-02-28 17:35 xxxDB
drwxr-x--- 2 mysql mysql 52K 2021-02-22 18:49 zzz
drwxr-x--- 2 mysql mysql 20K 2021-02-23 18:35 xxDB
drwxr-x--- 2 mysql mysql 12K 2021-02-16 15:05 xxDB
drwxr-x--- 2 mysql mysql 4.0K 2021-02-02 18:56 xxDB
drwxr-x--- 2 mysql mysql 52K 2021-02-23 12:53 xxxxDB
-rw-r----- 1 mysql mysql 56 2020-02-28 17:45 auto.cnf
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:34 backup
-rw-r----- 1 mysql mysql 36K 2021-02-10 15:22 ddl_log.log
-rw-r----- 1 mysql mysql 2.7M 2021-02-24 08:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 256M 2021-02-24 08:21 ib_logfile0
-rw-r----- 1 mysql mysql 256M 2021-02-24 03:05 ib_logfile1
-rw-r----- 1 mysql mysql 256M 2021-02-24 08:21 ib_logfile2
-rw-r----- 1 mysql mysql 500M 2021-02-24 08:21 ibdata1
-rw-r----- 1 mysql mysql 500M 2021-02-24 08:21 ibdata2
-rw-r----- 1 mysql mysql 500M 2021-02-24 03:13 ibdata3
-rw-r----- 1 mysql mysql 5.9G 2021-02-24 08:21 ibdata4
-rw-r----- 1 mysql mysql 2.8K 2020-02-28 17:43 mvno-my.cnf
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:35 mysql
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:34 performance_schema
drwxr-x--- 2 mysql mysql 12K 2020-02-28 17:43 sys
drwxr-x--- 2 mysql mysql 4.0K 2020-02-28 17:43 test
-rw-r----- 1 mysql mysql 22 2020-02-28 17:43 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 578 2020-02-28 17:43 xtrabackup_info
//DB shutdown 후 확인해보니 파일이 사라졌다. DB를 내릴때 같이 삭제하는 로직인 것을 알수 있다.
DB를 다시 startup 하고 ls -lh로 파일을 확인해보니 ibtmp1 파일이 12M 로 생성된 것을 확인할 수 있었다.
파티션별 디스크 용량한번 다시 확인
df -Th
Filesystem Type Size Used Avail Use% Mounted on
/dev/sda2 ext4 20G 4.0G 15G 22% /
tmpfs tmpfs 16G 0 16G 0% /dev/shm
/dev/sda1 ext4 477M 142M 310M 32% /boot
/dev/sda5 ext4 20G 9.7G 9.0G 52% /usr
/dev/sda7 ext4 3.9G 927M 2.8G 26% /var
/dev/sda6 ext4 3.9G 8.0M 3.7G 1% /tmp
/dev/sdb1 ext4 1.1T 224G 821G 22% /data
/dev/sda8 ext4 212G 136G 66G 68% /backup
xxx.xxx.xxx.xxx:/backup2 nfs 2.5T 1.9T 497G 80% /backup2
이슈 해결 끝~
참조:
https://m.blog.naver.com/PostView.nhn?blogId=sory1008&logNo=221381987533&proxyReferer=http:%2F%2Fwww.google.co.kr%2Furl%3Fsa%3Dt%26rct%3Dj%26q%3D%26esrc%3Ds%26source%3Dweb%26cd%3D%26ved%3D2ahUKEwjL3e3s6f7uAhUKPnAKHcIqCL8QFjAAegQIAhAD%26url%3Dhttp%253A%252F%252Fm.blog.naver.com%252Fsory1008%252F221381987533%26usg%3DAOvVaw3NPXn05xI9F2uUudbJ4-Vf
https://mysqldba.tistory.com/284
'RDB > mysql' 카테고리의 다른 글
mysql int(10) / int(11) / int(20) / bigint(20) 괄호 안에 숫자 의미는 ? (0) | 2022.08.16 |
---|---|
.TRG 파일 이건 뭐야 (0) | 2021.03.23 |
특정 컬럼 뒤에 여러개 컬럼 추가 (0) | 2020.12.03 |
점차 멀어지는 mysql vs MariaDB (0) | 2020.11.10 |
mysql text 형 어떤 걸 쓸까? (0) | 2020.10.15 |