into MySQL: MyISAM 테이블과 InnoDB 테이블의 통계 정보

MySQL은 각 테이블들에 대해서 INFORMATION_SCHEMA 데이터베이스의 STATISTICS라는 테이블에 통계 정보를 관리하고 있다.

이러한 통계 정보는 쿼리의 실행 시에 최적의 실행 방법을 찾아내기 위한 기초 데이터로 사용된다.

대표적으로 MySQL 옵티마이저가 인덱스를 사용해서 데이터를 조회할 지 또는 테이블 전체를 스캔할 지 등의 결정을 하는 데 사용된다.

MySQL의 통계 정보는 Oracle과 같은 다른 DBMS와는 달리 히스토그램 정보는 없으며 단순히 Cardinality만 관리한다.

여기서 Cardinality라 함은 해당 인덱스 값의 Unique 값의 수를 의미한다.

또한, MySQL은 Oracle과 달리 통계 정보가 상당히 동적이다. 동적이라 함은 여러 가지 Event에 의해서 상당히 자주

업데이트됨을 의미한다. 그래서 MySQL에서는 Oracle과 같이 통계 정보를 마이그레이션한다거나 정확한 통계 정보 수집을

위해서 ANALYZE를 해둔다는 것은 의미가 없다 (라고 자주 이야기되어진다).

MySQL의 통계 정보는 다음과 같은 Event가 발생할 경우, 자동적으로 재 수집을 하게 된다.

MySQL 서버에 의해서 테이블이 처음 Open되는 시점
ANALYZE TABLE TBL_NAME; 명령 실행 시
SHOW TABLE STATUS LIKE ‘TBL_NAME’; 명령 실행 시
SHOW INDEX FROM TBL_NAME; 명령 실행 시
Meta Query (테이블에 대한 DDL 문장) 실행 시 (InnoDB Plug-in에서는 innodb_stats_on_metadata 설정 옵션에 의존)
지정된 량 이상의 데이터가 변경 될 경우
기타 등등...

위와 같은 Event에 의해서 사용자가 눈치채지 못하는 사이, 자동으로 테이블의 통계 정보가 변경되기 때문에, Oracle과 같은 DBMS와는 달리 MySQL에서는 테이블의 통계 정보가 그다지 관심의 대상이 될 수 없는 것이다. (별달리 DBA가 해줄 수 있는 것도 별로 없다. ㅠㅠ)

MyISAM 테이블과 InnoDB 테이블의 통계 정보 또한 수집 방법이 조금씩 다른데,

MyISAM 테이블

인덱스 전체를 읽어서 정확한 Cardinality를 구하게 된다.
작은 테이블이라 하더라도 어느 정도의 시간이 걸리며
통계 정보를 수집하는 동안은 Lock을 걸어서 데이터의 변경이 금지된다.
InnoDB도 마찬가지지만, MyISAM 테이블의 경우에는 특히나 서비스 중에 통계 정보를 수집하는 것은 피하는 것이 좋을 듯 하다.
대신, MyISAM 테이블의 통계 정보는 한번 수집되면 상당히 정확한 Cardinality를 가지게 된다.
MyISAM 테이블의 통계 정보는 InnoDB 보다는 고정된 형태이며, “ANALYZE TABLE TBL_NAME;” 명령을 명시적으로 실행하는 경우에만 통계 정보가 수집됨

InnoDB 테이블

랜덤하게 데이터 페이지 몇 개를 샘플링해서 분석한 뒤 예측된 Cardinality를 구하게 된다.
MyISAM과 동일하게 통계 정보를 수집하는 동안은 Lock으로 변경이 금지된다.
InnoDB Plug-in 이전의 Built-in 버전까지는 무조건 랜덤하게 8개의 인덱스 페이지를 샘플링해서 분석했었지만,
Plug-in 버전부터는 랜덤하게 샘플링할 페이지의 개수를 설정 옵션으로 지정할 수 있다.
그래서, MyISAM 테이블의 통계 정보와는 달리 InnoDB 테이블의 통계 정보는 예측치이며, 실제 데이터와 상당한 차이를 보이게 된다.
MyISAM 보다는 상당히 동적이며, 위에 언급된 대부분의 경우에 통계 정보가 수집됨

그런데,

