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, 리드어헤드, 미리읽기,

MySQL /MariaDB 통계 정보

 

·       Version : MySQL 5.7, MariaDB 10.0

 

비용기반 최적화에서 가장 중요한것은 통계 정보이다. 통계 정보가 정확하지 않다면, 옵티마이저는 엉뚱한 실행계획을 생성하기 때문이다. MySQL, MariaDB 통계 정보 또한 일반 다른 RDBMS 같이 비용기반 옵티마이저를 사용하지만 아직 상용 RDBMS 비해서는 다양하지 않다.

 

MariaDB 10.0 MySQL 5.6 기능을 포함하고 있기 때문에 MySQL 5.6 매우 유사하다. MySQL 5.6 부터는 InnoDB 스토리지 엔진을 사용하는 테이블에 대한 통계 정보를 영구적(Persistent)으로 관리할 있다. MySQL 5.5 버전 까지는 show index from 으로 인덱스 칼럼의 분포를 확인할 있었지만 MySQL 5.6 부터는 mysql 데이터베이스에서 innodb_index_stats 테이블과 innodb_table_stats  테이블에서도 인덱스를 조회할 수있다.

show index from nclick.nclick;

 

 

 

select * from mysql.innodb_index_stats;

 

 

 

select * from mysql.innodb_table_stats;

 

 

MySQL 5.6 부터는 테이블을 생성할 STATS_PERSISTENT 옵션을 사용하여 통계 정보를 영구보관할 있다.

·       0 : 통계 정보를 MySQL 5.5 이전의 방식으로 관리, 단기적으로 사용

·       1 : 통계 정보를 innodb_index_stats, innodb_table_stats  테이블에 저장하여 영구 사용

·       DEFAULT : 기본값 1 설정을 적용.

 

Innodb_stats_auto_recalc 시스템 설정 변수값을 OFF 설정해도 통계가 자동으로 수집되는것을 막을 있다.

·       1 : 통계를 자동으로 수집

·       0 : ANALYZE TABLE 명령이 있을때만 수집

·       DEFAULT : 기본값 0 설정을 적용

 

CREATE TABLE `t1` (

`id` int(8) NOT NULL auto_increment,

`data` varchar(255),

`date` datetime,

PRIMARY KEY  (`id`),

INDEX `DATE_IX` (`date`)

) ENGINE=InnoDB,

  STATS_PERSISTENT=1,

  STATS_AUTO_RECALC=1,

  STATS_SAMPLE_PAGES=25;

 

 

[참고자료]

·       Configuring Persistent Optimizer Statistics Parameters  : https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html

·       Configuring Non-Persistent Optimizer Statistics Parameters : https://dev.mysql.com/doc/refman/5.7/en/innodb-statistics-estimation.html

 

 

 

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

 

MySQL, MySQL STATS, innodb_index_stats, innodb_table_stats, MySQL 통계, 히스토 그램, 옵티마이저

MySQL/MariaDB 파일 읽기/쓰기시 발생할 있는 secure-file-priv 오류

 

·       Version : MySQL 5.7

 

MySQL에서 LOAD DATA SELECT.. INTO OUTFILE, LOAD_FILE() 함수를 사용할 경우, 아래와 같은 오류와 함께 실행이 실패되는 경우가 있다.

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

 

이러한 오류는 MySQL 시스템 설정 문제로, 보안과 관련이 있다. , MySQL 허용하는 경로의 위치에서만 파일을 읽고 있도록 지정된 것이며, 아래 스크립트를 통해서 현재 사용할 있는 경로를 확인할 있다.

SHOW VARIABLES LIKE "secure_file_priv"

 

결과가 NULL 경우 어떠한 경로도 지정되어 있지 않다는 뜻이며,  디렉터리 경로가 표시될 경우 해당 위치에서는 파일을 읽고 쓸수 있다는 뜻이다. 또한 아무 값이 표시되지 않으면, 어떠한 위치에서도 파일을 읽고 있다.

해당 값을 수정하기 위해서는 설정 파일에 아래 스크립트를 참고하며, 서비스 재시작이 필요하다. my.cnf 또는 my.ini mysqld 항목에 추가한다.

[mysqld]

secure-file-priv = ""

 

 

[참고자료]

https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_secure-file-priv

 

 

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

 

MySQL, MySQL security, secure_file_priv, LOAD DATA, SELECT INTO OUTFILE, LOAD_FILE()

MySQL/MariaDB ROW Size Limit

 

·       Version : MySQL 5.7

 

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

Error Code: 1118. Row size too large (> 1982). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

 

해당 원인을 찾아본 결과, 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

+ Recent posts