[요약]MySQL을 위한 하드웨어 최적화

원글 작성일자 : 2009.12.28
원문링크 : http://database.sarang.net/database/mysql/tuning/optimize_mysql.html

비록 MySQL3.x대의 저수준시절에 나온 팁들이지만 그래도 유용하고 지금5.5버전에도 적용하기에 유효한것들이 많다. MySQL의 전반적인 부분을 이해하는데 매우 중요한 사항임을 명심하고 체크하자.

- 64bit & RAM의 양을 늘려라
큰 테이블(2GB)이 필요하다면, 64bit 하드웨어 시스템 구축하라. MySQL은 내부적으로 64bit정수를 많이 사용한다.
거대한 데이터베이스를 위한 최적화는 RAM, DISK I/O, CPU 순으로 진행
특히 RAM은 많을수록 좋다. 더많은 램은 사용되는 대부분의 KEY 페이지들을 RAM에 보관함으로써 빠른 KEY의 갱신이 가능하게 한다.

- 디스크를 최적화 하라.
시스템, 프로그램, 임시파일들을 위한 전용 디스크를 분리해서 구축하라.
자주 변경되는 경우에는 변경되는 기록과 트랜잭션 기록파일까지도 별도의 디스크에 배치하도록 한다.
데이터베이스 디스크는 빠른 탐색시간(seek time)이 키포인트다. 큰 테이블에서 하나의 레코드를 찾기 위해 소요되는 탐색횟수는 다음과 같이 계산할수 있다.

log(rowCount) / log(indexBlockLength / 3*2/(keyLength + dataPtrLength)) + 1

mediumint 형의 50만개 row를 가지고 있는 테이블의 경우  log(500000)/log(1024 /3*2/(3+4)) + 1 = 4. 즉 4번의 탐색을 필요로 한다.
인덱스의 크기는 500000 *7*3/2 = 5.2MB 이지만 실제 대부분의 블록들이 버퍼에 저장되므로 1,2회만에 탐색을 마칠수있다.

쓰기의 경우 새로운 키를 넣을 위치를 찾기 위해서는 위 계산과 같이 4번의 탐색이 필요하지만, 통상적인 인덱스를 갱신하기 위해서는 2번의 탐색이 더 필요하다.
매우 큰 데이터베이스의 경우, 디스크 탐색속도에 의해 성능이 좌우되는데, 탐색수는 더 많은 데이터를 얻을 때마다 N log N 씩 증가한다. 데이터베이스들과 테이블들을 다른 디스크에 분할해 넣은뒤 심볼릭링크를 통해 사용하도록 한다.  이럴 경우 좀더 향상된 성능을 얻을 수 있다.
Striping disk(RAID 0)는 읽기와 쓰기 양면에서 처리능력을 향상시킬수 있다.
미러링을 동반하는 Striping disk (RAID 0 + 1) 는 읽기/쓰기 성능을 향상시키고 안전성을 제공하지만 쓰기는 약간 느리다.
임시파일 또는 쉽게 갱신될수 있는 데이터에 대해서는 미러링이나 RAID를 사용하지 않는다. (RAID 0는 제외).

- Linux 최적화
1. 부팅할 때 hdparm -m 16 -d1 명령을 디스크에 적용하여 다중 섹터 읽기/쓰기와 DMA 사용이 가능하도록 한다. 이는 반응시간을 5~ 50% 증가시킨다.
2. 디스크를 마운트할때 async(기본값)와 noatime 옵션을 부여하여 마운트한다. 일부 특정 프로그램의 경우 아주 특수한 테이블을 램디스크에 저장하는 것도 방법이 된다. 그러나 보통은 필요없다.

