책너두 (Real MySQL 8.0 1권) 13일차 (~159p)

요약

• 슬로우 쿼리 로그에 대한 내용과 쿼리 튜닝을 위한 여러 지표에 대한 내용을 알게 됨.
• 트랜잭션과 잠금에 대한 내용을 이해하게 됨.
• MySQL 에서 InnoDB 와 MyISAM, MEMORY 스토리지 엔진에 따른 트랜잭션 여부 및 예시를 통해 트랜잭션을 통한 데이터 정합성의 중요성을 이해하게 됨.
• 트랜잭션의 범위를 최소화 하고, 네트워크 통신이 필요한 지연이 있을 경우, 해당 부분을 트랜잭션에서 반드시 제외하자는 가이드를 알게 됨.

발췌

• 잠금(Lock) 은 동시성을 제어하기 위한 기능이다.
• 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.
• 프로그램의 코드에서 라인 수는 한두 줄이라고 하더라도 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 한다.

메모

슬로우 쿼리 로그

• MySQL 서버의 쿼리 튜닝은 크게 아래와 같이 나눌 수 있음.
  • 서비스가 적용되기 전에 전체적으로 튜닝하는 경우
    • 검토해야할 대상 쿼리가 전부라서 모두 튜닝하면 됨.
  • 서비스 운영중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝
    • 어떤 쿼리가 문제인지 판단하기 상당히 어려움
      • 이 경우, 슬로우 쿼리 로그가 상당히 많은 도움이 됨.
• 슬로우 쿼리 로그 파일에 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록됨.
• 슬로우 쿼리 로그는 실제 소요 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를 판단함.
  • 따라서, 반드시 쿼리가 정상적으로 실행이 완료돼야 슬로우 쿼리에 기록될 수 있음.
• log_output 옵션을 이용해 슬로우 쿼리 로그 파일로 기록할지 테이블로 기록할지 선택할 수 있음.
  • TABLE 로 설정하면 제너럴 로그나 슬로우 쿼리 로그를 mysql DB 의 테이블 (general_log 와 slow_log 테이블) 에 저장한다.
    • TABLE 로 설정하더라도 mysql DB 의 slow_log 테이블과 general_log 테이블은 CSV 스토리지 엔진을 사용하므로 결국, CSV 파일로 저장하는 것과 동일하게 작동함.
  • FILE 로 설정하면 로그의 내용을 디스크의 파일로 저장한다.

  # Time : 2020-07-19T15:44:22.178484+09:00
  # User@Host: root[root] @ localhost [] Id:    14
  # Query_time: 1.180245  Lock_time: 0.002658 Rows_sent: 1. Rows_examined: 2844047
  use employees;
  SET timestamp=1595141060;
  select emp_no, max(salary) from salaries;

  • 실제 슬로우 쿼리 로그 파일은 위와 같이 출력됨. (슬로우 쿼리가 파일로 기록도니 것의 일부를 발췌한 내용)
  • MySQL 잠금 처리는
    • MySQL 엔진 레벨
    • 스토리지 엔진 레벨
    • 두 가지 레이어로 처리됨.
  • MyISAM 이나 MEMORY 스토리지 엔진은 별도의 스토리지 엔진 레벨의 잠금이 없음.
  • InnoDB 는 MySQL 엔진 레벨의 잠금과 스토리지 엔진 자체 레벨의 잠금을 가지고 있음.
    • 그래서 슬로우 쿼리 로그에 출력되는 내용이 혼란스러울 수 있음.
  • ‘Time’ 항목은 쿼리가 종료된 시점을 의미함.
    • 쿼리가 언제 시작됐는지 확인하려면 ‘Time’ 항목에 ‘Query_time’ 만큼 뺴야한다.
  • ‘User@Host’ 는 쿼리를 실행한 사용자 계정임.
  • ‘Query_time’ 은 쿼리가 실행되는 데 걸린 전체 시간을 의미함.
    • ‘Lock_time’ 은 MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간만 표현함.
      • 이 값이 0이 아니라고 무조건 잠금대기가 있었다고 판단하기는 어려움
      • 실제 쿼리가 실행되는 데 필요한 잠금 체크와 같은 코드 실행 부분의 시간까지 모두 포함되기 때문
  • ‘Row_examined’ 는 이 쿼리가 처리되기 위해 몇 건의 레코드에 접근헀는지를 의미함.
  • ‘Row_sent’ 는 실제 몇 건의 처리 결과를 클라이언트로 보냈는지를 의미함.
    • 일반적으로 ‘Rows_examined’ 레코드 건수가 높음. ‘Rows_sent’ 에 표시된 레코드 건수가 적다면 이 쿼리는 조금 더 적은 레코드만 접근하도록 튜닝해 볼 가치가 있음.
