InnoDB on-disk structures

Tables

tablespace > segment ( unix file ) > extent > page

Indexes

Clusterd Index

primary key 와 동의어 이기도 하다.  clustered index 가 생성되어 있지 않다면

• not null 이면서 unique index 를 pk 처럼 사용한다.
• auto-increment 를 대용으로 사용할 수 있다.
• pk, uk index 도 없다면 내부적으로 row id 사용하게 된다.
  • 6 byte
  • order insertion

Secondary Indexes

clustered index 를 제외한 모든 인덱스를 secondary index 라고 한다.

secondary index 의 각 레코드는 clustered index 의 컬럼들을 함께 가지고 있다.

이를 통해 clustered index 에서 원하는 행의 위치를 찾는다.

 

그래서 clustered index 는 되도록 짧게 구성하는게 저장 공간을 절약에 좋다.

 

spatial 을 제외하고 b-tree 인덱스로 구성된다. 데이터는 leaf page 에 저장되며 

default 크기는 16kb 이며 innodb_page_size 에 의해 결정 된다.

 

index 유휴 공간

입력된 데이터가 update 될 수 있도록 일부 공간을 유휴 공간으로 유지하는데

데이터가 순차적 데이터라면 (timestamp) 최대 15/16 까지 저장을 할 수 있으며

데이터가 랜덤한 데이터라면 1/2 부터 15/16 까지 저장될 수 있다.

 

bulk insert 이후 index creation, index rebuilding 을 "sorted index build" 라고하며 이때 의 유휴 공간은

innodb_fill_factor 에 결정된다 이 값은 percentage 로 설정할 수 있다.

 

Tablespaces

The System Tablespace

change buffer 를 위한 테이블스페이스이다. 이전 버전에서는 innodb meta 데이터, doublewrite buffer 등도 함께 저장되었었다.

사용자도 임의의로 데이터를 생성할 수는 있다.

resizing system tablespace

-- 새로운 파일을 추가 할 수 있다.
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

 

File-Per-Table Tablespaces

하나의 innodb table 을 하나의 파일로 저장하며 이를 file-per-table tablespaces 라고 한다.

사용법

-- using conf 파일
[mysqld]
innodb_file_per_table=ON
 
 
-- using session
mysql> SET GLOBAL innodb_file_per_table=ON;
-- example
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

장점

• 사용후 truncate, drop 등을 통해 사용하던 공간을 os 에 반환할 수 있다.
• 테이블 단위로 논리적인 export import 를 할 수 있다. ( no sql )
• O_DIRECT 사용시 성능향상이 있을 수 있다.
  • 공유테이블스페이스 는 복수의 테이블이 있지만 file-per-table 은 하나다.
• 데이터파일 사이즈 limit 가 높다.

단점

• 사용후 유휴 공간은 같은 테이블에서만 사용 가능하다.
• 복수의 테이블에 fync 오퍼레이션이 사용되는 경우 테이블 수 만큼 fync 요청 수가 증가한다.
• 테이블의 수가 매우 많다면 파일당 file descriptor 를 세션별로 유지해야 함으로 성능에 영향을 줄 수 있다.
• file-per-table 를 삭제 할때 관련된 캐싱된 buffer 를 삭제하기 위해 buffer pool 을 잠금스캔하는데 삭제 테이블의 크기가 매우 큰 경우 다른 operation 성능에 영향을 줄 수 있다.
• innodb_autoextend_increment 에 영향을 받지 않으면 4mb 씩 증가한다.

General Tablespaces

"CREATE TABLESPACE" 구분을 사용해서 생성하는 공유 테이블스페이를 말한다.

사용법

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLESPACE `ts1` Engine=InnoDB;
mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;
mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;
mysql> ALTER TABLE t2 TABLESPACE ts1;
mysql> ALTER TABLESPACE s1 RENAME TO s2;
mysql> DROP TABLE t1;
mysql> DROP TABLESPACE ts1;

Undo Tablespaces

clustered index record 에 대해 최신의 변경을 취소하기 위한 정보를 가지는 undo log 를 모아둔 테이블스페이스 이다.

초기화 되는 페이지의 크기는 innodb_page_size 에 의존적이다.

파일 최대 사이즈는 innodb_max_undo_log_size 로 정의 된다.

-- add undo tablespace
CREATE UNDO TABLESPACE tablespace_name ADD DATAFILE 'file_name.ibu';
-- drop undo tablespace
ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
drop undo tablespace tablespace_name;

 

Temporary Tablespaces

session temporary tablespaces

session temporary tablespace 는 두가지 유형이 있다.

• user defined temporary tablespace
• internal temporary tablespace by optimizer

global temporary tablespaces

user defined temporary table 에 의해 생기는 rollback segments 를 저장한다. innodb_temp_data_file_path 에는 파일명, 초기사이즈, autoextend 여부 등이 기록된다.

