mysql-shell 기능중 가장 눈에 띄는것은 parallel dump, load 

보통 mysqldump와 xtrabackup 을 사용한다. (본인은 ㅋㅋㅋ)

mysqldump

- 장점 : 압축백업하면 원래 DB의 1/4 이하로 크기가 줄어든다.

- 단점 : 백업시간이 오래걸린다.  핫백업이 어렵다

xtrabackup

- 장점 : 빠르다. 핫백업/원격백업 증분백업 등 다양한 백업방법을 지원한다

- 단점 : 백업결과가 논리백업보다 크다.

          디스크 공간이 부족해서 mysqldump 를 사용하는 경우도 있다.

mysqldump, mysqlpump, mydumper, mysql shell 4가지에 대한 성능 테스트 결과는 아래의 사이트를 확인한다. 

http://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/

mysql shell 이 성능이 제일 좋단다.

그냥 참고만 하고 도입여부는 실제 테스트를 해보고 결정해야 한다. 

병렬로 백업을 할때 시스템 부하도 봐야한다. 성능은 잘나오는데 시스템이 죽을려고 하면 안되니 

시스템에서 안정적으로 동작하는 최적의 thread 수도 찾아야되고 상황에 따라서는 I/O 제한 값도 찾아야 하니 테스트를 해보자.

util 부분을 살펴보면 백업에 관련되는것은 dumpInstance(), dumpSchemas(), dumpTables(), loadDump() 로 보인다. https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html

mysql-shell 에서 util. 을 입력하고 탭을 누르면 아래와 같이 확인이 가능하다. 

dump utility 

- mysqldump에서 제공하지 않는 아래 기능을 제공함

  - Oracle Cloud Infrastructure Object Storage 스트리밍

  - MySQL 데이터베이스 서비스 호환성 확인 및 수정,

  - 다중 스레드를 사용한 병렬 덤프

  - 파일 압축

- dump utility 의 종류 

  - util.dumpInstance(outputUrl[, options]) : 데이터베이스(인스턴스) 전체 
  - util.dumpSchemas(schemas, outputUrl[, options]) : 논리 스키마 (스키마, DB)
  - util.dumpTables(schema, tables, outputUrl[, options]) : 테이블별 

util.dumpInstance(outputUrl[, options]) 

아래의 이미지를 보면 알수 있을듯 mysql, information_schema, performance_schema, sys DB는 백업 받지 않는다. 

백업할때 어떤 명령이 실행되는지 general log 를 켜서 보자 

- FLUSH TABLES WITH READ LOCK 걸고 

- 테이블 PK 정보 확인 (뷰, 테이블 컬럼 등도 확인한다.)

- binlog position 확인 

- DB, Table 생성 DDL 문 확인 

- dump 할 테이블의 pk 값 최소/최대값 확인 

  - pk 기준으로 여러개의 chunk (기본 64MB) 로 나누어 저장하기 위해 확인하는 듯 

  - 테이블에 pk가 없는 경우 chunk 단위로 병렬 dump/load 가 안되서 하나의 파일로 dump 

- Data dump for table 에 해당하는 테이블 select 

  - 기본 4개의 thread 에서 dump 

백업된 파일 

주요 옵션

- threads : data chunk dump 시 사용할 thread 수 (기본값 4)

- maxRate : dump 중 데이터 읽기 처리량에 대한 thread 당 초당 최대 바이트수  (0, 빈값 지정시 제한 없음)

- showProgress : dump 진행 정보 표시여부 (true/false)

- compression : dump 데이터 파일 압축 유형 (기본 zstd)

- excludeSchemas /excludeTables : 지정한 schema/table 제외하고 dump 

- includeSchemas / includeTables  : 지정한 schema/table 만 dump 

- excludeUsers : 지정한 사용자 계정을 제외하고 dump 

- includeUsers : 지정한 사용자 계정만 dump 

- compatibility : 호환성을 위해 dump 시 DB 설정을 변경해서 dump  

