「사이트가 느리다」는 거의 언제나 데이터베이스로 귀결됩니다. 올바른 순서로 하면 확인에 몇 분이 걸립니다. 먼저 느려지는 그 순간에 보이는 것, 그다음 몇 주에 걸쳐 쌓여 온 것.

문제가 벌어지는 동안 보라

핵심 규칙. 가장 값진 정보는 정확히 모든 것이 느린 동안 얻을 수 있습니다. MySQL의 첫 명령:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

PostgreSQL의 경우:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

지금 무엇이 얼마나 오래 돌고 있는지 보입니다. 대개 답은 즉시 나옵니다. 무거운 쿼리 하나가 나머지를 붙잡고 있거나, 똑같은 쿼리 백 개가 잠금이 풀리기를 기다리고 있습니다.

연결

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Max_used_connections가 max_connections에 가까워지면 애플리케이션은 이따금 「too many connections」 거부를 받습니다. 한도를 올리는 것이 먼저 할 일은 아닙니다. 연결마다 메모리를 차지하며, 이미 메모리가 모자란 서버에서 그것을 올리면 swap으로 가는 길만 빨라집니다. 먼저 왜 연결이 반납되지 않는지 밝히세요. 대개는 긴 쿼리이거나 애플리케이션 쪽에 풀링이 없는 것입니다.

같은 것을 반대편에서 볼 가치도 있습니다. 연결의 급증은 인기가 늘어서가 아니라 봇들이 무거운 페이지를 두드려서 생길 수 있습니다 — 검색, 카탈로그 필터, 보고서 생성기.

느린 쿼리

느린 쿼리 로그 없이는 더 갈 데가 없습니다. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

하루 뒤:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL은 postgresql.conf에서:

log_min_duration_statement = 1000

그다음 쿼리별 총 시간을 바로 보여 주는 pg_stat_statements 확장:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

유의할 점. 중요한 것은 가장 긴 쿼리가 아니라 총 시간이 가장 큰 쿼리입니다. 1분에 천 번 도는 50밀리초 쿼리가 하루 한 번의 10초짜리 보고서보다 더 큰 해를 끼칩니다.

가장 자주 잊히는 설정

MySQL과 MariaDB에서는 InnoDB 버퍼 풀 크기입니다. 기본값은 128 MB이고 수십 년째 바뀌지 않았습니다. 데이터베이스가 몇 기가바이트를 차지하는 서버에서 그것은 메모리 대신 디스크에서 끊임없이 읽는다는 뜻입니다.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

전용 데이터베이스 서버의 합리적 기준은 RAM의 절반, 웹 서버와 PHP도 함께 도는 서버라면 4분의 1이며, swap이 시작되지 않는지 살피면서 잡습니다. 그 설정 하나가 대개 일주일의 쿼리 최적화보다 많은 것을 줍니다.

조용히 디스크를 먹는 두 가지

MySQL 바이너리 로그. 복제와 특정 시점 복구에 필요하며, 끊임없이 자라고, 기본으로는 아예 지워지지 않는 경우가 있습니다. 확인은 이렇게.

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

복제가 없고 특정 시점 복구를 쓰지 않는다면 보관 기간을 합리적인 며칠로 잡으세요.

잊힌 복제 슬롯이 딸린 PostgreSQL WAL 파일. 덜 알려졌고 더 위험한 경우입니다. 복제 슬롯이 있는데 그 소비자가 더는 접속하지 않으면, PostgreSQL은 끊긴 시점 이후의 모든 로그를 보관할 의무를 지고 — 디스크가 떨어질 때까지 보관합니다.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

아무에게도 필요 없는 비활성 슬롯은 지워야 합니다. 데이터베이스가 디스크를 0까지 몰고 가는 것이 보장되는 몇 안 되는 상황 중 하나입니다.

크기와 증가

SELECT table_schema, round(sum(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

유용한 것은 현재 수치보다 그것이 바뀌는 속도입니다. 트래픽의 상응하는 증가 없이 한 달에 두 배가 된 데이터베이스는 대개 어떤 기록용 테이블이 쓰이기만 하고 청소되지 않는다는 뜻입니다 — 세션, 작업 이력, 플러그인의 이벤트 로그.

두 줄짜리 보안

데이터베이스 콘솔을 연 김에 두 가지를 확인할 가치가 있습니다. 첫째, 데이터베이스가 밖에서 닿는지(ss -tulpn | grep 3306). 그것이 밖을 향할 일은 거의 없습니다. 둘째, 어떤 주소에서든 접속할 수 있는 계정:

mysql -e "SELECT user, host FROM mysql.user"

host = '%'인 항목은 「어디에서든 접속」을 뜻합니다. 포트까지 열려 있다면 데이터베이스와 인터넷 사이에 서 있는 것은 비밀번호뿐입니다.

백업

마지막 한 가지. 백업 파일의 존재는 아무것도 보장하지 않으며, 보장하는 것은 시험해 본 복구뿐입니다. 한 번도 복구해 본 적 없는 덤프는 잘려 있거나, 잠금 오류와 함께 떠졌거나, 엉뚱한 데이터베이스를 담고 있을 확률이 반반입니다. 그것은 사본이 필요해진 날이 아니라 미리, 별도의 머신에서 확인해야 합니다.

일상적인 감시에는 세 수치면 충분합니다. 연결 수, 하루의 느린 쿼리 수, 데이터베이스의 크기. 셋 다 느리고 예측 가능하게 변하며, 그중 하나의 급격한 움직임은 들여다볼 가치가 있습니다. 그것들이 한 페이지에서 어떤 모습인지는 아래 데모에서.