[MySQL] MySQL 5.7에서 8.0으로 업그레이드시 변경되는 설정 옵션 정리

 

l  Version : MySQL 8.X

 

MySQL 5.7 버전과 MySQL 8.0 변화가 있기 때문에, 업그레이드하기 전에 많은 주의가 필요하다. 현재 운영중인 MySQL 5.7X 버전에서 MySQL 8.0.X 업그레이드 프로젝트를 진행하게 되어, 업그레이드시 주의해야 사항 변경되는 사항을 정리해본다.

 

[Data Dictionary]

MySQL Server 8.0에는 트랜잭션 테이블의 데이터베이스 개체에 대한 정보가 포함된 전역 Data Dictionary 통합되어 있다. 이전 MySQL 시리즈에서는 Data Dictionary 메타데이터 파일과 InnoDB 시스템 테이블에 저장되었다. 이전에는 innodb_read_only 시스템 변수를 활성화하면 InnoDB 스토리지 엔진에 대해서만 테이블을 생성하고 삭제할 없었다. MySQL 8.0부터 innodb_read_only 활성화하면 모든 스토리지 엔진에 대해 이러한 작업이 방지된다. 모든 스토리지 엔진에 대한 테이블 생성 삭제 작업은 mysql 시스템 데이터베이스의 데이터 Dictionary 테이블을 수정하지만 해당 테이블은 InnoDB 스토리지 엔진을 사용하며 innodb_read_only 활성화되면 수정할 없다. Data Dictionary 테이블을 수정해야 하는 다른 테이블 작업에도 동일한 원칙이 적용된다.

 

이전에는 STATISTICS 테이블 통계에 대한 INFORMATION_SCHEMA 쿼리가 스토리지 엔진에서 직접 통계를 검색했다면, MySQL 8.0부터는 캐시된 테이블 통계가 기본적으로 사용된다. information_schema_stats_expiry 시스템 변수는 캐시된 테이블 통계가 만료되는 기간을 정의한다. 기본값은 86400(24시간)이며, 테이블에 대해 수동으로 캐시된 값을 업데이트하려면 ANALYZE TABLE 사용할 있다. 캐시된 통계가 없거나 통계가 만료된 경우에는 테이블 통계 열을 쿼리할 스토리지 엔진에서 통계가 검색된다. 항상 스토리지 엔진에서 직접 최신 통계를 검색하려면 information_schema_stats_expiry 0으로 설정한다.

 

INFORMATION_SCHEMA 테이블은 Data Dictionary 테이블에 대한 뷰로, 이를 통해 최적화 프로그램은 해당 기본 테이블에 대한 인덱스를 사용할 있다. 결과적으로 최적화 프로그램 선택에 따라 INFORMATION_SCHEMA 쿼리 결과의 순서가 이전 결과와 다를 있다. 쿼리 결과에 특정 순서 특성이 있어야 하는 경우 ORDER BY 절을 포함한다.

 

INFORMATION_SCHEMA 테이블에 대한 쿼리는 이전 MySQL 시리즈와는 다른 문자로 이름을 반환할 있다. 애플리케이션은 결과 세트에 대해 이름이 대소문자를 구분하지 않도록 테스트해야 한다. 이러한 부분을 방지하려면 필수 문자 대소문자로 이름을 반환하도록 별칭을 사용한다.

 

mysqldump mysqlpump 명령줄에 명시적으로 이름이 지정된 경우에도 이상 INFORMATION_SCHEMA 데이터베이스를 덤프하지 않는다. Data Dictionary 대한 자세한 내용은 아래 링크를 참고한다.

l  Data Dictionary Usage Differences : https://dev.mysql.com/doc/refman/8.0/en/data-dictionary-usage-differences.html

 

 

[caching_sha2_password 인증 플러그인 변경]

caching_sha2_password sha256_password 인증 플러그인은 mysql_native_password 플러그인보다 안전한 비밀번호 암호화를 제공하고, caching_sha2_password sha256_password보다 나은 성능을 제공한다. caching_sha2_password MySQL 8.0부터 기본 인증 플러그인으로 사용된다. 변경 사항은 서버와 libmysqlclient 클라이언트 라이브러리 모두에 영향을 미친다.

서버의 경우 default_authentication_plugin 시스템 변수의 기본값이 mysql_native_password에서 caching_sha2_password 변경된다. 변경 사항은 MySQL 8.0 이상을 설치하거나 업그레이드한 후에 생성된 계정에만 적용된다. 업그레이드된 설치에 이미 존재하는 계정의 경우 해당 인증 플러그인은 변경되지 않은 상태로 유지된다. 기존 사용자를 caching_sha2_password 전환하려면  ALTER USER 문을 사용하여 전환할 있다.

ALTER USER user IDENTIFIED WITH caching_sha2_password BY 'password';

 

MySQL 8.0 이상으로 업그레이드한 호환성 문제가 발생하는 경우 이전 기본 인증 플러그인으로 되돌리도록 서버를 재구성할 있다. 설정을 사용하면 기본의 애플리케이션들이 이전의 패스워드 방식을 사용할 있다. 하지만 방법은 보안에 취약하기 때문에 영구적으로 사용할 없다.

[mysqld]
default_authentication_plugin=mysql_native_password

 

자세한 내용은 아래 링크를 참고한다.

l  Pluggable Authentication : https://dev.mysql.com/doc/refman/8.0/en/pluggable-authentication.html#pluggable-authentication-compatibility

 

 

[caching_sha2_password and the root Administrative Account]

MySQL 8.0으로 업그레이드하는 경우 'root'@'localhost' 관리 계정용 플러그인을 포함하여 기존 인증 플러그인 계정은 변경되지 않은 상태로 유지된다. 새로운 MySQL 8.0 설치의 경우 데이터 디렉터리를 초기화하면 'root'@'localhost' 계정이 생성되고 해당 계정은 기본적으로 caching_sha2_password 사용한다. 따라서 데이터 디렉터리 초기화 서버에 연결하려면 caching_sha2_password 지원하는 클라이언트나 커넥터를 사용해야 한다. 신규 설치 루트 계정이 mysql_native_password 사용하도록 하려면 MySQL 설치하고 평소와 같이 데이터 디렉토리를 초기화한다. 그런 다음 루트로 서버에 연결하고 다음과 같이 ALTER USER 사용하여 계정 인증 플러그인과 비밀번호를 변경한다.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

 

 

[caching_sha2_password and Replication]

모든 서버가 MySQL 8.0.4 이상으로 업그레이드된 복제 시나리오에서 원본 서버에 대한 복제본 연결은 caching_sha2_password 인증하는 계정을 사용할 있다. 이러한 연결의 경우 caching_sha2_password 인증하는 계정을 사용하는 다른 클라이언트와 동일한 요구 사항이 적용된다. , 보안 연결 또는 RSA 기반 암호 교환을 사용한다.

 

 

[Configuration Changes]

MySQL 스토리지 엔진은 자체 파티셔닝 핸들러 제공을 담당하며 MySQL 서버는 이상 일반 파티셔닝 지원을 제공하지 않는다. InnoDB NDB MySQL 8.0에서 지원되는 기본 파티셔닝 핸들러를 제공하는 유일한 스토리지 엔진이다. 다른 스토리지 엔진을 사용하는 파티션된 테이블은 서버를 업그레이드하기 전에 InnoDB 또는 NDB 변환하거나 파티셔닝을 제거하기 위해 변경해야 한다. 그렇지 않으면 나중에 사용할 없다.

 

collation_server collation_database 시스템 변수의 기본값이 latin1_swedish_ci에서 utf8mb4_0900_ai_ci 변경되었다. 결과적으로 개체의 기본 문자 집합과 데이터 정렬은 명시적인 문자 집합과 데이터 정렬을 지정하지 않는 이전과 다르다. 여기에는 테이블, , 저장된 프로그램 데이터베이스와 안에 있는 개체가 포함된다. 이전 기본값이 사용되었다고 가정하면 이를 유지하기 위한 방법은 my.cnf 파일에서 아래 명령을 사용하여 서버를 재시작 한다.

[mysqld]
character_set_server=latin1
collation_server=latin1_swedish_ci

 

복제된 설정에서 MySQL 5.7에서 8.0으로 업그레이드할 , 업그레이드하기 전에 기본 문자 집합을 MySQL 5.7에서 사용된 문자 집합으로 다시 변경하는 것이 좋다. 업그레이드가 완료되면 기본 문자 집합을 utf8mb4 변경할 있다. 또한 MySQL 8.0 MySQL 5.7 수행하지 않는 특정 문자 집합의 허용된 문자에 대한 검사를 시행한다는 점을 기억해야 한다. 이는 알려진 문제로 업그레이드를 시도하기 전에 사용 중인 문자 세트에 대해 정의되지 않은 문자가 주석에 포함되어 있는지 확인해야 한다.  아래 가지 방법 하나로 문제를 해결할 있다.

l  문제의 문자를 포함하는 문자 세트로 문자 세트를 변경

l  문제가 되는 문자를 제거

 

MySQL 8.0.11부터 서버 초기화 사용된 설정과 다른 lower_case_table_names 설정으로 서버를 시작할 없다. 다양한 데이터 사전 테이블 필드에서 사용되는 데이터 정렬은 서버가 초기화될 정의된 lower_case_table_names 설정을 기반으로 하고, 다른 설정으로 서버를 다시 시작하면 식별자 정렬 비교 방법과 관련하여 불일치가 발생하므로 제한이 필요하다.

 

 

[Server Changes]

MySQL 8.0.11에서는 사용자 계정의 비권한 특성을 수정하기 위한 GRANT 사용, NO_AUTO_CREATE_USER SQL 모드, PASSWORD() 함수, old_passwords 시스템 변수 계정 관리와 관련된 이상 사용되지 않는 여러 기능이 제거되었다. 이러한 제거된 기능을 참조하는 명령문을 MySQL 5.7에서 8.0으로 복제하면 복제 오류가 발생할 있다. 제거된 기능을 사용하는 애플리케이션은 이를 방지하도록 수정되어야 하며, 가능하면 MySQL 8.0에서 제거된 기능에 설명된 대로 대안을 사용해야 한다.

 

MySQL 8.0에서 시작 실패를 방지하려면 MySQL 옵션 파일의 sql_mode 시스템 변수 설정에서 NO_AUTO_CREATE_USER 인스턴스를 제거한다. 저장된 프로그램 정의에 NO_AUTO_CREATE_USER SQL 모드를 포함하는 덤프 파일을 MySQL 8.0 서버로 로드하면 오류가 발생한다. MySQL 5.7.24 MySQL 8.0.13부터 mysqldump 저장된 프로그램 정의에서 NO_AUTO_CREATE_USER 제거한다. 이전 버전의 mysqldump 생성된 덤프 파일을 수동으로 수정하여 NO_AUTO_CREATE_USER 인스턴스를 제거해야 한다.

 

MySQL 8.0.11에서는 이상 사용되지 않는 호환성 SQL 모드인 DB2, MAXDB, MSSQL, MYSQL323, MYSQL40, ORACLE, POSTGRESQL, NO_FIELD_OPTIONS, NO_KEY_OPTIONS, NO_TABLE_OPTIONS 제거되었다. 이상 sql_mode 시스템 변수에 할당하거나 mysqldump -- Compatible 옵션에 허용되는 값으로 사용할 없다. MAXDB 제거된다는 것은 CREATE TABLE 또는 ALTER TABLE 대한 TIMESTAMP 데이터 유형이 이상 DATETIME으로 처리되지 않음을 의미한다.

 

제거된 SQL 모드를 참조하는 명령문을 MySQL 5.7에서 8.0으로 복제하면 복제 오류가 발생할 있다. 여기에는 현재 sql_mode 값에 제거된 모드가 포함되어 있는 동안 실행되는 저장 프로그램(저장 프로시저 함수, 트리거 이벤트) 대한 CREATE 문의 복제가 포함된다.

 

MySQL 8.0.3부터 공간 데이터 유형은 열에 저장된 값에 대한 공간 참조 시스템(SRS) 명시적으로 나타내기 위해 SRID 속성을 허용한다. 명시적인 SRID 특성이 있는 공간 열은 SRID 제한되며 해당열은 해당 ID 가진 값만 사용하며 열의 SPATIAL 인덱스는 최적화 프로그램에서 사용하게 된다. 최적화 프로그램은 SRID 속성이 없는 공간 열의 SPATIAL 인덱스를 무시한다. 최적화 프로그램이 SRID 제한되지 않은 공간 열의 SPATIAL 인덱스를 고려하도록 하려면 해당 열을 각각 수정해야 한다.

 

정확한 연산을 수행하는 함수에 대해 ST_ 접두사를 구현하거나 최소 경계 사각형을 기반으로 연산을 수행하는 함수에 대해 MBR 접두사를 구현하는 공간 함수 네임스페이스 변경으로 인해 MySQL 8.0.0에서 여러 공간 함수가 제거되었다. 생성된 정의에서 제거된 공간 함수를 사용하면 업그레이드가 실패할 있다. 업그레이드하기 전에 제거된 공간 함수에 대해 mysqlcheck --check-upgrade 실행하고 찾은 모든 것을 ST_ 또는 MBR이라는 이름의 대체 항목으로 변경해야 한다. 제거된 함수목록은 아래 링크를 참고한다.

l   Features Removed in MySQL 8.0 : https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals

 

BACKUP_ADMIN 권한은 MySQL 8.0.3 이상으로 전체 업그레이드를 수행할 RELOAD 권한이 있는 사용자에게 자동으로 부여된다.

MySQL 8.0.13부터는 기반 또는 혼합 복제 모드와 명령문 기반 복제 모드의 임시 테이블 처리 방식 차이로 인해 런타임 바이너리 로깅 형식 전환에 대한 새로운 제한 사항이 있다. 세션에 열려 있는 임시 테이블이 있으면 SET @@SESSION.binlog_format 사용할 없다. 복제 채널에 열려 있는 임시 테이블이 있으면 SET @@global.binlog_format SET @@persist.binlog_format 사용할 없다. PERSIST 달리 PERSIST_ONLY 런타임 전역 시스템 변수 값을 수정하지 않기 때문에 복제 채널에 열려 있는 임시 테이블이 있는 경우 SET @@persist_only.binlog_format 허용된다. 복제 채널 적용자가 실행 중인 경우 SET @@global.binlog_format SET @@persist.binlog_format 사용할 없다. 이는 적용자가 다시 시작될 때만 변경 사항이 복제 채널에 적용되며 이때 복제 채널에 열려 있는 임시 테이블이 있을 있기 때문이다. 동작은 이전보다 제한적이며 복제 채널 적용자가 실행 중인 경우 SET @@persist_only.binlog_format 허용된다.

 

MySQL 8.0.27부터 Internal_tmp_mem_storage_engine 대한 세션 설정을 구성하려면 SESSION_VARIABLES_ADMIN 또는 SYSTEM_VARIABLES_ADMIN 권한이 필요하다.

 

MySQL 8.0.27부터 복제 플러그인은 복제 작업이 진행되는 동안 donor 역할을 하는 MySQL 인스턴스에서 동시 DDL 작업을 허용한다. 이전에는 복제 작업 중에 백업 잠금이 유지되어 donor 대한 동시 DDL 방지되었다. 복제 작업 중에 donor에서 동시 DDL 차단하는 이전 동작으로 되돌리려면 clone_block_ddl 변수를 활성화한다. 자세한 내용은 아래 링크를 참고한다.

l   Cloning and Concurrent DDL : https://dev.mysql.com/doc/refman/8.0/en/clone-plugin-concurrent-ddl.html

 

MySQL 8.0.30부터 시작 log_error_services 값에 나열된 오류 로그 구성 요소는 MySQL Server 시작 시퀀스 초기에 암시적으로 로드된다. 이전에 INSTALL COMPONENT 사용하여 로드 가능한 오류 로그 구성 요소를 설치했고 시작 읽는 log_error_services 설정(: 옵션 파일에서) 해당 구성 요소를 나열한 경우 시작 경고가 발생하지 않도록 구성을 업데이트해야 한다. 자세한 내용은 아래 링크를 참고한다.

l   Error Log Configuration Methods : https://dev.mysql.com/doc/refman/8.0/en/error-log-configuration.html#error-log-configuration-methods

 

 

[InnoDB Changes]

InnoDB 시스템 테이블을 기반으로 하는 INFORMATION_SCHEMA 뷰는 Data Dictionary 테이블의 내부 시스템 뷰로 대체되었다. 그리고 영향을 받은 InnoDB INFORMATION_SCHEMA 뷰의 이름이 변경되었다. MySQL 8.0.3 이상으로 업그레이드한 이전 InnoDB INFORMATION_SCHEMA 이름을 참조하는 스크립트가 있다면 새로운 이름으로 업데이트 해야 한다.

Old Name New Name
INNODB_SYS_COLUMNS INNODB_COLUMNS
INNODB_SYS_DATAFILES INNODB_DATAFILES
INNODB_SYS_FIELDS INNODB_FIELDS
INNODB_SYS_FOREIGN INNODB_FOREIGN
INNODB_SYS_FOREIGN_COLS INNODB_FOREIGN_COLS
INNODB_SYS_INDEXES INNODB_INDEXES
INNODB_SYS_TABLES INNODB_TABLES
INNODB_SYS_TABLESPACES INNODB_TABLESPACES
INNODB_SYS_TABLESTATS INNODB_TABLESTATS
INNODB_SYS_VIRTUAL INNODB_VIRTUAL

 

MySQL 함께 번들로 제공되는 zlib 라이브러리 버전이 버전 1.2.3에서 버전 1.2.11 상향되었다. zlib 1.2.11 zlib 압축Bound() 함수는 zlib 버전 1.2.3에서보다 주어진 바이트 길이를 압축하는 필요한 버퍼 크기의 약간 높은 추정치를 반환한다. CompressBound() 함수는 압축된 InnoDB 테이블을 생성하거나 압축된 InnoDB 테이블에 행을 삽입 업데이트할 허용되는 최대 크기를 결정하는 InnoDB 함수에 의해 호출된다. 결과적으로 이전 릴리스에서 성공했던 최대 크기에 매우 가까운 크기를 사용하는 CREATE TABLE ... ROW_FORMAT=COMPRESSED, INSERT UPDATE 작업이 이제 실패할 있다. 문제를 방지하려면 업그레이드하기 전에 MySQL 8.0 테스트 인스턴스에서 행이 있는 압축된 InnoDB 테이블에 대해 CREATE TABLE 문을 테스트해야 한다.

 

--innodb-directories 기능이 도입되면서 절대 경로 또는 데이터 디렉터리 외부 위치로 생성된 테이블당 파일 일반 테이블스페이스 파일의 위치가 innodb_directories 인수 값에 추가되어야 한다. 그렇지 않으면 InnoDB 복구 중에 이러한 파일을 찾을 없어 오류가 발생한다. 테이블스페이스 파일 위치를 보려면 정보 스키마 FILES 테이블을 쿼리한단.

SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G

 

실행 취소 로그는 이상 시스템 테이블스페이스에 상주하지 않는다. MySQL 8.0에서 실행 취소 로그는 기본적으로 개의 실행 취소 테이블스페이스에 있다. 자세한 내용은 아래 링크를 참고한다.

l   Undo Tablespaces : https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

 

MySQL 5.7에서 MySQL 8.0으로 업그레이드하면 MySQL 5.7 인스턴스에 존재하는 모든 실행 취소 테이블스페이스가 제거되고 개의 새로운 기본 실행 취소 테이블스페이스로 대체된다. 기본 실행 취소 테이블스페이스는 innodb_undo_directory 변수에 의해 정의된 위치에 생성된다. innodb_undo_directory 변수가 정의되지 않은 경우 데이터 디렉터리에 실행 취소 테이블스페이스가 생성된다. MySQL 5.7에서 MySQL 8.0으로 업그레이드하려면 MySQL 5.7 인스턴스의 실행 취소 테이블스페이스가 비어 있고 안전하게 제거될 있도록 느린 종료가 필요하다. 이전 MySQL 8.0 릴리스에서 MySQL 8.0.14 이상으로 업그레이드하는 경우 innodb_undo_tablespaces 설정이 2보다 결과로 업그레이드 인스턴스에 존재하는 실행 취소 테이블스페이스는 사용자 정의 실행 취소 테이블스페이스로 처리된다. 업그레이드 각각 ALTER UNDO TABLESPACE DROP UNDO TABLESPACE 구문을 사용하여 삭제되었다. MySQL 8.0 릴리스 시리즈 내에서 업그레이드할 항상 느린 종료가 필요한 것은 아니며, 이는 기존 실행 취소 테이블스페이스에 실행 취소 로그가 포함될 있음을 의미한다. 따라서 기존 실행 취소 테이블스페이스는 업그레이드 프로세스에 의해 제거되지 않는다.

 

MySQL 8.0.17부터 CREATE TABLESPACE ... ADD DATAFILE 절은 순환 디렉터리 참조를 허용하지 않는다. 예를 들어, 다음 명령문의 순환 디렉터리 참조(/../) 허용되지 않는다. 제한사항에 대한 예외는 Linux에서 존재하며, 이전 디렉토리가 심볼릭 링크인 경우 순환 디렉토리 참조가 허용된다. 예를 들어, 예의 데이터 파일 경로는 any_directory 심볼릭 링크인 경우 허용된다. (데이터 파일 경로가 '../' 시작하는 것은 여전히 허용된다.) 업그레이드 문제를 방지하려면 MySQL 8.0.17 이상으로 업그레이드하기 전에 테이블스페이스 데이터 파일 경로에서 순환 디렉터리 참조를 제거하고, 테이블스페이스 경로를 검사하려면 정보 스키마 INNODB_DATAFILES 테이블을 쿼리한다.

 

MySQL 8.0.14 도입된 회귀로 인해 분할된 테이블이 있고 lower_case_table_names=1 설정된 인스턴스의 경우 MySQL 5.7 또는 MySQL 8.0.14 이전의 MySQL 8.0 릴리스에서 MySQL 8.0.16으로의 대소문자 불일치 문제로 인해 시스템 전체 업그레이드가 실패한다.

 

MySQL 테이블 파티션의 테이블스페이스 이름과 파일 이름을 구성할 구분 기호 문자열을 사용한다. 다음과 같이 " #p# " 구분 기호 문자열은 파티션 이름 앞에 오고 " #sp# " 구분 기호 문자열은 하위 파티션 이름 앞에 위치한다. MySQL 업그레이드 작업 아래 상황을 확인하고 수정한다.

l   소문자 구분 기호와 파티션 이름을 보장하기 위해 디스크와 Data Dirctionray 파일 이름을 파티션 한다.

l   이전 버그 수정으로 인해 발생한 관련 문제에 대한 데이터 사전의 파티션 메타데이터이다.

l   이전 버그 수정으로 인해 발생한 관련 문제에 대한 InnoDB 통계 데이터이다.

l   테이블스페이스 가져오기 작업 중에 디스크의 파티션 테이블스페이스 파일 이름을 확인하고 필요한 경우 소문자 구분 기호와 파티션 이름을 확인하기 위해 수정한다.

 

MySQL 8.0.21부터는 시작 또는 MySQL 5.7에서 업그레이드할 테이블스페이스 데이터 파일이 없는 디렉터리에 있는 것으로 발견되면 오류 로그에 경고가 기록된다. 알려진 디렉터리는 datadir, innodb_data_home_dir innodb_directories 변수에 의해 정의된 디렉터리이다. 디렉터리를 알리려면 해당 디렉터리를 innodb_directories 설정에 추가한다. 디렉터리를 알리면 복구 중에 데이터 파일을 찾을 있다. 자세한 내용은 아래 링크를 참고한다.

l   Tablespace Discovery During Crash Recovery : https://dev.mysql.com/doc/refman/8.0/en/innodb-recovery.html#innodb-recovery-tablespace-discovery

 

