Real MySQL 4장 정리

아키텍쳐

• MySQL엔진
  • MySQL엔진은 SQL 문장 분석이나 최적화같은 두뇌역할을 수행
  • 얘는 MySQL서버 하나에 한개밖에 없다.
  • 커넥션 핸들러, SQL파서 및 전처리기
  • 옵티마이저
    • 쿼리의 최적화된 실행을 위해
• 스토리지 엔진
  • 데이터 저장 / 불러오는 역할 수행
  • 이거는 여러개 동시사용 가능하다.

스레딩 구조

• MySQL서버는 프로세스가 아니라 스레딩 기반으로 동작
  • 포그라운드 스레드
    • 최소 mysql에 접근한 클라이언트의 수만큼 존재
      • 그래서 사용하면 여기있고 다쓰면 스레드캐시로 돌아감
        • 근데 스레드캐시가 일정 갯수 이상이면 그냥 종료시킨다.
    • 데이터를 데이터버퍼나 캐시로부터 가져온다.
      • 없는 경우에는 직접 디스크나 인덱스 파일에서부터 데이터를 가져옴
    • (InnoDB기준) 데이터 버퍼나 캐시에 저장
      • 이후는 백그라운드에서 진행..
  • 백그라운드
    • 인서트 버퍼 병합
    • 로그를 디스크로 저장
    • InnoDB버퍼풀의 데이터를 디스크에 기록
      • MySQL5.5 버전부터 데이터 쓰기와 읽기 쓰레드의 개수를 2개 이상 지정 가능
      • 데이터를 읽는 작업은 클라이언트 스레드에서 처리되어 많이 지정할필요는 없지만 쓰기는 아주 많은 작업을 백그라운드로 처리해서 충분히 설정해야 한다.
        • 일반적인 내장 디스크는 2~4개 정도
        • DAS나 SAN같은 스토리지 사용시는 최적 사용 가능하도록 설정하면 좋다함
    • 데이터를 버퍼로 읽어옴
    • 잠금이나 데드라락 모니터링

메모리 할당 및 사용 구조

• 글로벌 영역
  • 클라이언트 스레드 수와 무관하게 하나의 메모리 공간만 할당받는다.
    • 필요에 따라 더 받을수 있기는 한데 클라이언트 스레스 수와 무관
    • 그리고 영역이 N개라 해도 모든 스레드에 의해 공유된다.
  • 테이블 캐시
  • InnoDB 버퍼 풀
  • InnoDB 어댑티브 해시 인덱스
  • InnoDB 리두 로그 버퍼
• 로컬 영역 (세션 메모리 영역)
  • MySQL 서버상에 존재하는 클라이언트 스레드가 쿼리를 처리하는데 사용하는 메모리 영역
    • 커넥션 버퍼와 소트 버퍼 등
  • 클라이언트가 MySQL에 접근하면 서버에서 클라이언트 커넥션 요청 처리를 위해 할당
    • 그래서 클라이언트 메모리 영역이라고도 한다.
  • 얘는 독립적으로 할당되며 절대 공유되지 않는다.
  • 그리고 막 무변별하게 할당하면 서버 멈출수도 있음
  • 커넥션 열려 있는 동안 할당
    • 커넥션 버퍼
    • 결과 버퍼
  • 얘는 각 쿼리의 용도별로 필요할 때만 공간이 할당되고 필요하지 않은 경우 할당조차 하지 않음
    • 소트 버퍼
    • 조인 버퍼

쿼리 실행 구조

쿼리 실행 관점에서의 MySQL 구조!!

• 쿼리 파서
  • 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)로 분리해 트리 형태 구조로 만들어내는 작업
  • 문장 기본 문법 오류는 여기서 발견하고 리턴한다.
• 전처리기
  • 파서에서 만들어진 트리를 기반으로 쿼리 문장 구조적 문제점을 확인한다.
  • 토큰을 테이블 이름이나 칼럼 이름, 내장 함수같은 개체를 매핑해 해당 객체의 존재 여부와 객체 접근 권한 등을 확인하는 과정 수행
  • 실제 존재하지 않거나 권한상 사용 불가면 여기서 걸러진다.
