MySQL Study

MySQL에 대한 전반적인 내용을 정리하려고 합니다.

 

 

■ MySQL이란, 가장 많이 사용하는 오픈 소스 DBMS

  - 비용이 저렴하다.

  - 커뮤니티 활성도와 인지도가 높아서 경험이나 지식을 구하기 비교적 쉽다. (그러나 상용 DBMS에 비해 전문가가 많지 않은 것도 현실)

 

■ MySQL version

  - 5.7 출시일: 2015-10 / 지원 종료: 2023-10 

  - 8.0 출시일: 2018-04 / 지원 종료: 2026-04 

  - 8.1 출시일: 2023-07  

  - 분기마다 패치 (MySQL 8.0.34, 2024-07-18)

 

■ Community vs. Enterprise Edition

  - Enterprise와 Community는 모두 동일

  - Enterprise에는 부가 기능이 포함

Enterprise 부가 기능	설명	필요 여부	대안	참고 자료
Thread Pool	Thread 수 제한 및 관리	N (검토 필요)	Percona Server의 Plugin
Enterprise Backup	온라인 백업, 증분/차등 백업, 선택적 백업, 복원	Y	Percona xtrabackup	https://hoing.io/archives/5270
Enterprise Monitor	MySQL 모니터링, Replication 대시보드, 스토리지 엔진 모니터링 등	N	DBaaS Monitoring
Enterprise Audit	사용자 접속 및 쿼리 로그 수집	Y	Percona Server의 Plugin	https://hoing.io/archives/3178
Enterprise TDE	암호화 키 관리	N
Enterprise Authentication	외부 인증	N
Enterprise Firewall	SQL Injection 대책	N
MySQL 기술 지원		Y

 

■ MySQL 엔진

  - MySQL 엔진 : 클라이언트로부터 접속 및 쿼리 요청 처리, 쿼리 최적화 실행

  - 스토리지 엔진 : 실제 데이터를 디스크에 저장하고 디스크로부터 데이터를 읽어오는 역할

  

  1) 스레드 기반

    - MySQL 엔진은 스레드 기반으로 동작

    - 포그라운드 스레드(클라이언트 스레드) 

      · 스레드 생성은 부하가 많이 드는 작업이므로 일정 수의 스레드를 유지하는 스레드 캐시 사용

      · thread_cache_size

    - 백그라운드 스레드

      · Log thread, Write thread 등

  2) 메모리 구조

    - 글로벌 메모리 영역

      · InnoDB 버퍼 풀, Log Buffer 등

    - 로컬 메모리 영역  

      · 포그라운드 스레드 별로 독립적으로 할당    

      · Sort Buffer, Join Buffer 등

 

■ InnoDB 스토리지 엔진

  - MySQL 8.0의 기본 스토리지 엔진

  - 레코드 기반 잠금 제공 → 높은 동시성 처리 가능, 안정성 확보, 뛰어난 성능

 

1. InnoDB 버퍼 풀 (Buffer Pool)

 

  - 용도1. 데이터 캐시 : 데이터 파일과 인덱스 정보를 메모리에 캐시해두는 공간

  - 용도2. 쓰기 버퍼링 : 쓰기 작업을 일괄 작업으로 처리하여 랜덤한 디스크 작업 횟수 감소

 

  1) 버퍼 풀 크기 설정

    - innodb_buffer_pool_size

    - innodb_buffer_pool_instances : 인스턴스 수가 많을수록 트랜잭션 간의 Lock 경합 감소 (트랜잭션이 각각 다른 인스턴스 사용)

    - 메모리 8GB 미만인 경우 50%, 8GB 이상인 경우 50%로 시작하여 조금씩 확장하여 최적화, 50GB 이상인 경우 40-70%

  2) 버퍼 풀 구조

    - 페이지 단위로 관리

    - 데이터 저장 구조

      · LRU_list : 디스크로부터 읽어 온 페이지를 최대한 오래동안 버퍼 풀 메모리에 유지하기 위한 구조의 페이지 목록

      · Flush_list : 디스크로 동기화되지 않은 데이터를 가진 페이지(더티 페이지)의 변경 시점 기준의 페이지 목록

      · Free_list : 버퍼 풀에서 비어 있는 페이지 목록

  3) 버퍼 풀 플러시

    ① Flush_list 플러시

      · 플러시 리스트에서 오래전에 변경된 데이터 페이지 순서대로 디스크에 기록

      · innodb_page_cleaners : Cleaner Thread 개수 조정, 일반적으로 inno_buffer_pool_instances와 동일한 값으로 설정

      · adaptive flush : 리두 로그 증가 속도를 분석(즉 더티 페이지 생성 속도를 분석)해서 버퍼 풀 내 적절한 더티 페이지 공간을 사용하도록

    ② LRU_list 플러시

      · LRU 리스트에서 사용 빈도가 낮은 데이터 페이지를 제거하고 새로운 페이지들을 읽어올 공간 확보

 

