대담이의 블로그

안녕하세요! 대담이입니다. 벌써 10월의 마지막이 다가오고 다음주부터는 11월의 시작이 다가오네요 :) 

월동준비는 다들 잘 되고 계신지요?

오늘은 MySQL 의 엔진의 스토리지 엔진이라는 특성에 대해 안내드리고, 이중 가장 일반적인 목적에서 많이 사용되고, 높은 퍼포먼스를 보이는 InnoDB 스토리지 엔진에 대해 안내드리겠습니다 :) 

오늘 다룰 내용은 모두 MySQL 공식 레퍼런스 문서 (https://dev.mysql.com/doc/refman/8.0/en/innodb-introduction.html ) 를 참고했음을 미리 안내드리며, 시작해보겠습니다!

스토리지 엔진이란?

앞서 안내드렸던 MySQL 아키텍쳐에서 보듯이, MySQL은 여러가지의 스토리지 엔진을 채택하여 테이블을 생성할 수 있습니다. 이를 통해 사용자는 목적에 맞는 특화된 스토리지 엔진을 사용할 수도 있는 구조입니다. 아래에 간단한 예시를 들어드리겠습니다.

• 사용자가 캐시용도의 테이블을 운영하고 싶을 때(데이터가 날아가도 문제 없으나 빨라야함) : MEMORY
• 로깅 형태의 데이터 저장을 원하는 경우 (트랜잭션은 필요하지 않고 입력속도가 빨라야 함 ) : MyISAM
• RDBMS의 기본인 트랜잭션을 지원해야하고, 장애에 내구성이 강해야하는 일반적인 경우 : InnoDB
• 클러스터 구성을 통하여 쓰기 분산등의 이점을 얻고 싶은 경우 : NDBCluster

정도로 앞선 네개 엔진의 특성을 설명 드릴 수 있고, 추가로 사용자가 원하는 스토리지 엔진을 찾아 설치할 수도있습니다. 

이중, 가장 일반적으로, 그리고 재해에 강한 RDBMS의 특성을 가장 잘 반영하는 스토리지 엔진은 InnoDB라고 볼 수 있겠네요. 오늘은 이에 대해 정리를 해드리겠습니다!

먼저 InnoDB의 특장점부터 설명드리자면 아래와 같습니다 😁

• 트랜잭션을 지원합니다(ACID 지원) -> 어찌보면 다른 DBMS에서는 당연히 지원해야하는 것일 수 있겠지만, MySQL의 다른 스토리지 엔진은 성능 등 다른 이유로 트랜잭션을 지원하지 않는 경우가 있습니다. 그러므로 RDBMS의 가장 기본적 특성인 트랜잭션을 지원한다는 것은 아주 매력적인 부분입니다.
• Row-level 잠금을 지원합니다 -> 잠금을 위하여, Row 단위의 잠금을 사용하므로 DML의 동시성을 늘릴 수 있습니다. 
• Primary Key 위주의 정렬로 Primary Key 기준의 쿼리 수행시 가장 최적화된 쿼리를 수행 가능합니다. 이에 대해서는 차후 클러스터드 인덱스 항목을 통하여 더 자세히 구조에 대해 다뤄보겠습니다. 
• 데이터 정합성을 보장하기 위하여 외래키 기능을 지원합니다.
• 기본적으로 읽기시, 잠금 없는 일관된 읽기를 지원합니다. MVCC, 혹은 버전 컨트롤이라 불리는 이 내용에 대해서는 차후 더 자세히 다루도록 하겠습니다.

오늘은 여기서, InnoDB의 메모리 구조에 대해 안내드리려고 합니다.

InnoDB Buffer pool

먼저 InnoDB의 메모리 구조를 안내드리기 위해선, 일반적으로 MySQL 메모리의 가장 큰 부분을 차지하는 버퍼풀을 빼놓고 얘기할 수 없습니다. 일반적인 목적으로 사용하는 MySQL의 경우, 만약 다른 어플리케이션이 수행되지 않는 DB 전용 서버의 경우, 버퍼 풀의 사이즈를 전체 메모리 용량의 3/4 까지도 잡아서 사용합니다.

여기에는 사용자가 접근한 테이블과 인덱스 데이터가 저장됩니다. 그리고 앞선 SELECT 수행절차에서 말씀드렸던(https://daedamee.tistory.com/entry/MySQL-Internal-%EC%82%B4%ED%8E%B4%EB%B3%B4%EA%B8%B0-SELECT-%EC%88%98%ED%96%89%EA%B3%BC%EC%A0%95?category=727646) 메모리에 항상 올리고 시작하는 개념이 이 버퍼풀이라고 생각하시면 더 편하시겠죠? 😃

메모리 영역이 디스크보다 크다면 모든 데이터를 항상 메모리에 들고있을 수있어 굉장히 쾌적하게 이용할 수 있겠지만, 우리모두가 알고있듯 램은 비싸고 디스크는 저렴합니다. 그러므로 제한된 메모리를 효율적으로 사용하기 위해 InnoDB에서는 Least recently used (LRU) 알고리즘을 사용합니다. 자주 접근하는 데이터는 밀리지 않도록 앞으로 당기고, 자주 사용되지 않는 데이터는 메모리에서 제거하는(eviction이라고 부릅니다) 우선순위에 두는 방식으로 운영됩니다.

기본적으로 버퍼풀의 3/8은 old sublist로 분류되며(밀려날 수 있는 페이지들), 나머지는 new list로, 그리고 그 사이를 midpoint로 부릅니다. old list의 페이지를 읽게 된다면 이는 다시 new list로 바뀌게 되며, 사용자의 요청으로 읽히게 되도 이 페이지는 new list로 가게 됩니다. 데이터베이스가 운영될때, 버퍼풀의 페이지는 "나이" 에 의해 움직이진 않습니다. 

그리고 이에 대한 모니터링은 SHOW ENGINE INNODB STATUS; 명령을 통해 가능한데요, 아래 코드에서 간단히 설명드리겠습니다.

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
.....
----------------------
BUFFER POOL AND MEMORY -- 버퍼풀 메모리 영역의 설명입니다. 사용하지 않는 곳이라 아주 깨끗하네요 :) 
----------------------
Total large memory allocated 0 -- 버퍼풀에 얼마나 많은 메모리가 할당되었는지 나타냅니다.
Dictionary memory allocated 366605 -- InnoDB 데이터 딕셔너리가 얼마나 할당되어있는지 나타냅니다.
Buffer pool size   8192 -- 버퍼풀에 할당된 페이지의 사이즈입니다.
Free buffers       7061 -- 버퍼풀에 할단된 페이지 중, free 한 사이즈입니다.
Database pages     1127 -- 버퍼풀 LRU 리스트의 페이지의 사이즈입니다. 
Old database pages 436  -- LRU Old 서브리스트에 있는 페이지의 사이즈입니다.
Modified db pages  0    -- 버퍼풀에서 변경된 페이지의 현재 숫자입니다.
Pending reads      0    -- 버퍼풀에서 읽기를 대기하는 페이지의 숫자입니다.
Pending writes: LRU 0, flush list 0, single page 0 -- 쓰기를 대기하는 페이지로, LRU의 경우 더티페이지를, flush의 경우 체크보인팅을, single의 경우 독립 페이지를 의미합니다.
Pages made young 0, not young 0 -- LRU리스트에서 young으로 만들어지는 페이지와 이게 아닌 페이지입니다.
0.00 youngs/s, 0.00 non-youngs/s
Pages read 985, created 142, written 158 -- 버퍼풀에서 읽히고/만들어지고/쓰인 페이지 숫자입니다.
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1127, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

mysql>

이상, 버퍼풀에 대해서 간단히 구조에 대해 공식 문서 및 간단한 명령을 통해 알아보았습니다! 

MySQL에서 메모리의 용량이 데이터베이스에 어떤 영향을 미치는지, 그리고 자주 액세스하는 데이터가 왜 빠르게 리턴하는지에 대해 알 수 있는 시간이 되었네요!

긴 글 읽어주셔서 감사드리며, 다음 문서를 통해 메모리 구조에 대해 남은 Change buffer, Adaptive Hash Index, Log Buffer에 대해서도 정리하는 시간을 가져보겠습니다.

그럼 오늘도 모두 즐거운 하루 되세요!

안녕하세요! 대담이입니다. 

1주일만에 인사드리네요. 😃 오늘은 인터널을 살펴보기 위해서 Recovery Startup 과정을 살펴보려 합니다!

이를 살펴보면서 MySQL이 어떻게 트랜잭션을 유지할 수 있는지, 그리고 데이터 유실을 방지하기 위해 어떤 과정으로 쿼리가 수행되는지 볼 수 있을것으로 보이네요 :) 그럼 바로 시작해보겠습니다!

