RealMySQL 1 : 4장 아키텍처

MySQL 서버

1. MySQL 엔진
   1. SQL 문장 분석 또는 최적화
   2. group by, order by 등
2. 스토리지 엔진
   1. InnoDB, MyISAM
   2. 실제 데이터 디스크 스토리지에 저장
   3. 디스크 스토리지에서 데이터 읽어오기

• 핸들러 요청 : MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽을 때 보내는 요청
• MySQL 서버는 프로세스 기반이 아닌 쓰레드 기반

쓰레드

포그라운드 쓰레드

• MySQL의 데이터 버퍼나 캐시로부터 가져오며 버퍼나 캐시에 없는 경우 직접 디스크의 데이터나 인덱스 파일로부터 데이터를 읽어와서 작업 처리

백그라운드 쓰레드

• 버퍼로부터 디스크까지 기록하는 작업
• 로그를 디스크로 기록
• 데이터를 버퍼로 읽어오기
• 잠금이나 데드락 모니터링

플러그인 스토리지 엔진 모델

• 하나의 쿼리 작업은 여러 하위 작업으로 나뉘는데, 각 하위 작업이 MySQL 엔진 영역에서 처리되는지 아니면 스토리지 엔진 영역에서 처리되는지 구분할 줄 알아야한다.
• MySQL 서버가 준비되어 있다면 플러그인 형태로 빌드된 스토리지 엔진 라이브러리를 다운로드해서 끼워넣기만 하면 사용할 수 있다.

컴포넌트

• MySQL 8.0 부터는 플러그인 모델의 단점을 보완하기 위해 컴포넌트 아키텍처가 지원
  • 단점
    • 플러그인은 오직 MySQL 서버와 인터페이스할 수 있고, 플러그인끼리 통신 불가능
    • MySQL 서버의 변수나 함수를 직접 호출
      • 캡슐화 안됨
    • 플러그인은 상호 의존 관계를 설정할 수 없어서 초기화 어려움
• MySQL 5.7까지는 비밀번호 검증이 플러그인
  • MySQL 8.0은 컴포넌트로 개선

쿼리 실행 구조

쿼리 파서

• 쿼리 문장을 토큰(MySQL 이 인식할 수 있는 단위)으로 분리해 트리 형태 구조로 만들어내는 작업

전처리기

• 트리 기반으로 쿼리 문장에 구조적인 문제 확인
• 객체 존재 여부, 접근 권한 확인

옵티마이저

• 쿼리를 저렴한 비용으로 가장 빠르게 처리할지 결정

쓰레드 풀

MySQL 엔터프라이즈 (기업)

• 스레드풀 기능 제공

MySQL 커뮤니티 (개인)

• 쓰레드 풀 지원하지 않음
• 대신 Percona에서 제공하는 스레드 풀 라이브러리 설치 가능

트랜잭션 지원 메타데이터

• MySQL 5.7은 테이블 구조나 스토어드 프로그램의 메타데이터를 파일에 저장
  • 파일 기반 메타데이터는 트랜잭션 지원하지 않기 때문에 비정상적으로 종료되면 테이블 깨진다.
    • 비정상적 종료 : 스키마 변경 작업 도중 비정상적 종료
• MySQL 8 부터 테이블 구조나 스토어드 프로그램의 메타데이터를 InnoDB 테이블에 저장
  • mysql.ibd 라는 이름의 테이블스페이스에 저장
  • 사용자 임의수정 방지를 위해 숨겨졌지만 테이블 존재
  • 완전히 성공 or 완전히 실패
• InnoDB는 테이블이지만 MyISAM은 파일로 저장
  • InnoDB도 파일로 변경 가능

프라이머리 키에 의한 클러스터링

• InnoDB는 PK 기준으로 클러스터링
• MyISAM은 클러스터링 키 지원하지 않음

외래 키 지원