- users, event, routines, triggers : dump 에 사용자, 이벤트, 함수, 저장프로시저, 트리거 포함여부 (true/false)

- defaultCharacterSet : dump 를 위해 MySQL Shell 에서 MySQL 서버로 연결할 때 사용할 캐릭터셋 

- consistent : 일관된 데이터 백업을 위해 dump 시 인스턴스 잠금을 할지 여부 (true/false)

- ddlOnly : 데이터 없이 DDL 문만 dump 할지 여부 (true/false)

- dataOnly : 데이터만 dump 할지 여부 (true/false)

- chunking : 테이블 데이터를 여러 파일로 분할 여부 (true/false)

- bytesPerChunk : chunk 활성화시 chunk 파일 크기

참고

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html

http://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/

mysql shell 이라는게 생겼다.

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-features.html

일단 써보자.

설치는 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-linux-quick.html 를 참고하고 

mysqlsh 를 실행하고 \connect root@localhost:3306 으로 접속하는데 신기하게 MySQL JS 가 컬러로 나온다. 오~ @_@)

그리고 패스워드를 저장할것인지 물어본다. (테스트니 걍 y)

패스워드를 저장하긴 했는데 운영서버에서 저장하면 보안사고! 저장한거 지워보자 

shell.listCredentials() 로 패스워드가 저장된 연결을 확인 후 

shell.deleteCredential(url) 로 삭제한다.

mysqlsh mysql://root@localhost:3306 으로 다시 접속 

패스워드가 삭제되서 다시 패스워드를 저장할지 물어본다. 

패스워드 저장 관련 옵션을 찾아보자 

아래와 같이 3가지가 있다. 

- credentialStore.excludeFilters []

  패스워드 자동저장에서 제외할 서버의 url 
- credentialStore.helper default

  패스워드 저장여부를 물어보는 부분을 사용할지 여부 

  - default : 사용

  - disabled : 미사용
- credentialStore.savePasswords prompt

  패스워드 자동저장 처리 방식 

  - always : 자동저장
  - never : 저장하지 않음 
  - prompt : 사용자에게 물어본다. 

메뉴얼에 MySQL Shell은 JavaScript, Python 및 SQL로 작성된 코드를 처리합니다. 라고 되어 있다.

메뉴얼을 살짝 보니 내용이 많다. 시간날때 까봐야지 

MySQL Binlog format

- Statement : 쿼리를 그대로 기록하는 방식

- Row : 생성/변경된 모든 Row 정보를 기록하는 방식 

- Mixed : Statement, Row 가 혼합된 방식 

Statement 사용시 복제 구성에서 now(), sysdate() 같은 함수를 쓰는 쿼리가 있을 경우 Slave 에서 반영될때 시간차이가 발생한다. 

Master 에서 10:00:00 에 쿼리가 실행된 쿼리가 Slave에 10:00:04초에 반영되면 4초의 오차 발생!

Row 의 경우 기록되는 로그사이즈가 크다. 이로 인한 디스크 공간, I/O, 네트워크 부하 등등이 문제다.

그래서 사용하는게 Mixed! 

Mixed 의 경우 now(), sysdate() 같은 함수는 row 형태로 저장된다. 

binlog_row_image 

Row 방식으로 binlog를 기록할 때 로깅할 컬럼 Set을 지정한다. 

- FULL : 변경 전/후 이미지 전체를 binlog 에 기록

- MINIMAL : 최소한의 값만 기록, 변경된 데이터와 PK 만 기록 

- NOBLOB : BLOB, TEXT 같이 사이즈가 큰 값을 제외하고 기록 

Statement, Mixed 만 써봤네요.

ftp 게임하다 갑자기 이게 왜 생각날까? ㅡ_ㅡ);;

생각난김에 써봤음

데이터 건수 130건 정도에 작은 컬렉션에서 슬로우 쿼리가 발생한다. 

db.컬렉션.find() 만 해도 1800ms 가 나온다. 

열심 찾아보니 repairDatabase를 하던지 compact를 하란다. 

먼저 compact 시도... 동일하다. 

