MySQL을 고속으로 만드는 10 가지 방법

1. 버퍼를 늘리거나 또는  줄인다.

튜닝의 기본 중의 기본이지만, 적절한 버퍼 크기를 설정하는 것은 성능 튜닝의 핵심이다. 주요 버퍼는 다음과 같다.

innodb_buffer_pool_size ... InnoDB만을 사용하는 경우 사용 가능한 메모리의 7-8 % 정도를 할당하는 가장 중요한 버퍼이다. 여담이지만, 실제로는 여기에 지정된 값의 50-10 % 정도를 넉넉하게 메모리를 사용하므로주의가 필요하다.

key_buffer_size ... MyISAM만을 사용하는 경우 사용 가능한 메모리의 30 % 정도를 할당하면 좋다. 나머지는 파일 시스템 캐시 용으로 남겨 두자.

sort_buffer_size ... 정렬 처리에 사용하는 버퍼이다. OLTP는 256K~1M 정도를 할당하면된다. 이것이 너무 너무 크면 메모리 할당의 오버 헤드가 커지므로주의하자. DWH 시스템의 처리 등으로 큰 정렬이 필요한 경우, 세션마다 동적으로 조정하면 좋다.

read_buffer_size ... 전건 스캔을 할 때 사용하는 버퍼. OLTP는 128K~512K 정도를 할당하면된다.

read_rnd_buffer_size ... 정렬 처리에 인덱스를 이용하는 경우에 이용하는 버퍼. OLTP는 256K~1M 정도를 DWH 시스템의 처리 등으로 큰 정렬이 필요한 경우, 세션마다 동적으로 조정하면 좋다.

버퍼 늘리면 늘릴수록 좋을까 말하면 그렇지 않다. 메모리 할당이 오버 헤드이므로 쓸데없이 너무 크게 설정 것은 금물이다. 또한 버퍼를 늘려 너무 위해 스왑이 발생하면 성능이 비참하게되므로 부디 여유 메모리 용량에주의하자.

2. 고속의 디스크를 이용한다.

MySQL에만 한정된 이야기는 아니지만, RDBMS의 병목 현상은 99.99999 %가 디스크 I / O이다. 특히 디스크의 탐색 시간은 레이턴시가 크다. 이상적으로는 배터리 백업을 포함하는 RAID 장치를 이용하는 것이 좋다. 최근에는 RAID 장치에 필적 할만큼 빠른 SSD가 나오고 있기 때문에 재미이다.

전술 한 바와 같이 버퍼를 크게하면 디스크 I / O의 횟수 나 양이 줄어들 기 때문에 반드시 빠른 디스크 성능을 향상 시킨다는 것은 아니지만, 데이터 크기가 크고 버퍼에 맞지 않는 경우에는 아무래도 I / O가 대량으로 발생 해 버린다. 그럴 때는 빠른 디스크 장치를 이용하면 좋다.

3. 쿼리를 최적화

사실 가장 중요한 쿼리의 최적화이다. 아무리 다른 부분을 최적화 한 곳에서 매번 전건 스캔이 발생하고 있었던 것은 말이되지 않는다. 적절한 인덱스를 사용하거나 하위 쿼리를 JOIN에 고쳐 쓰거나하여 반입 않으면 안 줄 수가 가능한 한 적게되도록 쿼리를 작성 바꾸자. 쿼리를 최적화하려면 먼저 EXPLAIN 그리고 실행 계획을 확인하자. EXPLAIN의 견해에 대해서는 중 설명하고자한다.

또한 테이블에서 전건 인출 한 후 응용 프로그램에서 행을 수정하는 같은 논리를 구현하지 말라. 반드시 SQL 문, 즉 WHERE 절에서 행 검색 할 수 있도록하자.

쿼리를 닥치는대로 튜닝하고는 아무리 시간이 있어도 부족한 것이다. 문제가있는 쿼리 만 튜닝해야하지만, 그러한 쿼리를 찾으려면 슬로우 쿼리 로그와 상용 쿼리 분석기를 사용하면 효과적이다.

4. 테이블을 최적화

