파티션 : MySQL 파티션의 종류(3)

파티션 : MySQL 파티션의 종류(3)

7. 파티션 테이블의 실행 계획

파티션 테이블에 쿼리가 실행될 때 테이블의 모든 파티션을 읽는지 아니면 일부의 파티션만 읽는지는 성능에 아주 큰 영향을 미친다. 쿼리의 실행 계획이 수립될 때 불필요한 파티션은 모두 배제하고 꼭 필요한 파티션만들 걸러내는 과정을 파티션 프루닝이라고 하는데, 쿼리의 성능은 테이블에서 얼마나 많은 파티션을 프루닝할 수 있는지가 관건이다. 옵티마이저가 수립하는 실행 계획에서 어떤 파티션이 제외되고 어떤 파티션만들 접근하는지는 쿼리의 실행 계획으로 확인할 수 있다. 이때 파티션 프루닝 정보를 확인하려면 EXPLAIN PARTITIONS 명령을 사용해야 한다.

파티션 테이블의 실행 계획을 확인하고자 우선 테스트 용도로 다음과 같은 테이블을 준비하자.

CREATE TABLE employees (
  id INT NOT NULL,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  hired DATE NOT NULL DEFAULT '1970-01-01'
) ENGINE=INNODB
PARTITION BY range (YEAR(hired)) (
  PARTITION p0 VALUES LESS THAN (1991) ENGINE=INNODB,
  PARTITION p1 VALUES LESS THAN (1996) ENGINE=INNODB,
  PARTITION p2 VALUES LESS THAN (2001) ENGINE=INNODB,
  PARTITION p3 VALUES LESS THAN MAXVALUE ENGINE=INNODB
);

준비된 예제 테이블에 다음과 같이 파티션 키로 사용된 hired 칼럼을 검색하는 쿼리의 실행 계획을 한번 살펴보자.

EXPLAIN PARTITIONS
SELECT * FROM employees
WHERE hired='1995-12-10';

EXPLAIN PARTITIONS 명령으로 쿼리의 실행 계획을 확인하면 "partitions"라는 칼럼이 추가로 표시된다. 이 칼럼에는 쿼리의 실행을 위해 어떤 파티션을 읽어야 할지 옵티마이저가 판단한 내용을 보여준다. 만약 파티션 프루닝을 사용하지 못하는 쿼리는 여기에 테이블의 모든 파티션 이름이 나열될 것이다. 실행 계획에 특별히 "파티션 프루닝이 적용됐다"라는 형태의 메시지는 출력되지 않기 때문에 partitions 정보에 테이블의 모든 파티션이 나열됐는지, 아니면 예측했던 일부 파티션만 나열됐는지 확인해야 한다.

employees 테이블은 전체 4개의 파티션으로 구성돼 있다. 하지만 쿼리의 실행 계획에는 p1 파티션만 표시됐기 때문에 이 쿼리는 기대했던 대로 파티션 프루닝이 적절히 수행됐음을 알 수 있다. 그런데 다음 실행 계획에서 type 칼럼의 값이 ALL로 표시된 것이 어떤 의미인지를 알아야 한다. 이것은 employees 테이블의 4개 파티션 가운데 p1 파티션만 읽지만, p1 파티션은 풀 테이블 스캔으로 검색해야 한다는 것을 의미한다.

id	select _type	Table	partitions	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	p1	ALL	NULL	NULL	NULL	2	Using where

파티션 테이블에 실행되는 쿼리에서는 최적화 방법을 모두 확인해야 하며, 더불어 파티션 프루닝이 적절히 수행되는지도 함께 검토해야 한다. 위의 쿼리는 파티션 프루닝은 적절해 됐지만 하나의 파티션에 대해서는 풀 테이블 스캔을 수행하기 때문에 빠른 성능을 보장하기는 어렵다. 이 쿼리가 풀 테이블 스캔을 하지 않게 하려면 employees 테이블의 hired 칼럼에 인덱스가 필요하다는 것은 쉽게 예측할 수 있다. 다음과 같이 인덱스를 생성하고 실행 계획을 다시 한번 확인해보자.

