Mysql(3)

오늘 배운 것

3주차 학습 내용. (4.4 MySQL 로그파일 ~ 5.4.4 SERIALIZABLE)

4.4 MySQL 로그 파일

로그파일을 쓰면 서버 깊은지식이 없어도 뭐가 문제인지 잘 알수있다.

4.4.1 에러 로그 파일

mysql이 실행되는 도중에 발생하는 에러, 경고 메시지가 출력되는 로그파일.

위치는

  1. my.cnf설정파일에서 log_error 이름의 파라미터에 있는 경로에 생성됨.
  2. 별도 정의x면 datadir파라미터에 설정된 데이터 디렉터리에 .err라는 확장자가 붙은 파일로 생성됨. 그 메시지의 종류는 4.4.1.1~4.4.1.6이 보통 있다.

4.4.1.1 MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지

설정파일을 변경했거나, 비정상 종료 후 재시작 하는 경우 에러로그파일을 통해 확인해야 한다.

mysqld: ready for connections 와 같이 정상적으로 기동했고, 에러, 경고성 메시지가 없다면 잘 적용된 것이다.

만약 특정 변수가 ignore,된 경우는 서버는 정상적으로 기동하지만 해당 파라미터는 적용 안된거다. 변수명을 인식못하거나, 파라미터의 내용을 인식하지 못하면 에러메시지가 뜰거다.

4.4.1.2 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지

InnoDB는 mysql서버가 비정상적, 강제로 종료될 경우 재시작하면서 완료되지 못한 트랜잭션을 정리하고, 디스크에 기록되지 못한 데이터가 있다면 다시 하는 재처리 작업을 한다. 이 과정에 대한 메시지가 출력되곤 하는데, 문제가 있어서 복구되지 못한다면 mysql은 다시 종료된다. 이 과정의 문제는 어려워서 innodb_force_recovery 파라미터를 0보다 큰 값으로 설정하고 재시작 해야만 한다. 자세한건 지난주 내용인 4.2.6 을 참고하라.

4.4.1.3 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지

쿼리 도중 발생하는 에러는 사전 예방이 어렵다. 주기적으로 에러로그파일을 검토하는 과정에서 알게된다. 자주 에러 로그 파일을 검토하라.

쿼리 도중 발생하는 에러 사전예방이 어려운 이유

운영환경은 동적인환경이라 데이터 상태와 값을 예측하기 어렵다.

동시성문제도 대비하기 어렵다.

자원문제로 발생할 수도 있다.

4.4.1.4 비정상적으로 종료된 커넥션 메시지(Aborted Connection)

어떤 db서버의 로그파일을 보면 이 메시지가 누적된 경우가 많다. 클라이언트 애플리케이션에서 정상적으로 접속종료륾 못하고 종료되면 이런 메시지가 생긴다.

물론 네트워크 문제로 끊어져도 생긴다. 이게 너무 많이 생기면 애플리케이션의 커넥션 종료 로직을 검토할 필요가 있다. max_connect_errors 시스템 변수값이 너무 낮다면 클라이언트가 접속하지 못하고 Host ‘host_name’ is blocked 라는 에러가 발생할 수 있다. 이 메시지는 클라이언트 호스트에서 발생한 에러(커넥션 실패, 강제연결종료)가 max_connect_errors변수 값을 넘으면 발생한다.

4.4.1.5 InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS같은)의 결과메시지

Innodb의 테이블 모니터링이나 락 모니터링, innodb의 엔진상태를 조회하는 명령은 상대적으로 메시지가 큰 상태로 로그파일에 저장된다. 모니터링을 사용하면 다시 비활성화 하여 에러 로그파일의 크기를 작게 해야한다.

4.4.1.6 MySQL의 종료 메시지

가끔 mysql이 종료되어잇거나, 아무도 모르게 재시작 되는 경우가 있다. 에러로그파일을 보는것이 유일한 확인 방법이다. 누가 껐으면 Received SHUTDOWN from user…. 같이 뜨지만 스택 트레이스(16진수 주소값이 잔뜩 출력되는) 가 출력된다면 세그먼테이션 폴드로 비정상적으로 종료된 것으로 판단한다. 이 경우, 스택 트레이스 내용을 최대한 참조하여 mysql 버전을 업그레이드 하거나 회피책을 찾아야 한다.