MySQL 8.0.30부터 innodb_redo_log_capacity 변수는 리두 로그 파일이 차지하는 디스크 공간의 양을 제어한다. 변경으로 인해 리두 로그 파일의 기본 수와 해당 위치도 변경되었다. MySQL 8.0.30부터 InnoDB 데이터 디렉토리의 #innodb_redo 디렉토리에 32개의 리두 로그 파일을 유지한다. 이전에 InnoDB 기본적으로 데이터 디렉터리에 개의 리두 로그 파일을 생성했으며, 리두 로그 파일의 수와 크기는 innodb_log_files_in_group innodb_log_file_size 변수에 의해 제어되었다. 변수는 이제 이상 사용되지 않는다.

 

innodb_redo_log_capacity 설정이 정의되면 innodb_log_files_in_group innodb_log_file_size 설정이 무시된다. 그렇지 않으면 해당 설정은 innodb_redo_log_capacity 설정(innodb_log_files_in_group * innodb_log_file_size = innodb_redo_log_capacity) 계산하는 사용된다. 해당 변수가 설정되지 않은 경우 리두 로그 용량은 innodb_redo_log_capacity 기본값인 104857600바이트(100MB) 설정된다.

 

MySQL 5.7.35 이전에는 중복 또는 압축 형식이 있는 테이블의 인덱스에 대한 크기 제한이 없었다. MySQL 5.7.35부터 제한은 767바이트이며, 5.7.35 이전의 MySQL 버전에서 MySQL 8.0으로 업그레이드하면 액세스할 없는 테이블이 생성될 있다. 중복 또는 압축 형식의 테이블에 767바이트보다 인덱스가 있는 경우 MySQL 8.0으로 업그레이드하기 전에 인덱스를 삭제하고 다시 생성해야 한다. 이러한 부분에 문제가 발생하면 아래와 같은 오류 메시지가 나타난다.

mysql> ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

 

 

[SQL Changes]

MySQL 8.0.13부터 GROUP BY 절에 대해 이상 사용되지 않는 ASC 또는 DESC 한정자가 제거되었다. 이전에 GROUP BY 정렬에 의존했던 쿼리는 이전 MySQL 버전과 다른 결과를 생성할 있다. 지정된 정렬 순서를 생성하려면 ORDER BY 절을 사용해야 한다. GROUP BY 절에 ASC 또는 DESC 한정자를 사용하는 MySQL 8.0.12 이하에서 해당 형식의 쿼리를 사용한다면 수정해야 한다. 그렇지 않으면 MySQL 8.0.13 이상 복제본 서버로의 복제와 마찬가지로 MySQL 8.0.13 이상으로의 업그레이드가 실패할 있다.

 

MySQL 5.7에서는 예약되지 않았던 일부 키워드가 MySQL 8.0에서는 예약어로 사용된다. 이로 인해 이전에 식별자로 사용된 단어가 불법이 있다. 변경된 예약어는 아래 링크를 참고한다.

l   Keywords and Reserved Words : https://dev.mysql.com/doc/refman/8.0/en/keywords.html

 

업그레이드한 후에는 애플리케이션 코드에 지정된 최적화 프로그램 힌트를 테스트하여 원하는 최적화 전략을 달성하는 힌트가 여전히 필요한지 확인하는 것이 좋다. 최적화 기능 향상으로 인해 특정 최적화 기능 힌트가 불필요해질 있다. 어떤 경우에는 불필요한 최적화 힌트가 역효과를 낳을 수도 있다.

 

MySQL 5.7에서 CONSTRAINT 기호 없이 InnoDB 테이블에 대한 FOREIGN KEY 정의를 지정하거나 기호 없이 CONSTRAINT 키워드를 지정하면 InnoDB 생성된 제약 조건 이름을 사용하게 된다. 동작은 생성된 이름 대신 FOREIGN KEY index_name 값을 사용하는 InnoDB 사용하여 MySQL 8.0에서 변경되었다. 제약 조건 이름은 스키마(데이터베이스)별로 고유해야 하기 때문에 변경으로 인해 스키마별로 고유하지 않은 외래 인덱스 이름으로 인해 오류가 발생한다. 이러한 오류를 방지하기 위해 새로운 제약 조건 명명 동작은 MySQL 8.0.16에서 되돌려졌으며 InnoDB 다시 한번 생성된 제약 조건 이름을 사용한다. InnoDB와의 일관성을 위해 MySQL 8.0.16 이상 기반의 NDB 릴리스는 CONSTRAINT 기호 절이 지정되지 않거나 기호 없이 CONSTRAINT 키워드가 지정된 경우 생성된 제약 조건 이름을 사용한다. MySQL 5.7 이전 MySQL 8.0 릴리스를 기반으로 하는 NDB 릴리스는 FOREIGN KEY index_name 값을 사용했다. 이러한 변경 사항으로 인해 이전 외래 제약 조건 명명 동작에 의존하는 응용 프로그램에 대한 비호환성이 발생할 있다.

 

IFNULL() CASE() 같은 MySQL 흐름 제어 함수에 의한 시스템 변수 처리는 MySQL 8.0.22에서 변경되었다. 이제 시스템 변수 값은 상수가 아닌 동일한 문자 데이터 정렬의 값으로 처리된다. 이전에 성공했던 시스템 변수와 함께 이러한 함수를 사용하는 일부 쿼리는 나중에 잘못된 데이터 정렬 혼합으로 인해 거부될 있다. 이러한 경우 시스템 변수를 올바른 문자 집합과 데이터 정렬로 캐스팅해야 한다.

 

MySQL 8.0.28 이전 MySQL 8.0 릴리스에서 CONVERT() 함수가 때때로 BINARY 값을 이진이 아닌 문자 집합으로 잘못 변환하는 것을 허용하는 문제를 수정했다. 동작에 의존할 있는 응용 프로그램을 확인하고 필요한 경우 업그레이드 전에 수정해야 한다. 특히 CONVERT() 인덱스 생성 열에 대한 표현식의 일부로 사용된 경우 함수 동작의 변경으로 인해 MySQL 8.0.28 업그레이드한 인덱스가 손상될 있다. 이러한 문제를 예방하기 위해 아래 목록을 적용해 있다. 사전에 입력 데이터의 유효성을 검사할 없는 경우 MySQL 8.0.28 업그레이드를 수행할 때까지 인덱스를 다시 생성하거나 테이블을 다시 작성해서는 안된다.

l   업그레이드를 수행하기 전에 잘못된 입력 데이터를 수정

l   인덱스를 삭제한 다음 다시 생성

l   ALTER TABLE 테이블 FORCE 사용하여 테이블을 강제로 재구축

l   MySQL 소프트웨어를 업그레이드

 

 

[Changed Server Defaults]

아래표는 변경된 서버의  기본값이 요약되어 있다.

Option/Parameter Old Default New Default
Server changes    
character_set_server latin1 utf8mb4
collation_server latin1_swedish_ci utf8mb4_0900_ai_ci
explicit_defaults_for_timestamp OFF ON
optimizer_trace_max_mem_size 16KB 1MB
validate_password_check_user_name OFF ON
back_log -1 (autosize) changed from : back_log = 50 + (max_connections / 5) -1 (autosize) changed to : back_log = max_connections
max_allowed_packet 4194304 (4MB) 67108864 (64MB)
max_error_count 64 1024
event_scheduler OFF ON
table_open_cache 2000 4000
log_error_verbosity 3 (Notes) 2 (Warning)
local_infile ON (5.7) OFF
InnoDB changes    
innodb_undo_tablespaces 0 2
innodb_undo_log_truncate OFF ON
innodb_flush_method NULL fsync (Unix), unbuffered (Windows)
innodb_autoinc_lock_mode 1 (consecutive) 2 (interleaved)
innodb_flush_neighbors 1 (enable) 0 (disable)
innodb_max_dirty_pages_pct_lwm 0 (%) 10 (%)
innodb_max_dirty_pages_pct 75 (%) 90 (%)
Performance Schema changes    
performance-schema-instrument='wait/lock/metadata/sql/%=ON' OFF ON
performance-schema-instrument='memory/%=COUNTED' OFF COUNTED
performance-schema-consumer-events-transactions-current=ON OFF ON
performance-schema-consumer-events-transactions-history=ON OFF ON
performance-schema-instrument='transaction%=ON' OFF ON
Replication changes    
log_bin OFF ON
server_id 0 1
log-slave-updates OFF ON
expire_logs_days 0 30
master-info-repository FILE TABLE
relay-log-info-repository FILE TABLE
transaction-write-set-extraction OFF XXHASH64
slave_rows_search_algorithms INDEX_SCAN, TABLE_SCAN INDEX_SCAN, HASH_SCAN
slave_pending_jobs_size_max 16M 128M
gtid_executed_compression_period 1000 0
Group Replication changes    
group_replication_autorejoin_tries 0 3
group_replication_exit_state_action ABORT_SERVER READ_ONLY
group_replication_member_expel_timeout 0 5

 

 

[Server Defaults]

Character_set_server 시스템 변수 명령줄 옵션 --character-set-server 기본값이 latin1에서 utf8mb4 변경되었다. MySQL 5.7에서 MySQL 8.0으로 업그레이드해도 기존 데이터베이스 개체의 문자 집합은 변경되지 않는다. 하지만 Character_set_server 명시적으로 설정하지 않는 (이전 또는 값으로) 스키마는 기본적으로 utf8mb4 사용한다. 가능하면 utf8mb4 전환하는 것이 좋다.

 

collation_server 시스템 변수 명령줄 인수 --collation-server 기본값이 latin1_swedish_ci에서 utf8mb4_0900_ai_ci 변경되었다. 이는 서버의 기본 데이터 정렬, 문자 집합의 문자 순서이다. 문자 집합에는 가능한 데이터 정렬 목록이 제공되므로 데이터 정렬과 문자 집합 사이에는 링크가 있다. 5.7에서 8.0으로 업그레이드해도 기존 데이터베이스 개체의 데이터 정렬은 변경되지 않지만 개체에는 적용된다.

 

licit_defaults_for_timestamp 시스템 변수의 기본값이 OFF(MySQL 레거시 동작)에서 ON(SQL 표준 동작)으로 변경되었다. 옵션은 원래 5.6 도입되었으며 5.6 5.7에서는 OFF 되었다가 8.0에서 다시 ON으로 변경되었다.

 

Optimizer_trace_max_mem_size 시스템 변수의 기본값이 16KB에서 1MB 변경되었다. 이전 기본값으로 인해 모든 중요 쿼리에 대해 최적화 프로그램 추적이 잘렸다. 이러한 변경으로 인해 대부분의 쿼리에 유용한 최적화 프로그램 추적이 보장된다.

 

verify_password_check_user_name 시스템 변수의 기본값이 OFF에서 ON으로 변경되었다. , verify_password 플러그인이 활성화되면 기본적으로 현재 세션 사용자 이름과 일치하는 비밀번호를 거부한다.

 

back_log 시스템 변수의 자동 크기 조정 알고리즘이 변경되었다. 자동 크기(-1) 값은 이제 max_connections 값으로 설정됩니다. 이는 50 + (max_connections / 5) 계산된 값보다 크다. back_log 서버가 들어오는 요청을 따라잡을 없는 상황에서 들어오는 IP 연결 요청을 대기열에 추가한다. 최악의 경우, 동시에 재연결을 시도하는 클라이언트 수가 max_connections 경우 모두 버퍼링될 있으며 거부-재시도 루프가 방지된다.

 

max_allowed_packet 시스템 변수의 기본값이 4194304(4M)에서 67108864(64M) 변경되었다. 기본값의 주요 이점은 max_allowed_packet보다 삽입 또는 쿼리에 대한 오류를 수신할 가능성이 적다는 것이다. 이전 동작으로 되돌리려면 max_allowed_packet=4194304 설정한다.

 

max_error_count 시스템 변수의 기본값이 64에서 1024 변경되었다. 이는 MySQL 1000개의 행에 닿는 UPDATE 문과 같은 많은 수의 경고를 처리하고 많은 경고가 변환 경고를 제공하도록 보장한다. 변경 사항은 정적 할당이 없으므로 많은 경고를 생성하는 문의 메모리 소비에만 영향을 미친다.

 

event_scheduler 시스템 변수의 기본값이 OFF에서 ON으로 변경되었다. , 이벤트 스케줄러는 기본적으로 활성화되어 있다. 이는 "유휴 트랜잭션 종료" 같은 SYS 새로운 기능을 활성화하는 것이다.

 

table_open_cache 시스템 변수의 기본값이 2000에서 4000으로 변경되었다. 이는 테이블 액세스 세션 동시성을 높이는 것으로 사소한 변경 사항이다.

 

log_error_verbosity 시스템 변수의 기본값이 3(참고)에서 2(경고) 변경되었다. 목적은 기본적으로 MySQL 8.0 오류 로그를 장황하게 만드는 것이다.

 

 

[InnoDB Defaults]

innodb_undo_tablespaces 시스템 변수의 기본값이 0에서 2 변경되었다. InnoDB에서 사용하는 실행 취소 테이블스페이스 수를 구성한다. MySQL 8.0에서 innodb_undo_tablespaces 최소값은 2이며 이상 시스템 테이블스페이스에 롤백 세그먼트를 생성할 없다. 따라서 이는 5.7 동작으로 다시 되돌릴 없는 경우로, 변경의 목적은 실행 취소 로그(다음 항목 참조) 자동으로 자르고 mysqldump 같은 (가끔) 트랜잭션에 사용되는 디스크 공간을 회수할 있도록 하는 것이다.

 

innodb_undo_log_truncate 시스템 변수의 기본값이 OFF에서 ON으로 변경되었다. 활성화되면 innodb_max_undo_log_size 정의된 임계값을 초과하는 실행 취소 테이블스페이스가 잘림으로 표시된다. 실행 취소 테이블스페이스만 잘라낼 있다. 시스템 테이블스페이스에 있는 실행 취소 로그 자르기는 지원되지 않는다. 5.7에서 8.0으로 업그레이드하면 실행 취소 테이블스페이스를 사용하도록 시스템이 자동으로 변환된다. 시스템 테이블스페이스 사용은 8.0에서 옵션이 아니다.

 

innodb_flush_method 시스템 변수의 기본값은 Unix 계열 시스템에서는 NULL에서 fsync, Windows 시스템에서는 NULL에서 unbuffered 변경되었다. 이는 실질적인 영향을 주지 않는것으로 용어 옵션 정리에 가깝다. Unix 경우 이는 5.7에서도 기본값이 fsync였기 때문에 문서 변경일 뿐이다(기본 NULL fsync 의미함). 마찬가지로 Windows에서 innodb_flush_method 기본값 NULL 5.7에서 async_unbuffered 의미했으며 8.0에서는 기본 unbuffered 대체되었다. 이는 기존 기본 innodb_use_native_aio=ON 결합하여 동일한 효과를 갖는다.

 

호환되지 않는 변경 innodb_autoinc_lock_mode 시스템 변수의 기본값이 1(연속)에서 2(인터리브) 변경되었다. 기본 설정이 인터리브 잠금 모드로 변경된 것은 MySQL 5.7에서 발생한 기본 복제 유형이 명령문 기반에서 기반 복제로 변경된 것을 반영한다. 명령문 기반 복제에는 주어진 SQL 시퀀스에 대해 자동 증가 값이 예측 가능하고 반복 가능한 순서로 할당되도록 연속 자동 증가 잠금 모드가 필요한 반면, 기반 복제는 SQL 문의 실행 순서에 민감하지 않는다. 따라서 변경 사항은 명령문 기반 복제와 호환되지 않는 것으로 알려져 있으며 순차 자동 증분에 의존하는 일부 응용 프로그램이나 사용자 생성 테스트 모음을 손상시킬 있다. innodb_autoinc_lock_mode=1 설정하여 이전 기본값을 복원할 있다.

 

innodb_flush_neighbors 시스템 변수의 기본값은 1(활성화)에서 0(비활성화)으로 변경된다. 이는 빠른 IO(SSD) 이제 배포의 기본값이기 때문에 수행된다. 대부분의 사용자에게는 이로 인해 성능이 약간 향상될 것으로 예상된다. 느린 하드 드라이브를 사용하는 사용자는 성능 손실을 경험할 있으므로 innodb_flush_neighbors=1 설정하여 이전 기본값으로 되돌리는 것이 좋다.

 

innodb_max_dirty_pages_pct_lwm 시스템 변수의 기본값이 0(%)에서 10(%)으로 변경되었다. innodb_max_dirty_pages_pct_lwm=10 사용하면 InnoDB 버퍼 풀의 10% 이상이 수정된('Dirty') 페이지를 포함할 플러시 활동을 증가시킨다. 변경의 목적은 보다 일관된 성능을 제공하는 대신 최대 처리량을 약간 낮추는 것이다.

 

innodb_max_dirty_pages_pct 시스템 변수의 기본값이 75(%)에서 90(%)으로 변경되었다. 변경 사항은 innodb_max_dirty_pages_pct_lwm 대한 변경 사항과 결합되어 원활한 InnoDB 플러시 동작을 보장하고 플러시 버스트를 방지한다. 이전 동작으로 되돌리려면 innodb_max_dirty_pages_pct=75 innodb_max_dirty_pages_pct_lwm=0으로 설정한다.

 

 

[Performance Schema Defaults]

성능 스키마 MDL(메타 데이터 잠금) 계측은 기본적으로 켜져 있다. Performance-schema-instrument='wait/lock/metadata/sql/%=ON' 대한 컴파일된 기본값이 OFF에서 ON으로 변경되었다. 이는 SYS MDL 지향 뷰를 추가하기 위한 활성화 도구이다.

 

성능 스키마 메모리 계측은 기본적으로 켜져 있다. Performance-schema-instrument='memory/%=COUNTED' 대한 컴파일된 기본값이 OFF에서 COUNTED 변경되었다. 서버 시작 계측이 활성화된 경우 집계 결과가 올바르지 않고 할당이 누락되었다가 여유 공간이 확보되면 마이너스 결과 값이 발생할 있으므로 이는 중요하다.

 

성능 스키마 트랜잭션 계측은 기본적으로 켜져 있습니다. Performance-schema-consumer-events-transactions-current=ON, Performance-schema-consumer-events-transactions-history=ON Performance-schema-instrument='transaction%=ON' 대한 컴파일된 기본값이 OFF에서 ON으로 변경되었다.

 

 

[Replication Defaults]

log_bin 시스템 변수의 기본값이 OFF에서 ON으로 변경되었다. , 바이너리 로깅이 기본적으로 활성화되어 있다. 거의 모든 프로덕션 설치에는 복제 특정 시점 복구에 사용되는 바이너리 로그가 활성화되어 있다. 따라서 기본적으로 바이너리 로그를 활성화하면 하나의 구성 단계가 제거되고 나중에 활성화하려면 mysqld 다시 시작해야 한다. 기본적으로 이를 활성화하면 나은 테스트 적용 범위가 제공되고 성능 회귀를 쉽게 발견할 있다. server_id 설정해야 한다. 8.0 기본 동작은 ./mysqld --log-bin --server-id=1 실행한 것과 같다. 8.0 사용하고 있고 5.7 동작을 원하는 경우 ./mysqld --skip-log-bin --server-id=0 실행할 있다.

 

server_id 시스템 변수의 기본값이 0에서 1 변경되었다(log_bin=ON으로의 변경과 결합). 기본 ID 서버를 시작할 있지만 실제로 중복된 서버 ID 방지하려면 배포 중인 복제 인프라에 따라 서버 ID 설정해야 한다.

 

log-slave-updates 시스템 변수의 기본값이 OFF에서 ON으로 변경되었다. 이로 인해 복제본은 복제된 이벤트를 자체 바이너리 로그에 기록한다. 옵션은 그룹 복제에 필요하며 오늘날 표준이 다양한 복제 체인 설정에서 올바른 동작을 보장한다.

 

expire_logs_days 시스템 변수의 기본값이 0에서 30으로 변경되었다. 새로운 기본값 30 mysqld 30일보다 오래된 사용되지 않은 바이너리 로그를 주기적으로 제거하도록 한다. 변경은 복제 또는 복구 목적에 이상 필요하지 않은 바이너리 로그에 과도한 양의 디스크 공간이 낭비되는 것을 방지하는 도움이 된다. 이전 0 자동 바이너리 로그 제거를 비활성화한다.

 

master_info_repository Relay_log_info_repository 시스템 변수의 기본값이 FILE에서 TABLE 변경된다. 따라서 8.0에서는 복제 메타데이터가 기본적으로 InnoDB 저장된다. 이렇게 하면 기본적으로 충돌 방지 복제를 시도하고 달성하기 위한 안정성이 향상된다.

 

transaction-write-set-extraction 시스템 변수의 기본값이 OFF에서 XXHASH64 변경되었다. 변경으로 인해 기본적으로 트랜잭션 쓰기 세트가 활성화된다. 트랜잭션 쓰기 세트를 사용하면 소스가 쓰기 세트를 생성하기 위해 약간 많은 작업을 수행해야 하지만 결과는 충돌 감지에 도움이 된다. 이는 그룹 복제에 대한 요구 사항이며 새로운 기본값을 사용하면 소스에서 바이너리 로그 쓰기 집합 병렬화를 쉽게 활성화하여 복제 속도를 높일 있다.

 

slave_rows_search_algorithms 시스템 변수의 기본값이 INDEX_SCAN, TABLE_SCAN에서 INDEX_SCAN, HASH_SCAN으로 변경되었다. 변경 사항은 기본 없이 테이블에 변경 사항을 적용하기 위해 복제본 적용자가 수행해야 하는 테이블 검색 횟수를 줄여 기반 복제 속도를 높인다.

 

slave_pending_jobs_size_max 시스템 변수의 기본값이 16M에서 128M으로 변경되었다. 변경으로 인해 다중 스레드 복제본에 사용할 있는 메모리 양이 늘어난다.

 

gtid_executed_compression_기간 시스템 변수의 기본값이 1000에서 0으로 변경되었다. 변경으로 인해 mysql.gtid_executed 테이블의 압축은 필요할 때만 암시적으로 발생한다.

 

 

[Group Replication Defaults]

group_replication_autorejoin_tries 기본값이 0에서 3으로 변경되었다. , 자동 재참여가 기본적으로 활성화되어 있음을 의미한다. 시스템 변수는 그룹이 추방되거나 group_replication_unreachable_majority_timeout 설정에 도달하기 전에 그룹의 대다수에 연결할 없는 경우 구성원이 그룹에 자동으로 다시 참여하기 위해 시도하는 횟수를 지정한다.

 

group_replication_exit_state_action 기본값이 ABORT_SERVER에서 READ_ONLY 변경되었다. 예를 들어 네트워크 오류가 발생한 구성원이 그룹을 종료하면 인스턴스가 종료되지 않고 읽기 전용이 된다.

 

group_replication_member_expel_timeout 기본값이 0에서 5 변경되었다. 그룹과의 연락이 두절된 것으로 의심되는 구성원은 5 감지 기간 이후 5 후에 제명 책임을 지게 된다.

 

innodb_dedicated_server라는 옵션의 경우 개발 서버나 개인 랩탑의 경우 ON OFF 설정한다. 이유는 사용할 있는 모든 메모리를 사용하기 때문에 랩탑 같은 일반적인 공유 환경에서 메모리 부족 문제가 발생할 있기 때문이다. 하지만 프로덕션 환경의 경우 innodb_dedicated_server ON으로 설정하는 것이 좋다. ON으로 설정하면 다음 InnoDB 변수(명시적으로 지정하지 않은 경우) 사용 가능한 메모리 innodb_buffer_pool_size, innodb_log_file_size innodb_flush_method 기반으로 자동 크기 조정된다. 작세한 내용은 아래 링크를 참고한다.

l   Enabling Automatic Configuration for a Dedicated MySQL Server : https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

 

 

 

Conclusion

대부분의 기본값은 개발 프로덕션 환경 모두에 적합하지만, 항상 예외는 발생할 있기 때문에 각자의 환경에 맞게 기본값을 적절히 수정해서 사용할 있도록 한다. MySQL 8.0에는 시스템 변수에 대해 가장 최근에 설정된 소스와 해당 범위를 보여주는 성능 스키마 Variable_info 테이블이 있다. 이는 구성 변수와 값에 대해 알아야 모든 것에 대한 SQL 액세스를 제공한다.

 

 

 

[참고자료]

l   Changes in MySQL 8.0 :  https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html

 

 

 

 

 

