문뜩 상세 옵션이 궁금해서 정리

--apply-log-only
  - Redo Log 적용단계만 수행되고 다른 Cash Reecovery 단계는 무시된다. 
    로그만을 적용하고 데이터베이스를 실제로 복원하지 않는다. 
    Commit 되지 않은 트랜잭션의 롤백단계 방지용
  - 증분 백업시에 사용

--backup
  - --target-dir 에 지정한 디렉토리에 데이터베이스를 백업한다. 

--backup-lock-retry-count=
  - 메타데이터 락을 획득하려는 시도 횟수 

--backup-lock-timeout=
  - 메타데이터 락을 획득하려는 시도시의 제한 시간(초)

--binlog-info (OFF | ON | LOCKLESS | AUTO)
  - xtrabackup 이 binlog 로그 좌표를 검색하는 방법 지정 
  - OFF : binlog 정보 검색 없이 백업한다. 
          LOCK BINLOG FOR BACKUP 명령어 실행안함 
  - ON : binlog 정보 검색 활성화
         가능한 경우 LOCK BINLOG FOR BACKUP 명령어를 실행하여 binlog-lock의 공유 장금을 적용하여 일관성을 보장한다. 
  - LOCKLES : 락을 최소화하여 테이블 잠금없이 binlog 정보를 백업한다.
              LOCK BINLOG FOR BACKUP 명령어 실행안함 
              xtrabackup_binlog_info 파일 생성안함 
  - AUTO : 기본값 
           백업 대상서버의 have_backup_safe_binlog_info 서버 변수값에 따라 자동으로 ON, LOCKLESS로 전환된다. 
           have_backup_safe_binlog_info 변수가 나타내는 정보는 MySQL 서버의 현재 상태에 따라 동적으로 변경된다. 

--check-privileges
  - 백업을 수행하기전에 백업을 수행하기 위한 권한이 있는지 확인한다.

--close-files
  - xtrabackup은 테이블스페이스를 열때 일반적으로 DDL 작업을 올바르게 관리하기 위해 파일 핸들을 닫지 않는다. 
    테이블스페이스 수가 너무 많아 제한에 맞지 않을 경우 더 이상 액세스할 수 없으면 파일 핸들을 닫도록 이 옵션을 사용할 수 있다.
  - 옵션 사용시 일관성 없는 백업을 생성할 수 있다. (주의)

--compact
  - 보조 인덱스(Secondary Index) 페이지를 건너뛰어 백업 파일의 크기를 최적화하는 옵션이다. 
    주요 인덱스(primary index)는 백업 파일에 유지되지만, 보조 인덱스(Secondary Index)는 필요한 경우에만 유지되고 그렇지 않으면 생략된다. 

--compress
  - 지정된 압축 알고리즘을 사용하여 트랜잭션 로그 파일, 메타데이터 파일을 포함한 모든 출력 데이터를 압축한다.
  - quicklz 알고리즘만 지원하고 있다. 
    결과 파일은 qpress 아카이브 형식을 갖는다.
  - xtrabackup 에서 생성된 모든 \*.qp 파일은 기본적으로 단일 파일 qpress 아카이브이며 qpress 파일 아카이버로 추출하고 압축을 풀 수 있다. 

--compress-chunk-size=
  - 압축 스레드의 작업 버퍼 크기(바이트), 기본값 64K

--compress-threads=
  - 병렬 데이터 압축을 위해 사용하는 스레드 수 지정, 기본값 1 
  - 병력 압축(--compress-threads)은 병렬 백업 (--parallel)와 함께 사용할 수 있다. 
    ex) --parallel=4 --compress --compress-threads=2
        4개의 스레드로 병렬 압축 백업, 압축 스레드로 2개의 스레드를 사용한다. 

--copy-back
  - 백업된 데이터를 실제 데이터 디렉토리로 복원
  - 데이터 디렉토리에 비워져 있어야함
    데이터가 있을 경우 덮어쓰여질 수 있다. 
    
--core-file
  -  xtrabackup이 비정상 종료될 때 발생한 Core Dump를 저장하는 경로를 지정하는 옵션

--databases=
  - 백업 대상 데이터베이스 및 테이블 목록을 지정
  - "databasename1[.table_name1] databasename2[.table_name2] . . .". 형식으로 사용 

--databases-exclude=name
  - 백업시 제외할 데이터베이스명 지정
  - --databases 옵션보다 우선순위가 높다. 

--databases-file=
  - 백업대상 데이터베이스, 테이블의 파일 경로 지정 
  
--datadir=DIRECTORY
  - 백업할 MySQL 서버의 datadir 과 동일하게 지정
  - my.cnf 가 있을 경우 해당 디렉토리에서 읽어야됨 
    - 별도의 경로 지정시 별도로 지정해야됨 
  - --copy-back, --move-back 옵션과 같이 사용하면 xtrabackup 은 --datadir 디렉토리를 참조한다. 
  - 백업을 위해 datadir의 파일시스템 수준에서 READ 및 EXECUTE 권한이 필요함 

