MySQL 8.0 지원 스토리지 엔진

• InnoDB: MySQL 8.0의 기본 스토리지 엔진입니다. InnoDB사용자 데이터를 보호하기위한 커밋, 롤백 및 크래시 복구 기능이있는 MySQL 용 트랜잭션 안전 (ACID 호환) 스토리지 엔진입니다. InnoDB행 수준 잠금 및 Oracle 스타일의 일관된 비 잠금 읽기는 다중 사용자 동시성과 성능을 향상시킵니다. InnoDB사용자 데이터를 클러스터형 인덱스에 저장하여 기본 키를 기반으로하는 일반적인 쿼리에 대한 I / O를 줄입니다. 데이터 무결성을 유지하기 위해 FOREIGN KEY 참조 무결성 제약 조건도 지원합니다.
• MyISAM:이 테이블은 설치 공간이 작습니다. 테이블 수준 잠금 은 읽기 / 쓰기 워크로드의 성능을 제한하므로 웹 및 데이터웨어 하우징 구성의 읽기 전용 또는 대부분의 읽기 워크로드에서 자주 사용됩니다.
• Memory: 중요하지 않은 데이터의 빠른 조회가 필요한 환경에서 빠른 액세스를 위해 모든 데이터를 RAM에 저장합니다. 이 엔진은 HEAP 엔진으로 알려졌습니다. InnoDB버퍼 풀 메모리 영역은 대부분 또는 모든 데이터를 메모리에 보관하는 범용적인 방법을 제공하고  NDBCLUSTER는 방대한 분산 데이터 세트에 대한 빠른 키-값 조회를 제공합니다.
• CSV: 테이블은 실제로 쉼표로 구분 된 값이있는 텍스트 파일입니다. CSV 테이블을 사용하면 CSV 형식으로 데이터를 가져 오거나 덤프하여 동일한 형식을 읽고 쓰는 스크립트 및 응용 프로그램과 데이터를 교환 할 수 있습니다. CSV 테이블은 index가 생성되지 않기 때문에 일반적으로 InnoDB정상 작업 중에 데이터를 테이블에 보관 하고 가져 오기 또는 내보내기 단계 중에 만 CSV 테이블을 사용합니다.
• Archive: 인덱스 화되지 않은 압축 테이블은 참조되지 않는 많은 양의 기록을 보관 또는 보안 감사 정보를 저장하고 검색하기위한 것입니다.
• Blackhole: Blackhole 스토리지 엔진은 Unix /dev/null장치 와 마찬가지로 데이터를 허용하지만 저장하지 않습니다 . 쿼리는 항상 빈 집합을 반환합니다. 이러한 테이블은 DML 문이 복제본 서버로 전송되지만 원본 서버가 자체 데이터 복사본을 유지하지 않는 복제 구성에서 사용할 수 있습니다.
• NDB(NDBCLUSTER) :이 클러스터 된 데이터베이스 엔진은 가능한 최고 수준의 가용성이 필요한 애플리케이션에 특히 적합합니다.
• Merge: MySQL DBA 또는 개발자가 일련의 동일한 MyISAM테이블 을 논리적으로 그룹화하고 하나의 개체로 참조 할 수 있습니다. 데이터웨어 하우징과 같은 VLDB 환경에 적합합니다.
• Federated: 별도의 MySQL 서버를 연결하여 여러 물리적 서버에서 하나의 논리적 데이터베이스를 생성하는 기능을 제공합니다. 분산 또는 데이터 마트 환경에 매우 적합합니다.
• Example:이 엔진은 새 스토리지 엔진 작성을 시작하는 방법을 보여주는 MySQL 소스 코드의 예입니다. 스토리지 엔진은 아무 작업도 수행하지 않는 " stub" 입니다. 이 엔진으로 테이블을 생성 할 수 있지만 테이블에 데이터를 저장하거나 검색 할 수 없습니다.

전체 서버 또는 스키마에 대해 동일한 스토리지 엔진을 사용하도록 제한되지 않습니다. 모든 테이블에 대해 스토리지 엔진을 지정할 수 있습니다. 예를 들어, 애플리케이션은 데이터를 스프레드 시트로 내보내기위한 InnoDB하나의 CSV테이블과 MEMORY임시 작업 공간을위한 몇 개의 테이블이 있는 대부분의 테이블을 사용할 수 있습니다 .

스토리지 엔진 선택