그래 repairDatabase 를 하자

rs.printReplicationInfo()로 db.repairDatabase() 하고 그동안의 변경분을 oplog 에서 받아오는데

문제가 없을지 공간 확인을 한다. 

Replica Set을 구성하는 노드들을 한대를 shutdown 하고 Conf 파일에서 Replica Set 구성 부분을 주석처리하고

Standalone 으로 구동! 

해당 컬렉션이 있는 DB에서 db.repairDatabase() 를 실행하고 완료되면 Conf 파일에서 주석처리 했던 부분을 해제하고

Replica Set의 맴버로 구동해서 SECONDARY 로 올라오는지 확인한다.

(PRIMARY 에서 rs.status()로 resync 완료후 SECONDARY 로 올라왔는지 확인)

이 과정을 한대씩 다른 노드들에서 실행한다. 

마지막으로 PRIMARY에 접속해 rs.stepDown() 을 실행!

SECONDARY 가 PRIMARY 로 승격되는것을 확인하고 Shutdown 후 db.repairDatabase()를 실행 후 Replica Set의 맴버로 구동한다. 

이후 해당 증상 해결 정확한 원인을 알수 없어서 아쉽지만 해결!

점검 잡고 하면 좋겠지만 그게 안되면, 운영중에 어느정도 리스크를 감안하고 진행!

지금 쓰고 있는 스프라켓은 14-34t 7단 스프라켓인데 이걸 11-34t 나 11-28t로 바꿀까 고민중.

11-28t로 바뀌면 34t가 없어져서 오르막길에서 아쉬울껀데.

11-34t로 바꿀까?

고민되네.. 

14-34t : 14-16-18-20-22-24-34
11-34t : 11-13-15-18-21-24-34

MySQL에서 인덱스의 최대 크기는 얼마일까?

- innodb_large_prefix이 활성화 된 경우 3072 바이트, 비활성화 된 경우 767 바이트

- 여기서 중요한것은 바이트, 글자수가 아니라 바이트!

  - MySQL은 varchar(글자수)로 선언된다.  Oracle은 varchar(바이트) 그래서 중요함

- 해당 컬럼의 characterset 확인

  - utf8의 경우 1글자가 3바이트

    3072 / 3 = 1024

    767 / 3 = 255.6666 

  - utf8mb4 는 1글자가 4바이트 (알아서 나누기 4하셈)

    utf8mb4 에서 'a'라는 값이 저장된다고 4바이트를 쓰는게 아니다. 

    선언은 4바이트까지 받을수 있다지만 디스크에 저장될때는 1바이트로 저장된다.  (저장공간도 1바이트)

참고 : https://dev.mysql.com/doc/refman/5.7/en/innodb-limits.html

MySQL 8.0 이 출시된지 3년이 지났다. 
현재 시점에서는 8.0을 사용하는것을 권장한다. 

8.0 에서 성능 향상
- Read / Write 에 대한 부분도 있지만, IO Capactiy 활용률 개선과 
  경합이 많은 작업들 컨텐션이 많은 작업들을 이전에는 FIFO 방식으로 처리 했지만 
  CATS(Contention Aware Transaction Scheduling) 라는 알고리즘을 도입해서 빈번하게 자주 들어오는것에 대해서 
  웨이트를 높게줘서 빨리 처리되게 하는 부분을 도입했다. 
- 리소스 그룹, Read, Write 별 Thread 수 할당 가능
- UTF8MB4 가 기본 케릭터셋이 되면서 관련된 성능 개선
- 5.7 에서는 JSON/BLOB 에서는 업데이트시 전체를 업데이트되어 IO가 많이 발생하였으나 

  이제 업데이트된 부분만 변경한다. 
- 데이터 딕셔너리가 도입되었다. 
  모든 시스템에 있던 파일들이 다 innodb table로 들어갔다. 
  information schema, performance schema 에 있던것들도 인덱스나 쿼리리를 사용해서 조회 가능

하드웨어/OS 튜닝 

