jisangs/chocomae
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
chocomae/doc/plan/db/260916-load-and-capacity-check/00-check-methods.md
T

42 KiB
Raw Permalink Blame History

운영 서버 부하·용량 확인 방법 (260916)

서비스 중인 초코마에 운영 서버(AWS EC2 + MariaDB)에서 아래 3가지를 확인하기 위한 방법 모음입니다. 각 항목마다 여러 방법을 나열했으니, 권한·설치 가능 여부·측정 기간에 맞춰 골라서 쓰세요.

확인 항목 문서 위치
① EC2 인스턴스에서 MariaDB에 걸리는 성능 부하 A. MariaDB 성능 부하 측정
② 접속 서비스(chocomae / chocoadmin)별 pool 할당·부하 B. 접속 서비스별 연결 pool·부하 측정
③ best_record 등 기록 테이블에 데이터가 몇 개까지 들어가는지 C. 테이블 최대 저장 가능 행 수

⚠️ 이 문서는 측정 방법만 다룹니다. 실제 값은 result/에 저장하고, 결론은 90-결과-기록-양식에 채웁니다.

0. 사전 준비

0-1. 용어 정리

  • 질문의 best_score 테이블은 실제 스키마에 없습니다. 점수/기록을 담는 테이블은 아래 4개입니다. 이 문서에서는 이 4개를 "기록 테이블"로 부릅니다.
    테이블 내용 PK
    best_record 게임 플레이 기록 (가장 큼, 약 121만 행) BestRecordID INT UNSIGNED AUTO_INCREMENT
    app_highest_record 앱별 최고 기록 AppHighestRecordID INT UNSIGNED AUTO_INCREMENT
    typing_exam_record 타자 시험 기록 (약 13만 행) TypingExamRecordID INT UNSIGNED AUTO_INCREMENT
    typing_exam_highest_record 타자 시험 최고 기록 TypingExamHighestRecordID INT UNSIGNED AUTO_INCREMENT

0-2. 접속 경로 3가지

측정 방법마다 필요한 접속 경로가 다릅니다.

경로 접속 방법 할 수 있는 것
(가) EC2 SSH ssh ubuntu@13.124.33.235 (chocomae.jisangs.com) OS 지표(CPU/메모리/디스크 I/O), 설정 파일, 로그 파일, 로컬 mariadb 접속
(나) 원격 DB 클라이언트 mariadb -h chocomae.jinaju.com -P 3306 -u <계정> -p chocomae SQL로 가능한 모든 측정 (상태 변수, PROCESSLIST, EXPLAIN)
(다) 시놀로지 SSH ssh happyhome@jisangs.synology.me chocoadmin 컨테이너 설정·로그, 시놀로지→운영 DB 연결 확인

⚠️ 2026-09-15 작업에 쓰던 임시 계정 jisangs는 작업 후 삭제했습니다. 이번 측정에도 조회 전용 계정이 필요하니, 아래 중 하나를 먼저 정하세요.

  1. 관리자(root) 계정으로 직접 측정 (가장 간단, 권한 문제 없음)
  2. 측정 전용 임시 계정을 다시 만들고 끝나면 삭제 
    -- 관리자 계정으로 실행
CREATE USER 'perfcheck'@'182.217.174.221' IDENTIFIED BY '<임시 비밀번호>';
GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'perfcheck'@'182.217.174.221';
GRANT SELECT ON mysql.slow_log TO 'perfcheck'@'182.217.174.221';
-- 측정 종료 후
-- DROP USER 'perfcheck'@'182.217.174.221';
  3. EC2에 SSH로 들어가 localhost 소켓으로 접속 (원격 계정을 새로 만들지 않아도 됨)

0-3. 측정에 필요한 권한

측정 내용 필요 권한
SHOW GLOBAL STATUS, SHOW VARIABLES, information_schema.TABLES 기본 (일반 계정 가능)
SHOW FULL PROCESSLIST (다른 계정 세션까지 보기), SHOW ENGINE INNODB STATUS PROCESS
mysql.slow_log 조회 mysql.slow_log SELECT
SET GLOBAL ... (slow log 켜기, userstat 켜기) SUPER
OS 지표, 설정 파일, 로그 파일 EC2 SSH (ubuntu 계정, 일부는 sudo)

0-4. 결과 저장 위치

cd doc/plan/db/260916-load-and-capacity-check/result

개인정보·비밀번호 해시가 섞일 수 있는 파일(*grants*, 덤프)은 커밋하지 마세요. 2026-09-15 작업의 관례를 따릅니다.

0-5. MariaDB가 도커인지 호스트 설치인지 먼저 확인

문서마다 명령어가 달라지므로 가장 먼저 확인합니다. (EC2 SSH)

sudo docker ps --format 'table {{.Names}}\t{{.Image}}\t{{.Ports}}'
systemctl status mariadb --no-pager | head -5
ls -l /var/lib/mysql | head -5
  • 도커면: 이후 모든 mariadb ... 명령 앞에 sudo docker exec -i <컨테이너명> 을 붙입니다.
  • 호스트 설치면: 명령을 그대로 씁니다. (README 기준 설정은 /etc/mysql, 로그는 /var/log/mysql)

A. MariaDB 성능 부하 측정

"지금 이 EC2에서 MariaDB가 얼마나 힘들어하는가"를 보는 방법들입니다. OS 레벨(A-1) 과 DB 레벨(A-2~A-5) 을 같이 봐야 원인을 가릴 수 있습니다.

A-0. 방법 비교

방법 보는 것 설치 필요 측정 부하 지속 관찰 추천도
A-1 OS 지표 CPU·메모리·디스크 I/O·스왑 없음(일부 sysstat) 매우 낮음 스냅샷~단기 ★★★
A-2 상태 변수 델타 QPS, 버퍼 풀 적중률, 임시 테이블, 잠금 없음 매우 낮음 단기 ★★★
A-3 PROCESSLIST·INNODB STATUS 지금 실행 중인 쿼리, 대기·잠금 없음 매우 낮음 스냅샷 ★★★
A-4 slow query log 느린 쿼리 목록·빈도 없음 낮음(설정 주의) 장기 ★★★
A-5 userstat / performance_schema 쿼리 다이제스트별·계정별 누적 부하 없음(설정 변경) 낮음~중간 장기 ★★☆
A-6 모니터링 도구 시계열 그래프, 알림 있음 낮음 상시 ★★☆

