학습일지/데이터베이스
[스터디] 08. MyISAM 스토리지 엔진 아키텍처 및 MySQL 로그 파일
mandus
2024. 8. 30. 16:45
목차
이번에는 4장을 마무리하려고 합니다. MyISAM 스토리지 엔진 아키텍처와 MySQL 로그 파일에 대해 학습하겠습니다.
4.3 MyISAM 스토리지 엔진 아키텍처
4.3.1 키 캐시
- 키 캐시(Key cache)는 InnoDB의 버퍼 풀과 비슷한 역할을 하며, 키 버퍼라고도 불림.
- MyISAM 키 캐시는 인덱스만을 대상으로 작동하며, 인덱스의 디스크 쓰기 작업에 대해서만 부분적으로 버퍼링 역할을 함.
*키 캐시가 얼마나 효율적으로 작동하는지 확인하는 수식
키 캐시 히트율(Hit rate) = 100 - (Key_reads / Key_read_requests * 100)- Key_reads: 인덱스를 디스크에서 읽어 들인 횟수를 저장하는 상태 변수
- Key_read_requests: 키캐시로부터 인덱스를 읽은 횟수를 저장하는 상태 변수
- SHOW GLOBAL STATUS 명령으로 상태 값을 알아볼 수 있음.
mysql> SHOW GLOBAL STATUS LIKE 'Key%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 13 |
| Key_blocks_used | 1 |
| Key_read_requests | 4 |
| Key_reads | 1 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+-------+- 매뉴얼에서는 일반적으로 키 캐시를 이용한 쿼리의 비율(히트율, Hit rate)을 99% 이상으로 유지하라고 권장함. 히트율이 99% 미만이라면 키 캐시를 조금 더 크게 설정하는 것이 좋음.
- 32비트 운영체제에서는 하나의 키 캐시에 4GB 이상의 메모리 공간을 설정할 수 없음.
- 64비트 운영체제에서는 OS_PER_PROCESS_LIMIT 값에 설정된 크기만큼의 메모리를 할당할 수 있음.
- 제한 값 이상의 키 캐시를 할당하려면 기본(Default) 키 캐시 이외에 별도의 명명된 키 캐시 공간을 설정해야 함.
key_buffer_size = 4GB
kbuf_board.key_buffer_size = 2GB
kbuf_comment.key_buffer_size = 2GB- 기본(Default)이 아닌 명명된 추가 키 캐시는 어떤 인덱스를 캐시할지 MySQL(MyISAM 스토리지 엔진)에 알려줘야 함.
-- 명명된 각 키 캐시에 게시판 테이블(board)의 인덱스와 코멘트 테이블(comment)의 인덱스가 캐시되도록 설정
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_commnet 키 캐시를 사용할 수 있음. 나머지 테이블의 인덱스는 예전과 동일하게 기본 키 캐시를 사용함.
4.3.2 운영체제의 캐시 및 버퍼
- MyISAM 테이블의 인덱스는 키 캐시를 이용해 디스크를 검색하지 않고도 빠르게 검색할 수 있음.
- 하지만 MyISAM 테이블의 데이터에 대해서는 디스크로부터의 I/O를 해결해 줄 만한 어떠한 캐시나 버퍼링 기능도 가지고 있지 않음.
- MyISAM 테이블의 데이터 읽기나 쓰기 작업은 항상 운영체제의 디스크 읽기 또는 쓰기 작업으로 요청될 수밖에 없음.
- 운영체제의 캐시 공간은 남는 메모리를 사용하는 것이 기본 원칙임.
- 전체 메모리가 8GB인데 MySQL이나 다른 애플리케이션에서 메모리를 모두 사용하면, 운영체제가 캐시 용도로 사용할 수 있는 메모리 공간이 없음.
- 이런 경우, MyISAM 테이블의 데이터를 캐시하지 못하며 MyISAM 테이블에 대한 쿼리 처리가 느려짐.
- MyISAM이 주로 사용되는 MySQL에서 키 캐시는 최대 물리 메모리의 40% 이상을 넘지 않게 설정함. 나머지 메모리 공간은 운영체제가 자체적인 파일 시스템을 위한 캐시 공간을 마련할 수있게 해야 함.
4.3.3 데이터 파일과 프라이머리 키(인덱스) 구조
- InnoDB 테이블은 프라이머리 키에 의해서 클러스터링되어 저장되지만, MyISAM 테이블은 프라이머리 키에 의한 클러스터링 없이 데이터 파일이 힙(Heap) 공간처럼 활용됨.
- MyISAM 테이블에 레코드는 프라이머리 키 값과 무관하게 INSERT되는 순서대로 데이터 파일에 저장됨.
- MyISAM 테이블에 저장되는 레코드는 모두 ROWID라는 물리적인 주솟값을 가지는데, 프라이머리 키와 세컨더리 인덱스는 모두 데이터 파일에 저장된 레코드의 ROWID 값을 포인터로 가짐.
- MyISAM 테이블에서 ROWID는 가변 길이와 고정 길이의 두 가지 방법으로 저장될 수 있음.
- 고정 길이 ROWID
- 자주 사용되지 않음.
- MyISAM 테이블을 생성할 때 MAX_ROWS 옵션을 명시하면 MySQL 서버는 최대로 가질 수 있는 레코드가 한정된 테이블을 생성함.
- MAX_ROWS 옵션에 의해 MyISAM 테이블이 가질 수 있는 레코드의 개수가 한정되면 MyISAM 테이블은 ROWID 값으로 4바이트 정수를 사용함. 레코드가 INSERT된 순번이 ROWID로 사용됨.
- 가변 길이 ROWID
- MyISAM 테이블을 생성할 때 MAX_ROWS 옵션을 설정하지 않으면 MyISAM 테이블의 ROWID는 최대 myisam_data_ pointer_size 시스템 변수에 설정된 바이트 수만큼의 공간을 사용할 수 있음.
- myisam_data_pointer_size 시스템 변수의 기본값은 7이므로 MyISAM 테이블의 ROWID는 2바이트부터 7바이트까지 가변적인 ROWID를 갖게 됨.
- 그중에서 첫 번째 바이트는 ROWID의 길이를 저장하는 용도로 사용하고 나머지 공간은 실제 ROWID를 저장 하는 데 사용함.
- MyISAM 테이블이 가변적인 ROWID를 가지면 데이터 파일에서 레코드의 위치(offset)가 ROWID로 사용됨. MAX_ROWS 옵션이 명시되지 않은 MyISAM 테이블의 최대 크기는 256TB가 됨.
- 고정 길이 ROWID
4.4 MySQL 로그 파일
- 로그 파일을 이용하면 MySQL 서버의 깊은 지식이 없어도 MySQL의 상태나 부하를 일으키는 원인을 쉽게 찾아서 해결할 수 있음.
4.4.1 에러 로그 파일
- 에러 로그 파일: MySQL이 실행되는 도중에 발생하는 에러나 경고 메시지가 출력되는 로그 파일
- 에러 로그 파일의 위치는 MySQL 설정 파일(my.cnf)에서 log_error라는 이름의 파라미터로 정의된 경로에 생성됨.
- MySQL 설정 파일에 별도로 정의되지 않은 경우에는 데이터 디렉터리(datadir 파라미터에 설정된 디렉터리)에 .err라는 확장자가 붙은 파일로 생성됨.
4.4.1.1 MySQL이 시작하는 과정과 관련된 정보성 및 에러 메시지
- MySQL의 설정 파일을 변경하거나 데이터베이스가 비정상적으로 종료된 이후 다시 시작하는 경우에는 반드시 MySQL 에러 로그 파일을 통해 설정된 변수의 이름이나 값이 명확하게 설정되고 의도한 대로 적용됐는지 확인해야 함.
- MySQL 서버가 정상적으로 기동했고('mysqld: ready for connections' 메시지 확인), 새로 변경하거나 추가한 파라미터에 대한 특별한 에러나 경고성 메시지가 없다면 정상적으로 적용된 것으로 판단함.
- 그렇지 않고 특정 변수가 무시(ignore)된 경우에는 MySQL 서버는 정상적으로 기동하지만 해당 파라미터는 MySQL에 적용되지 못했음.
- 그리고 변수명을 인식하지 못하거나 설정된 파라미터 값의 내용을 인식하지 못하는 경우에는 MySQL 서버가 에러 메시지를 출력하고 시작하지 못했다는 메시지를 보여줌.
4.4.1.2 마지막으로 종료할 때 비정상적으로 종료된 경우 나타나는 InnoDB의 트랜잭션 복구 메시지
- InnoDB의 경우에는 MySQL 서버가 비정상적 또는 강제적으로 종료됐다면 다시 시작되면서 완료되지 못한 트랜잭션을 정리함. 디스크에 기록되지 못한 데이터가 있다면 다시 기록하는 재처리 작업을 함.
- 문제가 있어서 복구되지 못할 때는 해당 에러 메시지를 출력하고 MySQL은 다시 종료됨. innodb_force_recovery 파라미터를 0보다 큰 값으로 설정하고 재시작해야만 MySQL이 시작될 수도 있음.
4.4.1.3 쿼리 처리 도중에 발생하는 문제에 대한 에러 메시지
- 쿼리 도중 발생하는 문제점은 사전 예방이 어려움. 주기적으로 에러 로그 파일을 검토하는 과정에서 알게 됨.
- 쿼리의 실행 도중 발생한 에러나 복제에서 문제가 될 만한 쿼리에 대한 경고 메시지가 에러 로그에 기록됨. 따라서 자주 에러 로그 파일을 검토하는 것이 데이터베이스의 숨겨진 문제점을 해결하는 데 도움됨.
4.4.1.4 비정상적으로 종료된 커넥션 메시지(Aborted connection)
- 어떤 데이터베이스 서버의 로그 파일을 보면 이 메시지가 상당히 많이 누적되어 있는 경우가 있음.
- 클라이언트 애플리케이션에서 정상적으로 접속 종료를 하지 못하고 프로그램이 종료된 경우 MySQL 서버의 에러 로그 파일에 이런 내용이 기록됨.
4.4.1.5 InnoDB의 모니터링 또는 상태 조회 명령(SHOW ENGINE INNODB STATUS 같은)의 결과 메시지
- InnoDB의 테이블 모니터링이나 락 모니터링, 또는 InnoDB의 엔진 상태를 조회하는 명령은 상대적으로 큰 메시지를 에러 로그 파일에 기록함.
- InnoDB의 모니터링을 활성화 상태로 만들어 두고 유지하는 경우에는 에러 로그 파일이 매우 커져서 파일 시스템의 공간을 다 사용할 수도 있음. 따라서 모니터링을 사용한 이후에는 다시 비활성화해서 에러 로그 파일이 커지지 않게 만들어야 함.
4.4.1.6 MySQL의 종료 메시지
- MySQL이 아무도 모르게 종료되어 있거나 아무도 모르게 재시작되는 경우에는 에러 로그 파일에서 MySQL이 마지막으로 종료되면서 출력한 메시지를 확인해야 함. 이 방법이 왜 MySQL 서버가 종료됐는지 확인하는 유일한 방법임.
- 누군가가 MySQL 서버를 종료시 켰다면 에러 로그 파일에서 'Received SHUTDOWN from user ...'이라는 메시지를 확인할 수 있음.
- 아무런 종료 관련 메시지가 없거나 스택 트레이스(대표적으로 16진수의 주솟값이 잔뜩 출력되는)와 같은 내용이 출력되는 경우에는 MySQL 서버가 세그먼테이션 폴트(Segmentation fault)로 비정상적으로 종료된 것으로 판단함.
- 세그먼테이션 폴트로 종료된 경우에는 스택 트레이스의 내용을 최대한 참조해서 MySQL의 버그와 연관이 있는지 조사한 후 MySQL의 버전을 업그레이드하거나 회피책(WorkAround)을 찾음.
4.4.2 제너럴 쿼리 로그 파일(제너럴 로그 파일, General log)
- MySQL 서버에서 실행되는 쿼리로 어떤 것들이 있는지 전체 목록을 뽑아서 검토해 볼 때, 쿼리 로그를 활성화해서 쿼리를 쿼리 로그 파일로 기록하게 한 다음, 그 파일을 검토함.
- 쿼리 로그 파일에는 시간 단위로 실행됐던 쿼리의 내용이 모두 기록됨. 슬로우 쿼리 로그와는 다르게 제너럴 쿼리 로그는 실행되기 전에 MySQL이 쿼리 요청을 받으면 바로 기록하기 때문에 쿼리 실행 중에 에러가 발생해도 로그 파일에 기록됨.
- 쿼리 로그를 파일이 아닌 테이블에 저장하도록 설정할 수 있으므로 파일이 아닌 테이블을 SQL로 조회해서 검토해야 함.
4.4.3 슬로우 쿼리 로그
- MySQL 서버의 쿼리 튜닝은 서비스가 적용되기 전에 전체적으로 튜닝하는 경우와 서비스 운영 중에 MySQL 서버의 전체적인 성능 저하를 검사하거나 정기적인 점검을 위한 튜닝으로 나눌 수 있음.
- 전자의 경우에는 검토해야 할 대상 쿼리가 전부라서 모두 튜닝하면 됨.
- 후자의 경우에는 어떤 쿼리가 문제의 쿼리인지 판단하기가 상당히 어려움. 서비스에서 사용되는 쿼리 중에서 어떤 쿼리가 문제인지를 판단하는 데 슬로우 쿼리 로그가 도움이 됨.
- 슬로우 쿼리 로그 파일에는 long_query_time 시스템 변수에 설정한 시간 이상의 시간이 소요된 쿼리가 모두 기록됨.
- log_output 옵션을 이용해 슬로우 쿼리 로그를 파일로 기록할지 테이블로 기록할지 선택할 수 있음.
- log_output 옵션을 TABLE로 설정하면 제너럴 로그나 슬로우 쿼리 로그를 mysql DB의 테이블(general_log와 slow_log 테이블)에 저장함. FILE로 설정하면 로그의 내용을 디스크의 파일로 저장함.
- log_output 옵션을 TABLE로 설정하더라도 CSV 파일로 저장하는 것과 동일하게 작동함.
- MySQL 의 잠금 처리는 MySQL 엔진 레벨과 스토리지 엔진 레벨의 두 가지 레이어로 처리됨. MyISAM 이나 MEMORY 스토리지 엔진과 같은 경우에는 별도의 스토리지 엔진 레벨의 잠금을 가지지 않지만, InnoDB의 경우 MySQL 엔진 레벨의 잠금과 스토리지 엔진 자체 잠금을 가지고 있음.
# Time: 2020-07-19T15:44:22.178484+09:00
# User@Host: root[root] @ localhost [] Id: 14
# Query_time: 1.180245 Lock_time: 0.002658 Rows_sent: 1 Rows_examined: 2844047
use employees;
SET timestamp=1595141060;
select emp_no, max(salary) from salaries;- 슬로우 쿼리 로그 내용
- 'Time': 쿼리가 종료된 시점. 쿼리가 언제 시작됐는지 확인 하려면 'Time' 항목에 나온 시간에서 'Query_time'만큼 빼야 함.
- 'User@Host': 쿼리를 실행한 사용자의 계정
- 'Query_time': 쿼리가 실행되는 데 걸린 전체 시간.
- 'Lock_time': 두 가지 레벨의 잠금 가운데 MySQL 엔진 레벨에서 관장하는 테이블 잠금에 대한 대기 시간만 표현함. 'Lock_time'에 표기된 시간은 실제 쿼리가 실행되는 데 필요한 잠금 체크와 같은 코드 실행 부분의 시간까지 모두 포함됨. 이 값이 매우 작은 값이면 무시함.
- 'Rows_examined': 쿼리가 처리되기 위해 몇 건의 레코드에 접근했는지를 의미함.
- 'Rows_sent': 실제몇 건의 처리 결과를 클라이언트로 보냈는지를 의미함. 일반적으로 'Rows_examined'의 레코드 건수는 높지만 'Rows_sent'에 표시된 레코드 건수가 상당히 적으면 이 쿼리는 조금 더 적은 레코드만 접근하도록 튜닝하는 게 좋음.(GROUP BY나 COUNT(), MIN(), MAX(), AVG() 등과 같은 집합 함수가 아닌 쿼리인 경우만 해당)
- MyISAM이나 MEMORY 스토리지 엔진에서는 테이블 단위의 잠금을 사용하고 MVCC와 같은 메커니즘이 없기 때문에 SELECT 쿼리라고 하더라도 Lock_time이 1초 이상 소요될 가능성이 있음.
- 일반적으로 슬로우 쿼리 또는 제너럴 로그 파일의 내용이 많아서 직접 쿼리를 하나씩 검토하기에는 시간이 많이 걸리거나 어느 쿼리를 집중적으로 튜닝해야 할지 식별하기가 어려울 수도 있음. 이런 경우에 Percona에서 개발한 Percona Toolkit 10 의 pt-query-digest 스크립트를 이용하면 쉽게 빈도나 처리 성능별로 쿼리를 정렬해서 살펴볼 수 있음.
## General Log 파일 분석
linux> pt-query-digest --type='genlog' general.log > parsed_general.log
## Slow Log 파일 분석
linux> pt-query-digest --type='slowlog' mysql-slow.log > parsed_mysql-slog.log4.4.3.1 슬로우 쿼리 통계
- 분석 결과의 최상단에 표시됨.
- 모든 쿼리를 대상으로 슬로우 쿼리 로그의 실행 시간(Exec time), 그리고 잠금 대기 시간(Lock time) 등에 대해 평균 및 최소/최대 값을 표시함.
# 119s users time, 450ms system time, 94.90M rss, 308.31M vsz
# Current date: Sun Jul 19 07:10:33 2020
# Hostname: shop-db-001
# Files: mysql-slow.log
# Overall: 434.11k total, 916 unique, 0.03 QPS, 0.30x concurrency ________
# Time range: 2020-02-23T19:35:20 to 2020-07-19T07:03:48
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 3845025s 1s 29119s 9s 45s 69s 2s
# Lock time 80s 0 51ms 184us 384us 269us 125us
# Rows sent 15.23G 0 23.09M 36.79k 97.04k 381.15k 0.99
# Rows examine 544.53G 0 267.39M 1.28M 9.30M 5.40M 0.99
# Query size 205.97M 13 388.61k 497.42 964.41 1.53k 223.144.4.3.2 실행 빈도 및 누적 실행 시간순 랭킹
- 각 쿼리별로 응답 시간과 실행 횟수를 보여주는데, pt-query-digest 명령 실행 시 --order-by 옵션으로 정렬 순서를 변경할 수 있음.
- Query ID는 실행된 쿼리 문장을 정규화(쿼리에 사용된 리터럴을 제거)해서 만들어진 해시 값을 의미함. 같은 모양의 쿼리라면 동일한 Query ID를 가짐.
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== ========================= ================== ===== ========== =====
# 1 0x47525E2A043E8AF899FD... 2311636.7007 60.1% 35353 65.3873 43.41 SELECT orders order_details
# 2 0xB77F2FFEBF2338FD3B6C... 173684.6297 4.5% 73740 2.3554 0.21 SELECT users_details
# 3 0x00EFC721F6C35CC935AD... 152724.7593 4.0% 2153 70.9358 32.90 SELECT orders order_details
# 4 0xE97971F13DB2D3E78175... 117188.5615 3.0% 36451 3.2150 0.47 SELECT users
...4.4.3.3 쿼리별 실행 횟수 및 누적 실행 시간 상세 정보
- Query ID별 쿼리를 쿼리 랭킹에 표시된 순서대로 자세한 내용을 보여줌.
- 랭킹별 쿼리에서는 대상 테이블에 대해 어떤 쿼리인지만을 표시함. 실제 상세한 쿼리 내용은 개별 쿼리의 정보를 확인하면 됨.
# Query 1: 0.00 QPS, 0.27x concurrency, ID 0x47525E2A043E8AF899FDBA8EB30ADC13 at byte 216208909
# This item is included in the report because it matches --limit.
# Scores: V/M = 43.41
# Time range: 2020-03-13T04:50:29 to 2020-06-20T21:34:19
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 8 35353
# Exec time 60 2311637s 1s 252s 65s 175s 53s 49s
# Lock time 15 13s 211us 1ms 361us 467us 57us 348us
# Rows sent 4 672.94M 76 19.53k 19.49k 19.40k 733.28 19.40k
# Rows examine 60 328.28G 39.06k 36.70M 9.51M 25.91M 7.82M 7.29M
# Query size 16 33.78M 996 1003 1001.94 964.41 0 964.41
# String:
# Databases shop_db
# Hosts 192.168.1.190
# userss shopping
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s #########
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `shop_db` LIKE 'orders'\G
# SHOW CREATE TABLE `shop_db`.`orders`\G
# SHOW TABLE STATUS FROM `shop_db` LIKE 'order_details'\G
# SHOW CREATE TABLE `shop_db`.`order_details`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM (SELECT
...
이 글은 『Real MySQL 8.0 (1권)』 책을 학습한 내용을 정리한 것입니다.