정리 작업이 끝나지 않았다

오래된 로그를 지우는 배치를 만들어 crontab 으로 매일 새벽에 돌게 했다. 한 달 뒤에 보니 표가 계속 커지고 있었다. 그런데 배치는 매일 정상으로 끝났다고 기록하고 있었다.

Open Table of contents

한 번에 지우는 양이 적었다
반복하게 바꾸고 상한을 뒀다
검증 — 조건 컬럼의 인덱스
공간이 안 줄었다
유입 자체와 보관 기간
정리

한 번에 지우는 양이 적었다

배치는 이랬다.

DELETE FROM access_log
WHERE reg_date < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT 10000;

한 번에 만 건이고 하루 한 번 돈다. LIMIT 은 표를 오래 잠그지 않으려고 걸어 둔 것이었다.

들어오는 양을 COUNT(*) 로 세어 봤다.

SELECT COUNT(*) FROM access_log WHERE DATE(reg_date) = CURDATE();
-- 84,120

하루 8만 건이 들어오고 1만 건을 지운다. 지우는 속도가 쌓이는 속도를 못 따라간다.

배치는 성공하고 표는 계속 큰다. 실행 결과가 성공이라는 것이 아무것도 보장하지 않았다. 들어오는 양과 지우는 양을 나란히 놓고 나서야 밀리는 중인지가 보였다.

반복하게 바꾸고 상한을 뒀다

한 번에 다 지우면 잠금이 길어지니 나눠서 여러 번 돌게 했다.

$total = 0;
while (true) {
    $this->db->query(
        "DELETE FROM access_log WHERE reg_date < ? LIMIT 5000",
        [$cutoff]
    );
    $n = $this->db->affected_rows();
    $total += $n;
    if ($n < 5000) break;
    usleep(200000);   // 0.2초 쉼
}
log_message('info', "로그 정리 {$total}건");

지울 것이 없어질 때까지 돈다. affected_rows 가 LIMIT 보다 작으면 끝이고 사이에 usleep 으로 짧게 쉬어서 다른 요청이 들어갈 틈을 준다.

첫 실행에서 240만 건을 지웠고 40분 걸렸다.

무한정 도는 것도 위험했다. 실수로 조건이 잘못되면 전부 지운다.

$MAX_BATCH = 500;   // 5000 × 500 = 250만 건
$i = 0;
while ($i < $MAX_BATCH) {
    ...
    $i++;
}
if ($i >= $MAX_BATCH) {
    log_message('warning', "정리 상한 도달. 남은 것은 다음 회차에");
}

MAX_BATCH 에 닿으면 멈추고 log_message 로 알린다. 남은 것은 다음 회차가 가져간다.

검증 — 조건 컬럼의 인덱스

reg_date 에 인덱스가 없으면 매번 전체를 훑는다.

EXPLAIN SELECT * FROM access_log WHERE reg_date < '2014-09-15' LIMIT 5000;

type: ALL   rows: 4210000

EXPLAIN 의 type 이 ALL 이었다. 인덱스가 없었다.

ALTER TABLE access_log ADD INDEX idx_reg_date (reg_date);

400만 건에 idx_reg_date 를 거는 데 5분이 걸렸다. 정리 배치보다 이것이 먼저였다.

공간이 안 줄었다

다 지웠는데 파일 크기가 그대로였다.

SELECT table_name, ROUND(data_length/1024/1024) AS mb
FROM information_schema.TABLES WHERE table_name = 'access_log';
-- 3820 MB

data_length 가 그대로다. InnoDB 는 DELETE 가 페이지에 빈자리를 남길 뿐 그것을 운영체제에 돌려주지 않는다.

OPTIMIZE TABLE access_log;

이것을 돌리면 줄어든다. InnoDB 에서 OPTIMIZE TABLE 은 ALTER TABLE ... FORCE 로 매핑돼 표를 다시 만든다.

다만 표를 잠그고 오래 걸린다. 새벽에 한 번 돌려서 3.8기가가 400메가가 됐다.

그리고 그것만으로는 부족하다. innodb_file_per_table 이 켜져 있어 그 표가 자기 .ibd 파일을 가질 때만 디스크가 운영체제로 돌아가고 꺼져 있으면 공유 테이블스페이스 안에서 재사용될 뿐 파일 크기는 그대로다.

지우는 것과 공간을 되찾는 것은 다른 작업이다.

유입 자체와 보관 기간

지우는 것보다 안 쌓는 것이 나았다. 무엇이 쌓이는지 GROUP BY 로 봤다.

SELECT url, COUNT(*) FROM access_log
WHERE DATE(reg_date) = CURDATE() GROUP BY url ORDER BY COUNT(*) DESC LIMIT 5;

/health           42010
/api/ping         18220
/img/logo.png     12400

상태 확인과 정적 파일이 절반이 넘었다. 이건 남길 이유가 없다.

$skip = ['/health', '/api/ping'];
if (in_array($uri, $skip, true) || preg_match('#^/(img|css|js)/#', $uri)) {
    return;
}

skip 목록과 preg_match 로 걸러서 하루 8만 건이 3만 건이 됐다.

90일이 필요한지도 물어봤다. 실제로는 한 달 이상 지난 접속 기록을 본 적이 없어서 30일로 줄였고 표가 훨씬 작아졌다. 기간을 정할 때 얼마나 오래 봐야 하는지를 실제로 물어보지 않으면 기본값이 필요 이상으로 쌓는다.

정리

지우는 속도가 쌓이는 속도를 못 따라가면 영원히 안 끝난다
DELETE ... LIMIT 이 하루 유입보다 적으면 배치가 성공해도 표는 큰다
COUNT(*) 로 들어오는 양과 지우는 양을 같이 잰다
나눠서 반복하되 사이에 usleep 을 넣는다
MAX_BATCH 로 반복 횟수에 상한을 둔다
EXPLAIN 의 type 이 ALL 이면 조건 컬럼에 인덱스가 없는 것이다
DELETE 는 페이지에 빈자리를 남길 뿐 디스크를 안 돌려준다
OPTIMIZE TABLE 은 InnoDB 에서 ALTER TABLE ... FORCE 로 표를 다시 만든다
innodb_file_per_table 이 켜져 있어야 공간이 운영체제로 간다
지우는 것보다 안 쌓는 것이 낫고 GROUP BY 로 무엇이 쌓이는지 센다
보관 기간을 실제 필요로 정한다