InnoDB 테이블의 경우만 간단한 테이블을 생성 후, 레코드를 몇 건 입력하고 SHOW INDEX를 실행해 보았다.

mysql> show index from stat_test;

+-----------+------------+----------+--------------+-------------+..-------------+..

+-----------+------------+----------+--------------+-------------+..-------------+..

| stat_test | 0 | PRIMARY | 1 | fdpk |.. 14 |..

| stat_test | 1 | ix_test | 1 | fd1 |.. 14 |..

| stat_test | 1 | ix_test1 | 1 | fd1 |.. 14 |..

| stat_test | 1 | ix_test1 | 2 | fd2 |.. 14 |..

+-----------+------------+----------+--------------+-------------+..-------------+..

Ix_test1 이라는 인덱스는 2개의 컬럼으로 구성되어 있기 때문에 2개의 레코드로 표현되었으며, 현재 Cardinality가 모두 14 인것으로 표시되었다. 사실 여기 14는 입력된 레코드의 건수이다. (현재 테스트 테이블의 레코드가 전부 한 페이지에 저장될 정도이기 때문에 정확한 레코드 건수가 수집될 수 있었을 것으로 보인다. 하지만 일반적인 서비스 환경에서는 그렇지 않을 것이다.)

분명히, SHOW INDEX 명령에 의해서 한번 통계 정보가 수집되었을 것으로 보이는데, 상당히 부정확하다.

실제 테이블의 데이터를 한번 조회 해보면, 통계 정보와 상당히 다르다는 것을 알 수 있다.

mysql> select count(distinct fd1) as cardinality from stat_test;

+-------------+

| cardinality |

+-------------+

| 2 |

+-------------+

mysql> select count(distinct fd1, fd2) as cardinality from stat_test;

+-------------+

| cardinality |

+-------------+

| 11 |

+-------------+

이 결과로 보아, 정확한 통계 정보는 아래와 같이 표시되었어야 할 것으로 보인다.

정확히 이와 같진 않더라도, 이와 비슷한 값이 나왔어야 할 것으로 보이는데...

+-----------+------------+----------+--------------+-------------+..-------------+..

+-----------+------------+----------+--------------+-------------+..-------------+..

| stat_test | 0 | PRIMARY | 1 | fdpk |.. 14 |..

| stat_test | 1 | ix_test | 1 | fd1 |.. 2 |..

| stat_test | 1 | ix_test1 | 1 | fd1 |.. 2 |..

| stat_test | 1 | ix_test1 | 2 | fd2 |.. 11 |..

+-----------+------------+----------+--------------+-------------+..-------------+..

여기에서 다시 ANALYZE TABLE 명령을 실행 후, 통계 정보를 다시 확인해 보았다.

mysql> analyze table stat_test;

+----------------+---------+----------+----------+

+----------------+---------+----------+----------+

+----------------+---------+----------+----------+

mysql> show index from stat_test;

+-----------+------------+----------+--------------+-------------+..+-------------+..

+-----------+------------+----------+--------------+-------------+..+-------------+..

| stat_test | 0 | PRIMARY | 1 | fdpk |..| 14 |..

| stat_test | 1 | ix_test | 1 | fd1 |..| 4 |..

| stat_test | 1 | ix_test1 | 1 | fd1 |..| 4 |..

| stat_test | 1 | ix_test1 | 2 | fd2 |..| 14 |..

+-----------+------------+----------+--------------+-------------+..+-------------+..

이 결과는 상당히 현실적으로 보인다. 하지만, 데이터가 많아져 페이지 수가 많아지면 이 예측은 상당히 어긋날 가능성도 높아질 것이다.

내부적인 처리는 소스를 확인하지 않는 이상 모르겠지만, 각 Event 별로 수집되는 통계 정보의 수준이 다른 것이 아닌가 생각이 된다.

결론적으로 InnoDB 테이블도, 생성 및 초기 Open된 시점이 오래된 테이블의 경우에는 명시적인 ANALYZE TABLE ...;

또는 ALTER TABLE ... ENGINE=INNODB; 등의 명령으로 통계 정보를 업데이트해 줄 필요는 있어 보인다.

into MySQL

2010년 12월 25일 토요일

MyISAM 테이블과 InnoDB 테이블의 통계 정보

댓글 없음:

댓글 쓰기