MySQL은 인덱스를 메모리 상에서 B+TREE 구조로 관리합니다.
B-TREE구조는 들어봤어도, B+TREE는 생소할 수 있다고 생각합니다. 간략하게 그림으로 둘 사이의 비교를 해보겠습니다.
아래는 B-TREE의 구조입니다.
아래는 B+TREE의 구조인데, B-TREE구조와 다르게, 실제 데이터(d1 ~ d7)이 leaf노드에만 달려있습니다. 그리고, leaf 노드간에 링크로 연결되어있는 구조임을 알 수 있습니다.
B+TREE 구조의 가장 큰 특징은 트리구조에서 리프(leaf)노드에만 실제 데이터가 저장된다는 부분입니다. 반면에 B-TREE는 모든 노드에 데이터가 저장되기 때문에, 리프(leaf)노드까지 찾아가야 하는 B+TREE 구조보다 검색시에 더 좋은 성능을 가지게 됩니다. 반대로 B+TREE는 리프(leaf)노드의 모든 데이터가 링크로연결된 구조이기 때문에, 선형검색이 가능해서 full scan시에는 더 좋은 성능을 냅니다. PostgreSQL이 검색이 빠른 구조의 B-TREE를 이용하고 있습니다. 그래서 PostgreSQL이 MySQL보다 OLAP 서비스에서 더 성능이 좋다는 평가도 있습니다.
이렇게 인덱스 정보를 메모리에서 관리하게 되는데, 인덱스를 저장하는 단위를 Page라고 합니다.
인덱스관련 자료를 찾아보다보니, Page라는 용어가 크게 2가지 의미로 사용되고 있었습니다.
90% 이상의 문서에서는 Page를 디스크와 메모리 사이에서 InnoDB가 한번에 전송할 수 있는 양을 나타내는 단위로 사용하고 있고, 일부 문서에서는 Page를 가상메모리의 데이터 저장단위로 설명하는 문서도 있습니다.
이 포스트에서는 혼동을 없애기 위해서 Page를 인덱스를 저장하는 단위로 정의하겠습니다.
프로세스가 사용하는 메모리가 부족한 경우에는 디스크공간에 파일로 저장해서 활용하게 되는데, 이 파일들을 paging 파일이라고 합니다. 디스크공간을 활용해서 더 많은 데이터를 처리할 수 있게 되는 것입니다. 프로세스가 필요로 하는 데이터만 메모리로 올리는 처리를 Demanding Page 라고 합니다.
실제 Page의 데이터에 접근하기 위해서는 가상메모리 주소에 대응하는 물리 메모리 주소를 찾아내야 하는데, 이 정보를 관리하는 것이 페이지테이블입니다. Demanding Page처리를 위해서 접근하려고 하는 Page의 데이터가 물리메모리에 있다면, 바로 접근하지만, 물리메모리에 존재하지 않은 상태라면, 물리메모리로 로드하는 처리를 해야합니다.
Demanding Page처리가 있다고 해서 많은 메모리를 사용하는 것은 디스크에 저장된 파일을 물리메모리로 읽어들이는 작업을 해야되게 때문에, 성능에 영향을 줄 수 밖에 없습니다. 만약에 MySQL의 인덱스로 설정하는 컬럼의 사이즈가 크다면, 데이터가 많아지고, 인덱스를 모두 RAM(물리메모리)에 저장하지 못해서 Demanding Page 처리가 일어날 확율이 높아지기 때문에, MySQL의 성능에도 영향을 주게 됩니다. 그래서 작은 사이즈의 컬럼을 인덱스로 사용하는 것이 MySQL의 성능에 중요합니다.
그리고 여러 Page에 접근하는 것은 페이지테이블에 대한 접근도 의미하기 때문에, 성능상 불리합니다.
이 Page가 MySQL은 16k byte으로 기본 설정되있습니다. 이 설정은 my.cnf의 [mysqld] 섹션에 innodb_page_size 파라미터로 변경가능합니다. 하지만, AWS같은 클라우드서비스의 경우에는 변경을 허용하고 있지 않습니다. 그리고, Page의 크기를 크게 잡아 놓으면, 불필요한 메모리를 낭비하게 되므로 주의할 필요가 있습니다.
innodb_page_size 파라미터를 변경한 경우에는 이 설정은 InnoDB의 tablespace가 초기화 되기 전에 설정해야 됩니다. 그래서 설정을 적용하기 위해서는 다음 순서로 진행해야 합니다.
- 먼저 전체 데이터를 덤프 받고
- mysqld 정지
- innodb_page_size 설정 변경
- mysqld 기동 <= 기동중에 변경된 설정으로 innodb_page_size 설정으로 InnoDB tablespace가 초기화됩니다.
- 덤프받은 데이터를 import
실제로 mariadb.com에서 진행한 페이즈 크기에 따른 성능 변화를 체크한 그래프 입니다. 자세한 내용은 아래 링크에서 확인할 수 있습니다.
https://mariadb.com/resources/blog/does-innodb-page-size-matter/
InnoDB Page Size: Benchmarking Performance | MariaDB
Does InnoDB page size matter? We benchmark performance and look at response times to see if it does. View our results (with graphs).
mariadb.com
위 벤치마크 내용의 결과 내용중의 하나인데, 16k, 32k, 64k 로 설정한 해서, thread개수를 늘려가면서 성능 변화를 비교해본 내용으로, 64k인 경우는 성능저하가 약간 있습니다.
하지만, 16k와 32k는 innodb_page_size의 설정변화에 크게 성능에 영향이 없는 것을 알 수 있습니다.
또한, 어플리케이션이 많은 메모리 사용을 요구하는 경우에는 large page라고 하는 설정도 할 수 있습니다. 이 설정을 하기 위해서는 지원되는 OS 버전, 하드웨어 스펙을 확인해야 합니다.
https://dev.mysql.com/doc/refman/8.0/en/large-page-support.html
MySQL :: MySQL 8.0 Reference Manual :: 8.12.3.2 Enabling Large Page Support
8.12.3.2 Enabling Large Page Support Some hardware/operating system architectures support memory pages greater than the default (usually 4KB). The actual implementation of this support depends on the underlying hardware and operating system. Applications
dev.mysql.com
이상으로 MySQL의 인덱스에 대한 기본적인 내용을 알아보았습니다. 실제로 인덱스를 생성해서 성능을 비교해보는 내용은 다음 포스트에서 다루겠습니다.
이 포스트에서 가장 중요한 내용은 인덱스로 사용하는 데이터의 사이즈는 작게할 수록 유리하다. <= 이겁니다.
물론 인덱스로 사용하는 컬럼의 데이터 사이즈가 4byte로 될 수 없는 상황이라면, 비용이 부담 되겠지만, RAM을 늘려주면 해결방법이 될 수 있을 것입니다.
'MySQL > Index' 카테고리의 다른 글
| MySQL index - 인덱스 컬럼의 사이즈에 따른 조회 속도 성능 체크 삽질기 (0) | 2021.10.05 |
|---|---|
| MySQL Index (2) 기본 기능 (2) | 2021.10.05 |
| MySQL Index (4) 커버링 인덱스 (0) | 2021.10.04 |