기본 중의 기본은 적절한 데이터 타입을 사용한다는 것이다. 가능한 컬럼 크기가 작아 같은 데이터 유형을 선택하자. 숫자를 VARCHAR (자릿수) 등의 데이터 타입으로 저장하는 것을 가끔 볼 수 있는데, 이것은 잘못이다. INT 또는 BIGINT 등을 이용한 것이 훨씬 데이터 크기가 작아 し 고속이다.

또한 적절한 컬럼에 인덱스를 붙이는 것도 중요하다. 어떤 컬럼에 인덱스를 붙일 지 쿼리 패턴에 인한. 인덱스가 너무 많이 업데이트시 오버 헤드가 커질뿐만 아니라 인덱스 트리를 저장하는 데이터 용량이 증가하게되므로 인덱스 붙여 너무 조심하자. 가끔 모든 컬럼에 인덱스가 붙어있는 테이블을 볼 수 있는데 그런 테이블 디자인은 잘못이다. 쿼리 패턴에 따라 멀티 컬럼 인덱스와 파티션이 필요하게되는 등 여러가지 궁리가 필요하게된다.

열 수가 너무 많아되면 우선 정상화 할 수 있는지 여부를 검토해 주었으면한다. DWH 용도 등에서는 반대로 비정규하면 성능이 향상 될 수있다.

5. 목적에 맞는 스토리지 엔진을 선택

이것은 MySQL의 묘미이다. 스토리지 엔진은 각 성능 특성이 전혀 다르기 때문에 목적에 맞는 스토리지 엔진을 선택하면 극적으로 성능이 향상 될 수있다. 예를 들어, OLTP에서는 InnoDB 참조 시스템이 많은 경우 MyISAM 로그 목적이라면 ARCHIVE 실시간 병렬 처리하면 NDBCLUSTER 등. 그 밖에도 Sun / MySQL 이외의 제 벤더 나 커뮤니티에서 출시되고있는 스토리지 엔진 (SPIDER, PBXT, XtraDB, Q4M, Infobright, Kickfire 등)도 있으므로, 목적에 맞게 다양한 검토 해 보면 좋을 것이다 .

6. 복제 부하 분산

MySQL 정도 쉽게, 그리고 저렴하게 복제를 이용할 수있는 RDBMS는 다른 없을 것이다. 복제를 사용하여 많은 슬레이브에 참조 시스템의 처리 부하를 분산하는 기술은 Web 사이트 등에서 자주 사용되는 기법이다. 참조 시스템의 부하 분산을하는 경우뿐만 아니라, 예를 들어 OLTP의 데이터를 바탕으로 BI 등의 처리를 매일 실시하는 경우 등에도 효과적이다. 슬레이브에서 BI를하면 마스터에서 OLTP 계의 처리에 영향을 줄 수 없다.

7. 저장 프로시져

불행히도, MySQL은 저장 프로 시저, 스토어드 함수, 트리거 등의 성능은 그다지 좋지 않다. 가능한 한 그들을 이용하지 않고 논리를 응용 프로그램 측에 가지고 가면 좋을 것이다.

8. 파일 시스템을 튜닝하기

Linux이면 기본은 ext3 (이제 ext4로되어가는 것일까?)이지만, ext3를 XFS를 사용하면 성능이 향상 될 수있다. 또한 I / O 스케줄러를 변경하여 동일한 ext3도 성능 특성이 변화한다. Solaris에서 UFS, ZFS, QFS 등의 이용을 고려하면 좋을 것이다. Windows라면 NTFS가 아닌에별로 선택은 아니지만, MyISAM의 경우 LargeSystemCache를 활성화하는 등의 조정이 필요하다.

9. 커넥션 풀을 이용

응용 프로그램이 DB 작업이 필요할 때 매번 MySQL 서버에 연결하고 있던 것이 연결을위한 오버 헤드가 무시 할 수 없게된다. 그럴 때는 커넥션 풀을 이용하면 좋다.

10. 벤치마킹

어떤 조정도 실제로 효과가 있는지 측정하기 전까지는 알 수 없다. 또한 응용 프로그램에 효과가있는 튜닝도 다른 응용 프로그램의 부하 패턴에서는 역효과가되어 버리는 것은 많이있다. 그래서 응용 프로그램의 부하를 의사 적으로 만들어 튜닝의 효과를 측정하는 것은 매우 중요한 것이다.