• MyISAM 이나 MEMORY 스토리지 엔진은 테이블 단위 잠금을 사용하고, MVCC 같은 메커니즘이 없기에 SELECT 쿼리라고 해도 Lock_time 이 1초 이상 소요될 가능성이 있음.
• 가끔, InnoDB 테이블에 대한 SELECT 쿼리도 Lock_time 이 상대적으로 큰 값이 발생할 수 있음.
  • 이는 InnoDB 레코드 수준 잠금이 아닌 MySQL 엔진 레벨에서 설정한 테이블 잠금 때문일 가능성이 높음.
  • 따라서, InnoDB 테이블에만 접근하는 쿼리 문장의 슬로우 쿼리 로그에서 Lock_time 값이 튜닝이나 쿼리 분석에 별로 도움은 되지 않음.
• 일반적으로 슬로우 or 제너럴 로그 파일 내용이 많아서 직접 쿼리를 하나씩 검토하기 어려움 (시간 많이걸리고 어느 쿼리를 집중 튜닝할지 식별하기도 어려움)
  • Percona 에서 개발한 Percona Toolkit 인 pt-query-digest 스크립트를 이용해 쉽게 빈도나 처리 성능별 쿼리를 정렬해서 살펴볼 수 있음.
• 로그 파일이 분석 완료되면 그 결과를 아래 3개의 그룹으로 나누어 저장됨.

슬로우 쿼리 통계

• 분석 결과의 최상단에 표시됨
• 모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행 시간(Exec time), 잠금 대기 시간(Lock time) 에 대한 평균 & 최대/최소 값을 표시함.

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

• 각 쿼리별 응답 시간과 실행 횟수를 보여줌
• pt-query-digest 명령 실행 시 --order-by 옵션으로 정렬 순서를 변경할 수 있음.
• Query ID 는 실행된 쿼리 문장을 정규화(쿼리에 사용된 리터럴을 제거) 해서 만들어진 해시값임.
  • 일반적으로 같은 모양의 쿼리라면 동일한 Query ID를 가지게 된다.

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

• Query ID 별 쿼리를 쿼리 랭킹에 표시된 순서대로 자세한 내용을 보여줌
• 랭킹별 쿼리에서 대상 테이블에 대해 어떤 쿼리인지만을 표시함.
  • 실제 상세한 내용은 개별 쿼리의 정보를 확인해보면 됨.
  • 여기서는 쿼리가 얼마나 실행됐는지, 쿼리 응답시간에 대한 히스토그램같은 상세 내용을 볼 수 있음.

트랜잭션과 잠금

• MySQL 의 동시성에 영향을 미치는 잠금(Lock), 트랜잭션, 트랜잭션 격리 수준(Isolation level) 을 살펴본다.
• 트랜잭션은 작업의 완전성을 보장해 준다.
  • 논리적인 작업 셋을 모두 완벽하게 처리하거나, 처리하지 못할 경우 원 상태로 복구함.
    • 작업의 일부만 적용되는 현상(Partial update)을 방지해주는 기능이다.
