책너두 (Real MySQL 8.0 1권) 12일차 (~149p)

요약

• InnoDB, MyISAM, MEMORY 스토리지 엔진과, InnoDB 가 거의 많이 사용된다는 사실을 알게됨.
• MyISAM 스토리지 엔진의 아키텍쳐와 그 중 키 캐시에 대해 이해하게 됨.
• 키 캐시를 사용하지 않을 떄 운영체제의 기본 캐시 & 버퍼를 사용한다는 사실을 알게 됨.
• MyISAM 스토리지 엔진의 데이터 파일과 프라이머리 키(인덱스) 구조에 대해 알게 됨.
• 가변 길이 ROWID 에서 MyISAM 테이블 최대 크기가 2^(8*(7-1)) 에서 8을 왜 곱하는지 잘 모르겟음.. (해당 테이블의 칼럼에 들어갈 최대 값이 8바이트여서 그런건지.. 모르겟음) → 146page
• MySQL 트러블 슈팅을 위해 로그 파일을 보는 것을 습관해야 한다.
  • 각 에러로그를 보는 방법에 대해 이해하게 됨.

발췌

• MySQL 8.0 버전 부터는 MySQL 서버의 모든 기능을 InnoDB 스토리지 엔진만으로 구현할 수 있게 된 것이다.
• 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙이다.

메모

InnoDB 와 MyISAM, MEMORY 스토리지 엔진 비교

• 예전에는 MyISAM 이 기본 스토리지 엔진으로 사용되는 경우가 많았음.
  • 5.5 부터는 InnoDB 스토리지 엔진이 기본 스토리지 엔진으로 채택됐지만 MySQL 서버의 시스템 테이블 (사용자 인증 관련된 정보 & 복제 관련된 정보가 저장된 mysql DB 테이블)은 여전히 MyISAM 테이블을 사용했음.
  • 또, 전문 검색이나 공간 좌표 검색 기능은 MyISAM 테이블에서만 지원했음.
• 8.0 부터는 MySQL 서버의 모든 시스템 테이블이 InnoDB 스토리지 엔진으로 교체됨.
  • 공간 좌표 검색, 전문 검색 기능 모두 InnoDB 스토리지 엔진을 지원하도록 개선됨.
  • 8.0 버전 부터 MySQL 서버의 모든 기능을 InnoDB 스토리지 엔진만으로 구현할 수 있게 됨.
• InnoDB 스토리지 엔진에 대한 기능이 개선된 만큼 MyISAM 스토리지 엔진의 기능은 도태되는 상황임.
  • 이후 버전에서는 MyISAM 스토리지 엔진은 없어 질 것으로 예상됨.
• 가끔, MEMORY 스토리지 엔진의 이름이 ‘MEMORY’ 라는 이유로 과대 평가를 받는 경우가 많음.
  • MEMORY 스토리지 엔진 또한 동시 처리 성능에 있어서는 InnoDB 스토리지 엔진을 따라갈 수 없음.
  • MEMORY 스토리지 엔진은 모든 처리를 메모리에서만 수행하니 빠를 것이라 예상함.
    • 하나의 스레드에서만 데이터를 읽고 쓴다면 InnoDB 보다 빠를 수 있음.
      • 하지만 MySQL 서버는 온라인 트랜잭션 처리를 위한 목적으로 사용됨.
      • 온라인 트랜잭션 처리에서 동시 처리 성능이 매우중요한데, 동시에 몇십, 몇백 개의 클라이언트에서 쿼리 요청이 실행되는 경우라면 MEMORY 스토리지 엔진은 테이블 수준의 잠금으로 인해 제대로 된 성능을 내지 못하게 됨.
• MySQL 서버는 사용자 쿼리를 처리하기 위해 내부적으로 임시 테이블을 사용할 수도 있음.
  • 5.7 버전까지만 해도 MEMORY 스토리지 엔진이 내부 임시 테이블의 용도로 사용됨.
    • 하지만 MEMORY 스토리지 엔진은 가변 길이 타입의 칼럼을 지원하지 않는 문제가 있음.
      • 8.0 부터는 TempTable 스토리지 엔진이 MEMORY 스토리지 엔진들 대체해 사용되고 있음.
      • 8.0 에서 internal_tmp_mem_storage_engine 시스템 변수를 이용해서 내부 임시 테이블을 TempTable 엔진을 쓸지 MEMORY 엔진을 쓸지 선택 할 수 있다.
        • 기본 값은 TempTAble 임. 이를 MEMORY 스토리지 엔진으로 변경할 수 있음.
        • 근데 굳이 MEMORY 스토리지 엔진을 선택해서 얻을 수 있는 장점이 없어짐.
        • MEMORY 스토리지 엔진은 이전 버전과의 호환성 유지 차원에서 사용할 뿐일 것이고, 향후 버전에서 제거될 것으로 보임.