- 운영체제 최적화
1. 스왑을 제거한다. 무조건 메모리는 증설하라.(16GB정도면 괜찮을듯)
2. NFS는 절대 금지.
3. 시스템과 SQL서버를 위해서 open file 한계 수치를 증가시킨다.(safe_mysql 스크립트에 ulimit -n #을 추가한다.)
4. 프로세스와 쓰레드의 개수 제한을 늘려준다.
5. 파일시스템을 변경해보라. ReiserFS는 파일 읽기/쓰기에 있어 ext2보다 빠르다. 파일검사도 단지 수초많에 끝난다.

첨부 : (현재 CentOS 5.x 에서는 ext3, XFS 를 CentOS6.x 에 와서는 ext4를 지원한다. 이들의 성능비교는?)

 - 응용프로그램 최적화
1. 캐시서버는 필수다. 캐싱은 SQL 서버의 부하를 감소시킨다. 메이저포탈중에서 캐시서버를 사용하지 않는곳이 있는가?
2. 프로그램에서 사용하지 않는 컬럼은 사용하지 않는다. 공통사용을 할 목적으로 select * from 과 같은 쿼리는 사용하지 않도록 한다. 차라리 두개의 쿼리를 사용하는 편이 낫다.
3. 응용프로그램 모든부분에 대한 벤치마킹을 시도한다. 가장 유력한놈을 우선 선정하여 수행하고 발견되는 병목구간은 더미모듈로 대체한 뒤 다음 병목구간을 찾아나서는 식의 순서로 진행하도록 한다.
4. 일련의 작업중에 많은 변경(update, delete등)이 일어난다면 LOCK TABLES를 이용한다.

 - 병목구간 제거하기
1. cpu, 디스크, 메모리, SQL 서버, OS, API, 그리고 응용프로그램등의 병목구간을 찾아서 제거한다.
2. MySQL의 확장기능을 찾아서 사용하도록 한다.
3. SQL변경(쿼리 튜닝).
4. 테이블 레이아웃과 쿼리의 최적화
==> Oracle과 MySQL은 엄연히 다른 DBMS다. 쿼리도 다르게 사용해야 한다
5. replication 사용(select에서 기인한 동시처리를 분산하기 위한 목적)
6. 느린 네트워크라면 압축된 클라이언트/서버 의 프로토콜을 사용한다.
7. 이식성에 대한 부분은 먼저 걱정하지 말고 속도문제를 해결한뒤에 최적화 하라.

- MySQL최적화
1. 컴파일러와 컴파일 옵션을 충분히 고려하여 선택한다.
2. MySQL 시작옵션을 찾아본다.
3. MySQL 매뉴얼을 가까이한다.
4. Explain, Explain extended, show variables, show processlist 등의 명령을 사용한다.
processlist 를 일정시간마다 주기적으로 확인을 하려면 interval 옵션을 두어서 체크한다.
==> mysqladmin -i5 processlist (<== 5초마다 processlist를 확인한다.)
예전에 이 옵션을 알지 못했을때는 mytop utility를 쓰곤 했었는데, 설치되지 않은 DB에서 불편했던 것을 이 옵션을
있음을 알고 사용중이다.
5. 쿼리 옵티마이저가 작동하는 방식을 공부하고 이해하도록 한다.
6. myisamchk, check table, optimize table 등의 명령어로 테이블을 관리한다.

- MySQL 컴파일 및 설치
1. 자신의 시스템에서 사용 가능한 최상의 컴파일러를 선택함으로써 보통 10~30% 정도 성능향상을 기대할 수 있다.
2. MySQL에서 권장하는 플랫폼 별 최적의 옵션을 사용한다.
3. MySQL을 사용하려는 한 가지 문자셋만 지정하여 컴파일 한다.
4. mysqld 실행파일을 정적으로 컴파일(--with -mysqld -ldflags=-all-static)하고 strip sql/mysqld 명령으로 최종 실행파일에서 디버그 코드를 제거한다.
5. mySQL 벤치마크 테스터로 테스트하여 성능을 측정한다.

유지보수
1. 정기적으로 optimize table을 실행한다. 이는 특히 자주 갱신되는 가변크기 레코드에 대해서 중요하다.
2. 정기적으로 myisamchk -a 명령을 사용하여 테이블들의 key분산상태를 갱신한다. 이 작업을 수행하기 전에는 반드시 mySQL을 셧다운해야 한다는 점을 명심하라.
3. 파일들이 조각난 상태라면 다른 디스크로 모두 복사하고 기존의 디스크를 깨끗이 한 후 다시 파일을 옮기는 일도 시도해 볼만한 가치가 있다.
4. 문제가 발생한다면 테이블을 myisamchk나 CHECK table 명령으로 검사한다.
5. MySQL의 상태를 mysqladmin -i10 processlist extended-status 명령으로 모니터한다.
6. mysql gui 클라이언트를 사용하여 프로세스 목록과 상태를 다른 윈도우에서 모니터할 수 있다.
7. mysqladmin debug명령을 사용하여 잠금(lock)과 성능에 관한 정보를 얻는다.


SQL 최적화

RDBMS를 사용하는것이 좋은것에만 SQL을 사용하고 그렇지 않은 곳에서는 다른것(noSQL같은...)을 사용한다.
- WHERE 절에 의존하여 행을 찾는 경우
- 테이블들을 JOIN 할 경우
- GROUP BY
- ORDER BY
- DISTINCT

key를 폭넓게 사용한다.

key는 검색에는 좋지만, update, insert를 수행하는데는 좋지 않다.(index 업데이트가 시간이 많이 걸려 table lock을 유발할 수 있다.)
데이터를 제3의 보통데이터베이스 형식(in the 3rd normal database form)으로 유지하되, 속도를 중시한다면 정보의 중복이나 요약테이블(summary tables)을 생성하는 일을 기피할 필요는 없다.
큰 테이블에 대해서 GROUP BY 를 남용하는 대신 그 테이블에 대한 요약 테이블을 생성하고 이 테이블에 대해 쿼리하는 것이 낫다.
insert 에서 default values의 잇점을 십분 활용한다.


테이블 최적화

MySQL은 풍부한 상이한 컬럼 유형(type)들의 집합을 가지고 있다. 각 컬럼에 대해 가장 효과적인 유형을 선택하여 사용하는 것이 필요하다.

ANALYSE 프로시저는 테이블을 위한 최적의 컬럼 유형을 찾는데 도움이 될 것이다. SELECT * FROM table_name PROCEDURE ANALYSE()

널 값을 저장하지 않을 컬럼은 NOT NULL 로 지정한다. 이는 특별히 인덱스 컬럼의 경우 중요하다.

ISAM 테이블들을 MyISAM 으로 변경한다.

가능하다면, 테이블을 고정된 테이블 형식으로 만드는 것이 좋다.

사용하지 않을 인덱스는 아예 만들지 않는다.

MySQL이 인덱스의 접두부(prefix)에 대해서 검색을 수행할 수 있다는 점을 활용한다. INDEX (a, b) 로 되어 있다면, (a)에 대해 인덱싱할 필요는 없다.

길이가 긴 CHAR 형이나 VARCHAR 형이라면 해당 컬럼에 대해 인덱스를 생성하지 않고 그 컬럼의 접두부에 대해서만 인덱스를 생성하면 공간이 절약된다.

CREATE TABLE table_name (hostname CHAR(255) not null, index(hostname(10)))

각 테이블을 위한 가장 효과적인 테이블 유형을 사용한다.

서로 다른 테이블들 중 동일한 정보를 가지는 컬럼들은 같은 유형, 같은 이름을 가지도록 정의한다.

MySQL이 데이터를 저장하는 방식

- 데이터베이스는 디렉토리로 저장된다.
- 테이블은 파일로 저장된다.(*.frm, *.myi, *.myd)
- 컬럼은 가변 길이나 고정 길이 유형으로 파일 안에 저장된다. BDB테이블에서 데이터는 페이지에서 저장된다.
- 메모리 기반의 테이블도 지원된다.
- 데이터베이스와 테이블들은 다른 디스크로부터 심볼릭 링크될 수 있다.
Windows용 MySQL은 .sym 파일을 이용하여 데이터베이스에 대한 내부적인 심볼릭링크를 지원한다.

MYSQL 레코드 유형(MyISAM)
- MySQL은 모든 컬럼들이 고정 크기 유형이라면 (VARCHAR, BLOB, TEXT 컬럼이 없다면) 테이블을 고정 크기 테이블로 생성한다. 그렇지 않다면, 가변 크기 유형의 테이블로 만든다.
- 고정 크기 유형은 동적 크기 유형에 비해 속도가 빠르며 안전하다
- 동적 크기 레코드 유형은 대개 보다 적은 공간을 사용하지만 테이블의 갱신이 자주 발생한다면 파편화가 가중되기 마련이다.
- 어떤 경우에는 주 테이블의 속도를 향상시키기 위해 모든 VARCHAR, BLOB, TEXT 컬럼들을 다른 테이블로 옮기는 것도 유용할 때가 있다.
- myisampack을 사용하면 읽기 전용, 압축된 테이블을 만들 수 있다. 느린 디스크를 사용할 때는 디스크 사용량을 최소화하는 것이 좋은 한 방법이 된다. 압축된 테이블은 더 이상 갱신되지 않는 로그 테이블 등에 사용하면 최상이다

MySQL 캐시들(한번 적재되어 모든 쓰레드가 공유)
- 키 캐시: key_buffer_size, 기본값은 8M
- 테이블 캐시: table_cache, 기본값은 64
- 쓰레드 캐시: thread_cache_size, 기본값은 0
- 호스트명 캐시: 컴파일할 때 변경 가능, 기본값은 128
- 메모리에 맵핑된 테이블(Memory mapped tables): 현재는 압축된 테이블을 위해서만 사용된다.

MySQL 버퍼 변수들(공유되지 않으며 실행 중 적재됨)
- sort_buffer: ORDER BY / GROUP BY 절에서
- record_buffer: 테이블을 스캔할 때
- join_buffer_size: 키 없이 join을 수행할 때
- myisam_sort_buffer_size: REPAIR TABLE에서
- net_buffer_length: SQL 문장을 읽을 때와 결과 값을 버퍼링할 때
- tmp_table_size: 임시 결과값을 위한 HEAP-table-size

MySQL에서 테이블 캐시가 동작하는 방식
- MyISAM 테이블의 열려 있는 각각의 인스턴스는 인덱스 파일과 데이터 파일을 사용한다. 만일 어떤 테이블이 두 개의 쓰레드에 의해 사용되거나 같은 쿼리에서 두 번 사용되면, MyISAM이 인덱스 파일은 공유하지만 데이터 파일은 또 하나의 인스턴스를 위해 추가로 열게 된다.
- 캐시 안의 모든 테이블이 사용 중이라면 그 캐시는 일시적으로 테이블 캐시 크기보다 커진다. 이러한 상황이 발생하면, 그 다음 방면된 테이블이 닫히게 된다.
- mysqld 변수 Opend_tables를 검사해 보면 테이블 캐시가 너무 작은지 아닌지를 알 수 있다. 이 값이 높으면 테이블 캐시를 늘려줘야 한다.

MySQL 이 쿼리를 해결하는 방법을 찾는 방법
- show variables
- show column from \G;
- explain select ~ \G;
- flush status;
- select ...;
- show status\

MySQL에서 피해야 할것.
- select 결과를 insert , update 하기 즉, 테이블을 UPDATE 또는 삭제된 행을 테이블에 대해 INSERT하면서 시간이 오래 걸리는 SELECT 절들과 결합시키는 일
- WHERE 절에 올 수 있는 것들에 대한 HAVING
- 키를 사용하지 않은, 또는 충분히 유니크하지 않은 키를 사용한 JOIN
- 컬럼 유형이 서로 다른 컬럼들에 대해 JOIN 수행
- 온전한 키 전체가 아닌 키의 부분에 대해서만 '=' 로 비교연산할 때 HEAP 테이블을 사용
- MySQL monitor 에서 UPDATE 나 DELETE를 사용하면서 WHERE 절을 생략하는 일. 만약 자신이 이런 경향이 있다면, mysql 클라이언트 프로그램을 실행할 때 --i-am-a-dummy 옵션을 추가하기 바란다.

MySQL의 독특한 잠금들(Lock)

문제를 쉽게 해결하기 위해 MySQL로 부터 더 많은 정보를 얻는 기법들

MySQL 만의 기능들을 항상 주석처리함으로써 쿼리의 이식성 높일 수 있다.

SELECT /*! SQL_BUFFER_RESULTS */ ...

SELECT SQL_BUFFER_RESULTS ...
MySQL이 임시 결과 세트를 만들도록 강제한다. 임시 세트가 만들어지면, 그 테이블들에 대한 모든 잠금이 해제된다. 이는 테이블 잠금으로 인해 문제가 발생했을 때나 쿼리 결과를 클라이언트로 전송하는데 오랜 시간이 소요되는 경우에 도움이 된다.

SELECT SQL_SMAIL_RESULT ... GROUP BY ...
결과 세트가 적은 수의 레코드만 가지게끔 하도록 옵티마이저에게 지시한다.

SELECT SQL_BIG_RESULT ... GROUP BY ...
결과 세트가 많은 수의 레코드를 가지도록 옵티마이저에게 지시한다.

SELECT STRAIGHT_JOIN ...
옵티마이저가 FROM 절에 나타난 순서대로 테이블을 join 하도록 강제한다.

SELECT ... FROM table_name [USE INDEX (index_list) | IGNORE INDEX (index_list)] table_name2
MySQL이 특정 인덱스들을 사용하거나 무시하도록 강제한다.

일반적인 팁

- 프라이머리 키는 짧은 것을 사용한다. 테이블 조인할 때는 문자열형 보다는 숫자형을 사용한다.
- 여러 부분으로 구성된 키를 사용할 때는 첫 번째 부분이 가장 많이 사용되는 키이어야 한다.
- 의심스러울 때는, 앞부분이 더 많이 중복된 컬럼을 사용해서 보다 나은 키 압축 효과를 얻는다.
- 클라이언트를 실행 중이고 MySQL 서버가 같은 머신에 있다면, TCP/IP 대신에 유닉스 소켓을 사용하여 서버에 연결하는 것이 좋다.(이렇게 하면 7.5% 정도까지 효율이 증진된다.) MySQL 서버에 접속할 때 호스트이름이나 localhost를 지정하지 않으면 유닉스 소켓으로 접속하게 된다.
- 가능하다면 --skip-locking(일부 운영체제에서는 이 것이 기본값이다)을 사용한다. 이는 외부적인 잠금을 사용하지 않게 되고 퍼포먼스가 향상된다.
- 긴 키를 사용하기 보다는 응용프로그램 수준에서 해시된 값을 사용한다.

SELECT * FROM table_name WHERE hash=MD5(concat(col1,col2)) AND col_1='constant' AND col_2='constant';

Store BLOB's that you need to access as files in files. 데이터베이스에는 파일이름만 저장한다.
레코드들의 큰 부분을 지우는 것 보다 레코드 전체를 지우는 것이 더 빠르다.
SQL이 충분히 빠르지 않다면, 데이터에 접근하는 더 낮은 수준의 인터페이스를 점검해 본다.