▶Tablespace
▶ System Tablespace
Change buffer를 위한 저장영역입니다. 데이터가 일반 테이블스페이스나 file-per-table 이 아닌 System tablespace에서 생성되고 관리되는 경우 테이블 및 인덱스 데이터도 포함될 수 있습니다.
이전버전(8.0 이전버전)에는 InnoDB의 Data dictionary 정보도 저장하고 있었습니다.
Mysql 8.0버전 부터 InnoDB는 Data dictionary의 Metadata를 저장합니다.
이전버전에는 doublewrite buffer 저장영역도 system tablespace에 포함하고 있었습니다.
8.0.20버전 부터 별도의 doublewrite 파일 영역이 생겨서 그곳에서 관리됩니다.
System Tablespace난 하나 또는 그이상의 데이터파일을 가질 수 있습니다.
하나일 경우 물리적 파일 이름은 ibdata1 입니다.
| -rw-r-----. 1 mysql mysql 12M Jan 11 20:58 ibdata1 |
System Tablespace의 크기와 수는 innodb_data_file_path 파라미터 설정으로 정의됩니다.
innodb_data_file_path의 default 값
mysql> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
1 row in set (0.00 sec) System Tablespace resize 방법
사이즈를 늘리는 방법
1) autoextended값이 설정되어 있는 경우 자동으로 증가합니다.
필요시 8MB씩 자동적으로 증가하게 되며, innodb_autoextend_increment 파라미터로 증가하는 사이즈를 관리할 수 있습니다.
mysql> show variables like '%innodb_autoextend_increment%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_autoextend_increment | 64 |
+-----------------------------+-------+
1 row in set (0.00 sec)
2)다른 데이터파일을 추가하여 사이즈를 늘리는 방법
방법
1.Mysql Server를 중지합니다.
2.innodb_data_file_path의 설정이 autoextend로 설정되어 있으면 그것을 제거하고 현재 데이터파일 사이즈가 반영되도록 사이즈를 수정합니다.
3.새 데이터파일을 innodb_data_file_path에 추가하고, 옵션으로 autoextend 속성을 지정할 수 있습니다.
autoextend 속성은 해당 파라미터의 마지막 데이터파일에만 적용할 수 있습니다.
4.Mysql Server를 시작합니다.
※이미 생성된 System Tablespace의 사이즈를 해당 방법으로 늘릴수는 없습니다.
실제 테스트
ibdata2 12M autoextend인 system tablespace를 하나더 추가하시오
1.Mysql 서버 중지
$systemctl stop mysql.server
2.
$vi /etc/my.cnf
innodb_data_file_path=ibdata1:12M;ibdata2:12M:autoextend
내용 추가
3.Mysql 서버 시작
$systemctl start mysql.server
4.확인
mysql> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------------------+
| Variable_name | Value |
+-----------------------+------------------------------------+
| innodb_data_file_path | ibdata1:12M;ibdata2:12M:autoextend |
+-----------------------+------------------------------------+
1 row in set (0.00 sec)
$ ls -alh ibdata*
-rw-r-----. 1 mysql mysql 12M Jan 11 21:55 ibdata1
-rw-r----- 1 mysql mysql 12M Jan 11 21:55 ibdata2사이즈를 줄이는 방법
존재하는 System Tablespace의 사이즈를 줄이는 기능은 지원하지 않습니다. 오직 Backup 으로부터 새로 Mysql 인스턴스를 생성하여 그곳에서 구성한 작은 용량의 System tablespace을 resotre하는 방법입니다.
System Tablespace가 커지는 것을 피하기 위해서 file-per-table tablespace를 사용할 수도 있습니다.
File-per-table tablespace 구성은 default 타입이고, InnoDB를 생성할때 내부적으로 사용됩니다. File-per-table tablespace는 System Tablespace와 달리 Truncate나 drop을 하면 OS의 디스크 용량이 반환됩니다.
※ Raw disk를 System Tablespace에 사용할 수도 있습니다.
▶ File-Per-Table Tablespace
데이터와 인덱스를 포함하여 자체의 데이터파일로 OS에 저장됩니다.
아래의 예를 확인해보면 5개의 테이블이 존재하고 5개의 ibd파일도 5개가 존재합니다.
mysql> show tables
-> ;
+----------------+
| Tables_in_test |
+----------------+
| imptest |
| professor |
| student |
| t1 |
| test_dummy |
+----------------+
5 rows in set (0.00 sec)
$ ls -alrth
total 141M
-rw-r----- 1 mysql mysql 112K Dec 14 02:19 professor.ibd
-rw-r----- 1 mysql mysql 112K Dec 14 02:19 student.ibd
-rw-r----- 1 mysql mysql 140M Dec 31 01:06 test_dummy.ibd
-rw-r----- 1 mysql mysql 112K Jan 5 21:50 imptest.ibd
drwxr-x--- 2 mysql mysql 101 Jan 11 20:58 .
-rw-r----- 1 mysql mysql 112K Jan 11 20:58 t1.ibd
drwxr-x---. 9 mysql mysql 4.0K Jan 11 21:55 ..
InnoDB는 Table을 생성 시 기본적으로 File-Per-Table Tablespace로 생성합니다. innodb_file_per_table 변수를 통해서 관리할 수 있습니다. 해당 파라미터를 사용하지 않으면 InnoDB는 System Tablespace에 생성됩니다.
my.cnf로 파라미터를 변경하거나 set global 명령어를 사용하여 설정을 변경할 수 있습니다.
mysql> show variables like 'innodb_file_per_table'
-> ;
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
장점
- file-per-table tablespace로 생성 된 테이블을 truncate하거나 drop할 시 OS의 디스크 용량이 반환됩니다.
(shared tablespace 형식은 반환되지 않음)
- Truncate Table 명령어에 대한 성능이 뛰어납니다.
- I/O 최적화, 공간관리, 백업 목적 등으로 별도의 스토리지 디바이스에 File-per-table tablespace를 생성할 수 있습니다.
- 다른 MySQL 인스턴스로부터 있는 file-per-table tablespace를 import할 수 있습니다.
- raw format으로 DYNAMIC과 COMPRESSED 옵션을 제공합니다.
- MySQL 재시작 불가, binlog 사용불가,data corruption 등 발생시 복구를 빠르게 할 수 있고 시간을 절약할 수 있습니다
- MySQL Enterprise Backup을 사용하여 빠르게 백업&복구를 할 수 있습니다.
- 테이블 별로 데이터파일이 있으므로 OS 상에서 테이블 크기를 모니터링할 수 있습니다.
- 일반적인 Linux 파일시스템은 innodb_flush_method=O_DIRECT로 설정된 shared tablespace datafile 같은 단일 데이터 파일에 동시 쓰기가 허용되어 있지 않습니다. 하지만 file-per-table tablespace는 이 설정과 함께 사용할 때 성능이 향상될 수 있습니다.
- tablespace 크기는 64TB로 제한이 있습니다. file-per-table tablespace는 각 파일당 64TB까지 커질 수 있습니다.
단점
- 공간낭비가 생길 수 있습니다. Tablespace의 공간은 그 Table의 데이터만 사용할 수 있기 때문입니다.
- fsync 작업은 단일 데이터 파일 대신 여러 데이터파일에서 수행합니다. fsync작업은 파일 단위 작업이기 때문에 fsync작업의 총 수행 횟수가 결과적으로 늘어날 수 있습니다.
- mysqld는 각 file-per-table tablespace에 대해 파일의 관리를 할 수 있어야되는데, 관리해야하는 파일 수가 늘어나면 성능에 영향을 줄 수도 있습니다.
- 각 테이블에 자체 데이터파일이 있는경우 더 많은 파일 설명자가 필요합니다.
- DROP TABLE이나 Table SCAN 성능에 방해가 되는 단편화가 더 많이 발생할 가능성이 있습니다.
- file-per-table tablespace에 있는 테이블을 drop할 때 버퍼 풀이 스캔됩니다. 큰 버퍼풀의 경우 시간이 더 소요될 수 있습니다.
- shared tablespace가 full이 되었을 때 자동으로 크기를 확장해주는 innodb_autoextend_increment설정이 적용되지 않습니다. (해당 파라미터가 설정되 있더라도)
file-per-table tabpeslace는 4 MB씩 증가합니다.
▶ General Tablespace
CREATE TABLE 구문을 사용하여 생성하는 InnoDB의 Shared Tablespace입니다.
System Tablespace와 유사하여 여러 Table을 저장할 수 있는 tablespace입니다.
General Tablespace는 file-per-table tablespace보다 메모리 이점이 있습니다. 서버는 테이블스페이스의 수명동안 메타데이터를 저장해 놓는데, 별도의 테이블 당 테이블스페이스가 존재하는 테이블보다 메타데이터에 대한 메모리를 덜 소비합니다.
general tablespace 데이터 파일은 MySQL 데이터와 관련되거나 독립적인 디렉토리에 배치될 수 있습니다.
general tablespace는 모든 테이블 row format에 대한 기능을 지원합니다.
Tablespace 옵션을 ALTER TABLE과 함께 사용하여 general tablespace, file-per-table tablespace, system tablespace간에 테이블을 이동할 수 있습니다.
생성
데이터 파일의 경로를 지정해주지 않으면 datadir로 설정된 경로 밑에 생성됩니다. 물론 직접 경로를 지정해 줄 수도 있습니다.
mysql> create tablespace ts1 add datafile 'ts1.ibd' Engine=InnoDB;
Query OK, 0 rows affected (0.01 sec)
-rw-r----- 1 mysql mysql 112K Jan 12 00:48 ts1.ibd
--테이블스페이스에 테이블 추가
test 스키마에 tablespace ts1에 t1테이블을 생성합니다.
mysql> create table test.t1 (c1 int primary key) tablespace ts1;
Query OK, 0 rows affected (0.02 sec)
기존에 있는 test스키마의 imptest를 ts1테이블스페이스로 이동합니다.
mysql> alter table test.imptest tablespace ts1;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
삭제
DROP TABLESPACE문은 InnoDB general tablespace를 삭제하는데 사용됩니다.
DROP TABLESPACE 전에 테이블스페이스가 소유한 모든 테이블을 삭제해야 합니다. 테이블스페이스가 비어 있지 않으면 에러를 발생시킵니다.
mysql> select a.name name,b.name as table_name from information_schema.innodb_tablespaces a,information_schema.innodb_tables b where a.space=b.space and a.name like 'ts1';
+----------+--------------+
| spacname | table_name |
+----------+--------------+
| ts1 | test/imptest |
| ts1 | test/t1 |
+----------+--------------+
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
-imptest테이블은 있는 상태에서 삭제시도 에러발생
mysql> drop tablespace ts1;
ERROR 3120 (HY000): Tablespace `ts1` is not empty.
- imptest를 file-per-table tablespace로 변경
mysql> alter table test.imptest tablespace=innodb_file_per_table;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select a.name name,b.name as table_name from information_schema.innodb_tablespaces a,information_schema.innodb_tables b where a.space=b.space and a.name like 'ts1';
Empty set (0.00 sec)
-삭제
mysql> drop tablespace ts1;
Query OK, 0 rows affected (0.01 sec)
general tablespace는 특정 database(스키마)에 속하지 않습니다. DROP DATABASE명령어는 general tablespace에 속하는 테이블을 삭제할 수 있지만, 모든 테이블을 삭제하더라도 tablespace는 삭제되지 않습니다. 반드시 명시적으로 DROP TABLESPACE tablespace_name이름을 명시해줘야 합니다.
제한사항
- 존재하는 테이블스페이스를 general 로 변경할 수 없습니다.
- Temp general 테이블스페이스 생성은 지원하지 않습니다
- temp 테이블을 지원하지 않습니다.
- file-per-table tablespace와 다르게 truncate나 drop 시 OS의 디스크로 반환되지 않습니다.
- ALTER TABLE ... DISCARD TABLESPACE 및 ALTER TABLE ... IMPORT TABLESPACE가 지원되지 않습니다.
- MySQL 8.0.21부터 UNDO 테이블스페이스를 general tablespace에 생성할 수 없습니다.
▶ UNDO Tablespace
undo log가 포함되어 있으며, undo log는 클러스터드 인덱스 레코드의 트랜잭션의 가장 최근 변경정보 실행취소하는 정보(즉, 변경 전 정보)를 포함하고 있습니다. undo log는 undo log segment내에 존재하고 rollback segment내에 존재합니다.
innodb_rollback_segments 파라미터로 undo 테이블스페이스에 할당된 rollback segment의 수를 확인할 수 있습니다.
mysql> show variables like 'innodb_rollback_segments';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_rollback_segments | 128 |
+--------------------------+-------+
1 row in set (0.00 sec)
MySQL 인스턴스가 초기화되면 default로 두개의 UNDO 테이블스페이스가 생성됩니다.
UNDO 테이블스페이스의 자동 truncate를 지원하기 위해 최소 2개의 UNDO 테이블스페이스가 필요합니다.
innodb_undo_directory 파라미터에 설정된 위치에 생성되며 undo_001,undo_002 의 이름으로 생성됩니다.
MySQL 8.0.14부터 SQL을 통해서 운영 중에 UNDO 테이블스페이스를 추가할 수 있습니다.
| CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu'; |
UNDO 테이블스페이스 이름은 INFORMATION_SCHEMA의 FILES테이블에서 확인할 수 있습니다.
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
-> WHERE FILE_TYPE LIKE 'UNDO LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+
2 rows in set (0.00 sec)
생성
mysql> create undo tablespace undotest add datafile 'undotest.ibu';
Query OK, 0 rows affected (0.29 sec)
확인
mysql> select tablespace_name,file_name from information_schema.files where file_type like 'UNDO_LOG';
+-----------------+----------------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+----------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
| undotest | ./undotest.ibu |
+-----------------+----------------+
3 rows in set (0.01 sec)
삭제
mysql> alter undo tablespace undotest set inactive;
Query OK, 0 rows affected (0.01 sec)
mysql> drop undo tablespace undotest;
Query OK, 0 rows affected (0.01 sec)
mysql> select tablespace_name,file_name from information_schema.files where file_type like 'UNDO_LOG';
+-----------------+------------+
| TABLESPACE_NAME | FILE_NAME |
+-----------------+------------+
| innodb_undo_001 | ./undo_001 |
| innodb_undo_002 | ./undo_002 |
+-----------------+------------+
2 rows in set (0.00 sec)
▶ Temporary Tablespace
InnoDB는 session temporary tablespace와 global temporary tablespace로 사용합니다.
session temporary tablespace
사용자가 생성한 임시 테이블과 내부 임시테이블을 저장합니다. InnoDB가 on-disk 내부용 스토리지 엔진으로 구성될 때 옵티마이저에 의해 생성됩니다.
MySQL 8.0.16부터 임시테이블은 항상 InnoDB입니다. (이전에는 internal_tmp_disk_storage_engine 값으로 설정되었습니다)
세션 임시 테이블스페이스는 아래의 파라미터(innodb_temp_tablespaces_dir)의 경로에 .ibt형식으로 생성됩니다.
mysql> show variables like '%innodb_temp%';
+-----------------------------+-----------------------+
| Variable_name | Value |
+-----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_temp_tablespaces_dir | ./#innodb_temp/ |
+-----------------------------+-----------------------+
2 rows in set (0.00 sec)
$ ls -al
total 168
drwxr-x---. 2 mysql mysql 4096 Jan 11 21:55 .
drwxr-x---. 9 mysql mysql 4096 Jan 12 01:52 ..
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_10.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_1.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_2.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_3.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_4.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_5.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_6.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_7.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_8.ibt
-rw-r----- 1 mysql mysql 81920 Jan 11 21:55 temp_9.ibt
global temporary tablespace
ibtmp1 파일이름으로 되어있으며 사용자가 만든 임시 테이블의 변경사항에 대한 rollback segment를 저장합니다.
정상 종료 또는 abort 초기화, 재시작할때 제거됩니다. global temporary tablespace는 생성될 때 동적으로 공간 ID를 생성합니다. DBA는 수동으로 이 파일을 삭제하고 서버를 새시작할 수 있습니다. 재시작하면 자동으로 파일이 생성됩니다.
default로 설정된 파라미터입니다. 12MB의 크기를 가지고 있으며 autoextend입니다.
mysql> show variables like 'innodb_temp_data_file_path';
+----------------------------+-----------------------+
| Variable_name | Value |
+----------------------------+-----------------------+
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
+----------------------------+-----------------------+
1 row in set (0.00 sec)
삭제
# rm -rf ibtmp1
# ls -al ibtmp1
ls: cannot access ibtmp1: No such file or directory
재기동
# systemctl stop mysql.server
# systemctl start mysql.server
확인
# ls -al ibtmp1
-rw-r----- 1 mysql mysql 12582912 Jan 12 02:46 ibtmp1
아키텍쳐적으로 정리하고 싶었는데 매뉴얼에 그것 자체에 대한 설명과 사용법에 대한 설명이 많아서 정리하면서도 도움이 될까 생각이 많이 들었네요..
추가적으로 정보가 새기면 포스팅하겠습니다.
출처 : MySQL 8.0 Reference Manual , myinfrabox.tistory.com/47?category=814732
'다른 DBMS > MySQL&MariaDB' 카테고리의 다른 글
| InnoDB Redo Log (0) | 2021.01.15 |
|---|---|
| InnoDB Doublewrite Buffer (0) | 2021.01.14 |
| InnoDB IN-MEMORY Structures (0) | 2021.01.12 |
| Mysql 로그 종류 (0) | 2021.01.09 |
| InnoDB 소개 (0) | 2021.01.08 |
댓글