추천 조합: 먼저 A-1 + A-2 + A-3으로 현재 상태를 잡고, A-4를 24시간 돌려 실제 서비스 쿼리를 확인합니다. 상시 감시가 필요해지면 A-6.

방법 A-1. OS 레벨 지표 (EC2 SSH)

A-1-1. 한 번에 훑기

# 인스턴스 사양·부하 평균
nproc; free -h; uptime

# 프로세스별 CPU·메모리 상위 10개 (mysqld가 몇 위인지)
ps -eo pid,comm,pcpu,pmem,rss --sort=-pcpu | head -11

A-1-2. 실시간 관찰

top -b -n 3 -d 5 | grep -E 'Cpu|Mem|mysqld' > os_top.txt
cat os_top.txt

A-1-3. CPU·디스크 I/O 시계열 (sysstat 필요)

sudo apt-get install -y sysstat   # 이미 있으면 생략

# 5초 간격 12회 = 1분치 CPU·메모리·스왑
vmstat 5 12 > os_vmstat.txt

# 5초 간격 12회 디스크 I/O (await, %util 확인)
iostat -dx 5 12 > os_iostat.txt

# mysqld 프로세스만 따로
pidstat -p $(pgrep -o mysqld) 5 12 > os_pidstat_mysqld.txt

판단 기준

지표 정상 주의
vmstat r(실행 대기) vCPU 수 이하 vCPU 수보다 계속 큼 → CPU 포화
vmstat si/so(스왑) 0 0보다 큼 → 메모리 부족, DB 성능 급락
iostat %util 50% 이하 90% 이상 지속 → 디스크 I/O 병목
iostat await 10ms 이하(EBS gp3) 수십 ms 이상 → I/O 대기
free -h available 여유 있음 0에 가까움 → 버퍼 풀 축소 검토

A-1-4. 도커로 돌고 있다면

sudo docker stats --no-stream

A-1-5. AWS CloudWatch (SSH 없이 확인)

EC2 콘솔 → 대상 인스턴스 → 모니터링 탭에서 CPUUtilization, EBSReadOps/EBSWriteOps, NetworkIn/Out을 기간별로 봅니다. 기본 지표는 5분 간격이며, 메모리·디스크 사용률은 CloudWatch Agent를 설치해야 나옵니다. 과거 추이를 보는 데 가장 편합니다.

방법 A-2. MariaDB 상태 변수 델타 측정

절대 누적값은 서버 기동 이후 합계라 의미가 약합니다. 일정 시간 동안의 증가량(델타) 을 봐야 합니다.

A-2-1. 가장 간단한 방법 — mariadb-admin의 상대값 출력

# 10초 간격 6회, 이전 값 대비 증가량만 출력
mariadb-admin -h chocomae.jinaju.com -u <계정> -p extended-status -i 10 -c 6 -r \
  | grep -vE '\|\s+0\s+\|' > db_status_delta.txt

cat db_status_delta.txt

A-2-2. 요약 한 줄 보기

# 5초 간격으로 Queries/초, Threads, Opens, Slow queries 표시 (Ctrl+C로 종료)
mariadb-admin -h chocomae.jinaju.com -u <계정> -p status -i 5

A-2-3. 직접 SQL로 60초 델타 뽑기

mariadb -h chocomae.jinaju.com -u <계정> -p chocomae -N -B -e "
SHOW GLOBAL STATUS;" > st1.txt
sleep 60
mariadb -h chocomae.jinaju.com -u <계정> -p chocomae -N -B -e "
SHOW GLOBAL STATUS;" > st2.txt

join st1.txt st2.txt | awk '{d=$3-$2; if (d>0) printf "%-40s %12d\n", $1, d}' \
  | sort -k2 -nr | head -40 > db_status_delta_60s.txt

cat db_status_delta_60s.txt

A-2-4. 꼭 봐야 할 값

상태 변수 의미 해석
Questions, Com_select, Com_insert 처리한 쿼리 수 60초 델타 ÷ 60 = QPS
Threads_connected / Threads_running 연결 수 / 실제 실행 중 Threads_running이 지속적으로 vCPU 수 이상이면 과부하
Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads 논리 읽기 vs 디스크 읽기 적중률 = (1 - reads/read_requests) × 100, 99% 미만이면 버퍼 풀 부족 의심
Created_tmp_disk_tables 디스크 임시 테이블 증가하면 GROUP BY/ORDER BY가 디스크로 넘어감
Select_scan, Select_full_join 풀 스캔 쿼리 수 증가하면 인덱스 미사용 쿼리 존재
Slow_queries 느린 쿼리 수 A-4와 함께 확인
Table_locks_waited, Innodb_row_lock_waits, Innodb_row_lock_time_avg 잠금 대기 0에 가까워야 정상
Aborted_connects, Connection_errors_max_connections 연결 실패 0보다 크면 B 항목에서 원인 추적

A-2-5. 버퍼 풀 적중률·크기 한 번에 계산

mariadb -h chocomae.jinaju.com -u <계정> -p -e "
SELECT
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES
    WHERE VARIABLE_NAME='innodb_buffer_pool_size')/1024/1024                      AS buffer_pool_mb,
  (SELECT ROUND(SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024,1) FROM information_schema.TABLES
    WHERE TABLE_SCHEMA='chocomae')                                                AS chocomae_total_mb,
  ROUND(100 - (
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')
  ) * 100, 3)                                                                     AS hit_rate_pct;" > db_buffer_pool.txt

cat db_buffer_pool.txt

chocomae_total_mb가 buffer_pool_mb보다 작으면 데이터 전체가 메모리에 올라가므로 디스크 I/O 부하는 거의 없습니다. 이 값은 C-3(실용적 상한 계산)에서 다시 씁니다.

