[MySQL] BufferPool Instance 와 쿼리 성능 관계 알아보기 (innodb_buffer_pool_instances)

 

- 목차

 

들어가며.

이번 글에서는 MySQL 의 BufferPool Instance 와 쿼리 실행 속도의 관계에 대해서 알아보려고 합니다.

MySQL 은 BufferPool 이라는 자체적인 메모리 캐싱 구조를 가집니다.

즉, 버퍼풀이 수용할 수 있는 만큼의 용량의 데이터들을 메모리에 유지하려고 합니다.

만약에 버퍼풀의 크기가 4gb 로 설정되어 있다만 이만큼의 레코드나 인덱스 등의 여러 데이터들이 메모리에 캐싱될 수 있습니다.

그리고 BufferPool 은 MySQL 내부에서 단 한개만 존재하는 것이 아니라 설정을 통해서 여러개의 Instance 를 생성할 수도 있습니다.

 

만약에 BufferPool Instance 가 2개 이상이 존재한다면, 이들은 Hashing 이나 Modulo 연산 등을 통해서 데이터들이 여러 Instance 들로 분배됩니다.

데이터들이 가 Instance 들에 분배되어 저장된다곤 하지만, 완벽하게 중복 데이터가 전무하다고 단언하진 못합니다.

( 일부 Btree 구조 상 Root Page, Branch Page 등이 중복될 순 있음 )

하지만 데이터들은 거의 중복없이 각 Instance 들에 분배되어 저장됩니다.

 

이러한 구조인해서 장점이 발생합니다.

BufferPool Instance 가 단일로 존재하지 않기 때문에 여러 Transaction 사이에 Lock 경합이 발생하는 빈도가 줄어들 수 있습니다.

이번 글은 이러한 구조와 Lock 경합으로 인한 쿼리 속도의 관계에 대해서 알아보는 시간을 가지려고 합니다.

 

innodb_buffer_pool_instances.

innodb_buffer_pool_instances 는 mysqld 프로세스가 사용할 Buffer Pool Instance 의 갯수를 설정하는 옵션입니다.

이는 my.cnf 파일을 통해서 설정이 가능합니다.

또한 아래의 설정과 같이 innodb_buffer_pool_size 를 통해서 버퍼풀이 사용할 총 메모리 사이즈를 제어할 수 있습니다.

 