ALTER TABLE employees ADD INDEX ix_hired (hired);

EXPLAIN PARTITIONS 명령으로 SELECT 쿼리의 실행 계획을 확인해 보자. 실행 계획의 type 칼럼이 ALL에서 ref로 바뀐 것으로 봐서 ix_hired 인덱스를 레인지 스캔으로 효율적으로 검색했음을 알 수 있다.

id	select _type	Table	partitions	type	key	key_len	ref	rows	Extra
1	SIMPLE	employees	p1	ref	ix_hired	3	const	1

파티션 테이블을 사용하는 쿼리에서 한 가지 더 주의해야 할 사항은 쿼리의 WHERE 절의 파티션 키 칼럼의 조건을 꼭 파티션 표현식과 같이 사용하지 않아도 된다는 것이다. 파티션 키 칼럼에 대한 조건이라 하더라도 일반적으로 비교하던 형태로 사용하면 된다. 예를 들어 테이블이 YEAR(hired) 표현식으로 파티션되어 생성됐을 때 다음과 같이 쿼리의 WHERE 절에 YEAR() 함수를 사용해서 비교하면 오히려 역효과만 불러온다. 다음 쿼리는 파티션 프루닝을 제대로 처리하지 못하고, employees 테이블의 모든 파티션을 읽는 것으로 실행 계획이 출력될 것이다. 또한 hired 칼럼이 변형되어 비교되기 때문에 hired 칼럼에 생성돼 있는 ix_hired 인덱스도 이용하지 못하게 된다.

SELECT * FROM employees
WHERE YEAR(hired)=2009
  AND emp_no=10;

파티션 테이블의 쿼리라 하더라도 파티션 키로 사용된 표현식과는 관계없이 다음 예제와 같이 일반적인 비교를 하면 MysQL이 적절히 파티션 프루닝도 수행하고 인덱스도 효율적으로 사용할 수 있다.

SELECT * FROM employees
WHERE date_column BETWEEN '2009-01-01' AND '2009-01-30';
  AND emp_no=10;

8. 파티션 테이블 관련 벤치마킹

지금까지는 상당히 이론적이고 매뉴얼에서 소개하는 공식적인 내용 위주로 MySQL 파티션을 살펴봤다. 이제부터는 MySQL 파티션의 실질적인 성능에 대한 내용을 조금 살펴보자. 대표적으로 어느 정도의 테이블 크기에서 파티션 적용이 효과가 있는지, 정말 물리적인 메모리보다 인덱스의 크기가 커졌을 때 INSERT 속도가 과도하게 떨어지는지, 그리고 파티션 적용으로 디스크 공간의 사용량 등이 어떻게 변화하는지 등에 대해 간단한 벤치마킹 결과를 살펴보자.

테이블 크기

아래 그림은 대략 1,200만 건 정도의 레코드가 포함된 테이블의 데이터 파일의 크기를 비교한 것이다. KEY 파티션의 경우 일반 테이블과 거의 비슷햇지만 레인지 파티션의 경우 좀 더 크기가 크다. 적어도 파티션을 이용한다고 해서 디스크의 공간적인 장점은 없다는 것을 알 수 있다. 이것은 단순히 디스크만의 문제가 아니다. 디스크에서 차지하는 공간이 크다는 것은 InnoD의 버퍼 풀 메모리로 읽어 들어야 할 데이터가 많다는 것과 동일한 의미다.

INSERT 성능 테스트

아래 그림은 대략 1,200만 건의 레코드를 INSERT하는 테스트를 실행해 본 결과로, 레인지 파티션이 일반 테이블보다 35% 정도 더 빠른 결과를 확인할 수 있었다. MySQL 서버의 InnoDB 버퍼 풀 크기를 작게 설정한 다음 INSERT 테스트를 수행했기 때문에 이 성능은 인덱스나 데이터가 물리적인 메모리보다 훨씬 큰 상황에서 나타나는 성능과 비슷하다고 생각할 수 있다. 이 테스트에서 사용된 INSERT 쿼리는 AUTO_INCREMENT 칼럼을 프라이머리 키로 포함한 테이블에 대해 수행된 것이다.

