통계 화면이 시간 초과로 죽었다

관리자 통계 화면이 안 열렸다.

Maximum execution time exceeded

Open Table of contents

원인 — 인덱스로는 부족했다
미리 세어 뒀다
두 곳에서 정하던 경계
어디까지 미리 계산할지 정했다
정리

원인 — 인덱스로는 부족했다

쿼리를 봤다.

SELECT DATE(created_at) AS d, COUNT(*), SUM(amount)
  FROM orders
 WHERE created_at BETWEEN ? AND ?
 GROUP BY d;

원본 주문 테이블을 매번 훑는다. 기본 조회 기간이 1년이라 수십만 건을 매번 다 센다.

EXPLAIN 을 떠 보니 created_at 인덱스는 타고 있었다. 조금 빨라지기는 했지만 여전히 몇 초가 걸렸다. rows 로 잡힌 수 자체가 많아서 인덱스로 줄일 수 있는 한계가 있었다.

여기서 보이는 것이 있었다. 지난 날짜의 집계는 이미 결과가 정해져 있다. 어제 매출은 오늘 다시 세어도 언제나 같은 값이 나오는데 같은 COUNT(*) 와 SUM() 을 매번 반복하고 있었다.

미리 세어 뒀다

일별 집계를 미리 계산해 저장했다.

daily_stats
  date
  order_count
  amount_sum

crontab 에 건 배치가 매일 새벽에 전날 것을 넣는다. 수십만 건을 세던 것이 365행을 읽는 것이 되어 즉시 열렸다.

그런데 오늘 집계가 없었다. 배치는 하루가 끝난 뒤에 도니 어제까지만 들어간다.

$past = $model->get_daily_stats($from, $yesterday);
$today = $model->count_today();
$all = array_merge($past, [$today]);

지난 날짜는 daily_stats 에서 읽고 오늘만 원본에서 직접 센다. 하루치라 빠르다.

두 곳에서 정하던 경계

여기서 실수가 있었다. 어제와 오늘 경계를 잘못 잡아 하루가 두 번 들어갔다.

집계 테이블에 오늘이 이미 있었음
+ 직접 센 오늘

배치가 새벽에 도는데 어느 날짜까지 넣는지가 애매했다. 그래서 명시적으로 정했다.

집계 테이블: 어제까지만
화면: 오늘만 직접

원인은 오늘을 정하는 자리가 둘이었다는 것이다. 배치는 MySQL 의 CURDATE() 를 썼고 화면은 PHP 의 date('Y-m-d') 를 썼다.

이 둘은 서로 다른 설정을 본다. MySQL 은 세션 time_zone 을 보고 그 기본값 SYSTEM 은 OS 타임존을 따른다. PHP 는 date.timezone 을 보는데 그 값이 비어 있으면 UTC 로 동작한다. 서버 OS 가 KST 인데 php.ini 에 타임존이 없으면 아홉 시간이 어긋나 새벽 아홉 시 전까지는 날짜 자체가 하루 다르게 나온다.

$boundary = date('Y-m-d');   // 오늘
// 집계: < boundary
// 직접: >= boundary

date_default_timezone_set('Asia/Seoul') 을 부트스트랩에 넣고 배치도 같은 함수로 날짜를 만들게 했다. 경계를 정하는 자리를 하나로 모은 것이다. 두 곳에서 각자 정하면 어긋난다.

과거 주문이 취소되면 그날 집계가 달라지는 것도 걸렸다. 취소나 환불이 며칠 뒤에 들어오면 집계와 원본이 안 맞는다.

$model->recalc_daily($date);

그날 것만 다시 계산하게 했다. 다시 계산은 INSERT ... ON DUPLICATE KEY UPDATE 로 했는데 date 가 기본 키라 있으면 갱신되고 없으면 들어간다. 지우고 다시 넣는 것보다 중간에 빈 구간이 생기지 않았다.

어디까지 미리 계산할지 정했다

주기적으로 원본과 집계를 대조하는 것도 넣었다.

-- 최근 며칠만
SELECT d, 집계값, 원본값 FROM ... WHERE 다름;

전체를 대조하면 원본을 다 세게 되니 최근 며칠만 봤다. 오래된 것이 어긋나는 일은 드물었다.

화면에서 일과 주와 월을 다 보는데 일별만 저장해도 됐다.

SELECT YEAR(date), MONTH(date), SUM(order_count) FROM daily_stats GROUP BY 1, 2;

365행을 합치는 것이라 빨랐다. 월별도 미리 저장할까 하다가 그만뒀는데 저장할 것이 늘면 갱신할 것도 늘기 때문이다.

여기서 기준이 하나 생겼다. 느린 것만 미리 계산하고 빠른 것은 그때 계산한다. 전부 미리 계산하면 갱신할 것이 늘어 병목만 미리 하는 쪽이 맞았다.

정리

통계가 매번 원본을 GROUP BY 로 세면 느리다
EXPLAIN 의 rows 가 크면 인덱스로 줄일 한계가 있다
지난 날짜의 집계는 결과가 안 바뀐다. 미리 계산해 둔다
오늘 것만 원본에서 세고 합쳐서 보여 준다
어제와 오늘 경계를 한 곳에서 정한다. 두 곳이면 어긋난다
MySQL CURDATE() 와 PHP date() 는 서로 다른 타임존 설정을 본다
date.timezone 이 비면 PHP 는 UTC 라 KST 서버와 하루가 갈린다
과거가 바뀌면 그날만 다시 계산한다. ON DUPLICATE KEY UPDATE 로 한다
주기적으로 원본과 대조하되 최근 며칠만 본다
일별이 있으면 주와 월은 합쳐서 나온다
병목만 미리 계산한다. 전부 하면 갱신할 것이 는다