--debug-sleep-before-unlock
  - xtrabackup 테스트에서 사용되는 디버그 전용 옵션 
  
--decompress
  - --compress 옵션을 사용하여 백업받은 .qp 확장자의 파일의 압축 해제 
  - --parallel 옵션 사용시 여러 파일을 동시 처리 가능 
  - 압축해제를 위해 qpress 유틸티가 필요하며, 액세스할수 있는 경로에 있어함 
  - 압축 파일을 삭제하기 위해서는 --remove-original 옵션을 사용해야함 
    - xtrabackup 은 압축 파일을 자동으로 삭제 하지 않는다. 

--decrypt
  - --encrypt 옵션을 사용하여 백업한 백업에서 .xbcrypt 확장자 파일을 복호화한다.
  - --parallel 옵션 사용시 여러 파일을 동시 처리 가능 
  - 압축 파일을 삭제하기 위해서는 --remove-original 옵션을 사용해야함 
    - xtrabackup 은 압축 파일을 자동으로 삭제 하지 않는

--defaults-extra-file=[MY.CNF]
  - xtrabackup에서 MySQL 서버와의 통신에 필요한 추가 설정을 지정하는데 사용
  - ex) xtrabackup.cnf 파일에 아래와 같이 설정 
    [client]
    user=username
    password=password
    host=localhost
    port=3306
    - /path/to/xtrabackup.cnf 파일에 저장된 설정을 사용하여 MySQL 서버에 연결하여 백업을 처리한다. 
    xtrabackup --backup --defaults-extra-file=/path/to/xtrabackup.cnf --target-dir=/path/to/backup_directory


--defaults-file=[MY.CNF]
  - my.cnf 파일의 경로를 지정하여 해당 파일에 저장된 설정을 참조한다. 

--defaults-group=GROUP-NAME
  - my.cnf 파일에서 읽어야하는 그룹을 설정 
  - --default-group 옵션은 mysqld_multi 배포에서 사용한다.

--defaults-group-suffix=
  - my.cnf 파일에서 그룹 이름에 접미사를 추가하는 방식을 사용한다. 
    - concat(group, suffix)

--dump-innodb-buffer-pool
  - InnoDB 버퍼풀의 상태를 파일로 덤프하는데 사용 (기본 OFF)

--dump-innodb-buffer-pool-timeout
  - InnoDB 버퍼풀 덤프가 완료되었는지 확인하기 위해 innodb_buffer_pool_dump_status 값을 모니터링 해야하는 시간(초) (기본 10초)

--dump-innodb-buffer-pool-pct
  - InnoDB 버프풀을 덤프할때 사용할 버퍼풀의 비율 
  
--encrypt=ENCRYPTION_ALGORITHM
  - xtrabackup 으로 백업시 지정한 알고리즘으로 압축 

--encrypt-key=ENCRYPTION_KEY
  - --encrypt 옵션을 사용할때 지정한 ENCRYPTION_KEY를 사용하도록 설정 

--encrypt-key-file=ENCRYPTION_KEY_FILE
  - --encrypt 옵션을 사용할때 지정한 ENCRYPTION_KEY_FILE 에 저장된 암호화 키를 사용하도록 설정 

--encrypt-threads=
  - 병렬 암호화/복호화에 사용될 스레드수를 지정
  
--encrypt-chunk-size=
  - 암호호 스레드에 대한 작업 버퍼의 크기 지정(바이트 단위)
  - 암호화 사용시 xbcloud/xbstream 청크 크기를 조정하려면 --encrypt-chunk-size, --read-buffer-size 변수를 모두 조정해야한다. 

--export
  - 테이블을 내보내기 위한 파일을 만든다. 

--extra-lsndir=DIRECTORY
  - 백업시에 이 디렉토리에 xtrabackup_checkpoints, xtrabackup_info 파일의 복사본을 저장한다.

--force-non-empty-directories
  - --copy-back, --move-back 옵션 사용시 대상 디렉토리가 비어있지 않을 경우에도 처리한다.
  - xtrabackup 은 백업을 수생할때 타켓 디렉토리가 비어 있는지 확인하고 처리하는데 --force-non-empty-directories 옵션 지정시 디렉토리가 비어 있지 않아도 백업을 수행한다. 

--ftwrl-wait-timeout=SECONDS
  - xtrabackup 이 실행하기전에 FLUSH TABLES WITH READ LOCK을 차단하는 쿼리를 기다려야 하는 시간(초)를 지정한다. 
  - 제한 시간이 만료된 후에도 여전히 그러한 쿼리가 있는 경우 xtrabackup은 오류와 함께 종료된다. 
  - 기본값은 0이며, 이 경우 쿼리가 완료될 때까지 기다리지 않고 즉시 FLUSH TABLES WITH READ LOCK을 시작한다. 
  