MySQL과 함께 제공되는 다양한 스토리지 엔진은 다양한 사용 사례를 염두에두고 설계되었습니다. 다음 표는 MySQL과 함께 제공되는 일부 스토리지 엔진에 대한 개요를 제공하며 표 다음에 나오는 설명을 명확히합니다.

참고:
1. 스토리지 엔진이 아닌 서버에서 구현됩니다.
2. 압축 된 MyISAM 테이블은 압축 된 행 형식을 사용할 때만 지원됩니다. MyISAM에서 압축 된 행 형식을 사용하는 테이블은 읽기 전용입니다.
3. 암호화 기능을 통해 서버에서 구현됩니다.
4. 암호화 기능을 통해 서버에서 구현됩니다. MySQL 5.7 이상에서는 미사용 데이터 테이블 스페이스 암호화가 지원됩니다.
5. 외래 키에 대한 지원은 MySQL Cluster NDB 7.3 이상에서 사용할 수 있습니다.
6. FULLTEXT 인덱스에 대한 InnoDB 지원은 MySQL 5.6 이상에서 사용할 수 있습니다.
7. 지리 공간 인덱싱에 대한 InnoDB 지원은 MySQL 5.7 이상에서 사용할 수 있습니다.
8. InnoDB는 Adaptive Hash Index 기능을 위해 내부적으로 해시 인덱스를 사용합니다.

percona에서 새로 소개한 binlog2sql에 대해 설명한다.
[출처] www.percona.com/blog/2020/07/09/binlog2sql-binlog-to-raw-sql-conversion-and-point-in-time-recovery/

1. 개요

mysqlbinlog 툴을 사용하면 binary log를 binary에서 텍스트 형식으로 변환할 수 있다.
"binlog_format & binlog_rows_query_log_events "매개 변수 에 따라 다르지만 mysqlbinlog 유틸리티를 사용하여 정확한 SQL 문을 생성할 수도 있다.
하지만 mysqlbinlog 툴에는 ROLLBACK 문을 생성하는 기능이 없다.
percona binlog2sql은 MySQL binary log를 디코딩하고 raw SQL을 추출하며, 플래시 백 기능을 사용하여 PITR (Point-Time Recovery)을 위한 ROLLBACK 문을 생성한다.

2. binlog2sql은 무엇인가?

• binary log를 구문 분석하는 오픈 소스 도구다.
• binary log에서 raw SQL 문을 추출하는 기능이 있다.
• 특정 시점 복구를 위해 binary log에서 ROLLBACK SQL을 생성하는 기능이 있다.

3. Mysqlbinlog vs Binlog2sql

binlog2sql 도구로 이동하기 전에 Mysqlbinlog와 bilog2sql의 출력을 비교한다.
아래 쿼리를 사용하여 binlog 이벤트를 생성한다.

3.1. Mysqlbinlog ( binlog_format = STATEMENT )

> mysqlbinlog --base64-output=decode-rows -vv --start-position 1682 --stop-position 2009 /log/mysql-log/mysql-bin.000005 >> test1.log

SQL 형태로 결과물을 제공한다.

3.2 Mysqlbinlog ( binlog_format=ROW, binlog_rows_query_log_events=OFF )

> mysqlbinlog --base64-output=decode-rows -vv --start-position 1629 --stop-position 1903 /log/mysql-log/mysql-bin.000005 >> test2.log

텍스트 형식이긴 하지만 SQL 형태는 아니다.

3.3. Mysqlbinlog ( binlog_format=ROW and binlog_rows_query_log_events=ON )

> mysqlbinlog --base64-output=decode-rows -vv --start-position 2325 --stop-position 2669 /log/mysql-log/mysql-bin.000005 >> test3.log

SQL 형태로 결과물을 제공한다.

3.4. Binlog2sql

> ./binlog2sql-master/binlog2sql/binlog2sql.py --user root -p --start-file mysql-bin.000005

SQL 형태로 결과물을 제공한다.

4. MySQL 요구 사항

다음 파라미터를 활성화한다.

binlog2sql은 BINLOG_DUMP 프로토콜을 기반으로 binlog 컨텐츠를 얻는다.
binlog2sql은 서버에서 INFORMATION_SCHEMA.COLUMNS 테이블을 읽기 위해 메타 데이터 테이블이 필요하다.

5. 설치

5.1. 테스트 환경

파이선 2.7
MySQL 5.7.28
참고 : MySQL 8 이상을 지원하려면 “pymysql – 0.9.3”이 있어야 한다.

다음 명령어를 통해 설치 가능하다.

5.2. Binlog2sql 옵션