MyISAM 스토리지 엔진 아키텍처

• 위 그림은 MyISAM 스토리지 엔진의 간략한 구조임.
• MyISAM 스토리지 엔진의 성능에 영향을 미치는 요소인 키 캐시 와 운영체제의 캐시/버퍼 에 대해 살펴본다.

키 캐시

• InnoDB 버퍼 풀과 비슷한 역할을 한다. (Key cache, 키 버퍼 라고도 불림)
• MyISAM 키 캐시는 인덱스만을 대상으로 작동함.
• 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 함.
• 아래 수식을 통해 키 캐시가 얼마나 효율적으로 작동하는지 계산할 수 있다.

키 캐시 히트율(Hit rate) = 100 - (Key_reads / Key_read_requests * 100)

• Key_reads 는 인덱스를 디스크에서 읽어 들인 횟수를 저장하는 상태 변수임.
• Key_read_requests 는 키 캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수임.
• 위 상태 변수 값들은 SHOW GLOBAL STATUS LIKE 'Key%' 명령을 사용하면 된다.
• 매뉴얼에서 일반적으로 키 캐시를 이용한 쿼리의 비율을 99% 이상 유지하는걸 권장함.
  • 히트율 99% 미만이면 키 캐시를 조금 더 크게 설정하는 것이 좋음.
    • 32bit 운영체제에서는 하나의 키 캐시에 4GB 이상으 ㅣ메모리 공간을 설정할 수 없음.
    • 64bit 운영체제에서는 OS_PER_PROCESS_LIMIT 값에 설정된 크기만큼의 메모리를 할당할 수 있음.
    • 제한 값 이상의 키캐시를 할당하고 싶다면 default 키 캐시 이외에 별도의 이름이 붙은 키 캐시 공간을 설정해야 함.
      • default 키 캐시 공간을 설정하는 파라미터는 key_buffer_size 임.

key_buffer_size = 4GB
kbuf_board.key_buffer_size = 2GB
kbuf_comment.key_buffer_size = 2GB

• 위와 같이 설정하면 기본 키 캐시 4GB 와 kbuf_board, kbuf_comment 라는 이름의 키 캐시가 각각 2GB 씩 생성됨.
  • 하지만 기본 키 캐시 이외의 명명된 키 캐시 영역은 아무런 설정을 하지 않으면 메모리 할당만 해두고 사용하지 않게되므로 주의해야함.
    • 즉 기본이 아닌 명명된 추가 키 캐시는 어떤 인덱스를 캐시할지 MyISAM 스토리지 엔진에게 알려줘야 함.

mysql> CACHE INDEX db1.board, db2.board IN kbuf_board;
mysql> CACHE INDEX db1.comment, db2.comment IN kbuf_comment;

• 위와 같이 board 테이블의 인덱스는 kbuf_board 키 캐시를 사용하고, comment 테이블의 인덱스는 kbuf_comment 키 캐시를 사용하도록 설정할 수 있다.
• 이외의 나머지 테이블은 기본 키 캐시를 사용한다.

운영체제의 캐시 및 버퍼

• MyISAM 테이블의 인덱스는 키 캐시를 이용하기 떄문에 디스크 검색하지 않고도 빠르게 검색가능하다.
  • 인덱스가 아닌 데이터에 대해서는 디스크로부터의 I/O 를 해결해줄 만한 어떤 캐시나 버퍼링 기능도 없음. (MyISAM 에서..)
  • 따라서 MyISAM 테이블의 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청될 수밖에 없음.
    • 대부분의 운영체제는 디스크로부터 읽고 쓰는 파일에 대한 캐시나 버퍼링 메커니즘을 탑재함.
• 운영체제의 캐시 기능은 InnoDB 처럼 데이터의 특성을 알고 전문적으로 캐시나 버퍼링을 하지 못함.
  • 근데 없는거보다 나음.
• 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본원칙임.
  • 전체 메모리가 8GB 인데 MySQL 이나 다른 애플리케이션에서 메모리를 사용해 버린다면 운영체제가 캐시 용도로 사용할 수 있는 메모리 공간이 없어짐.
  • 따라서 MyISAM 테이블이 데이터 캐시를 하지못하고 MyISAM 테이블에 대한 쿼리 처리가 느려짐.
  • 데이터베이스에서 MyISAM 테이블을 주로 사용한다면 운영체제가 사용할 수 있는 캐시 공간을 위해 충분한 메모리를 비워 둬야 위 문제를 방지할 수 있음.
