DB 튜닝 개념 — 락·캐시·인덱스·체크포인트·메모리

개요 — 무엇을 하나

Service ▸ Database 화면은 등록된 DB 수집기(access=database)에 직접 연결해 얻은 실시간 부하·구조 지표를 보여줍니다. 단순히 숫자를 나열하지 않고, 각 핵심 지표(락 대기·캐시 적중률·인덱스 사용률·체크포인트·dirty 페이지)에 디자인된 교육용 hover 팝오버를 달아 "왜 이 값이 나쁜가 / 어떻게 개선하나"를 그 자리에서 배우게 합니다.

이 문서는 그 팝오버가 가르치는 개념을 요약합니다. 전체 설명 원문(사전)은 화면의 라이브 팝오버에 단일 소스로 존재하므로(resources/lang/{ko,en}/dbmon.php 의 edu 키), 실제 상황에서는 지표 위에 마우스를 올려 컨텍스트별 상세 문구를 확인하세요.

이 화면은 지표 관제(무엇이 지금 나쁜가), 팝오버는 원인 교육(왜·어떻게), 실제 설정 스냅샷은 인스턴스 파라미터([param] · nginx/my.cnf/sysctl)에 있습니다. 세 곳을 함께 보면 진단 → 근거 → 조치가 이어집니다.

언제 쓰나

DB 카드가 주의(degraded) 또는 **중단(down)**으로 뜨고 그 이유(연결 포화·높은 동시성·쿼리 밀림·slow 증가)를 이해하고 싶을 때.
캐시 적중률이나 인덱스 사용률 점수가 낮게 나올 때, 근본 원인과 개선 레버(버퍼풀 크기·인덱스 추가·커버링 인덱스)를 확인할 때.
**행 락 대기(row-lock wait)**가 치솟아 어느 쿼리·패턴이 경합을 만드는지 좁힐 때.
메모리 관리 상세에서 dirty 페이지·체크포인트 파형이 위험 구간으로 붙는 이유를 배우고 튜닝 방향(버퍼풀/redo 로그 증설)을 잡을 때.
DBA·DevOps 온보딩 — MySQL/MariaDB InnoDB 내부(락·버퍼풀·redo)를 실데이터로 학습.

화면 구성

교육용 팝오버가 붙는 위치:

위치	지표	팝오버가 가르치는 것
카드 풋 우측 · `캐시` 배지	캐시 적중률(버퍼풀)	점수 근거 공식 + 적중률 높이는 4가지 방법
카드 풋 우측 · `인덱스` 배지	인덱스 사용률	점수 근거 공식 + 인덱스 히트 높이는 5가지 방법
동시 실행 부하 · `lock`	row-lock 평균 대기(ms)	락 대기가 높아지는 7가지 원인(빈도순)
헤더/메모리 상세 · `Checkpoint … redo 대비 %`	체크포인트 age	redo 로그 압력의 의미와 오르내림 해석
상세(접이식) · 메모리 관리	Dirty 페이지 · 체크포인트 시계열	임계(LWM/MAX) overlay + flush 에피소드 주석

카드 본문 자체는 두 부하 축으로 요약됩니다:

동시 실행 부하 — 연결 포화율(링 게이지)·실행 중(threads_running)·대기(밀림)(waiting)·lock.
처리량 — 총 QPS 스파크·slow 비율(slow_qps)·slow 누적.

하단 스키마 분석(접이식)은 총 용량·상위 스키마·용량 상위 테이블·엔진 구성을 보여줍니다(튜닝 개념과 별개, information_schema 유래).

사용법

DB 카드에서 문제 지표(빨강/노랑 배지 또는 높은 값)를 찾습니다.
해당 지표 라벨/배지 위에 마우스를 올려 컨텍스트 팝오버를 엽니다 — 원인·공식·개선법이 표시됩니다.
상세를 펼쳐 메모리 관리 시계열에서 dirty/체크포인트 파형과 임계 절취선을 확인합니다.
팝오버가 가리키는 설정 레버(예: innodb_buffer_pool_size, 인덱스 추가)를 대상 DB에서 적용하거나, 인스턴스 파라미터의 현재 값과 대조합니다.

아래는 각 개념의 요약 참조입니다(라이브 팝오버가 정본).

행 락 대기 (row-lock wait) — 7가지 원인

행 잠금을 획득하기까지 InnoDB가 기다린 평균 시간(ms). 높을수록 경합이 큽니다. 빈도순:

