일반적으로 InnoDB를 주로 MySQL 서버에서 메모리 용량 산정을 할 때에는 컨넥션의 수와 각 컨넥션이 할당받아서 사용할 수 있는 세션 버퍼들을 기준으로 계산하게 된다.
메모리 용량 산정에서 대부분 SortBuffer나 JoinBuffer 그리고 ReadBuffer, Temporary table 크기 등을 기준으로 판단하는 것이 일반적이다.
이러한 세션 버퍼들은 대부분 초기 지정된 아주 작은 크기의 메모리를 할당받아서 필요한 경우 조금씩 더 할당받는 용도로 사용된다.
(c.f. 물론 MySQL 서버가 처리 대상 레코드 건수를 잘못 예측함으로 인해서 불필요한 메모리 할당이 발생하기도 하지만, 이것은 이 게시물에서는 언급하지 않도록 하겠다.)
메모리 용량 산정에서 대부분 SortBuffer나 JoinBuffer 그리고 ReadBuffer, Temporary table 크기 등을 기준으로 판단하는 것이 일반적이다.
이러한 세션 버퍼들은 대부분 초기 지정된 아주 작은 크기의 메모리를 할당받아서 필요한 경우 조금씩 더 할당받는 용도로 사용된다.
(c.f. 물론 MySQL 서버가 처리 대상 레코드 건수를 잘못 예측함으로 인해서 불필요한 메모리 할당이 발생하기도 하지만, 이것은 이 게시물에서는 언급하지 않도록 하겠다.)
이와 같이 필요한 만큼씩만 할당받아서 사용하는 방식에서는 이론적으로 계산한 필요 메모리 사용량과 실제 서비스시에 사용되는 메모리 사용량이 2~30GB 정도 차이가 나버리기 때문에, 메모리 용량 산정이라는 것이 쉽지 않을뿐만 아니라, 이런 이론적인 계산이 아무런 의미가 없고 경험 기반으로 판단해야 할 때가 더 많으며, 그냥 대략적으로 InnoDB Buffer pool로 대 부분의 메모리를 할당하고 Client의 요청을 처리하는 Foreground thread를 위해서 대략 3~5GB 정도를 남겨두는 방식으로 메모리 설정하고 서비스에 투입하게 된다.
그런데, MySQL 서버에서는 아주 엉뚱한 곳에서 엄청난 량의 메모리를 사용해 버리는 경향이 있다.
대 부분은 사용자들이 거의 관심을 가지지 않는 Table cache라는 것이 있는데, 이는 MySQL 서버에서 사용되어지는 각 테이블의 메타 정보를 담고 있는 일종의 캐시이다.
사실 Table cache는 어떻게 작동하는지 어떤 용도로 메모리를 할당하는지 알고 있는 사용자도 거의 없을 정도로, DBA나 사용자들로부터 관심 대상이 아니었다.
대 부분은 사용자들이 거의 관심을 가지지 않는 Table cache라는 것이 있는데, 이는 MySQL 서버에서 사용되어지는 각 테이블의 메타 정보를 담고 있는 일종의 캐시이다.
사실 Table cache는 어떻게 작동하는지 어떤 용도로 메모리를 할당하는지 알고 있는 사용자도 거의 없을 정도로, DBA나 사용자들로부터 관심 대상이 아니었다.
MySQL 서버에서는 테이블을 읽고 쓰기 위해서는 항상 테이블을 열어야 하며, 사용이 완료되면 닫아야 한다.
이러한 테이블의 열고 닫는 작업은 어느 정도의 부하를 유발하기 때문에, 오픈된 테이블의 정보를 Table cache에 담아두고 각 컨넥션에서 공유해서 사용하게 된다.
하지만, 동시에 테이블에 접근하게 되면 이 캐시는 동시에 공유되지 못하므로 생각보다 더 많은 테이블이 오픈되어야 하며 Table cache의 정보는 더 많이 필요하게 된다.
그런데, 이 Table cache는 각 테이블에 대해서 레코드의 최대 크기만큼의 버퍼를 3개씩 더 할당해서 가지고 있게 된다. (이는 최신 버전인 MySQL 5.5.30에서도 공히 적용되는 방식이다.)
많은 사용자가 “그게 얼마나 메모리를 차지할까?”라고 생각할 것이다. 그렇다 일반적으로 테이블이 2~30개 가진 MySQL 서버에서 컨넥션 2~300개 정도를 사용하는 서비스라면
전혀 문제되지 않으며, 고려할 필요도 없다.
이러한 테이블의 열고 닫는 작업은 어느 정도의 부하를 유발하기 때문에, 오픈된 테이블의 정보를 Table cache에 담아두고 각 컨넥션에서 공유해서 사용하게 된다.
하지만, 동시에 테이블에 접근하게 되면 이 캐시는 동시에 공유되지 못하므로 생각보다 더 많은 테이블이 오픈되어야 하며 Table cache의 정보는 더 많이 필요하게 된다.
그런데, 이 Table cache는 각 테이블에 대해서 레코드의 최대 크기만큼의 버퍼를 3개씩 더 할당해서 가지고 있게 된다. (이는 최신 버전인 MySQL 5.5.30에서도 공히 적용되는 방식이다.)
많은 사용자가 “그게 얼마나 메모리를 차지할까?”라고 생각할 것이다. 그렇다 일반적으로 테이블이 2~30개 가진 MySQL 서버에서 컨넥션 2~300개 정도를 사용하는 서비스라면
전혀 문제되지 않으며, 고려할 필요도 없다.
하지만, 이러한 테이블들의 개수가 1~2000개를 넘어서고 컨넥션까지 많이 가진다면 상당히 심각한 메모리 부족현상을 겪게 될 것이다.
또한 이러한 현상은 각 테이블이 수백개 이상의 파티션들을 가진다면 더 큰 문제를 유발하게 될 것이다.
또한 이러한 현상은 각 테이블이 수백개 이상의 파티션들을 가진다면 더 큰 문제를 유발하게 될 것이다.
간단한 확인을 위해서 아래와 같은 테이블을 살펴 보자.
CREATE TABLE `tb_part0_varchar200` (
`fdpk` int(11) DEFAULT NULL,
`fd` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`fdpk` int(11) DEFAULT NULL,
`fd` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tb_part0_varchar2000` (
`fdpk` int(11) DEFAULT NULL,
`fd` varchar(2000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`fdpk` int(11) DEFAULT NULL,
`fd` varchar(2000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tb_part0_varchar20000` (
`fdpk` int(11) DEFAULT NULL,
`fd` varchar(20000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`fdpk` int(11) DEFAULT NULL,
`fd` varchar(20000) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `tb_part0_mediumtext` (
`fdpk` int(11) DEFAULT NULL,
`fd` mediumtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
`fdpk` int(11) DEFAULT NULL,
`fd` mediumtext DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
각각의 테이블에는 레코드를 한건씩만 아래와 같이 INSERT 해두었다.
INSERT INTO tb_part0_varchar200 VALUES (1,’1′);
INSERT INTO tb_part0_varchar2000 VALUES (1,’1′);
INSERT INTO tb_part0_varchar20000 VALUES (1,’1′);
INSERT INTO tb_part0_mediumtext VALUES (1,’1′);
INSERT INTO tb_part0_varchar2000 VALUES (1,’1′);
INSERT INTO tb_part0_varchar20000 VALUES (1,’1′);
INSERT INTO tb_part0_mediumtext VALUES (1,’1′);
그리고, 위의 테이블과 컬럼을 동일하게 가지지만 10개, 100개, 500개, 1000개의 파티션으로 구성된 테이블까지 아래와 같이 20개의 테이블을 준비했다.
+————————–+
| Tables_in_test |
+————————–+
| tb_part0_mediumtext |
| tb_part0_varchar200 |
| tb_part0_varchar2000 |
| tb_part0_varchar20000 |
| tb_part1000_mediumtext |
| tb_part1000_varchar200 |
| tb_part1000_varchar2000 |
| tb_part1000_varchar20000 |
| tb_part100_mediumtext |
| tb_part100_varchar200 |
| tb_part100_varchar2000 |
| tb_part100_varchar20000 |
| tb_part10_mediumtext |
| tb_part10_varchar200 |
| tb_part10_varchar2000 |
| tb_part10_varchar20000 |
| tb_part500_mediumtext |
| tb_part500_varchar200 |
| tb_part500_varchar2000 |
| tb_part500_varchar20000 |
+————————–+
| Tables_in_test |
+————————–+
| tb_part0_mediumtext |
| tb_part0_varchar200 |
| tb_part0_varchar2000 |
| tb_part0_varchar20000 |
| tb_part1000_mediumtext |
| tb_part1000_varchar200 |
| tb_part1000_varchar2000 |
| tb_part1000_varchar20000 |
| tb_part100_mediumtext |
| tb_part100_varchar200 |
| tb_part100_varchar2000 |
| tb_part100_varchar20000 |
| tb_part10_mediumtext |
| tb_part10_varchar200 |
| tb_part10_varchar2000 |
| tb_part10_varchar20000 |
| tb_part500_mediumtext |
| tb_part500_varchar200 |
| tb_part500_varchar2000 |
| tb_part500_varchar20000 |
+————————–+
이제 컨넥션 40개를 열어서, 동시에 아래와 같이 1건의 레코드만 조회하는 쿼리를 실행해보자. 물론 각 테스트에서 40개의 컨넥션은 모두 동일하게 하나의 테이블씩만 조회한다. (즉, 테스트 대상 테이블 1개를 제외한 나머지 테이블들은 전혀 영향을 미치지 않도록 했다)
(쿼리를 조금 더 오랫동안 실행시켜 두기 위해서 sleep 함수를 사용했다.)
mysql> select fdpk, sleep(20) from ${TARGET_TABLE} where fdpk=1
이 쿼리를 처리하기 위해서 MySQL 서버가 할당하는 메모리에 대해서 프로파일링해 본 결과는 아래와 같다. 그림에서 dict_load_table은 InnoDB 딕셔너리를 위해서 할당된 메모리 공간을 의미하며, open_table은 MySQL이 Table cache를 통해서 테이블을 열고 닫는데 사용한 메모리 공간을 의미한다. (실제 dict_load_table과 open_table은 MySQL 서버의 소스 코드상에서 메모리 할당을 수행하는 함수의 이름이다.)
(만약 직접 MySQL Server의 Memory allocation profiling해 보고자 한다면, 가장 손쉬운 방법은 Memory alloc profiling기능을 가진 3rd party memory allocator를 이용하면 된다.)
(만약 직접 MySQL Server의 Memory allocation profiling해 보고자 한다면, 가장 손쉬운 방법은 Memory alloc profiling기능을 가진 3rd party memory allocator를 이용하면 된다.)
위의 결과에서도 알 수 있듯이,
1000개의 파티션을 가지는 테이블 하나에서 레코드 한건을 SELECT하기 위해서 2~300MB씩 메모리가 필요할 수도 있다는 것에 주의해야 하며,
이런 쿼리들이 동시에 2~300개씩 실행된다면 서버의 대 부분 메모리를 Table cache를 위해서 낭비해버리게 될 것이다.
이런 쿼리들이 동시에 2~300개씩 실행된다면 서버의 대 부분 메모리를 Table cache를 위해서 낭비해버리게 될 것이다.
이렇게 Table cache를 위해서 할당된 메모리는 쉽게 해제되지 않고 그대로 유지되는 경우가 많으며, 이 메모리를 다시 운영체제에게 반납하도록 하기 위해서는 “FLUSH TABLES”라는 명령을 실행하면 된다.
하지만, 이 명령은 컨넥션의 개수가 많아지면 몇십초에서 1~2분까지의 시간이 걸리기도 하며, 그 동안 모든 컨넥션의 쿼리는 Blocking된다.
“FLUSH TABLES”명령은 “FLUSH TABLES WITH READ LOCK”이라는 Database Global lock과 비슷하다고 보면 된다.
나름의 Workaround이긴 하지만, 서비스용 데이터베이스에서는 절대 사용할 수 없는 명령인 것이다.
하지만, 이 명령은 컨넥션의 개수가 많아지면 몇십초에서 1~2분까지의 시간이 걸리기도 하며, 그 동안 모든 컨넥션의 쿼리는 Blocking된다.
“FLUSH TABLES”명령은 “FLUSH TABLES WITH READ LOCK”이라는 Database Global lock과 비슷하다고 보면 된다.
나름의 Workaround이긴 하지만, 서비스용 데이터베이스에서는 절대 사용할 수 없는 명령인 것이다.
결론적으로, MySQL 서버에서 사용되는 메모리는 세션 버퍼뿐만 Table cache까지 같이 고려해서 용량 산정을 해야 한다.
만약 컨넥션이나 테이블의 개수가 많다면, 더더욱 Table cache에 집중해서 용량 산정을 해야 할 필요가 있는 것이다.
MySQL에서는 파티션도 하나의 테이블로 간주되므로, 테이블의 개수는 적더라도 파티션의 개수가 많다면 Table cache에 대한 고려는 필수적이라고 볼 수 있다.
만약 컨넥션이나 테이블의 개수가 많다면, 더더욱 Table cache에 집중해서 용량 산정을 해야 할 필요가 있는 것이다.
MySQL에서는 파티션도 하나의 테이블로 간주되므로, 테이블의 개수는 적더라도 파티션의 개수가 많다면 Table cache에 대한 고려는 필수적이라고 볼 수 있다.
사실 용량 산정뿐만 아니라, 개발 시점이나 모델링 시점에서도 이 부분을 고려해야 한다.
테이블에 VARCHAR(10)을 사용하는 것과 VARCHAR(100)을 사용하는 것은 최악의 경우 Table cache만으로 10배의 메모리를 소진해버릴 수도 있는 것이다.
가능하다면, DBA와 협의하여 테이블의 구조나 개수 그리고 컨넥션의 개수 등올 메모리 용량 산정을 필수적으로 진행하는 것이 좋을 것이다.
테이블에 VARCHAR(10)을 사용하는 것과 VARCHAR(100)을 사용하는 것은 최악의 경우 Table cache만으로 10배의 메모리를 소진해버릴 수도 있는 것이다.
가능하다면, DBA와 협의하여 테이블의 구조나 개수 그리고 컨넥션의 개수 등올 메모리 용량 산정을 필수적으로 진행하는 것이 좋을 것이다.
댓글 없음:
댓글 쓰기