데이터 베이스 메모리(TIL)

잡담

데이터 베이스를 공부하면서 특정 DB가 아닌 여러 개를 공부하다보니, 용어 등에 대해서 굉장히 혼란이 왔다. 특히 메모리 구조에 관한 이야기가 나올 때 갑자기 불쑥불쑥 등장하는 용어들이 헷갈린다. 예를 들면, Oracle에서는 워킹 메모리를 PGA라고 부르는데, MySQL에서는 정렬 버퍼라고 부르는 등 이런 내용이 헷갈렸다.

 처음에 이 글을 쓸 때에도 Oracle 기준으로 PGA와 SGA에 관해 쓰는게 맞을까를 고민했는데, 어느정도 정리를 하다보니 데이터 베이스 메모리가 가장 좋지 않을까 싶었다.

 이 글을 통해서 데이터 베이스에서 사용되는 메모리의 종류와 해당 메모리를 잘 쓰기 위한 방법이 약간이나마 정리되었으면 하여 작성한다.

---

1. 메모리, 디스크, 캐시

 

○ 디스크가 메모리보다 느린 이유?

 데이터 베이스를 공부하다보면 메모리에서 모든 데이터를 가져오고 디스크로는 최대한 가지 않게 하는 것이  사용에 있어 중요하다고 이야기를 한다. 운영 체제에서 프로세스 스케쥴링 혹은 yield 같은 시스템콜을 배우면 이런 내용에 관해서 배울 것이다. 파일 I/O를 하는 프로세스는 CPU를 점유하지 않으므로 그 동안 다른 프로세스에게 CPU 점유를 넘긴다. 그리고 대기 큐로 넘어갔다가 파일 I/O가 끝나면 다시 레디 큐로 돌아와서 CPU 선택을 기다린다.

 

 이처럼 디스크 I/O로 데이터를 읽어오는 과정에는 부가적인 일이 굉장히 많이 일어난다. 특히, 스케쥴링 과정에서 캐시 및 레지스터 내용 저장 및 복구와 같은 부가적인 비용이 발생한다. 그래서 디스크 I/O는 읽거나 쓰는 속도가 메모리와 같더라도 이런 비용 때문에 더 느릴 수 밖에 없다.

 

 게다가 디스크는 하드디스크가 되었건 요즘 빠르다고 하는 SSD를 사용하건 상대적으로 메모리가 저장되는 공간인 RAM에 비해서 1000배 가까이 느리다. 따라서, 패널티를 가지고 있는데 절대적인 속도마저도 느리다. 

 

○ 디스크와 메모리의 특성과 상호 보완 방안

 메모리는 상대적으로 동일 용량을 기준으로 디스크보다 비싸다. 또한 데이터를 읽고 쓰는 속도는 빠르지만 전원이 내려가는 순간 데이터는 날아가버린다. 반대로 디스크는 동일 용량 기준으로 메모리보다 싸며, 읽고 쓰는 속도는 느리지만 전원이 내려가도 데이터는 유지된다.

 

 따라서, 데이터에 영구저장하고 불러오기 위해서는 디스크를 반드시 쓸 수 밖에 없다. 대신, 데이터 베이스 프로세스가 동작하는 동안 빠르게 동작하라고 메모리 내에 목적에 맞게 캐시 공간을 만들어놓았다. 그래서 데이터를 찾아볼 때에 상대적으로 빠른 메모리 캐시에 데이터가 있는지 확인을 하고 없으면 어쩔 수 없이 디스크에서 데이터를 찾아본다. 이렇게 하면, 메모리의 빠름과 디스크의 비휘발성 및 저렴한 데이터 저장소라는 장점을 같이 사용할 수 있다.

 

 

 결과적으로, 이 글을 통해서 알아보려고 하는 내용은 데이터 베이스에서는 어떤 내용들을 무엇을 위해 캐시에 올려놓는지와 무엇이 메모리 영역을 벗어나서 디스크 영역으로까지 가서 처리 속도를 느리게 만드는지에 관해서 알아볼 예정이다.

---