Memory 
innodb buffer pool size 을 크게 가져갈수록 디스크 IO를 줄일수 있다. 
mysql이 가용가능한 메모리의 70~80%를 할당하고 가이드한다. 
- 10G기가 이하의 서버에서는 60~70% 권장 
- 처음에는 70~80% 할당후 사이트에 맞게 튜닝 권장 
메모리 튜닝을 할때 OS, FS Cache, Temporary 테이블을 사용하는 부분이 감안이 되어야 한다. 

CPU 
하이퍼 스레딩 활성화 
더 빠른 멀티 코어 프로세서 사용 권장 
CPU , IO, Memory 에 우선을 두자면 각 사이트의 주요 잡이 어떤것인지 봐야한다. 
보통 CPU 보다는 Memory나 IO에 더 신경을 쓰는것이 성능 향상에 도움이 된다. 
MySQL 버전에 따른 코어수 제한이 있다. 
- MySQL 5.1 : ~ 4코어 
- MySQL 5.5 : ~ 16코어 
- MySQL 5.6 : ~ 36 쓰레드(Core)
- MySQL 5.7 : ~ 64 쓰레드(32 Core-HT)
- MySQL 8.0 : ~ 100 쓰레드(48 Core-HT)

Disk 
SSD, NVMe 사용시 innodb_page_size=4K, innodb_flush_neighbors=0 로 사용을 추천함
- 신규로 세팅되는 서비스의 경우 추천
  사용중인 DB를 SSD로 변경후 적용시 성능향상 없음 
- innodb datadir, tmp 파일 및 undo 로그 모두 Ramdom IO가 발생함으로 SSD로 할당하고 
  로그(빈로그 등등)의 경우 Sequnential IO가 발생함으로 디스크로 할당하는것을 추천 

OS 
MySQL은 Linux 에 최적화 되어 있다. 
- 성능을 위해서는 Linux 를 추천한다. 
ulimit 로 파일/프로세스수 제한 
- MySQL 이외에 OS에서 설정을 해줘야되는 부분
- ulimit -n, 파일 수 제한 (connections, open tables, ...)
- ulimit -u, 스레드 수 제한 (connections, InnoDB background threads, event scheduler, ....)
NUMA 기반 서버의 경우 innodb_numa_interleave를 1로 설정 
InnoDB를 사용하는 경우 파일시스템 캐시를 사용안해도 된다. 
- buffer pool 이 데이터 캐시 형태로 사용함
- set inndbo_flush_method=O_DIRECT 
- 파일 시스템 캐시는 MySQL의 다른 부분(로그)에서 사용함으로 비활성화하지 않는다. 

MySQL 설정값 확인 방법

8.0부터  my.cnf 외에 mysqld-auto.cnf 가 추가 되었다. 

- 커맨드 라인에서 SET PERSIST 변수=값 지정시 mysqld 재기동시 설정된 값이 초기화 되었다. 

  SET PERSIST로 설정값이 mysqld-auto.cnf 파일에 저장되어 mysqld 재기동 이후에도 유지된다. 

- show [session|global] variables 로 조회 
- show [session|global] variables like '%보고 싶은것%';
- performance_schmea 에서 조회 가능 
  - performance_schmea.global_variables
  - performance_schmea.session_variables
  - performance_schmea.variables_by_thread
    - performance_schmea.threads 의 thread_id 에 connection 을 매핑 
- 설정값을 누가 언제 변경했는지 확인 가능 
  - performance_schmea 에서 확인 가능 

InnoDB buffer pool 과 redo log 
- innodb_buffer_pool_size 
  - 메모리에 DB페이지를 저장하기 위한 메모리 사이즈 
  - MySQL에서 사용가능한 메모리의 70~80% 할당 권장 
    - 각 서비스의 특성이 맞게 튜닝 필요 
  - MySQL 5.7 부터 innodb_buffer_pool_size 를 동적으로 변경 가능 
