Intro
데이터베이스 성능 최적화는 대규모 애플리케이션의 핵심 과제 중 하나입니다. 특히 MySQL의 InnoDB 스토리지 엔진에서 Buffer Pool은 성능에 직접적인 영향을 주는 중요한 요소 중 하나입니다.
이번 포스팅에서는 대량의 데이터 조회 시 발생하는 성능 문제를 InnoDB Buffer Pool 최적화를 통해 어떻게 해결할 수 있을지에 대한 과정을 다룹니다.
Buffer pool?
InnoDB Buffer Pool은 테이블 및 인덱스 데이터가 액세스될 때 InnoDB가 캐시하는 주 메모리 영역입니다. 이를 통해 자주 사용하는 데이터를 메모리에서 직접 액세스할 수 있게 하여 처리 속도를 크게 향상시킵니다.
Buffer Pool은 LRU(Least Recently Used) 알고리즘을 사용해 데이터를 관리합니다. 이 알고리즘의 작동 방식은 다음 그림과 같습니다.
InnoDB Buffer Pool의 LRU 알고리즘은 New Sublist와 Old Sublist로 구성됩니다. 새로 읽은 페이지는 중간 지점(Midpoint)에 삽입되고, 데이터에 액세스할 때 'New Sublist'의 시작으로 이동합니다. 사용되지 않는 페이지는 점차 Old Sublist의 끝으로 이동하여 최종적으로 제거됩니다. 이 방식으로 자주 사용되는 데이터를 메모리에 유지하면서 대규모 스캔으로 인한 Buffer Pool 오염을 방지합니다.
Scenario
| Title | datbase buffer pool size |
| Trigger | 상품 및 주문 데이터를 대량으로 Mysql에 삽입 후 대량의 데이터(10,000건)를 한 번에 조회해오는 질의문 실행. |
| Problem | 대량의 데이터 처리 시 DB 성능 저하 문제 발생하였음. 제한된 메모리로 인해 디스크 I/O가 증가하여 쿼리 처리 속도가 느려짐. |
| Action | DB 캐시 설정 최적화(Buffer Pool Size 조정) |
| Benefit | 메모리 내 데이터 보유량 증가로 디스크 I/O 감소 및 쿼리 응답 시간 단축 |
Validation
설정 값 확인
innodb buffer pool 관련 옵션들
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_%';
+-----------------------------------------+------------------------------------------------+
|Variable_name |Value |
+-----------------------------------------+------------------------------------------------+
|Innodb_buffer_pool_dump_status |Dumping of buffer pool not started |
|Innodb_buffer_pool_load_status |Buffer pool(s) load completed at 240724 6:29:44|
|Innodb_buffer_pool_resize_status | |
|Innodb_buffer_pool_resize_status_code |0 |
|Innodb_buffer_pool_resize_status_progress|0 |
|Innodb_buffer_pool_pages_data |3074 |
|Innodb_buffer_pool_bytes_data |50364416 |
|Innodb_buffer_pool_pages_dirty |0 |
|Innodb_buffer_pool_bytes_dirty |0 |
|Innodb_buffer_pool_pages_flushed |212 |
|Innodb_buffer_pool_pages_free |5118 |
|Innodb_buffer_pool_pages_misc |0 |
|Innodb_buffer_pool_pages_total |8192 |
|Innodb_buffer_pool_read_ahead_rnd |0 |
|Innodb_buffer_pool_read_ahead |0 |
|Innodb_buffer_pool_read_ahead_evicted |0 |
|Innodb_buffer_pool_read_requests |17951 |
|Innodb_buffer_pool_reads |2931 |
|Innodb_buffer_pool_wait_free |0 |
|Innodb_buffer_pool_write_requests |1902 |
+-----------------------------------------+------------------------------------------------+위 명령을 통해 Buffer Pool의 다양한 상태 정보를 확인할 수 있었습니다. 주요 항목은 다음과 같습니다.
- Innodb_buffer_pool_pages_total(Buffer Pool 총 페이지 수) =
Innodb_buffer_pool_pages_data(데이터를 포함한 페이지) +
Innodb_buffer_pool_pages_misc(기타 용도로 사용되는 페이지) +
Innodb_buffer_pool_pages_free(사용 가능한 빈 페이지) - Innodb_buffer_pool_read_requests : Buffer Pool에서 읽기 요청된 횟수
- Innodb_buffer_pool_reads : 디스크에서 직접 읽은 횟수로, Innodb_buffer_pool_read_requests에 비해 낮으면 캐시 효율이 좋음을 의미합니다.
innodb_buffer_pool_size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SET GLOBAL innodb_buffer_pool_size = 268435456;위 설정 값을 이용하여 Innodb의 Buffer pool 크기를 조정할 수 있습니다.
innodb_buffer_pool_size를 크게 설정하면 더 많은 데이터를 메모리에 캐싱하여 디스크 I/O를 줄이고 쿼리 성능을 크게 향상시킬 수 있습니다. 그러나 너무 크게 설정하면 시스템 리소스 균형이 깨지고 서버 안정성이 저하될 수 있으므로 시스템 자원과 워크로드 특성을 고려하여 최적의 크기를 결정해야 합니다.
Test
데이터 크기 추정
SELECT
SUM(
LENGTH(CAST(limited_orders.order_id AS CHAR)) +
LENGTH(limited_orders.create_at) +
LENGTH(CAST(limited_orders.member_id AS CHAR)) +
LENGTH(limited_orders.order_status) +
LENGTH(CAST(limited_orders.order_total_price AS CHAR)) +
LENGTH(limited_orders.updated_at)
) / 1024 / 1024 AS approx_size_mb
FROM (
SELECT
o1_0.order_id,
o1_0.create_at,
o1_0.member_id,
o1_0.order_status,
o1_0.order_total_price,
o1_0.updated_at
FROM
orders o1_0
WHERE
o1_0.member_id = 1
ORDER BY o1_0.order_id
LIMIT 10000
) AS limited_orders;
약 0.78MB // 10000건
----
SELECT
SUM(
LENGTH(CAST(oi.order_id AS CHAR)) +
LENGTH(CAST(oi.order_item_id AS CHAR)) +
LENGTH(oi.create_at) +
LENGTH(CAST(i.item_id AS CHAR)) +
LENGTH(i.active) +
LENGTH(i.create_at) +
LENGTH(i.item_description) +
LENGTH(i.item_name) +
LENGTH(CAST(i.item_price AS CHAR)) +
LENGTH(CAST(i.item_stock_quantity AS CHAR)) +
LENGTH(i.updated_at) +
LENGTH(CAST(oi.item_price AS CHAR)) +
LENGTH(CAST(oi.item_quantity AS CHAR)) +
LENGTH(oi.updated_at)
) / 1024 / 1024 AS approx_size_mb
FROM order_items oi
LEFT JOIN items i ON i.item_id = oi.item_id
JOIN (
SELECT order_id
FROM orders
WHERE member_id = 1
ORDER BY order_id
LIMIT 10000
) as o ON oi.order_id = o.order_id;
약 5.00MB # 29981 건 먼저, 실제 조회되는 데이터의 크기를 추정해보았습니다. SQL 쿼리를 통해 계산해 본 결과, 데이터 크기는 약 5.78MB(orders: 0.578MB + order_items + items: 5.00MB)로 예상됩니다.
테스트 방법
Buffer Pool Size를 128MB로 설정한 후, curl 명령을 사용하여 10,000 건의 데이터를 IN 절로 한번에 조회하는 API를 호출했습니다.
curl -v -w "\nTotal time: %{time_total}s\nTime to connect: %{time_connect}s\nTime to first byte: %{time_starttransfer}s\n" --location 'http://localhost:8080/api/v1/orders/v4'테스트 결과 분석
실제 메모리 사용량 변화
8ffb8331a298 yoonnsshop-db-1 1.79% 450.4MiB / 13.15GiB 3.34% 2.74kB / 4.01kB 0B / 8.19kB 37
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
...
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
8ffb8331a298 yoonnsshop-db-1 25.07% 478.9MiB / 13.15GiB 3.56% 101kB / 1.05MB 0B / 8.19kB 46
...
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
8ffb8331a298 yoonnsshop-db-1 14.46% 490.7MiB / 13.15GiB 3.64% 104kB / 1.49MB 0B / 8.19kB 47
...
CONTAINER ID NAME CPU % MEM USAGE / LIMIT MEM % NET I/O BLOCK I/O PIDS
8ffb8331a298 yoonnsshop-db-1 1.09% 560.5MiB / 13.15GiB 4.16% 118kB / 6.84MB 0B / 8.19kB 47Buffer Polo Size를 128MB로 설정한 후, Docker 컨테이너 모니터링을 통해 확인한 결과:
- API 실행 전 : 450.4MiB
- API 실행 후 : 560.5MiB
- 증가량 : 약 100MB
이 결과는 예상 데이터 크기(5.78MB)보다 훨씬 큰 메모리 사용량 증가를 보여줍니다. 이는 데이터베이스가 쿼리 처리를 위해 예상보다 더 많은 메모리를 사용하고 있음을 나타냅니다.
페이지 크기 변화
SHOW ENGINE INNODB STATUS명령으로 Buffer pool size, Free buffers, Database pages 값을 확인했습니다.- 실행 전 : Free buffers 6045, Database pages 2146
- 실행 후 : Free buffers 1159, Database pages 7032
InnoDB의 기본 페이지 크기는 16KB 입니다. 이를 고려하면, Database pages가 약 78MB 증가했음을 알 수 있습니다.
테이블별 Buffer Pool 사용량
SELECT
TABLE_NAME,
COUNT(*) AS PAGE_COUNT,
COUNT(*) * 16 / 1024 AS SIZE_MB
FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NOT NULL
AND TABLE_NAME LIKE '%shop%'
GROUP BY TABLE_NAME
ORDER BY SIZE_MB DESC;
+--------------------+----------+-------+
|TABLE_NAME |PAGE_COUNT|SIZE_MB|
+--------------------+----------+-------+
|`shop`.`items` |5364 |83.8125|
|`shop`.`order_items`|271 |4.2344 |
|`shop`.`orders` |89 |1.3906 |
+--------------------+----------+-------+
흥미롭게도, order_items는 4MB정도를 차지하는데 items 테이블이 83MB를 차지하고 있었습니다.
쿼리 실행 계획 분석
왜 items 테이블이 메모리를 83MB나 차지하고 있었을까 원인을 파악하기 위해 쿼리 실행 계획을 확인해보았습니다.
EXPLAIN SELECT oi1_0.order_id,
oi1_0.order_item_id,
oi1_0.create_at,
i1_0.item_id,
i1_0.active,
i1_0.create_at,
i1_0.item_description,
i1_0.item_name,
i1_0.item_price,
i1_0.item_stock_quantity,
i1_0.updated_at,
oi1_0.item_price,
oi1_0.item_quantity,
oi1_0.updated_at
from
order_items oi1_0
left join
items i1_0
on i1_0.item_id=oi1_0.item_id
where
oi1_0.order_id IN (...10000건...)
+--+-----------+-----+----------+------+---------------------------+-------+-------+------------------+-------+--------+-----------+
|id|select_type|table|partitions|type |possible_keys |key |key_len|ref |rows |filtered|Extra |
+--+-----------+-----+----------+------+---------------------------+-------+-------+------------------+-------+--------+-----------+
|1 |SIMPLE |oi1_0|null |ALL |FKbioxgbv59vetrxe0ejfubep1w|null |null |null |3268447|50 |Using where|
|1 |SIMPLE |i1_0 |null |eq_ref|PRIMARY |PRIMARY|8 |shop.oi1_0.item_id|1 |100 |null |
+--+-----------+-----+----------+------+---------------------------+-------+-------+------------------+-------+--------+-----------+
이 실행 계획을 통해 다음과 같은 문제점을 발견했습니다.
1. IN절의 크기가 너무 커서 옵티마이저가 인덱스 사용을 포기하고 전체 테이블 스캔(Full Table Scan)을 선택했습니다.
2. possible_keys에는 키가 보이지만 실제 key는 null입니다. 이는 옵티마이저가 키를 사용하지 않기로 결정한 것을 의미합니다.
3. order_items와 orders 모두 전체 테이블을 스캔하고 있을 가능성이 높습니다.
이러한 실행 계획은 대량의 데이터를 처리할 때 성능 저하의 주요 원인이 될 수 있습니다.
Buffer Polo Size에 따른 성능 변화
마지막으로, Buffer Pool Size를 변경하며 쿼리 성능이 어떻게 변하는지 테스트해보았습니다.
| Buffer Pool Size | Total time (s) | Buffer pool size | Free buffers | Database pages |
|---|---|---|---|---|
| 128MB (Default) | 6.929883 | 8192 | 1024 | 7168 |
| 256MB | 2.177575 | 16384 | 7252 | 9132 |
| 512MB | 1.973598 | 32766 | 14421 | 18345 |
이 결과를 통해 다음과 같은 사실을 확인할 수 있었스빈다.
1. Buffer Pool Size가 증가함에 따라 Free buffers와 Database pages수가 증가했습니다. 이는 더 많은 데이터를 메모리에 캐시할 수 있음을 의미합니다.
2. Free buffers의 크기가 Buffer Pool 크기에 정확히 비례하지 않는 것으로 보아, MySQL이 최소한으로 확보하는 Free buffers 크기가 있는 것으로 추정됩니다.
3. Buffer Pool Size를 증가시킴에 따라 쿼리 실행 시간이 크게 개선되었습니다.
- 128MB에서 256MB로 증가 시 약 68% 향상
- 256MB에서 512MB로 증가 시 추가 9% 성능 향상
Conclusion
이번 실험을 통해 InnoDB Buffer Pool 크기 조정이 데이터베이스 성능에 큰 영향을 미칠 수 있음을 확인했습니다. 주요 발견사항과 향후 고려사항은 다음과 같습니다:
- Buffer Pool Size와 성능 관계: 실험 결과, Buffer Pool Size를 128MB -> 256MB로 증가시켰을 때 68%의 성능이 향상되었고 256MB -> 512MB로 추가 증가 시 약 9%의 추가 성능이 향상이 있었습니다. 이는 Buffer Pool Size 조정이 성능에 유의미한 영향을 미칠 수 있음을 확인했습니다.
- 쿼리 최적화의 중요성: Buffer Pool의 효과를 극대화하려면 불필요한 데이터 조회를 최소화하는 것이 중요합니다. 예를 들어, 대량의 IN 절 사용으로 인한 Full Table Scan은 Buffer Pool의 효율적인 사용을 저해할 수 있습니다. 적절한 인덱스 사용이나 쿼리 구조 개선을 통해 필요한 데이터만을 정확히 조회함으로써 Buffer Pool 캐시 효과를 더욱 효과적으로 활용할 수 있습니다.
- 데이터 분포 분석: 특정 테이블이 Buffer Pool에서 많은 공간을 차지하는 현상에 대한 분석이 필요합니다.
- 시스템 리소스 균형: Buffer Pool Size 증가가 전체 시스템 리소스에 미치는 영향을 고려해야 합니다.
- 지속적인 모니터링: Buffer Pool 사용 패턴, 쿼리 실행 계획, 시스템 리소스 사용량 등의 지속적인 모니터링이 중요합니다.
결론적으로, Buffer Pool 최적화는 효과적이지만 전체 최적화 전략의 일부일 뿐입니다. 쿼리 최적화, 인덱스 전략, 데이터 분포 분석 등을 종합적으로 고려한 접근이 필요하며, 실제 운영 환경에서의 지속적인 모니터링과 튜닝을 통해 최적의 설정을 찾아나가는 과정이 중요합니다.
이번 실험에서는 단일 쿼리 유형에 대한 Buffer Pool의 영향을 주로 다루었지만 실제 데이터베이스 환경은 더욱 복잡합니다. 따라서 향후에는 다양한 복잡한 쿼리문에 대한 케이스와 여러 가지 질의문이 동시에 실행될 때의 Buffer Pool의 변화 등을 다룰 필요가 있습니다. 이를 통해 더 실제적인 상황에서의 Buffer Pool 최적화 전략을 수립할 수 있을 것입니다.
Reference
- InnoDB Buffer Pool : https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool.html