왜냐하면 세그폴트라는게 C/C++ 로 정교하게 작성된 MySQL에서 발생하기 어려운 에러인데 이게 났다는건 메모리를 잘 못다루는 버그가 있는거임. 그래서 버전업그레이드.

4.4.2 제너럴 쿼리 로그 파일(제너럴 로그 파일)

가끔 서버에서 실행되는 쿼리가 뭐뭐 있는지 조사할 때가 있다. 이때는 쿼리로그를 활성화 해서 쿼리를 쿼리로그파일로 기록하게 한 다음, 그 파일을 검토한다. 슬로우 쿼리로그랑은 달리 실행되기 전에 myssql이 쿼리요청을 받으면 바로 기록하기대문에 쿼리 실행중 에러가 생겨도 기록한다.

쿼리 로그파일의 경로는 general_log_file이란 파라미터에 설정되어있다.

파일이 아닌 테이블에 저장하게 설정한다면, 아래와 같이 해야한다.

SHOW GLOBAL VARIABLES LIKE 'general_log_file'
결과는 테이블처럼 나오고
 variable_name | value
 general_log_file | /usr/local/mysql/data/localhost_matt.log

쿼리 로그를 파일, 테이블 저장여부는 log_output 파라미터로 결정된다.

4.4.3 슬로우 쿼리 로그

mysql서버의 쿼리튜닝은 서비스 적용 이전에 전체적인 튜닝과 운영중 성능저하 검사와 정기적인점검을 위한 튜닝으로 나뉜다.

전자는 검토해야할 대상 쿼리가 전부라서 모두 튜닝하면 되지만 후자는 어떤 쿼리가 문제의 쿼리인지 판단하기 어렵다.

이 경우, 서비스의 쿼리중 어떤 쿼리가 문제인지 보는데 슬로우 쿼리로그가 도움이 된다.

4.4.3.1 슬로우 쿼리 통계

4.4.3.2 실행 빈도 및 누적 실행 시간순 랭킹

4.4.3.3 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보

5 트랜잭션과 잠금

트랜잭션, 락, 트랜잭션의 격리수준은 MySQL의 동시성에 영향을 미친다.

  • 트랜잯션
    • 작업의 완전성을 보장해 준다. 논리적인 작업 셋을 모두 완벽하게 처리하거나, 복구해서 작업의 일부만 적용되지 못하게 한다.(partial update 방지)

잠금과 트랜잭션은 비슷한 개념같지만, 잠금은 동시성을 제어 하기 위한 기능, 트랜잭션은 데이터의 정합성 을 보장하기 위한 기능이다.

격리수준은 하나의 트랜잭션 내, 여러 트랜잭션간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

5.1 트랜잭션

여전히 MyIsam, MEMORY 스토리지 엔진이 더 빠르다고, InnoDB는 복잡하고 번거롭다 생각하는 사람들이 많지만 이런 트랜잭션을 지원하지 않는 엔진의 테이블이 더 많은 고민거리를 만들어낸다.

5.1.1 MySQL에서의 트랜잭션

트랜잭션이 꼭 여러 변경작업을 수행하는 쿼리가 조합 됐을 때 의미있는것이 아니다. 쿼리의 수에 상관없이 단순히

  1. 작업이 100% 적용되었거나 (COMMIT)
  2. 아무것도 적용되지 않음 (ROLLBACK, 트랜잭션을 ROLLBACK하는 오류 발생시)

을 보장하는 것이다.

예시를 통해 보면, MyISAM, MEMORY는 Partial UPdate가 발생하여 재처리 작업이 필요하다. 게다가 쿼리가 여러개라면 더 복잡하다.

5.1.2 주의사항

트랜잭션도 DBMS의 커넥션과 같이 최소한 적용하는게 좋다. 일반적으로 DB커넥션은 갯수가 정해져있으므로 각 단위 프로그램이 소유하는 시간이 길어질수록 여유 커넥션이 감소한다. -> 성능 병목

특히, 네트워크 작업을 하는 경우에는 반드시 트랜잭션에서 배제해야한다. 엄청난 병목

5.2 MySQL엔진의 잠금

크게

  1. 스토리지 엔진 레벨의 잠금
  2. MySQL 엔진 레벨의 잠금