6. Binary Log에서 Raw SQL 추출하는 방법

> create table binlog2sql(id int primary key auto_increment, name varchar(16), status enum('A','NA'), up_date datetime default current_timestamp); Query OK, 0 rows affected (0.01 sec) > insert into binlog2sql (name,status) values ('kani','A'),('ram','A'),('durai','A'),('asha','A'),('sakthi','A'); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 ./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 3717 --stop-position 4209 | cut -f1 -d"#"

7. binlog2sql의 PITR 사용법

Binlog2sql 툴에 “–flashback” 옵션을 사용하여 ROLLBACK 문을 생성한다.
INSERT, DELETE, UPDATE 문에서 데이터를 복구할 수 있다.
DROP, TRUNCATE는 binary logs를 사용할 수 없으므로 지원하지 않는다.

7.1. Case 1 – DELETE PITR :

테스트 테이블 "binlog2sql" 에서 "id in (4,5)"의 두 레코드를 삭제한다.

참고를 위해 binlog2sql을 사용하여 원시 SQL을 생성한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 4209 --stop-position 4571 | cut -f1 -d"#"

위의 DELETE는 레코드를 삭제하기 위해 실행된 명령문이다.

이제 아래와 같이 “–flashback”옵션을 사용하여 ROLLBACK 문을 생성한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 4209 --stop-position 4571 --flashback | cut -f1 -d"#"

해당 DELETE 문이 INSERT 문으로 변환된 것을 볼 수 있다.

이제  롤백 명령문을 사용하여 복구를 시도한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 4209 --stop-position 4571 --flashback | cut -f1 -d"#" >> rollback.sql mysql -uroot -p -D test -vvv < rollback.sql

DELETE에서 데이터를 복구했다.

7.2. Case 2 – UPDATE PITR :

테이블 "binlog2sql" 에서 "id in (4,5)"의 두 레코드의 상태를 “A”에서 “NA”로 변경했다.

참조를 위해 binlog2sql을 사용하여 실행된 UPDATE를 생성한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 5409 --stop-position 5817 | cut -f1 -d"#"

ROLLBACK 문을 생성한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 5409 --stop-position 5817 --flashback | cut -f1 -d"#"

이제  롤백 명령문을 사용하여 복구를 시도한다.

./binlog2sql-master/binlog2sql/binlog2sql.py -uroot -p --start-file mysql-bin.000005 --start-position 5409 --stop-position 5817 --flashback | cut -f1 -d"#" >> rollback.sql mysql -uroot -p -D test -vvv < rollback.sql

변경 사항이 rollback 되었다.

8. 결론

앞에서 언급했듯이이 binlog2sql 도구는 INSERT, DELETE, UPDATE에 대해서만 롤백을 지원한다.
DDL 문은 binary log에 실제 데이터를 기록하지 않으므로 DDL을 지원하지 않는다.
binlog2sql은 암호화, 압축 binary log에서 작동하지 않는다.
binlog2sql은 MySQL 5.6 및 MySQL 5.7 환경에서 테스트되었다.

sysbench는 CPU, memory, file I/O, mutex performance, MySQL 등 다양한 테스트가 가능 하고, 많은 레퍼런스 보유하고 있어 성능 진단에 유용하게 사용 된다.
sysbench를 이용해서 mysql 성능을 측정해보자

설치는 yum으로 간단하게 할 수 있다.

> sudo yum -y install sysbench

설치가 완료 되면 /usr/share/sysbench 경로에 벤치 마크 예제들이 생성 된다.
oltp_read_write.lua 파일을 이용해서 mysql DB의 oltp 성능을 측정해보자.

진행은 다음 순서로 한다.
1. prepare : 초기 기반 테이블 및 데이터을 설치
2. run : 수행 후 분석 결과 확인
3. cleanup : 설치한 모든 내용 제거

옵션은 다음과 같다.
--max-requests=[쿼리 요청 개수] 
--max-time=[테스트 시간]
--mysql-host=[호스트명] 
--mysql-db=[테스트 데이터베이스명] 
--mysql-user=[DB USER] 
--mysql-password=[DB USER 비밀번호] 
--mysql-table-engine=[테이블 엔진 종류] 
--mysql-socket=[MySQL 소켓 파일 경로] 
--mysql-port=[MySQL-포트-번호] 
--oltp-test-mode=[simple|complex|nontrx] 
--oltp-read-only=[on|off] 
--num-threads=[동시 스레드 개수]
--table_size=[데이터 건수]
--tables=[테이블 개수]

