[MySQL][InnoDB] 테이블스페이스

■ 시스템 테이블 스페이스

시스템 테이블 스페이스는 InnoDB 데이터 딕셔너리, 이중 쓰기 버퍼, 변경 버퍼 및 실행 취소 로그의 저장 영역입니다. 테이블이 파일당 또는 일반 테이블 스페이스가 아닌 시스템 테이블 스페이스에서 테이블을 작성하는 경우 테이블 및 인덱스 데이터도 포함 할 수 있습니다.

 

시스템 테이블 스페이스에는 하나 이상의 데이터 파일이 있을 수 있습니다. 기본적으로 ibdata1이라는 단일 시스템 테이블 스페이스 데이터 파일이 데이터 디렉토리에 작성됩니다. 시스템 테이블 스페이스 데이터 파일의 크기와 수는 innodb_data_file_path 시작 옵션으로 정의됩니다.

 

시스템 테이블 스페이스에 대한 추가 정보는 섹션의 다음 주제에서 제공됩니다.

- 시스템 테이블 스페이스 크기 조정

- 시스템 테이블 스페이스에 원시 디스크 파티션 사용

 

▶ 시스템 테이블 스페이스 크기 조정

이 절에서는 시스템 테이블 스페이스의 크기를 늘리거나 줄이는 방법에 대해 설명합니다.

 

• 시스템 테이블 스페이스의 크기 증가

시스템 테이블 스페이스의 크기를 늘리는 가장 쉬운 방법은 자동 확장되도록 구성하는 것입니다. 그렇게하려면 innodb_data_file_path 설정에서 마지막 데이터 파일에 대한 autoextend 속성을 지정하고 서버를 다시 시작합니다. 예를 들면 다음과 같습니다.

innodb_data_file_path=ibdata1:10M:autoextend

autoextend 속성이 지정되면 공간이 필요할 때 데이터 파일의 크기가 자동으로 8MB 씩 증가합니다. innodb_autoextend_increment 변수는 증가 크기를 제어합니다.

 

다른 데이터 파일을 추가하여 시스템 테이블 스페이스 크기를 늘릴 수도 있습니다. 다음과 같은 순서로 진행합니다.

1. MySQL 서버를 중지합니다.

 

2. innodb_data_file_path 설정의 마지막 데이터 파일이 autoextend 속성으로 정의된 경우 이를 제거하고 현재 데이터 파일 크기를 반영하도록 size 속성을 수정합니다. 지정할 적절한 데이터 파일 크기를 결정하려면 파일 시스템의 파일 크기를 확인하고 해당 값을 가장 가까운 MB값으로 내립니다.(MB는 1024 x 1024).

 

3. 새 데이터 파일을 innodb_data_file_path 설정에 추가하고, 선택적으로 autoextend 속성을 지정합니다. autoextend 속성은 innodb_data_file_path 설정에서 마지막 데이터 파일에 대해서만 지정할 수 있습니다.

 

4. MySQL 서버를 시작합니다.

 

예를 들어, 이 테이블 스페이스에는 하나의 자동 확장 데이터 파일이 있습니다.

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

 

데이터 파일이 시간이 지남에 따라 988MB로 증가했다고 가정합니다. 현재 데이터 파일 크기를 반영하도록 size 속성을 수정 한 후 새 50MB 자동 확장 데이터 파일을 지정한 후 innodb_data_file_path 설정입니다.

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

새 데이터 파일을 추가 할 때 기존 파일 이름을 지정하지 않습니다. InnoDB는 서버를 시작할 때 새 데이터 파일을 생성하고 초기화합니다.

 

▶ 참고

size 속성을 변경하여 기존 시스템 테이블스페이스의 데이터 파일 크기를 늘릴 수 없습니다. 예를 들어, innodb_data_file_path 설정을 ibdata1:10M:autoextend에서 ibdata1:12M:autoextend로 변경하면 서버를 시작할 때 다음 오류가 발생합니다.

 

[ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system 
data file './ibdata1' is of a different size 640 pages (rounded down to MB) than 
specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!

 

이 오류는 기존 데이터 파일 크기 (InnoDB 페이지로 표시)가 구성 파일에 지정된 크기와 다르다는 것을 나타냅니다. 이 오류가 발생하면 이전 innodb_data_file_path설정을 복원하고 시스템 테이블 스페이스 크기 조정 지시 사항을 참고합니다.

InnoDB 페이지 크기는 innodb_page_size 변수에 의해 정의됩니다. 기본값은 16384 바이트입니다.

 

 

• InnoDB 시스템 테이블 스페이스의 크기 감소

시스템 테이블 스페이스에서 데이터 파일을 제거 할 수 없습니다. 시스템 테이블 스페이스 크기를 줄이려면 다음 절차를 수행합니다.

 

1. mysqldump를 사용하여 mysql 스키마에있는 InnoDB 테이블을 포함하여 모든 InnoDB 테이블을 덤프합니다. 다음 쿼리를 사용하여 mysql 스키마에서 InnoDB 테이블을 식별합니다.

mysql> SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='mysql' and ENGINE='InnoDB';
+---------------------------+
| TABLE_NAME                |
+---------------------------+
| engine_cost               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
+---------------------------+

2. 서버를 중지합니다.

3. ibdata 및 ib_log 파일을 포함하여 기존 테이블 스페이스 파일 (* .ibd)을 모두 제거합니다. mysql 스키마에있는 테이블의 *.ibd 파일을 반드시 제거해야 합니다.

4. InnoDB 테이블의 .frm 파일을 제거합니다.

5. 새 시스템 테이블 스페이스에 대한 데이터 파일을 구성합니다. 시스템 테이블 스페이스 데이터 파일 구성을 참조합니다.

6. 서버를 다시 시작합니다.

7. 덤프 파일을 가져옵니다.

 

노트

데이터베이스가 InnoDB 엔진만 사용하는 경우 모든 데이터베이스를 덤프하고 서버를 중지하고 모든 데이터베이스와 InnoDB 로그 파일을 제거하고 서버를 다시 시작한 후 덤프 파일을 가져 오는 것이 더 간단 할 수 있습니다.

 

대형 시스템 테이블 스페이스를 피하려면 데이터에 테이블당 테이블 스페이스를 사용합니다. 테이블당 파일 공간은 기본 테이블 공간 유형이며 InnoDB 테이블을 생성할 때 암시적으로 사용됩니다. 시스템 테이블 스페이스와 달리 테이블당 테이블 스페이스에서 작성된 테이블을 잘라내거나 삭제한 후 디스크 공간이 운영 체제로 리턴됩니다. 자세한 정보는 아래 테이블 당 파일 테이블 스페이스를 참조하세요.

 

▶ 시스템 테이블 스페이스에 원시 디스크 파티션 사용

원시 디스크 파티션을 InnoDB 시스템 테이블 스페이스에서 데이터 파일로 사용할 수 있습니다. 이 기술은 파일 시스템 오버헤드 없이 Windows와 일부 Linux 및 Unix 시스템에서 버퍼되지 않은 I/O를 가능하게합니다. 원시 파티션 유무에 관계없이 테스트를 수행하여이 변경이 실제로 시스템 성능을 향상시키는지 확인해보아야 합니다.

 

원시 디스크 파티션을 사용하는 경우 MySQL 서버를 실행하는 사용자 ID에 해당 파티션에 대한 읽기 및 쓰기 권한이 있는지 확인합니다. 예를 들어, 서버를 mysql 사용자로 실행하는 경우 mysql에서 파티션을 읽고 쓸 수 있어야합니다. --memlock 옵션을 사용하여 서버를 실행하는 경우 서버를 루트로 실행해야하므로 루트로 파티션을 읽고 쓸 수 있어야합니다.

 

아래에 설명된 절차에는 옵션 파일 수정이 포함됩니다.

• Linux 및 Unix 시스템에서 원시 디스크 파티션 할당

1. 새 데이터 파일을 작성할 때 innodb_data_file_path 옵션의 데이터 파일 크기 바로 뒤에 키워드 newraw를 지정합니다. 파티션은 최소한 지정한 크기보다 커야 합니다. InnoDB의 1MB는 ​​1024×1024 바이트인 반면 디스크 사양의 1MB는 ​​일반적으로 1,000,000 바이트를 의미합니다.

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw

 

2. 서버를 다시 시작합니다. InnoDB는 newraw 키워드를 확인하고 새 파티션을 초기화합니다. 그러나 아직 InnoDB 테이블을 만들거나 변경하지 않습니다. 그렇지 않으면 다음에 서버를 다시 시작할 때 InnoDB가 파티션을 다시 초기화하고 변경 내용이 손실됩니다. 안전 조치로 InnoDB는 newraw가있는 파티션을 지정할 때 사용자가 데이터를 수정하지 못하게 합니다.

 

3. InnoDB가 새 파티션을 초기화한 후 서버를 중지하고 데이터 파일 사양에서 newraw를 raw로 변경합니다.

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw

 

4. 서버를 다시 시작합니다. 이제 InnoDB 변경이 가능합니다.

 

• Windows에서 원시 디스크 파티션 할당

Windows 시스템에서는 innodb_data_file_path 설정이 Windows에서 약간 다르다는 점을 제외하고 Linux 및 Unix 시스템에 대해 설명된 것처럼 동일한 단계와 같은 지침이 적용됩니다.

 

새 데이터 파일을 작성할 때 innodb_data_file_path 옵션의 데이터 파일 크기 바로 뒤에 키워드 newraw를 지정합니다.

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Gnewraw
The //./ corresponds to the Windows syntax of \\.\ for accessing physical drives. In the example above, D: is the drive letter of the partition.

//./는 실제 드라이브에 액세스하기위한 \\. \의 Windows 구문에 해당합니다. 위의 예에서 D :는 파티션의 드라이브 문자입니다.

 

서버를 다시 시작하합니다. InnoDB는 newraw 키워드를 확인하고 새 파티션을 초기화합니다.

 

InnoDB가 새 파티션을 초기화한 후 서버를 중지하고 데이터 파일 사양에서 newraw를 raw로 변경합니다.

[mysqld]
innodb_data_home_dir=
innodb_data_file_path=//./D::10Graw

 

Restart the server. InnoDB now permits changes to be made.

서버를 재시작합니다. InnoDB는 이제 만들어진것에 대해 변경을 허용합니다.

 

 

 

■ 테이블당 파일 테이블스페이스

 

테이블당 파일 테이블스페이스는 단일 InnoDB 테이블에 대한 데이터 및 인덱스를 포함하며 파일 시스템에 자체 데이터 파일로 저장됩니다.

 

테이블당 파일 테이블스페이스의 특성은 이 섹션의 다음 주제에서 설명합니다.

+ File-Per-Table Tablespace Configuration

+ File-Per-Table Tablespace Data Files

+ File-Per-Table Tablespace Advantages

+ File-Per-Table Tablespace Disadvantages

 

▶ 테이블당 파일 테이블스페이스 설정

InnoDB는 기본적으로 테이블당 파일 테이블스페이스에 테이블을 생성합니다. 이 동작은 innodb_file_per_table 변수에 의해 제어됩니다. innodb_file_per_table을 비활성화하면 InnoDB가 시스템 테이블 스페이스에 테이블을 만듭니다.

 

옵션 파일에 innodb_file_per_table 설정을 지정하거나 SET GLOBAL 문을 사용하여 런타임시 구성 할 수 있습니다. 런타임시 설정을 변경하면 전역 시스템 변수를 설정할 수 있는 권한이 필요합니다.

 

옵션파일 : 

[mysqld]
innodb_file_per_table=ON

 

런타임시 SET GLOBAL 사용 :

mysql> SET GLOBAL innodb_file_per_table=ON;

innodb_file_per_table은 MySQL 5.6 이상에서 기본적으로 활성화되어 있습니다. 이전 버전의 MySQL과 호환성이 문제가 될 경우 이를 비활성화하는 것을 고려할 수 있습니다.

 

경고

innodb_file_per_table을 사용하지 않으면 테이블 복사 ALTER TABLE 조작이 시스템 테이블 스페이스에있는 테이블을 테이블당 파일 테이블 스페이스로 내부적으로 이동하지 못합니다. 테이블 복사 ALTER TABLE 조작은 현재 innodb_file_per_table 설정을 사용하여 테이블을 재작성합니다. 이 동작은 보조 인덱스를 추가하거나 삭제할 때 적용되지 않으며 INPLACE 알고리즘을 사용하는 ALTER TABLE 조작 또는 CREATE TABLE ... TABLESPACE 또는 ALTER TABLE ... TABLESPACE 구문을 사용하여 시스템 테이블 스페이스에 추가 된 테이블에도 적용되지 않습니다.

 

▶ 테이블당 파일 테이블스페이스 데이터 파일

테이블 당 파일 공간은 MySQL 데이터 디렉토리 아래의 스키마 디렉토리에있는 .idb 데이터 파일에 작성됩니다. .ibd 파일의 이름은 테이블 (table_name.ibd)입니다. 예를 들어, test.t1 테이블의 데이터 파일은 MySQL 데이터 디렉토리 아래의 테스트 디렉토리에 작성됩니다.

mysql> USE test;  
mysql> CREATE TABLE t1 (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(100) 
 ) ENGINE = InnoDB; 

shell> cd /path/to/mysql/data/test
shell> ls 
t1.ibd

CREATE TABLE 문의 DATA DIRECTORY 절을 사용하여 데이터 디렉토리 외부에서 테이블당 파일 공간 테이블 데이터 파일을 내부에서 사용할 수 있도록 작성할 수 있습니다.

 

▶ 테이블당 파일 테이블 스페이스 장점

테이블당 파일 테이블스페이스는 시스템 테이블스페이스 또는 일반 테이블스페이스와 같은 공유 테이블스페이스에 비해 다음과 같은 장점이 있습니다.

+ 테이블당 파일 테이블 스페이스에서 작성된 테이블을 자르거나 삭제한 후 디스크 공간이 운영 체제로 리턴됩니다. 공유 테이블 스페이스에 저장된 테이블을 잘라내거나 삭제하면 공유 테이블 스페이스 데이터 파일 내에 여유 공간이 만들어지며 InnoDB 데이터에만 사용할 수 있습니다. 즉, 테이블이 잘리거나 삭제 된 후에 공유 테이블 스페이스 데이터 파일의 크기가 줄어들지 않습니다.

+ 공유 테이블 스페이스에 있는 테이블에 대한 테이블 복사 ALTER TABLE 조작은 테이블 스페이스가 차지하는 디스크 공간의 양을 늘릴 수 있습니다. 이러한 작업에는 테이블의 데이터 + 인덱스만큼 많은 추가 공간이 필요할 수 있습니다. 이 공간은 테이블 당 파일 테이블스페이스용이므로 운영 체제로 다시 릴리스되지 않습니다.

+ 테이블당 테이블 스페이스에 있는 테이블에서 TRUNCATE TABLE문이 실행될 때 성능이 향상됩니다.

+ I/O 최적화, 스페이스 관리 또는 백업 목적으로 별도의 스토리지 장치에서 테이블당 파일 공간 데이터 파일을 작성할 수 있습니다.

+ 다른 MySQL 인스턴스에서 테이블당 파일 공간에 있는 테이블을 가져올 수 있습니다.

+ 테이블당 파일 공간으로 작성된 테이블은 바라쿠다 파일 형식을 사용합니다. 바라쿠다 파일 형식을 사용하면 DYNAMIC 및 COMPRESSED 행 형식과 관련된 기능을 사용할 수 있습니다.

+ 개별 테이블 스페이스 데이터 파일에 저장된 테이블은 데이터 손상이 발생하거나 백업 또는 이진 로그를 사용할 수 없거나 MySQL 서버 인스턴스를 다시 시작할 수 없는 경우 시간을 절약하고 성공적인 복구 기회를 개선 할 수 있습니다.

+ 다른 InnoDB 테이블 사용을 중단하지 않고도 MySQL Enterprise Backup을 사용하여 테이블 당 파일 공간 테이블 스페이스에서 생성 된 테이블을 신속하게 백업 또는 복원할 수 있습니다. 이는 다양한 백업 스케줄이 있거나 덜 자주 백업해야하는 테이블에 유용합니다.

+ 테이블당 파일 공간은 테이블 공간 데이터 파일의 크기를 모니터링하여 파일 시스템에서 테이블 크기를 모니터링 할 수있게합니다.

+ 공통 Linux 파일 시스템은 innodb_flush_method가 O_DIRECT로 설정된 경우 공유 테이블 스페이스 데이터 파일과 같은 단일 파일에 대한 동시 쓰기를 허용하지 않습니다. 결과적으로이 설정과 함께 테이블 당 파일 테이블 스페이스를 사용할때 성능이 향상 될 수 있습니다.

+ 공유 테이블 스페이스의 테이블 크기는 64TB 테이블 스페이스 크기 제한으로 제한됩니다. 이에 비해 각 테이블 당 파일 공간에는 64TB 크기 제한이 있어 개별 테이블당 충분한 공간을 제공합니다.

 

▶ 테이블당 파일 테이블스페이스의 단점

테이블당 파일 테이블스페이스는 시스템 테이블스페이스 또는 일반 테이블 스페이스와 같은 공유 테이블 스페이스와 비교할 때 다음과 같은 단점이 있습니다.

+ 테이블당 파일 테이블 스페이스를 사용하면 각 테이블에 동일한 테이블의 행에서만 사용할 수있는 사용되지 않은 공간이있을 수 있으므로 제대로 관리하지 않으면 공간이 낭비 될 수 있습니다.

+ 단일 동기화 테이블스페이스 데이터 파일 대신 여러 테이블당 파일 데이터 파일에서 fsync 작업이 수행됩니다. fsync 작업은 파일 단위이므로 여러 테이블에 대한 쓰기 작업을 결합할 수 없으므로 총 fsync 작업 수가 더 많아 질 수 있습니다.

+ mysqld는 각 테이블당 테이블 스페이스에 대해 열린 파일 핸들을 유지해야하며, 이는 테이블 당 테이블 스페이스에 많은 테이블이있는 경우 성능에 영향을 줄 수 있습니다.

+ 각 테이블에 자체 데이터 파일이 있는 경우 더 많은 파일 설명자가 필요합니다.

+ 조각화가 더 많이 발생하여 DROP TABLE 및 테이블 스캔 성능을 방해 할 수 있습니다. 그러나 조각화가 관리되면 테이블당 파일 공간이 이러한 조작의 성능을 향상시킬 수 있습니다.

+ 테이블당 파일 테이블 스페이스에 있는 테이블을 제거할 때 버퍼 풀이 스캔되며, 큰 버퍼풀의 경우 몇 초가 걸릴 수 있습니다. 스캔은 넓은 내부 잠금으로 수행되므로 다른 작업이 지연 될 수 있습니다.

+ 자동 확장 공유 테이블 스페이스 파일이 가득 찼을 때 크기를 확장하기 위한 증분 크기를 정의하는 innodb_autoextend_increment 변수는 innodb_autoextend_increment 설정에 상관없이 자동 확장되는 테이블당 파일 테이블 스페이스 파일에는 적용되지 않습니다. 초기 테이블당 테이블 스페이스 확장자는 적은 양이며, 그 후 확장자는 4MB 씩 증가합니다.

 

■ 일반 테이블스페이스

일반 테이블 스페이스는 CREATE TABLESPACE 구문을 사용하여 생성된 공유 InnoDB 테이블 스페이스입니다. 일반 테이블스페이스 기능 및 특징은 아래에서 계속 설명합니다.

 

- 일반 테이블 스페이스 기능

- 일반 테이블 스페이스 생성

- 일반 테이블 스페이스에 테이블 추가

- 일반 테이블 스페이스 행 형식 지원

- ALTER TABLE을 사용하여 테이블 스페이스 간 테이블 이동

- 일반 테이블 스페이스 삭제

- 일반 테이블 스페이스 제한

 

▶ 일반 테이블 스페이스 기능

일반 테이블 스페이스 기능은 다음 기능을 제공합니다.

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

 

- 일반 테이블 스페이스는 테이블당 파일 테이블 스페이스보다 잠재적인 메모리 이점이 있습니다. 서버는 테이블 스페이스 수명 동안 테이블 스페이스 메타 데이터를 메모리에 유지합니다. 적은 수의 일반 테이블 스페이스에있는 여러 테이블은 별도의 테이블당 테이블 스페이스에 있는 동일한 수의 테이블보다 테이블 스페이스 메타 데이터에 대한 메모리를 덜 소비합니다.

 

- 일반 테이블 스페이스 데이터 파일은 MySQL 데이터 디렉토리와 관련되거나 독립적인 디렉토리에 배치 될 수 있으며, 이는 테이블당 테이블스페이스의 많은 데이터 파일 및 스토리지 관리 기능을 제공합니다. 테이블당 테이블스페이스과 마찬가지로, MySQL 데이터 디렉토리 외부에 데이터 파일을 배치하는 기능을 사용하면 중요한 테이블의 성능을 개별적으로 관리하고 특정 테이블에 대해 RAID 또는 DRBD를 설정하거나 테이블을 특정 디스크에 바인딩 할 수 있습니다.

 

일반 테이블 스페이스는 안테로프(Antelope) 및 바라쿠다(Barracuda) 파일 형식을 모두 지원하므로 모든 테이블행 형식 및 관련 기능을 지원합니다. 두 파일 형식을 모두 지원하므로 일반 테이블 스페이스는 innodb_file_format 또는 innodb_file_per_table 설정에 의존하지 않으며 이러한 변수가 일반 테이블 스페이스에 영향을 미치지 않습니다.

 

-TABLESPACE 옵션을 CREATE TABLE과 함께 사용하여 일반 테이블 스페이스, 테이블당 파일 테이블 스페이스 또는 시스템 테이블 스페이스에 테이블을 작성할 수 있습니다.

 

-TABLESPACE 옵션을 ALTER TABLE과 함께 사용하여 일반 테이블 스페이스, 테이블당 파일 테이블 스페이스 및 시스템 테이블 스페이스간에 테이블을 이동할 수 있습니다. 이전에는 테이블당 파일 테이블 스페이스에서 시스템 테이블 스페이스로 테이블을 이동할 수 없었습니다. 일반 테이블 스페이스 기능을 사용하면 가능합니다.

 

▶ 일반 테이블 스페이스 생성

일반 테이블 스페이스는 CREATE TABLESPACE 구문을 사용하여 작성됩니다.

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

 

일반 테이블 스페이스는 데이터 디렉토리 또는 외부에 작성할 수 있습니다. 내재적으로 작성된 테이블당 테이블 스페이스와의 충돌을 피하기 위해 데이터 디렉토리 아래의 서브 디렉토리에 일반 테이블 스페이스 작성은 지원되지 않습니다. 데이터 디렉토리 외부에 일반 테이블 스페이스를 작성할 때 테이블 스페이스를 작성하기 전에 디렉토리가 존재해야 합니다.

 

.isl 파일은 일반 테이블 스페이스가 MySQL 데이터 디렉토리 외부에 작성 될 때 MySQL 데이터 디렉토리에 작성됩니다.

 

예제)