• MyISAM 을 사용하는 MySQL 에서 일반적으로 키 캐시는 최대 물리 메모리의 40% 이상을 넘지 않게 설정하여 나머지 메모리 공간을 운영체제가 자체적인 파일 시스템을 위한 캐시 공간을 마련하도록 하는게 좋다.

데이터 파일과 프라이머리 키(인덱스) 구조

• InnoDB 스토리지 엔진을 사용하는 테이블은 프라이머리 키에 의해 클러스터링 되어 저장됨
• MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼활용됨.
  • MyISAM 테이블에 레코드는 프라이머리 키와 무관하게 INSERT 된느 순서대로 데이터 파일에 저장됨.
  • 그리고 MyISAM 테이블에 저장되는 레코든느 모두 ROWID 라는 물리적인 주소값을 갖는다.
    • 프라이머리 키와 세컨더리 인덱스 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가짐.
• MyISAM 테이블에서 ROWID 는 가변 길이와 고정 길이, 2가지 방법으로 저장될 수 있음.
  • 고정 길이 ROWID
    • 자주 사용되지는 않지만 MyISAM 테이블 생성 시 MAX_ROWS 옵션을 사용할 수 있음.
      • 해당 옵션을 명시하면 MySQL 서버는 최대로 가질 수 있는 레코드가 한정된 테이블을 생성함.
      • 레코드 개수가 한정되면 MyISAM 테이블은 ROWID 값으로 4바이트 정수를 사용한다.
      • 레코드가 INSERT 된 순번이 ROWID 로 사용된다.
  • 가변 길이 ROWID
    • MAX_ROWS 옵션을 설정하지 않으면 MyISAM 테이블의 ROWID 는 최대 myisam_data_pointer_size 시스템 변수에 설정된 바이트 수만큼의 공간을 사용할 수 있음.
      • 해당 값은 기본값이 7이고, MyISAM 테이블의 ROWID 는 2바이트 부터 7바이트 까지 가변적인 ROWID 를 갖게 됨.
        • 이 중, 첫 번째 바이트는 ROWID 의 길이를 저장하는 용도로 사용, 나머지 공간은 실제 ROWID 를 저장함.
      • MyISAM 테이블이 가변적인 ROWID 를 가지면 데이터 파일에서 레코드의 위치(offset) 가 ROWID 로 사용됨.

MySQL 로그 파일

• 로그 파일을 이용하면 MySQL 서버의 깊은 내부 지식이 없어도 MySQL 의 상태나 부하를 일으키는 원인을 쉽게 찾아 해결할 수 있다.
• MySQL 서버에 문제가 생겼을 때는 다음에 나오는 로그 파일들을 자세히 확인하는 습관이 필요함.

에러 로그 파일

• MySQL 이 실행되는 도중 발생하는 에러나 경고 메시지가 출력되는 로그 파일임.
• 에러 로그 파일 위치는 MySQL 설정파일 (my.cnf) 에서 log_error 라는 이름의 파라미터로 정의된 경로에 생성됨.
• 설정 파일에 별도로 정의하지 않은 경우, 데이터 디렉터리 (datadir 파라미터에 설정된 디렉터리)에 .err 라는 확장자가 붙은 파일로 생성됨.
• 여기서 아래에 소개되는 메시지를 가장 자주보게 된다.

MySQL 이 시작하는 과정과 관련된 정보성 및 에러 메시지

• MySQL 설정 파일을 변경하거나 데이터베이스 비정상 종료후 다시 시작될 때 반드시 MySQL 에러 로그 파일을 통해 설정된 변수의 이름이나 값이 명확하게 설정되고 의도한 대로 적용됐는지 확인해야 함.
  • MySQL 서버가 정상 기동했고 (’mysqld: ready for connections’ 메시지로 확인 가능) 새로 변경하거나 추가한 파라미터에 대한 특별한 에러나 경고 메시지가 없으면 정상 적용된 것이라 생각하면 됨.
  • 특정 변수가 무시된 경우에는 MySQL 서버는 정상 기동했지만 해당 파라미터는 MySQL 에 적용되지 못했음을 의미
  • 변수명을 인식하지 못하거나 설정된 파라미터 값의 내용을 인식하지 못하는 경우 MySQL 서버가 에러 메시지 출력과 함꼐 시작하지 못했다는 메시지를 보여줌

마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB 의 트랜잭션 복구 메시지

• InnoDB 의 경우 MySQL 서버 비정상 종료시 완료되지 못한 트랜잭션을 재처리 하는 작업을 하면서 간단한 메시지가 출력되는데, 가끔 문제가 있어서 복구되지 못할 때, 이 메시지를 출력하고 MySQL 이 다시 종료되게 됨.
• 이 단계에서 발생하는 문제는 해결하기 어려운 문제점일 때가 많음.
• innodb_force_recovery 파라미터를 0 보다 큰 값으로 설정하고 재시작해야만 MySQL 이 시작될 수 있다.

쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지

• 쿼리 도중 발생하는 문제는 사전 예방이 어려움
• 주기적으로 에러 로그 파일을 검토하는 과정에서 알게됨.
• 쿼리 실행 도중 발생한 에러나 복제에서 문제가 될 만한 쿼리에 대한 경고 메시지가 에러 로그에 기록됨
  • 자주 에러 로그를 검토하는 것이 데이터베이스의 숨겨진 문제점을 해결하는데 많은 도움을 준다.

비정상적으로 종료된 커넥션 메시지(Aborted connection)

• 어떤 데이터베이스 서버 로그에 이 메시지가 상당히 많이 누적돼 있는 경우가 있음.
• 클라이언트 애플리케이션에서 정상적으로 접속 종료하지 못하고 프로그램이 종료된 경우 MySQL 서버의 에러 로그 파일에 위 에러가 기록됨.
  • 물론, 네트워크의 문제로 의도하지 않게 접속이 끊어지는 경우에도 이 메시지가 기록됨.
• 이 메시지가 아주 많이 기록된다면 애플리케이션의 커넥션 종료 로직을 한번 검토해볼 필요가 있음.
  • max_connect_errors 시스템 변수값이 너무 낮게 설정된 경우 클라이언트 프로그램이 MySQL 서버에 접속하지 못하고 “Host {host_name} is blocked” 라는 에러가 발생할 수 있음.
    • 이 메시지는 클라이언트 호스트에서 발생한 에러(커넥션 종료 or 강제 연결종료와 같은)의 횟수가 max_connect_errors 를 넘어서면 발생함.
    • max_connect_errors 늘려서 해결해도 되지만 이 에러가 어떻게 발생했는지 그 원인을 살펴보는게 좋음.

InnoDB 의 모니터링 또는 상태 조회 명령 (SHOW ENGINE INNODB STATUS 같은) 의 결과 메시지

• InnoDB 테이블 모니터링이나 락 모니터링, 또는 InnoDB 엔진 상태 조회하는 명령은 상대적으로 큰 메시지를 에러 로그 파일에 기록함.
• InnoDB 모니터링을 활성화하고 오래 유지하면 에러 로그 파일이 매우 커져서 파일 시스템의 공간을 다 사용해 버릴 수 있음.
  • 모니터링 사용 이후 다시 비활성화 해서 에러 로그 파일이 커지지 않도록 만들어야 함.

MySQL 의 종료 메시지

• 가끔 MySQL 이 아무도 모르게 종료됐거나 재시작 하는 경우가 있음.
  • 이때, 에러 로그 파일에서 MySQL이 마지막으로 종료되면서 출력한 메시지를 확인하는 것이 왜 MySQL 서버가 종료됐는지 확인하는 유일한 방법임.
    • 만약 누군가가 MySQL 서버를 종료 시켰다면 에러 로그 파일에 ‘Received SHUTDOWN from user …’ 라는 메시지를 확인할 수 있음.
    • 만약 아무런 종료 관련 메시지가 없거나 스택트레이스 (16진수 주소값이 잔뜩 출력되는..) 와 같은 내용이 출력된다면 MySQL 서버가 세그멘테이션 폴트로 비정상 종료된것으로 판단할 수 있음.
      • 세그멘테이션 폴트로 종료된 경우 스택 트레이스를 최대한 참조해서 MySQL의 버그와 연관이 있는지 조사하고, MySQL 버전을 업그레이드 하거나 회피책(WorkAround)을 찾는것이 최적의 방법임.

제너럴 쿼리 로그 파일(제너럴 로그 파일, General log)

• MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해볼 때가 있음.
  • 이때 쿼리 로그를 활성화 해서 쿼리를 쿼리 로그파일로 기록하게 하고 그 파일을 검토한다.
  • 쿼리 로그 파일에는 시간 단위로 실행됐던 쿼리의 내용이 모두 기록된다.
  • 슬로우 쿼리 로그와 달리, 제너럴 쿼리 로그는 실행되기 전에 MySQL 이 쿼리 요청을 받으면 바로 기록하기 떄문에 쿼리 실행중에 에러가 발생해도 일단 로그파일에 기록된다.
• 쿼리 로그 파일의 경로는 general_log_file 이라는 파라미터에 설정돼 있음.
• 쿼리 로그를 파일이 아닌 테이블에 저장하도록 설정할 수 있음.
  • 이 경우에는 파일이 아닌 테이블을 SQL 로 조회해서 검토해야 함.
• 쿼리 로그를 파일로 저장할 지 테이블로 저장할 지는 log_output 파라미터로 결정된다.