방법 A-3. 실행 중인 쿼리·잠금 관찰

A-3-1. 지금 무엇이 돌고 있는가

mariadb -h chocomae.jinaju.com -u <계정> -p -e "
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, LEFT(INFO,120) AS query
FROM information_schema.PROCESSLIST
WHERE COMMAND <> 'Sleep'
ORDER BY TIME DESC;"

A-3-2. 1초 간격으로 반복 관찰 (부하가 걸리는 시간대에)

for i in $(seq 1 60); do
  date +%FT%T
  mariadb -h chocomae.jinaju.com -u <계정> -p<비밀번호> -N -B -e "
    SELECT COUNT(*) AS running FROM information_schema.PROCESSLIST WHERE COMMAND<>'Sleep';"
  sleep 1
done > db_running_1min.txt

비밀번호를 명령줄에 쓰면 셸 히스토리에 남습니다. ~/.my.cnf([client] 섹션)에 적어두고 -p를 빼는 편이 안전합니다.

A-3-3. InnoDB 내부 상태 (잠금·대기·I/O)

mariadb -h chocomae.jinaju.com -u <계정> -p -e "SHOW ENGINE INNODB STATUS\G" > db_innodb_status.txt

grep -A12 'TRANSACTIONS' db_innodb_status.txt
grep -A10 'BUFFER POOL AND MEMORY' db_innodb_status.txt
grep -A8  'LATEST DETECTED DEADLOCK' db_innodb_status.txt

A-3-4. 대기 중인 잠금만 콕 집어 보기

-- MariaDB 10.5+ (information_schema.INNODB_TRX + metadata lock 정보)
SELECT trx_id, trx_state, trx_started, trx_wait_started, trx_mysql_thread_id, LEFT(trx_query,100)
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

SELECT * FROM information_schema.METADATA_LOCK_INFO;   -- 플러그인 설치 시

방법 A-4. Slow query log 분석

2026-09-15 작업 때 이미 켰던 경로라 재사용이 쉽습니다.

A-4-1. 현재 설정 확인

mariadb -h chocomae.jinaju.com -u <계정> -p -e "
SHOW GLOBAL VARIABLES WHERE Variable_name IN
 ('slow_query_log','slow_query_log_file','long_query_time','log_output',
  'log_queries_not_using_indexes','log_slow_admin_statements','min_examined_row_limit');" > slow_log_settings.txt

cat slow_log_settings.txt

A-4-2. 측정 기간에만 임계값 낮추기 (SUPER 필요)

-- 원래 값을 먼저 기록해 두고 실행
SET GLOBAL long_query_time = 0.5;   -- 0.5초 이상 쿼리를 기록
SET GLOBAL slow_query_log = ON;
-- 측정 종료 후 원래 값으로 복원 (예: 3초)
-- SET GLOBAL long_query_time = 3;

⚠️ log_queries_not_using_indexes는 켜지 마세요. 인덱스를 안 쓰는 모든 쿼리가 기록돼 로그가 폭증합니다. log_output에 TABLE이 포함돼 있으면 mysql.slow_log가 계속 커지고 자동으로 비워지지 않습니다.

A-4-3. 파일 로그 분석 (EC2 SSH)

# 요약: 실행 시간 합계가 큰 순서로 정렬
sudo mariadb-dumpslow -s t -t 20 /var/log/mysql/mariadb-slow.log > slow_top20.txt
cat slow_top20.txt

# 더 정밀한 분석이 필요하면 (percona-toolkit 설치 필요)
sudo apt-get install -y percona-toolkit
sudo pt-query-digest /var/log/mysql/mariadb-slow.log > slow_digest.txt

A-4-4. 테이블 로그 분석 (log_output에 TABLE이 있을 때)

-- 일자별 건수
SELECT DATE(start_time) AS d, COUNT(*) AS cnt, ROUND(MAX(query_time_sec),1) AS max_sec
FROM (SELECT start_time, TIME_TO_SEC(query_time) AS query_time_sec FROM mysql.slow_log) t
GROUP BY d ORDER BY d;

-- 계정·호스트별 느린 쿼리 (B 항목에서 서비스 구분에 활용)
SELECT user_host, COUNT(*) AS cnt, ROUND(AVG(TIME_TO_SEC(query_time)),2) AS avg_sec
FROM mysql.slow_log GROUP BY user_host ORDER BY cnt DESC;

2026-09-15 측정에서는 slow log에 애플리케이션 쿼리가 한 건도 없었고, 매일 04:00 백업 덤프의 best_record 전체 SELECT(약 5초)만 기록됐습니다. 이번에도 같은 항목이 계속 잡히니 성능 저하로 오해하지 마세요.

방법 A-5. 누적 통계 (userstat / performance_schema)

A-5-1. MariaDB userstat (가볍고 설치 불필요, SUPER 필요)

SET GLOBAL userstat = 1;   -- 켠 시점부터 누적 (재시작하면 꺼짐)

-- 잠시 서비스 트래픽을 받은 뒤
SELECT * FROM information_schema.CLIENT_STATISTICS\G   -- 접속 호스트별
SELECT * FROM information_schema.USER_STATISTICS\G     -- 계정별
SELECT * FROM information_schema.TABLE_STATISTICS
 WHERE TABLE_SCHEMA='chocomae' ORDER BY ROWS_READ DESC;
SELECT * FROM information_schema.INDEX_STATISTICS
 WHERE TABLE_SCHEMA='chocomae' ORDER BY ROWS_READ DESC;

FLUSH CLIENT_STATISTICS;   -- 구간 측정을 하려면 초기화 후 다시 수집
  • B 항목(서비스별 부하)에 가장 직접적인 방법입니다. CLIENT_STATISTICS는 접속 호스트별 CONCURRENT_CONNECTIONS, CONNECTED_TIME, BUSY_TIME, ROWS_READ, SELECT_COMMANDS 등을 보여줍니다.
  • INDEX_STATISTICS에 안 나오는 인덱스는 한 번도 안 쓰인 인덱스입니다. 불필요한 인덱스 정리 판단에 쓰세요.
  • 영구 적용하려면 설정 파일에 userstat=1을 넣고 재시작해야 합니다.