데이터 디렉토리에 일반 테이블 스페이스 작성 :

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

 

데이터 디렉토리 외부의 디렉토리에 일반 테이블 스페이스 작성 :

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;

 

테이블 스페이스 디렉토리가 데이터 디렉토리 아래에 있지 않으면 데이터 디렉토리에 상대적인 경로를 지정할 수 있습니다. 이 예에서 my_tablespace디렉토리는 데이터 디렉토리와 동일한 레벨에 있습니다.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../my_tablespace/ts1.ibd' Engine=InnoDB;

 

노트

ENGINE = InnoDB 절은 CREATE TABLESPACE 문의 일부로 정의되거나 InnoDB는 기본 스토리지 엔진 (default_storage_engine = InnoDB)으로 정의되어야합니다.

 

▶ 일반 테이블 스페이스에 테이블 추가

InnoDB 일반 테이블 스페이스를 생성 한 후 다음 예제와 같이 CREATE TABLE tbl_name ... TABLESPACE [=] tablespace_name 또는 ALTER TABLE tbl_name TABLESPACE [=] tablespace_name을 사용하여 테이블 스페이스에 테이블을 추가 할 수 있습니다.

 

CREATE TABLE:

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

 

ALTER TABLE:

mysql> ALTER TABLE t2 TABLESPACE ts1;

 