2. 공동 메모리 영역 - 데이터 캐시

 

 데이터 캐시는 찾았던 데이터를 메모리로 올려놓아 다음에 동일한 데이터를 찾을 때 디스크까지 가지 않아도 데이터를 찾을 수 있게 해준다. DB마다 이름이 다르며 Oracle에서는 SGA 내부 데이터베이스 버퍼 캐시, MySQL에서는 버퍼 풀이라고 불린다.

 

 데이터 캐시는 DB 서버에서 공유하는 자원이다. 개별 자원을 가지지 않는다. 그래서 데이터 캐시를 통해 데이터를 읽는 경우, 락을 걸고 해시 체인에 접근해 데이터를 조회하고 변경하고 락을 다시 푸는 방식이다. 이는 아래의 이미지와 같다. 따라서, 인덱스로 데이터를 가져올 때엔 락과 해시 체인 내의 탐색과정이 추가되기 때문에 무조건적으로 빠르지 않다.

 

 

---

3. 공동 메모리 영역 - SQL 공유 영역

 

 앞의 캐시영역과 달리 공동 메모리 영역이지만 동시성 접근에 관해 크게 고민하지 않아도 된다. 이 영역은 옵티마이저에 생길 과부하를 줄이기 위한 영역이다.

 

 실행 계획을 세울 때, SQL을 파싱하고 통계 내역을 살펴가며 수 많은 실행 계획의 비용을 계산하는 것은 매우 부하가 많이 드는 일이다. 그래서 이전에 동작한 적 있는 SQL을 캐시에서 찾아 즉시 실행단계로 넘어가는 방법이 있는데, 이를 소프트 파싱이라고 한다.

 

 SQL을 저장할 때에는 키와 실행계획이 저장된다. SQL 자체는 대소문자를 가리지 않고 동작하지만, SQL 공유 영역에 저장된 SQL은 동일하게 동작하더라도 대소문자를 구분해 저장한다. 따라서 아래의 구문들은 동일한 동작이지만 모두 다 다른 이름으로 저장되는 캐시의 키 값의 예시다. 

 

SELECT * FROM emp WHERE empno = 7900;
select * from EMP where EMPNO = 7900;
select * from emp where empno = 7900;

 

 

 당연히 SQL 문 자체는 외부에서 미리 만들어놓은 값을 사용하기에 위아 같은 이유로 캐시에 같은 값이 차는 경우는 적을 것이다. 하지만 검색 조건과 같이 외부에서 입력된 파라미터를 받는 경우에는 파라미터에 따라 모두 다 다르게 저장된다. 따라서 파라미터를 바인드 변수로 고정된 SQL에 값을 받아오는 게 좋다. 바인드 변수는 SQL Injection 공격 뿐만 아니라 SQL 공유에도 큰 도움을 줌을 알 수 있다.

---

4. 공동 메모리 영역 - Redo와 Undo 로그 영역

 

 데이터베이스마다 이름이 다를 수 있는데, 갑자기 오류가 나도 이를 복구할 수 있는 시스템이 Redo로그, 중간에 롤백해버리는 경우 과거 상태로 돌리는 시스템이 Undo 로그이다.

 

 이 자체는 파일에 영구적으로 저장된 걸 의미하기에 메모리 영역이라 부르기 힘들다. 하지만, 로그가 기록되기 이전엔 여러 프로세스에서 로그 버퍼에 잠시 쓰였다가 저장된다. DBMS는 아직 파일에 쓰이지 않아도 로그 버퍼에 작성이 되면 커밋됐다 논리적으로 쳐버린다.

 

 그런데, 생각해보면 로그 시스템이 디스크에 접근을 두 번 만들어서 오히려 나쁘지 않을까 할 수도 있다. 하지만, 로그 시스템은 순서가 있어 디스크 랜덤 엑세스 접근을 하지 않는다. 그래서 더 빨리 디스크에 남길 수 있고 이후에 문제가 생겼을 때 이를 통해 데이터베이스 정합성을 맞출 수 있다.

 

 하지만, 이로 인해 병목현상이 발생할 수 있다. Redo로그를 예시로 들어보자. 커밋되었지만 디스크에 쓰이지 않은 데이터는 dirty 블록에 적혀있다. 종종 백그라운드에 있는 쓰레드가 깨어나 주기적으로 이 블록을 디스크에 입력하고 로그를 정리한 뒤 freelist로 올려놓는다. 아래 이미지는 학부 때 Innodb 엔진을 배웠을 때 이와 관련된 이미지를 가져왔다.

 

 

 