• 옵티마이저
  • 쿼리 문장을 저렴한 비용으로 가장 빠르게 처리할지를 결정하는 역할
• 실행 엔진
  • 옵티마이저가 뭐 하라고하면 얘가 핸들러한테 시킨다.
  • 만들어진 계획대로 각 핸들러에게 요청해서 받은 결과를 또 다른 핸들러 요청의 입력으로 연결
• 핸들러(스토리지 엔진)
  • MySQL서버의 가장 밑단에서 MySQL실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어 오는 역할
  • 핸들러는 결국 스토리지 엔진을 의미
• 복제
  • 나중에...
• 쿼리 캐시
  • 캐시해서 보여주는거
  • 예전에는 좋았는데 이제는 안쓴다.
    • 데이터가 변경되면 이전 데이터를 모두 지우고 새로 가져와야 했다.
• 스레드 풀
  • MySQL 서버 엔트프라이즈 에디션은 제공하는데 커뮤니티 에디션은 스레드 풀 기능 제공 X
  • 여기서는 Percona Server에서 제공하는 스레드 풀 기능을 살펴본다.
    • Percona Server 스레드 풀은 플러그인 형태로 작동한다.
  • 내부적으로 사용자의 요청을 처리하는 스레드 개수를 줄여서 동시 처리되는 요청이 많다 하더라도 MySQL서버 CPU가 제한된 개수의 스레드 처리에만 집중할 수 있게 함 -> 서버 자원 소모를 줄이기
    • 이게 막 성능이 엄청 올라가는건 아니라고 한다.
      • 실행 중인 스레드들을 CPU가 최대한 잘 처리할 수 있는 수준으로 줄여서 처리하도록
        • 그러면 스케줄링 과정에서 CPU시간을 잘 확보하지 못하면 오히려 쿼리 처리가 느려질 수 있다.
  • 기본적으로 CPU코어 개수만큼 스레드 그룹 생성
  • 스레드 그룹의 모든 스레드가 일을 처리하고 있다면 스레드 그룹에 새로운 작업 스레드를 추가할지 아니면 작업 끝날때까지 기다릴지 여부를 판단해야 한다.
    • 상태를 체크해서 thread_pool_stall_limit에 정의된 시간만큼 작업을 끝내지 못하면 새로 추가한다.
  • 선순위 후순위 큐를 이용해 특정 트랜잭션이나 쿼리를 우선적으로 처리할 수 있는 기능도 제공한다.
    • 먼저 시작된 트랜잭션 내에 속한 SQL을 빨리 처리해주면 해당 트랜잭션이 가지고 있던 잠금이 빨리 해제되고 잠금 경합을 낮춰서 전체적인 처리 성능을 향상
• 트랜잭션 지원 메타데이터
  • DB서버에서 테이블의 구조 정보와 스토어드 프로그램 등의 정보를 딕셔너리 또는 메타데이터라고 한다.
  • MySQL8.0부터 테이블 구조 정보나 스토어드 프로그램의 코드 관련 정보를 InnoDB 테이블에 저장하도록 개선했다.
    • 데이터 딕셔너리랑 시스템 테이블(인증테이블같은거)가 트랜잭션 기반의 InnoDB스토리지 엔진에 저장되어서 스키마 변경이 완전한 성공/실패로 정리된다.

---

InnoDB스토리지 엔진 아키텍쳐

InnoDB는 MySQL에서 사용할 수 있는 스토리지 엔진 중 거의 유일하게 레코드 기반 잠금 제공
높은 동시성 처리가 가능하고 안정적이며 성능이 뛰어나다.

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

• InnoDB의 모든 테이블은 기본적으로 PK기준 클러스터링 -> 이 순서로 저장된다.
  • 기본적으로 PK가 클러스터링 인덱스 -> PK기준이 속도가 빠르다.
    • 실행 계획에서 PK가 기본적으로 다른 인덱스보다 선택될 확률이 높다.

