Data Science Lab

MySQL/MariaDB Sort Buffer

·       Version : MySQL,  MariaDB

MySQL/MariaDB에서는 데이터를 정렬(ORBER BY)하기 위해 별도의 메모리 공간을 할당하는데 이때 사용되는 메모리가 Sort buffer이다. 이 메모리는 정렬이 필요할 경우에만 할당되며, 쿼리 실행이 완료되면 시스템으로 즉시 반납된다. Soft buffer의 크기는 시스템 설정 변수인 sort_buffer_size로 조정 할 수 있다. sort_buffer_size는 byte단위로 표시된다.

정렬해야하는 데이터의 크기가 작은 경우에는 Sort buffer 만으로 충분할 수도 있지만, 정렬해야하는 데이터가 sort buffer보다 큰 경우에는 정렬해야하는 데이터를 여러 조각으로 나누어서 처리되며, 임시 저장을 위해 디스크를 사용한다. 그리고 디스크에 저장된 데이터를 다시 메모리로 읽어 처리하고, 다시 디스크에 저장하는 행위를 반복한다.

여러  조각으로 나누어진 데이터를 정렬을 위해서 디스크에 쓰고 읽고를 반복하는 것을 Multi-Merge 라고 하는데,  Multi-Merge 횟수는  sort_merge_passes 라는 변수에 누적되기 때문에 모니터링이 가능하다. Multi-Merge가 발생하게되면 디스크를 사용하는데, 디스크의 성능은 메모리보다 느리기 때문에 실제 쿼리 요청이 느려질 수 있다. 이러한 디스크 사용을 줄이기 위해 sort_buffer_size를 증가 시킬수도 있지만, 특정 크기 이상 부터는 더 이상의 성능 향상을 기대하기 어렵다. 아래 그림의 테스트 자료를 보면 1MB 부터는 큰 성능 향상이 없는것을 확인할 수 있다.

출처 : https://www.percona.com/blog/2010/10/25/impact-of-the-sort-buffer-size-in-mysql/

sort_buffer_size의 영역은 각 세션 마다 할당되기 때문에 너무 큰 sort_buffer_size는 자칫하면 메모리 부족현상을 겪을 수 있다. 또한 메모리가 부족할시 OS에서는 강제적으로 프로세스를 Kill할 수 있으므로 MySQL/MariaDB의 프로세스가 중지될 수도 있기 때문에 주의해야 한다.

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

MySQL, MariaDB, Sort Buffer, sort_buffer_size, sort_merge_passes, Multi-Merge

MySQL/MariaDB Full Table Scan

·       Version : MySQL,  MariaDB

Full Table Scan은 인덱스를 사용하지 않고 테이블 데이터를 처음부터 끝까지 읽어서 요청 작업을 처리하는것을 의미한다. MySQL 및 MariaDB에서는 아래와 같은 조건일때, Full Table Scan을 사용한다.

·       테이블의 레코드 건수가 적어서 인덱스를 통해 읽는것보다 직접 테이블을 읽는것이 더 빠르다고 판된될때 (일반적으로 매우 적은페이지( 1페이지)일때)

·       WHERE절이나 ON절에 사용할 수 있는 인덱스가 없을 경우

·       인덱스 레인지스캔을 사용할 수 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우

테이블 전체를 읽는 작업은 많은 디스크 읽기 오버헤드를 발생시키기 때문에 데이터를 읽을때 한번에 여러개의 블록이나 페이지를 읽어오도록 설계되어 있다. 하지만 MariaDB에서는 풀 테이블 스캔을 실행 할때 한번에 몇개의 페이지를 읽어 올지 설정하는 변수는 없다.

InnoDB나 XtraDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 Read Ahead 작업이 자동으로 시작된다. 이 작업은 어떤 영역의 데이터가 앞으로 필요해질 것을 예측하여 요청이 오기전에 미리 읽어 버퍼풀에 두는 것을 의미한다.

풀테이블 스캔이 실행되면 처음 몇개의 페이지는 포그라운드 스레드(Foreground thread, 클라이언트 스레드)가 페링지를 읽기를 실행하지만, 특정 시점부터는 일긱 작업을 백그라운드 스레드로 넘겨 한버네 4개 또는 8개의 페이지를 읽으면서 계속해서 그 페이지를 증가시켜 최대 64개의 페이지까지 읽어 버퍼풀에 저장한다. 포그라운드 스레드는 버퍼풀의 데이터를 가져다 쓰면 되므로 쿼리가 빨리 처리된다. MariaDB의 XtraDB에서는 Read Ahead의 설정은 innodb_read_ahead_threshold 시스템 변수를 이용해 변경할 수 있다.  이 옵션값을 낮추면 자주 Read Ahread가 발생한다. 하지만 빈번한 Read Ahead는 불필요한 I/O 작업을 유발시키므로 주의해야 한다.

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

MySQL, MariaDB, Full Table Scan, 풀 테이블 스캔, Read Ahead, 리드어헤드, 미리읽기,

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값이 클수록 히스토그램의 정확도는 높아지지만 저장공간을 많이 사용한다.

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

MySQL/MariaDB ROW Size Limit

·       Version : MySQL 5.7

SQL Server에서 사용하던 일부 데이터를 MySQL 서버로 마이그레이션 작업 진행중, 컬럼수가 많은 (또는 컬럼의 길이가 큰 테이블) 테이블이 MySQL에서는 아래와 같은 오류와 함께 테이블이 생성되지 않는 문제가 발생하였다.

해당 원인을 찾아본 결과, SQL Server의 경우 한 페이지의 크기가 8K로 고정이 되어있고, Row의 최대 사이즈는  8K 이다. 하지만  MySQL의 경우 InnoDB를 사용할 경우 페이지 사이즈를 사용자가 설정 할 수 있으며, 페이지에서 사용할 수 있는 최대 ROW크기는 페이지 크기의 약 절반보다 약간 작다. 예를들어 MySQL의 기본 Page 크기는 16K 인데, 최대 행 크기는 8K 보다 약간 작다.

·       Limits on InnoDB Tables :  https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html

·       Limits on Table Column Count and Row Size : https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

또한 BLOB이나 TEXT의 경우 LOB 영역을 사용한다고 하더라도, 9~12바이트의 포인터 값을 가지고 있다.

최근 SSD의 보급으로 SSD 최적화된 아키텍처를 제공하기 위해 일부 서비스에서는 페이지의 크기로 4K를 할당하는 경우가 있다.  이 경우 한 로우에 저장할 수 있는 데이터의 크기가 작아지므로 미리 데이터를 설계할때 주의가 필요하다.

[참고자료]

·       Limits on InnoDB Tables :  https://dev.mysql.com/doc/refman/8.0/en/innodb-restrictions.html

·       Limits on Table Column Count and Row Size : https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html

2019-07-19 / 강성욱 / http://sungwookkang.com

MySQL, MySQL column limit, MySQL row size, MySQL Innodb, page size, Row size limit, MariaDB