Real MySQL

[DB] Real MySQL - 3장

3.1 MySQL 아키텍쳐

• 3.1.1 MySQL의 전체 구조

  

  mySQL은 대부분의 RDBMS의 접근을 허용한다.

  MySQL 엔진은 클라이언트로부터의 접속과 쿼리 요청을 처리하는 커넥션 핸들러, SQL 파서, 전처리기, 옵티마이저로 구성

  스토리지 엔진은 sql 문장을 분석하거나 최적화한다.

  핸들러 API는 mySQL 엔진의 쿼리 실행기에서 데이터 읽거나 쓸 때는 각 스토리지 엔진에 읽기/쓰기 요청을 하고 핸들러 요청이라 한다.

• 3.1.2 MySQL 스레딩 구조

  • foreground thread

    최소 클라이언트 수만큼 존재하며 사용자가 요청하는 쿼리 문장 처리, 작업을 마치면 다시 스레드 캐시로 복귀(thread_cache_size 유지)

    MySQL의 데이터 버퍼나 캐시로부터 데이터를 가져오거나 직접 디스크의 데이터나 인덱스 파일로부터 데이터 가져와 작업 처리

  • background thread

    인서트 버퍼를 병합하는 스레드, InnoDB 버퍼 풀의 데이터를 디스크에 기록하는 스레드, 데이터를 버퍼로 읽어들이는 스레드, 락 담당 스레드, 로그 스레드, 쓰기 스레드 존재

• 3.1.3 메모리 할당 및 사용 구조

  글로벌 메모리 영역은 MySQL 서버가 시작되면서 운영체제로부터 할당받고 모든 스레드에 의해 공유된다.

  로컬 메모리 영역은 MySQL 서버 상에 존재하는 클라이언트 스레드가 쿼리를 처리하는 데 사용하는 메모리 영역으로 독립적으로 할당되고 절대 공유되어 사용되지 않는다.

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

  SQL 파서 <-> 옵티마이저 <-> 실행기 <-> 데이터 읽기/쓰기 <-> 디스크 스토리지

  ————-MySQL 엔진————–

  ——스토리지엔진—–

  —디스크 스토리지—