[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 1

 

mysqlslap.

이번 글에서는 버퍼풀 인스턴스의 갯수와 쿼리 실행의 상관관계를 파악하기 위해서 mysqlslap 이라는 벤치마킹 도구를 사용합니다.

mysqlslap 은 mysql-client 에서 제공되는 하나의 실행파일이며, mysql:8.0.30-debian 과 같은 debian 으로 태깅된 MySQL 도커 이미지에 내장되어 있습니다. 

 

BufferPool Instance 갯수와 Query 테스트.

테스트는 아래와 같은 순서로 진행됩니다.

1. Buffer Pool Instance 를 1개 / 4개 / 8개 로 구성된 MySQL 를 생성
2. 1천만개의 Row 를 가지는 테이블을 생성
3. mysqlslap 을 통해서 부하 테스트 진행.

BufferPool Instance 갯수 1.

아래의 명령어를 통해서 BufferPool Instance 의 갯수가 1 개인 MySQL 를 실행할 수 있습니다.

그리고 이 MySQL Docker Container 를 Init SQL 파일을 즉시 실행하며, 이 파일은 1천만개의 Row 를 생성합니다.

cat <<'EOF'> /tmp/my.cnf
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 1
EOF

cat <<'EOF'> /tmp/init.sql
CREATE DATABASE test;

USE test;

CREATE TABLE large_table (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO large_table (user_id, product_id, quantity, price)
SELECT 
    FLOOR(RAND() * 1000000),
    FLOOR(RAND() * 10000),
    FLOOR(RAND() * 10) + 1,
    ROUND(RAND() * 500, 2)
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS tmp1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS tmp2,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS tmp3,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS tmp4;

INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;

EOF

docker network create mysql
docker run --platform linux/amd64 -d --rm --name mysql \
  --hostname mysql --network mysql \
  -v /tmp/my.cnf:/etc/mysql/conf.d/my.cnf \
  -v /tmp/init.sql:/docker-entrypoint-initdb.d/init.sql \
  -e MYSQL_ROOT_PASSWORD=1234 mysql:8.0.30

 

그리고 얼마간 시간이 흐른 뒤에 large_table 이름의 테이블을 조회하게 되면 대략 1천만개의 Row 를 가지는 테이블이 존재합니다.

 

select count(*) from test.large_table\G;

*************************** 1. row ***************************
count(*): 10240000

 

1 Concurrency & 100 Queries.

1개의 Concurrency 환경에서 Select Query 를 100 번 요청합니다. 

Concurrency 를 통한 동시처리를 적용하지 않았기 때문에 100 번의 쿼리 실행이 순차적으로 발생합니다.

docker run -it --network mysql --rm mysql:8.0.30-debian mysqlslap \
  --host=mysql --port=3306 --create-schema=test \
  --user=root --password=1234 \
  --concurrency=1 --iterations=1 --number-of-queries=100 \
  --query="SELECT * FROM test.large_table WHERE id = FLOOR(RAND() * 10000000);" --verbose

Benchmark
	Average number of seconds to run all queries: 249.224 seconds
	Minimum number of seconds to run all queries: 249.224 seconds
	Maximum number of seconds to run all queries: 249.224 seconds
	Number of clients running queries: 1
	Average number of queries per client: 100

 

 

10 Concurrency & 100 Queries.

10개의 Client 들이 동시에 쿼리를 요청하는 테스트입니다.

--number-of-queries 를 1000으로 설정하여 각 Concurrency 가 100 개의 요청을 처리하도록 합니다.

docker run -it --network mysql --rm mysql:8.0.30-debian mysqlslap \
  --host=mysql --port=3306 --create-schema=test \
  --user=root --password=1234 \
  --concurrency=10 --iterations=1 --number-of-queries=1000 \
  --query="SELECT * FROM test.large_table WHERE id = FLOOR(RAND() * 10000000);" --verbose

Benchmark
	Average number of seconds to run all queries: 362.665 seconds
	Minimum number of seconds to run all queries: 362.665 seconds
	Maximum number of seconds to run all queries: 362.665 seconds
	Number of clients running queries: 10
	Average number of queries per client: 100

 

 

BufferPool Instance 갯수 8개.

cat <<'EOF'> /tmp/my.cnf
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
EOF

cat <<'EOF'> /tmp/init.sql
CREATE DATABASE test;

USE test;

CREATE TABLE large_table (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO large_table (user_id, product_id, quantity, price)
SELECT 
    FLOOR(RAND() * 1000000),
    FLOOR(RAND() * 10000),
    FLOOR(RAND() * 10) + 1,
    ROUND(RAND() * 500, 2)
FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS tmp1,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS tmp2,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS tmp3,
     (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
      UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) AS tmp4;

INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;
INSERT INTO large_table (user_id, product_id, quantity, price, created_at) SELECT user_id, product_id, quantity, price, created_at FROM large_table;

EOF

docker network create mysql
docker run --platform linux/amd64 -d --name mysql \
  --hostname mysql --network mysql \
  -v /tmp/my.cnf:/etc/mysql/conf.d/my.cnf \
  -v /tmp/init.sql:/docker-entrypoint-initdb.d/init.sql \
  -e MYSQL_ROOT_PASSWORD=1234 mysql:8.0.30

 

 

1 Concurrency & 100 Queries.

Benchmark
	Average number of seconds to run all queries: 244.212 seconds
	Minimum number of seconds to run all queries: 244.212 seconds
	Maximum number of seconds to run all queries: 244.212 seconds
	Number of clients running queries: 1
	Average number of queries per client: 100

 

10 Concurrency & 100 Queries.

Benchmark
	Average number of seconds to run all queries: 333.773 seconds
	Minimum number of seconds to run all queries: 333.773 seconds
	Maximum number of seconds to run all queries: 333.773 seconds
	Number of clients running queries: 10
	Average number of queries per client: 100

 

BufferPool Instance 의 갯수가 늘어날수록 QPS 의 평균적인 응답속도는 빨라집니다.

1 Concurrency & 100 Queries 경우에 249s -> 244s

10 Concurrency & 100 Queries 경우에 362s -> 333s

의 개선을 보입니다.

 

드라마틱한 개선 결과를 보이지는 않지만, 서버의 실행이 지속될수록 관련된 개선점이 명확해질 것 같습니다.

 

BufferPool Instance 의 갯수와 성능의 관계.

Instance 의 갯수가 증가할수록 Lock Contention 의 가능성이 줄어듭니다.

일반적으로 MySQL 은 Record 단위로 Lock 을 설정합니다.

Instance 가 여러개가 존재하고 데이터가 여러 Instance 들로 분산되어 캐싱된다고 해서 Record 단위의 Lock Contention 이 완화되지는 않습니다.

하지만 Buffer Pool 내부에 존재하는 LRU List, Free List, Flush List 등의 자료구조가 존재하는데,

Buffer Pool Instance 를 여러개로 나누면 이러한 자료구조에 접근하기 위한 Mutex Lock 의 경합이 완화되게 됩니다. 

 

예를 들어, LRU List 에서 특정 레코드 또는 페이지를 조회하고 수정해야하는데 단일 Buffer Pool Instance 를 여러 Transaction 이 접근하게 된다면 Lock Contention 이 발생합니다.

하지만, 서로 다른 Buffer Pool Instance 에서 2개의 Transaction 이 접근한다면 관련된 Lock Contention 은 발생하지 않습니다.

 

따라서 Instance 로 인한 성능의 개선은 반드시 존재하지만, 그렇게 드라마틱하지 않습니다.

결론은 쿼리의 성능이 개선되며, 그 이유는 Buffer Pool Instance 내부의 자료구조와의 접근의 Lock Contention 이 완화된다는 점입니다.