으로 나뉜다. MySQL엔진이란 곧 스토리지 엔진을 제외한 나머지 부분을 말한다. MySQL엔진레벨의 잠금은 모든 스토리지 엔진에 영향을 끼치나, 스토리지 엔진 레벨의 잠금은 스토리지 엔진간 상호 영향을 주지 않는다.

흔히 떠올리는 테이블락 이외에도 다양한 락을 제공한다.

5.2.1 글로벌 락

FLUSH TABLES WITH READ LOCK

명령으로 획득가능, 가장 범위가 큰 락. 한 세션에서 이걸 획득하면 다른 세션에서 SELECT를 제외한 대부분의 ddl, dml을 실행하는 경우 글로벌락이 해제될 때까지 그 문장이 대기상태로 남는다.

영향범위는 mysql 서버 전체. 작업대상 테이블, db가 다르더라도 동일한 영향을 미친다.

MyISAM, MEMORY가 종종 사용되는 이전버전에서는 글로벌락을 썼으나, 점점 Innodb사용이 보편화되면서 InnoDB는 트랜잭션을 지원하므로 일관된 데이터 상태를 위해 모든 데이터 변경작업을 멈출 필요는 없다. 또한 8.0부터 아예 기본이 되면서 조금 더 가벼운 글로벌락 필요성이 생겼다. -> 백업락

특정 세션에서 백업락을 획득하면 모든 세션에서 다음과 같이 테이블 스키마나 사용자 인증관련 정보를 변경할 수 없게된다.

  1. 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제
  2. REPAIR TABLE과 OPTIMIZE TABLE 명령
  3. 사용자 관리 및 비밀번호 변경

하지만, 백업락은 일반적인 테이블의 데이터 변경은 허용된다.

중요, 자주 나오는 개념

소스서버 vs 레플리카 서버

일반적인 Mysql서버의 구성은 소스서버와 레플리카 서버로 구성되는데, 백업은 주로 레플리카 서버에서 실행된다. 하지만 백업이 FLUSH TABLES WITH READ LOCK 명령어로 글로벌 락을 획득하면 복제는 백업 시간만큼 지연될 수 밖에 없다. 레플리카 서버에서 백업을 실행하는 도중에 소스서버에 문제가 생기면 레플리카 서버의 데이터가 최신 상태가 될때까지 서비스를 멈춰야 할 수도 있다. 물론 XtraBackup, Enterprise Backup툴은 복제가 진행되는 상태에서도 일관된 백업을 만들 수도 있다. 근데 이 툴이 실행되는 도중에 스키마 변경이 실행되면 백업은 실패한다.

6~7시간동안 백업을 하고있는데, 갑자기 DDL명령 하나로 백업이 실패하면 다시 그만큼 시간을 들여서 백업을 해야한다. 그래서 백업락이 도입되었으며 정상적으로 복제는 실행되지만, 백업의 실패를 막기위해 DDL명령이 실행되면 복제를 일시 중지하는 역할을 한다.

추가설명 : 소스서버로부터의 복제는 UPDATE니까 DML인데, 백업락은 이거는 허용 하고, 스키마 변경같은 DDL은 차단한다.

5.2.2 테이블 락

Innodb도 테이블락을 명시적으로 획득 할 수 있다. 명시적 테이블락도 특별한 상황 아니면 크게 쓸 일이 없다. 글로벌락과 비슷하게 온라인 작업에 영향이 크므로.

묵시적 테이블락은 Myisam, memory에서 쓰인다.

innodb는 스토리지 엔진 차원에서 레코드 기반의 잠금을 제공하므로, 단순 데이터 변경 쿼리로 묵시적인 테이블 락이 설정되지는 않는다.

더 정확히는, 테이블 락이 설정되지만, 대부분의 DML쿼리에서는 무시되고 스키마를 변경하는 DDL에만 영향을 미친다.

5.2.3 네임드 락

임의의 문자열에 대해 잠금을 설정한다.

특징은

  • 대상이 테이블이나 레코드, AUto_INCREMENT와 깥은 db객체가 아니라는 것.
  • 단순히 사용자가 지정한 문자열에 거는 락.

자주 안쓴다.

5.2.4 메타데이터 락

데이터베이스 객체(테이블, 뷰)의 이름이나 구조를 변경하는 경우에 획득하는 락이다.

명시적으로 획득, 해제하는것이 아니고,

RENAME TABLE tab_1 TO tab_2