• 외래키는 DB 서버 운영이 불편함
  • 급하게 데이터 삭제하려는데 제약조건 걸린다.
  • 외래키 체크로 인해 데드락 걸릴 가능성 있다.
  • foreign_key_checks를 off로 설정하면 외래키 체크 일시적 멈춤 가능
    • 대신 데이터 정합성 안맞을 수 있음.
      • CASCADE를 무시하기 때문에 정합성이 맞지 않는 데이터가 남아있을 수 있다.

MVCC

• 레코드 레벨 트랜잭션 지원
  • InnoDB
  • 잠금을 사용하지 않는 일관된 읽기
  • 언두 로그
  • 트랜잭션 길어지면 언두로그 많아져서 db 공간 많이 차지
    • 언두 영역을 필요로 하는 트랜잭션이 더는 없을 때 삭제
    • 따라서 트랜잭션은 빠르게 완료하는게 좋다.

자동 데드락 감지

• InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있다.
• InnoDB 레벨보다 상위 레벨인 MySQL에서 관리되는 테이블 잠금(LOCK TABLES)은 볼 수가 없어서 데드락 감지 불확실하다.
  • 그러나 innodb_table_locks 시스템 변수 활성화하면 볼 수 있다.
• 동시 처리 스레드가 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다.
  • 작업 속도 저하 (CPU 자원 소모)
  • innodb_deadlock_detect 시스템 변수를 off하면 데드락 감지 스레드 동작하지 않지만 속도 향상
  • 그러면 데드락 동안 무한대기할텐데 어떡하지?
    • innodb_lock_wait_timeout 시스템 변수 설정하면 데드락 상황에서 타임아웃 건다.
    • 기본값인 50초보다 훨씬 낮은 시간으로 설정 권고

InnoDB 버퍼 풀

• 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간
• 쓰기 작업 지연시켜 일괄 작업으로 처리할 수 있게 해주는 버퍼 역할도 수행
  • INSERT, UPDATE, DELETE
• MySQL 5.7부터 InnoDB 버퍼 풀 크기 동적으로 조절 가능
• 버퍼 풀은 128MB 청크 단위로 쪼개어 관리된다.
  • 잠금으로 인해 내부 잠금 경합을 많이 유발했는데, 이걸 분산
• 버퍼풀은 클린 페이지, 더티 페이지를 가지고 있다.
  • 클린 페이지 : 디스크에서 읽은 상태로 전혀 변경되지 않은 페이지
  • 더티 페이지 : INSERT, UPDATE, DELETE 명령으로 변경된 데이터를 가진 페이지

버퍼 풀 상태 백업 및 복구

• 버퍼 풀에 쿼리가 있으면 디스크에서 데이터를 읽지 않아도 쿼리가 처리된다.
  • 속도가 상당히 빠르다.
  • 디스크 데이터가 버퍼 풀에 적재돼 있는 상태를 워밍업이라고 표현
  • MySQL 5.5 버전에서는 점검을 위해 서버를 껐다키고 오픈 전 강제 워밍업을 위해 주요 테이블, 인덱스를 풀스캔하고 서비스를 오픈했었다.
  • MySQL 5.6 버전부터는 버퍼 풀 덤프 및 적재 기능 도입
    • MySQL 서버를 재시작 하는 경우 innodb_buffer_pool_dump_now 시스템 변수를 통해 버퍼 풀 상태 백업
    • 실제 존재하는 페이지는 버퍼 풀로 적재하지만 그렇지 않은 경우 조용히 무시

언두 로그

• InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(INSERT, UPDATE, DELETE)로 변경되기 전 버전의 데이터를 별도로 백업한다. 이 데이터를 언두 로그라고 한다.
• 트랜잭션 보장
  • 트랜잭션 롤백 시 언두 로그에 백업된 이전 버전의 데이터를 이용해 복구
• 격리 수준 보장
  • 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 한다.
