MySQL Instance Architecture

MySQL 는 Oracle이나 MS SQL과는 다르게 특색이 있는 편이긴 한데...

Oracle 12c 에서 나온 PDB, CDB와 같은.. 또, MS-SQL과 비슷한 구조적인 느낌은 있는데..

한번 살펴보자.

 

1. MySQL Server/Engine Architecture

 1) MySQL Engine의 구성요소

 MySQL Server의 구성요소를 살펴보면,
 크게 MySQL Connector 와 MySQL Server/Engine Process 2개로 나눠 볼 수 있으며,
 MySQL Server/Engine Process는 논리적인 영역과 물리적인 영역으로 나눠어 볼 수 있으며,

 논리적인 영역으로는 Connection Pools, SQL Interface, Parser, Cache & Buffers 등과 같은 Database로서의 기능을 제공할 수 있는 MySQL Engine영역과 Storage Engine 영역으로 나뉘어 진다.

 물리적인 영역은 이러한 데이터베이스를 구성하는 Data Files 및 Log 등이 존재한다.

출처 : https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html

다른 데이터베이스와 차이나는 영역이 바로 Storage Engine 영역이라고 생각한다.

 Application 성격에 맞는 Storage 기능을 제공함으로써 하나의 데이터베이스 서버에서 다양한 특성을 지닌 데이터베이스를 쓰는 효과를 얻을 수 있어 Application/Service 성격에 맞게 유연한 설계를 할 수 있다.

 

따라서, 이 Storage Engine의 특성을 잘 파악하는 것 역시 MySQL 을 사용함에 있어 중요한 부분이다.

특히, MySQL에서 주로 InnoDB 와 MyISAM Storage Engine을 주로 사용되고있으나

점차 갈수록 InnoDB Storage Engine (default storage engine)중심으로 맞춰지고 있는 추세이다.

- 공식 홈페이지에서도 InnoDB Storage Engine Architecture만 이미지로 제공할 정도니...

 

* 참고. MySQL Enterprise Edition과 Community Edition의 가장 큰 차이가 바로 MySQL 엔진에서 제공되는 기능의 차이가 가장 크다. (Enterprise monitoring, TDE, EM 등...)

 

 2) 프로세스 / 스레드

MySQL 서버의 또 다른 특성으로는 프로세스 기반이 아니라 스레드 기반이다.

그리고, 이러한 스레드는 포그라운드와 백그라운드 스레드로 구분할 수 있다.

백그라운드 스레드 : 데이터를 버퍼로 읽어 오는 스레드, 잠금이나 데드락을 모니터링 하는 스레드 등 이 있으며 가장 중요한 것은 로그 스레드와 버퍼의 데이터를 디스크로 내려쓰는 작업을 처리하는 쓰기 스레드이다.

포그라운드 스레드 : 또는 클라이언트 스레드라 하며, 최소한 MySQL 서버에 접속된 클라이언트 수 만큼 존재한다.

이러한 접속된 클라이언트 스레드들은 사용자의 요청한 SQL 문장을 처리하고, 작업을 완료하면 스레드 캐시로 돌아간다. 

* 스레드 구성의 단점을 보자면, 프로세스의 경우 특정 프로세스의 비정상적인 행동이 발견되면 강제로 죽일 수 있으나 mysql 처럼 스레드 기반인 경우 세션 레벨에 대한 컨트롤을 제공하지만 스레드 레벨에서는 컨트롤이 불가하여 재기동만이 해결해주는 경우가 발생하기도 한다.

 

 3) 메모리 구조

 MySQL은 글로벌 메모리 영역과 세션/로컬 메모리 영역 크게 2가지로 구분될 수 있다.

 글로벌 메모리 영역은 MySQL 서버가 시작되면서 운영체제로부터 시스템 변수에 설정된 메모리 크기 만큼 할당되며 각 메모리 영역은 아래 구성의 특성을 지니고 있다.
 글로벌 메모리 영역은 InnoDB Buffer Pool, MyISAM Key Cache, Binary log buffer, redo log buffer, table cache.
 세션/로컬 메모리 영역은 : Join buffer, Sort buffer, Network buffer, read buffer.

 

2. Storage Engine Architecture

스토리지 엔진의 역할은 SQL 구문을 최적화하거나, DBMS의 핵심적인 데이터 처리, 디스크 저장 등의 주요 기능을 제공한다. 또한, 각 스토리지 엔진 마다 제공하는 기능이 다르다.
따라서, 스토리지 엔진 마다 B-tree/Hash/T-tree index, MVCC, Locking granularity, Transactions, Backup 등의 기능의 차이가 존재한다.