2. Double Write Buffer

 

  - 용도: 하드웨어 오작동 또는 시스템 비정상 종료로 인한 파셜 페이지(페이지 일부만 디스크에 기록되는 현상)를 방지

  - 더티 페이지를 모아서 Double Write Buffer에 한번에 기록

  - 실제 데이터 파일 기록을 중간에 실패할 때만 사용

  - innodb_doublewrite : Double Write 기능 사용 유무, 데이터 무결성이 중요하다면 활성화 

 

 

3. Undo Log

 

  - 용도1. 트랜잭션 보장 : 롤백 시 언두 로그를 읽어 데이터를 복구

  - 용도2. 격리 수준 보장 : 데이터 변경 중에 다른 커넥션에서 데이터를 조회하면 격리 수준에 따라 언두 로그의 변경 전 데이터를 읽기도 함

 

  1) 언두 테이블스페이스

    - 기본적으로 2개 생성

    - 추가 테이블스페이스 생성 가능 (MySQL 8.0.14 이후)

    - inno_page_size에 따라 초기 크기 설정 (16KB인 경우 10MB, 8/32/64KB인 경우 각각 8/20/40MB)

  2) 언두 테이블스페이스 구조

    - 언두 로그 레코드 ∋ 언두 로그 (페이지) ∋ 언두 슬롯 ∋ 롤백 세그먼트 ∋ 언두 테이블스페이스

    - 언두 슬롯은 트랜잭션이 로그를 남기는 단위로 SQL 문장에 따라 1~4개 사용

 

4. Change Buffer

 

  - 용도: 변경해야 할 인덱스 페이지가 버퍼 풀에 없는 경우 임시 공간에 저장해두고 사용자에게 결과 반환하여 성능 향상

 

5. Log Buffer

 

  - 용도: 리두 로그를 메모리에 캐시해두는 공간

  - 기본값 크기 : 16KB

 

6. Redo Log

 

  - 용도: MySQL 서버가 비정상적으로 종료됐을 때 데이터 파일에 기록되지 못한 데이터를 보장하기 위한 안전장치 

 

  commit 됐지만 데이터 파일에 기록되지 않은 데이터 → 리두 로그 데이터를 데이터 파일에 기록

  rollback 됐지만 데이터 파일에 기록된 데이터 → 리두 로그에서 데이터 파일이 어떤 상태였는지 확인한 후 언두 로그로 데이터 파일 복구

 

  1) 리두 로그와 디스크 동기화 주기

    - 데이터 정합성을 위해 commit 후 리두 로그를 즉시 디스크에 기록하는 것이 권장되나 부하 유발

    - innodb_flush_log_at_trx_commit

      · 0 : 1초마다 디스크 기록 및 동기화

      · 1 : commit 될 때마다 디스크 기록 및  동기화 (default)

      · 2 : commit 될 때마다 디스크로 기록 그리고 1초마다 동기화 (OS 메모리 버퍼에 기록 보장)

      ※ 디스크 기록은 InnoDB Write 동작으로 Log Buffer → OS Disk Cache로, 동기화는 InnoDB Flush 동작으로 OS Disk Cache → Disk

   2) 리두 로그 파일 크기

    - innodb_log_file_size : 1개 리두 로그 파일 크기

    - innodb_log_files_in_group : 리두 로그 파일 개수

  3) 리두 로그 아카이빙

    - Enterprise Backup / Xtrabackup 수행 시, 리두 로그 및 새로 추가된 리두 로그 엔트리를 복사하는데 이때 리두 로그가 빠르게 증가할 경우 새로 추가되는 리두 로그 엔트리는 복사하기 전 덮어쓰일 위험이 있으므로 이를 방지하기 위한 기능

    - innodb_redo_log_archive_dirs : 아카이빙된 리두 로그 저장되는 디렉토리, mysql user만 접근 가능해야 함

    - innodb_redo_log_archive_start / stop을 통해 아카이빙 시작 / 종료

    - 아카이빙 파일은 수동으로만 삭제해야 함

  4) 리두 로그 비활성화

    - 대용량 데이터를 한번에 적재하거나 데이터를 복구하는 경우, 리두 로그 비활성화해서 데이터 적재 시간 단축 가능

    - 적재 작업이 끝나면 반드시 리두 로그 다시 활성화 (MySQL 서버 재시작 시 리두 로그와 데이터 파일 동기화 상태를 검사하는데 비활성화되어 종료되면 서버 재시작 불가능할 수도 있음)