그런데 새로운 데이터를 입력할 수 있는 freelist 내부에 블록이 없는 경우 지연현상이 발생한다. 백그라운드 쓰레드 개수를 늘리는 것도 방법이지만 경험상 CPU 성능에 따라 한계치가 넘으면 더이상 늘려도 효과가 없다.

 

 이 때는 커밋하는 횟수에 문제가 있을 가능성이 있다. 커밋이 짧게 자주 일어나면 위에서 언급한 Redo로그 관련 문제가 발생하고 반대로 너무 오랫동안 커밋을 안하고 많이 데이터를 보내면 Undo로그가 발생한다. 그러니, 이를 잘 고려해서 결정해야 한다.   

---

5. 개별 메모리 영역

 

 위에선 공통적으로 사용하는 메모리 중 데이터 캐시 부분에 관해 이야기를 했었다. 공통적인 영역 외에도 개별 영역도 존재한다. 이는 오라클에선 PGA영역, MySQL에서는 Thread Cache와 Sort, Join Buffer 이렇게 말하는 영역이다. 이렇게 개별적인 영역을 가지면, 앞의 공통 영역과 달리 데이터에 접근을 할 때, 동시성 제어가 필요가 없어 빠르다.

 

 MySQL의 이름에서 알 수 있듯이 이 영역은 SQL을 처리하면서 정렬 연산, 해시 연산을 하는 영역이다. 이 영역은 인덱스 실행 계획이 아닌 경우, MultiBlock I/O로 한번에 여러 블록을 가져올 때 동작하는 영역이다. 따라서 조심해야 하는 부분은 개별 메모리에서 감당할 수 있는 데이터양을 초과하는 경우이다. 데이터베이스는 Java처럼 Out of Memory로 서버가 픽 죽는게 아닌 디스크 영역까지 활용해 어떻게든 정렬을 하려고 한다. 이 경우 메모리만 사용하는 경우와 비교했을 때 실행 속도가 굉장히 느리다. 개별 영역이라 동시성 제어에 대한 부담은 적지만 이 점을 주의해야 한다.

 

 따라서 성능이 나오지 않는다면 부분 처리를 가능하게 하는 방법 혹은 메모리를 최소화하는 방법을 고려를 해보아야 한다. 이런 예시로, TopN이나 Sort Aggregation 같은 방식은 실제 정렬이 아닌 메모리 사용을 최소화하면서도 정렬한 것과 비슷한 효과를 내는 방법들이다.

 

 Sort Aggregation은 개별 레코드를 하나씩 읽어가며 집계 함수를 사용할 때에 SUM, MAX, MIN, COUNT 정보 같은 필요한 영역만 기록하고 메모리 영역에서 지워버린다. 그러면 해당 집계 함수 결과물을 담을만큼만 메모리를 사용하게 된다.

 

 TopN은 Stopkey 알고리즘이라는 것을 가지고 있는데, 모든 데이터를 한꺼번에 가져와서 정렬을 하는 것이 아니라 만약 10개의 데이터를 가져오는 경우, 다음 데이터를 가져올 때마다 10개 중 낮은 값은 버리고 높은 값은 추가하는 방식으로 실행할 수 있다. 그렇게 하면 데이터를 O(N)의 시간 복잡도로 원하는 레코드만큼의 메모리만 사용하면서 정렬한 효과를 낼 수 있다.

 

 이런 식으로 개별 메모리 영역을 최소화할 수 있으니 쿼리가 늦어지는 원인을 파악하고 적절한 힌트를 주어서 해결하는 방법을 잘 알고 있어야 한다.

---

 

 책에 적힌 내용과 학부 때 내용을 섞어 정리를 해보았는데, 개별 메모리 영역과 해당 영역을 잘 사용하는 방법을 최대한 엮으려고 노력했다. 책의 내용을 그대로 가져온 게 아니라 틀린 내용이 있을 수도 있지만 많은 도움이 되었길 바란다.

 

[ 참고 자료 ]

친절한 SQL 튜닝 - 조시형 

https://www.slideshare.net/meeeejin/mysql-buffer-management