데이터 양 증가 대책과 고속화 수법

테이블 사이즈와 부하 대책

• 시간이 지나면서 데이터 양이 증가하면 적절한 인덱스가 사용되지 않아 테이블 스캔이 다발하고 있는 경우 CPU 사용량이 급증하는 형태로 문제가 발생함

• 이는 데이터베이스 성능 문제 중에서도 기본 중의 기본

• 전체 테이블 스캔과 같은 나쁜 쿼리를 고친다 해도 데이터 양의 증가에 따라서 성능은 떨어짐

• 이는 데이터 양이 적은 상황에서는 메모리 안에서의 액세스로 완결하고 있던 것이 메모리 크기를 크게 초과하면서 디스크 액세스가 다발하게 되기 때문

• 데이터 증가에 따라 성능이 악화되는 것은 참조 처리뿐 아니라 갱신 처리의 성능도 마찬가지임
• UPDATE 및 DELETE는 갱신 대상의 레코드를 특정하기 위해 읽기 작업이 필요
• INSERT는 추가하려는 레코드가 속한 인덱스 블록을 읽어 오기 위해서 로드 처리가 필요

• 참조 트래픽의 부하 대책은 그다지 어렵지 않음(슬레이브 추가 or 캐시 서버 추가)

• 한편, 갱신 처리는 데이터를 가지고 있는 모든 서버에 갱신할 수 밖에 없기 때문에 부하 대책이 어려움

INSERT 튜닝

INSERT 주체의 테이블

• 액세스 로그, 일기, 대전 이력 등의 이력계 테이블에서는 INSERT가 자주 발생함

• SELECT(및 UPDATE)가 필요가 전혀 없는 경우는 인덱스 만들 필요가 없고, 이런 상황에서는 데이터 양이 늘어도 쓰기 성능 저하가 발생하지 않음

• 그러나 SELECT 빈도가 나름 높다면 인덱스가 필요.

• 레코드 수가 늘어나면 인덱스 크기도 커짐 → 거대한 인덱스를 메모리 안에서 다 처리할 수 없음 → 갱신 대상의 인덱스를 로드하기 위해 디스크 로부터의 로드가 발생 (대폭적 성능 다운) → 서비스를 운용하는 데 커다란 리스크

INSERT의 성능 지표

• 메모리 내에서 INSERT가 완결되고 있던 것이 디스크 I/O 가 발생하게 됨으로써 어느 정도의 성능 저하가 발생하는지를 지표로서 파악해 두는 것이 중요함

• 메모리 내에서의 INSERT라면 초간 10,000건 ~ 15,000건 정도는 충분히 도달 가능함

• 인덱스 사이즈가 메모리 사이즈(버퍼 풀 사이즈)를 초과하여 디스크 읽기가 다발하게 되면 초당 INSERT 성능은 크게 떨어지기 시작함

• InnoDB는 Insert Buffering이라는 자체 최적화 메커니즘이 있어서 랜덤 읽기의 발생을 최소화 해 주기 때문에 더 빨라진다. 하지만 그렇다고 해도 초당 2,000 ~ 4,000 INSERT 정도까지 떨어짐

• INSERT를 고속화하기 위해서는 뭐니뭐니 해도 INSERT를 메모리 안에서 완결하는 것이 중요. 이를 실현하기 위해서는 인덱스 크기를 작게 하는 것이 필요함

시계열 처리의 고속화 접근

• 날짜로 레인지 파티션을 구성하기 (테이블을 물리적으로 분할하면서도 애플리케이션에서는 논리적으로 하나의 테이블로서 보여주는 것임)

• INT 형이나 TIMESTAMP 형이라면 초 단위까지 설정이 가능하고 과거 데이터를 ALTER TABLE… DROP PARTITION 으로 빠르게 제거가 가능함

• 다만 MySQL에서는 TIMESTAMP 형을 파티션의 키로 하면 파티션 풀링(특정 파티션에만 액세스하는 것)을 할 수 없기 때문에 이 경우는 INT 형이 좋음

DELETE의 튜닝

UPDATE 튜닝

• UPDATE 문을 실행하기 위해서는 갱신 대상의 레코드(블록)을 메모리에 읽어 올 필요가 있음

• 인덱스 뿐 아니라 테이블이 거대하면 할수록 메모리 안에서 전부 처리하는 것이 어려워져 디스크 I/O가 발생하기 쉬움

• INSERT에 비교하면 UPDATE 만으로 데이터의 양이 증가하는 것은 아니므로 갑자기 성능 문제가 발생하지는 않음. 그러나 소지 아이템 데이터 등은 시간이 지남에 따라 서서히 INSERT 되어 증가하기 때문에 단계적으로 디스크 I/O가 보다 많이 발생. 따라서 데이터 양의 증가 상태나 부하의 증가 상태를 관찰 하면서 너무 늦지 않게 대책을 강구해야 함

• INSERT 문이 적절한 튜닝에 의해 메모리 안에서의 처리를 완결시키기 쉽고 DELETE 문이 사용자 측면에서 비동기적으로 실행시키기 쉬운 반면(심야 등 트래픽이 적은 시간에 시행하는 것도 가능), UPDATE 문은 사용자 측면에서 동기적으로 실행해야 함. 또한 필요한 레코드가 메모리에 있어야 디스크에서의 읽기를 동기적으로 할 수 있다는 점에서 결국 튜닝이 가장 어려운 유형

• UPDATE 문의 성능은 하드웨어의 스펙에 크게 의존. 메모리 내에서는 초당 12,000회 정도 일반적으로 실행 가능. 한편 HDD에 액세스가 집중하기 시작하면 초당 300회 정도밖에 실행할 수 없음

• 디스크 I/O가 빈발하고 있는 경우의 해결 방법으로 가장 효과적이라고 생각되는것이 SSD로의 교체. 혹은 메모리의 증설

부하 경향 모니터링하기

• MySQL의 효과적인 지표로서 갱신 빈도와 병렬도 를 들 수 있음