study MySQL 아키텍처 슬로우 쿼리 로그 트랜잭션과 잠금 트랜잭션 잠금

슬로우 쿼리 로그

아키텍처 마무리

책너두 6기 14일차

백은빈, 이성욱의 Real MySQL8.0 1권 p.150 ~ p.159

내용정리

04 아키텍처

4.4.3 슬로우 쿼리 로그

MySQL 서버의 쿼리 튜닝은 서비스가 적용되기 전 튜닝과 운영 중 전체적인 성능 검사나 정기 점검 튜닝으로 나눌 수 있다. 슬로우 쿼리 로그 파일에는 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다. 실행 완료 후 기록으로 판단하기 때문에 실행이 정상적으로 완료되야만 기록될 수 있다.

  • Time : 쿼리가 종료된 시점

  • User@Host : 쿼리를 실행한 사용자의 계정
  • Query_time : 쿼리가 실행되는데 걸린 전체 시간. Time에서 이것을 뺴면 시작 시간을 알 수 있다.
  • Rows_examined : 이 쿼리가 처리되기 위 해 몇 건의 레코드에 접근했는지

스크립트를 이용해 빈도나 처리 성능 별로 쿼리를 정렬해서 살펴볼 수 있다. Log 파일을 분석하면 다음이 나온다.

4.4.3.1 슬로우 쿼리 통계

최상단에 표시. 모둔 쿼리를 대상으로 실행 시간, 잠금 대기 시간 등 관련 표시

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

각 쿼리별로 응답 시간과 실행 횟수

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

쿼리 랭킹에 표시된 순서대로 자세한 내용을 보여준다.

05 트랜잭션과 잠금

트랜잭션 : 작업의 완전성을 보장해 주는 것. 논리적인 작업 셋(set)을 완벽하게 처리하거나, 처리하지 못할 경우에는 원 상태로 복구해서 작업의 일부만 적용되는 형상(Partial update)이 발생하지 않게 만들어주는 기능.

잠금(Lock) : 동시성을 제어하기 위한 기능. 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할

5.1 트랜잭션

5.1.1 MySQL에서의 트랜잭션

트랜잭션은 하나의 논리적인 작업 셋에 몇개의 쿼리가 있뜬 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 아무것도 적용되지 않아야 함을 보장해 주는 것이다.

5.1.2 주의사항

트랜잭션 또한 DBMS의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 프로그램 코드에서 트랜잭션의 범위를 최소화하라는 의미다.

다음 간소화된 과정을 통해 살펴보자

  1. 처리 시작
    • 데이터베이스 커넥션 생성
    • 트랜잭션 시작
  2. 사용자의 로그인 여부 확인
  3. 사용자의 글쓰기 내용의 오류 여부 확인
  4. 첨부로 업로드된 파일 확인 및 저장
  5. 사용자의 입력 내용을 DBMS에 저장
  6. 첨부 파일 정보를 DBMS에 저장
  7. 저장된 내용 또는 기타 정보를 DBMS에서 조회
  8. 게시물 등록에 대한 알림 메일 발송
  9. 알림 메일 발송 이력을 DBMS에 저장
    • 트랜잭션 종료(COMMIT)
    • 데이터베이스 커넥션 반납
  10. 처리 완료
  • 보통 개발자들은 위의 순서로 처리하는데 실제로 DBMS에 데이터를 저장하는 작업(트랜잭션)은 5번부터 시작된다는 것을 알 수 있다. 그래서 2번, 3번, 4번의 절차가 아무리 빨리 처리된다고 하더라도 DBMS의 트랜잭션에 포함시킬 필요는 없다.
  • 네트워크를 통해 원격 서버와 통신하는메일 전송이나 FTP 파일 전송 작업 등은 어떻게 해서든 DBMS의 트랜잭션 내에서 제거하는 것이 좋다. 프로그램 실행 중 서버와 통신할 수 없는 상황이 발생하면 DBMS 서버까지 위험해진다.
  • 이 과정에는 DBMS의 작업이 크게 4개가 있다.
    • 사용자의 입력 정보를 저장하는 5번, 6번 작업은 하나의 트랜잭션으로 묶어야 한다.
    • 7번 작업은 저장된 데이터의 단순 확인 및 조회이므로 트랜잭션에 포함될 필요가 없다.
    • 9번 작업은 성격이 조금 달라 5,6번 트랜잭션과 함께 묶지 않아도 좋다.

위 결론을 바탕으로 처리 절차를 재설계해보자.

  1. 처리 시작
  2. 사용자의 로그인 여부 확인
  3. 사용자의 글쓰기 내용의 오류 발생 여부 확인
  4. 첨부로 업로드된 파일 확인 및 저장
    • 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기)
    • 트랜잭션 시작
  5. 사용자의 입력 내용을 DBMS에 저장
  6. 첨부 파일 정보를 DBMS에 저장
    • 트랜잭션 종료(COMMIT)
  7. 저장된 내용 또는 기타 정보를 DBMS에서 조회
  8. 게시물 등록에 대한 알림 메일 발송
    • 트랜잭션 시작
  9. 알림 메일 발송 이력을 DBMS에 저장
    • 트랜잭션 종료(COMMIT)
    • 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)
  10. 처리 완료
CATALOG
    MySQL