A-5-2. performance_schema 다이제스트 (기본 OFF)

SHOW VARIABLES LIKE 'performance_schema';   -- OFF면 아래는 못 씀

OFF인 경우 /etc/mysql/my.cnf(또는 conf.d/*.cnf)에 performance_schema=ON을 넣고 재시작해야 합니다. 서비스 중단이 생기므로 급하지 않으면 A-4/A-5-1로 대체하세요.

-- 총 실행 시간이 큰 쿼리 유형 순
SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1e12,2)  AS total_sec,
       ROUND(AVG_TIMER_WAIT/1e9,2)   AS avg_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME='chocomae'
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

-- 계정별 부하 (B 항목에도 사용)
SELECT * FROM performance_schema.events_statements_summary_by_account_by_event_name
WHERE EVENT_NAME='statement/sql/select' ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

방법 A-6. 상시 모니터링 도구

도구 설치 위치 특징
mytop / innotop EC2 (apt-get install mytop percona-toolkit) 터미널 실시간 대시보드. QPS·슬로우·연결·실행 쿼리를 한 화면에서 봄. 가장 가볍게 시작 가능
Netdata EC2 (에이전트 1개) 설치 5분, MySQL 플러그인 자동 인식, 웹 UI로 시계열 확인
Prometheus + mysqld_exporter + Grafana 수집기는 시놀로지, exporter는 EC2 장기 보관·대시보드·알림. 이미 시놀로지 도커가 있으니 구성 부담이 적음
Percona PMM 도커 1개 + 에이전트 MySQL 전용 대시보드·쿼리 분석기(QAN)가 강력. 리소스는 좀 더 씀
AWS CloudWatch (+Agent) EC2 인스턴스 지표·알림. DB 내부 지표는 안 보임

mytop 예시

sudo apt-get install -y mytop
mytop -h 127.0.0.1 -u root -p --dbname chocomae

선택 가이드: 이번처럼 "한 번 확인"이면 A-1~A-5로 충분합니다. "앞으로 계속 보겠다"면 mysqld_exporter + 시놀로지 Grafana 조합이 운영 부담 대비 효과가 가장 좋습니다.

B. 접속 서비스별 연결 pool·부하 측정

B-0. 먼저 알아야 할 구조

서비스 위치 접속 방식 pool 존재 여부
chocomae (PHP) 같은 EC2 new mysqli($db_host, ...) — connect_db.php:17, localhost(또는 도커 호스트명) pool 없음. PHP 요청마다 새 연결을 맺고 요청이 끝나면 끊습니다. p: 접두사(persistent)를 쓰지 않으므로, 동시 연결 수의 상한은 사실상 Apache의 동시 프로세스 수(MaxRequestWorkers) 입니다
chocoadmin (Next.js/Node) 시놀로지 도커 인터넷 경유로 chocomae.jinaju.com:3306 Node의 MySQL 드라이버(mysql2 등)를 쓰면 커넥션 풀 존재. connectionLimit 기본값은 보통 10. 컨테이너/프로세스 수만큼 배수가 됨
배치/백업 EC2 cron, 시놀로지 cron 단발 연결 매일 04:00 풀백업이 대표적

따라서 확인할 것은 3가지입니다.

  1. DB가 허용하는 상한 (max_connections)과 실제 최대 사용치 (Max_used_connections)
  2. 출처별 현재·최대 연결 수 (EC2 로컬 vs 시놀로지 공인 IP)
  3. 각 애플리케이션 쪽 설정값 (Apache 워커 수, Node pool connectionLimit)

B-0-1. 방법 비교

방법 보는 것 서비스 구분 가능? 필요 권한
B-1 연결 상한·사용치 전체 연결 수, 한계 근접 여부 ✗ 기본
B-2 PROCESSLIST 집계 출처 IP·계정별 현재 연결 수 ○ (IP로) PROCESS
B-3 userstat 출처별 누적 연결 시간·쿼리 수·읽은 행 수 ◎ (가장 정확) SUPER + 조회
B-4 계정 분리 서비스별 완전 분리 + 상한 제한 ◎ CREATE USER, GRANT
B-5 애플리케이션 설정 pool 할당값 자체 ◎ 서버 접근
B-6 네트워크 소켓 TCP 연결 수·상태 ○ (IP로) EC2 SSH
B-7 주기 샘플링 시간대별 추이 ○ PROCESS + cron

방법 B-1. 연결 상한과 실제 사용치

mariadb -h chocomae.jinaju.com -u <계정> -p -e "
SHOW GLOBAL VARIABLES WHERE Variable_name IN
 ('max_connections','max_user_connections','thread_handling','thread_pool_size',
  'wait_timeout','interactive_timeout','back_log','table_open_cache','open_files_limit');
SHOW GLOBAL STATUS WHERE Variable_name IN
 ('Threads_connected','Threads_running','Threads_created','Threads_cached',
  'Max_used_connections','Max_used_connections_time','Connections',
  'Aborted_connects','Aborted_clients','Connection_errors_max_connections');" > conn_limits.txt

cat conn_limits.txt

해석

값 의미
Max_used_connections / max_connections 연결 여유율. 80%를 넘으면 상한을 올리거나 pool을 줄여야 합니다
Max_used_connections_time 최고치를 찍은 시각 → 그 시간대에 무슨 작업이 있었는지 추적
Connection_errors_max_connections 0보다 크면 연결 거부가 실제로 발생한 것
Aborted_connects 인증 실패·타임아웃. 시놀로지↔AWS 인터넷 구간 문제의 신호일 수 있음
Threads_created ≫ Connections의 일부 스레드 캐시 부족 (thread_cache_size 조정 대상)
wait_timeout pool의 유휴 연결이 이 시간을 넘기면 서버가 끊습니다. chocoadmin에서 간헐적 ECONNRESET/Connection lost가 난다면 1순위 원인

방법 B-2. PROCESSLIST로 출처별 집계

mariadb -h chocomae.jinaju.com -u <계정> -p -e "
SELECT USER,
       SUBSTRING_INDEX(HOST, ':', 1)                  AS client_ip,
       DB,
       COUNT(*)                                       AS conns,
       SUM(COMMAND <> 'Sleep')                        AS active,
       SUM(COMMAND =  'Sleep')                        AS idle,
       MAX(TIME)                                      AS max_idle_or_run_sec
FROM information_schema.PROCESSLIST
GROUP BY USER, client_ip, DB
ORDER BY conns DESC;" > conn_by_source.txt

cat conn_by_source.txt

읽는 법

  • client_ip가 localhost/127.0.0.1/도커 내부 IP(172.x) → chocomae(PHP)
  • client_ip가 시놀로지 공인 IP → chocoadmin
  • idle이 크고 max_idle_or_run_sec이 수백 초 → pool이 유휴 연결을 계속 붙잡고 있는 상태 (정상이지만 max_connections 여유를 잡아먹음)
  • active가 계속 큼 → 실제 쿼리 부하

시놀로지 공인 IP를 모른다면 시놀로지에서 curl -s ifconfig.me 로 확인하세요. README 기준 출발지 IP는 182.217.174.221입니다.

방법 B-3. userstat으로 출처별 누적 부하 (가장 정확)

SET GLOBAL userstat = 1;
FLUSH CLIENT_STATISTICS;   -- 구간 측정 시작점

-- (서비스 트래픽을 받는 동안 대기: 예를 들어 1시간)

SELECT CLIENT,
       TOTAL_CONNECTIONS, CONCURRENT_CONNECTIONS,
       ROUND(CONNECTED_TIME/60,1)  AS connected_min,
       ROUND(BUSY_TIME,1)          AS busy_sec,
       ROUND(CPU_TIME,1)           AS cpu_sec,
       ROWS_READ, ROWS_SENT, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS
FROM information_schema.CLIENT_STATISTICS
ORDER BY BUSY_TIME DESC;
  • BUSY_TIME·CPU_TIME = 그 출처가 DB를 실제로 점유한 시간입니다. "chocomae와 chocoadmin 중 어느 쪽이 DB를 더 쓰는가"에 대한 가장 직접적인 답입니다.
  • USER_STATISTICS는 같은 형식을 계정 단위로 보여줍니다. B-4(계정 분리)를 먼저 하면 IP가 아니라 계정만으로 깔끔하게 구분됩니다.

방법 B-4. 서비스별 DB 계정 분리 + 연결 상한 지정 (구조 개선)

지금은 chocomae와 chocoadmin이 같은 chocomae 계정을 쓸 가능성이 큽니다(README 기준 계정은 root/chocomae/backup 3개). 계정을 나누면 측정과 제어가 동시에 쉬워집니다.

-- 관리자 계정으로 실행
CREATE USER 'chocoadmin'@'182.217.174.221' IDENTIFIED BY '<새 비밀번호>';
GRANT SELECT, INSERT, UPDATE, DELETE ON chocomae.* TO 'chocoadmin'@'182.217.174.221';

-- 이 계정 하나가 쓸 수 있는 동시 연결 수 상한 (폭주 시 서비스 보호)
ALTER USER 'chocoadmin'@'182.217.174.221' WITH MAX_USER_CONNECTIONS 20;

FLUSH PRIVILEGES;
SHOW GRANTS FOR 'chocoadmin'@'182.217.174.221';
  • 계정을 나눈 뒤에는 B-2/B-3의 집계가 USER 기준으로 바로 읽힙니다.
  • MAX_USER_CONNECTIONS를 걸어두면 chocoadmin 쪽 pool 설정 실수로 운영 서비스가 연결을 못 맺는 사고를 막습니다.
  • ⚠️ 계정 변경은 chocoadmin 배포와 함께 진행해야 하니, 측정만이 목적이라면 B-2/B-3(IP 기준)으로 충분합니다.

방법 B-5. 애플리케이션 쪽 설정 확인 (pool 할당값 자체)

B-5-1. chocomae (PHP / Apache) — EC2 SSH

# 동시 처리 가능한 요청 수 = 사실상 최대 동시 DB 연결 수
apache2ctl -V | grep -i 'Server MPM'
grep -RnE 'MaxRequestWorkers|ServerLimit|StartServers|MaxConnectionsPerChild' /etc/apache2/mods-available/mpm_*.conf /etc/apache2/apache2.conf

# php-fpm을 쓴다면
grep -RnE '^(pm|pm\.max_children|pm\.start_servers|pm\.max_spare_servers)' /etc/php/*/fpm/pool.d/*.conf 2>/dev/null

# persistent connection 관련 설정 (현재 코드는 사용 안 함)
php -i | grep -iE 'mysqli.allow_persistent|mysqli.max_persistent|mysqli.max_links|default_socket_timeout'

판단: MaxRequestWorkers(prefork 기본 150) ≤ max_connections여야 합니다. 그렇지 않으면 트래픽 급증 시 PHP가 "Too many connections"를 만납니다.

현재 코드는 요청마다 연결을 새로 맺습니다(new mysqli). 연결 비용이 부담되면 p:localhost 형태의 persistent 연결을 검토할 수 있지만, 연결이 프로세스에 남아 max_connections를 오래 점유하므로 먼저 B-1의 여유율을 확인한 뒤 결정하세요.

B-5-2. chocoadmin (Next.js / Node) — 시놀로지 SSH

cd /volume1/docker/service/jinaju/chocoadmin

# pool 설정값 찾기
grep -RniE 'createPool|connectionLimit|pool|queueLimit|connectTimeout|enableKeepAlive' \
  --include='*.ts' --include='*.js' --include='*.mjs' . | grep -v node_modules | head -40

# 환경변수 (비밀번호는 출력하지 말 것)
grep -vE 'PASS|PWD|SECRET|TOKEN' .env.production

# 실행 중인 컨테이너·프로세스 수 (pool이 몇 배로 늘어나는지)
sudo docker ps --filter name=chocoadmin
sudo docker exec chocoadmin sh -c 'ps -eo pid,comm | head'

확인 포인트

항목 확인 내용
connectionLimit 명시하지 않았다면 mysql2 기본값 10
컨테이너/워커 수 pool은 프로세스마다 하나입니다. Next.js를 여러 워커로 띄우면 connectionLimit × 워커 수가 실제 상한
모듈 스코프 재사용 pool을 요청마다 만들면 연결이 계속 늘어납니다. 모듈 최상단에서 한 번만 생성하는지 확인
connectTimeout, enableKeepAlive 시놀로지→AWS 인터넷 구간이라 지연·끊김 대비 필요
wait_timeout(B-1) 대비 유휴 시간 서버가 먼저 끊으면 죽은 연결을 재사용하다 오류가 납니다

B-5-3. chocoadmin 쪽 실제 연결 확인 (시놀로지 SSH)

# 컨테이너에서 운영 DB로 맺은 연결 수
sudo docker exec chocoadmin sh -c "ss -tn state established '( dport = :3306 )' 2>/dev/null | wc -l" \
  || sudo netstat -tn | grep ':3306' | wc -l

방법 B-6. 네트워크 소켓 레벨 확인 (EC2 SSH)

DB에 로그인하지 않고도 누가 3306에 붙어 있는지 셀 수 있습니다.

# 출처 IP별 3306 연결 수
sudo ss -tn state established '( sport = :3306 )' \
  | awk 'NR>1 {split($5,a,":"); print a[1]}' | sort | uniq -c | sort -nr > conn_by_ip_ss.txt

cat conn_by_ip_ss.txt

# 프로세스까지 같이 보기
sudo ss -tnp state established '( sport = :3306 )' | head -30

# TIME_WAIT 등 상태별 집계 (연결을 자주 맺고 끊는지 확인 — PHP 특성상 많을 수 있음)
sudo ss -tan '( sport = :3306 )' | awk 'NR>1 {print $1}' | sort | uniq -c

TIME_WAIT가 수천 개면 "요청마다 연결/해제"의 비용이 눈에 보이는 수준이라는 뜻입니다. persistent 연결이나 pool 도입을 검토할 근거가 됩니다.

방법 B-7. 시간대별 추이 샘플링 (권장: 24시간)

한 번의 스냅샷으로는 피크를 놓칩니다. cron으로 1분마다 찍어 CSV로 모읍니다.

# EC2에 저장: /home/ubuntu/conn_sample.sh
cat > /home/ubuntu/conn_sample.sh <<'EOS'
#!/bin/bash
TS=$(date +%FT%T)
mariadb -N -B -e "
SELECT '$TS', USER, SUBSTRING_INDEX(HOST,':',1), COUNT(*), SUM(COMMAND<>'Sleep')
FROM information_schema.PROCESSLIST GROUP BY USER, 3;" >> /home/ubuntu/conn_sample.tsv
mariadb -N -B -e "
SELECT '$TS','__global__','-',
 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Threads_connected'),
 (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Threads_running');" \
 >> /home/ubuntu/conn_sample.tsv
EOS
chmod +x /home/ubuntu/conn_sample.sh

# 1분마다 실행
( crontab -l 2>/dev/null; echo "* * * * * /home/ubuntu/conn_sample.sh" ) | crontab -

수집 후 정리:

# 시간대별 최대 연결 수
awk -F'\t' '$2=="__global__" {split($1,t,"T"); split(t[2],h,":"); \
  key=t[1]" "h[1]"시"; if ($4>m[key]) m[key]=$4} END {for (k in m) print k, m[k]}' \
  /home/ubuntu/conn_sample.tsv | sort > conn_hourly_peak.txt

cat conn_hourly_peak.txt

# 측정 끝나면 cron 제거
crontab -l | grep -v conn_sample.sh | crontab -

C. 테이블 최대 저장 가능 행 수

"best_record에 데이터가 몇 개까지 들어갈 수 있나"는 상한이 세 겹입니다. 셋 중 가장 먼저 걸리는 것이 실제 한계입니다.

구분 상한 요인 대략적인 값
C-1 논리적 상한 PK INT UNSIGNED AUTO_INCREMENT 최대값 4,294,967,295 (약 42.9억)
C-2 물리적 상한 InnoDB 테이블스페이스 / 디스크 여유 공간 디스크 여유 ÷ 행당 바이트
C-3 실용적 상한 버퍼 풀·인덱스 크기 대비 성능이 버티는 지점 보통 위 둘보다 훨씬 먼저 도달

방법 C-1. 논리적 상한 — AUTO_INCREMENT 소진까지

기록 테이블 4개 모두 PK가 INT UNSIGNED AUTO_INCREMENT이므로 42.9억이 상한입니다. 중요한 점은 행을 삭제해도 AUTO_INCREMENT 값은 되돌아가지 않는다는 것입니다. 즉 "현재 행 수"가 아니라 "지금까지 발급된 ID 값"이 기준입니다.

mariadb -h chocomae.jinaju.com -u <계정> -p -e "
SELECT TABLE_NAME,
       TABLE_ROWS                                         AS approx_rows,
       AUTO_INCREMENT                                     AS next_id,
       4294967295 - AUTO_INCREMENT                        AS remaining_ids,
       ROUND(AUTO_INCREMENT / 4294967295 * 100, 4)        AS used_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='chocomae' AND AUTO_INCREMENT IS NOT NULL
ORDER BY used_pct DESC;" > capacity_auto_increment.txt

cat capacity_auto_increment.txt

남은 기간 계산

mariadb -h chocomae.jinaju.com -u <계정> -p chocomae -e "
SELECT DATE_FORMAT(RecordDateTime,'%Y-%m') AS ym, COUNT(*) AS cnt
FROM best_record
WHERE RecordDateTime >= DATE_SUB(CURDATE(), INTERVAL 13 MONTH)
GROUP BY ym ORDER BY ym;" > capacity_monthly_load.txt

cat capacity_monthly_load.txt

남은 기간 = remaining_ids ÷ (월 적재량 × 12)

2026-09-15 기준 개략 계산: 월 평균 약 57,968행 → 연 약 69.6만 행. 42.9억 ÷ 69.6만 ≈ 6,000년 이상. 지금 증가 속도가 100배가 되어도 60년이 넘습니다. → AUTO_INCREMENT는 실질적인 제약이 아닙니다.

단, 아래 경우는 소진이 빨라지니 used_pct를 연 1회 확인하세요.

  • INSERT ... ON DUPLICATE KEY UPDATE / REPLACE를 많이 쓰면 실패한 INSERT도 ID를 소비합니다
  • 대량 삭제 후 재적재를 반복하는 경우

넘어갈 조짐이 보이면: ALTER TABLE best_record MODIFY BestRecordID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT; (테이블 재구성이 필요해 시간과 디스크 여유가 많이 듭니다. 미리 계획하세요)

방법 C-2. 물리적 상한 — 디스크 여유 공간

C-2-1. 현재 테이블 크기와 행당 바이트

mariadb -h chocomae.jinaju.com -u <계정> -p -e "
SELECT TABLE_NAME,
       TABLE_ROWS                                          AS approx_rows,
       ROUND(DATA_LENGTH/1024/1024,1)                      AS data_mb,
       ROUND(INDEX_LENGTH/1024/1024,1)                     AS index_mb,
       ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,1)       AS total_mb,
       ROUND(DATA_FREE/1024/1024,1)                        AS free_mb,
       ROUND((DATA_LENGTH+INDEX_LENGTH)/NULLIF(TABLE_ROWS,0), 1) AS bytes_per_row
FROM information_schema.TABLES
WHERE TABLE_SCHEMA='chocomae'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC;" > capacity_table_sizes.txt

cat capacity_table_sizes.txt

TABLE_ROWS는 InnoDB 추정치입니다. 정확한 값이 필요하면 SELECT COUNT(*) FROM best_record;를 쓰되, 운영 시간대에는 부하를 고려하세요.

C-2-2. 디스크 여유 (EC2 SSH)

df -h /var/lib/mysql
sudo du -sh /var/lib/mysql
sudo du -sh /var/lib/mysql/chocomae/*.ibd 2>/dev/null | sort -h | tail -10

C-2-3. 계산

추가 가능 행 수 ≈ (디스크 여유 바이트 × 0.8) ÷ bytes_per_row
남은 기간(년)   ≈ 추가 가능 행 수 ÷ (월 적재량 × 12)

0.8은 안전 계수입니다. 백업·바이너리 로그·임시 파일·ALTER 시 임시 테이블 공간을 남겨둬야 합니다. 특히 인덱스 추가나 BIGINT 변경 같은 온라인 DDL은 테이블 크기만큼의 여유 공간을 추가로 요구합니다.

2026-09-15 기준 개략 계산: best_record는 약 121만 행에 data 72.5MB + index 72.1MB ≈ 145MB → 행당 약 125바이트. 42.9억 행을 다 채우면 약 537GB가 필요합니다. 즉 디스크가 AUTO_INCREMENT보다 훨씬 먼저 한계입니다. 현재 df -h 결과로 실제 여유를 확인하세요.

C-2-4. InnoDB 자체 상한(참고)

  • 테이블스페이스 최대 크기: 페이지 16KB 기준 64TB (SHOW VARIABLES LIKE 'innodb_page_size'로 확인)
  • innodb_file_per_table이 ON이면 테이블마다 .ibd 파일 → 파일시스템 최대 파일 크기도 확인 대상 (ext4는 16TB)
  • 실무에서 여기에 닿는 일은 없습니다. 디스크 여유(C-2-2)가 먼저 걸립니다.

방법 C-3. 실용적 상한 — 성능이 버티는 지점 (가장 중요)

디스크가 남아도 인덱스가 버퍼 풀을 넘어가는 순간 조회 성능이 급격히 나빠집니다. "몇 개까지 넣을 수 있나"의 현실적인 답은 대개 여기입니다.

mariadb -h chocomae.jinaju.com -u <계정> -p -e "
SELECT
  ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES
          WHERE VARIABLE_NAME='innodb_buffer_pool_size')/1024/1024, 0)      AS buffer_pool_mb,
  ROUND((SELECT SUM(DATA_LENGTH+INDEX_LENGTH) FROM information_schema.TABLES
          WHERE TABLE_SCHEMA='chocomae')/1024/1024, 1)                      AS chocomae_total_mb,
  ROUND((SELECT SUM(INDEX_LENGTH) FROM information_schema.TABLES
          WHERE TABLE_SCHEMA='chocomae')/1024/1024, 1)                      AS chocomae_index_mb;" \
  > capacity_buffer_pool.txt

cat capacity_buffer_pool.txt

판단 기준

상태 의미 조치
chocomae_total_mb < buffer_pool_mb 데이터 전체가 메모리에 상주. 디스크 읽기 거의 없음 여유 있음
chocomae_index_mb < buffer_pool_mb < chocomae_total_mb 인덱스는 메모리, 데이터는 일부만 정상 범위. 버퍼 풀 적중률(A-2-5) 감시
buffer_pool_mb < chocomae_index_mb 인덱스조차 다 못 올림 성능 급락 구간. 버퍼 풀 증설 / 아카이빙 / 파티셔닝 필요

한계 도달 시점 추정

현재 인덱스 크기 ÷ 현재 행 수 = 행당 인덱스 바이트
(버퍼 풀 크기 × 0.8 - 현재 인덱스 크기) ÷ 행당 인덱스 바이트 = 여유 행 수
여유 행 수 ÷ (월 적재량 × 12) = 남은 기간(년)

인덱스 크기는 2026-09-15 작업에서 인덱스 5개 + UNIQUE 2개를 추가한 뒤의 값으로 다시 재야 정확합니다. 인덱스가 늘면 행당 바이트도 늘어납니다.

추가 확인: 실제 메모리 여유 (EC2 SSH)

free -h
# 버퍼 풀을 키울 때는 (버퍼 풀 + 연결당 버퍼 × max_connections + OS) < 물리 메모리 여야 합니다
mariadb -e "SHOW VARIABLES WHERE Variable_name IN
 ('innodb_buffer_pool_size','sort_buffer_size','join_buffer_size','read_buffer_size',
  'read_rnd_buffer_size','tmp_table_size','max_heap_table_size','max_connections');"

방법 C-4. 증가 속도 기반 예측 (한 번에 정리)

아래 쿼리 하나로 세 상한을 같이 뽑을 수 있습니다.

mariadb -h chocomae.jinaju.com -u <계정> -p chocomae -e "
SELECT
  t.TABLE_NAME,
  t.TABLE_ROWS                                                     AS approx_rows,
  t.AUTO_INCREMENT                                                 AS next_id,
  ROUND((t.DATA_LENGTH+t.INDEX_LENGTH)/1024/1024,1)                AS total_mb,
  ROUND((t.DATA_LENGTH+t.INDEX_LENGTH)/NULLIF(t.TABLE_ROWS,0),1)   AS bytes_per_row,
  ROUND((4294967295 - t.AUTO_INCREMENT) / (:monthly_rows * 12), 1) AS years_to_autoinc_limit,
  ROUND((4294967295 - t.AUTO_INCREMENT)
        * (t.DATA_LENGTH+t.INDEX_LENGTH)/NULLIF(t.TABLE_ROWS,0)
        /1024/1024/1024, 1)                                        AS gb_needed_for_autoinc_limit
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA='chocomae'
  AND t.TABLE_NAME IN ('best_record','app_highest_record',
                       'typing_exam_record','typing_exam_highest_record');" > capacity_projection.txt

cat capacity_projection.txt

:monthly_rows 자리에는 C-1에서 구한 월 평균 적재량(2026-09 기준 약 57968)을 숫자로 직접 넣으세요. MariaDB CLI에는 바인드 변수가 없습니다.

방법 C-5. 상한에 가까워졌을 때의 대응 (기존 문서 연결)

대응 효과 문서
인덱스·쿼리 최적화 같은 데이터로 더 오래 버팀 02-option1-index-and-query-rewrite.md
일별 요약 테이블 랭킹 조회가 원본 테이블을 안 읽음 03-option2-daily-summary-tables.md
아카이빙 (오래된 기록 분리) 활성 테이블 크기 감소 → 버퍼 풀 여유 04-option3-archiving.md
파티셔닝 (날짜 기준) 조회 시 읽는 파티션만 접근 05-option4-partitioning.md
DB 분리 (RDS 등) 웹/DB 리소스 경합 해소 09-aws-db-separation.md
시놀로지 복제 읽기 부하(chocoadmin) 분산 08-synology-replication-setup.md

chocoadmin이 DB를 많이 읽는 것으로 B-3에서 확인된다면, 복제본을 두고 chocoadmin만 복제본을 보게 하는 것이 가장 효과가 큰 조치입니다.

90. 결과 기록 양식

측정 후 아래 표를 채워 넣으세요.

A. MariaDB 부하

항목 측정값 측정 방법 판단
vCPU / 메모리 A-1-1
CPU 사용률 (peak) A-1-3 / A-1-5
스왑 사용 A-1-3 0이어야 정상
디스크 %util / await A-1-3
QPS (평균 / peak) A-2-1
Threads_running (peak) A-2-4 vCPU 이하면 정상
버퍼 풀 적중률 A-2-5 99% 이상
디스크 임시 테이블 (60초) A-2-3
잠금 대기 A-2-4 / A-3-3
느린 쿼리 (24시간) A-4 백업 SELECT 제외

B. 서비스별 연결

항목 chocomae (EC2) chocoadmin (시놀로지) 측정 방법
접속 방식 new mysqli (pool 없음) Node pool B-0
설정상 최대 연결 Apache MaxRequestWorkers = connectionLimit × 워커 수 = B-5
현재 연결 수 B-2
최대 연결 수 (24h) B-7
유휴 연결 수 B-2
누적 BUSY_TIME B-3
읽은 행 수 B-3
전체 max_connections 여유율: B-1
연결 거부 발생 B-1

C. 테이블 용량

테이블 현재 행 수 next_id AUTO_INCREMENT 사용률 total_mb 행당 바이트 디스크 기준 남은 행 남은 기간
best_record
app_highest_record
typing_exam_record
typing_exam_highest_record
항목 값
월 평균 적재량
innodb_buffer_pool_size
chocomae 전체 크기 (data+index)
디스크 여유 (/var/lib/mysql)
가장 먼저 걸리는 상한 (AUTO_INCREMENT / 디스크 / 버퍼 풀)
예상 도달 시점

91. 주의사항

  1. 측정 자체가 부하입니다. SELECT COUNT(*)나 SHOW ENGINE INNODB STATUS를 반복 실행하지 마세요. 정확한 행 수가 꼭 필요할 때만 한 번 실행합니다.
  2. SET GLOBAL로 바꾼 값은 반드시 복원합니다. 변경 전 값을 파일로 먼저 저장하세요 (long_query_time, slow_query_log, userstat).
  3. log_queries_not_using_indexes는 켜지 않습니다. 로그가 폭증합니다.
  4. cron 샘플링(B-7)은 측정이 끝나면 지웁니다. 지우지 않으면 TSV가 무한히 커집니다.
  5. 매일 04:00 풀백업이 돌고 있습니다. 이 시간대 측정값은 백업 부하가 섞이니 따로 표시하세요.
  6. 비밀번호를 명령줄에 쓰지 마세요. 셸 히스토리와 ps 출력에 남습니다. ~/.my.cnf(권한 600)를 쓰세요. 
    [client]
host=chocomae.jinaju.com
user=perfcheck
password=<비밀번호>
  7. 임시 계정·임시 권한은 측정 후 회수합니다. (DROP USER, REVOKE)
  8. README에 평문으로 남아 있는 DB 비밀번호(root/chocomae/backup이 모두 같은 값)는 이 작업과 별개로 교체가 필요합니다.
Reference in New Issue View Git Blame Copy Permalink