--ftwrl-wait-threshold=SECONDS
  - FLUSH TABLES WITH READ LOCK를 차단하는 장기 실행 쿼리를 식별하기 위한 최소 임계값 (기본값 60초)
  - FLUSH TABLES WITH READ LOCK은 이러한 장기 실행 쿼리가 있을 경우 시작되지 않습니다. 
  - --ftwrl-wait-timeout이 0인 경우 이 옵션은 효과가 없습니다.
  - FLUSH TABLES WITH READ LOCK을 실행 하기 전에 –ftwrl-wait-timeout 설정 만큼 기다렸는데도 –-ftwrl-wait-threshold(기본값 60초)에 설정된 시간 보다 더 오래 실행되는 쿼리가 있으면 백업을 취소시켜 lock 대기로 인한 이슈를 최소화 시킨다.

--ftwrl-wait-query-type=all|update
  - 전역 잠금을 실행하기 전에 완료할 수 있는 쿼리 유형을 정의 
    - xtrabackup은 백업중에 데이터가 변경되지 않도록 전역 잠금을 건다. 
      실행 과정에서 명령문을 만나면 명령문이 완료될 때까지 기다렸다가 전역 잠금을 건다.
  - ALL : 전역 잠금을 실행하기 전에 모든 쿼리가 완료될 때까지 대기 (기본값)
  - SELECT : 전역 잠금을 실행하기 전에 SELECT 문이 완료될 때까지 대기
  - UPDATE : 전역 잠금을 실행하기 전에 UPDATE 문이 완료될 때까지 대기 

--galera-info
  - 백업시의 로컬 노드 상태를 포함하는 xtrabackup_galera_info 파일을 생성한다.
  - Percona XtraDB Cluster 백업할때 사용하는 옵션
  - 백업 잠금을 사용하여 백업을 하는 경우 아무런 효과가 없다. 

--generate-new-master-key
 - copy-back 작업을 할때 새 마스터 키를 생성한다.

--history=name
  - PERCONA_SCHEMA.xtrabackup_history 테이블에서 백업 기록을 추적하도록 하는 옵션 

--incremental
  - 증분 백업시 사용하는 옵션 
  - --incremental-lsn, --incremental-basedir 옵션 지정가능 
  - 두 옵션 모두 지정하지 않으면 백업 기본 디렉토리에 백업 

--incremental-basedir=DIRECTORY
  - 증분 백업시 기본 전체 백업이 저장되는 디렉토리 

--incremental-dir=DIRECTORY
  - 중분 백업시에 전체 백업과 증분 백업을 결합하여 새로운 전채 백업을 저장하는 디렉토리 

--incremental-force-scan
  - 증분 백업을 생성할 때 전체 변경된 페이지 비트맵 데이터를 사용할 수 있는 경우에도 백업에 사용할 인스턴스의 데이터 페이지에 대한 전체 스캔을 강제로 수행한다.

--incremental-history-name=name
  - 중분백업의 기반이 되는 PERCONA_SCHEMA.xtrabackup_history 테이블에 저장되는 백업 시리즈의 이름 지정 

--innodb-checksum-algorithm=name
  - InnoDB 가 페이지 체크섬을 계산하는데 사용하는 알고리즘
  - 사용가능한 알고리즘 
    - CRC32, INNODB, NONE, STRICT_CRC32, STRICT_INNODB, STRICT_NONE

--incremental-history-uuid=UUID
- 중분백업의 기반이 되는 PERCONA_SCHEMA.xtrabackup_history 테이블에 저장되는 백업 시리즈에 UUID 를 지정한다.

--incremental-lsn=LSN
  - 증분백업시에 --incremental-basedir을 지정하는 대신 LSN 을 지정 

--innodb-log-arch-dir=DIRECTORY
  - archived log 디렉토리를 지정 
  - --prepare 옵션과 함께 사용함 
  
--innodb-undo-directory=name
  - undo tablespace 의 경로 

--innodb-undo-tablespace=
  - 사용할 undo tablespace 수 
  
--keyring-file-data=FILENAME
  - 키랑 파일의 경로, --xtrabackup-plugin-dir 옵션과 같이 사용 

--kill-long-queries-timeout=
  - xtrabackup이 FLUSH TABLES WITH READ LOCK 을 시작하고 이를 차단하는 실행중인 쿼리를 종료하는 사이에 대기하는 시간(초) 
  - 기본값은 0초로 쿼리 종료를 하지 않는다. 
  - --kill-long-queries-timeout=60 60초동안 실행 중인 쿼리가 있으면 해당 쿼리를 중단하고 백업을 실행한다. 