외래 키 지원

• FK 지원

MVCC(Multi Version Concurrency Control)

• 레코드 레벨의 트랜잭션을 지원하는 DBMS가 제공하는 기능
• 언두 로그를 통한 멀티버전(하나의 레코드에 여러 개의 버전이 동시 존재)
  • 이거는 shared lock과 관계가 있는데, 잠금 없이 일관된 읽기를 제공하는게 이를 통해 가능해진다.
    • 격리 수준으로 볼 때
      • READ_UNCOMMITED의 경우 : 데이터 커밋과 관계 없이 변경된 데이터를 반환
      • READ_COMMITED 이상의 경우 : 언두 영역 데이터 반환(커밋 전까지는)
  • 참고로 언두영역 이거 커밋됐다고 바로 삭제되는건 아니고 트랜잭션에서 쓰임이 다 끝나야 삭제됨

잠금 없는 일관된 읽기

• 위에서의 MVCC를 이용해 잠금 없이 읽기 진행
• 그리고 오랫동안 트랜잭션이 활성 상태일 때에 언두 로그를 계속 유지하고 있게 된다.
  • 그래서 트랜잭션은 가능한 빨리 롤백이나 커밋해야함.

자동 데드락 감지

• 잠금 대기 목록을 Wait-for List 형태로 관리한다.
• 데드락 감지 스레드를 통해 스레드 검사 -> 데드락이면 강제 종료
  • 언두 로그 양을 기준으로 뭐부터 종료할지 정한다. -> 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다.
    • 롤백해도 언두처리 할 내용이 적고, 서버 부하도 덜하니까
  • 데드락 감지 스레드는 잠금 목록을 검사해서 찾아본다.
    • 그래서 잠금 목록에 변화가 없도록 검사할 때 새로 잠금을 걸고 데드락 스레드를 찾는다.
      • 동시 처리 스레드가 많으면 CPU자원을 많이 소모한다.

자동 장애 복구

• 뭔가뭔가 완료하지 못한 트랜잭션이나 디스크에 일부만 기록된 데이터의 복구 작업이 자동으로 이루어짐

InnoDB 버퍼 풀

• InnoDB스토리지 엔진에서 가장 핵심적인 부분
• 디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해 두는 공간
  • 쓰기 작업을 지연시켜 일괄 작업으로 처리할 수 있게 하는 버퍼 역할도 한다.
    • 데이터 변경 쿼리는 데이터 파일 이곳저곳의 레코드를 변경해서 랜덤한 디스크 작업을 발생시키는데 버퍼 풀이 변경된 데이터를 모아서 처리하면 랜덤한 디스크 잡업의 횟수를 줄일 수 있다.

버퍼 풀 크기 설정

• 일반적으로는 물리 메모리의 80%를 InnoDB 버퍼 풀로 설정하라고 함
• 이게 동적으로 바꿀 수 있는데 직접 확인해가면서 바꿔야 한다.

버퍼 풀의 구조

• 거대한 메모리 공간을 페이지 크기의 조각으로 쪼개서 InnoDB엔진이 데이터를 필요로 할 때 해당 데이터 페이지를 읽어서 각 조각에 저장
  • LRU 리스트(Least Recently Used)
    • 자주 쓰이는 메모리를 보관하도록
  • Flush 리스트
    • 디스크에 변경 사항을 반영하고 특정 시점에 기록
  • Free 리스트
    • 새로운 페이지 할당

버퍼 풀과 리두 로그

• 클린 페이지
  • 디스크에서 읽은 후로 변경이 없는 페이지
• 더티 페이지
  • 데이터가 변경된 페이지
  • 이거는 디스크랑 버퍼 풀의 상태가 달라서 언젠가 기록돼야 한다.
    • 근데 데이터가 계속 바뀌면 계속 덮어 씌어짐
    • 그래서 재사용 가능한 공간과 당장 재사용 불가능한 공간을 구분해서 관리해야 한다.
      • 재사용 불가능한 공간을 활성 리두 로그라 함

