테이블 스페이스

테이블 스페이스는 테이블이 저장되는 논리적 공간으로, 물리적 공간인 데이터 파일에 매핑된다.

일반적으로 DBMS 사용 시 어떤 논리적·물리적 공간에 저장되는지에 대해서는 상관하지 않고 CRUD 작업을 한다.

하지만 대용량 데이터를 저장하는 경우에는 성능 향상을 위해 테이블 스페이스에 대해 고려하는 것이 좋다.

테이블 스페이스를 지정하지 않는 경우

가장 일반적인 경우로, 시스템 테이블 스페이스에 저장된다.

innodb_data_file_path 변수에 시스템 테이블 스페이스의 데이터파 일 경로가 담겨져 있다.

show variables like 'innodb_data_file_path'; -- ibdata1:12M:autoextend

위 명령어를 사용해 데이터 파일 경로를 확인한 결과 ibdata에 담겨져 있으며, 크기는 12MB, 최대 파일 크기는 자동 증가임을 알 수 있다.

C:\ProgramData\MySQL\MySQL Server 8.0\Data 위치에 ibdata가 존재하며,
전체 경로는 C:\ProgramData\MySQL\MySQL Server 8.0\Data\ibdata이다.

테이블 스페이스 추가

대용량 테이블을 동시에 여러 개 사용하는 상황에서 테이블마다 별도의 테이블 스페이스에 저장하는 것이 성능에 효과적이다.

성능 향상을 위해 테이블 스페이스를 추가하고, 어떤 테이블에 대한 테이블 스페이스를 지정할 수 있다.

설정 확인

show variables like 'innodb_file_per_table'; -- ON

먼저 각 테이블이 별도의 테이블 스페이스에 저장되려면, innodb_file_per_table 변수의 값이 ON이어야 한다.
위 명령어를 통해 값을 확인할 수 있다. 만약 값이 ON이 아니라면,

SET GLOBAL innodb_file_per_table=ON;

위 명령어를 사용하여 변수 값을 바꿔준다.

테이블 스페이스 생성 및 물리적 공간 매핑

CREATE TABLESPACE ts_a ADD DATAFILE 'ts_a.ibd';
CREATE TABLESPACE ts_b ADD DATAFILE 'ts_b.ibd';
CREATE TABLESPACE ts_c ADD DATAFILE 'ts_c.ibd';

위 명령어를 통해 테이블 스페이스를 생성하고, 데이터 파일을 매핑할 수 있다.

데이터 파일들은 시스템 테이블 스페이스가 담긴 경로와 같은 위치인 C:\ProgramData\MySQL\MySQL Server 8.0\Data에 생성된다.
(이미 생성된 테이블 스페이스에 데이터 파일을 추가하는 기능은 MySQL 8.0.13 이후로 제한되었다.)

테이블의 테이블 스페이스 지정

테이블 생성 혹은 변경 시 테이블 스페이스를 지정할 수 있다.

-- 테이블 생성과 동시에 테이블 스페이스 지정
CREATE TABLE table_a (id INT) TABLESPACE ts_a;

-- 테이블 생성을 먼저 한 후, 테이블 스페이스 지정
CREATE TABLE table_c (select * from employees.salaries);
ALTER TABLE table_c TABLESPACE ts_c;

대용량 데이터 작업 시 오류

대용량 데이터의 읽기, 쓰기 작업에서
Error Code: 2013. Lost connection to MySQL server during query 30.000 sec가 발생한다.

워크벤치의 MySQL Session 기본 설정 시간보다 오랜 시간동안 쿼리가 끝나지 않는 경우 이러한 오류가 발생한다.

Edit-Preferences-SQL Editor-MySQL Session에서

DBMS connection read timeout interval과 DBMS connection timeout interval의 시간을 조절해주면 된다.

0으로 설정하는 경우 쿼리가 얼마나 오래 걸리든 상관하지 않고 세션이 유지된다.