- innodb_log_file_size (redo log 사이즈)
  - 이부분을 크게 가져가면 쓰기 속도가 빨라진다. 
    - 서버가 재기동하면서 복구할때 사용하는 영역으로 사이즈를 너무 크게 잡을 경우 DB 복구시간이 느려진다. 
  - 운영서버 최소 512MB 권장 
  - innodb_log_files_in_group 에 의해 결정된 총 redo log 용량 (기본값은 2) 
    - 대부분 3을 사용하는편 

Trading performance over consistency (AICD 에 D에 해당하는 부분)
- Commit 된 트랜잭션에 대해 InnoDB flush/sync 는 언제해야 할까?
- innodb_flush_log_at_trx_commit 
  - 0 : commit 되면 redo log를 메모리에 기록하고 매 1초마다 메모리에서 디스크로 flush 
  - 1 (기본값) : 완전한 ACID 지원, 매 commit 마다 redo log 에 기록하고 디스크에 flush 
  - 2 : commit 되면 O/S Buffer로 Redo Log가 기록되고 매 1초마다 메모리(O/S Buffer)에서 디스크로 flush 함 
- 다음 경우를 제외하고 1을 권장(일관성을 위해)
 - 대량 데이터를 로딩할 때 로드 하는 동안 세션 변수를 2로 설정하고 데이터를 로드하는 경우 
   mysql 8.0.21 버전을 사용하는 경우 redo-logging 을 비활성화 할 수도 있다. 
 - 예상치 못한 엄청난 부하(디스크)를 경험하고 있는데 문제를 해결할 동안 일단 서버가 어떻게든 동작해야 하는 경우 
 
Buffers that are per client connections 
- connection(세션)당 할당되는 버퍼들 
  - read_buffer_size : Sequnential scan (full table scan)을 사용할 때 사용하는 버퍼 
  - read_rnd_buffer_size : 정렬 작업후, 정렬된 순서대로 데이터를 다시 읽어 들일 때 사용하는 버퍼 
  - join_buffer_size : 인덱스를 사용하지 않는 조인에 사용되는 버퍼 
                       Session level에서 join 단위로 생성
  - sort_buffer_size : 인덱스를 사용하지 않는 정렬에 사용하는 버퍼 
  - binlog_cache_size (if binary logginigs is enabled)
- connection이 많을 경우 이 버퍼들이 메모리를 점유하기 때문에 크게 세팅하면 안된다.
  만약 크게 필요한 경우가 있다면 필요한 세션에서만 크게 잡아서 사용한다. 
  
Analyzing queries
- EXPLAIN
  - optimizer 의 실행 계획을 보여줌
    실데이터가 아닌 카디널러티를 가지고 만든 실행계획 
- EXPLAIN ANALYZE 
  - 8.0.19에 추가됨 실제 데이터를 가지고 만든 실행계획
- SET profiing=1, 프로파일링 활성화 (SHOW PROFILES, SHOW PROFILE FOR QUERY X)
  - MySQL 8.0 기준 
- Optimizer trace (가능한 모든 optimizer 실행 계획 확인)

ibdata 파일크기가 급격하게 증가했다.

범인은.. 롱 ~ 쿼리 

실행한 사람은 쿼리 실행을 종료했다는데 쭉~ 실행되고 있었다.

UNDO에 데이터의 스냅 샷을 생성해서 해당 트랜잭션이 시작된 이후 데이터베이스의 일관된보기를 제공 할 수 있도록 이전 페이지를 실행 취소 상태로 유지한다.

데이터베이스에 쓰기로드가 많으면 많은 실행 취소 페이지가 저장되면서 UNDO의 크기는 커진다.

문제는 ibdata 에 UNDO가 있고 한번 커진 ibdata 파일을 축소하기 위해서는 DB를 다시 구성해야된다. 

1. 전체 데이터베이스 백업

2. mysql, information_schema 를 제외한 전체 데이터베이스 삭제 

3. ibdata 파일 및 ib_log 파일 삭제 

4. 전체 데이터베이스 복원 

이런 노가다를 하지 않기 위해 롱 쿼리가 돌고 있는지 모니터링 하자!