다음과 같이 실행 시간 기반으로 요청 수에 관계 없이 240초 동안 실행하고 결과를 출력한다.

sysbench --time=240 --max-requests=0 /usr/share/sysbench/oltp_read_write.lua \
--table_size=10000000 --tables=30 --report-interval=1 \
--mysql-host=localhost --mysql-port=3306 \
--db-driver=mysql --mysql-user=sysbench --mysql-password=sysbench \
--mysql-db=sysbench --mysql-socket=/tmp/mysql.sock --num-threads=110 \
prepare

sysbench --time=240 --max-requests=0 /usr/share/sysbench/oltp_read_write.lua \
--table_size=10000000000 --tables=15 --report-interval=1 \
--mysql-host=localhost --mysql-port=3306 \
--db-driver=mysql --mysql-user=sysbench --mysql-password=sysbench \
--mysql-db=sysbench --mysql-socket=/tmp/mysql.sock --num-threads=110 \
run

본 테스트 시스템에서는 threads가 130이 넘으면 에러가 발생하고, table_size가 1000000000000 이상이면 동작하지 않으므로 적절한 값으로 조절하여 수행 하면 된다.

다음 테스트는 time 240으로 table_size 와 threads에 변화에 따른 tps 수치 변화를 표로 만들어 보았다.

threads 값 보다는 table_size가 tps에 더 많은 영향을 미치고 오히려 threads가 높으면 Latency가 높아서 성능이 더 떨어지는 상황이 발생 하였다.

테스트시 수행되는 SQL의 패턴은 다음과 같다.

mysql 벤치 마크 툴인 DBT2에 대해서 설정하고, 서버의 성능에 대하여 측정 해본다.

테스트에 사용된 DB : MySQL 5.7.28

DBT2 : dbt2-0.37.50.15

DBT2의 다운로드 사이트 : https://dev.mysql.com/downloads/benchmarks.html

해당 사이트에서 툴을 다운로드 받은 후 테스트 할 서버로 업로드를 한다.

해당 툴의 압축을 해제 한다.

> cd cd /data/mytmp
> tar xzvf dbt2-0.37.50.15.tar.gz

해제한 툴을 설치 한다.

> cd /data/mytmp/dbt2-0.37.50.15/
> ./configure --with-mysql=/db/mysql/

> make

데이터 생성 및 데이터 로딩

> mkdir -p /data/mytmp/dbt2-0.37.50.15/output
> ./src/datagen -w 30 -d /data/mytmp/dbt2-0.37.50.15/output --mysql

mysql 에서 테스트에 사용할 유저 생성

> create user test@'localhost';
> grant all privileges on *.* to test@'localhost';

> create user test2@'%' identified by 'test2';
> grant all privileges on *.* to test2@'%';
> create user test2@'localhost' identified by 'test2';
> grant all privileges on *.* to test2@'localhost';

mysql 에 테스트 데이터 생성

> ./scripts/mysql/mysql_load_db.sh --mysql-path /db/mysql/bin/mysql --database dbt2 --path /data/mytmp/dbt2-0.37.50.15/output --socket /tmp/mysql.sock --user test --verbose

위와 같이 에러가 발생하는데 스크립트 상에 오류와 데이터에 문제가 좀 있어 수정이 필요하다.

mysql_load_db.sh 파일을 열어 다음 부분을 찾아 수정한다.

그리고 다시 수행

> ./scripts/mysql/mysql_load_db.sh --mysql-path /db/mysql/bin/mysql --database dbt2 --path /data/mytmp/dbt2-0.37.50.15/output --socket /tmp/mysql.sock --user test --verbose

프로시저 생성

> ./scripts/mysql/mysql_load_sp.sh --database dbt2 --sp-path ./storedproc/mysql/ --client-path /db/mysql/bin/ --user test2 --password test2

벤치마크 툴 수행

> ./scripts/run_mysql.sh -u test2 --password test2 -d dbt2 -s /tmp/mysql.sock -w 3 -t 300 -c 50  --lib-client-path /db/mysql/bin/mysql --verbose

워크로드가 적어 성능 측정이 되지 않는다.

적절하게 값을 늘려본다.

> ./scripts/run_mysql.sh -u test2 --password test2 -d dbt2 -s /tmp/mysql.sock -w 50 -t 300 -c 50  --lib-client-path /db/mysql/bin/mysql --verbose

해당 서버는 사양이 좋지 않아 분당 약 40 의 주문 트랜잭션 처리가 가능하다.