Data Science Lab

MySQL Master DB를 백업하여 Replication Slave DB 구성하기 · Version : MySQL 5.7.5 Later MySQL DB를 처음 구축할때 마스터DB와 슬레이브DB를 구성하는것이 아니라면 반드시 마스터 DB의 데이터를 백업하여 슬레이브DB를 구성 하여야 한다. 이때 특정 시점까지 (Point in Time)의 마스터 DB를 백업하면, 백업파일에는 백업이 완료된 시점의 바이너리 로그파일과 포지션 정보가 기록된다. 슬레이브DB에서는 마스터DB 백업 파일 이후로 발생된 데이터 변경사항을 전달받아 레플리케이션 한다. InnoDB 스토리지 엔진은 트랜잭션을 지원하므로 특정 시점까지 백업한 후 백업 시점의 마지막 바이너리 로그 파일과 로그 포지션 정보로 마스터 DB와 슬레이브 DB를..

MySQL InnoDB Buffer Pool Resizing Online · Version : MySQL 5.7.5, 8.0 MySQL 5.7.5 부터 InnoDB 버퍼풀의 크기를 서비스 가동중에도 동적으로 조절할 수 있다. 동적으로 버퍼풀 확대 또는 축소를 제공하기 위해 청크 크기를 정의하는 새로운 변수인 innodb_buffer_pool_chunk_size 가 도입되었으며, 이 변수는 동적이 아니며 잘못 구성되면 원하지 않는 상황이 발생할 수도 있다. 아래 그림은 innodb_buffer_pool_size, innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size가 상호작용하는 방식을 나타낸것이다. 버퍼풀은 여러 인스턴스를 보유할 수 있으며 각 인스..

MySQL MMM 구성 (Centos7 + MySQL 8.0) · Version : MySQL 8.0.XX, Centos 7 MySQL MMM(MySQL Multi-Master) 을 구성하는 방법에 대해서 살펴본다. 이번 포스트에서는 MMM 구성에 대해서만 다루므로 MySQL 설치 및 Master-Slave 구성은 다른 글을 참고할 수 있도록 한다. MMM구성에 관한 글을 찾아보면 대부분 Centos6 기반의 환경에서 설치된 글을 많이 볼 수 있다. 필자의 경우 Centos7 환경에서 구성을 진행하였는데, Centos7 버전에서는 공식 가이드 문서에서 제공하는 코드와 조금 다르게 디렉터리 경로가 바뀐 부분이 있어 처음 설치시 오류가 발생하였다. 디렉터리 PATH에 대한 설정 값을 수정하고 정상 작동확인한..

MySQL/MariaDB Sort Buffer · Version : MySQL, MariaDB MySQL/MariaDB에서는 데이터를 정렬(ORBER BY)하기 위해 별도의 메모리 공간을 할당하는데 이때 사용되는 메모리가 Sort buffer이다. 이 메모리는 정렬이 필요할 경우에만 할당되며, 쿼리 실행이 완료되면 시스템으로 즉시 반납된다. Soft buffer의 크기는 시스템 설정 변수인 sort_buffer_size로 조정 할 수 있다. sort_buffer_size는 byte단위로 표시된다. show variables where Variable_Name like '%sort_buffer%'; 정렬해야하는 데이터의 크기가 작은 경우에는 Sort buffer 만으로 충분할 수도 있지만, 정렬해야하는 데..

MySQL/MariaDB Full Table Scan · Version : MySQL, MariaDB Full Table Scan은 인덱스를 사용하지 않고 테이블 데이터를 처음부터 끝까지 읽어서 요청 작업을 처리하는것을 의미한다. MySQL 및 MariaDB에서는 아래와 같은 조건일때, Full Table Scan을 사용한다. · 테이블의 레코드 건수가 적어서 인덱스를 통해 읽는것보다 직접 테이블을 읽는것이 더 빠르다고 판된될때 (일반적으로 매우 적은페이지( 1페이지)일때) · WHERE절이나 ON절에 사용할 수 있는 인덱스가 없을 경우 · 인덱스 레인지스캔을 사용할 수 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 테이블 전체를 읽는 작업은 많은 디스크 읽기 오버헤드..

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/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이 허용하는 경로의 위치에서만 파일을 읽고 쓸 수 있도록 지정된 것이며, 아래 스크립트를 통해서 현재 사용할 수 있는 경로를 확인할 수 있다. ..

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..

MySQL/MariaDB sql_mode 설정 변경으로 NULL 데이터 처리하기 · Version : MySQL 5.7 MS SQL Server에서 BCP를 이용하여 데이터를 탭 구분형식의 텍스트 파일로 내려받고 MySQL에서는 Bulk load(LOAD DATA INFILE)를 사용하여 데이터를 복원할때 아래와 같은 에러가 지속적으로 발생하였다. Error Code: 1366. Incorrect decimal value … 이 문제는 Decimal 컬럼에 NULL값을 입력할때 MySQL의 유효성 문제로 에러가 발생한 것이다. 해당 컬럼은 NULL 허용 컬럼임에도 불구하고 지속적으로 동일한 문제가 발생하여, 해결 방법을 찾아본 결과 아래와 같은 sql_mode 변경으로 해결할 수 있었다. SET SESS..

MySQL/MariaDB Timezone 설정 및 확인 · Version : MySQL 5.7, Docker MySQL/MariaDB를 운영할때, 타임존에 대해서 알아본다. 아래 스크립트는 현재 데이터베이스의 타임존을 확인한다. 타임존이 SYSTEM 으로 표시되면 별도의 타임존 설정이 되어 있지 않다는 뜻이며 시스템의 타임존을 사용하겠다는 뜻이다. SELECT @@GLOBAL.time_zone, @@SESSION.time_zone, @@system_time_zone; 데이터베이스 설정에서 default-time-zone 변경 방법은 my.ini (my.cnf)에서 [mysqld] 영역에 타임존을 추가한다. 아래 스크립트는KST로 타임존을 설정하였으며 스크립트 적용 후MySQL 서비스 재시작이 필요하다. ..