이 테스트에서는 20개 미만의 파티션으로 구성된 테이블을 사용했다. 하지만 파티션이 많은 테이블에서는 INSERT나 UPDATE, 그리고 DELETE 등의 쿼리가 더 느려질 수 있다는 점에 주의한다.

SELECT 성능 테스트

Salaries 테이블의 from_date 칼럼으로 파티션된 테이블과 파티션되지 않은 일반 테이블의 SELECT 성능을 비교해 보자. 우선 다음과 같이 from_date 칼럼의 값으로 연도별로 파티션한 salaries 테이블을 준비했다.

-- // 파티션되지 않은 salaries 테이블
CREATE TABLE salaries (
  emp_no INT NOT NULL,
  salary INT NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  PRIMARY KEY (emp_no, from_date),
  KEY ix_empno (emp_no),
  KEY ix_fromdate (from_date)
) ENGINE=INNODB;

-- // from_date 칼럼으로 파티션된 salaries 테이블
CREATE TABLE partition_salaries (
  emp_no INT NOT NULL,
  salary INT NOT NULL,
  from_date DATE NOT NULL,
  to_date DATE NOT NULL,
  PRIMARY KEY (emp_no, from_date),
  KEY ix_empno (emp_no),
  KEY ix_fromdate (from_date)
) ENGINE=INNODB
PARITION BY RANGE (YEAR(from_date))
(
  PARTITION p01 VALUES LESS THAN (1985),
  PARTITION p02 VALUES LESS THAN (1986),
  PARTITION p03 VALUES LESS THAN (1987),
  PARTITION p04 VALUES LESS THAN (1988),
  PARTITION p05 VALUES LESS THAN (1989),
  PARTITION p06 VALUES LESS THAN (1990),
  PARTITION p07 VALUES LESS THAN (1991),
  PARTITION p08 VALUES LESS THAN (1992),
  PARTITION p09 VALUES LESS THAN (1993),
  PARTITION p10 VALUES LESS THAN (1994),
  PARTITION p11 VALUES LESS THAN (1995),
  PARTITION p12 VALUES LESS THAN (1996),
  PARTITION p13 VALUES LESS THAN (1997),
  PARTITION p14 VALUES LESS THAN (1998),
  PARTITION p15 VALUES LESS THAN (1999),
  PARTITION p16 VALUES LESS THAN (2000),
  PARTITION p17 VALUES LESS THAN (2001),
  PARTITION p18 VALUES LESS THAN (2002),
  PARTITION p19 VALUES LESS THAN (3000)
);

위의 파티션된 테이블과 그렇지 않은 테이블에 대해 다음 쿼리로 간단하게 성능 테스트를 해봤다. 이 테스트에서는 InnoDB의 버퍼 풀을 아주 작게 설정해서 데이터와 인덱스의 크기가 물리적인 메모리보다 훨씬 클 때와 똑같은 상황으로 가정해 볼 수 있다.

SELECT SQL_NO_CACHE COUNT(*)
FROM salaries
WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';

SELECT SQL_NO_CACHE COUNT(*)
FROM parition_salaries
WHERE from_date BETWEEN '1999-01-01' AND '1999-12-31';

공정한 성능 비교를 위해 다음의 2가지 사항을 확인해 두었다.

• 위의 두 쿼리 모두 SQL_NO_CACHE 힌트를 사용해 쿼리 캐시를 전혀 사용하지 못하게 했다.
• 파티션 테이블을 SELECT하는 두 번째 쿼리는 "EXPLAIN PARTITIONS" 명령으로 아래와 같이 꼭 필요한 파티션(p16)만 참조한다는 것을 확인했다.

id	select _type	Table	partitions	type	key	key_len	ref	rows	Extra
1	SIMPLE	partition _salareis	p16	range	emp_no	4		21381	Using where; Using index

