MySQL의 300억 레코드 물리적 삭제 이야기

소개

안녕하세요. 웹개발자 ningenMe 입니다.

제목 그대로 제 이야기는 MySQL에서 300억 개의 레코드를 물리적으로 삭제하는 이야기입니다.

나는 이것에 관심을 갖게 되었기 때문에 알림(지침)을 만들기로 결정했습니다.

홈 - 경고

제가 사용하고 유지 관리하는 배치 서버에는 하루에 한 번 MySQL에서 지난 달의 데이터를 수집하는 정기적인 프로세스가 있습니다.

보통 이 과정은 1시간 정도 안에 완료되는데 이번에는 7~8시간이 지나도 끝나지 않고 경고창도 뜨지 않더군요...

이유 찾기

프로세스를 다시 시작하고 로그를 살펴보았으나 잘못된 점은 발견되지 않았습니다.
쿼리가 올바르게 색인화되었습니다. 그런데 무엇이 잘못됐는지 생각해 보니 데이터베이스 크기가 상당히 크다는 것을 깨달았습니다.

hoge_table | 350'000'000 |

350억 XNUMX천만 개의 레코드. 인덱싱이 올바르게 작동하는 것 같았지만 매우 느렸습니다.

매월 필요한 데이터 수집량은 약 12개의 레코드였습니다. select 명령이 오래 걸려 트랜잭션이 오랫동안 실행되지 않은 것 같습니다.

DB

기본적으로 매일 약 400개의 항목이 늘어나는 테이블입니다. 데이터베이스는 지난달의 데이터만 수집하기로 되어 있었기 때문에 정확히 이 정도의 데이터를 견딜 수 있을 것으로 예상됐으나 아쉽게도 회전 작업은 포함되지 않았습니다.

이 데이터베이스는 제가 개발한 것이 아닙니다. 다른 개발자에게 맡겼기 때문에 여전히 기술적인 빚 같은 느낌이 들었습니다.

매일 삽입되는 데이터의 양이 많아져 마침내 한계에 도달하는 시점이 왔습니다. 그렇게 많은 양의 데이터를 작업할 때는 데이터를 분리해야 한다고 가정했지만 안타깝게도 그렇게 하지 않았습니다.

그리고 나서 나는 행동에 나섰습니다.

정정

로직 자체를 바꾸는 것보다 데이터베이스 자체의 크기를 줄이고 처리 시간을 줄이는 것이 더 합리적이었습니다.

300억 개의 기록을 지우면 상황이 크게 바뀔 것 같아서 그렇게 하기로 했어요... 어, 이거면 확실히 될 거라 생각했어요.

액션 1

안정적인 백업을 준비하고 드디어 요청을 보내기 시작했습니다.

「요청 보내기」

DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';

"..."

"..."

“흠… 대답은 없습니다. 어쩌면 그 과정이 오래 걸릴 수도 있지 않을까요?” — 라고 생각했는데 혹시라도 grafana를 살펴보니 디스크 부하가 엄청 빠르게 늘어나는 걸 봤습니다.
“위험해.” 나는 다시 생각하고 즉시 요청을 중단했습니다.

액션 2

모든 것을 분석한 결과, 데이터의 양이 너무 커서 한 번에 모든 것을 삭제할 수 없다는 것을 깨달았습니다.

약 1개의 레코드를 삭제할 수 있는 스크립트를 작성하기로 결정하고 실행했습니다.

「대본을 구현합니다」

“이제 확실히 효과가 있을 것 같아요.” 나는 생각했습니다.

액션 3

두 번째 방법은 효과가 있었지만 매우 노동 집약적이었습니다.
불필요한 신경을 쓰지 않고 모든 일을 신중하게 수행하려면 약 XNUMX주가 걸립니다. 하지만 이 시나리오는 서비스 요구 사항을 충족하지 못했기 때문에 이 시나리오에서 벗어나야 했습니다.

그래서 제가 결정한 것은 다음과 같습니다.

테이블을 복사하고 이름을 바꿉니다.

이전 단계에서 이렇게 많은 양의 데이터를 삭제하면 똑같이 큰 부하가 발생한다는 것을 깨달았습니다. 그래서 삽입을 사용하여 처음부터 새 테이블을 만들고 삭제할 데이터를 그 테이블로 옮기기로 결정했습니다.

| hoge_table     | 350'000'000|
| tmp_hoge_table |  50'000'000|

새 테이블을 위와 같은 크기로 만들면 데이터 처리 속도도 1/7 빨라진다.

테이블을 생성하고 이름을 바꾼 후 마스터 테이블로 사용하기 시작했습니다. 이제 300억 개의 레코드가 있는 테이블을 삭제하면 모든 것이 괜찮을 것입니다.
잘라내거나 삭제하는 것이 삭제하는 것보다 오버헤드가 덜 발생한다는 것을 알고 이 방법을 사용하기로 결정했습니다.

실행

「요청 보내기」

INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';

"..."
"..."
"여자 이름…?"

액션 4

이전 아이디어가 작동할 것이라고 생각했지만 삽입 요청을 보낸 후 여러 오류가 나타났습니다. MySQL은 용서하지 않습니다.

이미 너무 피곤해서 더 이상 이 일을 하고 싶지 않다는 생각이 들기 시작했습니다.

나는 앉아서 생각하고 한 번에 너무 많은 삽입 쿼리가 있었을 수도 있다는 것을 깨달았습니다 ...
데이터베이스가 하루에 처리해야 하는 데이터 양에 대한 삽입 요청을 보내려고 했습니다. 일어난!

그 후에도 동일한 양의 데이터에 대한 요청을 계속 보냅니다. 한 달 분량의 데이터를 삭제해야 하므로 이 작업을 약 35회 반복합니다.

테이블 이름 바꾸기

행운은 내 편이었습니다. 모든 것이 순조롭게 진행되었습니다.

경고가 사라졌습니다

일괄 처리 속도가 향상되었습니다.

이전에는 이 과정이 약 2시간 정도 걸렸지만 이제는 약 XNUMX분 정도 걸립니다.

모든 문제가 해결된 것을 확인한 후 300억 개의 레코드를 삭제했습니다. 나는 테이블을 삭제하고 다시 태어난 느낌을 받았습니다.

요약

일괄 처리에서 회전 처리가 누락된 것이 가장 큰 문제라는 것을 깨달았습니다. 이러한 종류의 아키텍처 오류는 시간 낭비로 이어집니다.

데이터베이스에서 레코드를 삭제할 때 데이터 복제 중 부하를 고려하시나요? MySQL에 과부하를 주지 말자.

데이터베이스에 정통한 사람들은 그러한 문제에 직면하지 않을 것입니다. 나머지 분들에게는 이 기사가 도움이 되었기를 바랍니다.

읽어 주셔서 감사합니다!

이 기사가 마음에 드셨는지, 번역이 명확한지, 도움이 되셨는지 알려주시면 매우 기쁠 것입니다.

출처 : habr.com