언두 로그

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML 변경 이전 데이터를 별도로 백업하는데, 이게 언두 로그이다.

• 트랜잭션 보장
  • 트랜잭션이 롤백되면 트랜잭션 도중 변경된 데이터를 언두 로그에 백업해둔 이전 데이터를 통해 백업함
• 격리 수준 보장
  • 데이터 변경 도중 다른 커넥션에서 조회시 격리 수준에 맞게 변경중 레코드를 읽지 않고 백업한 데이터를 읽어서 반환하기도 한다.

체인지 버퍼

• Insert / Update시에는 데이터 파일 뿐 아니라 테이블 인덱스 업데이트 작업도 필요하다
• 테이블에 인덱스가 많으면 이 변경에서 데이터 소모가 많음
  • InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있다면바로 업데이트
  • 없으면 임시 공간에 저장해두고 사용자에게 결과 반환
    • 요 임시 메모리 공간을 체인지 버퍼라고 한다.
• 반드시 중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼 사용 불가

리두 로그 및 로그 버퍼

• ACID중 Durable과 해당하는 영속성과 연관됨
• 리두 로그를 통해 비정상 종료 발생시 이전 상태로 복구
• MySQL8.0부터 리두 로그 아카이빙 가능
  • 데이터 변경이 많아서 리두 로그가 덮어쓰여도 백업이 실패하지 않도록 한다.

어댑티브 해시 인덱스

• InnoDB스토리지 엔진에서 사용자가 자주 요청하는 데이터에 대해 자동으로 생성하는 인덱스
• 자주 읽히는 데이터 페이지의 키 값을 이용해 인덱스 생성
  • 이후 필요할 때마다 어댑티브 해시 인덱스를 검색해서 즉시 찾아가기 가능
• 근데 이거 안쓰는 경우도 많다고 한다. 상황에 맞춰 활성화 / 비활성화
  • 성능에 도움이 안되는 경우
    • 디스크 읽기가 많은 경우
    • 특정 패턴의 쿼리가 많은 경우(조인이나 LIKE패턴)
    • 매우 큰 데이터를 가진 테이블의 레코드를 폭넓게 읽는 경우
  • 성능에 도움이 되는 경우
    • 디스크의 데이터가 InnoDB버퍼 풀 크기와 비슷한 경우(디스크 읽기가 많지 않은 경우)
    • 동등 조건 검색(동등 비교와 IN 연산자)이 많은 경우
    • 쿼리가 데이터 중에서 일부 데이터에만 집중되는 경우
  • 일단 확실한건 어댑티브 해시 인덱스는 데이터 페이지를 메모리(버퍼 풀)내에서 접근하는 것을 빠르게 만드는 기능이기 때문에 데이터 페이지를 디스크에서 읽어오는 경우가 많은 DB에서는 아무런 도움이 되지 않는다는 것.
  • 참고로 삭제할때 여기서도 지워야해서 성능 문제 가능성 있음

MyISAM스토리지 엔진 아키텍쳐

• 키 캐시
  • InnoDB의 버퍼 풀과 비슷한 역할을 한다.
  • 근데 이거는 이름 그대로 인덱스만을 대상으로 작동하고, 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할
• 운영체제의 캐시 및 버퍼
  • 키 캐시를 이용해 디스크를 검색하지 않고도 충분히 빠르게 검색 가능

MySQL 로그 파일

다른것보다는 슬로우 쿼리를 봐야할 것 같음

• 슬로우 쿼리 로그
  • 슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록된다.
  • 참고로 이거는 실행이 완료되어야 저장되기 때문에 일단 잘 처리는 된 것이다.
  • Time : 쿼리가 종료된 시점을 의미
  • User@Host : 쿼리를 실행한 사용자의 계정
  • Query_time : 쿼리가 실행되는데 소요된 전체 시간
    • 쿼리가 언제 시작되었는지를 알려면 Time에서 이걸 뺴면 됨
  • Rows_examined : 이 쿼리가 처리되기 위해 몇 건의 레코드에 접근하였는지?