--kill-long-query-type=select|all
  - FLUSH TABLES WITH READ LOCK을 차단하는 쿼리를 종료할때 대상 쿼리 유형 지정
  - 기본값 : select 

--lock-ddl
  - 백업시 서버에서 모든 DDL 작업을 차단 

--lock-ddl-per-table
  - xtrabackup이 복사를 시작하기 전과 백업이 완료될 때까지 각 테이블에 대한 DDL을 잠근다. 
  
--lock-ddl-timeout
  - 지정한 시간내에 LOCK TABLES FOR BACKUP 이 되지 않을 경우 백업을 중단한다. 

--log-bin[=name]
  - 로그 시퀀스의 기본 이름 

--log-copy-interval=
  - 로그 복사 스레드 확인 사이의 시간 간격을 밀리초 단위로 지정(기본값 1초)
  
--login-path=
  - 로그인 파일에서 이경로를 읽는다. 

--move-back
  - 백업의 모든 파일을 백업디렉토리에서 원래 위치로 이동

--no-backup-locks
  - FLUSH TABLES WITH READ LOCK 대신 백업 잠금을 사용할지 여부를 설정 
  - 백업 잠금은 서버에서 지원해야 옵션이 적용됨 
  - 기본적으로 활성화되어 있어 --no-backup-locks를 사용하여 옵션을 비활성화한다. 

--no-defaults
  - xtrabackup이 MySQL 설정 파일 (예: my.cnf)의 기본값을 사용하지 않도록하는 옵션

--no-lock
  - InnoDB 테이블을 백업하는 동안 잠금을 설정하지 않도록 하는 옵션 
    - 백업중에 다른 트랜잭션이나 쿼리 실행가능 

--no-version-check
  - xtrabakcup --backup 시 자동 버전확인 비활성화

--open-files-limit=
  - 백업 작업 중에 열린 파일의 제한 설정
    - 백업 작업 중에 동시에 열 수 있는 InnoDB 데이터 파일의 수를 제한

--parallel=
  - 병렬 작업에 사용할 스레드 수 (기본값 1)

--password=PASSWORD
  - 데이터베이스에 연결할 때 사용할 비밀번호 

--prepare
  - 백업본으로 복구 수행 

--print-defaults
  - xtrabackup 실행 시 현재 설정값을 표시
  - xtrabackup이 어떤 설정값을 사용하는지 확인하고 디버깅할 때 유용함
 
--print-param
  - 데이터 파일을 원래 위치로 다시 복사하여 복원할 수 있는 매개 변수를 xtrabackup에서 출력한다. 

