[펌][MySQL] 대용량 테이블의 경우 성능 개선을 위한 10 가지 방안

[출처] http://www.mimul.com/pebble/default/2013/12/07/1386399914136.html

---

대용량 테이블을 운용하는 경우 도움이 될만한 아티클 "Ten ways to improve the performance of large tables in MySQL"이 있어, 이 블로그에 정리해 봅니다.
아래 가지 10가지 방법을 잘 고민해 보면 좋은 도움을 받을 수 있을 것 같습니다.

오늘은 성능 문제를 일으키는 원인이 되는 대용량 테이블의 성능을 개선하는 방법을 살펴본다. 여기의 조언들은 일부는 많은 테이블을 가진 큰 데이터베이스에 적용할 수 있지만 대부분의 경우는 개별적으로 특별히 큰 테이블이 더 큰 문제라는 것이다.

테이블의 내용 변경의 속도는 테이블 사이즈가 커질수록 줄어드는 것을 일반적으로 알려진 사실이다. 아래는 B+Tree 인덱스의 성능을 시계열로 보여주는 그래프이다.



위의 그래프는 MySQL@Facebook에서 포스팅한 것이다. 이것은 insert buffer를 해제한 상태(물론 추천 하지는 않고, 데모 목적)에서 테이블에 10억 행을 insert했을 때의 성능 저하를 나타낸 것이다. 이 벤치 마크는 iibench도구를 사용했고, TokuTek에서 디자인한 것이다.

그래서 우리는 이러한 성능 저하가 인덱스 구조에 의한 것이라고 생각했지만, 그래도 우리는 이 곡선을 급격하게 내려가지 않고, 옆으로 길게 늘어지도록 할 수 있는 방법을 모색해 봐야 한다.

대용량 테이블의 성능에 영향을 줄일 수 있는 10가지 방법

1. MyISAM 대신에 InnoDB를 사용하자. MyISAM은 테이블의 마지막에 insert하는 경우에는 속도가 빠르지만, 테이블 잠금(update 및 delete에 제한 되지만)이 있고, 데이터를 디스크에서 읽기와 쓰기를 할 때 경합 때문에 키 버퍼를 보호하기 위해 싱글락을 사용한다. 또한 후술하겠지만, 체인지 버퍼 기능을 가지고 있지 않다.

2. InnoDB는 유니크하지 않은 보조 인덱스의 빌딩을 지연시키는 체인지 버퍼 기능(이전에 insert buffer라고 불리던 기능)을 가지고 있다. 이에 대한 자세한 것은 Facebook의 노트에 기술되어 있다. 이것은 위의 그래프에는 보여지지 않지만, insert의 성능을 상당히 빠르게하는 것으로, 기본적으로 활성화되어 있다. 이 기능은 MySQL 5.5에서 좋게 개선 되었기 때문에, 만약 업그레이드 하지 않는 경우에는 즉시하는 것이 좋다.

3. 파티셔닝은 인덱스의 크기를 작게하여 테이블 자체를 효율적으로 작게 나눌 수 있게 된다. 또한, MySQL 5.7.2 DMR에서 상당히 개선된 내부적인 인덱스 잠금(index->lock) 경합(contention)도 줄여 준다.

4. InnoDB의 압축 기능을 사용하자. 몇몇 부하 종류의(특별히 많은 char/varchar/text형 컬럼이있는 경우) 압축 기능은 데이터를 압축해 성능 저하의 곡선을 완만하게 해준다. 또한, 일반적으로 용량이 작은 SSD를 사용해도 된다. InnoDB의 압축 기능은 Facebook에서 제공한 여러가지 패치 덕택에 MySQL 5.6에서는 크게 개선 되었다.

5. 정렬후 대용량의 데이터를 테이블에 로드해라. 정렬된 데이터를 인서트하는 것은, 페이지 분할(메모리 상에 없는 테이블에서 성능은 악화되는)이 작게 될 것이고, 대용량 데이터의 로드는 테이블의 용량과는 특별히 관계가 없지만, redo 로그의 압축 부하를 줄여주는데 도움을 준다.

6. 테이블에서 불필요한 인덱스를 지우자. 체인지 버퍼 기능을 비활성화시키는 UNIQUE 키를 특히 주의하자. 제약 조건을 사용할 이유가 없는 경우, UNIQUE 키를 사용하지 않고 일반적인 INDEX를 사용하자.

7. 5, 6에서 관련된 PRIMARY KEY의 종류도 중요하다. 성능 저하를 빠르게 만들어버리는 GUID와 같은 데이터 타입보다, INT나 BIGINT를 사용하자. PRIMERY KEY가 없는 것도 성능에 부정적인 영향을 준다.

8. 새 테이블에 대용량 데이터를 로드할 경우 PRIMARY KEY가 아닌 인덱스는 나중에 만들자. 모든 데이터가 로드된 후 인덱스를 만든다면, InnoDB는 pre-sort와 및 대용량 로드 프로세스(빠르고 인덱스가 좀 더 콤팩트한 인덱스를 만드는)를 적용 할 수 있게 된다. 이 최적화는 MySQL 5.5에서 이루어졌다.

9. 메모리가 많으면 많을수록 도움을 받을 수 있다. 최근의 메모리의 실제 가격을 비교해 보면 새로운 데이터 베이스 서버에 너무 적은 메모리를 적용하는 것을 자주 볼 수 있다. 간단한 조언을 해 보면, SHOW ENGINE INNODB STATUS의 결과에서 BUFFER POOL AND MEMORY의 reads/s의 보여주고(읽고 있음을 나타냄), Free buffers(이것도 BUFFER POOL AND MEMORY 아래에 있다)의 수가 0이면 메모리를 더 늘리면 혜택이 얻을 수 있다.(innodb_buffer_pool_size를 잘 최적화했다는 가정하에. 이 문서를 참고).

10. 메모리 뿐만 아니라, SSD도 도움이 된다. 그래프의 곡선이 하향이 되는 이유는 테이블이 커져서 일어나는 IO 속성 때문이다. 하드 디스크가 초당 200 오퍼레이션(IOPS)을 수행하는데 반해, 일반적인 SSD는 20000 IOPS 이상 수행이 가능하다.

용어 정의

• Change Buffer: MySQL 5.5이전에는 insert buffer라고 했으며, 이름 그대로 INSERT의 성능을 향상시키기 위한 버퍼다. insert buffer는 새로운 행을 삽입하는 INSERT에 대해서만 유효했는데, Change Buffer는 UPDATE 및 DELETE도 좋은 효율성을 보여준다.