#	원인	요지
1	락을 오래 쥔 트랜잭션	`BEGIN … UPDATE …` 후 commit 지연(외부 API·사용자 대기·느린 쿼리) → 그 행 대기열이 누적. 가장 흔함.
2	핫 로우 경합	여러 트랜잭션이 같은 행(카운터·재고·요약행·시퀀스)을 동시 갱신 → 직렬화되어 대기 누적.
3	인덱스 없는 UPDATE/DELETE	WHERE가 인덱스를 못 타면 필요 이상으로 넓게 잠금 → 무관 트랜잭션까지 차단(실무 숨은 1등).
4	갭 락 / 넥스트키 락	REPEATABLE READ 범위 잠금이 행 사이 gap까지 잠가 INSERT 차단. 범위 UPDATE + 동시 INSERT 패턴.
5	대량 배치 DML	수만 행을 건드리는 UPDATE/DELETE가 많은 락을 오래 보유.
6	잠금 보유자가 느림	락 쥔 쿼리가 I/O·CPU 대기로 느리면 락을 오래 붙듦 — slow 증가와 락 대기는 함께 움직임.
7	외래키(FK) 검사	FK 테이블 갱신 시 부모 행 공유락 → 대기 가중.

#3(인덱스 없는 UPDATE)이 화면상 락 대기 급증의 숨은 1등입니다. SHOW ENGINE INNODB STATUS의 TRANSACTIONS 섹션과 인덱스 사용률 점수를 함께 보세요.

캐시 적중률 (InnoDB 버퍼풀)

대부분의 읽기가 디스크가 아닌 메모리(버퍼풀)에서 처리되면 높습니다.

적중률 = 1 − Δinnodb_buffer_pool_reads / Δinnodb_buffer_pool_read_requests

등급	임계
우수	≥ 99.5%
양호	≥ 98%
보통	≥ 90%

높이는 방법(요약): innodb_buffer_pool_size 증설(RAM의 50~75% 관행) · 풀스캔 축소로 버퍼풀 오염 방지 · 작업셋 축소(불필요 컬럼·BLOB 분리·아카이브) · innodb_buffer_pool_instances로 경합 완화 + 재시작 시 버퍼풀 덤프/로드.

인덱스 사용률

인덱스로 행을 찾은 읽기 vs 풀스캔으로 다음 행을 훑은 읽기의 비율.

인덱스 사용률 = Δhandler_read_key / (Δhandler_read_key + Δhandler_read_rnd_next)

등급	임계
우수	≥ 90%
양호	≥ 70%
보통	≥ 40%

높이는 방법(요약): EXPLAIN으로 풀스캔(type=ALL)·filesort 탐지 후 WHERE/JOIN/ORDER BY 컬럼 인덱스화 · 복합 인덱스는 선택도 높은 컬럼을 앞(좌측 접두 규칙) · 컬럼에 함수·형변환 금지(WHERE DATE(col)=… → 범위 조건) · 커버링 인덱스 · 중복/미사용 인덱스 제거.

체크포인트 age (redo 로그 압력)

마지막 체크포인트 이후 redo 로그가 전진한 양(바이트) = 아직 데이터파일에 flush되지 않은 변경, 크래시 시 복구할 작업량. 헤더의 **redo 대비 %**가 flush 폭주 절벽까지의 거리입니다.

상태	redo 대비 %	의미
안전	< 50%	변경을 제때 flush 중
주의	50 ~ 75%	쓰기 유입이 flush보다 빨라지는 중, 강제 flush 근접
위험	≥ 75%	async/sync flush 임계 근접 — 강제 flush로 지연·I/O 폭주 위험

해석: 올라감 = 쓰기 > flush(redo 차오름) · 내려감 = flush 따라잡음(건강) · 톱니 = 쌓임↔flush 정상 리듬. 75% 접근 시 버퍼풀·innodb_log_file_size 증설 검토.

Dirty 페이지 flush (메모리 관리)

버퍼풀에서 아직 디스크에 반영되지 않은(변경된) 페이지. 적절한 값 = 서버 자체 flush 임계(innodb_max_dirty_pages_pct = MAX, innodb_max_dirty_pages_pct_lwm = LWM). 전문가 권고: dirty 페이지는 LWM 아래에서 순환해야 건강.