• 잠금(Lock) 은 동시성을 제어하기 위한 기능이다.
• 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다.
• 격리 수준은 하나의 트랜잭션 내에서, 혹은 여러 트랜잭션 간의 작업 내용을 어떻게 공유 및 차단할지 결정하는 레벨을 의미함.

트랜잭션

• 트랜잭션은 데이터베이스 서버에서 개발자에게 엄청난 큰 혜택을 제공한다..
• 트랜잭션을 지원하지 않는 MyISAM 과 트랜잭션을 지원하는 InnoDB 처리 방식 차이를 살펴본다.

MySQL에서의 트랜잭션

• 트랜잭션이 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합됐을 때만 의미있는 개념이 아님.
  • 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계 없이, 논리적인 작업 셋 자체가 100%(COMMIT) 적용되거나 0%(ROLLBACK)로 아무것도 적용되지 않아야 함을 보장해 주는 것임.

mysql> CREATE TABLE tab_myisam (fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MYISAM;
mysql> INSERT INTO tab_myisam (fdpk) VALUES (3);

mysql> CREATE TABLE tab_innodb (fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=INNODB;
mysql> INSERT INTO tab_innodb (fdpk) VALUES (3);

mysql> SET autocommit=ON;

mysql> INSERT INTO tab_myisam (fdpk) VALUES (1),(2),(3);
mysql> INSERT INTO tab_innodb (fdpk) VALUES (1),(2),(3);

• 위 와 같이 MyISAM 스토리지 엔진과 InnoDB 엔진을 구분하여 테이블 및 데이터를 삽입하는 쿼리임.
  • 이미 3인 값이 있는 상태에서 1,2,3을 한번에 삽입하는 예시이다.
• 이때, 이미 3이 있으므로 두 엔진 모두 Duplicate entry 에러가 뜬다.
  • 하지만, 이후 테이블을 조회하면 MyISAM 의 데이터는 1,2,3이 저장되어 있고, InnoDB 는 3만이 저장되어 있다.
  • InnoDB 스토리지 엔진은 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다. (트랜잭션 원칙)
  • MyISAM (=MEMORY 스토리지 엔진도 동일하게 작동함) 테이블에서 발생하는 이러한 현상을 부분 업데이트 라고 표현하며 이러한 부분 업데이트 현상은 테이블 데이터 정합성을 맞추기 상당히 어렵게 만든다.
• 위 예시와 같이 2개 이상의 쿼리를 실행하는 경우, 실패에 대한 재처리 작업을 고민해야 함.
  • MyISAM, 에서 첫번째 쿼리 실패, 성공에 대한 분기와 함께 다음 쿼리에 대한 분기가 계속해서 이어지게 됨. → 상당히 복잡한 로직이 추가되야 함.
    • 근데, 트랜잭션이 지원되지 않는 MyISAM 에 레코드를 INSERT 할 때는 이 방법 외에는 방법이 없음.
    • 이렇게 하지 않으면 부분 업데이트가 될 수 있기에 쓰레기 데이터가 테이블에 남아있을 가능성이 높음.
  • InnoDB 의 경우, 위 문제를 간단한 코드로 완벽 구현 가능하다.

주의사항

• 트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.
  • 프로그램 코드에서 트랜잭션의 범위를 최소화 하라는 의미임.
• 실제로 DBMS 에 데이터를 저장하는 작업이 시작되는 부분부터 트랜잭션을 시작하게 만들어서 트랜잭션 소유 시간을 최대한 짧게 가져가도록 한다. (미리 트랜잭션을 시작할 필요 없다.)
• 메일 전송이나 FTP 파일 전송 작업, 혹은 네트워크를 통해 원격 서버와 통신하는 작업은 어떻게 해서든 DBMS 의 트랜잭션 내에서 제거하는 것이 좋음.
  • 메일 서버와 통신할 수 없는 상황이 발생하면 웹 서버 뿐 아니라 DBMS 서버 까지 위험해지는 상황이 발생하게 된다.