--read-buffer-size[=#]
  - 읽기 버퍼 크기 설정 (기본값 10MB)
  - 이 옵션으로 xbcloud/xbstream 청크 크기를 기본값인 10MB에서 조정가능 
  - 암호화를 사용할 때 xbcloud/xbstream 청크 크기를 조정하려면 --encrypt-chunk-size 변수와 --read-buffer-size 변수를 모두 조정해야함 

--rebuild-indexes
  - --prepare 시 로그 적용후 InnoDB 테이블에서 보조 인덱스를 재구축

--rebuild-threads=
  - 백업에서 인덱스를 재구성할 스레드 수를 정의. --prepare 및 --rebuild-index와 함께만 사용한다. 

--redo-log-version=
  - 백업의 redo log 파일의 버전 지정, --prepare 와 함께 사용 

--reencrypt-for-server-id=
  - 복제 복제본이나 Galera 노드와 같이 암호화된 백업을 가져온 서버 인스턴스와 다른 server_id로 서버 인스턴스를 시작할 수 있다.
  - xtrabackup이 새 server_id를 기반으로 ID를 가진 새 마스터 키를 생성하여 키링 파일에 저장한 후 테이블스페이스 헤더 내의 테이블스페이스 키를 다시 암호화한다. 

--remove-original
 - 복호화 및 압축 해제 후 .qp, .xbcrypt 및 .qp.xbcrypt 파일 삭제 

--rsync
  - rsync를 사용하여 InnoDB가 아닌 모든 파일을 복사한다. 
  - --stream과 함께 사용할 수 없다. 

--safe-slave-backup
  - FLUSH TABLES WITH READ LOCK 을 실행하기전에 replica SQL thread 를 중지하고 SHOW STATUS 의 Slave_open_temp_tables가 0이 될때까지 백업 시작을 대기한다. 
  - 열려 있는 임시 테이블이 없으면 백업이 수행되고, 열려 있는 임시 테이블이 없을 때까지 SQL 스레드가 시작되고 중지된다. 
  - --safe-slave-backup-timeout 초 후에 Slave_open_temp_tables가 0이 되지 않으면 백업이 실패한다.
  - 백업이 완료되면 replica SQL thread 가 다시 시작된다.
  - 이 옵션은 임시 테이블 복제를 처리하기 위해 구현되었으며 행 기반 복제에서는 필요하지 않다.
  
--safe-slave-backup-timeout=SECONDS
  - Slave_open_temp_tables가 0이 될 때까지 기다려야 하는 시간(초) (기본값 300초)
  
--secure-auth
  - 클라이언트가 이전(4.1.1 이전) 프로토콜을 사용하는 경우 서버에 대한 클라이언트 연결을 거부한다.
  - 기본적 활성화. 비활성화하려면 --skip-secure-auth 를 사용 

--server-id=
  - 백업 중인 서버 인스턴스

--server-public-key-path=name
  - PEM 형식의 public RSA 키 파일 경로

--skip-tables-compatibility-check
  - 엔진 호환성 경고를 비활성화 


--slave-info
  - 복제본 서버를 백업할때 유용한 옵션
  - 소스 서버의 binlog position 을 출력한다. 
  - xtrabackup_slave_info 파일에 CHANGE MASTER 명령으로 binlog position 을 기록한다. 
    - 백업으로 복제 서버를 시작하고 xtrabackup_slave_info 파일에 저장된 binlog position 으로 CHANGE MASTER 명령을 실행하여 이 소스에 대한 새 복제본을 설정할 수 있다. 

--ssl
  - ssl 연결을 활성화 한다. 

--ssl-ca
  - 신뢰할 수 있는 SSL CA 목록이 포함된 파일의 경로 지정 

--ssl-capath
  - PEM 형식의 신뢰할 수 있는 SSL CA 인증서가 포함된 디렉터리 경로지정 

--ssl-cert
  - PEM 형식의 X509 인증서가 포함된 파일의 경로지정 
  
--ssl-cipher
  - 연결 암호화에 사용할 수 있는 암호화 목록

--ssl-crl
  - 인증서 해지 목록이 포함된 파일의 경로

--ssl-crlpath
  - 인증서 해지 목록 파일이 포함된 디렉터리의 경로

--ssl-key
  - PEM 형식의 X509 키가 포함된 파일의 경로
  
--ssl-mode
  - ssl 모드 지정 

--ssl-verify-server-cert
  - 서버에 연결할 때 사용되는 호스트 이름에 대해 서버 인증서 일반 이름 값을 확인
  
--stats
  - xtrabackup이 지정된 데이터 파일을 스캔하고 인덱스 통계를 출력
  
--stream=name
  - 모든 백업 파일을 지정된 형식의 표준 출력으로 스트리밍. 현재 지원되는 형식은 xbstream, tar
  
--tables=name
  - 부분백업시사용 이름이 일치하는 테이블만 백업 

--tables-compatibility-check
  - 엔진 호환성 경고 활성화

--tables-exclude=name
  - 백업에서 제외할 테이블명

--tables-file=name
  - 백업 대상 테이블을 특정 파일에서 읽어올 때 사용, 파일명 지정 

--target-dir=DIRECTORY
  - 백업을 저장할 디렉토리 지정 
  - 디렉터리가 존재하지 않으면 디렉터리를 생성한다.
    - 디렉터리가 존재하고 비어 있으면 백업성공
    - 파일이 있을 경우 운영 체제 오류 17가 발생하고 백업 실패 
  - 백업을 수행하려면 --target-dir 값으로 제공하는 디렉터리에 대한 파일 시스템 수준의 READ, WRITE 및 EXECUTE 권한이 필요하다. 

--throttle=
  - 초당 복사되는 청크 수를 제한합니다. 청크 크기는 10MB
  - 대역폭을 10MB/s로 제한하려면 옵션을 1: --throttle=1로 설정한다. 

--tls-version=name
  - tls 버전 지정 (TLSv1, TLSv1.1, TLSv1.2)

--to-archived-lsn=LSN
  - 로그를 적용할 LSN을 지정, --prepare 옵션과 함께 사용 

--transition-key
  - 옵션 사용시 키링 볼트 서버에 엑세스 하지 않고도 백업 처리 가능
    -  xtrabackup은 지정된 암호문에서 AES 암호화 키를 가져와 백업 중인 테이블 공간의 테이블 공간 키를 암호화하는데 사용한다. 

--user=USERNAME
  - MySQL DB 계정 지정   

--version
  - xtrabackup 버전 출력 

--xtrabackup-plugin-dir=DIRNAME
  - 키링 플러그인이 포함된 디렉터리의 절대 경로

MySQL 서버의 CPU를 알뜰하게 다 사용해서 사용률이 100% 를 친다면?

MySQL은 쓰레드로 동작한다. CPU 사용률이 가장 높은 쓰레드를 찾아서 performance_schema.threads  에서 THREAD_OS_ID로 조회할 수 있다. 

pidstat 를 사용해서 CPU 사용률이 높은 쓰레드를 찾아본다. 

pidstat -t -p <mysqld_pid> 1  # 1 은 1초 간격으로 실행 

-- mysqld 의 pid 확인 
# ps -ef |grep mysqld
mysql        955       1  0 22:21 ?        00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
root        1622    1573  0 22:22 pts/1    00:00:00 grep --color=auto mysqld

# pidstat -t -p 955 1
-bash: pidstat: command not found

-- pidstat가 없을 경우 sysstat를 설치한다. 
# yum -y install sysstat

-- CPU 사용률이 가장 높은 TID 확인 
# pidstat -t -p 955 1
Linux 4.18.0-305.19.1.el8_4.x86_64 (localhost.localdomain) 	10/05/2022 	_x86_64_	(1 CPU)

10:24:46 PM   UID      TGID       TID    %usr %system  %guest   %wait    %CPU   CPU  Command
10:24:47 PM    27       955         -    1.98    0.99    0.00    0.00    2.97     0  mysqld
10:24:47 PM    27         -       955    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       959    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       963    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       965    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       966    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       967    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       968    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       969    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       970    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       971    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       972    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       973    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       974    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       994    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       995    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       996    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       997    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       998    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -       999    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1000    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1001    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1015    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1016    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1017    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1018    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1053    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1057    0.00    0.00    0.00    0.00    0.00     0  |__mysqld
10:24:47 PM    27         -      1564    2.97    0.99    0.00    0.99    3.96     0  |__mysqld

CPU 사용률이 가장 높은 TID 1565 번을 확인한다. 

-- THREAD_OS_ID = 1564 확인 
mysql> select * from performance_schema.threads where THREAD_OS_ID=1564\G;
*************************** 1. row ***************************
          THREAD_ID: 28
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 3
   PROCESSLIST_USER: root
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: test
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 101
  PROCESSLIST_STATE: query end
   PROCESSLIST_INFO: select * from tb_test where col1 = 'aaaa'
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 1564
1 row in set (0.00 sec)

ERROR: 
No query specified

-- PROCESSLIST_ID = 3 kill 
mysql> kill 3;
Query OK, 0 rows affected (0.01 sec)

show engine innodb status 의 TRANSACTIONS 부분에서 ACTIVE TRANSACTION 으로 Lock 을 잡고 있는 쿼리를 찾아야될때가 있다. 

회사에서 가장 많이 사용중인 5.7 버전부터 테스트해 본다. 

MySQL 5.7 테스트

mysql> create table tb_test (
    -> col1 int not null primary key,
    -> col2 varchar(10) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tb_test values (1, 'aaa');
Query OK, 1 row affected (0.03 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_test set col2 = 'bbb' where col1 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ACTIVE TRANSACTION은 확인되지만 어떤 쿼리인지는 확인이 안된다. 

mysql> show engine innodb status;
...
------------
TRANSACTIONS
------------
Trx id counter 1289
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421405712030432, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1288, ACTIVE 80 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 139930706618112, query id 65 localhost root

mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits;
Empty set, 1 warning (0.00 sec)

mysql> SELECT 
    ->   straight_join
    ->   w.trx_mysql_thread_id waiting_thread,w.trx_id waiting_trx_id,w.trx_query waiting_query,b.trx_mysql_thread_id blocking_thread,
    ->   b.trx_id blocking_trx_id,b.trx_query blocking_query,bl.lock_id blocking_lock_id,bl.lock_mode blocking_lock_mode,
    ->   bl.lock_type blocking_lock_type,bl.lock_table blocking_lock_table,bl.lock_index blocking_lock_index,wl.lock_id waiting_lock_id,
    ->   wl.lock_mode waiting_lock_mode,wl.lock_type waiting_lock_type,wl.lock_table waiting_lock_table,wl.lock_index waiting_lock_index
    -> FROM information_schema.INNODB_LOCK_WAITS ilw
    -> INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = ilw.blocking_trx_id
    -> INNER JOIN information_schema.INNODB_TRX w ON w.trx_id = ilw.requesting_trx_id
    -> INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = ilw.blocking_lock_id
    -> INNER JOIN information_schema.INNODB_LOCKS wl ON wl.lock_id = ilw.requested_lock_id;
Empty set, 3 warnings (0.00 sec)

다른 세션에서 Lock Wait 상황을 발생시키면?

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_test set col2 = 'bbb' where col1 = 1;

Lock Wait 가 발생하면 확인이 된다.

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1289              | 1289:23:3:2       | 1288            | 1288:23:3:2      |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from information_schema.INNODB_TRX;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                      | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 1289   | LOCK WAIT | 2022-10-01 15:39:28 | 1289:23:3:2           | 2022-10-01 15:40:35 |          2 |                  10 | update tb_test set col2 = 'bbb' where col1 = 1 | starting index read |                 1 |                 1 |                2 |                  1136 |               2 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 1288   | RUNNING   | 2022-10-01 15:31:45 | NULL                  | NULL                |          3 |                   8 | NULL                                           | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 1 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> SELECT 
    ->   straight_join
    ->   w.trx_mysql_thread_id waiting_thread,w.trx_id waiting_trx_id,w.trx_query waiting_query,b.trx_mysql_thread_id blocking_thread,
    ->   b.trx_id blocking_trx_id,b.trx_query blocking_query,bl.lock_id blocking_lock_id,bl.lock_mode blocking_lock_mode,
    ->   bl.lock_type blocking_lock_type,bl.lock_table blocking_lock_table,bl.lock_index blocking_lock_index,wl.lock_id waiting_lock_id,
    ->   wl.lock_mode waiting_lock_mode,wl.lock_type waiting_lock_type,wl.lock_table waiting_lock_table,wl.lock_index waiting_lock_index
    -> FROM information_schema.INNODB_LOCK_WAITS ilw
    -> INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = ilw.blocking_trx_id
    -> INNER JOIN information_schema.INNODB_TRX w ON w.trx_id = ilw.requesting_trx_id
    -> INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = ilw.blocking_lock_id
    -> INNER JOIN information_schema.INNODB_LOCKS wl ON wl.lock_id = ilw.requested_lock_id;
+----------------+----------------+------------------------------------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+-----------------+-------------------+-------------------+--------------------+--------------------+
| waiting_thread | waiting_trx_id | waiting_query                                  | blocking_thread | blocking_trx_id | blocking_query | blocking_lock_id | blocking_lock_mode | blocking_lock_type | blocking_lock_table | blocking_lock_index | waiting_lock_id | waiting_lock_mode | waiting_lock_type | waiting_lock_table | waiting_lock_index |
+----------------+----------------+------------------------------------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+-----------------+-------------------+-------------------+--------------------+--------------------+
|             10 | 1289           | update tb_test set col2 = 'bbb' where col1 = 1 |               8 | 1288            | NULL           | 1288:23:3:2      | X                  | RECORD             | `test`.`tb_test`    | PRIMARY             | 1289:23:3:2     | X                 | RECORD            | `test`.`tb_test`   | PRIMARY            |
+----------------+----------------+------------------------------------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+-----------------+-------------------+-------------------+--------------------+--------------------+
1 row in set, 3 warnings (0.00 sec)

5.7 에서는 Lock Wait 이 발생해야 Lock 정보 확인이 가능하다.

즉 Lock 경합이 없으면 어떤 쿼리가 Lock 잡고 있는지 확인이 안된다. 

아하하하 미친다. (왜 미치냐면 이게 문제가 되고 있어서.)

MySQL 8.0 테스트

mysql> create table tb_test (
    -> col1 int not null primary key,
    -> col2 varchar(10) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tb_test values (1, 'aaa');
Query OK, 1 row affected (0.03 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_test set col2 = 'bbb' where col1 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ACTIVE TRANSACTION, Lock 쿼리 확인 

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 7713
Purge done for trx's n:o < 7710 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422006973530112, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422006973528496, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422006973527688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 7712, ACTIVE 26 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 140531536729856, query id 26 localhost root

mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 140531996818648:1083:140532001090368   |                  7712 |        48 |       32 | test          | tb_test     | NULL           | NULL              | NULL       |       140532001090368 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 140531996818648:22:4:2:140532001087456 |                  7712 |        48 |       32 | test          | tb_test     | NULL           | NULL              | PRIMARY    |       140532001087456 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)

mysql> SELECT straight_join
    ->   dl.thread_id,est.sql_text,dl.object_schema,dl.object_name,dl.index_name,
    ->   dl.lock_type,dl.lock_mode,dl.lock_status,dl.lock_data
    -> FROM performance_schema.data_locks dl 
    -> INNER JOIN performance_schema.events_statements_current est ON dl.thread_id = est.thread_id
    -> ORDER BY est.timer_start,dl.object_instance_begin;
+-----------+------------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| thread_id | sql_text                                       | object_schema | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+-----------+------------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|        48 | update tb_test set col2 = 'bbb' where col1 = 1 | test          | tb_test     | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
|        48 | update tb_test set col2 = 'bbb' where col1 = 1 | test          | tb_test     | NULL       | TABLE     | IX            | GRANTED     | NULL      |
+-----------+------------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

8.0 에서는 Lock Wait 이 발생하지 않아도 Lock 을 잡고 있는 쿼리 확인이 잘된다. 

SELECT
  t.TABLE_SCHEMA AS `schema`,
  t.TABLE_NAME AS `table`,
  t.AUTO_INCREMENT AS `auto_increment`,
  c.DATA_TYPE AS `pk_type`,
  (t.AUTO_INCREMENT / (CASE DATA_TYPE
                         WHEN 'tinyint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 255, 127)
                         WHEN 'smallint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 65535, 32767)
                         WHEN 'mediumint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 16777215, 8388607)
                         WHEN 'int' THEN IF(COLUMN_TYPE LIKE '%unsigned', 4294967295, 2147483647)
                         WHEN 'bigint' THEN IF(COLUMN_TYPE LIKE '%unsigned', 18446744073709551615, 9223372036854775807)
                         END / 100)) AS `max_value`
FROM information_schema.TABLES t
INNER JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME 
WHERE t.AUTO_INCREMENT IS NOT NULL
AND c.COLUMN_KEY = 'PRI'
AND c.DATA_TYPE LIKE '%int';

MySQL 5.7.20 버전부터 deprecate 되었고 8.0 에서는 사라졌다. 

쿼리를 Query Cache 에서 조회해보고 Query Cache에 있다면 Parsing, Optimizing, Executing 을 거치지 않고 결과를 반환한다. 

좋지 않은가?

근데 왜 없어졌을까?

Query Cache 에서는 아래의 두 쿼리가 다르다고 판단한다. 

SELECT * FROM TABLE

select * from table

Query Cache 에서 쿼리가 동일한 것으로 인식되기 위해서는 대소문자, 바이트 까지 동일해야한다. 

쿼리 스트링이 같다고 해도 데이터베이스, 프로토콜버전, 디폴트 문자셋 등이 다른 경우도 다른 쿼리로 판단한다. 

Query Cache 에 저장되어 있는 쿼리 결과의 대상 테이블의 변경 (INSERT, UPDATE, DELETE, ALTER TABLE, TRUNCATE 등등)이 발생할 경우 캐시에서 제거한다. 이때 다른 쓰레드에서 이 데이터를 참조하지 못하도록 Lock을 건다. 

(Query Cache는 여러 세션이 공유하는 부분으로 동기화를 위해 Query Cache Lock을 건다.)

Query Cache Lock 이 풀릴때까지 Query Cache에 접근하는 쓰레드들은 "Waiting for query cache lock" 대기가 걸린다. 

테이블 변경이 자주 발생하는 경우 Query Cache 를 사용하는 쿼리들의 대기 시간이 많아진다. 

이런 문제 때문에 사라진듯하다. 

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

갑자기 생각이 안날때가 있어서 정리해본다. 

- IGNORE INDEX : 특정 인덱스 사용안함

- USE INDEX

  - 옵티마이저에게 지정한 인덱스 사용 권장 

  - 권장이기 때문에 Table Scan 이더 빠르면 Table Scan 을 할수도 있다. 

- FORCE INDEX 

  - 인덱스 사용 강제

  - 인덱스를 사용하지 못하는 경우에만 해당 인덱스를 사용하지 않고 다른 플랜으로 처리 

  - Table Scan 이 더 성능이 좋아도 인덱스 사용 

참고 : https://dev.mysql.com/doc/refman/8.0/en/index-hints.html

mysql 5.7 부터 performance_schema.metadata_locks 가 추가 되었으며 
이를 조회해서 metadata lock 을 잡고 있는 세션 확인이 가능하다.

auto_commit = false 상태로 쿼리를 하다보면 본인도 모르게 metadata lock 을 잡을 수 도 있다. 

예를 들면 python 에서 pymysql 로 접속시 기본 설정이 auto_commit = false 로 되어 있다. 

metadata lock 조회를 위한 선행 작업 

-- 기본 설정이 YES 지만 확인해본다.
SELECT * FROM performance_schema.setup_consumers WHERE NAME = 'global_instrumentation';

-- ENABLED = YES 가 아닐 경우 업데이트 
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation';

-- MySQL 8.0 에서는 기본설정이 ENABLED = YES 지만 
-- 5.7 에서는 NO 로 되어 있어 확인이 필요하다.
SELECT * FROM performance_schema.setup_instruments WHERE NAME = 'wait/lock/metadata/sql/mdl';

-- ENABLED = YES 가 아닐 경우 업데이트 
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl';

metadata lock 조회

SELECT 
  OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS,
  THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO
FROM performance_schema.metadata_locks
INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID
WHERE PROCESSLIST_ID <> CONNECTION_ID();

1. 컬럼 타입은 text 가 아닌 json 컬럼 타입을 사용한다. 

    text 타입을 사용할 경우 json 문법을 안지켜서 나중에 문제가 되는 경우가 발생한다. 

2. json 내부의 속성중 업데이트가 자주 발생하는 경우 컴럼 전체 update를 하지 않고 

   json 함수를 사용하여 업데이트할 속성만 업데이트한다. 

   json 전체를 업데이트할 경우 binlog 증가 이슈가 발생한다. 

3. json 내부의 속성값을 조회조건으로 사용하는 경우 가상컬럼을 만들어서(인덱스 생성 필수) 사용한다. 

   json 을 그냥 검색할 경우 full scan 이슈가 발생한다.