상태	조건	의미
안정(good)	임계 아래 순환(정상 sawtooth)	flush 시 DB가 고통받지 않음
주의(warn)	저수위(LWM) 초과 preflush 구간	쓰기 압력·checkpoint 지연 관찰
위험(crit)	임계(MAX) 도달	aggressive flush → 지연 스파이크·I/O 폭주(버퍼풀/redo 증설)

메모리 관리 시계열은 dirty 페이지 위에 LWM/MAX 절취선을 overlay하고, 축출/flush 에피소드를 핀으로 주석합니다. 우측 지표에 대기 쓰기(pending writes)·row-lock 평균도 함께 표시됩니다.

데이터 소스

교육 개념이 참조하는 실제 카운터와 저장:

개념	원천 카운터 / 상태	비고
락 대기	`SHOW GLOBAL STATUS` · `information_schema.processlist`	대기(waiting) 산출, PROCESS 권한 필요
캐시 적중률	`Innodb_buffer_pool_reads` / `_read_requests`	샘플 간 델타
인덱스 사용률	`Handler_read_key` / `Handler_read_rnd_next`	샘플 간 델타
체크포인트 · dirty	InnoDB 상태(checkpoint age·dirty pages·pending)	메모리 관리 시계열
QPS · slow	`Questions` · `Slow_queries`(누적)	델타로 QPS/slow rate 파생

시계열은 infr_db_metric 테이블(마이그레이션 000681)에 적재됩니다. App\Services\Database\DbCollectorService가 등록 DB에 직접 연결해 SHOW GLOBAL STATUS + @@max_connections + processlist를 읽고, 이전 샘플과의 델타로 파생 지표를 계산합니다.
수집은 infr:collect-db 커맨드(기본 매분 스케줄, 단발 종료). 화면 진입 시 마지막 샘플이 오래되면 ensureFresh()가 즉석 1회 샘플을 뜹니다.
화면 폴링 주기: 30초. 연결 누수 방지를 위해 수집기는 finally에서 DB::purge + PDO 타임아웃을 사용합니다.

카드 상태(degraded) 임계(OpsPolicy::dbThresholds() 기본값 — 운영 정책에서 조정):

조건	기본 임계	사유 라벨
연결 포화율 `conn_pct` ≥	80%	연결 포화
`threads_running` ≥	40	높은 동시성
`waiting`(대기 쿼리) ≥	5	쿼리 밀림
`slow_qps` ≥	1/s	slow 쿼리 증가

권한

읽기(화면·팝오버 열람): demo(level 1) 이상. 팝오버는 순수 교육 콘텐츠라 별도 게이팅·감사가 없습니다.
DB 상태 임계 조정: 운영 정책에서 관리 — admin(level 5/10 · infr.level:N).
DB 수집기 등록/수정: 수집기 관리 admin(level 5/10). 비밀번호는 수집기에 Crypt로 암호화 저장하거나 cred_ref = env:PREFIX로 .env 자격 세트를 재사용합니다(하드코딩 금지).

문제해결 · FAQ

Q. 대기(밀림)이 "자기 연결만"으로 표시됩니다. 서버 전역 대기 쿼리를 세려면 DB 계정에 PROCESS 권한이 필요합니다. 없으면 자기 연결만 집계합니다. sql/infr_db_monitor_grant.sql 참조(전용 모니터링 계정 권장).

Q. 캐시/인덱스 점수가 비어 있습니다(N/A). 파생 지표는 두 샘플 간 델타로 계산됩니다. 수집기가 방금 시작했거나 카운터가 리셋(재시작)되면 첫 샘플에서는 점수가 나오지 않습니다 — 다음 주기까지 기다리세요.

Q. 카드가 최근 샘플 없음(stale)으로 뜹니다. economy 모드에서는 콘솔 사용자가 없으면 수집을 절약합니다. 진입 시 ensureFresh(45s)가 즉석 샘플을 뜨지만, 스케줄러가 밀렸다면 잠시 pending으로 강등될 수 있습니다. 상시 수집이 필요하면 운영 정책에서 DB 수집 모드를 always로.

Q. 팝오버 문구를 번역/수정하려면? 교육 사전은 resources/lang/{ko,en}/dbmon.php의 edu 키 하나에만 존재합니다(단일 소스). 다른 로케일은 en으로 폴백됩니다.

Q. 락 대기 원인을 실제로 좁히려면? 대상 DB에서 SHOW ENGINE INNODB STATUS(TRANSACTIONS·LATEST DETECTED DEADLOCK)와 information_schema.INNODB_TRX / data_locks를 확인하고, 위 7원인 표와 대조하세요.