인사이트리포트

들어가며

DBMS에서 통계 정보는 데이터베이스 성능을 최적화하는데 중요한 역할을 한다. 이러한 통계는 쿼리 최적화, 리소스 관리, 인덱스 활용 등 다양한 측면에서 데이터베이스의 효율성을 높이는데 도움을 준다. 통계 정보 수집을 통해 데이터베이스는 좀 더 정확한 실행 계획을 수립하여 쿼리 성능을 향상 시킬 수 있기 때문에, DBMS의 성능을 최적화하려면 통계 정보 수집이 필수적이다.

오라클을 비롯한 일반적인 RDBMS에서 ‘통계 정보를 수집한다.’는 것은, 옵티마이저가 쿼리 실행 계획을 수립할 때 최적의 계획을 세울 수 있도록 테이블의 행 수, 인덱스 컬럼의 분포도, 컬럼의 히스토그램 데이터 등을 수집하는 것을 의미한다. 이러한 통계 정보는 DBMS 성능 최적화에 중요한 요소이며, 이를 효과적으로 수집 및 관리 하는 것 또한 중요한 과제이다.

이번 인사이트 리포트에서는 MariaDB의 통계 정보 수집 방식 및 항목을 이해하고, 통계 정보를 통한 최적화 방안에 대해 알아보고자 한다.

쿼리 실행 절차

우선, 통계 정보에 대한 내용을 살펴보기 전에 쿼리가 어떻게 실행되는지 간략히 알아보고자 한다.

MariaDB에서 쿼리가 실행되는 과정은 크게 세 가지 단계로 나눌 수 있다.

SQL 쿼리가 수행되면 MariaDB 서버에서 SQL 파싱과 실행 계획 수립 등의 작업이 이루어진다. 이 후 쿼리의 실행은 MariaDB 서버와 스토리지 엔진이 함께 처리한다. 이 과정에서 쿼리 최적화는 옵티마이저가 담당하며, 옵티마이저는 MariaDB 서버의 성능에 있어 매우 중요한 역할을 한다. 최적화 과정에서 옵티마이저가 효율적이고 정확한 실행 계획을 수립하기 위해서는 테이블에 대한 정확한 통계 정보가 필수적이다. 통계 정보는 데이터의 분포, 크기, 그리고 인덱스에 대한 정보를 포함하며, 이를 바탕으로 옵티마이저는 최적의 쿼리 실행 경로를 결정하게 된다.

이전 버전의 통계 정보

MariaDB의 10.0 버전 이전까지의 InnoDB 통계 정보는 Disk에 저장되지 않아 서버를 재 기동하게 되면 스토리지 엔진에서 통계 정보를 다시 계산해야 했다. 이로 인해 일관되지 않은 통계 정보를 제공하거나, 데이터 종류에 대한 제약이 있었고, 통계에 대한 통제 불가 및 통계 관리 제한 등 여러가지 문제들이 발생되게 되면서 데이터베이스 통계 품질에 대한 개선이 필요하게 되었다.

통계 정보 수집 개요

MariaDB에서 통계 정보 수집은 크게 엔진 통계, Slow Query 통계, 그리고 사용자 통계 정보로 구분 할 수 있다. Slow Query와 사용자 통계 정보는 사용자가 수행한 쿼리의 결과를 바탕으로 실행계획 결과를 보여주거나 테이블 별 수행 통계 정보를 제공하기 위한 정보이다. 반면, 쿼리 플랜과 실행계획 분석에 직접적으로 영향을 미치는 것은 엔진에서 수집되는 통계 정보이다.

MariaDB는 다양한 스토리지 엔진의 특성과 요구사항을 반영하여 최적의 성능을 제공하고, 각 엔진의 특화된 기능을 최대한 활용하기 위해 엔진 통계 수집 방식을 두 가지로 나누어 관리한다. 이를 통해 사용자에게 더 나은 쿼리 성능과 관리 옵션을 제공할 수 있다.

InnoDB 영구 통계(InnoDB Persistent Statistics)

이 방식은 InnoDB 엔진에서 사용하는 통계 수집 방법이다. InnoDB는 테이블과 인덱스의 통계를 디스크에 영구적으로 저장하고, 이를 기반으로 쿼리 최적화를 수행한다. InnoDB 통계는 데이터를 기반으로 자동으로 수집되며, 테이블이나 데이터의 변동에 따라 통계 정보가 지속적으로 업데이트 된다. 특히 트랜잭션 처리와 높은 동시성을 지원하는 InnoDB 엔진의 특성을 반영하여 쿼리 최적화에 중요한 역할을 한다.

엔진 독립적 테이블 통계(Engine-Independent Table Statistics)

