MariaDB 히스토그램 (옵티마이저가 실행계획 생성시 참고하는 데이터 분포 정보)

 

·       Version : MariaDB 10.0

 

히스토그램은 테이블의 컬럼값의 분포를 분석할 있는 통계 정보이다. 히스토그램 정보가 없는 경우에는 하나의 컬럼에서 유니크한 값의 개수에 기반해서 대략적인 분포도를 예측하는 형태로 실행 계획의 비용이 계산되었다.  또한 인덱스가 생성된 컬럼에 대해서만 유니크한 개수가 관리되었다. 이처럼 히스토그램이 관리되지 않으면 컬럼에 대한 데이터 분포를 예측하여 옵티마이저가 잘못된 실행 계획을 생성할 수도 있다.

 

히스토그램  기반 통계는 옵티마이저가 선택한 쿼리 계획을 개선하기 위한 메커니즘으로 MariaDB 10.0 버전에서 도입되었다.  이전까지는 실행계획을 생성할 인덱싱 되지 않은 컬럼의 조건은 모두 무시되었다. 이번에 도입된 히스토그램은 메커니즘은 인덱스로 만들어진 컬럼 뿐만 아니라 인덱싱되지 않은 컬럼에 대해서 모두 히스토그램 정보를 저장할 있도록 개선되었다. 모든 테이블의 모드 컬럼에 대해서 최대값, 최소값, NULL 값을 가진 비율을 계산하여 mysql.column_stats 라는 테이블에 저장되어 관리된다.

 

MariaDB 10.0 Height-Balanced Histogram 알고리즘을 사용한다. 알고리즘은 컬럼의 모든 값을 정렬해서 동일한 레코드 건수가 되도록 그룹을 개로 나눈다. 그리고 그룹의 마지막 (정렬된 상태에서 가장 큰값) 히스토그램에 저장한다. MariaDB에서는 그룹의 개수를 histogram_size 라는 시스템 설정 변수로 제어할 있다. histogram_size 0 ~ 255 값을 가지며, 크게 설정하면 히스토그램의 정확도는 높아지겠지만 그만큼 저장 공간 분석 시간이 필요하다.

 

 

MariaDB에서는 히스토그램 값을 그룹별로 한바이트씩 할당해서 VARBINARY(255) 타입의 histogram 컬럼에 저장한다. 실제 저장되는 값은 단순 그룹의 최대값이 아닌 아래와 같은 계산을 거쳐서 결과를 histogram 컬럼의 바이트에 순서대로 저장한다.  a값이 클수록 히스토그램의 정확도는 높아지지만 저장공간을 많이 사용한다.

그룹 최대값 / (컬럼 최대값 칼럼 최소값) * a

 

MariaDB에서 히스토그램 생성은 ANALYZE TABLE 명령으로 다른 통계 정보와 함께 생성된다. histogram_size 기본값은 0으로 히스토그램을 사용하지 않는다는 것을 뜻한다.  히스토그램을 사용하려면 반드시 histogram_size 0보다 값으로 설정한다. 또한 정확도를 위해서 histogram_type SINGLE_PREC_HB 또는 DOUBLE_PREC_HB 설정 있다.

 

optimizer_user_condition_selectivity 옵션은 옵티마이저가 최상의 실행 계획을 사용할 있도록 통계를 제어한다.

·       optimizer_user_condition_selectivity = 1 : MariaDB 5.5 사용되었던 선택도 방식을 그대로 유지

·       optimizer_user_condition_selectivity = 2 : 인덱스가 생성된 컬럼에 대해서만 선택도 사용

·       optimizer_user_condition_selectivity = 3 : 모든 컬럼에 대해서 선택도 사용 (히스토그램 사용 안함)

·       optimizer_user_condition_selectivity = 4 : 모든 컬럼에 대해서 선택도 사용 (히스토그램 사용)

·       optimizer_user_condition_selectivity = 5 : 모든 컬럼에 대해서 선택도 사용하면 레인지 스캔이 불가한 컬럼에 대해서 샘플링해서 선택도 사용

 

 

[참고자료]

·       Histogram-Based Statistics : https://mariadb.com/kb/en/library/histogram-based-statistics/

·       http://www.aioug.org/sangam14/images/Sangam14/Presentations/201446_garg_ppt.ppt.pdf

 

 

 

2019-09-17 / 강성욱 / http://sungwookkang.com

 

MariaDB, MariaDB 10.0, 히스토그램, histogram, optimizer_user_condition_selectivity, ANALYZE TABLE, mysql.column_stats, histogram_size, histogram_type

SQL Server Statistics Update row sampling rate and histogram step

-          통계 업데이트 샘플링 비율과 히스토그램 단계

 

·         Version : SQL Server

 

SQL Server에서 통계(statistics)정보는 데이터를 조회할때 검색 조건에 따라 옵티마이저가 해당 컬럼으로 구성되는 인덱스를 사용할지 여부를 결정하는 중요한 참고 정보이다. 통계는 데이터가 있는 컬럼에 대해 인덱스가 만들어질 컬럼값의 히스토그램 관련정보를 이용해서 만들어 진다.

·         SQL Server Statistics : http://sqlmvp.kr/140165557766

 

통계에 대한 샘플링 비율이 높을 수록 통계 정보의 신뢰도는 높아진다. 그렇다면 통계에 대한 샘플링 비율을 Full Scan(전체 샘플링)하였을때, 오히려 히스토그램의 단계가 줄어든 이유는 무엇일까?

