Real My SQL 8.0 읽고 공부하기 - ④-③,④ InnoDB 스토리지 엔진 아키텍처

③ MyISAM 스토리지 엔진 아키텍처

 

1) 키 캐시

InnoDB의 버퍼풀과 비슷한 역할을 하지만, 인덱스 대상으로만 작동한다. 그리고 쓰기 작업에 대해서만 부분적 버퍼링

역할을 한다. 키캐시 히트율은 100 - (Key_reads / Key_read_requests * 100)  로 알 수 있다.

Key_reads 는 인덱스를 디스크에서 읽어들이는 횟수를 저장하는 상태 변수, Key_read_requests는 키캐시로부터

인덱스를 읽은 횟수를 저장하는 상태 변수이다. (SHOW GLOBAL STATUS로 알수 있다.)

 

키 캐시 히트율은 99% 이상을 권장하며 미만이면 키 캐시 공간을 늘려야한다.

키 캐시는 영역설정을 해주어야 하는데, 어떤 스키마의 어떤 테이블의 인덱스를 캐시할지 설정을 해주어야한다.

 

 

---

 

2) 운영체제의 캐시 및 버퍼

 

MyISAM의 테이블 인덱스는 키캐시를 이용해 빠르게 검색할 수 있지만 디스크로부터의 테이블 데이터 I/O 의 캐싱이나 버퍼링기능은

존재하지 않는다. 그래서 운영체제의 캐시나 버퍼링을 사용해야하기 때문에 MySQL이나 다른 애플리케이션에서 남는 메모리를 모두

사용하게 되면 운영체제가 캐시해주지 못한다. 이에 따라 키캐시는 최대 40%정도로 하며 운영체제를 위한 메모리 공간을

꼭 남겨 두는 것이 좋다.

 

 

---

 

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

 

MyISAM은 프라이머리 키에의한 클러스터링 없이 데이터 파일이 힙(heap) 공간처럼 활용된다. 즉 키값과 무관하게 INSERT 되는 순서대로

데이터가 저장된다. 해당 레코드들은 모두 ROWID라는 물리적인 주소값을 가지는데 이 값을  포인터로 사용된다.

 

여기서 ROWID는 두가지 방식으로 사용될 수 있다.

 

● 고정길이 ROWID

최대로 가질 수 있는 레코드가 한정된 테이블을 생성한다.

 

● 가변길이 ROWID

시스템 변수값에 의해 설정된 바이트 수만큼 공간을 사용할 수 있는데, 초기값은 7바이트이며, 2~7바이트까지 가변적인 ROWID를

가지게 된다. 그중 첫번째 바이트는 ROWID의 길이를 저장하는 용도로 사용하고 나머지는 ROWID값을 저장하는데 사용한다.

 

 

---

 

4) MySQL 로그 파일

 

MySQL 서버를 진단할수 있는 도구들이 많이 지원되지만 이런 기능들은 많은 지식들이 필요하지만,

로그파일을 이용하면 원인을 쉽게 찾아 해결할 수 있는데, MySQL 서버의 해결책을 찾기 위해선 이 로그파일을 자세히 확인하는 습관이

필요하다.

 

1) 에러 로그 파일

my.ini 파일에 log.error라는 파라미터로 정의된 경로에 생성되며 .err라는 확장자가 붙은 파일로 생성된다.

 

 

여러가지 메시지가 나올 수 있는데 아래는 그 예시들이다.

 

1. MySQL 시작하는 과정과 관련된 정보성 및 에러 메시지 -> 설정 및 변수명 확인

2. 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB 트랜잭션 복구 메시지 -> innodb_force_recovery 변수 설정

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

4. 비정상적으로 종료된 커넥션 메시지 -> max_connect_errors(커넥션 실패나 연결종료 횟수) 변수 값 설정

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

6. MySQL의 종료 메시지 -> 종료 메시지가 없거나 스택트레이스 내용이 출력되면 세그먼테이션 폴트로 비정상 종료된 것이다.

 

 

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

 

서버에서 실행되는 쿼리가 어떤 것들이 있는지 전체 목록을 뽑아 검토해볼 때가있는데, 쿼리 로그를 활성화해서 쿼리를 쿼리 로그파일로

기록하게 한 다음, 그 파일을 검토한다. 슬로우 쿼리로그와는 다르게 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행중 에러가

발생하여도 일단 로그파일에 기록된다.

 

general_log_file이라는 변수에 경로가 설정되어있다. 테이블로도 저장할 수 있으므로 (log_output 파라미터) 상황에 따라서

SQL로 조회해야 할 수도 있다.

 

 

3) 슬로우 쿼리 로그

 

MySQL 쿼리 튜닝은 서비스 적용전 전체 튜닝과 서비스 운영중 전체적인 성능을 검사하거나 정기적인 점검을 위한 튜닝이 있다.

전자의 경우 대상 쿼리가 전체라서 모두 튜닝하면 되지만, 후자의 경우 어떤 쿼리가 슬로우 쿼리인지 알 수 없다.

슬로우 쿼리 로그는 이 방면에서 도움이 된다.

 

슬로우 쿼리는 long_query_time 변수값에 의해 쿼리 실행 후 실제 소요된 시간을 기준으로 슬로우 쿼리 로그에 기록할지 여부를

판단한다. (반드시 실행 완료되어야 기록된다.)

 

MySQL 잠금처리는 MySQL 엔진 레벨과 스토리지 엔진 레벨 두 가지 레이어로 처리된다. MyISAM과 MEMORY 엔진은 스토리지 엔진

레벨의 잠금이 존재하지 않는다.

 

슬로우 쿼리의 내용은 다음과 같다.

- Time: 쿼리가 종료된 시점 만약 쿼리가 언제 시작됬는지 확인하려면 Query_time 만큼 빼야한다.

- User@Host: 쿼리를 실행한 사용자의 계정

- Query_time: 쿼리가 실행되는데 걸린 전체 시간을 의미

- Lock_time: 테이블 잠금에 대한 대기 시간 (0이 아니라고 잠금 대기가 있었다고 판단하기는 어렵다. -> 잠금체크도 포함)

- Rows_examined: 이 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지 의미 (이 수에 비해 Rows_sent가 낮다면 튜닝 가치 있음)

단, GROUP BY 나 COUNT(), MIN() .. 집합 함수가 아닌 쿼리에 가능

- Rows_sent: 실제 몇 건의 처리 결과를 클라이언트를 보냈는지 의미

 

슬로우 쿼리 로그 혹은 제너럴 로그 파일 내용이 많아서 직접 검토하기 힘든 경우 Percona에서 개발한 Percona Tookit의 

pt-query-digest 스크립트 사용해보면 좋다.

분석결과는 3가지 그룹으로 나뉘어 저장 된다.

1. 슬로우 쿼리 통계: 모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행시간, 잠금대기 시간 등 평균 및 최소/최대값을 표시

2. 실행 빈도 및 누적 실행 시간순 랭킹: 각 쿼리별 응답 시간과 실행횟수를 보여준다. (Order By 가능)

3. 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보: Query ID별 쿼리를 랭킹에 표시된 순서대로 자세한 내용을 보여준다.