노트

공유 테이블 스페이스에 테이블 파티션 추가 지원은 MySQL 5.7.24에서 더 이상 사용되지 않으며 향후 MySQL 버전에서 제거 될 예정입니다. 공유 테이블 스페이스에는 InnoDB 시스템 테이블 스페이스 및 일반 테이블 스페이스가 포함됩니다.

 

구문에 대한 자세한 내용은 CREATE TABLE 및 ALTER TABLE을 참조하세요.

 

▶ 일반 테이블 스페이스 행(Row) 형식 지원

일반 테이블 스페이스는 모든 물리적 페이지 크기로 인해 압축된 테이블과 압축되지 않은 테이블이 동일한 일반 테이블 스페이스에 공존 할 수 없다는 경고와 함께 모든 테이블 행(Row) 형식 (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이어야 합니다.

 

다음 표는 허용된 innodb_page_size, FILE_BLOCK_SIZE 및 KEY_BLOCK_SIZE 조합을 보여줍니다. FILE_BLOCK_SIZE 값은 바이트 단위로 지정할 수도 있습니다. 지정된 FILE_BLOCK_SIZE에 유효한 KEY_BLOCK_SIZE 값을 판별하려면 FILE_BLOCK_SIZE 값을 1024로 나눕니다. 테이블 압축은 32K 및 64K InnoDB 페이지 크기를 지원하지 않습니다.

 

표 14.3 압축 테이블에 허용 된 페이지 크기, FILE_BLOCK_SIZE 및 KEY_BLOCK_SIZE 조합

InnoDB Page Size (innodb_page_size)	Permitted FILE_BLOCK_SIZE Value	Permitted KEY_BLOCK_SIZE Value
64KB	64K (65536)	압축이 지원되지 않습니다
32KB	32K (32768)	압축이 지원되지 않습니다
16KB	16K (16384)	N/A:  innodb_page_size가 FILE_BLOCK_SIZE와 같으면 테이블 스페이스에 압축 된 테이블이 포함될 수 없습니다.
16KB	8K (8192)	8
16KB	4K (4096)	4
16KB	2K (2048)	2
16KB	1K (1024)	1
8KB	8K (8192)	N/A:  innodb_page_size가 FILE_BLOCK_SIZE와 같으면 테이블 스페이스에 압축 된 테이블이 포함될 수 없습니다.
8KB	4K (4096)	4
8KB	2K (2048)	2
8KB	1K (1024)	1
4KB	4K (4096)	N/A:  innodb_page_size가 FILE_BLOCK_SIZE와 같으면 테이블 스페이스에 압축 된 테이블이 포함될 수 없습니다.
4K	2K (2048)	2
4KB	1K (1024)	1

 

이 예는 일반 테이블 스페이스 작성 및 압축 된 테이블 추가를 보여줍니다. 이 예에서는 기본 innodb_page_size가 16KB라고 가정합니다. FILE_BLOCK_SIZE가 8192이면 압축 테이블의 KEY_BLOCK_SIZE가 8이어야합니다.

 

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

 

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

 

일반 테이블 스페이스를 작성할 때 FILE_BLOCK_SIZE를 지정하지 않으면 FILE_BLOCK_SIZE의 기본값은 innodb_page_size입니다. FILE_BLOCK_SIZE가 innodb_page_size와 동일한 경우, 테이블 스페이스에는 압축되지 않은 행 형식 (COMPACT, REDUNDANT 및 DYNAMIC 행 형식)이있는 테이블만 포함될 수 있습니다.

 

▶ ALTER TABLE을 사용하여 테이블 스페이스 간 테이블 이동

TABLESPACE 옵션과 함께 ALTER TABLE을 사용하여 테이블을 기존 일반 테이블 스페이스, 테이블 당 새 파일 테이블 스페이스 또는 시스템 테이블 스페이스로 이동할 수 있습니다.

 

노트

공유 테이블 스페이스에 테이블 파티션을 배치하는 기능은 MySQL 5.7.24에서 더 이상 사용되지 않으며 향후 MySQL 버전에서 제거 될 예정입니다. 공유 테이블 스페이스에는 InnoDB 시스템 테이블 스페이스 및 일반 테이블 스페이스가 포함됩니다.

 

테이블당 파일 테이블 스페이스 또는 시스템 테이블 스페이스에서 일반 테이블 스페이스로 테이블을 이동하려면 일반 테이블 스페이스의 이름을 지정합니다. 일반 테이블 스페이스가 존재해야합니다.

ALTER TABLE tbl_name TABLESPACE [=] tablespace_name;

 

일반 테이블 스페이스 또는 테이블 당 파일 테이블 스페이스에서 시스템 테이블 스페이스로 테이블을 이동하려면 테이블 스페이스 이름으로 innodb_system을 지정합니다.

ALTER TABLE tbl_name TABLESPACE [=] innodb_system;

 

시스템 테이블 스페이스 또는 일반 테이블 스페이스에서 테이블 당 파일 테이블 스페이스로 테이블을 이동하려면 테이블 스페이스 이름으로 innodb_file_per_table을 지정합니다.

ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

 

ALTER TABLE ... TABLESPACE 조작은 TABLESPACE 속성이 이전 값에서 변경되지 않은 경우에도 항상 전체 테이블을 다시 작성합니다.

 

ALTER TABLE ... TABLESPACE 구문은 임시 테이블 스페이스에서 영구 테이블 스페이스로 테이블 이동을 지원하지 않습니다.

 

DATA DIRECTORY 절은 CREATE TABLE ... TABLESPACE = innodb_file_per_table과 함께 허용되지만 그렇지 않으면 TABLESPACE 옵션과 함께 사용할 수 없습니다.

 

암호화된 테이블 스페이스에서 테이블을 이동할때 제한 사항이 적용됩니다.

 

▶ 일반 테이블 스페이스 삭제

DROP TABLESPACE 문은 InnoDB 일반 테이블 스페이스를 삭제하는데 사용됩니다.

 

DROP TABLESPACE 조작 전에 테이블 스페이스에서 모든 테이블을 삭제해야합니다. 테이블 스페이스가 비어 있지 않으면 DROP TABLESPACE는 오류를 리턴합니다.

 

일반 테이블 스페이스에서 테이블을 식별하려면 다음의 쿼리를 실행합니다. 필요한 정보가 더 있다면 컬럼을 추가하여 실행합니다.

mysql> SELECT a.NAME AS space_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';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1        | test/t1    |
| ts1        | test/t2    |
| ts1        | test/t3    |
+------------+------------+

 

아무것도 없는 일반 테이블 스페이스에서 DROP TABLESPACE 조작이 오류를 리턴하면 테이블 스페이스에 서버 종료로 인해 중단 된 ALTER TABLE 조작으로 정보가 삭제된 테이블스페이스 혹은 삭제가 되다가 실패한 테이블이 포함될 수 있습니다. 이때는 별도로 장애해결 방법으로 처리해야 합니다.

 

테이블 스페이스의 마지막 테이블을 삭제할 때 일반 InnoDB 테이블 스페이스는 자동으로 삭제되지 않습니다. DROP TABLESPACE tablespace_name을 사용하여 테이블 스페이스를 명시 적으로 삭제해야합니다.

 

일반 테이블 스페이스는 특정 데이터베이스에 속하지 않습니다. DROP DATABASE 조작은 일반 테이블 스페이스에 속하는 테이블을 삭제할 수 있지만 DROP DATABASE 조작이 테이블 스페이스에 속하는 모든 테이블을 삭제하더라도 테이블 스페이스를 삭제할 수는 없습니다. DROP TABLESPACE tablespace_name을 사용하여 일반 테이블 스페이스를 명시 적으로 삭제해야합니다.

 

시스템 테이블스페이스와 마찬가지로 일반 테이블스페이스에 저장된 테이블을 잘라내거나 삭제하면 내부 테이블이 일반 .ibd 데이터 파일에 여유 공간이 생겨 새로운 InnoDB 데이터에만 사용할 수 있습니다. DROP TABLE 조작 중에 테이블당 파일 공간이 삭제될때와 같이 스페이스는 운영 체제로 다시 릴리스되지 않습니다.

 

이 예제는 InnoDB 일반 테이블 스페이스를 삭제하는 방법을 보여줍니다. 일반 테이블 스페이스 ts1은 단일 테이블로 작성됩니다. 테이블 스페이스를 삭제하기 전에 테이블을 삭제해야합니다.

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;
mysql> DROP TABLE t1;
mysql> DROP TABLESPACE ts1;

 

노트

tablespace_name은 MySQL에서 대소 문자를 구분하는 식별자입니다.

 

▶ 일반 테이블 스페이스 제한

-생성되거나 기존 테이블 스페이스를 일반 테이블 스페이스로 변경할 수 없습니다.

-임시 일반 테이블 스페이스 작성은 지원되지 않습니다.

-일반 테이블 스페이스는 임시 테이블을 지원하지 않습니다.

-일반 테이블 스페이스에 저장된 테이블은 일반 테이블 스페이스를 지원하는 MySQL 릴리스에서만 열 수 있습니다.

-일반 테이블 스페이스에 저장된 테이블을 잘라내거나 삭제하는 것은 시스템 테이블 스페이스와 유사하게 새로운 InnoDB 데이터에만 사용할 수있는 일반 테이블 스페이스 .ibd 데이터 파일에 내부적으로 여유 공간을 만듭니다. 여유공간은 테이블당 파일 공간용이므로 운영 체제로 다시 릴리스되지 않습니다.

 

또한 공유 테이블 스페이스 (일반 테이블 스페이스 또는 시스템 테이블 스페이스)에 있는 테이블에 대한 테이블 복사 ALTER TABLE 조작은 테이블 스페이스에서 사용하는 공간의 양을 늘릴 수 있습니다. 이러한 작업에는 테이블의 데이터+인덱스만큼 많은 추가 공간이 필요합니다. 테이블 복사 ALTER TABLE 조작에 필요한 추가 공간은 테이블당 파일 공간 용이므로 운영 체제로 다시 릴리스되지 않습니다.

 

-ALTER TABLE ... DISCARD TABLESPACE 및 ALTER TABLE ... IMPORT TABLESPACE는 일반 테이블 스페이스에 속하는 테이블에는 지원되지 않습니다.

-일반 테이블 스페이스에 테이블 파티션을 배치하는 기능은 MySQL 5.7.24에서 더 이상 사용되지 않으며 향후 MySQL 버전에서 제거 될 예정입니다.

-ADD DATAFILE 절은 마스터와 슬레이브가 동일한 위치에 동일한 이름의 테이블 스페이스를 작성하게 하므로 마스터와 슬레이브가 동일한 호스트에 상주하는 복제 환경에서는 지원되지 않습니다.

 

 

 

■ 언두 테이블 스페이스

실행취소-언두(Undo) 테이블 스페이스에는 실행 취소 로그가 포함되어 있습니다. 이 Undo 로그는 트랜잭션에 의해 클러스터된 인덱스 레코드에 대한 최신 변경을 실행 취소하는 방법에 대한 정보가 포함 된 언두 로그 레코드의 모음입니다. 언두 로그는 롤백 세그먼트 내에 포함 된 실행 취소 로그 세그먼트 내에 있습니다. innodb_rollback_segments 변수는 각 언두 테이블 스페이스에 할당 된 롤백 세그먼트 수를 정의합니다.

 

언두 로그는 시스템 테이블 공간 대신 ​​하나 이상의 언두 테이블 공간에 저장 될 수 있습니다. 이 레이아웃은 시스템 테이블 스페이스에 언두 로그가있는 기본 구성과 다릅니다. 언두 로그에 대한 I / O 패턴은 언두 테이블 공간을 SSD 스토리지에 적합하게 만들면서 시스템 테이블 공간을 하드 디스크 스토리지에 유지합니다.

 

InnoDB가 사용하는 언두 테이블 스페이스 수는 innodb_undo_tablespaces 구성 옵션에 의해 제어됩니다. 이 옵션은 MySQL 인스턴스를 초기화 할 때만 구성 할 수 있습니다. 나중에 변경할 수 없습니다.

 

노트

innodb_undo_tablespaces 구성 옵션은 더 이상 사용되지 않으며 향후 릴리스에서 제거 될 예정입니다.

 

언두 테이블 스페이스 및 해당 테이블 스페이스 내의 개별 세그먼트는 삭제할 수 없습니다. 그러나 언두 테이블 스페이스에 저장된 실행 취소 로그는 잘릴 수 있습니다. 자세한 정보는 아래 언두 테이블 스페이스 자르기를 참고하세요.

 

▶ 언두(Undo) 테이블 스페이스 구성

MySQL 인스턴스에 대한 언두 테이블 스페이스를 구성하려면 다음 단계를 수행합니다. 구성을 프로덕션 시스템에 배치하기 전에 테스트 인스턴스에서 프로시저를 수행한다고 가정합니다.

 

중요사항

언두 테이블 스페이스 수는 MySQL 인스턴스를 초기화할 때만 구성 할 수 있으며 인스턴스가 사용될동안까지 고정됩니다.

 

1. innodb_undo_directory 구성 옵션을 사용하여 언두 테이블 스페이스의 디렉토리 위치를 지정합니다. 디렉토리 위치를 지정하지 않으면 데이터 디렉토리에 실행 취소 테이블 스페이스가 작성됩니다.

 

2. innodb_rollback_segments 구성 옵션을 사용하여 롤백 세그먼트 수를 정의합니다. 상대적으로 낮은 값으로 시작하여 시간에 따라 점진적으로 증가시켜 성능에 미치는 영향을 조사합니다. innodb_rollback_segments의 기본 설정은 최대 값 인 128입니다.

 

하나의 롤백 세그먼트는 항상 시스템 테이블 스페이스에 지정되며 32개의 롤백 세그먼트는 임시 테이블 스페이스 (ibtmp1)를 위해 예약됩니다. 따라서 롤백 세그먼트를 언두(Undo) 테이블 스페이스에 할당하려면 innodb_rollback_segments를 33보다 큰 값으로 설정합니다. 예를 들어, 언두 테이블 스페이스가 두 개인 경우 하나의 롤백 세그먼트를 두 개의 언두 테이블 스페이스 각각에 지정하려면 innodb_rollback_segments를 35로 설정합니다. 롤백 세그먼트는 언두 테이블 스페이스에 순환 방식으로 분배됩니다.

 

별도의 실행 취소 테이블 스페이스를 구성하면 시스템 테이블 스페이스의 롤백 세그먼트가 비활성화됩니다.

 

3. innodb_undo_tablespaces 옵션을 사용하여 언두테이블 스페이스 수를 정의합니다. 지정된 수의 언두 테이블 스페이스는 MySQL 인스턴스의 수명 동안 고정되어 있으므로 최적의 값에 대해 확실하지 않은 경우 높은쪽으로 추정합니다.

 

4. 선택한 옵션 값을 사용하여 새 MySQL 테스트 인스턴스를 생성합니다.

 

5. 프로덕션 서버와 유사한 데이터 볼륨으로 테스트 인스턴스에서 현실적인 워크로드를 사용하여 구성을 테스트합니다.

 

6. I/O 집약적 인 워크로드 성능을 벤치마킹합니다.

 

7. I / O 성능이 더 이상 개선되지 않을 때까지 innodb_rollback_segments의 값을 주기적으로 늘리고 성능 테스트를 다시 실행합니다.

 

▶ 언두 테이블 스페이스 잘라 내기

언두 테이블 스페이스를 잘라내려면 MySQL 인스턴스에 최소 두 개의 활성 언두 테이블 스페이스가 있어야하므로, 한개의 언두 테이블 스페이스는 활성 상태로 유지되고 다른 언두 테이블 스페이스는 오프라인 상태로 절단됩니다. 언두 테이블 스페이스 수는 innodb_undo_tablespaces 변수에 의해 정의됩니다. 기본값은 0입니다. 다음 명령문을 사용하여 innodb_undo_tablespaces의 값을 확인합니다.

mysql> SELECT @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
|                         2 |
+---------------------------+

 

실행 취소 테이블 스페이스가 잘리려면 innodb_undo_log_truncate 변수를 활성화합니다. 예를 들면 다음과 같습니다.

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

 

innodb_undo_log_truncate 변수가 사용 가능한 경우, innodb_max_undo_log_size 변수에 의해 정의 된 크기 한계를 초과하는 실행 취소 테이블 스페이스는 잘립니다. innodb_max_undo_log_size 변수는 동적이며 기본값은 1073741824 바이트 (1024MiB)입니다.

mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+

 

innodb_undo_log_truncate 변수가 사용 가능한 경우 :

1. innodb_max_undo_log_size 설정을 초과하는 언두 테이블 스페이스는 잘림으로 표시됩니다. 잘라내기를 위한 언두 테이블 스페이스 선택은 매번 동일한 언두 테이블 스페이스가 잘리지 않도록 순환 방식으로 수행됩니다.

 

2. 선택한 언두 테이블 스페이스에 있는 롤백 세그먼트가 비활성 상태가되어 새 트랜잭션에 할당되지 않습니다. 현재 롤백 세그먼트를 사용중인 기존 트랜잭션을 완료 할 수 있습니다.

 

3. 퍼지 시스템은 더 이상 사용하지 않는 롤백 세그먼트를 해제합니다.

 

4. 언두 테이블 스페이스의 모든 롤백 세그먼트가 해제되면 자르기 작업이 실행되고 언두 테이블 스페이스가 초기 크기로 잘립니다. 언두 테이블 스페이스의 초기 크기는 innodb_page_size 값에 따라 다릅니다. 기본 16KB 페이지 크기의 경우 초기 언두 테이블 스페이스 파일 크기는 10MiB입니다. 4KB, 8KB, 32KB 및 64KB 페이지 크기의 경우 초기 언두 테이블 스페이스 파일 크기는 각각 7MiB, 8MiB, 20MiB 및 40MiB입니다.

 

자르기 작업 후 언두 테이블 스페이스의 크기는 작업 완료 후 즉시 사용하기 때문에 초기 크기보다 클 수 있습니다.

 

innodb_undo_directory 변수는 언두 테이블 스페이스 파일의 위치를 ​​정의합니다. innodb_undo_directory 변수가 정의되지 않은 경우 언두 테이블 스페이스는 데이터 디렉토리에 있습니다.

 

5. 롤백 세그먼트가 다시 활성화되어 새 트랜잭션에 할당 될 수 있습니다.

 

▶ 언두 테이블 스페이스의 잘라 내기

제거 스레드는 언두 테이블 스페이스를 비우고 잘립니다. 제거 스레드는 기본적으로 제거가 호출 된 128 회마다 한 번씩 자르기 언두 테이블 스페이스를 찾습니다. 제거 스레드가 잘라낼 언두 테이블 스페이스를 찾는 빈도는 기본 설정이 128 인 innodb_purge_rseg_truncate_frequency 변수에 의해 제어됩니다.

mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+

 

해당 빈도를 늘리려면 innodb_purge_rseg_truncate_frequency 설정을 줄입니다. 예를 들어, 제거 스레드가 제거가 호출 될 때마다 32 회 실행 취소 테이블 스페이스를 찾도록하려면 innodb_purge_rseg_truncate_frequency를 32로 설정합니다.

 

mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency = 32;

제거 스레드가 절단이 필요한 언두 테이블 스페이스를 찾으면 제거 스레드가 빠르게 비워지고 언두 테이블 스페이스가 잘립니다.

 

▶ 잘라 내기(Truncate) 언두 테이블 스페이스 파일의 성능 영향

언두 테이블 스페이스가 잘리면 언두 테이블 스페이스의 롤백 세그먼트가 비활성화됩니다. 다른 언두 테이블 스페이스의 활성 롤백 세그먼트는 전체 시스템 로드를 담당하므로 약간의 성능 저하가 발생할 수 있습니다. 성능 저하 정도는 여러 가지 요인에 따라 다릅니다.

 

-언두 테이블 스페이스 수

-실행취소 로그 수

-언두 테이블 스페이스 크기

-I / O susbsystem의 속도

-기존 장기 거래

-시스템 부하

 

언두 테이블 스페이스를 절단 할 때 성능에 영향을주지 않는 가장 쉬운 방법은 언두 테이블 스페이스 수를 늘리는 것입니다.

 

■ 임시 테이블 스페이스

압축되지 않은 사용자 작성 임시 테이블 및 온 디스크 내부 임시 테이블은 공유 임시 테이블 스페이스에 작성됩니다. innodb_temp_data_file_path 구성 옵션은 임시 테이블 스페이스 데이터 파일의 상대 경로, 이름, 크기 및 속성을 정의합니다. innodb_temp_data_file_path에 값을 지정하지 않으면 기본 동작은 12MB보다 약간 큰 innodb_data_home_dir 디렉토리에 ibtmp1이라는 자동 확장 데이터 파일을 작성하는 것입니다.

 

노트

MySQL 5.6에서 압축되지 않은 임시 테이블은 임시 파일 디렉토리의 개별 테이블당 테이블 스페이스 또는 innodb_file_per_table이 비활성화 된 경우 데이터 디렉토리의 InnoDB 시스템 테이블 스페이스에 작성됩니다. MySQL 5.7에 공유 임시 테이블 스페이스를 도입하면 각 임시 테이블에 대한 테이블당 테이블 스페이스 생성 및 제거와 관련된 성능 비용이 제거됩니다. 전용 임시 테이블 스페이스는 더 이상 임시 테이블 메타 데이터를 InnoDB 시스템 테이블에 저장할 필요가 없음을 의미합니다.

 

ROW_FORMAT = COMPRESSED 속성을 사용하여 작성된 압축 된 임시 테이블은 임시 파일 디렉토리의 테이블당 파일 공간에 작성됩니다.

 

임시 테이블 스페이스는 정상 종료 또는 중단 된 초기화에서 제거되며 서버가 시작될 때마다 다시 작성됩니다. 임시 테이블 스페이스는 생성 될 때 동적으로 생성 된 스페이스 ID를 받습니다. 임시 테이블 스페이스를 작성할 수 없는 경우 시작이 거부됩니다. 서버가 예기치 않게 정지 된 경우 임시 테이블 스페이스는 제거되지 않습니다. 이 경우 데이터베이스 관리자는 임시 테이블 스페이스를 수동으로 제거하거나 서버를 다시 시작하여 임시 테이블 스페이스를 자동으로 제거하고 다시 작성할 수 있습니다.

 

임시 테이블 스페이스는 Raw 디바이스에 상주 할 수 없습니다.

 

INFORMATION_SCHEMA.FILES는 InnoDB 임시 테이블 스페이스에 대한 메타 데이터를 제공합니다. 임시 테이블 스페이스 메타 데이터를 보려면 다음과 유사한 쿼리를 실행합니다.

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G

 

INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO는 현재 InnoDB 인스턴스 내에서 활성화 된 사용자 작성 임시 테이블에 대한 메타 데이터를 제공합니다.

 

▶ 임시 테이블 스페이스 데이터 파일 크기 관리

기본적으로 임시 테이블 스페이스 데이터 파일은 디스크 내 임시 테이블을 수용하기 위해 자동 확장되며 크기가 커집니다. 예를 들어, 조작이 크기가 20MB인 임시 테이블을 작성하는 경우, 작성시 기본적으로 크기가 12MB 인 임시 테이블 스페이스 데이터 파일은 이를 수용 할 수 있도록 크기가 확장됩니다. 임시 테이블을 삭제하면 여유 공간을 새 임시 테이블에 재사용 할 수 있지만 데이터 파일은 확장된 크기로 유지됩니다.

 

자동 확장 임시 테이블 스페이스 데이터 파일은 큰 임시 테이블을 사용하거나 임시 테이블을 광범위하게 사용하는 환경에서 커질 수 있습니다. 임시 테이블을 사용하는 오래 실행되는 쿼리로 인해 큰 데이터 파일이 생성 될 수도 있습니다.

 

임시 테이블 스페이스 데이터 파일이 자동 확장되는지 확인하려면 innodb_temp_data_file_path 설정을 확인합니다.

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

 

임시 테이블 스페이스 데이터 파일의 크기를 확인하려면 다음과 유사한 쿼리를 사용하여 INFORMATION_SCHEMA.FILES 테이블을 조회합니다.

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE 
       AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES 
       WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912
      DATA_FREE: 6291456
   MAXIMUM_SIZE: NULL

 

TotalSizeBytes 값은 임시 테이블 스페이스 데이터 파일의 현재 크기를 보고합니다. 다른방법으로 운영 체제에서 임시 테이블 스페이스 데이터 파일 크기를 확인합니다. 기본적으로 임시 테이블 스페이스 데이터 파일은 innodb_temp_data_file_path 구성 옵션으로 정의 된 디렉토리에 있습니다. 이 옵션에 명시 적으로 값을 지정하지 않은 경우, ibtmp1이라는 임시 테이블 스페이스 데이터 파일이 innodb_data_home_dir에 작성되며, 지정되지 않은 경우 MySQL 데이터 디렉토리가 기본값이 됩니다.

 

임시 테이블 스페이스 데이터 파일이 차지하는 디스크 공간을 확보하려면 MySQL 서버를 다시 시작합니다. 서버를 다시 시작하면 innodb_temp_data_file_path에 의해 정의 된 속성에 따라 임시 테이블 스페이스 데이터 파일이 제거되고 다시 작성됩니다.

 

임시 데이터 파일이 너무 커지는 것을 방지하기 위해 최대 파일 크기를 지정하도록 innodb_temp_data_file_path 옵션을 구성 할 수 있습니다. 예를 들면 다음과 같습니다.

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

데이터 파일이 최대 크기에 도달하면 테이블이 가득 찼음을 나타내는 오류와 함께 쿼리가 실패합니다. innodb_temp_data_file_path를 구성하려면 서버를 다시 시작해야 합니다.

 

또는 default_tmp_storage_engine 및 internal_tmp_disk_storage_engine 옵션을 구성합니다.이 옵션은 각각 사용자 작성 및 온 디스크 내부 임시 테이블에 사용할 스토리지 엔진을 정의합니다. 두 옵션 모두 기본적으로 InnoDB로 설정되어 있습니다. MyISAM 스토리지 엔진은 각 임시 테이블에 대해 개별 파일을 사용하며 임시 테이블이 삭제 될 때 제거됩니다.

 

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※