이러한 스토리지 엔진의 지정은 데이터베이스 및 테이블 단위로 지정이 가능하므로, 하나의 인스턴스(MySQL 엔진)에서 다양한 스토리지 엔진을 사용함으로써, 어플리케이션의 유연성을 제공한다는 장점이 존재한다.

 

 가장 대표적으로 사용되는것이 InnoDB 이고, 현재 MySQL에서도 대표적으로 사용되는 엔진이다.

 

  1) InnoDB Storage Architecture

  - MySQL에서 사용되는 가장 높은 신뢰성과 성능의 균형을 이루는 범용 스토리지 엔진으로 MySQL 8.0 부터는 default storage engine으로 사용된다. (create table ~ storage engine에 별도 지정이 없는 경우 innodb 로 구성됨.)

  - InnoDB Storage Engine의 처리 프로세스를 살펴보면, Data File로 부터 InnoDB Buffer Pool로 Load data page를 수행하며, 데이터의 변경이 발생되면 Undo log에 Old data를 저장하며, Executor에 의해 InnoDB Buffer Pool에 데이터에 대한 Update page & Lock 이 발생되며, 동시에 Redo log Buffer에 기록을 하게 됩니다.

 이렇게 기록된 사항을 Redo log에 Commit & Flush 를 수행하게 됩니다.
 또한, Executor에서 수행된 사항을 Bin log cache에 Commit & Write를 수행하고 이 내용을 Bin log에 동기화 (Sync binlog)를 하게 됩니다. 이렇게 기록된 binlog 정보, ACK, Commit를 Redo log에 보냅니다.

 결국 정리해보자면, 데이터를 InnoDB Buffer Pool에 Cache하여 데이터 작업에 대한 처리를 수행하고 이에 대한 기록을 Redo log buffer에 저장함으로써 빠른 데이터 처리를 확보함과 동시에 Redo log buffer의 기록된 내용을 redo log와 bin log에 기록함으로써 데이터에 대한 손실을 예방할 수 있게된다.

출처 : https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html

 * 여기서 종종 완전 복구/안정성을 위해 Innodb_flush_log_at_trx_commit 변수를 조정하는 경우가 있는데,

이럴 경우 disk I/O를 줄일 수는 있지만 데이터의 안정성 확보는 할 수 없게 된다.

 

  2) MyISAM Storage Architecture

  이전 ISAM으로 불리던 스토리지 엔진이 많은 기능이 추가되면서 MyISAM 이라는 이름으로 변경되었다.

출처 : Real MySQL 8.0 book 1

MyISAM 스토리지 엔진의 특징은 테이블 당 2개의 파일을 디스크에 저장한다.

MyISAM 스토리지를 사용하는 테이블을 파티셔닝이 가능했지만, 8.0부터는 불가능해졌다.

MyISAM의 Key Cache/Key buffer는 인덱스를 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 한다.

따라서, MyISAM의 특징은 테이블의 전체적인 데이터에 대한 정보를 닮고 있어 빠른 SELECT 처리가 가능하다. 그만큼 복잡한 데이터를 다루는 테이블에는 적합하지 않다. (DML이 자주 일어나며(트랜잭션 처리 필요), 대용량의 데이터(row 값이 큰)를 다루는 경우 적합하지 않다.)

 

Table 16.1 Storage Engines Feature Summary

Feature	MyISAM	Memory	InnoDB	Archive	NDB
B-tree indexes	Yes	Yes	Yes	No	No
Backup/PITR (note1)	Yes	Yes	Yes	Yes	Yes
Cluster database support	No	No	No	No	Yes
Clustered indexes	No	No	Yes	No	No
Compressed data	Yes (note 2)	No	Yes	Yes	No
Data caches	No	N/A	Yes	No	Yes
Encrypted data	Yes (note 3)	Yes (note 3)	Yes (note 4)	Yes (note 3)	Yes (note 3)
Foreign key support	No	No	Yes	No	Yes (note 5)
Full-text search indexes	Yes	No	Yes (note 6)	No	No
Geospatial data type support	Yes	No	Yes	Yes	Yes
Geospatial indexing support	Yes	No	Yes (note 7)	No	No
Hash Indexes	No	Yes	No (note 8)	No	Yes
Index cache	Yes	N/A	Yes	No	Yes
Locking granularity	Table	Table	Row	Row	Row
MVCC	No	No	Yes	No	No
Replication Support (note1)	Yes	Limited (note 9)	Yes	Yes	Yes
Storage limits	256TB	RAM	64TB	None	384EB
T-tree indexes	No	No	No	No	Yes
Transactions	No	No	Yes	No	Yes
Update statistics for data dictionary	Yes	Yes	Yes	Yes	Yes

출처 : https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

 

 

Feature MyISAM Memory InnoDB Archive NDB

Notes:

1. Implemented in the server, rather than in the storage engine.

2. Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.

3. Implemented in the server via encryption functions.

4. Implemented in the server via encryption functions; In MySQL 5.7 and later, data-at-rest encryption is supported.

5. Support for foreign keys is available in MySQL Cluster NDB 7.3 and later.

6. Support for FULLTEXT indexes is available in MySQL 5.6 and later.

7. Support for geospatial indexing is available in MySQL 5.7 and later.

8. InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.

9. See the discussion later in this section.

 

* MySQL 8.0에서 달라진 점

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

MySQL :: MySQL 8.0 Reference Manual :: 2.11.4 Changes in MySQL 8.0