이 방식은 다양한 스토리지 엔진(MyISAM, InnoDB, Aria 등)을 사용하는 테이블에 대한 통계를 수집하고, 이를 통해 일관된 쿼리 최적화를 가능하게 하며, 유연한 통계 관리를 지원한다. 스토리지 엔진과 관계없이 MariaDB 서버에 의해 관리되며, 쿼리 최적화에 필요한 정보를 제공한다. 또한, 엔진에 독립적이기 때문에, 서로 다른 스토리지 엔진을 사용하는 테이블 간의 일관된 최적화가 가능하다.

통계 정보 수집 및 갱신 기법

통계 정보는 InnoDB 테이블 통계를 사용하는 InnoDB 영구 통계 수집, 엔진 독립적 통계 수집, 수동 통계 수집, 히스토그램 통계 수집으로 나눌 수 있으며, 각 방식의 특징은 다음과 같다.

InnoDB 영구 통계(InnoDB Persistent Statistics)

InnoDB는 테이블과 인덱스에 대한 통계 정보를 자체적으로 관리하며, 테이블 통계 정보의 갱신 비율 및 샘플링 크기 등을 상황에 따라 조절할 수 있다.

InnoDB 스토리지 엔진에서 관리하는 주요 통계 정보 변수는 아래와 같다.

• innodb_stats_persistent

• innodb_stats_auto_recalc

• innodb_stats_persistent_sample_pages

• innodb_stats_traditional

• innodb_stats_transient_sample_pages

Persistent 관련 설정은 CREATE/ALTER TABLE 문에서 추가 옵션을 통해 테이블 별로 설정 할 수 있어서 테이블의 사용 목적 및 성격에 따라 유연하게 설정할 수 있다.

InnoDB 영구 통계 정보를 관리하는 테이블과 수집 항목 및 적용 예시는 다음과 같다.

• 수집 항목

• 적용 예시

엔진 독립적 테이블 통계(Engine-Independent Table Statistics)

MariaDB에서는 쿼리 최적화 시 통계 정보를 어떻게 활용할 지를 지정하는 여러 가지 변수를 제공한다. 그 중 “use_stat_tables” 변수는 엔진 독립적 테이블 통계를 제어 할 수 있으며 설정 가능한 옵션은 아래와 같다.

• use_stat_tables

“complementary” 또는 “preferably”로 설정하면 “ANALYZE TABLE <table name>” 명령어를 수행할 때 엔진 독립적 통계 정보를 수집한다. “complementary_for_queries” 또는 “preferably_for_queries”로 설정하면, “ANALYZE TABLE <table name>” 명령어에 “PERSISTENT FOR” 옵션을 추가해서 수행해야만 통계 수집이 가능하다.

PERSISTENT FOR 옵션을 사용한 통계 수집 방식은 특정 테이블과 인덱스, 컬럼에 대한 통계 정보를 수집할 수 있으며, 통계 정보 관리 테이블과 수집 항목 및 수집 예시는 다음과 같다.

• 수집 항목

• 수집 예시

엔진 독립적 테이블 통계 방식은 전체 테이블 및 전체 인덱스 스캔을 수행하여 수집하며, 히스토그램 통계도 같이 수집되기 때문에 비용이 많이 발생할 수 있으므로 필요에 따라 통계 정보를 수집하는 것이 좋다.

통계 정보 히스토그램

앞서 설명한 엔진 독립적 테이블 통계 정보 중 히스토그램 통계는 데이터베이스의 쿼리 옵티마이저가 특정 테이블의 열에 저장된 값들의 분포를 이해하는데 도움을 준다. 인덱싱 된 열 뿐만 아니라 인덱싱 되지 않은 열에 대해서도 히스토그램을 수집할 수 있으며, 특히 데이터 값들이 고르게 분포되지 않았을 때 쿼리 성능을 최적화 하는데 유용하다. 히스토그램 정보는 “mysql.column_stats” 테이블에 저장되므로, 이는 본질적으로는 엔진 독립적 테이블 통계의 하위 집합이라고 할 수 있다.

히스토그램에 대한 개념과 함께 확인해야 할 변수 및 변수 설정 시 고려할 사항들을 살펴보겠다.

1. 히스토그램 개념 및 역할

히스토그램은 특정 열의 값의 분포를 시각화하는 데이터 구조로, 특히 데이터가 불균형하게 분포되는 등 특정 값에 집중된 경우 히스토그램 통계가 유용 할 수 있다. 히스토그램을 사용하기 전에는 최상의 실행 계획을 검색할 때 인덱싱 되지 않은 열의 모든 조건은 무시되었으나, 히스토그램을 사용함으로써 인덱싱 된 열과 인덱싱 되지 않은 열 모두에 대한 히스토그램 통계 정보를 수집하여 옵티마이저가 활용할 수 있게 되었다.

