테이블 관리(file-per-table와 테이블스페이스)

출처 : https://dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

https://dev.mysql.com/doc/refman/8.0/en/general-tablespaces.html

 

Mysql은 기본적으로 database라고 불리는 영역이 존재하고 그 내부에 테이블들이 존재합니다.(engine = InnoDB 기준)

DB : mysql 5.7.43

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| ysbae              |
+--------------------+
5 rows in set (0.00 sec)

 

mysql> use ysbae
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_ysbae |
+-----------------+
| aa              |
| bb              |
| mig_test        |
+-----------------+
3 rows in set (0.01 sec)

 

그리고 해당 테이블들의 물리적인 위치는 $data_dir 밑에 데이터베이스이름 밑에 테이블명.ibd라는 이름으로 파일이 존재하게 됩니다.

 

[root@ysbae ysbae]# pwd
/var/lib/mysql/ysbae
[root@ysbae ysbae]# ls -al
합계 252
drwxr-x---. 2 mysql mysql    102  1월  5 13:49 .
drwxr-x--x. 6 mysql mysql   4096  1월  5 13:42 ..
-rw-r-----. 1 mysql mysql   8554  1월  5 13:43 aa.frm
-rw-r-----. 1 mysql mysql 114688  1월  5 13:43 aa.ibd
-rw-r-----. 1 mysql mysql   8554  1월  5 13:49 bb.frm
-rw-r-----. 1 mysql mysql     65 10월 24 10:43 db.opt
-rw-r-----. 1 mysql mysql   8554 10월 24 10:43 mig_test.frm
-rw-r-----. 1 mysql mysql  98304 10월 24 10:44 mig_test.ibd

 

Mysql은 당연히 이방법밖에 없는줄 알았는데, 일반적으로 알고있는 Tablespace 개념도 있었습니다. 그리고 default로 설정되어 있는 것의 정식명칭은 innodb_file_per_table 였습니다. 파라미터에서 확인하면 기본적으로 on이 되어있습니다.

이번 시간에는 이 두개를 간단히 정리해보겠습니다.

 

 

innodb_file_per_table (default)

InnoDB가 테이블을 생성할 때 기본적으로 사용하는 방식입니다. 해당 파라미터를 비활성화(OFF)하면 시스템 테이블스페이스 공간에 테이블을 생성합니다.

my.cnf 파일에서 파라미터의 내용을 설정하거나 set global 명령어를 이용해서 변경할 수 있습니다.

 

mysql> show variables like '%innodb_file_per_table%'
    -> ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

 

변경예시

###명령어
mysql> SET GLOBAL innodb_file_per_table=ON;

##파일
[mysqld]
innodb_file_per_table=ON

 

사용시 이점

- Truncate / Drop 시 OS의 공간이 반환됩니다. 테이블스페이스의 경우 Truncate / Drop시에는 InnoDB만 사용할 수 있는 여유공간이 반환됩니다. 즉 테이블스페이스의 OS의 물리적인 크기는 줄어들지 않습니다. 

- Truncate Table의 성능이 테이블 스페이스를 사용할 때보다 더 좋습니다.

- I/O최적화,공간관리 또는 백업목적으로 별도의 저장장치에 생성할 수 있습니다.

- innodb_file_per_table의 테이블을 다른 MySQl의 인스턴스에서 가져올 수 있습니다.

- 시스템테이블스페이스에서 지원되지 않는 Row_format Dynamic 및 Compressed 기능을 지원합니다.

- innodb_file_per_table 로 저장된 테이블의 복구 시  MySQL Enterprise Backup를 사용하여 빠르게 백업/복구가 가능합니다.

- 물리적인 파일의 크기를 모니터링하여 테이블의 크기를 확인하기에 용이합니다.

- 테이블스페이스의 크기는 최대 64TB입니다. 이에 비해 innodb_file_per_table 는 파일당 64TB로 테이블의 크기가 충분히 늘어날 수 있는 공간을 제공합니다.

- 일반적인 Linux 파일시스템에서는 innodb_flush_method=O_DIRECT로 설정되어 있을 때 테이블스페이스 데이터파일과 같은 단일 파일에 해서 동시에 쓰기가 제한됩니다(동시 쓰기 허용 x).  innodb_file_per_table  사용시 동시쓰기 성능이 향상될 수 있습니다.

 

사용 시 단점

- 각 테이블별로 파일이 관리되므로 적절하게 사용되지 않으면 공간낭비가 있을 수 있습니다.

- fsync 연산은 파일단위이므로 테이블스페이스가 아닌 각 테이블 파일별로 연산을 해서 fsync 연산의 총 수가 많아질 수 있습니다.

- file-per-table 마다 파일 핸들을 열어야합니다. 

- 더 많은 단편화가 발생할 수 있습니다.

 

Tablespace

테이블스페이스는 다음과 같은 기능을 제공합니다.

- 시스템 테이블스페이스와 유사하게 여러 테이블에 대한 데이터를 저장할 수 있습니다.

- 잠재적인 메모리 이점을 같습니다. 파일별로 메타데이터를 메모리에 유지하는데, file-per-table 보다 파일 갯수가 적으므로 메모리를 더 적게 소비합니다.

- file-per-table과 마찬가지로 독립적인 디렉토리 외부에 생성할 수 있습니다.

- 모든 ROW_FORMAT 형식을 지원합니다.

- file-per-table,테이블스페이스,시스템 테이블스페이스로 ALTER TABLE MOVE가 가능합니다.

 

생성구문

CREATE TABLESPACE tablespace_name
    [ADD DATAFILE 'file_name']
    [FILE_BLOCK_SIZE = value]
        [ENGINE [=] engine_name]

mysql> create tablespace ts_test add datafile '/var/lib/mysql/ts_test01.ibd' engine=InnoDB file_block_size = 8192;
Query OK, 0 rows affected (0.01 sec)

 

테이블 생성구문

mysql> create table bb(a int) tablespace ts_test KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.00 sec)

 

테이블 ALTER 구문

mysql> ALTER TABLE ysbae.aa TABLESPACE ts_test KEY_BLOCK_SIZE=8;
Query OK, 0 rows affected (0.02 sec)

 

ROW_FORMAT?

4가지 옵션이 있습니다.(REDUNDANT, COMPACT, DYNAMIC, COMPRESSED)

일반테이블스페이스에 압축테이블(ROW_FORMAT=COMPRESSED)이 포함되도록 하려면 FILE_BLOCK_SIZE 옵션을 지정해야 하며, FILE_BLOCK_SIZE값은 innodb_page_size값과 관련하여 유효한 압축 페이지 크기여야 합니다.

또한 압축테이블의 물리페이지 크기(KEY_BLOCK_SIZE)는 FILE_BLOCK_SIZE/1024와 같아야합니다.

예를 들어 innodb_page_size=16KB이고 FILE_BLOCK_SIZE=8K인 경우 테이블의 KEY_BLOCK_SIZE는 8이어야 합니다.

 

실제로 위의 구문에서 KEY_BLOCK_SIZE=8을 설정해야 생성이 되는 것은 DB의 innodb_page_size=16384(16KB)로 설정되어 있기때문에 저 구문을 빼면 에러를 뱉으며 생성이 안되었던 거네요.

mysql> show variables like '%innodb_page_size%'
    -> ;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)

 

에러 발생한 구문 및 메세지

mysql> create table bb(a int) tablespace ts_test;
ERROR 1478 (HY000): InnoDB: Tablespace `ts_test` uses block size 8192 and cannot contain a table with physical page size 16384