Data Science Lab

ProxySQL 이란 무엇인가 l Version : ProxySQL ProxySQL은 MySQL 및 포크된 데이터베이스(Percona Server 및 MariaDB)를 위한 고성능, 고가용성을 제공하는 프록시이다. 글을 쓰는 현재(2023년 7월)는 GPL 라이선스로 공개되어 있어 누구나 사용할 수 있다. ProxySQL에 대한 자세한 정보는 아래 링크를 참고한다. l 공식 사이트 주소 - https://www.proxysql.com/ l ProxySQL 설치 및 사용 가이드 - https://github.com/sysown/proxysql/wiki l ProxySQL 도커 이미지 - https://hub.docker.com/r/proxysql/proxysql [Proxy서버는 무엇일까] 프록시 서버는 ..

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 Single-pass, Two-pass Sort Algorithm · Version : MySQL 5.X Later, MariaDB 5.X Later MySQL/MariaDB에서는 정렬 알고리즘으로Single-pass 와 Two-pass 알고리즘을 사용한다. 그리고 정렬처리 방식으로 인덱스를 사용한 방식과 테이블을 사용하여 정렬하는 방식이 있다. Single-pass 알고리즘은 Sort Buffer에 정렬 기준 칼럼을 포함한 SELECT에 포함된 컬럼의 데이터를 버퍼 메모리에 담아서 정렬을 수행하는 방식이다. Two-pass 알고리즘은 정렬 컬럼과 프라이머리 키 값만 Sort buffer에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리키로 테이블을 읽어서 SELECT ..

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절에 사용할 수 있는 인덱스가 없을 경우 · 인덱스 레인지스캔을 사용할 수 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 테이블 전체를 읽는 작업은 많은 디스크 읽기 오버헤드..

MariaDB 히스토그램 (옵티마이저가 실행계획 생성시 참고하는 데이터 분포 정보) · Version : MariaDB 10.0 히스토그램은 테이블의 컬럼값의 분포를 분석할 수 있는 통계 정보이다. 히스토그램 정보가 없는 경우에는 하나의 컬럼에서 유니크한 값의 개수에 기반해서 대략적인 분포도를 예측하는 형태로 실행 계획의 비용이 계산되었다. 이 또한 인덱스가 생성된 컬럼에 대해서만 유니크한 개수가 관리되었다. 이처럼 히스토그램이 관리되지 않으면 컬럼에 대한 데이터 분포를 잘 못 예측하여 옵티마이저가 잘못된 실행 계획을 생성할 수도 있다. 히스토그램 기반 통계는 옵티마이저가 선택한 쿼리 계획을 개선하기 위한 메커니즘으로 MariaDB 10.0 버전에서 도입되었다. 이전까지는 실행계획을 생성할 때 인덱싱 되..

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