히스토그램의 단계 수는 고유 수보다 적을 있다. 경계 지점이 200 미만인 열에도 고유 값의 개수보다 적을 있다. 고유 값만으로 구성된 열인 경우 통합된 히스토그램은 최소 3단계를 포함하게 된다. 따라서 완벽한 분포(빈도 = 1) 경우 데이터 분포를 정확하게 묘사하는데 필요한 단계가 많기 때문에 최소 3단계를 포함하게 된다.

 

아래 실습을 통해서 히스토그램에 대한 분포 변화를 살펴보자. IDENTITY GUID 사용하여1백만건을 데이터를 생성한다.

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[CustomersTableGuid]') AND [type] IN (N'U'))

CREATE TABLE CustomersTableGuid

(

    ID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

)

GO

 

IF NOT EXISTS (SELECT [object_id] FROM sys.objects (NOLOCK) WHERE [object_id] = OBJECT_ID(N'[CustomersTableIdent]') AND [type] IN (N'U'))

CREATE TABLE CustomersTableIdent

(

    ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50)

)

GO

 

SET NOCOUNT ON;

DECLARE @i INT = 0

WHILE (@i <= 1000000)

BEGIN

    INSERT INTO CustomersTableGuid (FirstName, LastName)

    VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))

 

    INSERT INTO CustomersTableIdent (FirstName, LastName)

    VALUES ('FirstName' + CAST(@i AS VARCHAR),'LastName' + CAST(@i AS VARCHAR))

   

    SET @i +=1

END

 

 

FULLSCAN으로 전체 행을 샘플링하여 통계를 업데이트 한다.

UPDATE STATISTICS CustomersTableGuid WITH FULLSCAN

GO

UPDATE STATISTICS CustomersTableIdent WITH FULLSCAN

GO

 

샘플된 행과 단계를 살펴보면 통계에 대해 3 또는 4단계 히스토그램이 있는 것을 확인할 있다.

SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], sp.stats_id, name,

    last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter  

FROM sys.stats AS stat  

CROSS APPLY sys.dm_db_stats_properties(stat.[object_id], stat.stats_id) AS sp 

WHERE stat.[object_id] = OBJECT_ID('CustomersTableGuid')

    OR stat.[object_id] = OBJECT_ID('CustomersTableIdent');

GO

 


 

 

ID열에 대한 히스토그램을 살펴 보면 통계에 대해 3단계 또는 4단계로 표시된다. SQL Server 히스토그램 품질을 유지하면서 가능한 적은 단계로 여러 히스토그램을 압축 한다. 예상대로 FULLSCAN 완벽한 분포이므로 3단계로 압축 있다.

SELECT OBJECT_NAME(stat.[object_id]) AS [TableName], stat.name, sh.stats_id,

    sh.range_high_key, sh.range_rows, sh.equal_rows

FROM sys.stats AS stat

INNER JOIN sys.stats_columns AS sc

    ON stat.stats_id = sc.stats_id AND stat.[object_id] = sc.[object_id]

INNER JOIN sys.all_columns AS ac

    ON ac.column_id = sc.column_id AND ac.[object_id] = sc.[object_id]

CROSS APPLY sys.dm_db_stats_histogram(stat.[object_id], stat.stats_id) AS sh

WHERE (stat.[object_id] = OBJECT_ID('CustomersTableGuid')

    OR stat.[object_id] = OBJECT_ID('CustomersTableIdent'))

    AND ac.name = 'ID';

GO

 


 

통계 샘플을 90% 설정하여 업데이트 한다. (사용자 원한는 입력하여 테스트 가능)

UPDATE STATISTICS CustomersTableGuid WITH SAMPLE 90 PERCENT

GO

UPDATE STATISTICS CustomersTableIdent WITH SAMPLE 90 PERCENT

GO

 

통계 정보를 살펴본다. SQL Server 페이지 데이터를 가져온 다음 이를 전체 분포로 추정한다. 예상대로 표본 분포는 근사치로 빈도가 1 근접하지만 정확히 1 아니다.


 


 

실습을 통해서 히스토그램 단계가 많은 것이 나은 예측을 있다는 판단을 모든 환경에 적용된다고 없다. 하지만 테스트 결과는 유니크한 데이터를 기준으로만 판한단 것이므로 무조건 단계가 낮은것이 좋은 것으로 오해하지 않기를 바란다. 일반적으로 샘플링 비율이 높을 수록 상대적으로 정확한 통계 정보로 인해 옵티마이저가 나은 판단을 있다. 또한 대용량의 테이블 경우, 샘플링 비율이 높을 성능적인 문제가 발생하기 때문에, 통계 업데이트에 대한 주기 비율은 각자의 비즈니스 환경에 따라서 DBA 최적의 컨디션을 유지할 있도록 조절해야 한다.

 

[참고자료]

·         https://blogs.msdn.microsoft.com/sql_server_team/perfect-statistics-histogram-in-just-few-steps/

·         https://docs.microsoft.com/en-us/sql/relational-databases/statistics/statistics

 

 

2018-01-22 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

SQL Server, MS SQL, SQL Statistics, Histogram, SQL Optimizer, SQL Index, query, DB Tuning, SQL Density, update statistics, sys.stats


+ Recent posts