innodb_buffer_pool_size 설정

설정 시 참고할 정보 조회

현재 버퍼풀 설정 상태 및 유효한 설정 가능 값을 확인한다.

SELECT
    "innodb_buffer_pool_size/1024/1024/1024" AS "key",
    concat(ROUND(@@innodb_buffer_pool_size / 1024 / 1024 / 1024), " (GB)") AS "value"
UNION
SELECT
    "innodb_buffer_pool_chunk_size/1024/1024" AS "key",
    concat(ROUND(@@innodb_buffer_pool_chunk_size / 1024 / 1024), " (MB)") AS "value"
UNION
SELECT
    "innodb_buffer_pool_instances" AS "key",
    concat(ROUND(@@innodb_buffer_pool_instances), " (EA)") AS "value"
UNION
SELECT
    "innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances" AS "key",
    concat(
        ROUND(@@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances / 1024 / 1024),
        " (MB)"
    ) AS "value"
UNION
SELECT
    "Must be set as multiple of the third key value." AS "key",
    ROUND(@@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances) * <배수>;

결과 예시 (<배수> = 4):

+--------------------------------------------------------------+-------------+
| key                                                          | value       |
+--------------------------------------------------------------+-------------+
| innodb_buffer_pool_size/1024/1024/1024                       | 40 (GB)     |
| innodb_buffer_pool_chunk_size/1024/1024                      | 128 (MB)    |
| innodb_buffer_pool_instances                                 | 8 (EA)      |
| innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances | 1024 (MB)   |
| Must be set as multiple of the third key value.              | 42949672960 |
+--------------------------------------------------------------+-------------+

innodb_buffer_pool_size는 innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances의 배수로 설정해야 한다.

설정 변경

set global innodb_buffer_pool_size = <바이트값>;

예시:

set global innodb_buffer_pool_size = 41875931136;
set global innodb_buffer_pool_size = 4294967296;