2023-09-13 / Sungwook Kang / http://sungwookkang.com

 

MySQL, 업그레이드 고려사항, MySQL8 변경사항

[MySQL] 성능 모니터링을 위한 Performance_Schema 개념

 

l  Version : MySQL 5.X, 8.X

 

MySQL에서 다양한 성능 관리 지표를 얻기 위해 사용하는 정보는 performance_schema (이하 성능 스키마”) information_schema이다. 이번 포스트에서는 performance_schema 관한 전체적인 개념을 다루며 항목에 대한 자세한 세부 내용은 별도 포스팅 한다.

 

 MySQL performance_schema 로우레벨에 실행되며, 런타임으로 서버 내부를 검사한다. 사용자가 자주 사용하는 것은 디폴트로 활성화되어 있기 때문에 따로 설정하지 않아도 정보를 있지만, 외에는 필요에 따라 설정을 활성화해야 한다. performance_schema 정보를 수집하는 instruments 수집한 정보를 저장하는 consumers 설정하여 이용하는 구조이다.

l  Performance_schema : 성능 데이터 검사에 사용.

l  Information_schema : 메타데이터 검사에 사용

 

  performance_schema information_schema
주요 목적 성능 데이터의 수집 메타 데이터의 수집
애플리케이션 성능 개선 감시 도구이자 관리 도구
도입된 버전 5.5 5
SQL 표준 MySQL 전용 기능 O
설치방법 스토리지 엔진으로 설치 infomation schema API
데이터 수집 방법 mysqld 내부에서 런타임 수집 information schema 테이블 접근 시
통상적인 오버 헤드 있음 없음
출력에 의한 오버 헤드 적음 많음

 

Information_schema 경우 DROP DATABASE 명령으로 삭제할 없다. 이유는 SYSTEM VIEW 이기 때문이다. 따라서 일반 사용자에게 all privileges on *.* 권한을 주더라도 삭제할 없다. 스키마의 table_type 아래와 같다.

l   Information_schema : SYSTEM VIEW

l   mysql : BASE TABLE

l   performance_schema : BASE TABLE

l   sys : VIEW or BASE TABLE

 

performance_schema 이벤트는 서버의 바이너리 로그(데이터 수정을 설명) 기록된 이벤트 이벤트 스케줄러 이벤트(저장 프로그램 유형) 다르다. 성능 스키마 이벤트는 MySQL 서버의 특정 인스턴스에만 적용된다. 성능 스키마 테이블은 서버에 로컬로 간주되며 해당 테이블에 대한 변경 사항은 바이너리 로그에 복제되거나 기록되지 않는다.

 

[Performance_Schema 스토리지 엔진]

performance_schema 스토리지 엔진은 서버 소스 코드의 "계측 지점(Instrumentation Point)" 사용하여 이벤트 데이터를 수집한다. 수집된 이벤트는 Performance_schema 데이터베이스의 테이블에 저장된다. 이러한 테이블은 다른 테이블과 마찬가지로 SELECT 문을 사용하여 쿼리할 있다. 그리고 성능 스키마의 테이블은 지속적인 온디스크 스토리지를 사용하지 않는 인메모리 테이블이기 때문에, 수집된 내용은 서버 시작 다시 채워지고 서버 종료 삭제된다.

 

아래 쿼리를 사용하여 performance_schema 어떤 스토리지 엔진을 사용하는지 확인할 있다.

SELECT * FROM INFORMATION_SCHEMA.ENGINES WHERE ENGINE = 'PERFORMANCE_SCHEMA';

 

 

결과를 살펴보면 전용 PERFORMANCE_SCHEMA 스토리지 엔진을 사용하는 것을 확인할 있으며, Transactions 항목을 보면 NO 되어 있으므로 SQL 사용할 수는 있지만 InnoDB 와는 다르게 Transaction 지원하지 않는다는 것을 있다.

 

성능 스키마 구성은 SQL 문을 통해performance_schema 데이터베이스의 테이블을 업데이트하여 동적으로 수정할 있다. 구성 변경은 데이터 수집에 즉시 영향을 미친다. 데이터 수집은 계측을 추가하기 위해 서버 소스 코드를 수정하는 방식으로 구현된다. 복제나 이벤트 스케줄러와 같은 다른 기능과 달리 성능 스키마와 관련된 별도의 스레드가 없다

 

 

[Performance_Schema 동작 방식]

Performance_schema 동작은 가지로 나눌 있다.

l   Instrument(인스트루먼트) : 정보를 얻고자 하는 MySQL 코드를 나타냄

l   Consumer(컨슈머) : 어떤 코드를 수행하였는지에 대한 정보를 저장

 

Instrument(인스트루먼트)

performance_schema setup_instruments 테이블에는 지원되는 모든 인스트루먼트 목록이 포함되어 있으며, 모든 인스트루먼트 명은 슬래시로 구분해서 구성되어 있다. 가지 예시를 살펴보자.

l   statement/sql/select

l   wait/synch/mutex/innodb/autoinc_mutex

인스트루먼트 이름의 가장 왼쪽이 인스트루먼트의 종류를 의미한다. 따라서 위의 예시에서 statement 인스트루먼트가 sql 문장임을 나태내고 있으며, wait/ 대기 관련된 내용을 나타내고 있다. 인스트루먼트에서 수집할 있는 성능 정보 항목은 performance_schema.setup_instruments 에서 확인 있다. 그룹별로 개의 인스투르먼트가 있는지 확인한다.

select substring_index(name, '/', 1) as category, count(*) from setup_instruments group by substring_index(name, '/', 1);

 

 

인스트루먼트의 상세 목록은 아래 쿼리를 사용한다. MySQL 5.7 버전에서는 1032개의 인스트루먼트가 있다.

select * from performance_schema.setup_instruments;

 

 

 

Consumer(컨슈머)

컨슈머는 인스트루먼트가 정보를 보내는 대상을 의미한다. Performance_schema 인스트루먼트 결과를 다양한 테이블에 저장하게 된다. performance_schema 종류는 아래 쿼리로 확인할 있으며, 테이블의 내용은 SELECT 구문으로 조회할 있다. 이번 포스트에서는 테이블의 기능 역할을 설명하지는 않는다. MySQL 5.7 기준으로 89개의 테이블이 있다. MySQL 8.0 경우에는 110 테이블이 존재한다. 버전별로 특성이 있기 때문에 구분해서 사용할 있도록 한다.

use performance_schema;
show tables;

 

 

 

[Performance_Schema 활성화]

인스트루먼트를 활성화하거나 비활성화 하는 방법은 performance_schema에서 수집하는 성능 지표 항목에 따라 활성 비활성을 있다. 아래 쿼리로 목록을 조회하고, 결과를 살펴보면 ENABLED 컬럼의 결과로 활성화 여부를 확인할 있다.

select * from performance_schema.setup_instruments;

 

 

 setup_instruments 테이블에서는 ENABLED TIMED 칼럼을 업데이트하여 기능을 활성화할 있습니다. UPDATE 문으로 이들 값을 'YES' 변경하면 활성화되고, 'NO' 변경하면 비활성화된다. setup_instruments TIMED 칼럼값을 'YES' 변경하여 활성화하면 instruments 타이밍 정보를 수집할 있다. memory 카테고리의 instruments 계측하는 구조가 없으므로 TIMED 칼럼을 'YES' UPDATE해도 쿼리는 성공하지만 카테고리 레코드 값은 'NO' 남아 있어 활성화할 없다.

update performance_schema.setup_instruments set ENABLED='YES', TIMED='YES' where name='statement/sql/select';

 

인스트루먼트의 활성/비활성화를 쿼리로 업데이트한 경우, 영구적이지 않기 때문에 MySQL 서버 재시작시 다시 적용해주어야 한다. 그렇기 때문에 영구 적용을 위해서는 my.cnf 시스템 변수로 설정한다.

mysql.cnf

performance-schema-instrument='wait/synch/mutex/sql/LOCK_status=ON'
performance-schema-instrument='wait/synch/mutex/innodb/autoinc_mutex=ON'

 

컨슈머를 활성화하는 방법도 인스트루먼트와 유사하게 performance_schema.setup_consumers 테이블을 update하거나 my.cnf 시스템 변수에 등록한다.

 

 

 

[참고자료]

l   Chapter 1 MySQL Performance Schema : https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/performance-schema.html

l   MySQL Performance Schema - 성능 스키마 : https://hoing.io/archives/3811

l   MySQL performance-schema-instruments 사용에 따른 성능 영향 실험 : https://engineering.linecorp.com/ko/blog/mysql-research-performance-schema-instruments#:~:text=performance_schema%EB%8A%94%20%EC%A0%95%EB%B3%B4%EB%A5%BC%20%EC%88%98%EC%A7%91,%EC%84%A4%EC%A0%95%ED%95%98%EC%97%AC%20%EC%9D%B4%EC%9A%A9%ED%95%98%EB%8A%94%20%EA%B5%AC%EC%A1%B0%EC%9E%85%EB%8B%88%EB%8B%A4.

 

 

 

 

2023-09-02 / Sungwook Kang / http://sungwookkang.com

 

MySQL, 성능 모니터, performance_schema, 성능스키마, MySQL모니터링, MySQL 시스템 정보 수집

[MySQL] “innodb_table_stats” not found 오류와 예상하지 못한 사이드 이펙트

 

l  Version : MySQL 5.7

 

MySQL 5.7에서 서비스 재시작 아래와 같은 오류가 로그에 잔뜩 쌓여있다. MySQL 에러 로그 위치는 일반적으로 아래와 같다.

sudo cat /var/log/mysql/error.log

 

2023-08-31T05:15:56.437819Z 2598 [ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
2023-08-31T05:15:56.437825Z 2598 [ERROR] InnoDB: Fetch of persistent statistics requested for table `XXXX`.`wp_46549_XXXXX ` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

 

서비스를 재시작 했을 뿐인데 이와 같은 로그가 발생한 것일까? 이유를 살펴보니 MySQL 5.6부터는 mysql database 통계정보나 slave 관련 정보를 저장하기 위해 새롭게 InnoDB 테이블이 추가되었다.

l  innodb_index_stats

l  innodb_table_stats

l  slave_master_info

l  slave_relay_log_info

l  slave_worker_info

MySQL 5.5 데이터를 MySQL 5.6이나 MySQL 5.7 업그레이드 했을때, mysql_upgrade 작업을 추가로 해주지 않으면 mysql database 구조는 5.5 되돌아간다. 그렇기 때문에 반드시 mysql_upgrade 진행해 주어야 한다.

 

발생하는 오류를 해결하기 위해서는 아래 방법을 실행한다.

1.       관련 테이블 삭제

USE mysql;
DROP TABLE innodb_index_stats;
DROP TABLE innodb_table_stats;
DROP TABLE slave_master_info;
DROP TABLE slave_relay_log_info;
DROP TABLE slave_worker_info;

 

2.       MySQL 서비스 중지 mysql database 존재하는 ibd 파일 삭제 (frm 파일은 DROP TABLE 삭제되었음)

cd /var/lib/mysql/mysql/
 
rm -rf innodb_index_stats.ibd
rm -rf innodb_table_stats.ibd
rm -rf slave_master_info.ibd
rm -rf slave_relay_log_info.ibd
rm -rf slave_worker_info.ibd

 

3.       MySQL 서비스 시작

4.       mysql 강제 업그레이드를 진행한다. 작업을 통해서 존재하지 않는 5 테이블이 새로 생성된다.

mysql_upgrade –force -uroot –p

 

MySQL 업그레이드가 진행되면 아래와 같은 메시지들이 출력되며 업그레이드를 진행한다. 전체 스키마가 진행되는 모든 로그가 출력되기 때문에 테이블이 많은 데이터베이스라면 시간이 오래 걸린다. (나의 경우 테이블 갯수가 십만개 존재하는 데이터베이스였다.)

root@XXXXX:/var/lib/mysql/mysql# mysql_upgrade --force -uroot -p
Enter password:
Checking server version.
Running queries to upgrade MySQL server.
mysql_upgrade: [ERROR] 1146: Table 'mysql.plugin' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.servers' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_topic' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_category' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_relation' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_keyword' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_name' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_transition' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_transition_type' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_leap_second' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.gtid_executed' doesn't exist
mysql_upgrade: [ERROR] 1243: Unknown prepared statement handler (stmt) given to EXECUTE
mysql_upgrade: [ERROR] 1243: Unknown prepared statement handler (stmt) given to DEALLOCATE PREPARE
mysql_upgrade: [ERROR] 1146: Table 'mysql.server_cost' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.server_cost' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.engine_cost' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.engine_cost' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.plugin' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.servers' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.gtid_executed' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_category' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_topic' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_topic' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_category' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_relation' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.help_keyword' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.plugin' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.servers' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_leap_second' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_name' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_transition' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.time_zone_transition_type' doesn't exist
mysql_upgrade: [ERROR] 1146: Table 'mysql.servers' doesn't exist
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost
Error    : Table 'mysql.engine_cost' doesn't exist
status   : Operation failed
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed
Error    : Table 'mysql.gtid_executed' doesn't exist
status   : Operation failed
mysql.help_category
Error    : Table 'mysql.help_category' doesn't exist
status   : Operation failed
mysql.help_keyword
Error    : Table 'mysql.help_keyword' doesn't exist
status   : Operation failed
mysql.help_relation
Error    : Table 'mysql.help_relation' doesn't exist
status   : Operation failed
mysql.help_topic
Error    : Table 'mysql.help_topic' doesn't exist
status   : Operation failed
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin
Error    : Table 'mysql.plugin' doesn't exist
status   : Operation failed
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost
Error    : Table 'mysql.server_cost' doesn't exist
status   : Operation failed
mysql.servers
Error    : Table 'mysql.servers' doesn't exist
status   : Operation failed
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone
Error    : Table 'mysql.time_zone' doesn't exist
status   : Operation failed
mysql.time_zone_leap_second
Error    : Table 'mysql.time_zone_leap_second' doesn't exist
status   : Operation failed
mysql.time_zone_name
Error    : Table 'mysql.time_zone_name' doesn't exist
status   : Operation failed
mysql.time_zone_transition
Error    : Table 'mysql.time_zone_transition' doesn't exist
status   : Operation failed
mysql.time_zone_transition_type
Error    : Table 'mysql.time_zone_transition_type' doesn't exist
status   : Operation failed
mysql.user                                         OK
 
Repairing tables
mysql.engine_cost
Error    : Table 'mysql.engine_cost' doesn't exist
status   : Operation failed
mysql.gtid_executed
Error    : Table 'mysql.gtid_executed' doesn't exist
status   : Operation failed
mysql.help_category
Error    : Table 'mysql.help_category' doesn't exist
status   : Operation failed
mysql.help_keyword
Error    : Table 'mysql.help_keyword' doesn't exist
status   : Operation failed
mysql.help_relation
Error    : Table 'mysql.help_relation' doesn't exist
status   : Operation failed
mysql.help_topic
Error    : Table 'mysql.help_topic' doesn't exist
status   : Operation failed
mysql.plugin
Error    : Table 'mysql.plugin' doesn't exist
status   : Operation failed
mysql.server_cost
Error    : Table 'mysql.server_cost' doesn't exist
status   : Operation failed
mysql.servers
Error    : Table 'mysql.servers' doesn't exist
status   : Operation failed
mysql.time_zone
Error    : Table 'mysql.time_zone' doesn't exist
status   : Operation failed
mysql.time_zone_leap_second
Error    : Table 'mysql.time_zone_leap_second' doesn't exist
status   : Operation failed
mysql.time_zone_name
Error    : Table 'mysql.time_zone_name' doesn't exist
status   : Operation failed
mysql.time_zone_transition
Error    : Table 'mysql.time_zone_transition' doesn't exist
status   : Operation failed
mysql.time_zone_transition_type
Error    : Table 'mysql.time_zone_transition_type' doesn't exist
status   : Operation failed
The sys schema is already up to date (version 1.5.2).
Checking databases.

 

5.       MySQL 서비스를 재시작 오류 로그를 확인하여 정상적으로 동작하는지 확인한다.

 

 

[Side Effect]

CASE 1.

mysql_upgrade 작업을 완료 MySQL 서비스를 재시작 하였더니 이번엔 새로운 로그가 기록되고 있었다. 다행이 오류 수준이 [ERROR] 아닌 [WARNING]이었지만 뭔가 이상하다. 발생한 로그는 아래와 같다.

2023-08-31T06:17:51.827559Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX_indexable from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2023-08-31T06:17:51.827751Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX _indexable_hierarchy from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2023-08-31T06:17:51.828046Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX _yoast_migrations from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2023-08-31T06:17:51.828290Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX _primary_term from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2023-08-31T06:17:51.829080Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.
2023-08-31T06:17:51.829516Z 25 [Warning] InnoDB: Cannot open table XXXX/XXXX_XXXX from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

 

로그는 데이터베이스 복구 과정에서 발생한 것이다. 문제는 위와 같은 내용을 에러로그에 기록하면서 CPU 메모리를 엄청 많이 소비했다는 것이다. 현재 개발 환경이므로 CPU 2Core, Memory 32GB 환경에 MySQL buffer cache 3GB 세팅 되어 있다. 그런데 모니터링 그래프를 보면 CPU 100% 사용에 물리 메모리를 모두 소진한 것을 확인할 있다.

 

 

그런데 실제 버퍼캐시 메모리 사용은 거의 없었다. 예상하기는 mysql 시스템 작업을 위해 별도 스레드에서 사용한거 같은데, 중요한건 작업이 완료된 후에도 메모리가 반환되지 않는 상태로 mysqld 프로세스가 계속 사용중으로 유지되고 있었다.

 

작업이 완료되고, CPU 사용량이 정상화된 , MySQL 서비스 재시작으로 메모리를 회수할 있었으며, 이후 정상적인 메모리 상태를 유지하고 있다.

 

CASE 2.

당시 상황을 스크린샷으로 남기지 못했지만, 상황에서 평소 1GB 미만의 메모리 사용량을 사용하는 pmm-agent에서 10GB 이상의 메모리 사용량을 보이면서 이상현상을 보이고 있었다. pmm-agent 서비스를 재시작 해보았지만 동일 증상이 계속되었다. 그래서 pmm client 완전히 제거 재설치 하여 해당 문제를 해결하였다. 모니터링 솔루션이기 때문에 해당 문제의 원인을 찾아야 안정적인 라이브 서비스를 있을 듯한데, 원인을 아직 못찾았다.  예상으로는 perfschema 모니터링 하면서 변경된 사항을 추적하느라 발생하는 것으로 예상만 하고 있다. (다음에 동일한 상황이 재현되면 상세히 확인해 예정)

 

개발 서버에서 발생한 문제이다 보니 다양한 상태를 모니터링 하기 위한 설정이 미흡하여 많은 정보를 수집하지 못해 근본적인 원인을 찾지 못하는 아쉬움이 남는 트러블슈팅이었다. 모니터링은 과하다 싶을 정도로 많이 해서 문제가 발생했을 빠르고 정확하게 해결할 있는 정보로 활용할 있도록 하는 것이 매우 중요하다.

 

2023-09-01 / Sungwook Kang / http://sungwookkang.com

 

MySQL, innodb_index_stats, MySQL업그레이드, MySQL 트러블슈팅, MySQL Error

MySQL PMM(Percona Monitoring and Management) 소개 설치

 

l  Version : MySQL, MariaDB, PMM

 

PMM Percona Monitoring and Management 약자로 PostgreSQL MySQL, MongoDB 위한 오픈소스 데이터베이스 모니터링 관리 솔루션이다. 이를 통해 데이터베이스 시스템의 상태를 관찰하고, 동작의 새로운 패턴을 탐색하고, 문제를 해결하고, 온프레미스나 클라우드 어디에 있든 데이터베이스 관리 작업을 수행할 있다. PMM 데이터베이스와 해당 호스트로부터 수천 개의 기본 성능 지표를 수집한다. 그리고 UI 대시보드를 제공하여 데이터를 시각화 한다. 추가 기능에는 데이터베이스 상태 평가를 위한 조언을 제공하기도 한다.

 

 

 

PMM 서버, 클라이언트 구조로 노드들에 PMM Client 설치하여 중앙의 PMM 서버로 데이터를 전송하는 방식이다. 화면을 보면 그라파나(Grafana) 대시보드를 사용하는 것을 있다. 그리고 데이터를 수집하는 에이전트는 프로메테우스(Prometheus) 사용한다.

 

PMM Client

l  pmm-admin : 각종 exporter들을 구동 제어한다. pmm-admin 노드 관리를 효과적으로 하기 위해, PMM서버에 포함된 Consul 서버에 exporter 에이전트 접속 정보(아이피와 포트) 등록한다. Prometheus 수집주기마다, Consul 등록된 에이전트 리스트를 가져와서 모니터링 메트릭을 수집한다.

l  node_expoter, mysql_expoter : 모니터링 대상 노드(서버) 서버에 데몬 형태로 구동되는 에이전트로, Prometheus에서 에이전트 포트로 메트릭 요청을 하면 현재 서버의 상태를 전송하는 역할을 한다.

 

PMM Server

l   QNAP : DB서버를 모니터링 , 시스템의 메트릭 뿐만 아닌, 슬로우 쿼리나, 각종 이벤트 정보들을 pmm-server 데이터를 전달해야 하는데, Prometeus 이러한 데이터를 받아들이지 않는다. 이러한 데이터를 받기 위해 QAN API 사용한다. expoter에서 pmm-server 80포트를 사용하여 /qan-api 데이터를 전송하면, 내부의 nginx 서비스가 전달한 데이터를 받아서, 127.0.0.1:9001 리다이렉션하여 쿼리를 저장한다.

l   Prometheus : 타임시리즈 기반의 저장소로, 데이터 수집을 비롯해서 저장 그리고 쿼리 질의, Alert 에이전트의 포트에 접근해서, 모니터링 시스템의 메트릭을 Pull 방식으로 끌어오는 역할을 수행한다.

l   Consul : pmm-admin pmm consul 모니터링이 대상들의 아이피와 포트 번호를 넣어주면, Prometheus 정보를 바탕으로 등록된 scrape_interval마다 DB서버 에이전트에 접근하여 모니터링 지표를 수집한다.

l   Grafana : 수집된 데이터를 Web UI 제공한다.

 

 

PMM설치의 경우, 서버는 컨테이너로 제공되며, 클라이언트는 컨테이너 또는 전용 패키지 설치 파일로 제공된다. 이번 포스트에서는 Docker에서 PMM 서버를 실행하고, Client 패키지로 설치한 다음, 모니터링 서버를 등록하는 과정을 살펴본다.

 

[PMM 서버 설치]

도커 이지지를 다운로드 한다.

docker pull percona/pmm-server:2

 

데이터를 저장할 호스트 볼륨을 생성한다.

docker volume create pmm-data

 

이미지를 실행하여 PMM 서비스를 실행한다. 이때, 관리를 위한 443 포트와 UI 제공하기 위한 80포트를 사용하는데, 포트가 충돌나지 않도록 적절히 포트를 포워딩하여사용한다. ( 포스트에서는 도커 사용법을 다루지는 않는다.)

docker run --detach --restart always \
--publish 443:443 --publish 80:80 \
-v pmm-data:/srv \
--name pmm-server \
percona/pmm-server:2

 

서버가 정상적으로 실행되었으면, 웹브라우저에서 PMM 서버가 설치된 IP 입력하여 UI 대시보드에 접속한다. 필자의 경우 컨테이너를 생성할 , 8080포트를 사용하도록 매핑하였다. 기본 ID 패스워드는 admin / admin 이다. 정상적으로 접속이 되면 우선 서버 설치는 정상적으로 완료되었다고 판단하면 된다.

http://172.30.1.49:8080

 

 

 

 

[PMM Client 설치]

모니터링할 대상 서버에는 PMM Client 설치한다. 운영체제에 따라 전용 패키지 또는 설치 파일을 다운로드하여 설치할 있다. 실습 환경은 우분투이며 편의상 패키지로 설치하였다. 환경에 따른 설치 방법은 아래 링크를 참고한다.

l   Set up PMM Client  : https://docs.percona.com/percona-monitoring-and-management/setting-up/client/index.html

 

apt update
apt-get install -y pmm2-client

 

 

Client 설치가 설치가 완료되었으면 PMM 서버에 수집할 클라이언트 목록을 등록해야 한다. 아래 명령어로 등록할 있다.

pmm-admin config --server-insecure-tls --server-url=https://admin:admin@X.X.X.X:443

l   X.X.X.X : PMM 서버 IP 입력한다.

l   443 : 기본 포트 번호. 컨테이너 실행시 포트 번호를 다르게 매핑하였다면 수정해야한다.

l   Admin/admin : PMM 서버의 기본 접속 정보이다. 만약 이전 UI 통해서 수정하였다면 부분도 수정한다.

 

여기까지 진행되었으면 PMM 대시보드에서 모니터링의 대상 서버가 추가된 것을 확인할 있다. 대시보드에서는 Node Names에서 확인할 있다. 정보가 업데이트 되기까지 약간의 시간이 걸릴수도 있다.

 

이번 실습에서는 MySQL 모니터링을 하기 때문에 MySQL 서비스에 대한 모니터링을 있도록 MySQL 등록한다. 사용자 환경에 따라 필요한 서비스를 등록한다.  지원되는 서비스 목록은 아래와 같다.

l   MySQL (and variants Percona Server for MySQL, Percona XtraDB Cluster, MariaDB)

l   MongoDB

l   PostgreSQL

l   ProxySQL

l   Amazon RDS

l   Microsoft Azure

l   Google Cloud Platform (MySQL and PostgreSQL)

l   Linux

l   External services

l   HAProxy

l   Remote instances

 

MySQL 서비스에 대한 정보를 수집할 있도록 PMM Client 사용할 계정 다양한 시스템 환경을 설정한다. 설정 방법은 공식 문서를 참고할 있도록 한다.

l   https://docs.percona.com/percona-monitoring-and-management/setting-up/client/mysql.html

 

이번 포스트에서는 슬로우 쿼리 로그를 모니터링 있도록 서비스를 등록하였다.

pmm-admin add mysql --query-source=slowlog --username=pmm --password=pass

 

대시보드를 살펴보면 MySQL 서비스가 등록되었으며, 관련 지표를 확인할 있다.

 

 

PMM 내부 적으로 알림 시스템(alert-manager) 가지고 있어, 특정 임계치를 설정하였을 , 임계치를 넘으면 알림을 받을 있다. 이번 포스트에서는 다루지 않지만, 이러한 알림 시스템까지 설정하여 활용하면 시스템에 대한 상태를 매번 지켜보지 않아도 문제가 발생하였을 , 메신저 등으로 알림을 받을 있어 빠르게 문제 인지 원인을 파악할 있다.

 

 

[참고자료]

l   https://prometheus.io/docs/introduction/overview/

l   https://docs.percona.com/percona-monitoring-and-management/setting-up/server/docker.html

l   https://docs.percona.com/percona-monitoring-and-management/setting-up/client/mysql.html

l   https://prometheus.io/docs/introduction/overview/

 

 

 

 

 

2023-08-22 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MariaDB, Percona, DB모니터링, PMM, MySQL모니터링, 퍼코나 모니터링, Grafana, Promethus, 슬로우쿼리모니터링

MySQL Galera Cluster + ProxySQL에서 Galera Cluster 특성을 고려한 R/W 호스트 그룹 설정 하기

 

l  Version : MySQL, MariaDB, Galera Cluster, ProxySQL 2.X

 

Galera Cluster (이하 갈레라 클러스터”) 다중 마스터 동기 복제를 제공하는 솔루션으로 DB 간의 직접적인 데이터 공유가 없는 형태의 오픈소스 고가용성 솔루션이다.  갈레라 클러스터는 동기 방식의 복제구조를 사용하는 멀티마스터 RDB 클러스터로 제공된다.

ProxySQL 여러 데이터베이스에 대해 동일한 커넥션을 재사용할 있도록 멀티 플렉싱 기능과 쿼리를 분석하여 Write/Read 분산하는 다양한 기능을 제공하는 솔루션이다.

l  ProxySQL 이란 무엇인가 : https://sungwookkang.com/1528

l  MySQL/MariaDB 환경에서 다중 마스터 복제를 지원하는 Galera Cluster 알아보기 : https://sungwookkang.com/1536

 

ProxySQL 2.x 부터는 Galera Cluster 좀더 유연하게 지원한다. 이번 포트스에서는 MySQL Galera cluster + ProxySQL 구성된 환경에서 Galera Cluster 특성을 고려한 ProxySQL Write, Read 호스트 그룹 전략에 대해서 알아본다.

 

[mysql_galera_hostgroups]

ProxySQL에서 호스트 그룹에 따라 Write, Read 역할을 정의한다. Galera Cluster 환경에서는 mysql_galera_hostgroups에서 해당 노드의 역할을 정의할 있다. 아래 스크립트를 실행하면 mysql_galera_hostgroups 테이블의 정보를 확인할 있다.

select * from mysql_galera_hostgroups\G

 

Admin> select * from mysql_galera_hostgroupsG
*************************** 1. row ***************************
writer_hostgroup: 10
backup_writer_hostgroup: 20
reader_hostgroup: 30
offline_hostgroup: 9999
active: 1
max_writers: 1
writer_is_also_reader: 2
max_transactions_behind: 20
comment:

 

 

l   writer_hostgroup : 쓰기 가능한 (read_only=0) 모든 구성원을 포함할 호스트 그룹의 ID

l   backup_writer_hostgroup : 쓰기 백업 노드의 그룹 ID. 클러스터가 다중 쓰기 모드(read_only=0 여러 노드가 있는 경우)에서 실행 중이고 max_writers 전체 쓰기 노드 수보다 작은 수로 설정된 경우 추가 노드는 backup_writer_hostgroup 으로 이동된다.

l   reader_hostgroup : 읽기 가능한 (read_only=1 노드) 모든 구성원을 포함할 호스트 그룹의 ID

l   offline_hostgroup : 클러스터에서 참여하지 않는 노드들에 대한 그룹ID. ProxySQL 모니터링에서 호스트가 OFFLINE 것으로 확인되면 호스트가 offline_hostgroup으로 이동된다.

l   Active : 호스트 그룹을 활성화하는 부울 (0 또는 1)

l   max_writers : writer 호스트 그룹에서 허용되는 최대 노드 수를 제어한다. Writer_hostgroup 노드 수가 max_writers 수보다 경우 추가 노드는 backup_writer_hostgroup으로 이동된다.

l   writer_is_also_reader : 1이면 writer_hostgroup 노드가 reader_hostgroup에도 배치되어 읽기에 사용된다. 2 설정하면 backup_writer_hostgroup 노드가 writer_hostgroup 노드 대신 reader_hostgroup 배치된다.

l   max_transactions_behind : 오래된 읽기를 방지하기 위해 노드가 SHUNNED되기 전에 클러스터의 노드가 대기할 있는 최대 쓰기 세트 수를 결정한다. (wsrep_local_recv_queue Galera 변수를 쿼리하여 결정됨).

l   Comment : 사용자가 정의한 목적에 맞게 사용할 있는 텍스트 필드

 

 

ProxySQL MySQL 상태 변수를 모니터링하여 Galera 상태 확인을 수행한다.

l   read_only : 설정값이 ON 경우, writer_is_also_reader 1 아니면 ProxySQL 정의된 호스트를 reader_hostgroup으로 그룹화한다.

l   wsrep_desync : 설정값이 ON 경우, ProxySQL 노드를 사용할 없는 것으로 표시하여 offline_hostgroup으로 이동한다.

l   wsrep_reject_queries : 설정값이 ON이면 ProxySQL 노드를 사용할 없는 것으로 표시하고 이를 offline_hostgroup으로 이동한다. (특정 유지 관리 상황에서 유용함).

l   wsrep_sst_donor_rejects_queries : 설정값이 ON이면 ProxySQL Galera 노드가 SST 기증자 역할을 하는 동안 해당 노드를 사용 불가로 표시하여 offline_hostgroup으로 이동한다.

l   wsrep_local_state : 해당 값이 동기화를 의미하는 4 이외의 값을 반환하면 ProxySQL 노드를 사용할 없는 것으로 표시하고 offline_hostgroup으로 이동한다.

l   wsrep_local_recv_queue : 해당 값이  max_transactions_behind보다 높으면 노드가 회피된다.

l   wsrep_cluster_status : 상태가 기본이 아닌 다른 상태로 반환되면 ProxySQL 노드를 사용할 없는 것으로 표시하고 offline_hostgroup으로 이동한다.

 

ProxySQL 2.x mysql_galera_hostgroups 매개변수와 mysql_query_rules 정책을 함께 결합함으로써 훨씬 다양한 Galera Cluster 구성에 대한 유연성을 갖게 되었다. 예를 들어 쿼리 규칙의 대상 호스트 그룹으로 정의된 단일 쓰기, 다중 쓰기 다중 읽기 호스트 그룹을 가질 있으며, 쓰기 수를 제한하고 오래된 읽기 동작을 보다 세밀하게 제어할 있다.

 

ProxySQL에서 max_writers writer_is_also_reader 변수는 ProxySQL 백엔드 MySQL 서버를 동적으로 그룹화하는 방법을 결정할 있으며 커넥션 분산 쿼리 라우팅에 직접적인 영향을 끼친다. 예를 들어 아래와 같이 MySQL 백엔드(노드) 서버가 등록되어 있다고 가정한다. 등록된 노드는 아래 스크립트로 확인 가능하다.

 

Admin> select hostgroup_id, hostname, status, weight from mysql_servers;

 

Admin> select hostgroup_id, hostname, status, weight from mysql_servers;
+--------------+--------------+--------+--------+
| hostgroup_id | hostname     | status | weight |
+--------------+--------------+--------+--------+
| 10           | DB1          | ONLINE | 1      |
| 10           | DB2          | ONLINE | 1      |
| 10           | DB3          | ONLINE | 1      |
+--------------+--------------+--------+--------+

 

그리고 현재 구성되어 있는 mysql_galera_hostgroup 설정도 살펴본다.

Admin> select * from mysql_galera_hostgroups\G

 

 

Admin> select * from mysql_galera_hostgroups\G
*************************** 1. row ***************************
writer_hostgroup: 10
backup_writer_hostgroup: 20
 reader_hostgroup: 30
offline_hostgroup: 9999
active: 1
max_writers: 1
writer_is_also_reader: 2
max_transactions_behind: 20
comment:

 

현재 노드 설정에서 모든 호스트가 가동되어 실행 중임을 고려하면 ProxySQL 호스트를 아래와 같이 다양한 시나리오로 그룹화할 있다.

 

그림에 따른 3가지 케이스의 특징을 한번 살펴본다.

Configuration Description
writer_is_also_reader=0 l  호스트를 2개의 호스트 그룹(writer backup_writer)으로 그룹화한다.
l  Writer backup_writer 일부이다.
l  Write Reader 아니므로 read_only=1 설정된 호스트가 없기 때문에 호스트 그룹 30(Reader)에는 아무것도 없다. 읽기 전용 플래그를 활성화하는 것은 Galera에서 일반적인 관행이 아니다.
writer_is_also_reader=1 l  호스트를 3개의 호스트 그룹(writer, backup_writer reader)으로 그룹화한다.
l  Galera 변수 read_only=0 영향을 미치지 않으므로 writer 호스트 그룹 30(reader)에도 있다.
l  writer backup_writer 일부가 아니다.
writer_is_also_reader=2 l  writer_is_also_reader=1 유사하지만 writer backup_writer 일부이다.

 

그룹 구성에 따른 특성을 활용하면 사용자는 특정 워크로드에 맞는 호스트 그룹 대상을 다양하게 선택할 있다. "핫스팟" 쓰기는 다중 마스터 충돌을 줄이기 위해 하나의 서버로만 이동하도록 구성할 있다. 충돌하지 않는 쓰기는 다른 마스터에 균등하게 분배될 있다. 쓰기는 최신 서버로 전달될 있으며 분석 읽기는 슬레이브 복제본으로 전달될 있다.

 

[Galera 클러스터용 ProxySQL 배포]

아래 그림과 같이 ClusterControl 의해 배포된 3노드 Galera Cluster 구성되어 있다고 가정하고, 시나리오를 만족하기 위해 클러스터를 세팅하는 과정을 다루어 본다.

 

시나리오는 아래와 같다.

WordPress 애플리케이션은 Docker에서 실행되고 WordPress 데이터베이스는 베어메탈 서버에서 실행되는 Galera Cluster에서 호스팅 된다. 우리는 WordPress 데이터베이스 쿼리 라우팅을 제어하고 데이터베이스 클러스터 인프라를 완전히 활용하기 위해 WordPress 컨테이너와 함께 ProxySQL 컨테이너를 실행하기로 결정했다. 읽기-쓰기 비율이 80%-20%이므로 ProxySQL 다음과 같이 구성하려고 한다.
l   모든 쓰기를 하나의 Galera 노드로 전달(충돌 감소, 쓰기에 집중)
l   다른 Galera 노드에 대한 모든 읽기의 균형을 분배 (대부분의 워크로드에 대해 나은 분배).

 

시나리오를 만족할 있도록 ProxySQL 구성한다. 먼저 컨테이너에 매핑할 있도록 Docker 호스트 내부에 ProxySQL 구성 파일을 만든다.

$ mkdir /root/proxysql-docker
$ vim /root/proxysql-docker/proxysql.cnf

 

아래와 같이 설정값을 입력한다. (코드에 대한 자세한 설명은 아래부분에서 하나씩 설명한다.)

datadir="/var/lib/proxysql"
 
admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
    refresh_interval=2000
    web_enabled=true
    web_port=6080
    stats_credentials="stats:admin"
}
 
mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
    default_schema="information_schema"
    stacksize=1048576
    server_version="5.1.30"
    connect_timeout_server=10000
    monitor_history=60000
    monitor_connect_interval=200000
    monitor_ping_interval=200000
    ping_interval_server_msec=10000
    ping_timeout_server=200
    commands_stats=true
    sessions_sort=true
    monitor_username="proxysql"
    monitor_password="proxysqlpassword"
    monitor_galera_healthcheck_interval=2000
    monitor_galera_healthcheck_timeout=800
}
 
mysql_galera_hostgroups =
(
    {
        writer_hostgroup=10
        backup_writer_hostgroup=20
        reader_hostgroup=30
        offline_hostgroup=9999
        max_writers=1
        writer_is_also_reader=1
        max_transactions_behind=30
        active=1
    }
)
 
mysql_servers =
(
    { address="db1.cluster.local" , port=3306 , hostgroup=10, max_connections=100 },
    { address="db2.cluster.local" , port=3306 , hostgroup=10, max_connections=100 },
    { address="db3.cluster.local" , port=3306 , hostgroup=10, max_connections=100 }
)
 
mysql_query_rules =
(
    {
        rule_id=100
        active=1
        match_pattern="^SELECT .* FOR UPDATE"
        destination_hostgroup=10
        apply=1
    },
    {
        rule_id=200
        active=1
        match_pattern="^SELECT .*"
        destination_hostgroup=30
        apply=1
    },
    {
        rule_id=300
        active=1
        match_pattern=".*"
        destination_hostgroup=10
        apply=1
    }
)
 
mysql_users =
(
    { username = "wordpress", password = "passw0rd", default_hostgroup = 10, transaction_persistent = 0, active = 1 },
    { username = "sbtest", password = "passw0rd", default_hostgroup = 10, transaction_persistent = 0, active = 1 }
)

 

코드에서 살펴보면 mysql_galera_hostgroups 구성은 아래와 같이 정의되어 있다.

mysql_galera_hostgroups =
(
    {
        writer_hostgroup=10
        backup_writer_hostgroup=20
        reader_hostgroup=30
        offline_hostgroup=9999
        max_writers=1
        writer_is_also_reader=1
        max_transactions_behind=30
        active=1
    }
)

 

writer 그룹은 10, backup 그룹은 20, reader 그룹은 30으로 정의 되어있다. max_writers 1 설정하여 모든 쓰기가 전송되어야 하는 호스트 그룹 10 대해 단일 쓰기로 작동하도록 하였다. 그런 다음 writer_is_also_reader 1 정의하여 모든 Galera 노드를 읽기 노드에 참여할 있도록 하였다. 이렇게 하면 읽기 요청은 모든 노드에 균등하게 배포할 있다. Offline 그룹은 9999이며 ProxySQL 작동하지 않는 Galera 노드를 감지한 경우 offline_hostgroup으로 이동시킨다.

 

mysql_servers 클러스터에 참여하는 노드 정보를 정의한다. 시나리오에서는 모든 노드를 쓰기 가능한 호스트 그룹인 10으로 MySQL 서버를 구성하였다.

mysql_servers =
(
    { address="db1.cluster.local" , port=3306 , hostgroup=10, max_connections=100 },
    { address="db2.cluster.local" , port=3306 , hostgroup=10, max_connections=100 },
    { address="db3.cluster.local" , port=3306 , hostgroup=10, max_connections=100 }
)

 

위의 구성을 그림으로 표현하면 ProxySQL 아래와 같이 호스트 그룹을 구성하고 인식한다.

 

 

mysql_query_rules 쿼리 라우팅을 정의한다. 요구 사항에 따라 모든 읽기는 작성자(호스트 그룹 20) 제외한 모든 Galera 노드로 전송되어야 하며 다른 모든 것은 단일 작성자의 호스트 그룹 10으로 전달된다.

mysql_query_rules =
(
    {
        rule_id=100
        active=1
        match_pattern="^SELECT .* FOR UPDATE"
        destination_hostgroup=10
        apply=1
    },
    {
        rule_id=200
        active=1
        match_pattern="^SELECT .*"
        destination_hostgroup=20
        apply=1
    },
    {
        rule_id=300
        active=1
        match_pattern=".*"
        destination_hostgroup=10
        apply=1
    }
)

 

마지막으로 ProxySQL 통해 전달될 MySQL 사용자를 정의한다.

mysql_users =
(
    { username = "wordpress", password = "passw0rd", default_hostgroup = 10, transaction_persistent = 0, active = 1 },
    { username = "sbtest", password = "passw0rd", default_hostgroup = 10, transaction_persistent = 0, active = 1 }
)

 

우리는 transaction_persistent 0으로 설정하여 사용자로부터 오는 모든 연결이 읽기 쓰기 라우팅에 대한 쿼리 규칙을 준수하도록 한다. 그렇지 않으면 연결이 하나의 호스트 그룹에 도달하게 되어 로드 밸런싱의 목적이 상실하기 때문이다. 모든 MySQL 서버에서 해당 사용자를 먼저 생성하는 것을 잊지 않도록 한다. ClusterControl 활용해서 사용자를 추가하는 경우 [관리] -> [스키마 사용자 기능] 사용하여 해당 사용자를 생성할 있다.

 

설정 파일 생성이 완료되었으면 이제 컨테이너를 실행한다. ProxySQL 컨테이너를 시작할 ProxySQL 구성 파일을 바인드 마운트로 매핑하기 때문에 아래와 같은 명령어로 실행한다.

$ docker run -d
--name proxysql2
--hostname proxysql2
--publish 6033:6033
--publish 6032:6032
--publish 6080:6080
--restart=unless-stopped
-v /root/proxysql/proxysql.cnf:/etc/proxysql.cnf
severalnines/proxysql:2.0

 

마지막으로 ProxySQL 컨테이너 포트 6033 가리키도록 WordPress에서 데이터베이스 연결 포트를 변경한다.

$ docker run -d
--name wordpress
--publish 80:80
--restart=unless-stopped
-e WORDPRESS_DB_HOST=proxysql2:6033
-e WORDPRESS_DB_USER=wordpress
-e WORDPRESS_DB_HOST=passw0rd
wordpress

 

지금까지의 서비스 구성을 그림으로 표현하면 아래 아키텍처와 같다.

 

 

ProxySQL 컨테이너를 영구적으로 유지하려면 /var/lib/proxysql/ Docker 볼륨에 매핑하거나 바인드를 마운트 해야한다. 아래 스크립트는 컨테이너 실행시 설정 파일을 영구 저장소로 바인드하는 예제이다.

$ docker run -d
--name proxysql2
--hostname proxysql2
--publish 6033:6033
--publish 6032:6032
--publish 6080:6080
--restart=unless-stopped
-v /root/proxysql/proxysql.cnf:/etc/proxysql.cnf
-v proxysql-volume:/var/lib/proxysql
severalnines/proxysql:2.0

 

위와 같이 영구 저장소로 실행하면 컨테이너를 다시 시작 /root/proxysql/proxysql.cnf 사용하지 않는 다는 것에 주의한다. 이는 /var/lib/proxysql/proxysql.db 존재하는 경우 ProxySQL 구성 파일에서 로드 옵션을 건너뛰고 대신 SQLite 데이터베이스에 있는 항목을 로드하는 ProxySQL 다중 계층 구성 때문이다. (initial 플래그로 proxysql 서비스를 시작하면 설정 파일의 값으로 시작한다.)  ProxySQL 구성 관리는 구성 파일을 사용하는 대신 포트 6032 ProxySQL 관리 콘솔을 통해 수행해야 한다.

 

ProxySQL 서비스 시작 사용하는 구성파일에 대한 자세한 정보는 아래 글을 참고한다.

l   ProxySQL 서비스에 필요한 설정값을 어디에 저장하고 재사용할까? : https://sungwookkang.com/1535

 

 

[Monitoring]

ProxySQL 프로세스 로그는 기본적으로 syslog 기록되며 표준 docker 명령을 사용하여 있다.

$ docker ps
$ docker logs proxysql2

 

현재 ProxySQL에서 운영중인 호스트 그룹을 확인하려면 runtime_mysql_servers 테이블을 쿼리한다. 아래 스크립트는 호스트 환경에서 컨테이너의 ProxySQL 관리 콘솔에 접속한다.

$ docker exec -it proxysql2 mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> '

 

$ docker exec -it proxysql2 mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> '
Admin> select hostgroup_id,hostname,status from runtime_mysql_servers;
+--------------+--------------+--------+
| hostgroup_id | hostname     | status |
+--------------+--------------+--------+
| 10           | 192.168.0.21 | ONLINE |
| 30           | 192.168.0.21 | ONLINE |
| 30           | 192.168.0.22 | ONLINE |
| 30           | 192.168.0.23 | ONLINE |
| 20           | 192.168.0.22 | ONLINE |
| 20           | 192.168.0.23 | ONLINE |
+--------------+--------------+--------+

 

만약 특정 쓰기 노드가 다운되면 해당 노드는 offline_hostgroup (HID 9999)으로 이동된다. 예제에서는 192.168.0.21 노드가 다운되어 9999 그룹으로 이동되었다.

Admin> select hostgroup_id,hostname,status from runtime_mysql_servers;
+--------------+--------------+--------+
| hostgroup_id | hostname     | status |
+--------------+--------------+--------+
| 10           | 192.168.0.22 | ONLINE |
| 9999         | 192.168.0.21 | ONLINE |
| 30           | 192.168.0.22 | ONLINE |
| 30           | 192.168.0.23 | ONLINE |
| 20           | 192.168.0.23 | ONLINE |
+--------------+--------------+--------+

 

위의 토폴로지 변경 사항은 아래 그림과 같다.

 

 