• 3.1.5 쿼리 실행 구조

  1. 파서

     쿼리 문장을 토큰(mySQL이 인식할 수 있는 최소 단위 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어내는 작업

  2. 전처리기

     파서 트리를 기반으로 쿼리 문장에 문제점 확인, 테이블 이름이나 칼럼 이름, 내장 함수를 매핑해 객체의 존재여부와 접근권한 확인

  3. 옵티마이저

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

  4. 실행 엔진

     만들어진 계획대로 핸들러에 요청해 받은 결과를 또 다른 핸들러의 요청으로 연결하는 역할

  5. 핸들러(스토리지 엔진)

     실행 엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할

• 3.1.6 복제(Replication)

  

  데이터는 갈수록 커지고 하나의 데이터베이스로만 사용할 수 없으니 확장성을 중요시해야한다.

  MySQL에서는 2대 이상의 sql 서버가 동일한 데이터를 담도록 실시간으로 동기화해주는 복제를 이용해 확장성을 제공해준다.

  쓰기(insert, update, delete)를 수행하는 Master MySQL 서버와 읽기(read)를 수행하는 Slave MySQL 서버가 존재한다. 마스터는 반드시 1개, 슬레이브는 1개 이상으로 구성될 수 있다.

  서버로 2대 이상을 구축해 마스터와 슬레이브를 둘 수도 있지만 서버 하나가 마스터이면서 슬레이브까지 수행하도록 할 수도 있다.

  마스터는 dml과 ddl 중에 데이터를 변경하는 쿼리는 바이너리 로그에 기록한다. 바이너리 로그가 활성화되면 마스터가 된다. 슬레이브가 변경 내역 조회를 요청하면 Binlog dump라는 스레드가 바이너리 로그를 읽어 슬레이브에 전달한다.

  슬레이브는 데이터를 받아올 마스터의 정보(ip주소, 포트 번호, 접속 계정)를 가지고 있는 경우 슬레이브가 된다. 동기화를 위해 슬레이브 서버의 I/O 스레드는 마스터 서버에 접속해 변경 내역을 요청하고 릴레이 로그에 기록한다. 그 릴레이로그에 변경된 내역을 재실행함으로써 동기화한다.

  주의사항

  • 슬레이브는 하나의 마스터만 설정

  • 마스터와 슬레이브의 동기화를 위해 슬레이브는 읽기 전용으로

  • 슬레이브 서버용 장비는 마스터와 동일하게

  • 복제가 불필요하면 바이너리 로그 중지

    바이너리 로그는 mysql에 큰 자원을 사용한다. Gap lock을 항시 유지하고, 트랜잭션 커밋마다 쿼리를 기록해야 한다. 더욱이 이 작업이 완료되어야 쿼리가 끝나는 경우도 있다. 그러므로 불필요하면 안쓰도록 설정

  • 바이너리 로그와 트랜잭션 격리 수준

    바이너리 로그 파일은 STATEMENT 포맷 방식과 ROW 포맷 방식을 두고 STATEMENT는 바이너리에 마스터에서 실행되는 쿼리를 기록, ROW는 마스터에서 실행된 쿼리에 의해 변경된 레코드 값을 기록하는 방식이다. STATEMENT에서는 repeatable read 격리 수준이어야만 가능하므로 insert 시 주의해야 한다.

• 3.1.7 쿼리 캐시

  복잡한 처리 절차와 큰 비용으로 실행된 결과를 쿼리 캐시(key-value)에 담아두고 동일한 요청이 왔을 때 바로 결과로 준다.

  1. 요청된 문장이 캐시에 존재하는가? 권한이 있는가?

     문장이 동일한지 비교하고 권한 확인

  2. 트랜잭션 내 쿼리인 경우 괜찮은 쿼리인가?

     InnoDB의 모든 트랜잭션은 ID를 갖게되는데 자신보다 큰 ID는 참조할 수 없다. 쿼리캐시도 결과가 작을 때만 사용 가능

  3. date, rand와 같이 시점에 따라 달라지는 요소 있는지 확인

  4. 변수가 결과에 영향을 미치지 않는지 확인

  5. 쿼리 결과가 너무 크지 않은 쿼리 캐시하도록

  하지만 이 쿼리 캐시는 내부에서 자동으로 동작하는지라 오히려 비효율적인 경우가 존재했고, 따라서 8.0부터는 제거되었다.

3.2 InnoDB 스토리지 엔진 아키텍쳐

레코드 기반 잠금, 뛰어난 동시성 처리, 안정적, 뛰어난 성능 제공

• 3.2.1 InnoDB 스토리지 엔진 특성

  • 프라이머리 키에 대한 클러스터링
  • 잠금이 필요없는 일관된 읽기
  • 외래 키 지원
  • 자동 데드락 감지
  • 자동화된 장애 복구
  • 오라클 아키텍쳐 적용

• 3.2.2 InnoDB 버퍼 풀

  디스크의 데이터 파일이나 인덱스 정보를 메모리에 캐시해두는 공간, 쓰기 작업 지연을 통해 일괄 작업으로 처리할 수 있도록 하는 버퍼 역할 수행

  데이터와 인덱스 모두 캐시하고 쓰기 버퍼링 역할 수행

• 3.2.3 언두 로그

  데이터 변경 전 데이터 보관 -> 트랜잭션 롤백 대비, 트랜잭션 격리 수준 유지와 함께 높은 동시성 제공을 위해

• 3.2.4 인서트 버퍼

  인서트나 업데이트는 데이터 뿐만 아니라 인덱스에도 업데이트가 필요하고 인덱스가 많으면 많을수록 많은 자원이 소모된다.

  임시공간을 두어 바로 사용자에게 결과를 반환하기 위해 사용된다.

• 3.2.5 리두로그 및 로그 버퍼

  데이터 변경되면 ACID 보장을 위해 변경된 데이터를 파일로 기록해야 하는데 리두 로그는 변경된 내용을 순차적으로 디스크에 기록하는 로그파일

  리두로그 버퍼링에 사용되는 것이 로그 버퍼

• 3.2.6 MVCC(Multi Version Concurrency Control)

  잠금을 사용하지 않는 일관된 읽기 제공을 위해 존재

  언두 로그를 이용해 하나의 레코드에 대해 여러 개의 버전이 동시에 관리됨

  1. 사용자가 update 하면 버퍼 풀에 새로운 값 업데이트
  2. 커밋 전에 그 값을 조회하고자 하면?
     • Read_uncommitted면 버퍼풀이나 데이터 파일로부터 변경되지 않은 데이터 읽어서 반환
     • Read_committed 이상의 격리수준이면 버퍼 풀이나 언두 영역 데이터 반환

• 3.2.7 잠금 없는 일관된 읽기

  serializable 이하의 격리 수준인 경우 순수한 읽기 작업은 언두로그를 사용해 잠금 대기 없이 수행