파티션되지 않은 salaries 테이블은 from_date 칼럼에 인덱스가 있기 때문에 인덱스 레인지 스캔으로 처리되며, 파티션된 partition_salaries 테이블의 쿼리는 파티션 프루닝과 인덱스 레인지 스캔 방식으로 처리된다. 두 쿼리 모두 최적의 상태로 실행될 수 있는 형태의 쿼리다. 아래 그림은 각 쿼리의 성능 테스트 결과를 그래프로 표현한 것이다. 그림만 보면 파티션된 partition_salaries 테이블과 파티션되지 않은 salaries 테이블의 SELECT 성능은 거의 차이가 없음을 알 수 있다.

가끔 인터넷을 검새갛다 보면 파티션 테이블과 파티션되지 않은 테이블의 비교를 위해 풀 테이블 스캔으로 SELECT하는 쿼리의 성능을 비교하는 게시물이 자주 보인다. 1억 건의 레코드가 담긴 테이블을 100개의 파티션으로 나눠서 저장하도록 생성했다. 그리고 이 파티션된 테이블에 파티션 프루닝을 통해 하나의 파티션만 접근해도 되는 결국 100(1억/100)만 건의 레코드가 담긴 테이블을 풀 스캔하는 것과 같은 성능을 보이는 것이 당연한 것이다. 그런데 이러한 인터넷 게시물에서는 100만건 씩 100개로 파티션된 테이블과 1억 건이 담긴 테이블을 풀 스캔하는 쿼리로 "파티션을 사용하면 이렇게 빨라진다"라고 결론을 내리고 있다.

9. 파티션 기능에 대한 결론

아직 MySQL에서 파티션은 그다지 오래되거나 성숙된 기술은 아니다. 이번 장에서 주로 언급한 파티션의 장점이나 주의사항은 상당히 이론 위주의 내용이었다. 파티션과 관련된 이런저런 성능 테스트를 해본 결과, MySQL의 파티션은 SELECT 쿼리의 성능에는 그다지 큰 도움을 주지 못했으며, 쓰기 성능에는 어느 정도 도움되는 것으로 보인다.

RDBMS에서 INSERT나 UPDATE, 그리고 DELETE 쿼리와 같은 쓰기 작업은 읽기에 비해 상대적으로 비용이 많이 드는 작업이다. 이는 인덱스나 칼럼의 개수가 많아지고 한 레코드의 크기가 커지면 추가 비용은 더 높아지기 마련이다. 또한 대부분의 RDBMS에서 공통적인 문제겠지만, 테이블의 레코드 건수가 어느 정도 이상이 되면 INSERT나 UPDATE, 그리고 DELETE와 같은 SQL은 급격하게 성능이 떨어진다. MySQL에서는 레코드의 평균 크기나 하드웨어의 성능에 따라 차이는 있겠지만 경험상 1~3억건 수준이 임계치라고 생각한다. 어떤 파티션 종류를 사용하든 모든 파티션을 골고루 읽고 써야 하는 테이블이라면 절대 파티션을 이용해 SELECT 성능을 향상시키기는 어렵다. 하지만 레코드의 건수가 너무 많아져서 INSERT나 DELETE와 같은 쓰기 작업이 심각하게 느려지고 있다면 파티션 적용을 고려해 보는 것이 좋다.

만약 날짜 칼럼을 이용해 레인지 파티션을 사용할 수 있고, 읽기나 쓰기 작업을 일부 파티션으로 모을 수 있다면 테이블의 크기와 관계없이 항상 파티션을 적용하는 것이 쓰기 및 읽기, 그리고 관리 작업에까지 상당히 도움될 것이다. 하지만 해시나 키 파티션을 사용해야 하는 상황이라면 무조건 파티션을 적용하기보다는 위에서 언급한 상황(쓰기 성능이 현저히 떨어졌을 때)에서 쓰기 성능의 개선을 위해 파티션 적용을 고려해 보는 것이 좋다.

언젠가 MySQL에서 병렬 처리가 도입되는 순간이 오면 MySQL의 파티션이 새로운 대안으로 다시 떠오를 것이다.

참고

• Real MySQL