먼저, 이전에 설치했던 서버를 이용하여 스타트업이 어떤 에러로그를 발생시키는지 살펴볼까요?

2021-10-23T11:29:02.239156Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.26) starting as process 3493
2021-10-23T11:29:02.290251Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-10-23T11:29:02.814050Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-10-23T11:29:03.175226Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-10-23T11:29:03.175366Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-10-23T11:29:03.178772Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-10-23T11:29:03.178959Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-10-23T11:29:03.242920Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-10-23T11:29:03.243089Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.

이를 통해 알 수 있는 것은 MySQL 이 어떤 버전인지, 어떤 PID를 가지는지, 그리고 로컬 접속을 위한 소켓의 위치와 원격 접속을 위한 port 위치를 알 수 있네요.

여기서는 MySQL 의 시작과 관련하여 일반적인 수행뿐만이 아닌 Crash Recovery 과정까지 포함할 예정이기 때문에, 수동으로 crash를 발생시킨후 띄우는 에러로그까지 포함하겠습니다! kill -9 명령을 통해 킬 후 다시 mysqld를 기동하였을 때, 아래와 같은 에러로그가 포함되는 것을 확인할 수 있었습니다.

2021-10-23T11:54:03.077366Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.26) starting as process 4455
2021-10-23T11:54:03.089246Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-10-23T11:54:04.402779Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.

InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 92021-10-23T11:54:04.613362Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...
 10 11 122021-10-23T11:54:04.640950Z 0 [System] [MY-010232] [Server] XA crash recovery finished.
 13 14 15 16 17 18 19 202021-10-23T11:54:04.725160Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2021-10-23T11:54:04.725389Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2021-10-23T11:54:04.726445Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-10-23T11:54:04.726699Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
 21 22 23 242021-10-23T11:54:04.775302Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-10-23T11:54:04.775443Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.26'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

먼저 MySQL이 시작되기 위해서는, 설정 파일(/etc/my.cnf - 설치 형식/OS에 따라 달라질 수 있습니다.)에 존재하는 설정에 적절한 파일이 존재하고, 접근이 가능해야합니다. 기본적으로 basedir, datadir, socket 설정 등이 있겠습니다. 설정이 제대로 되어있다면, 데이터 파일을 살펴보게 됩니다. 데이터파일을 살펴보던 도중 데이터파일에 이슈가 있다면 이에 대한 자동 복구를 시작하게 되며 이때 recovery를 수행하게 됩니다.

리커버리는 아래와 같은 절차를 통해 수행되게 됩니다. 여기서는 MySQL에서 가장 많이 사용되는 InnoDB 스토리지 엔진에 대해서만 다루겠습니다.

1.  테이블스페이스 찾기 : InnoDB가 리두 로그 적용이 필요한 테이블스페이스를 찾아내기 위해 사용되는 절차입니다.
2. 리두로그 적용 : 초기화동안 리두로그 적용이 진행됩니다. 다른 커넥션들을 허용하기 전에, 모든 변경분이 버퍼풀로부터 테이블스페이스로 적용이 되어있다면(= 더티 페이지가 없다면) 해당 절차는 생략됩니다.
3. 완료가 되지 않은 트랜잭션 롤백 : 완료가 되지않은 트랜잭션이란, 예상치 못한 크래시 이전에 활성화된 트랜잭션이나 Fast shutdown시 발견되는 트랜잭션을 의미합니다. 해당 작업들을 롤백하는 작업이 리두로그 적용 이후 적용됩니다.
4. 체인지 버퍼 병합 : 변경들을 체인지 버퍼로부터 인덱스의 리프 페이지에 적용합니다(시스템테이블스페이스의 일부).
5. 퍼지 : 삭제 플래그가 있는 레코드들을 삭제합니다.

