MYSQL 디스크 용량이 거의 다 찼어요

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