ProxySQL에서 admin-web_enabled=true 설정할 경우 통계 UI 사용할 있다. UI 액세스하려면 포트 웹브라우저로 Docker 호스트6080 포트로 접속한다. (, http://192.168.0.200:8060) 사용자 이름을 묻는 메시지가 표시되면 admin-stats_credentials 정의된 자격 증명을 입력한다. 로그인하면 아래와 같은 통계 화면을 있다.

 

 

MySQL 연결 테이블을 모니터링하면 모든 호스트 그룹에 대한 연결 분포 개요를 확인할 있다.

Admin> select hostgroup, srv_host, status, ConnUsed, MaxConnUsed, Queries from stats.stats_mysql_connection_pool order by srv_host;

 

Admin> select hostgroup, srv_host, status, ConnUsed, MaxConnUsed, Queries from stats.stats_mysql_connection_pool order by srv_host;
+-----------+--------------+--------+----------+-------------+---------+
| hostgroup | srv_host     | status | ConnUsed | MaxConnUsed | Queries |
+-----------+--------------+--------+----------+-------------+---------+
| 20        | 192.168.0.23 | ONLINE | 5        | 24          | 11458   |
| 30        | 192.168.0.23 | ONLINE | 0        | 0           | 0       |
| 20        | 192.168.0.22 | ONLINE | 2        | 24          | 11485   |
| 30        | 192.168.0.22 | ONLINE | 0        | 0           | 0       |
| 10        | 192.168.0.21 | ONLINE | 32       | 32          | 9746    |
| 30        | 192.168.0.21 | ONLINE | 0        | 0           | 0       |
+-----------+--------------+--------+----------+-------------+---------+

 

위의 출력 값을 살펴보면 쿼리 규칙에 의해 호스트 그룹 30 아무 것도 처리하지 않음을 있다.

 

Galera 노드와 관련된 통계는 mysql_server_galera_log 테이블에서 있다.

>  select * from mysql_server_galera_log order by time_start_us desc limit 3 \G

 

Admin>  select * from mysql_server_galera_log order by time_start_us desc limit 3 \G
*************************** 1. row ***************************
                       hostname: 192.168.0.23
                           port: 3306
                  time_start_us: 1552992553332489
                success_time_us: 2045
              primary_partition: YES
                      read_only: NO
         wsrep_local_recv_queue: 0
              wsrep_local_state: 4
                   wsrep_desync: NO
           wsrep_reject_queries: NO
wsrep_sst_donor_rejects_queries: NO
                          error: NULL
*************************** 2. row ***************************
                       hostname: 192.168.0.22
                           port: 3306
                  time_start_us: 1552992553329653
                success_time_us: 2799
              primary_partition: YES
                      read_only: NO
         wsrep_local_recv_queue: 0
              wsrep_local_state: 4
                   wsrep_desync: NO
           wsrep_reject_queries: NO
wsrep_sst_donor_rejects_queries: NO
                          error: NULL
*************************** 3. row ***************************
                       hostname: 192.168.0.21
                           port: 3306
                  time_start_us: 1552992553329013
                success_time_us: 2715
              primary_partition: YES
                      read_only: NO
         wsrep_local_recv_queue: 0
              wsrep_local_state: 4
                   wsrep_desync: NO
           wsrep_reject_queries: NO
wsrep_sst_donor_rejects_queries: NO
                          error: NULL

 

쿼리 결과 내용은 특정 타임스탬프에 대한 모든 Galera 노드의 관련 MySQL 변수/상태 상태를 반환한다. 구성에서는 Galera 상태 확인이 2초마다 실행되도록 구성되어 있다. (monitor_galera_healthcheck_interval=2000). 따라서 클러스터에 토폴로지 변경이 발생하는 경우 최대 장애 조치 시간은 2초이다.

 

지금까지 MySQL Galera 클러스터 환경에서 ProxySQL 조합하여 사용할 , 쓰기, 읽기 그룹에 대한 정의와 ProxySQL 컨테이너로 구성하는 과정을 다루었다. 실제 운영환경에서는 각자의 노드 구성과 SLA 모두 다르기 때문에 내용을 이해하고 활용할 있도록 한다.

 

[참고자료]

l   How to Run and Configure ProxySQL 2.0 for MySQL Galera Cluster on Docker : https://severalnines.com/blog/how-run-and-configure-proxysql-20-mysql-galera-cluster-docker/

l  ProxySQL 이란 무엇인가 : https://sungwookkang.com/1528

l  MySQL/MariaDB 환경에서 다중 마스터 복제를 지원하는 Galera Cluster 알아보기 : https://sungwookkang.com/1536

l  ProxySQL 서비스에 필요한 설정값을 어디에 저장하고 재사용할까? : https://sungwookkang.com/1535

 

 

 

2023-08-10 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MariaDB, Galera Cluster, 마리아디비, 마이에스큐엘, 갈레라 클러스터, MariaDB 복제, MariaDB Cluster, MySQL Cluster, MariaDB Replication, MySQL, ProxySQL, ProxySQL설정, ProxySQL 구성관리, ProxySQL 시작, Proxy Container

MySQL/MariaDB 환경에서 다중 마스터 복제를 지원하는 Galera Cluster 알아보기

 

l  Version : MySQL, MariaDB, Galera Cluster

 

Galera Cluster (이하 갈레라 클러스터”) 다중 마스터 동기 복제를 제공하는 솔루션으로 DB 간의 직접적인 데이터 공유가 없는 형태의 오픈소스 고가용성 솔루션이다.  현재 갈레라 클러스터는 MySQL Percona Xtradb 클러스터와 MariaDB 가지 버전을 지원한다. 특히 MariaDB에서는 Galera Cluster 공식적으로 지원한다. 이번 포스트에서는 갈레라 클러스터의 개념과 장단점에 대해서 알아본다.

 

갈레라 클러스터는 동기 방식의 복제구조를 사용하는 멀티마스터 RDB 클러스터로 제공된다. 갈레라 클러스터는 인증 기반 복제를 제공하며 노드간 통신을 위해 wsrep API 사용한다. 데이터 복제는 논리적으로는 완전 동기이지만 실제 write tablespace commit하는 과정은 별개이고 노드간에는 비동기로 동작한다. 그래서 갈레라 클러스터에서는 이러한 방식을 virtually synchronous replication이라 부른다.

 

 

[Galera Cluster 특징]

l  다중 쓰기 : Active-Active 방식의 다중 Master 구조이기 때문에 모든 노드에서 Read/Write 가능하다.

l  동기 복제 : 슬레이브 지연이 없고 노드 충돌시 데이터 손실이 없다.

l  일관적 데이터 유지 : 모든 노드는 항상 같은 데이터 상태를 유지 한다.

l  장애조치 : 노드 장애가 발생하였을 빠르게 모니터링 되어 전환되므로 서비스 중단이 최소화 된다. 멀티 포인트 쓰기를 지원하므로 전환이 쉽다. 특정 노드 장애가 전체 서비스에 영향을 미치지 않는다. 이론적으로 모든 노드가 쓰기 가능하기 때문에 모든 노드가 다운되기 전까지는 서비스 유지가 가능하다.

l  자동 노드 복제 : 노드를 추가하거나 관리를 위해 종료 또는 노드에서 제거 해야 경우 증분 데이터 또는 기본 데이터를 수동으로 백업할 필요가 없다. 갈레라 클러스터는 노드를 추가할 경우 자동으로 온라인 노드의 데이터를 가져오고 일관성을 유지한다.

 

[Galera Cluster 단점]

l   동기 복제 특성상 클러스터의 성능은 가장 성능이 낮은 노드에 의해 결정된다. 그래서 최대한 동일한 성능의 서버로 구성하는 것이 좋다.

l   동기 복제이기 때문에 쓰기가 많이 발생할 경우 다른 아키텍처(비동기 복제 )보다 상대적으로 성능이 저하되며 스케일 아웃에 한계가 있다.

l   신규 노드가 조인되거나 대규모 노드 조인시 지연이 발생하면 데이터 전체를 복사(SST, 상태 스냅샷 전동) 하는 문제가 발생한다. 이때 데이터를 제공하는 서버를 Donor라고 하는데 Donor 노드는 복제 데이터를 전송하는 동안 읽기 쓰기 서비스가 중단된다.

l   인증 기반 복제를 사용하기 때문에 인증이 실패할 경우 데드락이 발생할 가능성이 있다.

 

[Galera Cluster 제약사항]

l   MyISAM 일부와 InnoDB 스토리지 엔진 테이블만 지원한다.

l   노드 간의 쿼리 실행 순서를 피하기 위해 모든 테이블에서 기본키를 필수적으로 사용한다.

n  기본 키가 없는 테이블에서는 DELETE 작업이 지원되지 않는다.

n  기본 키가 없는 테이블의 행은 다른 노드에서 다른 순서로 나타날 있다.

l   Query Cache 비활성화한다

l   XA 트랜잭션 (전역 트랜잭션) 지원하지 않는다.

l   일반 쿼리 로그와 슬로우 쿼리 로그는 테이블로 보낼 없다. 이러한 로그를 활성화하는 경우 log_output=FILE 설정하여 로그를 파일로 전달해야 한다.

 

 

Galera Cluster wsrep(Write-Set Replication) API 통해 노드와 데이터를 동기화 한다. 여기서 wsrep API DBMS 복제를 위한 인터페이스이며 실제 동기화 구현은 Galera Replication Plugin에서 이루어 진다.

 

 

노드에서 쓰기나 업데이트가 발생할 경우 노드간에 데이터를 복제하고 업데이트 내용을 GCache 영역에 저장한다. GCache 복제된 트랜잭션을 위한 임시 저장소 역할을 한다.

 

 

Galera Cluster 인증기반 복제를 사용한다. 인증 기반 복제의 주요 컨셉은 트랜잭션이 충돌이 없다고 가정하고 커밋 지점에 도달할 때까지 관례적으로 실행된다는 것이다. 이를 낙관적 실행이라고 한다.

Galera Cluster에서 인증 기반 복제 구현은 트랜잭션의 전역 순서에 따라 다르다. Galera Cluster 복제 중에 트랜잭션에 전역 시퀀스 번호 또는 seqno 할당한다. 트랜잭션이 커밋 지점에 도달하면 노드는 마지막으로 성공한 트랜잭션의 시퀀스 번호와 시퀀스 번호를 확인한다. 사이의 간격을 통해 모든 트랜잭션은 해당 트랜잭션과 기본 충돌이 있는지 확인한다. 충돌이 감지되면 인증 테스트에 실패한다.

  그림에서 데이터가 변경되고 커밋되기 까지의 순서를 나타내면 아래와 같다.

1.          클라이언트가 데이터를 수정하고 커밋 요청을 서버에 요청한다.

2.          서버는 커밋 요청을 받으면 실제 커밋을 실행하기 전에 트랜잭션 변경된 행의 기본 키에 의해 데이터베이스에 대한 모든 변경 사항이 쓰기 세트로 수집된다.

3.          데이터베이스는 쓰기 세트를 다른 모든 노드로 전송한다.

4.          쓰기 세트는 기본 키를 사용하여 결정적 인증 테스트를 거친다. 작업은 쓰기 세트를 생성한 노드를 포함하여 클러스터의 노드에서 수행된다.

5.          노드가 쓰기 세트를 적용할 있는지 여부를 결정한다.

6.          인증 테스트에 실패하면 노드는 쓰기 세트를 삭제하고 클러스터는 원래 트랜잭션을 롤백한다. 그러나 테스트가 성공하면 트랜잭션이 커밋되고 쓰기 세트가 클러스터의 나머지 부분에 적용된다.

 

 

Galera Cluster Replication Write 정책은 First Committer Win이다. 트랜잭션이 커밋되는 시점에 다른 노드에 유효한 트랜잭션인지 여부를 체크하는 방식으로 동작한다. 클러스터 내에서 트랜잭션은 모든 서버에 동시에 반영되거나 전부 반영되지 않는 경우 하나로 동작한다. 이러한 이유로 Write 트랜잭션은 하나의 서버로 사용하는 것을 권장한다.

트랜잭션을 시작하는 시점(BEGIN)에는 자신의 노드에서는 Pessimistic Locking으로 동작하나, 노드 사이에서는 Optimistic Locking Model 동작한다. 먼저 트랜잭션을 자신의 노드에 수행을 하고, 커밋을 시점에 다른 노드로부터 해당 트랜잭션에 대한 유효성을 확인한다. 일반적으로 InnoDB 같이 트랜잭션을 지원하는 시스템인 경우 SQL 시작되는 시점에서 Lock 확인할 있으나, 갈레라 클러스터에서는 커밋되는 시점에 노드 트랜잭션 유효성 체크한다.

 

 

트랜잭션 커밋 또는 롤백 결정은 네트워크를 통한 다른 노드와 통신에서 결정된다. 결정 요소에는 아래 항목들의 상태가 포함된다.

l   네트워크 왕복 시간

l   노드에서 유효성 체크 시간

l   노드에서 데이터 반영 시간

 

Galera Cluster 필요한 최소 노드 수는 2개이다. 그러나 최소 3개의 노드가 권장된다. 최대 노드 제한은 없다. 그러나 10 이상의 노드가 있는 단일 클러스터는 네트워크 또는 인터넷에서 너무 많은 노드를 동기화하는 지연이 발생할 있다. 최대 노드 구성은 네트워크 구성에 따라 달라지므로 각자의 환경에 맞게 구성하도록 한다.

 

Galera Cluster 트랜잭션 크기를 명시적으로 제한하지 않지만 쓰기 집합은 단일 메모리 상주 버퍼로 처리되므로 결과적으로 매우 트랜잭션(: LOAD DATA) 노드 성능에 부정적인 영향을 미칠 있다. 이를 방지하기 위해 wsrep_max_ws_rows wsrep_max_ws_size 시스템 변수는 기본적으로 트랜잭션 행을 128K, 트랜잭션 크기를 2Gb 제한한다. 필요한 경우 사용자는 이러한 제한을 늘릴 있다. (향후 버전에서는 트랜잭션 조각화에 대한 지원을 추가할 예정이라고 한다.)

 

 

MySQL MariaDB에서 고가용성을 위한 솔루션으로 Galera Cluster 대해서 살펴보았다. 다양한 솔루션이 있지만 Galera Cluster 특징은 모든 노드가 마스터 역할로 클러스터에 참여하는 구조로 운영되며, 데이터베이스 사이의 연결이 아닌 외부 wsrep API 통한 데이터 복제가 이루어진다는 점에서 흥미로웠다. 이후 클러스터 구성 방법과 장애조치시 마스터 선정, 옵션에 대한 설정 과정을 다른 포스트에서 다뤄볼 예정이다.

 

[참고자료]

l   https://galeracluster.com/library/documentation/

l   http://galeracluster.com/documentation-webpages/galera-documentation.pdf

l   http://galeracluster.com/documentation-webpages/index.html

l   https://www.percona.com/doc/percona-xtradb-cluster/LATEST/index.html

l   https://mariadb.com/kb/en/library/getting-started-with-mariadb-galera-cluster/

l   https://severalnines.com/resources/whitepapers/galera-cluster-mysql-tutorial/

l   https://www.slideshare.net/marcotusa/galera-explained-3

l   MariaDB Galera Cluster - Known Limitations : https://mariadb.com/kb/en/mariadb-galera-cluster-known-limitations/

l   https://galeracluster.com/library/faq.html

l   https://www.slideshare.net/AbdulManaf19/mariadb-galera-cluster-63088921

 

 

 

2023-08-07 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MariaDB, Galera Cluster, 마리아디비, 마이에스큐엘, 갈레라 클러스터, MariaDB 복제, MariaDB Cluster, MySQL Cluster, MariaDB Replication

ProxySQL 서비스에 필요한 설정값을 어디에 저장하고 재사용할까?

 

l  Version : MySQL, ProxySQL

 

ProxySQL에서 서버를 추가하거나, 사용자를 등록하는 설정을 변경하고 적용하려면 관리자 모드 접속을 통해서 필요한 내용을 수정하고, 수정한 내용을 서비스 중단 없이 runtime 적용할 있다. 그리고 서비스 재시작시 기존의 설정을 유지하기 위해서는 현재 설정 값을 데이터베이스 또는 디스크로 저장하여 재사용 있다.

 

아래 그림을 참고하여 설명하면, 일반적으로 관리자 모드에 접속하여 수정하는 부분은 MEMORY영역이다. 그렇기 때문에 관리자에서 수정하였을 경우에는 아직 메모리에서만 적용된 것으로 서비스에 적용되지 않은 상태이다. (아마도 실수가 발생하였을 서비스 보호를 위해서 변경 사항을 자동으로 적용시키지 않는 모델로 디자인한 같다.) 그리고 LOAD 명령어를 사용하여 RUNTIME영역에 적용을 있다. RUNTIME 적용되면 서비스에 반영되어 운영된다. 그리고 메모리 값을 수정하였더라도, 런타임 기준으로 메모리 롤백이 필요할 런타임의 값을 메모리로 SAVE하여 RUNTIME설정을 메모리로 저장할 있다. 이러한 과정을 영역별로 LOAD SAVE 있다.

 

 

ProxySQL 서비스를 시작할 config 파일을 읽어들여 datadir 위치를 확인한다. 다음에 datadir 경로에 위치한 데이터베이스 파일 또는 디스크 파일이 있는지 여부를 확인하여 해당 파일을 읽어 설정 값으로 사용한다. 일반적인 설치를 진행하였다면 config 파일은 아래 경로에 존재한다.

/etc/proxysql.cnf

 

 

datadir 경로에서 데이터베이스 파일이 발견되면 ProxySQL 지속형 온디스크 데이터베이스(proxysql.db) 읽어 메모리 구성을 초기화한다. 따라서 디스크는 메모리에 로드된 다음 런타임 구성으로 전파된다. 데이터베이스 파일이 없는 경우에는 config 파일의 설정 값을 읽어 메모리 데이터베이스에 적용한 다음 온디스크 데이터베이스에 저장되고 런타임에 적용한다.

 

설정 값을 사용할 데이터베이스 파일이 발견되면 config 파일의 설정 값은 사용되지 않는다는 점에 유의해야 한다. , 정상적인 시작 중에 ProxySQL 지속형 온디스크 데이터베이스에서만 메모리 구성을 초기화 한다.

 

ProxySQL 시작할 config 파일의 설정값을 메모리 런타임에 사용하기 위해서는 아래와 같은 순서를 적용할 있다.

1.       최신의 Config 파일을 배포한다. 일반적인 설치를 진행하였다면 아래 경로에 설정 파일이 위치한다.

/etc/proxysql.cnf

 

2.       배포한 Config 파일에 적절한 권한 부여한다. 실습에서는 775 권한을 부여하였다.

sudo chmod 775 proxysql.cnf

 

3.       실행중인 ProxySQL 서비스를 중지한다. 서비스 중지전에 유입되는 트래픽은 다른 곳으로 라우팅 되도록 하여 서비스 장애가 발생하지 않도록 주의한다.

sudo service proxysql stop

 

4.       기존의 데이터베이스 설정이 사용되지 않도록 데이터베이스 파일을 삭제한다.

sudo rm -rf /var/lib/proxysql/proxy*

 

5.       ProxySQL 서비스를 시작한다. 이때 initial 옵션을 함께 사용한다.

sudo service proxysql start initial

 

 

실제 현업에서 ProxySQL 운영한다고 하면 필요에 따라 다수의 ProxySQL 운영하는 환경이 발생한다. 이러한 환경에서는 서버의 설정 파일을 수정하고 이력을 관리하는 것이 매우 중요하다. 그렇기 때문에 직접 서버에 접속하여 설정 파일을 수정하는 것은 이력이 남지 않아 매우 불편하면서도 위험한 상황이 발생할 있다. 그래서 정책적으로 config파일을 기준으로 수정하고, 이력 관리는 Git 등을 사용하면 안전한 이력 관리를 있다. 그리고 서비스 시작 데이터베이스 정보가 아닌 config 값을 사용하면 혼란없이 다수의 서버 환경에서 안전한 관리가 가능하다.

 

 

 

[참고자료]

l  Configuration system : https://github.com/sysown/proxysql/blob/master/doc/configuration_system.md

 

 

 

 

2023-08-04 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, ProxySQL설정, ProxySQL 구성관리, ProxySQL 시작

MySQL HA + ProxySQL 환경에서 서비스 장애조치 구성

 

l  Version : MySQL, ProxySQL

 

MySQL HA + ProxySQL 환경에서 장애조치가 어떻게 동작하는지 알아본다. 실습 환경은 아래 포스트를 참고하여 구성할 있도록 한다.

 

l  ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

l  MySQL HA 환경에서 Orchestrator 활용한 클러스터 리팩토링 자동 장애조치 구성 : https://sungwookkang.com/1533

 

실습에 사용되는 서버 구성이다.

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
proxy-orchestrator 172.30.1.24 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33, container.d

 

MySQL HA 기본 구성으로 1대의 마스터 서버와, 2대의 슬레이브 서버가 GTID 기반으로 복제 구성이 되어 있다. 그리고 서버 장애시 역할 변경을 위해 오케스트레이터로 자동 failover 구성해 놓은 상태이다.

 

ProxySQL에서는 노드의 서비스 상태를 헬스 체크하여 서비스에 사용할 서버를 판단한다. 아래는 노드의 서비스 상태를 체크하는 모듈이다.

l  connect : 모든 노드(백엔드) 연결하고 성공 또는 실패를 mysql_server_connect_log  테이블에 기록한다.

l  ping : 모든 노드 (백엔드) ping 보내고 성공 또는 실패를 mysql_server_ping_log 테이블에 기록한다. mysql-monitor_ping_max_failures 하트비트가 없으면 MySQL_Hostgroups_Manager 신호를 보내 모든 연결을 끊는다.

l  replication lag : max_replication_lag 0보다 값으로 구성된 모든 노드(백엔드) 대해 Seconds_Behind_Master 확인하고 mysql_server_replication_lag_log 테이블에 확인을 기록한다. Seconds_Behind_Master > max_replication_lag 경우 Seconds_Behind_Master < max_replication_lag까지 서버를 회피한다.

l  read only : mysql_replication_hostgroup 테이블의 호스트 그룹에 있는 모든 호스트에 대해 읽기 전용을 확인하고, 상태값은 mysql_server_read_only_log 기록된다. read_only=1이면 호스트가 reader_hostgroup으로 복사/이동되고, read_only=0이면 호스트가 writer_hostgroup으로 복사/이동된다.

 

이번 포스트에서 다루는 ProxySQL 장애조치 시나리오는 가지이다.

l   시나리오 1 : slave 서버에 장애가 발생하여 장애가 발생한 서버로는 read 트래픽을 요청하지 않는다.

l   시나리오2 : master 서버에 장애가 발생하여 slave 서버중 하나가 master 승격되고, ProxySQL 호스트 그룹에서 Write 가능한 호스트 그룹을 변경한다.

 

시나리오에 따른 실습을 하기 전에 현재 구성되어 있는 ProxySQL 접속하여 현재의 상태를 살펴본다. proxy-sql 서버에서 아래와 같은 스크립트를 실행하여, ProxySQL 관리 모드로 접속한다.

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='admin> '

 

 

현재 호스트 그룹에 등록된 서버들의 목록을 확인할 있다. hostgroup_id = 1 write 가능한 그룹이며, hostgroup_id = 2 읽기가 가능한 그룹이다.

select * from mysql_servers;

 

 

 

아래 스크립트는 등록된 서버들의 커넥션 로그를 확인한다. 만약 여기서 커넥션 오류로그가 있다면 해당 서버와의 통신에 문제가 발생한 것이다.

select * FROM monitor.mysql_server_connect_log order by time_start_us desc limit 10;

 

 

 

위의 커넥션 로그가 실제 쿼리가 호출될 확인된다면, 아래는 서비스 헬스 체크를 위한 로그이다.

select * from monitor.mysql_server_ping_log order by time_start_us desc limit 10;

 

 

 

ProxySQL 클라이언트용 포트로 접속하여 현재 상태에서 정상적으로 모든 서버가 응답하는지 테스트한다.

mysql -uubuntu -p123456 -h127.0.0.1 -P6033 -e "SELECT @@hostname";

 

 

 

아래 스크립트에서는 현재 연결된 커넥션 풀에 대한 상태를 확인할 있다. 실제 연결에 문제가 발생할 경우 stats_mysql_connection_pool status 컬럼에 상태가 변경되는 것을 확인할 있다.

select * from stats_mysql_connection_pool;

 

 

 

시나리오1.  slave 서비스 장애

slave 서버중 하나에 장애가 발생한 상황으로, 장애가 발생한 서버로는 읽기 요청을 하지 않도록 하여 서비스 장애를 예방한다.

 

mysql-slave2 서버에 접속하여, MySQL 서비스를 중지한다.

sudo systemctl stop mysql
 
sudo systemctl status mysql

 

 

서비스가 종료된 상태에서 ProxySQL 관리자의 mysql_servers 테이블 상태를 살펴보면 여전히 모든 서버가 ONLINE 상태로 보이는 것을 확인할 있다. 사실 테이블은 서버의 목록을 관리하는 것이지, 서버의 상태를 나타내지는 않는다. 다만 수동으로 status 값을 OFFLINE으로 변경하면, 서비스가 활성화 상태여도 해당 서버로 트래픽을 보내지 않는다.

select * from mysql_servers;

 

 

 

현재 실시간의 노드의 상태를 확인하려면 runtime_mysql_servers  테이블의 정보를 확인한다. 아래 스크립트를 사용하여 중지된 서버의 상태가 SHUNNED 표시된 것을 확인할 있다.

select * from runtime_mysql_servers;

 

 

 

커넥션 상태에서도 동일하게 SHUNNED으로 나타나는 것을 확인할 있다.

select * from stats_mysql_connection_pool;

 

 

 

커넥션 로그를 확인해보면 중지된 서버(mysql-slave2) 커넥션 실패 로그를 확인할 있다.

select * FROM monitor.mysql_server_connect_log order by time_start_us desc limit 10;

 

 

 

로그에서도 오류를 확인할 있다.

select * from monitor.mysql_server_ping_log order by time_start_us desc limit 10;

 

 

 

mysql-slave2 서버의 MySQL 서비스가 중지된 상태로, ProxySQL에서도 해당 서버의 문제점을 인지한 상태이다. 이제 클라이언트 연결을 통해서 실제 운영중인 서버로만 트래픽이 정상적으로 라우팅 되는지 확인한다. 테스트를 위해 proxy-sql 서버 콘솔에서 아래 스크립트를 여러 실행한다.

mysql -uubuntu -p123456 -h127.0.0.1 -P6033 -e "SELECT @@hostname";

 

 

문제의 서버를 제외하고 정상적인 서버로만 트래픽을 라우팅하는 것을 확인할 있다. ProxySQL에서는 이렇게 문제의 서버를 감지하여 트래픽을 보내지 않음으로써 서비스 장애를 예방할 있다.

 

다른 시나리오 테스트를 진행하기 위해 중지된 서버를 시작하여 클러스터를 원상 복구한다.

 

시나리오2. master 서버 장애

이번 시나리오는 master서버에 장애가 발생하여, 서비스가 다운되고, slave 서버 하나가 master 승격되어 운영되는 환경이다. 이때 장애를 감지하고 자동으로 서버를 승격시키는 역할은 MySQL 오케스트레이터가 한다.

 

mysql-master 서버에 접속하여 MySQL 서비스를 중지한다. 오케스트레이터에서는 master 서버의 장애를 감지하고, 중지된 master 서버를 클러스터에서 격리한 slave 서버중 하나를 master 승격여 클러스터 토폴로지를 재구성한다.

 

새로 구성된 클러스터를 살펴보면 이전 slave역할이었던 mysql-slave2(172.30.1.13)서버가 master 승격된 것을 확인할 있다. 그리고 나머지 slave 서버가 새로운 master 복제하고 있다.

 

 

ProxySQL에서도 커넥션 로그를 확인해보면 기존의 master역할을 하던 mysql-master(172.30.1.97) 문제가 있음을 인지한 것을 확인할 있다.

select * from stats_mysql_connection_pool;

 

 

 

실행중인 서버의 상태를 확인해봐도 문제를 인지한 것을 확인할 있다.

select * from runtime_mysql_servers;

 

 

 

proxy-sql에서 문제가 발생한 노드가 제외되고 서비스가 정상적으로 되는지 확인한다. 호스트 이름을 검색하는 쿼리를 실행해보면 문제없이 작동한다

mysql -uubuntu -p123456 -h127.0.0.1 -P6033 -e "SELECT @@hostname";

 

 

하지만 현재 상태는 정상적인 서비스를 없는 문제가 있다. 현재 클러스터 상태는 기존의 mysql-master (마스터 역할) 서버가 중지되고 mysql-slave2서버가 master 역할로 승격된 상태인데, mysql_servers 테이블의 정보에는 여전히 이전의 mysql-master 서버가 hostgroup_id=1 등록되어 있다. 실질적으로 ProxySQL에서는 Write 가능한 서버를 잘못 인식하고 있는 상태이다. 정보를 업데이트하지 않으면 write 대해서는 계속해서 실패가 발생하게 된다.

이러한 서버 장애시 hostgroup_id 자동으로 변경하기 위해 복제 호스트 그룹으로 관리할 있다. , 이렇게 하기 위해서는 역할에 따른 MySQL 서버의 Read only 속성값이 중요하다. master 역할 서버는 read_only =false, slave 역할의 서버는 read_only=true 되어 있어야 한다. 그렇지 않으면 ProxySQL에서는 read_only=false 서버를 write 가능한 서버로 인식하기 때문에 master-master구조로 인식되어 의도하지 않는 write 오류가 발생할 있다.

 

아래 스크립트는 write 가능한 호스트들은 1 그룹으로, 읽기가 가능한 호스트들은 2 그룹으로 할당한다. 서버의 정보를 변경하였기에 해당 변경 사항이 런타임으로 적용될 있도록 LOAD 명령어도 함께 실행한다.

INSERT INTO mysql_replication_hostgroups VALUES (1,2,'read_only','1=write, 2=read');
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

 

 

 

기존의 클러스터 구성으로 원상 복구하고, mysql-master (마스터 역할) 서버를 중지하였을 , 오케스트레이터에서 자동 장애 조치가 활성화되어 서비스가 중지된 master 서버는 클러스터에서 분리하고, 정상적인 slave 서버중 하나를 master 승격한다. 그리고 ProxySQL에서는 slave에서 master 승격된 서버를 houstgroup_id=1 자동 할당되는 것을 확인할 있다.

 

 

런타임 서버 상태 테이블에서도 장애가 발생한 서버는 SHUNNED 변경되어 서비스에서 제외된 것을 확인할 있으며, 새로운 master 서버가 hostgroup_id=1 변경된 것을 확인할 있다.

 

 

 

이번 실습을 통해서 mysql_servers 테이블은 호스트의 상태 값을 관리하는 테이블이 아닌 호스트 그룹을 관리하기 위한 서버 목록 용도로 사용되는 것을 있었으며, 실제 서비스들의 상태나 운영가능한 호스트들이 목록은 runtime_mysql_servers 테이블에서 확인 가능한 것을 있었다.

 

MySQL 오케스트레이터와 ProxySQL 조합하여 사용할 경우, 오케스트레이터에서는 자동으로 장애를 감지하고 클러스터 토폴로지 재구성으로 빠르게 역할을 승격하여 안정적인 서비스를 유지할 있고, ProxySQL에서는 오케스트레이션에서 승격시킨 서버의 상태를 확인하여 호스트 그룹의 정보를 자동으로 변경함으로써, 만약의 상태에도 안정적인 서비스가 가능한 구성을 만들 있다는 것을 확인할 있다.

 

[참고자료]

l   Orchestrator : https://github.com/openark/orchestrator

l   Failure detection : https://github.com/openark/orchestrator/blob/master/docs/failure-detection.md

l   MySQL Orchestrator - HA(High Availability) - 2 - 리팩토링 Failover Automated Recovery : https://hoing.io/archives/91

l   Configuration: failure detection : https://github.com/openark/orchestrator/blob/master/docs/configuration-failure-detection.md

l   https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case : https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case

l   Orchestrator: MySQL Replication Topology Manager : https://www.percona.com/blog/orchestrator-mysql-replication-topology-manager/

l   Orchestrator and ProxySQL : https://www.percona.com/blog/orchestrator-and-proxysql/

 

 

 

 

 

2023-08-02 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, MySQL복제 오케스트레이션, MySQL복제설치, MySQL MHA, MySQL Orchestrator , MySQL장애조치, MySQL고가용성

MySQL HA 환경에서 Orchestrator 활용한 클러스터 리팩토링 자동 장애조치 구성

 

l  Version : MySQL, Orchestrator

 

MySQL HA 환경에서 Orchestrator(이하 오케스트레이터’) 활용하여 클러스터 리팩토링 장애조치 방법에 대해서 알아본다. 이번 실습을 진행하기 위해서는 MySQL HA Orchestrator 구성이 완료되어 있어야 한다. 아래 링크를 참고하여 HA구성 Orchestrator 구성할 있도록 한다.

l  ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

l  MySQL 고가용성 운영을 위한 Orchestrator 설치 : https://sungwookkang.com/1532

 

실습에 사용되는 서버 구성이다.

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
proxy-orchestrator 172.30.1.24 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33, container.d

 

MySQL HA 기본 구성으로 1대의 마스터 서버와, 2대의 슬레이브 서버가 GTID 기반으로 복제 구성이 되어 있다.

이번 포스트에서 4개의 시나리오를 구성해 보았으며, 시나리오별로 실습 과정을 다루었다.

시나리오1. Slave Refactoring

Slave간에 복제 토폴로지 리팩토링으로, 슬레이브1, 슬레이브2 마스터에서 싱크하던 환경에서, 슬레이브2 슬레이브1에서 복제를 싱크할 있도록 토폴로지를 변경한다.

 

 

시나리오2. Master Refactoring (Promote)

슬레이브 서버를 마스터로 승격시키고, 기존의 마스터를 슬레이브로 역할을 전환한다.

 

 

시나리오3. Master – Master Refactoring

슬레이브 서버중 하나를 마스터로 승격시켜 대의 마스터로 구성한다.

 

 

시나리오4. Master Failover

기존의 운영중이던 마스터 서버를 강제로 중지하여 장애 상황을 구현한 다음, 현재 슬레이브로 운영되던 서버중 하나가 새로운 마스터가 되어 장애조치 된다. 그리고 나머지 슬레이브 서버는 새로운 마스터 서버와 복제 싱크를 한다.

 

 

실습환경 살펴보기

오케스트레이터 대시보드에 접속하면 현재 구성되어 있는 클러스터 목록을 있다. 여기에서 숫자 3 1 보이는데, 3 현재 구성된 클러스터의 전체 서버 개수이며, 2 슬레이브 서버 개수이다.

 

 

클러스터를 클릭해보면 현구 구성되어 있는 클러스터 토폴로지를 확인할 있다. 시나리오에 따라 서버의 역할이 변경되기 때문에, 현재 구성되어 있는 서버이름과 역할을 혼돈하지 않도록 주의한다.

 

 

시나리오1. Slave Refactoring

Slave간에 복제 토폴로지 리팩토링으로, 슬레이브1, 슬레이브2 마스터에서 싱크하던 환경에서, 슬레이브2 슬레이브1에서 복제를 싱크 있도록 토폴로지를 변경한다.

 

mysql-slave2(172.30.1.13)서버를 드래그하여 mysql-slave1(172.30.1.10) 서버에 드롭한다. 이때 드롭 위치가 중요하다. 서버의 중간쯤으로 이동하면 드롭하는 서버 정보에 relocate라고 표시가 되는데 이때 드롭한다.

 

 

Relocate 대한 변경 사항에 대해서 경고 팝업이 나타나고 OK 클릭하면 변경 사항이 적용된다.

 

 

클러스터 토폴로지 구조가 변경된 것을 확인할 있다.

 

 

mysql-slave2(172.30.1.13) 서버에 접속하여 호스트 이름 복제 상태를 조회해 보면, 현재 mysql-slave1(172.30.1.10) 서버를 마스터 호스트로 연결되어 있는 것을 확인할 있다.

select @@host;
show slave status\G;

 

 

 

시나리오를 처음 상태로 원상복구하려면 mysql-slave2(172.30.1.13) 드래그하여 mysql-master(172.30.1.97) 드롭 한다.

 

 

Relocate 대한 변경 사항에 대해서 경고 팝업이 나타나고 OK 클릭하면 변경 사항이 적용된다.

 

 

클러스터 토폴로지 구조가 변경된 것을 확인할 있다.

 

 

mysql-slave2(172.30.1.13) 서버에 접속하여 호스트 이름 복제 상태를 조회해 보면, 현재 mysql-master(172.30.1.97) 서버가 마스터 호스트로 연결되어 있는 것을 확인할 있다.

 

 

시나리오2. Master Refactoring (Promote)

슬레이브 서버를 마스터로 승격시키고, 기존의 마스터를 슬레이브로 역할을 전환한다.

 

 

mysql-slave1(172.30.1.10) 드래그 하여 mysql-master(172.30.1.97) 드롭 한다. 이때 드롭하는 위치를 현재 마스터보다 조금 앞으로 위치하여 PROMOTE AS MASTER 표시될 드롭 한다. (마스터 보다 뒤에 경우 relocate 표시된다.)

 

 

Takeover 대한 경고 팝업이 나타나고 OK 클릭하면 변경 사항이 적용된다.

 

 

변경사항을 적용하였을 , 대시보드 화면이 바로 갱신이 안되는 경우가 있다. 이때 새로고침을 하면 변경사항이 정상적으로 나타난다. 현재 구성되어 있는 토폴로지 상태를 보면, mysql-slave1(172.30.1.10) 서버가 master 역할을 하며, mysql-master(172.30.1.97)서버가 slave 역할을 하는 것으로 역할 체인지가 것을 확인할 있다. 하지만 mysql-master(172.30.1.97) 서버를 보면 역할 변경 새로운 마스터에 대한 복제 구성이 되지 않아서 오류로 표시되고 있다.

 

 

복제 시작을 위해 slave역할의 mysql-master(172.30.1.97) 서버에서 메뉴 아이콘을 클릭하면 설정 팝업이 나타나는데, [Start replication] 클릭한다.

 

 

복제가 정상적으로 구성되고 모든 클러스터가 정상으로 표시되는 것을 확인할 있다.

 

 

다음 실습을 준비하기 위해 원래 클러스터 토폴로지 구조로 원상복구를 한다. 원상복구 방법은 앞에서 했던 방법과 동일하게 진행한다.

 

 

시나리오3. Master – Master Refactoring

슬레이브 서버중 하나를 마스터로 승격시켜, 대의 마스터로 구성한다.

 

 

mysql-master(172.30.1.97) 서버를 드래그 하여 mysql-slave1(172.30.1.10) 드롭 한다. 이때 MAKE CO MASTER 표시되면 드롭 한다.

 

 

make-co-master 대한 경고 팝업이 나타나고 OK 클릭하면 변경 사항이 적용된다.

 

 

실행이 완료되면 클러스터 토폴로지 구성이 master – master 변경된 것을 확인할 있다.

 

정확히는 master-master 구조만 만들어진 상황으로, 완벽한 master-master 작동을 하는 것은 아니다. 환경은 구성되었지만 mysql-slave(172.30.1.10) 서버는 현재 Read Only 상태이다. [Set writeable] 클릭하여 쓰기 상태로 변경해야 사용가능한 master-master 환경이 된다.

 

 

master-master 구조에서 master-salve 원복하기 위해서는 mysql-master(172.30.1.97) 서버에서 설정 아이콘을 클릭한다. 팝업이 나타나면 Master 항목에서 [Reset replica] 클릭한다.

 

 

이전과 같은 상태로 클러스터 토폴로지가 구성된 것을 확인할 있다.

 

 

시나리오4. Master Failover

기존의 운영중이던 마스터 서버를 강제로 중지하여 장애 상황을 구현한 다음, 현재 슬레이브로 운영되던 서버중 하나가 새로운 마스터가 되어 장애조치 된다. 그리고 나머지 슬레이브 서버는 새로운 마스터 서버와 복제 싱크를 한다.

 

 

현재 master역할을 하고 있는 mysql-master(172.30.1.97) 서버에 접속하여 MySQL 서비스를 강제로 중지한다.

hostname
 
sudo systemctl stop mysql

 

 

 

MySQL 서비스가 중지되고 오케스트레이터가 장애 상황을 인지하면 아래와 같이 대시보드 상태가 변경된다. 파란색 숫자3 현재 구성되어 있는 클러스터 서버 개수이며, 빨간색 숫자2 슬레이브 개수이며 복제 불가능을 표시하고 있다. 검은색 1 응답 없는 서버 개수를 나타낸다.

 

 

해당 클러스터를 클릭해서 상세 대시보드를 살펴보면, 현재 mysql-master(172.30.1.97) 서버가 응답 없음을 나타내고 있으며, 슬레이브는 복제가 되지 않는 것을 확인할 있다. 우측의 느낌표를 클릭하면 노드의 에러 메시지를 상세하게 확인할 있다.

 

오류 메시지는 오케스트레이터의 로그 파일에서도 확인할 있다. 특별히 경로를 변경하지 않았으면 아래 경로에서 확인 가능하다. 만약 경로 확인이 필요하면 설정 파일에서 확인할 있다.

/tmp/recovery.log

 

 

[수동 Failover]

오케스트레이터의 장애조치 모드 기본값은 수동이다. 수동으로 장애조치는 관리자가 문제를 인지하고 직접 장애 조치를 진행해야 한다. 현재 응답이 없는 mysql-master(172.30.1.97) 서버에서 [Recover] 클릭한다. Failover 가능한 서버 목록에서, mysql-salve1(172.30.1.10)서버로 장애조치를 진행한다.

 

 

장애조치를 실행하게 되면, 응답 없는 서버를 기존의 클러스터에 분리된다. 기존 서비스가 중지된 mysql-master 노드는 기존 클러스터에서 분리된 것을 확인할 있다.

 

 

클러스터를 클릭하여 상세 내용을 보면 mysql-slave1(172.30.1.10) 서버가 master역할을 하고, mysql-slave2(172.30.1.13) 서버가 slave역할을 하는 것을 확인할 있다.

 

 

mysql-master(172.30.1.97) 서버를 복구했다는 가정하에 mysql-master(172.30.1.97) 서비스를 다시 시작한다.

 

 

MySQL 서버가 정상적으로 실행되었으며 현재는 독립적으로 실행되고 있기 때문에, 기존의 복제 클러스터에 조인하기 위해서는 현재 master 역할로 운영중인 mysql-slave1(172.30.1.10) 서버로 복제를 연결한다.

 

 

복제가 성공적으로 연결되고, 복제 싱크가 완료되면 오케스트레이터 대시보드에서도 클러스터가 정상적으로 연결되어 운영되는 것을 확인할 있다.

 

 

[자동 failover]

기본 모드가 수종이기 때문에, 자동으로 장애조치를 하기 위해서는 파라메터 수정이 필요하다. 아래 경로의 설정파일에서 수정할 있다. 설정 파라메터의 종류가 많기 때문에 주의해서 살펴본다.

vi /usr/local/orchestrator/orchestrator.conf.json

 

 

변경전 변경후
"RecoveryPeriodBlockSeconds": 3600,
  "RecoveryIgnoreHostnameFilters": [],
  "RecoverMasterClusterFilters": [
    "_master_pattern_"
  ],
  "RecoverIntermediateMasterClusterFilters": [
    "_intermediate_master_pattern_"
  ],
"RecoveryPeriodBlockSeconds": 10,
  "RecoveryIgnoreHostnameFilters": [],
  "RecoverMasterClusterFilters": [
    "*"
  ],
  "RecoverIntermediateMasterClusterFilters": [
    "*"
  ],

 

 

설정 변경을 완료하였으면, 오케스트레이터는 설정 파일에 대한 변경사항을 런타임중에 적용할 있다. 대시보드 상단 메뉴에서 [Home] - [Status] 클릭한다. Status페이지에서 [Reload congifuration] 클릭하여 설정을 로드 한다.

 

 

자동 장애조치 테스트를 위해 현재 master 운영중인 서버의 MySQL 강제로 중지한다.

 

 

Master서버가 중지되고 오케스트레이터에서 장애 상황을 인지한 다음 자동으로 장애조치가 완료되면 대시보드에서 아래와 같이 개의 클러스터를 있다. 장애가 발생한 서버는 기존 클러스터에서 분리가 되었다.

 

 

클러스터를 클릭해서 상태를 살펴보면, 기존의 slave 서버중에 하나가 master 승격되고, 나머지 슬레이브가 새로운 마스터에 조인되어 싱크되는 것을 확인할 있다.

 

분리된 장애 서버에서는 Recover 클릭해도 클러스터에서 분리된 상태이기 때문에 failover 있는 서버 목록을 찾을 없다고 표시된다.

 

 

지금까지 MySQL HA환경에서 오케스터레이터를 활용하여 토포로지 리팩토링 장애조치 하는 방법에 대해서 살펴보았다. 장애조치를 진행할 운영중인 서버중 하나가 랜덤하게(정확히는 씽크가 가장 빠른 서버) 마스터로 승격되는 것을 확인하였는데, 또한 파라메터를 수정하여 다양한 조건을 부여하여 우선순위조건을 조정할 있다. 앞애서도 살펴보았듯이 파라메터의 종류가 매우 많다. 상세한 내용은 다른 포스트에서 하나씩 살펴볼 예정이다.

 

 

 

[참고자료]

l   Orchestrator : https://github.com/openark/orchestrator

l   Failure detection : https://github.com/openark/orchestrator/blob/master/docs/failure-detection.md

l   MySQL Orchestrator - HA(High Availability) - 2 - 리팩토링 Failover Automated Recovery : https://hoing.io/archives/91

l   Configuration: failure detection : https://github.com/openark/orchestrator/blob/master/docs/configuration-failure-detection.md

l   https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case : https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case

l   Orchestrator: MySQL Replication Topology Manager : https://www.percona.com/blog/orchestrator-mysql-replication-topology-manager/

l   Orchestrator and ProxySQL : https://www.percona.com/blog/orchestrator-and-proxysql/

 

 

 

2023-07-27 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, MySQL복제 오케스트레이션, MySQL복제설치, MySQL MHA, MySQL Orchestrator, MySQL장애조치, MySQL고가용성

 

MySQL 고가용성 운영을 위한 Orchestrator 설치

 

l  Version : MySQL, Orchestrator

 

데이터베이스를 운영할 단일 장애 포인트(SPOF, Single Point Of Failure) 예방하기 위해서 고가용성(HA, High-Available) 매우 중요한 부분이다. 그렇기 때문에 클러스터링, 복제 고가용성으로 구성된 다수의 데이터베이스를 효율적으로 관리하기 위해 다양한 솔루션들이 존재한다.  

이번 포스트는 MySQL MariaDB 고가용성을 효율적으로 운영하기 위한 솔루션인 Orchestrator(이하 오케스트레이터’) 대해서 알아본다. 오케스트레이터는 MySQL 고가용성 복제 관리 도구로, 서비스로 실행되며 명령줄 액세스, HTTP API 인터페이스를 제공한다.

 

l  Orchestrator : https://github.com/openark/orchestrator

 

오케스트레이터의 주요 기능으로는 아래와 같다.

l  Discovery : 토폴로지를 통해 지속적으로 클러스터의 복제 상태 구성과 같은 기본 MySQL 정보를 읽어 오케스트레이션에 매핑한다. 장애가 발생하였을 복제 문제를 포함하여 토폴로지의 자세한 정보를 시각화로 제공한다.

l  Refactoring : 복제 서버의 Binlog file:position, GTID, Pseudo GTID 다양한 정보를 수집한다. 복제 토폴로지에서 리팩토링은 GUI에서 복제본을 다른 마스터 아래로 끌어서 놓는 방식으로 안전하게 진행할 있다. 오케스트레이터는 비정상적인 리팩토링 시도를 거부하며 다양한 명령줄 옵션을 통해 세밀한 제어가 가능하다.

l  Recovery : 전체적인 접근 방식을 사용하여 마스터 중간 마스터 오류를 감지한다. 토폴로지 자체에서 얻은 정보를 기반으로 다양한 장애 시나리오를 인식한다. 장애가 인식되면 자동 또는 수동 복구를 수행하도록 구성할 있다. 복구 프로세스는 오케스트레이터의 토폴로지 이해와 리팩토링 수행 능력을 활용하며, 구성이 아닌 상태를 기반으로 한다. 오케스트레이터는 자체 복구시 토폴로지를 조사/평가하여 최상의 복구 방법을 선택한다.

 

오케스트레이터는 다양한 인터페이스를 지원하는데 명령줄 인터페이스(CLI, Command Line Interface) 활용하여 디버그 메시지 등을 수집하고 자동화된 스크립팅을 제어할 있다. 외에도 다양한 Web API (HTTP Get access) 인터페이스를 제공한다.

l  Highly available

l  Controlled master takeovers

l  Manual failovers

l  Failover auditing

l  Audited operations

l  Pseudo-GTID

l  Datacenter/physical location awareness

l  MySQL-Pool association

l  HTTP security/authentication methods

 

 

 

Orchestrator 사용하려면 우선 MySQL복제 환경이 구성되어 있어야 한다. 오케스트레이터 구성에서 GTID 사용을 권장하고 있으나, Binlog position 사용시 Pseudo GTID 사용하면 된다. MySQL 복제 구성은 아래 링크를 참고한다.

l  ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

 

오케스트레이터를 구성할 서버 정보들이다. 링크에서 구성한 환경을 기반으로 진행한다.

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
proxy-orchestrator 172.30.1.24 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33, container.d

 

proxy-orcherator서버에는 오케스트레이터가 설치되어 운영된다. 오케스트레이터에서 필요한 정보를 저장하기 위해 오케스트레이터용 리포지토리 MySQL 설치한다.

sudo apt-get install mysql-server

 

MySQL설치가 완료되면 오케스트레이터가 사용할 계정 데이터 베이스를 생성한다. 테스트 용도이기 때문에 편의상 전체 권한을 부여한다. 실제 업무에서 사용할 때에는 보안 정책에 따라 오케스트레이터에 필요한 권한만 할당한다.

--데이터베이스 생성
CREATE DATABASE IF NOT EXISTS orchestrator;
 
--사용자 생성
CREATE USER 'orchestrator'@'%' IDENTIFIED BY 'orchestrator';
--GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'%';
--GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'%';
 
GRANT ALL PRIVILEGES ON *.* TO 'orchestrator'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

 

이번 포스트에서는 컨테이너로 빌드된 오케스트레이터를 설치하기 때문에, 컨테이너를 실행할 있는 도커 패키지를 설치한다.

--시스템 패키지 업데이트
sudo apt-get update
 
--필요한 패키지 설치
sudo apt-get install apt-transport-https ca-certificates curl gnupg-agent software-properties-common
 
--Docker 공식 GPG키를 추가
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
 
--Docker 공식 apt 저장소를 추가
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
 
--시스템 패키지 업데이트
sudo apt-get update
 
--Docker 설치
sudo apt-get install docker-ce docker-ce-cli containerd.io
 
--Docker 서비스 실행 확인
sudo systemctl status docker

 

 

 

도커 설치가 완료되었으면, 도커 허브에서 오케스트레이터 컨테이너를 다운로드 한다.

ü  https://hub.docker.com/r/openarkcode/orchestrator

 

 

아래 스크립트를 실행하면 도커 이미지가 다운로드 되며, 현재 호스트에 저장되어 있는 도커 이미지를 확인할 있다.

sudo docker pull openarkcode/orchestrator
sudo docker images

 

 

 

아래 스크립트를 사용하여 도커를 실행한다.

sudo docker run -d --name orchestrator -p 3000:3000 openarkcode/orchestrator

 

 

 

오케스트레이터 서비스가 정상적으로 실행되었으면, 브라우저를 사용하여 오케스트레이터 대시보드로 접속한다.

http://172.30.1.24:3000/

 

 

상단의 [Cluster] – [Discover]에서 MySQL 클러스터를 등록할 있다. 기본값으로 호스트 이름으로 등록하게 되어 있다. 호스트 이름으로 MySQL 서버들을 검색하려면 proxy-orchstrator (오케스트레이터의 호스트 서버) 서버의 /etc/hosts 파일에 호스트 정보를 등록한다.

 

 

서버 이름이 아닌 서버IP 클러스터 서버를 찾기 위해서는 아래와 같이 설정 파일을 수정하여 사용할 있다. 기본적으로 설정 파일은 아래 경로에 있다.

/usr/local/orchestrator/

 

컨테이너 환경에서는 컨테이너 내부에서 파일 수정을 하는 것은 좋은 방법은 아니지만 실습 편의상 컨테이너 내부 쉘로 접근해서 수정하였다. 실제 서비스 환경에서는 컨테이너 특성상 데이터가 휘발되기 때문에 반드시 설정파일 따로 보관할 있도록 한다. 아래 스크립트는 실행 중인 컨테이너의 ID 확인하고, 해당 컨테이너 쉘로 접근한다.

sudo docker ps -a
sudo docker exec -it 93c5bfafa537 /bin/bash

 

 

 

설정파일이 위치한 경로로 이동하여 설정 파일을 수정한다.

cd /usr/local/orchestrator/
sudo cp orchestrator-sample.conf.json orchestrator.conf.json
vi orchestrator.conf.json

 

 

오케스트레이터 서버가 MySQL 클러스터 서버들에게 접속할 있는 계정과 비밀번호를 입력한다. 사용자 환경에 맞게 수정하여 사용한다.

"MySQLTopologyUser": "orc_client_user",
"MySQLTopologyPassword": "orc_client_password",

 

오케스트레이터 서버가 orchester 데이터베이스 접속할 있는 계정과 비밀번호를 입력한다. 오케스트레이터용 리포지토리 MySQL 설치 단계에서 생성한 계정을 입력하도록 한다.

"MySQLOrchestratorHost": "127.0.0.1",
"MySQLOrchestratorPort": 3306,
"MySQLOrchestratorDatabase": "orchestrator",
"MySQLOrchestratorUser": " orchestrator ",
"MySQLOrchestratorPassword": " orchestrator ",

 

 

호스트 이름 대신 IP 검색할 있도록 옵션을 수정한다.

변경 "HostnameResolveMethod": "default",
"MySQLHostnameResolveMethod": "@@hostname",
변경 "HostnameResolveMethod": "none",
"MySQLHostnameResolveMethod": "",

 

GTID 사용하지 않고 binlog position 사용할 경우 failover 위해서 PeseudoGTID 활성화 하기위해 아래 파리메터를 입력한다. 해당 파라메터는 설정 파일에 없으므로 직접 추가해야 한다.

"AutoPseudoGTID": true,

 

 

각종 로그파일은 아래 경로에서 확인할 있다. 필요시 사용자 경로에 맞게 수정하여 사용할 있다.

"OnFailureDetectionProcesses": [
"echo 'Detected {failureType} on {failureCluster}. Affected replicas: {countSlaves}' >> /tmp/recovery.log"
  ],
"PreGracefulTakeoverProcesses": [
"echo 'Planned takeover about to take place on {failureCluster}. Master will switch to read_only' >> /tmp/recovery.log"
  ],
"PreFailoverProcesses": [
"echo 'Will recover from {failureType} on {failureCluster}' >> /tmp/recovery.log"
  ],
"PostFailoverProcesses": [
"echo '(for all types) Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
"PostUnsuccessfulFailoverProcesses": [],
"PostMasterFailoverProcesses": [
"echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Promoted: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
"PostIntermediateMasterFailoverProcesses": [
"echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
"PostGracefulTakeoverProcesses": [
"echo 'Planned takeover complete' >> /tmp/recovery.log"
  ],

 

설정값 변경이 완료되었으면 해당 사항을 저장하고 오케스트레이터 서비스를 재시작한다. 오케스트레이터 대시보드에 접속하여 IP Discover 등록할 있다. 서버는 master 역할 서버만 입력한다.

 

 

정상적으로 등록이 완료되면 아래와 같이 등록된 서버가 나타난다.

 

 

해당 서버를 클릭하면 현재 구성된 토폴로지 상태를 나타내는 대시보드를 있다,

 

 

대시보드에 나타난 서버들을 클릭해보면 현재 상태의 상세 정보를 확인할 있으며, 일부 제어를 있는 메뉴가 나타난다.

 

 

 

 

[참고자료]

l  Orchestrator : https://github.com/openark/orchestrator

l  MySQL Orchestrator - HA(High Availability) - 1 - 기능 설명 설치 : https://hoing.io/archives/72

l  https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case : https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case

l  Orchestrator: MySQL Replication Topology Manager : https://www.percona.com/blog/orchestrator-mysql-replication-topology-manager/

l  Orchestrator and ProxySQL : https://www.percona.com/blog/orchestrator-and-proxysql/

 

 

 

 

2023-07-25 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, MySQL복제 오케스트레이션, MySQL복제설치, MySQL MHA, MySQL Orchestrator

ProxySQL Internals 시스템 구성 둘러보기

 

l  Version : ProxySQL

 

이번 포스트에서는 ProxySQL 인터널 구조와 설정 파일의 구조에 대해서 살펴본다. 상세한 인터널 구조를 모두 설명하지는 않으며 전체적인 구조 특징만 소개하도록 한다. 세부적인 내용은 다른 포스트에서 하나씩 다뤄볼 예정이다.

 

ProxySQL 시작되면 즉시 프로세스를 생성한다. 상위 프로세스는 엔젤 프로세스(와치독과 비슷한 의미로 해석됨) 작동하고 ProxySQL 크래시 되었을 밀리초 내에 서비스를 다시 시작한다. MySQL 비유하면 mysqld_safe 매우 유사하다.

ProxySQL에는 기본적으로 개의 포트가 필요하다. 트래픽을 수신하는 6033 포트와 ProxySQL 관리하기 위한 게이트웨이 역할을 하는 6032포트이다. 명령줄 관리 인터페이스(CLI) MySQL 클라이언트에서 액세스할 있다. 대부분의 구성은 SQL 사용하여 수행된다. ProxySQL 구성을 저장하는 사용하는 기본 데이터베이스는 SQLite이지만 가능한 MySQL 가까운 환경을 만들기 위해 노력한 흔적을 있다. 예로 일부 MySQL 구문을 사용하여 ProxySQL 구성을 확인하거나(SHOW GLOBAL VARIABLES) 구성을 설정 (SET GLOBAL variable_name = value) 있다. 백엔드 구성은 테이블에 저장된다. 모든 변경 사항은 SQL(INSERT, UPDATE, DELETE) 통해 이루어진다.

 

 

l  Query processor/rules engine : ProxySQL MySQL 프로토콜(쿼리) 해석한다. 규칙 엔진은 들어오는 트래픽을 일치시키고 쿼리를 캐시할지 또는 호스트 그룹 대상에 쿼리를 차단, 경로 재지정, 쿼리 재작성 또는 미러링 할지 여부를 정의한다.

l  User authentication : 기본 데이터베이스에 대한 사용자 자격 증명이 해시되어 프록시에 저장된다.

l  Hostgroup manager : 트래픽을 보낼 서버 그룹을 관리하고 해당 상태를 추적한다.

l  Connection pool : 백엔드 데이터베이스에 대한 연결을 관리한다. 연결 풀은 백엔드를 향해 설정되며 모든 애플리케이션에서 공유/재사용된다.

l  Monitoring : 백엔드를 모니터링하고 지표를 수집한다. 모니터링을 하면서 응답하지 않는 호스트 또는 복제 지연에 대해 필요에 따라 트래픽을 제어한다.

 

Query Processor SQL 트래픽을 분석하고 이해할 있으므로 광범위한 라우팅을 수행할 있다. 스키마, 사용자, 호스트, 포트 또는 정규 표현식과 같은 다양한 속성으로 쿼리를 일치시킬 있다. 이렇게 하면 변경하려는 쿼리와 관련하여 상당한 유연성을 얻을 있다. 예를들어 쿼리캐시는 쿼리 대기 시간을 줄일 있도록 정의된 시간(TTL) 동안 캐시할 있다. 이러한 기능은 일반적으로 응용 프로그램에 추가 복잡성을 도입하는 외부 캐싱 계층의 필요성을 제거할 수도 있다. 또한 MySQL 쿼리 캐시(결국 8.0에서 제거됨)보다 훨씬 나은 솔루션일 수도 있다. 그리고 사용자 정의에 따라 쿼리를 제한할 수도 있다. 예를들어 성능에 영향을 미치는 무거운 쿼리들을 초당 또는 분당 번만 실행되도록 커스텀하게 설정할 있다. 쿼리 미러링기능도 있지만 아직은 모든 트래픽이 미러되는 것은 아니기 때문에 기대를 정도는 아니다. 그러나 특정 상황에서 매우 유용하게 활용하 있다. 주어진 쿼리에 대해 프로덕션 호스트뿐만 아니라 별도의 위치로 보내서 슬로우 로그에서 캡처하거나 일부 디버깅을 수행할 있다. 쿼리 재작성을 통해 쿼리를 즉석에서 재작성할 있다. 간단한 FORCE INDEX 문제를 해결할 있지만 수정, 테스트, 코드 등의 시간 소모적인 프로세스를 거치지 않고는 문제를 추가할 없는 상황에서, ProxySQL에서 쿼리를 다시 작성하면서 인덱스 힌트 등을 추가할 있다.

 

ProxySQL 매우 복잡한 구조를 가진것 처럼 보이지만, 시스템을 구성하고 사용하는데 있어서는 4개의 계층(Runtime, Memory, Disk, Configuration file) 제공하여 쉽게 사용할 있다. 특징으로는 설정에 대해서 즉시 업데이트가 되고, 데몬의 재시작 없이 적용할 있다. 또한 잘못 구성되었을 경우에도 쉽게 롤백 있다.

 

RUNTIME

Runtime ProxySQL 기동 운영에 사용된다. 요청을 처리하는 스레드에서 사용하는 ProxySQL 메모리 데이터 구조를 나타낸다. 여기에는 사용된 전역 변수의 , 호스트 그룹으로 그룹화된 백엔드 서버 목록 또는 프록시에 연결할 있는 MySQL 사용자 목록이 포함된다. 운영자는 RUNTIME 구성 섹션의 내용을 직접 수정할 없다. 하위 레이어를 수정하고 내용을 적용시킬 있다.

 

MEMORY

MEMORY(메인이라고도 ) MySQL 호환 인터페이스를 통해 외부에 노출되는 메모리 SQLite3 데이터베이스를 나타낸다. 사용자는 인터페이스에 MySQL 클라이언트를 연결하고 다른 테이블과 데이터베이스를 쿼리할 있다. 인터페이스를 통해 사용할 있는 구성 테이블은 다음과 같다.

l   mysql_servers : ProxySQL에서 중계할 서버들의 목록을 관리한다. 중계 대상 서버를 백엔드 서버라고 부른다.

l   mysql_users : ProxySQL 연결할 있는 사용자 자격 증명 목록으로, ProxySQL 이러한 자격 증명을 사용하여 백엔드 서버에도 연결한다.

l   mysql_query_rules : 다른 백엔드 서버로 트래픽을 라우팅하기 위한 규칙 목록으로 이러한 규칙으로 인해 쿼리를 다시 작성하거나 결과를 캐싱할 수도 있다.

l   global_variables : 런타임에 조정할 있는 프록시 전체에서 사용되는 전역 변수 목록이다. 아래 그림은 글로벌 전역 변수 예시이다.

 

 

l   mysql_collations : 프록시가 작업할 있는 MySQL 데이터 정렬 목록으로 클라이언트 라이브러리에서 직접 추출된다.

l   debug_levels (디버그 빌드에서만 사용 가능) : ProxySQL 세부 수준과 함께 내보내는 디버그 문의 유형 목록이다. 이를 통해 다양한 문제를 디버깅하기 위해 로그에 어떤 종류의 명령문이 있는지 런타임에 쉽게 구성할 있다. 성능에 영향을 있으므로 디버그 빌드에서만 사용할 있다.

 

DISK

DISK 기본 위치가 $(DATADIR)/proxysql.db 온디스크 SQLite3 데이터베이스를 나타낸다. ProxySQL 자체 데이터베이스가 실제로 동작하는 곳의 이름이 memory 이유는, 실제로 in-memory 형태의 SQLite3 데이터베이스가 상주하고 있기 때문이다. ProxySQL 서비스를 다시 시작하면 유지되지 않은 메모리 구성이 손실되므로 구성을 DISK 유지하는 것이 중요하다.

 

CONFIG

CONFIG 파일은 전형적인 구성 파일이다. Memory에서 운영중인 설정 파일을 덤프할 있다. 서비스를 시작하는 동안 ProxySQL 구성 파일(있는 경우) 읽어, 지정된 경로에서 내부 데이터베이스 파일을 찾으려고 시도한다. 지정된 경로에 데이터베이스 파일을 발견되면 ProxySQL 디스크의 데이터베이스를 사용하여 메모리 구성을 초기화한다. , 디스크 구성이 MEMORY 로드되고 RUNTIME에도 전파된다. 데이터베이스 파일을 찾을 없는 경우(: ProxySQL 처음으로 실행 중이거나 데이터베이스가 삭제된 경우) 구성 파일의 정보가 MEMORY 로드되고 DISK 데이터베이스에 저장되며 RUNTIME에도 전파된다.

l   Initial startup (옵션 –initial) : ProxySQL 처음 시작하는 동안 메모리 런타임 구성이 구성 파일에서 채워지고 모든 구성이 ProxySQL 내부 데이터베이스에 저장된다. –initial 옵션은 SQLite 데이터베이스 파일을 원래 상태(, 구성 파일에 정의된 상태) 재설정하고 기존 SQLite 데이터베이스 파일의 이름을 바꾸는 플래그 함께 proxysql 실행하여 초기 구성을 강제로 다시 발생시킬 있다. 롤백이 필요한 경우 사용한다.

l   Reload startup (옵션 –reload flag) : ProxySQL 바이너리가 --reload플래그와 함께 실행되면 구성 파일의 구성을 데이터베이스 파일의 내용과 병합하려고 시도한다. 그런 다음 새로 병합된 구성을 사용하여 시작된다. 충돌이 있을 ProxySQL 구성 소스를 성공적으로 병합할 것이라는 보장은 없으며 사용자는 항상 병합이 예상대로 실행되었는지 확인해야 한다.

l   Runtime ProxySQL Configuration : 런타임 구성  설정은 ProxySQL(기본적으로 6032) ProxySQL 관리 포트를 통해 수행된다. MySQL 호환 클라이언트로 연결하면 다양한 기본 ProxySQL 구성 통계 테이블을 쿼리하기 위한 표준 MySQL 스타일 인터페이스가 표시된다.

 

 

계층 간에 구성을 이동하기 위해 관리 인터페이스를 통해 사용할 있는 다양한 관리 명령 세트가 있다. 레이어간 이동 명령어은 아래 링크를 참고한다.

l   Moving config between layers : https://github.com/sysown/proxysql/blob/master/doc/configuration_system.md

 

 

[참고자료]

l   Database load balancing for MySQL and MariaDB with ProxySQL : https://severalnines.com/resources/whitepapers/database-load-balancing-for-mysql-and-mariadb-with-proxysql/

l   Multi layer configuration system : https://proxysql.com/documentation/configuring-proxysql/

l   Configuration system : https://github.com/sysown/proxysql/blob/master/doc/configuration_system.md  

 

 

 

 

2023-07-24 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브, ProxySQL설치, MySQL복제설치, 쿼리룰설정 ,ProxySQL Internals, ProxySQL Architecture, ProxySQL 아키텍처, ProxySQL 인터널스

ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기

 

l  Version : ProxySQL, MySQL 8.X

 

ProxySQL MySQL 포크된 데이터베이스(Percona Server MariaDB) 위한 고성능, 고가용성을 제공하는 프록시이다.

l  ProxySQL이란 무엇인가 : https://sungwookkang.com/1528

 

ProxySQL 구성할 MySQL 서버 환경이 복제 구성이 되어 있으면 Write, Read 효과적으로 분산하여, ProxySQL장점을 조금 효율적으로 활용할 있다. MySQL설치, 복제구성, ProxySQL 설치는 아래 링크를 참고한다.

l  ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

 

이번 포스트에서는 ProxySQL + MySQL Replication 환경에서, 쿼리 타입(INSET UPDATE, DELET, SELECT) 따라 Write, Read 분기하여 호출하는 방법에 대해서 알아본다. 실습에 사용된 서버 환경은 아래와 같다.

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33

 

이전의 (https://sungwookkang.com/1529)에서 ProxySQL 구성할 서버를 등록하는 과정에서, 모든 서버를 동일한 그룹으로 지정하였다. 쿼리 타입에 따라 각기 다른 서버로 호출하기 위해서는 서버 특성에 따라 그룹 할당이 필요 하다. 이번 포스트에서는 1대의 Write서버와 2대의 Read서버로 구성하는 그룹으로 구성한다. proxy-sql 서버에서 아래 스크립트를 사용하여 데이터베이스에 접속한다.

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

 

proxy-sql 데이터베이스에 접근이 되었으면, 아래 스크립트를 사용하여, write read 서버를 등록한다. 이때 필요한 그룹을 지정할 있도록 한다.

l  Write : hostgroup_id = 1

l  Read : hostgroup_id = 2

--기존 서버 목록 삭제
DELETE FROM mysql_servers;
 
--master (write) 추가
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.97', 3306);
 
--slave (read) 추가
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.30.1.10', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.30.1.13', 3306);
 
--추가된 서버 확인
select * from mysql_servers;
 
-- 변경사항 반영
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

 

 

사용자 기본 호스트 그룹을 지정한다. hostgroup_id = 1 기본으로 지정하였다.

-- hostgroup_id 기본 1 지정
UPDATE mysql_users SET default_hostgroup = 1;
 
-- 변경사항 반영
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

 

 

쿼리 타입에 따라 그룹으로 분기할 룰을 지정한다. SELECT쿼리는 hostgroup_id = 2 서버로 호출하고, 나머지 쿼리는 hostgroup_id = 1 서버로 호출한다. 이때 동일 호스트 그룹에 2 이상의 서버가 있으면 ProxySQL에서 라운드로빈으로 로드밸런싱을 한다.

-- 기존 쿼리 삭제
DELETE FROM mysql_query_rules;
 
-- SELECT 시작하는 쿼리는 hostgroup=2(slave) 라우팅
INSERT INTO mysql_query_rules (rule_id, active, apply, match_digest, destination_hostgroup) VALUES (1, 1, 1, '^SELECT', 2);
 
-- 변경사항 반영
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

 

 

 

쿼리 설정이 완료되었으면, 쿼리를 실행하고 정상적으로 분기되는지 확인이 필요하다. 전에 서버에 general_log 활성화하여, 서버로 호출되는 쿼리를 기록할 있도록 한다. 아래 스크립트는 로그를 테이블 파일에 동시에 기록한다.

mysql-master mysql-slave1 mysql-slave2
set global log_output = 'TABLE,FILE';
set global general_log = on;
show variables like '%general%';
show variables LIKE '%log_output%';

 

 

 

모든 설정이 완료되었으면, 클라이언트 도구(ex : MySQL Workbench) 사용하여, proxy-sql 서버에 접속한 다음, INSERT SELECT 쿼리를 호출 한다. 여러번 호출하여 라운드로빈이 발생하였을 서버로 유입 있도록 한다.

proxy-sql
#proxy-sql
select * from testdb.tbl_a;
 
insert into testdb.tbl_a (name) values ('insert test for proxy5');
 
select * from testdb.tbl_a;

 

 

 

mysql-master 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 있다.

mysql-master
#mysql-mster
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

 

 

 

mysql-slave1 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 있다.

mysql-slave1
#mysql-slave1
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

 

 

 

mysql-slave2 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 있다.

mysql-slave2
#mysql-slave2
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

 

 

 

지금까지 ProxySQL에서 쿼리 타입에 따른 룰을 지정하여, Write Read 분기하는 방법에 대해서 알아보았다. 실제 현업에 사용할 때에는 로그 기록에 따른 오버헤드가 발생할 있으므로 사용자 환경에 따라 제네럴로그 기록을 OFF 또는 적절히 조절할 있도록 한다. 대용량 트래픽 환경에서 Write Read 쿼리를 분기 분산함으로써 DB 서버의 오버헤드를 분산하여 유용하게 활용할 있으리라 생각한다.

 

 

2023-07-23 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브, ProxySQL설치, MySQL복제설치, 쿼리룰설정

ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에)

 

l  Version : ProxySQL, MySQL 8.X

 

ProxySQL MySQL 포크된 데이터베이스(Percona Server MariaDB) 위한 고성능, 고가용성을 제공하는 프록시이다.

l  ProxySQL이란 무엇인가 : https://sungwookkang.com/1528

 

이번 포스트는 ProxySQL 구성하는 방법에 대해서 다뤄본다. 이번 실습에서 구성되는 ProxySQL 아키텍처는 아래 그림과 같다. 모든 클라이언트 연결은 ProxySQL 연결이 되고, ProxySQL에서는 쿼리 타입에 따라 Write Read 분산하여 요청한다. MySQL 구성은 Master에서 Slave1, Slave2 GTID 사용하여 복제를 구성한다. DB 클러스터에 보면 Hostgroup라는 것을 있는데, 이는 ProxySQL에서 Failover 감지하여 역할을 변경할 , 그룹단위로 이동할 있도록 MySQL 역할에 따라 그룹을 만들었다.

 

GTID
Global Transaction Identifier 약자로 MySQL 복제에서 서버의 트랜잭션을 구분하는 고유한 식별자이다. GTID 모든 트랜잭션과 1:1 관계이며, GTID 활용하면 복제본으로 장애 조치, 계층적 복제, 특정 시점으로 백업 복구하는 등의 작업을 쉽게 구현할 있으며, 오류 발생 빈도도 줄일 있다.

 

 

ProxySQL 구현하기 위해서는 MySQL 복제 환경이 필요하다. MySQL 설치부터 복제 구성, ProxySQL 구성까지 따라할 있도록 문서를 작성하였다.

 

[시스템 구성]

실습 환경을 만들기 위해서는 전체 4대의 서버가 필요 하다. 시스템 사양은 고사양일 필요는 없지만, 너무 낮은 사양의 경우 서비스가 정상적으로 실행되지 않을 있으므로 적절히 선택하도록 한다.

l  Virtual Box

l  CPU : 2 Core

l  Memory : 4 GB

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33

 

OS 패키지 버전 정보를 업데이트를 진행한 다음 패키지 업그레이드를 진행한다. 그리고 실습을 원활히 있도록 방화벽은 중지하도록 한다. 실제 업무 환경에서는 보안 정책에 따라 방화벽 설정을 있도록 한다.

--package update and upgrade
sudo apt-get update
sudo apt-get upgrade
 
--disable firewall
sudo ufw disable

 

 

[MySQL 설치]

MySQL 설치를 진행한다. 편의상 패키지로 설치를 진행하였다. 실제 업무에서는 필요한 버전을 선택하여 설치할 있도록 한다.

mysql-master mysql-slave1 mysql-slave2
sudo apt-get install mysql-server sudo apt-get install mysql-server sudo apt-get install mysql-server

 

MySQL 설치가 완료되었으면 외부에서 mysql 접속할 있도록 my.cnf 수정해야 한다. 일반적으로 my.cnf파일은 아래와 같은 위치에 있지만, 사용자 환경에 따라 커스텀한 경로에 저장된 경우도 있다.

l   /etc/mysql/my.cnf

l   /etc/mysql/mysql.conf.d/mysqld.cnf

 

my.cnf 파일을 오픈하여, bind-address 부분을 주석 처리한다. 부분은 로컬 호스트(localhost, 127.0.0.1)에서 들어오는 접속을 바인딩한다는 뜻으로 해당 설정 때문에 외부에서 접근을 하지 못한다.

sudo vi /etc/mysql/my.cnf
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

 

 

bind-address 대한 주석 처리가 완료되었으면 mysql 서비스를 재시작해서 변경 사항을 적용할 있도록 한다.

sudo service mysql restart

 

 

실습에서 사용할 mysql 사용자 계정을 생성한다.

sudo mysql

 

mysql >create user mysqluser@'%' identified by 'yourpassword' ;
mysql >grant all privileges on *.* to mysqluser@'%' with grant option;
mysql >flush privileges;

 

MySQL워크 벤치 등을 사용하여 외부에서 생성한 계정으로 접근이 되는지 확인한다.

 

[MySQL 복제 구성]

MySQL복제를 구성하기 위해서는 master서버와 Slave서버에 파라미터 설정을 진행해야 한다. 파라미터 설정에서 servier-id 값이 중복되지 않도록 주의한다. my.cnf 파일을 오픈하여 아래 내용을 추가한다.

mysql-master mysql-slave1 mysql-slave2
server-id=1
log-bin=binlog
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
server-id=2
log-bin=binlog
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON
server-id=3
log-bin=binlog
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON

 

 

파라메터 수정이 완료되었으면, my.cnf파일을 저장하고, 모든 서버의 mysql서비스를 재시작하여 gtid_mode ON인지 확인한다.

sudo service mysql restart

 

show variables like '%gtid_mode%'

 

 

mysql-master 접속하여 테스트용 데이터베이스를 생성한다.

mysql-master
create database testdb default character set utf8mb4;
show databases;

 

 

mysql-master에서 데이터베이스 백업을 진행한다.  mysqldump 명령을 사용하였고, 복제 구성을 위한 백업이므로 필요한 옵션을 추가하였다. 아래 스크립트에서 백업 경로는 적절하게 수정하여 사용할 있도록 한다.

mysql-master
mysqldump -u mysqluser -p -v --databases testdb --quick --single-transaction --routines --set-gtid-purged=ON --triggers --extended-insert --master-data=2 > /home/dev/db_backup/testdb.sql

 

백업이 완료되었으면 슬레이브를 구성할 서버에 백업 파일을 복사한다. 파일을 복사하는 방법은 다양하게 있으며 이번 실습에서는 scp명령을 사용하였다.

mysql-master
--copy mysql-master to myslq-slave1
sudo scp testdb.sql dev@172.30.1.10:/home/dev/db_backup
 
--copy mysql-master to mysql-slave2
sudo scp testdb.sql dev@172.30.1.13:/home/dev/db_backup

 

 

mysql-slave1, mysql-slave2 서버에서 데이터베이스를 복원한다. 아래 스크립트로 복원할 있다.

mysql-slave1 mysql-slave2
mysql -u mysqluser -p testdb < testdb.sql mysql -u mysqluser -p testdb < testdb.sql

 

복원 과정에서 아래와 같은 오류 메시지가 발생한다면, mysql 접속하여 reset master 명령을 실행하여 해결할 있다. 오류는 서버마다 uuid gtid 생성하는데 master 것을 가져와서 slave 반영하려 했기 때문에 발생한 것이다.

 

mysql> reset master;

 

복원이 정상적으로 되었는지 mysql-slave1, mysql-slave2 서버에 접속하여 확인한다. 그리고 슬레이브 서버에서 마스터 서버로 복제 연결을 진행한다.

mysql-slave1 mysql-slave2
change master to master_host = '172.30.1.97',
master_user='mysqluser', master_password=’password',
master_auto_position =1;
 
start slave;
change master to master_host = '172.30.1.97',
master_user='mysqluser', master_password=’password',
master_auto_position =1;
 
start slave;

 

아래 스크립트를 사용하여 정상적으로 복제가 연결되었지 확인한다.

mysql-slave1 mysql-slave2
show slave status; show slave status;

 

 

 

mysql-master 데이터를 입력하면 mysql-slave1, mysql-slave2 데이터 변경이 정상적으로 반영되는 것을 확인할 있다.

 

[ProxySQL 설치]

ProxySQL 깃헙 저장소에서 ProxySQL 파일을 다운로드한다. 아래 스크립트를 실행하면 다운로드 자동으로 설치가 진행된다.

sudo wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb
 
sudo dpkg -i proxysql_2.2.0-ubuntu20_amd64.deb

 

 

 

설치가 완료되었으면, 현재 설치되어 있는 ProxySQL 버전을 확인한다.

proxysql --version

 

 

 

ProxySQL 서비스를 실행 종료하기 위해서는 아래 스크립트를 사용한다.

--실행
sudo service proxysql start
--종료
sudo service proxysql stop

 

 

ProxySQL 서비스를 초기화는 아래 명령어를 사용할 있다.

sudo service proxysql-initial start

 

ProxySQL 서비스가 시작되었으면, 이제 ProxySQL 접속해서 여러가지 세팅을 진행한다. 우선 ProxySQL 접속하기 위해서는 MySQL Client 필요하다. 사용자 환경에 따라 MySQL 또는 MariaDB 클라이언트를 설치한다.

sudo apt install mysql-client-core-8.0     # version 8.0.33-0ubuntu0.22.04.2
sudo apt install mariadb-client-core-10.6  # version 1:10.6.12-0ubuntu0.22.04.1

 

 

ProxySQL 접속한다. 아래 스크립트를 proxy-sql 서버에서 실행한다.

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='admin> '

 

 

ProxySQL 운영하기 위해 필요한 데이터베이스도 확인해볼 있다.

show databases;

 

 

 

ProxySQL에서 MySQL서버를 모니터링 있는 계정을 설정한다. 설정은 ProxySQL MySQL 접속할 있는 계정이어야 한다. 사용자 환경에 따라 username passwrd 변경하여 사용한다.

UPDATE global_variables SET variable_value = 'username' WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'password' WHERE variable_name = 'mysql-monitor_password';

 

모니터링 계정이 추가 되었는지 확인한다.

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

 

 

 

변경 사항 저장 즉시 적용을 위해 아래 스크립트를 실행한다.

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

 

 

ProxySQL에서 연결할 MySQL 서버 정보를 추가한다. 현재는 구성된 모든 서버를 호스트그룹 1 구성하였다.

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.97', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.10', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.13', 3306);

 

추가된 MySQL 목록을 확인한다.

SELECT * FROM mysql_servers;

 

 

변경 사항 저장 즉시 적용을 위해 아래 스크립트를 실행한다. 위에 사용한 설정 저장 적용 스크립트와 명령이 다르므로 주의한다.

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

 

 

아래 스크립트를 실행하면, 추가한 MySQL서버들에 대해서 연결 성공 여부 로그를 확인할 있다. 바로 실행하면 현재까지 연결된 적이 없기 때문에 로그가 남지 않는다. 하지만 연결 테스트를 진행 확인해보면 커넥션 로그가 남아있는 것을 확인할 있다.

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

 

 

 

모니터링을 위해 ProxySQL MySQL사이의 로그도 확인할 있다.

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

 

 

이제는 외부 클라이언트가 ProxySQL 접속할 계정 생성한다. 계정을 사용하여 외부 어플리케이션들이 ProxySQL 접속하게 된다.

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('ubuntu', '123456', 1);

 

 

외부 접근용 계정을 확인한다.

select * from mysql_users;

 

 

변경 사항 저장 즉시 적용을 위해 아래 스크립트를 실행한다. 위에 사용한 설정 저장 적용 스크립트와 명령이 다르므로 주의한다.

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

 

proxy-sql 서버에서 아래 명령으로 ProxySQL 접속하여, 연결 테스트 현재 그룹에 연결된 서버들에게 요청이 되는지 확인한다. 여러 호출해보면 등록된 MySQL 서버로 분산되어 호출되어 호스트 이름이 변경되어 호출되는 것을 확인할 잇다.

mysql -uubuntu -p123456 -h127.0.0.1 -P6033 -e "SELECT @@hostname";

 

Tip : MySQL 8 버전대에서는 패스워드 문제로 일부 연결 발생할 있다고 한다. 이런 경우 아래와 같은 명령을 사용할 있다.
 
mysql --default-auth=mysql_native_password -uubuntu -p123456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname";

 

 

 

 

 

지금까지의 ProxySQL 서버의 설정을 파일로 백업할 경우 아래 명령어를 사용할 있다. 실습에서는 서버의 대수가 적고 룰도 간단해서 백업이 필요하지 않을 수도 있지만, 실제 업무에서 사용할 때에는 서버의 대수도 많고 동일한 환경을 유지하기 위해서 반드시 설정 백업을 있도록 한다.

--ProxySQL설정 백업
SELECT CONFIG INTO OUTFILE /tmp/backup.cfg;
SAVE CONFIG TO FILE  /tmp/backup.cfg;

 

백업된 파일 내용을 살펴보면 아래와 같은 내용들이 포함된 것을 확인할 있다.

--백업된 설정 내용 조회 (/etc/proxysql.cnf)
SELECT CONFIG FILE;

 

 

Tip. MySQL8 버전부터는 계정 비밀번호 정책이 변경되었기 때문에 아직 ProxySQL에서도 문제가 발생하곤 한다. 아래 링크를 참고하여 본인 서버에 발생하는 케이스를 해결하는데 도움이 있도록 한다.
l   ProxySQL-2 cannot connect to PXC using the user with a caching_sha2_password plugin. : https://github.com/sysown/proxysql/issues/2580
l   Access denied while connecting via proxysql to MySQL pxc : https://github.com/sysown/proxysql/issues/3672
l   Access denied after redeployed proxysql : https://github.com/sysown/proxysql/issues/2424
l   access proxysql 6033 using mysql client sometimes successsometimes failed : https://github.com/sysown/proxysql/issues/3185
l   Why ERROR 1045 (28000): Access denied in ProxySQL server? : https://devpress.csdn.net/mysqldb/63036be47e668234661985e8.html

 

 

 

2023-07-22 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브, ProxySQL설치, MySQL복제설치

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서버는 무엇일까]

프록시 서버는 리소스를 요청하는 클라이언트와 해당 리소스를 제공하는 서버 사이에 중개자 역할을 한다. 프록시 서버에 요청된 내용들은 캐시를 이용하여 저장하고, 캐시 정보를 요구하는 요청에 대해서는 원격 서버에 접속하여 데이터를 가져올 필요가 없다. 전송시간을 절약할 있다. 또한 불필요하게 외부와의 연결을 하지 않아도 된다는 장점과 외부의 트래픽을 줄이게 됨으로써 네트워크 병목 현상을 방지할 있다.

l  Proxy Server : https://en.wikipedia.org/wiki/Proxy_server

 

 

[ProxySQL 사용하는 이유는 무엇일까]

서비스 운영시 MySQL서버를 여러대로 구성해야하는 경우가 있다. (, Replication, Sharding ) 이때 어플리케이션 수가 증가할수록 MySQL 서버와 맺게 되는 커넥션수가 급격하게 늘어난다. 예를들어 MySQL 서버가 30, 어플리케이션 서버가 200대이고, 서버별로 커넥션을 10개씩 유지한다면 30 * 200 * 10 = 60000 커넥션이 발생한다. 이런 이슈를 피하기 위해 중간에 미들웨어 개념으로 프록시 역할을 해주는 것이 ProxySQL이다. ProxySQL 효율적인 커넥션 관리뿐만 아니라 라우팅 기능 여러 기능을 제공한다.

 

 

[ProxySQL 주요기능]

l  Connection Multiplexing : ProxySQL 멀티플렉싱은 여러 어플리케이션들이 동일한 데이터베이스에 대한 연결을 재사용할 있도록 하는 기능이다. MySQL "스레드 " 구현이 아닌 "연결당 스레드" 사용기 때문에, 커넥션수가 증가함에 따라 (유휴 상태에서도) 점차 성능이 저하된다. ProxySQL "스레드 " 사용하고 다중화를 통해 데이터베이스 커넥션을 할당하고 관리하는 리소스의 수를 줄여 성능을 크게 향상시킨다.

l  Query Routing : ProxySQL 쿼리 규칙을 정의하여 쓰기를 기본으로 라우팅하고, 읽기를 복제본에 분산할 있다. 매우 세분화된 기준으로 즉시 쿼리를 다시 작성한다.

l  Query Cache : ProxySQL 쿼리 캐싱은 요청 결과를 캐시하며, 동일한 쿼리가 실행되었을 캐시의 결과를 재사용하여 반환한다.

o   https://proxysql.com/documentation/Query-Cache/

l  Monitoring : ProxySQL 연결된 서버의 상태를 핑으로 모니터링 한다. 주기 설정 응답이 없을 재시도하며, 재시도 횟수 초과시, 응답 없는 서버로 판단하여 프록시 서버 목록에서 제거한다.

l  Scheduler : 정해진 시간에 스크립트를 실행해주는 기능을 담당한다.

 

[Recvoery 위한 orchestrator]

ProxySQL Recovery 기능을 제공하지는 않는다. 그래서 MySQL 서버 장애시DB 클러스터에서는 Slave DB (Read only) 운영되던 데이터베이스를 Master DB 승격을 시켜야 하는데, 기능을 제공하지는 않는다. 이러한 단점을 극복하기 위해 MySQL 고가용성 솔루션인 오케스트레이터(orchestrator) 함께 사용한다.

 

오케스트레이터는 Replication으로 구성된 DB 구조를 파악하고 스케줄러가 DB 설정을 변경할 있어 기존의Slave DB Master 승격시킬 있다. 그리고 ProxySQL 인지할 있도록 스케줄러가 ProxySQL 설정 파일을 업데이트 한다. 이러한 방식으로 Recovery 구성할 있다.

 

 

 

[참고자료]

l   공식 사이트 주소 - https://www.proxysql.com/

l   ProxySQL 설치 사용 가이드 - https://github.com/sysown/proxysql/wiki

l   ProxySQL 도커 이미지 - https://hub.docker.com/r/proxysql/proxysql

l   MySQL high availability & autofailover with ProxySQL & Orchestrator - Codemotion Milan 2018 : https://speakerdeck.com/thijsferyn/mysql-high-availability-and-autofailover-with-proxysql-and-orchestrator-codemotion-milan-2018

l   https://www.linkedin.com/pulse/iac-ha-db-architecture-aws-kuiyang-edwin-wang/

 

 

 

2023-07-20 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브

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에서 데이터를 백업할 시점 백업(Point in Time) 수행하는 방법에는 가지가 있다. 첫번째 방법은 mysqldump 명령을 사용할때 –single=transaction옵션을 사용하는 방법이고, 두번째 방법은 xtrabackup 사용하는 것이다. 이번 포스트에서는 mysqldump 다루도록 한다.

mysqldump 사용한 시점 백업은 아래와 같다.

mysqldump --all-databases --master-data –single-transaction > all_databases.sql

 

백업이 완료 되었으면 아래 명령을 사용하여 Slave DB에서 백업 파일을 복원한다.

mysql -uroot -p ‘xxxx’ < all_databases.sql

 

슬레이브DB 백업파일 복원이 완료 되었으면 백업파일에서 마스터DB 로그 파일과 포지션 정보를 확인하여 레플리케이션 정보를 등록한다.

head -30 all_databases.sql

 

 

정보를 바탕으로 연결할 마스터DB 정보를 입력하고 연결을 시작한다.

--연결설정
mysql>CHANGE MASTER TO
MASTER_HOST=’1.2.3.4’,
MASTER_USER=’replication_user’,
MASTER_PASSWORD=’replication_password’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000108’,
MASTER_LOG_POS=384679355;


--연결시작
mysql> start lave

 

슬레이브DB 복제가 시작되었으면 show slave status 명령을 사용하여 슬레이브DB 복제상태를 확인할 있으며 “Slave I/O thread”, “Slave SQL thread” 상태가 YES 표시되면 정상 작동이며 , NO라고 표시될 경우 비정상 작동이라고 판단할 있다.

 

[참고자료]

·       mysqldump — A Database Backup Program : https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-transaction-options

 

 

2021-05-28 / Sungwook Kang / http://sungwookkang.com

 

MySQL, MysqlDump, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브

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 상호작용하는 방식을 나타낸것이다.

 

버퍼풀은 여러 인스턴스를 보유할 있으며 인스턴스는 청크로 분할된다. 인스턴스의 수는 1 ~ 64 까지 있으며 청크의 양은 1000개를 초과하지 않도록 해야한다. 따라서 3GB RAM 있는 서버에서, 8개의 인스턴스가 있는 2GB 버퍼 기본값(128M) 청크를 가지고 있는 경우 인스턴스당 2개의 청크를 얻게 된다. 뜻은 16개의 청크가 있음을 의미한다.

 

현재 설정되어있는 버퍼풀 크기를 확인하는 방법은 아래 스크립트를 사용한다.

mysql> show global variables like 'innodb_buffer_pool_size';

+-------------------------+------------+

| Variable_name           | Value      |

+-------------------------+------------+

| innodb_buffer_pool_size | 1073741824 |

+-------------------------+------------+

 

버퍼 크기를 조절하는 방법은 아래 스크립트를 실행한다.  이때 파라메터는 바이트 값이므로 설정시 주의 한다.

mysql> set global innodb_buffer_pool_size=1610612736;

 

 

 

[참고자료]

·       InnoDB Buffer Pool Resizing: Chunk Change : https://www.percona.com/blog/2018/06/19/chunk-change-innodb-buffer-pool-resizing/

 

 

 

 

2020-10-20 / Sungwook Kang / http://sungwookkang.com

 

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

 

서버 구성

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

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

그룹 최대값 / (컬럼 최대값 칼럼 최소값) * 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 통계 정보

 

·       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

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 SESSION sql_mode = ''

 

Server SQL Mode MySQL 지원해야 하는 SQL 구문과 수행해야하는 데이터의 유효성 검사의 종류를 정의한다. MySQL 서버는 이러한 모드를 클라이언트에 개별적으로 적용할 있다. 운영중에 모드를 변경하려는 경우, SET 명령을 사용할 수있으며, GLOBAL 또는 SESSION  변수를 사용할 있다. GLOBAL 변수의 경우 SUPER 권한이 필요하며, 설정 이후 모든 클라이언트의 작동에 영향을 준다. SESSION 현재 클라이언트에만 영항이 있다. 현재 설정된 sql_mode 값을 확인하려면 아래 스크립트를 실행한다.

SELECT @@GLOBAL.sql_mode;

SELECT @@SESSION.sql_mode;

 

 

. MySQL 5.7 경우 기본SQL 모드는 아래와 같다.

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, and NO_ENGINE_SUBSTITUTION

 

이번 해결은 유효성 검사를 비활성화 하여 해결하였지만 Sql_mode 변경은 시스템 환경을 변경하는것으로 반드시 알고 사용해야 하며 다른 서비스에 영향이 없는지 반드시 검토가 필요하다.  자세한 내용은 아래 링크를 참고 한다.

·       Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

 

 

 [참고자료]

·       Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

·       FAQ : Server SQL Mode : https://dev.mysql.com/doc/refman/5.7/en/faqs-sql-modes.html

 

 

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

 

MySQL, MySQL sql_mode, 유효성 검사, 벌크로드, NULL Exception, NULL 값처리, 데이터 마이그레이션, LOAD DATA INFILE

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 서비스 재시작이 필요하다.

[mysqld]

default-time-zone='+9:00'

 

다른 방법으로는 현재 서비스 상태에서 글로벌, 또는 세션 변수의 값을 명시적으로 설정할 있다. 방법은 서비스를 재시작 하지 않아도 된다.

SET GLOBAL time_zone='timezone;

SET time_zone='timezone;

 

어플리케이션에서 연결시에도 타임존을 지정할 있다. 경우 세션으로 작동한다. JDBC URL 추가하는 방법은 아래 코드를 참고 한다.

<bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource" destroy-method="close">

  <property name="driverClassName" value="com.mysql.jdbc.Driver"/>

  <property name="url" value="jdbc:mysql://127.0.0.1:3306/ db?serverTimezone=UTC" />

  <property name="username" value="admin"/>

  <property name="password" value="admin"/>

</bean>

 

Timezone 값은 여러 가지 포맷으로 사용할 있으며 대소문자 구분이 없다.

·       SYSTEM MySQL 시스템의 타임존과 동일한 설정을 사용

·       ‘+9:00’ 또는 ‘-6:00’ 같이 GTM/UTC 기준 Offset 사용

·       US/Eastern, Asia/Seoul 같은 named timezone 사용

 

named timezone 형태의 값을 사용하려면  “mysql” 데이터베이스에 “time_zone”, “time_zone_name”, “time_zone_transition”, “time_zone_transition_type”, “time_zone_leap_second” 테이블을 참조하는데 실제 테이블은 빈테이블이기 때문에 사용자가 데이터를 입력해주어야 한다. 타임존 데이터는 아래 링크에서 정보를 다운로드 받아 스크립트를 실행한다. 스크립트를 살펴보면 타임존 정보를 확인할 있다.

·       https://dev.mysql.com/downloads/timezones.html

 

[참고자료]

·       https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html

 

2019-06-12 / 강성욱 / http://sungwookkang.com

MySQL, MySQL Timezone, 타임존, DB time, time_zone, time_zone_name, time_zone_transition, time_zone_transition_type, time_zone_leap_second, named timezone