해당 절차는 (https://dev.mysql.com/doc/refman/5.7/en/innodb-recovery.html) 페이지에서 확인하였으며, 위와 같은 절차로 수행됩니다. 여기서 트랜잭션을 종료 이후에도 유지하기 위한 InnoDB의 요소로 세가지 정도를 확인할 수 있겠네요.

• 리두로그
• 더티 페이지
• 체인지 버퍼

해당 요소에 대한 안내는 차후 엔진의 구조를 설명드릴때 다시한번 자세히 다루도록 하겠습니다! RDBMS를 쓰는 가장 큰 이유! 트랜잭션을 가장 극한의 상황인 예상치못한 크래시로부터 어떻게 유지할 수 있는지에 대해, 그중 리커버리 과정에 대해 알아보았습니다.

다음 시간에는 해당 부분을 다루다 보니 또 중요하게 생각되는 요소인, SELECT 절차와 요 페이지에서 소개된 구조요소에 대해 한번 다시 정리해드리도록 하겠습니다.

날씨가 점점 쌀쌀해지는데, 감기 조심하시길 바라며, 지금까지 대담이였습니다~ 😊

안녕하세요, 대담이 입니다!

저번 블로그에서 설명드린 MySQL 설치에 관한 내용을 이용해 뭐를 더 설명드릴까 하다가.. SELECT 수행 과정에 대해 설명드리는 것도 좋을 거 같아 이 부분에 대해 설명드리겠습니다.

저는 현재 Database Engineer로써 근무하고 있습니다.

데이터베이스의 특성 중 하나는 추상화(abstraction) 인데요, 이는 간단히 얘기해서 데이터베이스가 어떤 방식으로 데이터를 돌려주는지는 몰라도, 쉽게 이를 사용할 수 있다!입니다. 

그래도 운행하는 차에 문제가 생기면 자동차 엔지니어에게 수리를 요청하면 그 자동차 엔지니어는 차가 왜 운행할 수 없는 상태인지 진단할 수 있듯이, 데이터베이스 엔지니어라면 데이터베이스가 느린 원인을 진단을 통해 알아낼 수 있어야겠죠? 😃 

이를 위해 가장 많이 살펴보는 요소중 하나가 쿼리가 의도된 대로 수행되는지입니다.

이를 제대로 알아내기 위해서는 SELECT 쿼리의 수행 방식부터 제대로 파악하는 것이 중요합니다! 그럼 이전에 설치된 테이블을 이용하여 간단한 예제를 만들어 SELECT 쿼리의 수행과정에 대해 안내드려보도록 하겠습니다!

mysql> CREATE DATABASE test; 
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed

mysql> create table test_tab(id int , val int, date_val datetime, char_val varchar(40), primary key (id)); # 테스트 테이블을 생성해줍니다.
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test_tab(val, date_val, char_val) values(RAND()*10000000, DATE_ADD(NOW(), INTERVAL RAND()*100000900 second), UUID());
Query OK, 1 row affected (0.00 sec)

mysql> insert into test_tab(val, date_val, char_val) SELECT RAND()*10000000, DATE_ADD(NOW(), INTERVAL RAND()*100000900 second), UUID() FROM test_tab;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
## ..반복합니다.
mysql> select COUNT(*) FROM test_tab; # 약 100만 건의 테스트 데이터를 입력 완료하였습니다.
+----------+
| COUNT(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.10 sec)

위의 구문을 이용해 랜덤한 데이터를 쉽게 만들어내실 수 있습니다. 이를 이용해 한 가지 쿼리를 해볼 텐데요, 

기본적인 쿼리의 모양을 한번 만들어 보겠습니다.

SELECT YEAR(date_val) as year, COUNT(*) as cnt
FROM test_tab
WHERE id BETWEEN 1 AND 10000
GROUP BY YEAR(date_val)
ORDER BY cnt DESC;
+------+------+
| year | cnt  |
+------+------+
| 2023 | 1866 |
| 2022 | 1854 |
| 2024 | 1783 |
| 2021 |  414 |
+------+------+
4 rows in set (0.00 sec)

위의 쿼리는 제가 테스트로 짜 본 쿼리입니다. 먼저 의미는 아신다는 가정하에 적고 있지만, id가 1부터 10000까지 가지는 테이블의 데이터 중, date_val의  row 카운트를 확인하는 쿼리입니다.

해당 쿼리를 나누어 어디부터 데이터베이스 엔진이 해석하는지 살펴볼까요?

1. FROM 절 : 먼저 어느 테이블을 가져와서 어느 테이블에 접근해야 할지 알아야 하기 때문에 FROM 절부터 확인합니다.

2. WHERE절 : 어느 테이블에 접근해야 하는지 알았으면, 어느 데이터를 가져와야 할지 알아야겠죠? 이를 위해 WHERE절에서 어떤 데이터를 가져오는지 체크하는 절차가 두 번째로 확인됩니다.

3. GROUP BY 절 : 데이터를 가져왔으면, 이번 쿼리에서는 집계가 포함되어있으니 집계해야겠죠? 이를 GROUP BY 절에 맞추어 진행합니다!

4. SELECT 절 : SELECT 절에서 집계한 데이터를 어떻게 표현할지(alias, AS로 표현한 별칭입니다.), 그리고 이 카운트는 얼마나 되는지 체크합니다.

5. ORDER BY 절 : 집계가 완료된 데이터를 제가 요청한 순서에 맞추어 정렬합니다.

이 순서를 알고 나면 뒤에 설명드릴 MySQL 엔진에서의 셀렉트 수행절차를 인터널로 살펴보시는데도 더 도움이 됩니다. 

위와 같은 MySQL 아키텍처를 통하여 SELECT 쿼리 처리 수행 절차에 대해 다시 설명드리겠습니다. 각 아키텍처의 역할은 차후 설명드리고, 또 운영방식에 대한 설명도 차후 다시 드릴 수 있도록 하겠습니다 😃

 1. 먼저, 쿼리가 들어오면, 이것이 Engine이 알아먹을 수 있는지 체크하는 Parser부터 들르게 됩니다! 여기서는 이 쿼리를 수행한 주체가 요청한 쿼리(여기선 SELECT)를 수행할 권한을 가졌는지, 그리고 문법에 맞게 작성된 쿼리인지 확인합니다.

2. 쿼리를 해석했다면, 아까 첫 번째로 수행되는 구문이 FROM이었죠? 일단 메모리 영역(Cache & buffers)에 접근하여, 해당 데이터가 존재하는지 확인합니다! 데이터가 있다면 바로 반환하지만(잠금에 따라 달라지지만, 이는 또 차후에 다루어보겠습니다!) 없을 때는 스토리지에 들러 해당 데이터를 캐시에 올리는 작업까지 진행합니다.

3. 캐시에 올라왔다면, 이를 쿼리를 요청한 클라이언트에 반환합니다. 반환 시에는 집계 등은 완성이 되지 않은 테이블에 저장된 그대로의 형태로 반환됩니다.

4. 클라이언트에서 해당 데이터를 받아, 집계 및 정렬을 수행 후, 반환합니다.

복잡하다면 복잡하고, 쉽다면 쉬운 SELECT 수행절차였습니다. SELECT 구문의 간단한 수행 뒤에는 이런 복잡한 일련의 과정이 Database 내부적으로 일어나고 있었다는 점, 알고 계셨나요? 파면 팔수록 재미있는 데이터베이스 엔진의 내부활동, 앞으로도 기대해주시면 감사하겠습니다!

그럼 오늘도 행복한 하루 보내시길 바라며, 지금까지 대담이었습니다!