• 트랜잭션이 장시간 유지되면 언두로그도 계속 보존된다.
• 언두 테이블스페이스 : 언두 로그가 저장되는 공간
• 언두 로그 슬롯이 부족하면 트랜잭션을 시작할 수 없는 심각한 문제가 발생
  • 언두 로그 관련 시스템 변수를 변경해야한다면 트랜잭션 개수에 맞게 언두 테이블스페이스와 롤백 세그먼트의 개수 설정

체인지 버퍼

• 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행하지만 디스크로부터 읽어와서 업데이트해야한다면 이를 즉시 실행하지 않고 임시 공간에 저장해두고 바로 사용자에게 결과를 반환하는 형태로 성능 향상. 이때 사용하는 임시 메모리 공간을 체인지 버퍼라고 한다.

• MySQL 서버에서 트랜잭션이 커밋돼도 데이터 파일은 즉시 디스크로 동기화되지 않는 반면, 리두 로그는 항상 디스크로 기록된다.

어댑티브 해시 인덱스

• 자주 사용되는 컬럼을 해시로 정의하여 B-Tree를 타지 않고 바로 데이터에 접근할 수 있는 기능
  • 테이블 정리 시 Adaptive Hash Index를 사용하다가 변경하면 쿼리 응답속도가 떨어지게 되어 장애발생할 수 있습니다.
    • 최대한 트래픽이 없는 시점에 진행
• 사용자가 수동으로 생성하는 인덱스가 아니라 InnoDB 스토리지 엔진에서 사용자가 주로 요청하는 데이터에 의해 자동으로 생성하는 인덱스
• 자주 읽히는 데이터 페이지의 키 값을 이용해 해시 인덱스를 생성
• 인덱스 키 값 : B-Tree 인덱스의 고유 번호 + B-Tree 인덱스의 실제 키 값
  • 어댑티브 해시 인덱스는 하나만 존재하기 때문에 B-Tree 인덱스의 고유 번호가 포함
• MySQL 8.0 부터는 어댑티브 해시 인덱스 파티션 기능 제공
• 예시
  • 성능 향상에 도움
    • 디스크의 데이터가 InnoDB 버퍼 풀 크기와 비슷한 경우 (디스크 읽기가 많지 않은 경우)
      • 버퍼 풀이 작으면 디스크 읽기
      • 풀 테이블 스캔하는 쿼리
      • 데이터 셋이 매우 큰 경우
    • 동등 조건 검색(=, IN)이 많은 경우
    • 쿼리가 데이터 중에서 일부 데이터만 집중되는 경우
  • 성능 향상에 도움되지 않음
    • 디스크 읽기가 많은 경우
    • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE)
    • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
• 어댑티브 해시 인덱스가 활성화될 경우
  • 저장공간인 메모리 사용
  • 인덱스 공간 사용
  • 테이블 삭제나 변경 시 어댑티브 해시 인덱스도 수정
    • CPU 많이 사용
• 어댑티브 해시 인덱스와 사용하지 않은 검색의 비율을 비교해서 적절히 조절

슬로우 쿼리 로그

• 쿼리가 정상적으로 실행이 완료돼야 슬로우 쿼리 로그에 기록될 수 있다.

• Time : 쿼리가 종료된 시점
• Query_time
  • 쿼리가 실행되는데 걸린 전체 시간
  • Lock_time은 MySQL 엔진 레벨에서 관장하는 테이블 잠금
  • 실제 쿼리가 실행되는데 필요한 잠금 체크와 같은 코드 실행 부분의 시간까지 포함되기 때문에 잠금이 걸리지 않더라도 0초가 아닐 수 있다.
    • 매우 작은 값이면 무시해도 무방
• Rows_examined
  • 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지 의미
• Rows_sent
  • 실제 몇 건의 처리 결과를 클라이언트로 보냈는지 의미
• Rows_examined가 높지만 Rows_sent가 상당히 적다면 쿼리 튜닝해볼 가치가 존재
• InnoDB 테이블에만 접근하는 쿼리 문장의 슬로우 쿼리 로그에서는 Lock_time 값은 튜닝이나 쿼리 분석에 별 도움 되지 않는다.