-- 설정 조회
root@localhost:mysql 15:57:02> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+
1 row in set (0.00 sec)
-- global temporary tablespace 상태 조회
root@localhost:mysql 15:57:03>  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
1 row in set (0.01 sec)
 
-- limit 설정 (cnf)
[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

• https://dev.mysql.com/doc/refman/8.0/en/information-schema-innodb-session-temp-tablespaces-table.html

Doublewrite Buffer

buffer pool 의 페이지가 데이터 파일에 플러쉬 되기 이전에 기록되는 buffer 파일이다. buffer pool 이 내용이 플러쉬 되는 당시 운영체제 및 스토리지의 오류로 인해 페이지에 문제가 생기는 경우 mysqld 재 시작시 doublewrite buffer 에서 사본을 사용하여 복구할 수 있다.

8.0.20 이전에는 innodb system tablespace 에 있었지만 이후 버전에서는 별도의 파일에 기록된다.

doublewirte buffer configuration

variable namedesc

innodb_doublewrite	enable on/off
innodb_doublewrite_dir	doublewrite 파일위치
innodb_doublewrite_files	doublewrite buffer파일 개수 기본적으로 2개의 파일로 정의된다. ( per buffer pool instance) flush list doublewrite file checkpoint 시 disk 로 flush 되야 하는 list LRU lst doublewrite file old sublist 에 존재함으로써 disk 로 flush 되야 하는 list 새로운 read 가 요청되었지만 buffer pool 이 부족할때 발생 기본 설정 권장
innodb_doublewrite_pages	thread 당 제어할 수 있는 최대 doublewrite pages
innodb_doublewrite_batch_size	배치로 처리할 페이지 수 , 기본 설정 권장

 

RedoLog

redo log 는 디스크 기반의 구조체로 미완성된 트랜잭션의 데이터를 모아 장애를 복구하기 위한 로그 이다.

redo log 는 기본적으로 두개의 파일로 제공된다.

• ib_logfile0
• ib_logfile1
  

로그파일은 circular 하게 사용한다.  로그 파일은 재사용 측면으로 표현되어 진다. 

redo log 파일수 및 사이즈 변경

• mysql 서버 종료
• my.cnf 수정
  • innodb_log_file_size 수정
  • innodb_log_files_in_group 수정
• mysql 서버 시작

redo log flushing 을 위한 그룹 commit

redo log 를 disk flush 할 수 있는건 한개의 스레드 인데 flush 요청을 처리하려는 시점에 flush 요청이 들어오면 어느정도의 양을

함께 commit 하여 성능을 올리는 기법이다. (대부분의 dbms 에서 내부적으로 지원하고 있는 기능)\

redo log archiving

redo log  를 circular 하게 사용하는데 redo log 데이터 축척속도가 redo log 를 읽어 백업하는 유틸리티가 있다고 가정했을때 일부 redo log 를 유실할 수 있다.  8.0.17 부터는 archiving 이 도입되어 redo log 파일 재사용시 별도의 archiving 을 통해 지난 redo log 를 읽으려는 유틸리티가 데이터 유실 없이 작업을 수행할 수 있다. 

innodb_redo_log_archive_dirs='label1:/backups1;label2:/backups2'
mysql> SET GLOBAL innodb_redo_log_archive_dirs='label1:directory_path1[;label2:directory_path2;…]';
mysql> SELECT innodb_redo_log_archive_start('label', 'subdir');
mysql> DO innodb_redo_log_archive_start('label', 'subdir');
Query OK, 0 rows affected (0.09 sec)
+------------------------------------------+
| innodb_redo_log_archive_start('label') |
+------------------------------------------+
| 0                                        |
+------------------------------------------+
mysql> SELECT innodb_redo_log_archive_stop();
+--------------------------------+
| innodb_redo_log_archive_stop() |
+--------------------------------+
| 0                              |
+--------------------------------+
mysql> DO innodb_redo_log_archive_stop();
Query OK, 0 rows affected (0.01 sec)

performance considerations

• 아카이빙 스토리지는 redo log 스토리지와 속도가 유사해야 한다.
• 아카이빙 모드 사용시 약간의 성능 저하가 있을 수 있다.

disableing redo logging

새로운 instance 구축을 위해 대량을 데이터를 loading 하는 경우 redo log 를 disable 시킴으로써 archiving 혹은 doublewrite buffer 로 인한 성능 저하를 제거할 수 있다.

mysql> GRANT INNODB_REDO_LOG_ENABLE ON *.* to 'data_load_admin';
mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | OFF   |
+-------------------------+-------+

 

Undo Logs

undo log 는 단일 트랜잭션 관련 실행 취소를 위한 레코드 정보의 모음이다.  로그 레코드는 clustered index record 에 대한 트랜잭션의 최신 변경 사항을 취소하기 위한 정보가 있다.

• secondary index 를 참조해도 결국 clustered index 를 포함하기 때문인듯