와 같이 자동으로 획득하는 잠금이다.

5.3 InnoDB 스토리지 엔진 잠금

앞서 나온 락들은 MySQL 엔진의 잠금으로서, 모든 스토리지 엔진에 일괄적용된다. InnoDB는 MYSQL 락과 별개로 레코드 기반 락을 탑재하고 있다.

그러나 이원화된 잠금 탓에 Innodb락 정보는 Mysql 명령을 통해 접근하기 까다로웠지만 요즘은 또 방식이 생겼다.

5.3.1 InnoDB 스토리지 엔진의 잠금

레코드 기반의 잠금을 제공하며, 잠금정보가 상당히 작은 공간으로 관리되므로 레코드-페이지락-테이블락으로 레벨업(락 에스컬레이션)은 없다. 또 특이한게 record, record사이의 간격을 잡그는 GAP 락도 존재한다.

5.3.1.1 레코드 락

레코드 자체가 아니라 인덱스의 레코드를 잠근다.

인덱스가 하나도 없어도 InnoDB 는 클러스터링 인덱스가 자동 생성되서.

많은 사용자가 간과하지만, 레코드를 잠그느냐, 인덱스를 잠그느냐는 큰 문제라서 다음에 다시 다룬다.

따라서 InnoDB에서 보조인덱스를 이용한 변경작업은 next key 락 또는 GAP lock을 사용하지만, PK, Unique Index에 의한 변경작업에서는 GAP은 안하고 레코드만 락을 건다.

5.3.1.2 갭 락

레코드와 인접한 레코드 사이의 간격을 잠근다. 그 사이에 새로운 레코드가 insert 되는 것을 방지한다. 이 자체보다는 넥스트키락의 일부로 사용된다.

5.3.1.3 넥스트 키 락

쉽게 보면 레코드락 + 갭락

= 특정 인덱스 레코드와 그 레코드 바로 앞의 빈 공간까지 함께 잠근다.

목적 : 바이너리 로그에 기록되는 쿼리가 레플리카 서버에서 실행될때 소스서버에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다.

STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL서버에서는 REPEATABLE_READ 격리수준을 사용해야한다.

그런데 이걸로 인해 데드락과 다른 트랜잭션을 기다리게 만드는 일이 자주 일어난다. 가능하다면 바이너리 로그 포맷을 ROW형태로 바꿔서 넥스트 키 락이나 갭락을 줄이는게 좋다.

포맷락

5.3.1.4 자동 증가 락

자동 증가하는 숫자 값을 위해 AUTO_INCREMENT라는 칼럼 속성을 제공한다. 그런데 동시에 여러 레코드가 insert되는 경우, 중복되지 않도록 하는게 필요하다. insert, replace와 같이 새로운 레코드를 저장하는 쿼리에서만 사용되고, update, delete에서는 안걸린다. 명시적으로 할 수 없고 자동으로 짧은시간동안 걸리며 작동 방식을 변경할 수 있다.

5.3.2 인덱스와 잠금

레코드락 부분에서 보면, InnoDB의 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리된다. 그래서, 변경해야할 레코드를 찾기위해 검색한 인덱스의 레코드를 모두 락을 걸어야 한다.

이게 왜 중요하냐면, update를 해서 레코드 하나만 변경하는데도 수십, 수백개의 레코드도 함께 잠긴다. 만약 인덱스가 하나도 없다면, 풀스캔을 하는데 이 과정에서 마주친 모두가 잠긴다. 그래서 인덱스설계가 매우 중요하다.

5.3.3 레코드 수준의 잠금 확인 및 해제

레코드 수준의 잠금은 테이블 수준의 잠금보다 더 복잡하다. 문제 발생시 더 자세히 파봐야 해서 더 그렇다.

5.1버전부터는 레코드 잠금에 대한 메타정보가 제공되어서 레코드 잠금과 잠금대기에 대한 조회가 가능하다. 쿼리 하나만 실행해 보면 잠금과 잠금대기를 바로 확인할 수 있다.

5.4 MySQL의 격리 수준

트랜잭션의 격리수준이란 여러 트랜잭션이 동시에 처리될때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.

5.4.1 READ UnCOMMITED

5.4.2 READ COMMNITED

5.4.3 REPEATABLE READ

5.4.4 SERIALILZABLE

results matching ""

    No results matching ""