MariaDB에서 사용하는 Height-Balanced(HB) 히스토그램은 쿼리 옵티마이저가 비 인덱스 열에 대해 쿼리 성능을 향상 시키기 위해 사용하는 데이터 구조이다. 옵티마이저는 열 값의 분포 통계 정보를 바탕으로 가장 효율적인 실행 계획을 결정하는데, HB 히스토그램은 열의 값을 여러 개의 “버킷”으로 나누고, 각 버킷에 대략 동일한 수의 행을 포함시킨다. 이를 통해 옵티마이저는 특정 범위의 값을 조회할 때, 몇 개의 행을 반환할지 더 정확하게 추정할 수 있게 된다.

2. 히스토그램 유형

MariaDB 10.7부터는 JSON 기반 히스토그램(JSON_HB)이 도입되어, 기존의 데이터 불균형으로 인한 성능 저하 및 이진 형식 저장으로 인한 유연성 부족에 대해 개선 되었다. JSON_HB은 더 복잡한 데이터 패턴을 파악하여 통계를 더 잘 이해할 수 있게 만들어 옵티마이저의 성능을 향상 시킨다.

히스토그램을 관리하는 변수는 다음과 같다.

• histogram_size

• histogram_type

• optimizer_use_condition_selectivity

• 수집 예시

histogram_size 및 histogram_type에 따라 히스토그램 데이터의 분포도 및 정밀도에 영향을 줄 수 있다. histogram_size가 작을수록 데이터 분포에 대한 표현이 단순화 되지만, 크기를 증가 시키면 더 정밀한 통계 정보를 제공할 수 있다.

• 플랜 예시

다음 예시는 조건 선택도(“OPTIMIZER_USE_CONDITION_SELECTIVITY”)에 따라 쿼리 실행 계획이 어떻게 달라지는지 확인해 보고자 한다.

참고로, 테스트를 위해 https://github.com/datacharmer/test_db 에 있는 테스트DB를 설치하였다.

analyze로 엔진 독립적 통계정보를 수집하기 위하여 “use_stat_tales”는 “PREFERABLY”로 설정하였고, 통계 수집 후 테이블 및 히스토그램 통계가 수집된 것을 확인하였다.

쿼리에서 조건을 가진 “titles”테이블의 구조는 다음과 같다.

“titles”테이블에는 PRIMARY [emp_no + title + from_date] 인덱스가 하나 생성되어 있다.

이제 조건 선택도 설정에 따른 실행 계획의 변화를 살펴보겠다.

조건 선택도를 “1”과 ”2”로 설정했을 때는, 테이블에 구성된 인덱스 컬럼을 활용하여 실행계획을 수립된다. 이 경우, “titles”테이블에서 “USING(emp_no)” 조건을 기반으로 먼저 조인을 수행하고, 그 후 필터링하는 방식으로 쿼리가 수행된다.

반면, 조건 선택도를 “3”과 “4”로 설정했을 경우, 인덱스를 사용하지 않는 실행 계획이 수립된다. 이 때는 수행 순서가 거의 동일해 보이지만, filtered 항목에서 차이가 발생하는 것을 확인 할 수 있다. 특히 조건 선택도가 “4”로 설정되었을 때는 히스토그램을 활용하기 때문에 좀 더 정밀한 실행 계획을 수립할 수 있다는 것을 보여준다.

수행 시간을 비교해 보면, 조건 선택도를 “1”로 설정했을 때보다 “4”로 설정 할 경우 수행 속도가 더 빠른 것을 확인할 수 있다.

수동 통계 갱신

MariaDB는 테이블의 스토리지 엔진을 호출하여, 각 스토리지 엔진의 특성에 따라 테이블 통계를 수집한다. 이를 통해 필요한 경우, 특정 테이블의 통계 정보를 수동으로 갱신 할 수 있다. 통계 테이블을 수동으로 업데이트 해야 할 상황은 다음과 같다.

통계 정보를 수동으로 갱신하는 방법은 다음과 같다.

주의할 점은, 통계를 수동으로 갱신할 때 잘못된 수정을 할 경우 성능에 부정적인 영향을 미칠 수 있으므로 신중하게 처리해야 한다.

“ANALYZE TABLE <table name>” 명령을 사용하여 통계를 수집할 경우, 상황에 맞게 다양한 방법으로 수집 가능하다.

마치며

MariaDB의 통계 정보는 쿼리 실행 계획의 정확성을 높이고 자원의 효율적 사용을 가능하게 하므로 데이터베이스 운영에서 매우 중요한 역할을 한다. 통계 수집 방식에 대한 이해를 통해 통계 정보와 관련된 변수 설정을 환경에 맞게 적절히 조정하여 MariaDB의 성능을 개선할 수 있고, 주기적인 통계 갱신은 복잡한 데이터 환경에서도 안정적인 성능을 보장 할 수 있다. 그리고, 통계 정보의 지속적인 모니터링과 최적화는 효율적인 데이터베이스 운영과 최적화된 성능을 실현하는데 도움을 줄 것이라 생각된다.

이 리포트를 통해 통계 정보에 대한 이해와 성능 최적화에 대한 고민에 도움이 되었기를 바란다.