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 대한 설정 값을 수정하고 정상 작동확인한 내용을 정리하였다.

 

서버 구성

Host

IP

VIP

Role

DevMMM-Witness

192.168.0.1

 

Monitoring

DevMySQLMMM-1

192.168.0.2

192.168.0.102

Master 1

DevMySQLMMM-2

192.168.0.3

192.168.0.103

Master 2

 

·       Write VIP : 192.168.0.101

 

MNM 구성

·       MMM Monitor : MMM Agent 서버의 상태를 체크하고 상태에 따라 역할(reader, writer) 변경,관리

·       MMM Agent : MMM 에서 reader, writer 역할을 하는 구성원

·       MMM VIP : 유동적으로 writer 마스터 역할을 변경할 있도록 writer 구성하는 virtual IP

·       역할구성 : master역할을 있는 writer후보자들은는 두개로 구성하고, 나머지는 slave 역할만 하도록 구성

 

 

DevMMM-Witness

DevMySQLMMM-1

DevMySQLMMM-2

MMM 사용할 계정 생성

 

CREATE USER 'mmm_monitor'@'%' IDENTIFIED BY 'PASSWORD';

CREATE USER 'mmm_agent'@'%' IDENTIFIED BY 'PASSWORD';

 

GRANT ALL PRIVILEGES on *.* TO'mmm_monitor'@'%';

GRANT ALL PRIVILEGES on *.* TO'mmm_agent'@'%';

 

FLUSH PRIVILEGES;

 

호스트에 사용자 계정 추가

 

useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

 

MMM 설치

sudo yum install mysql-mmm mysql-mmm-monitor

sudo yum install mysql-mmm mysql-mmm-agent

sudo yum install mysql-mmm mysql-mmm-agent

mmm_common.conf 설정

sudo vi /etc/mysql-mmm/mmm_common.conf

 

아래 설정 입력

active_master_role          writer

 

<host default>

        cluster_interface eth0

        pid_path /run/mysql-mmm-agent.pid

        bin_path /usr/libexec/mysql-mmm/

        replication_user repluser

        replication_password PASSWORD

        agent_user mmm_agent

        agent_password PASSWORD

</host>

 

<host DevMySQLMMM-1>

        ip 192.168.0.2

        mode master

        peer DevMySQLMMM-2

              mysql_port 3306

</host>

 

 

<host DevMySQLMMM-2>

        ip 192.168.0.3

        mode master

        peer DevMySQLMMM-1

              mysql_port 3306

</host>

 

<role writer>

        hosts DevMySQLMMM-1, DevMySQLMMM-2

        ips 192.168.0.101

        mode exclusive

</role>

 

<role reader>

        hosts DevMySQLMMM-1,DevMySQLMMM-2

        ips 192.168.0.102,192.168.0.103

        mode balanced

</role>

mmm_agent.conf 설정

 

sudo vi /etc/mysql-mmm/mmm_agent.conf

 

아래 설정 입력

include mmm_common.conf

this DevMySQLMMM-1

sudo vi /etc/mysql-mmm/mmm_agent.conf

 

아래 설정 입력

include mmm_common.conf

this DevMySQLMMM-2

mmm_mon.conf 설정

sudo vi /etc/mysql-mmm/mmm_mon.conf

 

아래 설정 입력

include mmm_common.conf

 

<monitor>

    ip                  127.0.0.1

    pid_path            /run/mysql-mmm-monitor.pid

    bin_path            /usr/libexec/mysql-mmm/

    status_path         /var/lib/mysql-mmm/mmm_mond.status

    ping_ips            192.168.0.2,192.168.0.2

    auto_set_online     60

</monitor>

 

<host default>

    monitor_user        mmm_monitor

    monitor_password    PASSWORD

</host>

debug 0

 

 

부팅시 자동 시작 등록

sudo systemctl enable mysql-mmm-monitor

sudo systemctl enable mysql-mmm-agent

sudo systemctl enable mysql-mmm-agent

MMM 시작

sudo systemctl start mysql-mmm-monitor

sudo systemctl start mysql-mmm-agent

sudo systemctl start mysql-mmm-agent

 

MMM동작 확인

·       sudo mmm_control show

·       sudo mmm_control checks all

 

·       Role Change (Run as Monitor)

sudo mmm_control move_role writer DevMySQLMMM-1

 

 

MMM Troubleshooting

MMM 서비스 아래와 같이 ADMIN OFFLINE 표시한 경우 아래 명령어 실행

 

mmm_control set_online mysqltest-2

 

 

[참고자료]

·       https://mysql-mmm.org/mmm2_guide.html

 

 

2020-01-31 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MMM, MySQL 복제, MySQL 이중화, MySQL Multi Master, 멀티마스터 복제, HA, Database 이중화, DBA

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 포함된 컬럼의 데이터를 가져오는 방식이다. Two-pass 알고리즘은 같은 테이블을 2 읽어야 하기 때문에 매우 불합리해 보일 있으나, Sing-pass 알고리즘은 많은 소트 버퍼의 공간이 필요하다.

 

MySQL 5.0 MariaDB 5.X 버전부터는새로운 정렬 알고리즘은 Single-pass 방식이 사용되지만 아래와 같은 경우에는 Two-pass 알고리즘이 사용된다.

·       레코드의 크기가 max_length_for_sort_data 파라미터로 설정된 값보다

·       BLOB 이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될때

 

싱글패스 알고리즘은 정렬 대상 건수가 작을때 빠른 성능을 보이며, 패스 알고리즘은 데이터 레코드의 건수가 많을 효율적이다. 쿼리에 ORDER BY 사용되면 정렬을 하기 위해 아래 3가지 방식중 하나로 처리 된다. 옵티마이저는 가장 먼저 인덱스를 이용할 있을지 검토하고, 인덱스가 없을 경우 조건에 일치하는 레코드를 검색하여 버퍼에 저장하면서 정렬을 처리(Filesort)한다.

정렬 처리 방법

실행 계획의 Extra 코멘트

인덱스 사용한 정렬

별도의 내용 표기 없음

트라이빙 테이블만 정렬

(조인이 없는 경우 포함)

“Using filesort” 표시됨

조인 결과를 임시 테이블로 저장한 , 임시 테이블에서 정렬

“Using temporary; Using filesort” 같이 표시됨

 

하나의 테이블로부터 SELECT해서 정렬을 하는 경우라면 임시 테이블이 필요하지 않다. 하지만 2 이상의 테이블을 조인해서 결과를 정렬해야한다면 임시 테이블이 필요할 수도 있다. 드라이빙 테이블만 정렬된 경우에는 임시 테이블을 사용하지 않는다.

 

 

 

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

 

MySQL, MariaDB, Sort Buffer, Single-pass, Two-pass , Filesort

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 만으로 충분할 수도 있지만, 정렬해야하는 데이터가 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, 리드어헤드, 미리읽기,

+ Recent posts