mysql의 최대 성능 향상 방법

mysql의 최대 성능 향상 방법

출처 : http://www.apmtip.co.kr

버퍼 크기 조정

mysqld 서버가 사용하는 기본 버퍼 크기는 다음의 명령으로 알 수 있다.

shell> mysqld --help

이 명령은 모든 mysqld 옵션의 목록과 설정 변수를 보여준다. 출력되는 내용은 기본값을 포함하고 있으며 다음과 비슷하다.

Possible variables for option --set-variable (-O) are:
back_log current value: 5
connect_timeout current value: 5
join_buffer current value: 131072
key_buffer current value: 1048540
long_query_time current value: 10
max_allowed_packet current value: 1048576
max_connections current value: 90
max_connect_errors current value: 10
max_join_size current value: 4294967295
max_sort_length current value: 1024
net_buffer_length current value: 16384
record_buffer current value: 131072
sort_buffer current value: 2097116
table_cache current value: 64
tmp_table_size current value: 1048576
thread_stack current value: 131072
wait_timeout current value: 28800

mysqld 서버가 현재 가동 중이면 다음의 명령을 통해 실제 변수 값을 볼 수 있다.

shell> mysqladmin variables

각 옵션은 밑에서 설명한다. 버퍼 크기, 길이, 스택 크기는 바이트이다. 'K'(킬로바이트)나 'M'(메가바이트)를 앞에 붙여 값을 지정할 수 있다. 예를 들면 16M는 16 메가바이트를 가리킨다. 대소문자는 구별하지 않는다. 16M 와 16m은 같다.

back_log

mysql이 가질 수 있는 최대 연결 요청의 수. 이것은 main mysql 쓰레드가 매우 짧은 시간동안 매우 많은 연결 요청을 받을 때 기능을 한다. 이때 메인 쓰레드가 연결을 체크하고 새로운 쓰레드를 시작하는 데는 약간의 시간이 걸린다.(그러나 아주 짧은 시간임) back_log 값은 mysql이 순간적으로 새로운 요청에 답하는 것을 멈추기 전에 이 짧은 시간동안 얼마나많은 요청을 쌓아두고 있는지를 지정한다. 매우 짧은 시간동안 매우 많은 연결이 예상될 때만 이 값을 증가 시켜야 한다. 다른 말로 이 값은 tcp/ip 연결을 받는 listen queue의 크기이다. 각 운영체제마다 이러한 큐의 크기에 한계가 있다. Unix system call listen(2) 매뉴얼페이지에 자세한 정보가 있다. back_log값의 한계는 운영체제 문서를 확인해봐라. back_log를 최대값보다 더 높여도 효과가 없다.

connect_timeout

Bad handshake에 반응하기 전에 연결 패킷을 mysql 서버에서 기다리는 시간.(초)

join_buffer

(인덱스를 사용하지 않는 조인의) full-join에서 사용하는 버퍼의 크기. 버퍼는 두 테이블 사이에서 각 full-join마다 한번 할당이 된다. 인덱스를 추가하지 못할 때 조인 버퍼를 증가시키면 full join의 속도를 향상시킬 수 있다. (일반적으로 빠르게 조인을 하는 가장 좋은 방법은인덱스를 추가하는 것이다)

key_buffer

인덱스 블록은 버퍼링되고 모든 쓰레드에서 공유한다. 키 버퍼는 인덱스 블록에서 사용하는 버퍼의 크기이다. 인덱스가 많은 테이블에서 delete나 insert 작업을 많이 하면 키 버퍼 값을 증가 시키는 것이 좋다. 더 빠른 속도를 내려면 LOCK TABLES를 사용하자

max_allowed_packet

한 패킷의 최대 크기. 메시지 버퍼는 net_buffer_length 바이트로 초기화되지만 필요하면 최대 허용 패킷 바이트를 증가시킬 수 있다.기본값은 큰 패킷을 잡기에는 작다. 거대 BLOB컬럼을 사용한다면 값을 증가 시켜야 한다. 사용자가 원하는 최대 blob만큼 크게 해야 한다.

max_connections

동시 클라이언트 숫자. mysqld가 필요로 하는 파일 지시자(descriptor)의 숫자만큼 값을 늘려야 한다. 밑에서 파일 디스크립터 제한에 대한 내용을 참고하자.

max_connect_errors

호스트에서 최대 연결 에러이상의 interrupted 연결이 있으면 더 많은 연결을 위해 호스트는 block화된다. FLUSH HOSTS 명령으로 호스트의 block을 해제할 수 있다.

max_join_size

최대 조인 크기이상으로 레코드를 읽는 조인을 하면 에러가 난다. 만약 사용자가 where 문을 사용하지 않고 시간이 많이 걸리면서 몇 백만만 개의 레코드를 읽는 조인을 수행하려 하면 이 값을 설정한다.

max_sort_length

BLOB나 TEXT 값으로 정렬할때 사용하는 바이트의 숫자. (각값 중 오직 첫번째 max_sort_length 바이트만 사용된다. 나머지는 무시된다)

net_buffer_length

질의에서 통신 버퍼가 초기화되는 크기. 일반적으로 바뀌지 않지만 매우 적은 메모리를 가지고 있을 때 예상되는 질의에 맞게 세팅할 수 있다. (이것은 클라이언트에 가는 예상된 sql문의 길이이다. 질의문이 이 크기를 넘으면 버퍼는 자동으로 max_allowed_packet 바이트까지 증가한다)

record_buffer

순차적인 검색을 하는 각 쓰레드에서 각 검색 테이블에 할당하는 버퍼 크기. 순차적인 검색을 많이 하면 이 값을 증가 시켜야 한다.

sort_buffer

정렬이 필요한 각 쓰레드에서 할당하는 버퍼 크기. order by 나 group by 오퍼레이션을 빠르게 하려면 이 값을 증가 시킨다.

table_cache

모든 쓰레드에서 열 수 있는 테이블의 숫자. mysqld가 필요로 하는 파일 디스크립터의 숫자만큼 이 값을 증가 시킨다. mysql은 각 유일한 오픈 테이블에서 두개의 파일 디스크립터가필요하다.

tmp_table_size

임시 테이블이 이 값을 넘으면 mysql은 "The Table tbl_name is full"이라는 에러 메시지를낸다. 매우 많은 group by 질의를 사용하면 이 값을 증가 시켜야 한다.

thread_stack

각 쓰레드의 스택 사이즈. creash-me test(데이터베이스의 벤치마킹을 하는 테스트이다.)에서 잡히는 많은 제한은 이 값에 달려있다. 기본값은 일반적으로 충분히 크다. 11장의 [벤치마크] 참조

wait_timeout

연결을 끊기 전에 연결 활동(activity)을 서버에서 기다리는 시간(초).

table_cache 와 max_connections는 서버가 열 수 있는 최대 파일 갯수에 영향을 미친다. 이값을 증가 시키면 운영시스템에서 오픈 파일 디스크립터의 per-process 숫자의 한계까지 올릴 수 있다. 그러나 많은 시스템에서 이 한계를 증가시킬수 있다. 이렇게 하려면 각 시스템에서 이 한계를 변화 시키는 방법이 매우 다양하므로 운영체제 문서를 참고해야 한다.

table_cache 는 max_connections 와 관계가 있다. 예를 들면 200개의 연결이 있으면 최소 200 * n 의 테이블 캐쉬를 가 져야 한다. 여기서 n은 조인에서 테이블의 최대 숫자이다.

mysql은 매우 유용한 알고리즘을 사용하기 때문에 일반적으로는 매우 적은 메모리로 사용할 수 있으며 메모리가 많을 수록 성능이 더 많이 향상된다.

많은 메모리와 많은 테이블을 가졌고 중간정도 숫자의 클라이언트에서 최대의 성능을 원한다면 다음과 같이 사용한다.

shell> safe_mysqld -O key_buffer=16M -O table_cache=128 -O sort_buffer=4M -O record_buffer=1M &

메모리가 적고 연결이 많으면 다음과 같이 사용한다.

shell> safe_mysqld -O key_buffer=512k -O sort_buffer=100k -O record_buffer=100k &
shell> safe_mysqld -O key_buffer=512k -O sort_buffer=16k -O table_cache=32 -O record_buffer=8k -O net_buffer=1K &

매우 많은 연결이 있을 때 mysqld가 각 연결마다 최소한의 메모리를 사용하도록 설정하지않았다면 "swapping problems" 문제가 생길 것이다.

mysqld에서 옵션을 바꾸었으면 그것은 서버의 해당하는 인스턴스에만 영향을 미친다는 것을 기억하자.

옵션을 바꾸었을 때의 효과를 보기 위해 다음과 같이 해보자.

shell> mysqld -O key_buffer=32m --help

마지막에 --help 옵션이 들어간 것을 기억하자. 그렇지 않으면 커맨드 라인에서 사용한 옵션의 효력은 출력에는 반영되지 않을 것이다.

메모리 사용 방법 <메모리 최적화>

아래에서 설명하는 목록은 mysqld 서버가 메모리를 사용하는 방법에 대해서 나타내고 있다. 메모리 사용과 관련된 서버의 변수 이름이 주어진다.

- 키 버퍼(변수 key_buffer)는 모든 쓰레드에서 공유한다. 서버에서 사용하는 다른 버퍼는필요한대로 할당이 된다.
- 각 연결은 각 쓰레드 마다 특정한 공간을 사용한다. 스택(64k, 변수 thread_stack) , 연결버퍼(변수 net_buffer_length), result 버퍼 (변수 net_buffer_length) 등. 연결 버퍼와 result버퍼는 필요할 때 max_allowed_packet 까지 동적으로 증가된다. 질의가 수행될 때 현재의질의문의 복사문이 또한 할당이 된다.
- 모든 쓰레드는 같은 기본 메모리를 공유한다.
- 메모리 맵은 아직 지원이 안된다. (압축 테이블을 제외하고. 그러나 이것은 다른 이야기이다) 왜냐하면 4GB의 32비트 메모리 공간은 대부분의 대형 테이블에서 충분히 크기가 않기때문이다. 우리가 64비트 주소 공간을 가진 시스템을 가지게 될 때 우리는 메모리 맵핑을위한 일반적인 지원을 추가할 것이다.
- 테이블에서 순차적인 검색을 하는 각 요청은 read 버퍼에 할당이 된다. (변수 record_buffer)
- 모든 조인은 한번에 수행이 되며 대부분의 조인은 임시 테이블을 생성하지 않고 수행이된다. 대부분의 테이블은 메모리 기반(HEAP) 테이블이다. 거대 길이의 레코드를 가졌거나 BLOB 컬럼을 포함한 임시 테이블은 디스크에 저장이 된다. 현재의 문제는 메모리 기반 테이블이 tmp_table_size를 초과했을 때 "The table tbl_name is full"이라는 에러가 생기는 것이다. 가까운 시일 안에 필요할 때 자동적으로 메모리 기반(HEAP) 테이블을 디스크 기반(NISAM) 테이블로 바꾸도록 고칠 것이다.
//이 문제를 해결하기 위해서 mysqld의 tmp_table_size 옵션을 설정하여 임시 테이블 크기를늘이거나 클라이언트 프로그램에서 SQL_BIG_TABLES라는 sql 옵션을 설정하여야 한다
//mysql 3.20에서 임시 테이블의 최대 크기는 record_buffer*16이다. 3.20 버전을 사용하고 있다면 record_buffer의 값을 증가시켜야 한다. 또한 mysqld를 시작할 때 --big-tables 옵션을사용하여 항상 임시 테이블을 디스크에 저장할 수 있지만 질의 속도에 영향을 미친다.
- 정열을 하는 대부분의 요청은 정렬 버퍼와 하나나 두개의 임시 파일을 할당한다.
- 대부분의 파징(parsing)과 계산은 지역 메모리에서 이루어진다. 작은 아이템에는 메모리 overhead가 필요없고 일반적인 느린 메모리 할당(slow memory allocation)과 freeing(메모리해제)는 무시된다. 메모리는 오직 예상지 못한 거대 문자열에서 할당이 된다.( mallloc() 과free() 사용)
- 각 인덱스 파일은 한번에 열리며 각 병행수행되는 쓰레드에서 데이터 파일은 한번에 열린다. 각 병행수행 쓰레드마다 테이블 구조, 각 컬럼의 컬럼 구조, 3 * n 의 버퍼 크기가 할당된다. ( n은 최대 레코드 길이이며 BLOB 컬럼은 해당하지 않는다) BLOB는 BLOB 데이터의 길이에 5에서 8 바이트를 더한 값을 사용한다.
- BLOB 컬럼을 가진 각 테이블에서 버퍼는 거대 BLOB 값을 읽을 수 있도록 동적으로 커진다. 테이블을 검색하면 버퍼는 최대 BLOB의 값만큼 버퍼가 할당이 된다.
- 모든 사용중인 테이블의 테이블 핸들러는 캐쉬에 저장되며 FIFO로 관리가 된다. 일반적으로 캐쉬는 64 엔트리를 갖는다. 동시에 두개의 실행 쓰레드에서 테이블을 사용하면 캐쉬는 테이블의 두 엔트리를 포함한다.
- mysqladmin flush-tables 명령은 사용하지 않는 모든 테이블을 닫고 현재 실행되는 스레드가 끝날 때 모든 사용중인 테이블을 닫는다고 표시한다. 이것은 효과적으로 사용중인 메모리를 해제한다.

ps 와 다른 시스템 상황 프로그램은 mysqld가 많은 메모리를 사용하고 있다고 보고할 것이다. 이것은 다른 메모리 주소의 쓰레드-스택때문에 생긴다. 예를 들면 솔라리스의 ps 는 스택사이의 사용하지 않는 메모리를 사용하는 메모리로 간주한다. 이것은 swap -s를 이용 사용가능한 스왑을 체크하여 확인할수 있다. 우리는 mysqld를 상용 메모리 유출 측정 프로그램으로 테스팅해서 mysqld에는 메모리 유출이 없다.

속도 향상에 영향을 미치는 컴파일/링크 방법 <컴파일시 최적화하기>

다음 테스트의 대부분은 리눅스와 mysql 벤치마크를 가지고 수행되었지만 다른 운영 시스템에도 암시해주는 것이 있다.

static으로 링크를 할 때 가장 빠른 실행 속도를 얻을 수 있다. 데이터베이스에 연결하기 위해 TCP/IP보다는 유닉스 소켓을 사용하면 더 좋은 성능을 낼 수 있다.

리눅스에서 pgcc와 -O6을 사용하면 가장 빠르다. 'sql_yacc.cc'를 이 옵션으로 컴파일 하려면 gcc/pgcc는 모든 성능을 내기 위해 많은 메모리가 필요하기 때문에 180M의 메모리가 필요하다. 또한 mysql을 설정할 때 libstdc++ 라이브러리를 포함하지 않기 위해 CXX=gcc라고 설정해야 한다.

- pgcc를 사용하고 모두다 -O6 옵션으로 컴파일하면 mysqld 서버는 gcc로 컴파일한 것보다 11% 빨라진다.
- 동적으로 링크하면 (-static을 사용하지 않고) 13% 느려진다.
If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower.
- 유닉스 소켓을 사용하는 것보다 tcp/ip로 연결하는 것이 7.5% 느려진다.
- On a Sun sparcstation 10, gcc 2.7.3 is 13% faster than Sun Pro C++ 4.2.
- On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads.

번역을 안한 이후. 리눅스랑 상관없으니깐...
TcX에서 제공한 mysql 리눅스 배포판은 pgcc로 컴파일 되었고 정적으로 링크 되었다.

MySQL의 인덱스 사용

모든 인덱스(PRIMARY, UNIQUE and INDEX()) 는 B-trees에 저장된다. 문자열은 자동적으로 앞 뒤의 공간이 압축된다.

인덱스의 사용 :
- WHERE 문에서 해당하는 레코드 빨리 찾기
- 조인을 수행할때 다른 테이블에서 레코드 가져오기
- 특정 키에서 MAX() 나 MIN() 값 찾기
- 소팅이나 그룹화 할 때 인덱스 키를 사용하면 테이블을 정열하거나 그룹화한다. 키에 DESC가 붙으면 역순으로 인 덱스를 읽는다.
- 어떤 경우에는 데이터 파일에 묻지 않고 값을 가져온다. 어떤 테이블에서 사용하는 모든컬럼이 숫자이고 특정 키로 형성되어있으면 빠른 속도로 인덱스 트리에서 값을 가져올 수있다.

다음 예제를 보자.

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

다중 컬럼 인덱스가 col1 과 col2에 있으면 해당하는 레코드를 직접 가져올 수 있다. 분리된 단일 컬럼 인덱스가 col1 과 col2 에 있으면 최적화기는 어떤 인덱스가 더 적은 레코드를 가졌는지 확인하고 레코드를 가져오기 위해 그 인덱스를 사용하도록 결정한다.

테이블이 다중 컬럼 인덱스를 가졌다면 최적화기가 레코드를 찾는데 어떤 인덱스키를 사용할 수 있다. 예를 들면 세가지 컬럼 인덱스(col1, col2, col3)를 가졌다면 (col1), (col1,col2)(col1,col2,col3) 인덱스를 사용하여 검색을 할 수 있다.

mysql> SELECT * FROM tbl_name WHERE col1=val1; mysql> SELECT * FROM tbl_name WHERE col2=val2; mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

인덱스가 (col1,col2,col3)로 있다면 위의 질의 중 오직 첫번째 질의만 인덱스를 사용한다. 두번째 및 세 번째 질의는 인덱스 된 컬럼이 포함되어 있지만 (col2) 와 (col2,col3)는 (col1,col2,col3) 인덱스에 해당하지 않는다.

mysql은 또한 LIKE의 인수가 와일드카드 문자로 시작하지 않는 상수 문자열이라면 LIKE 비교문에서 인덱스를 사용한다. 예를 들어 다음의 SELECT 문은 인덱스를 사용한다.

mysql> select * from tbl_name where key_col LIKE "Patrick%"; mysql> select * from tbl_name where key_col LIKE "Pat%_ck%";

첫번째 문장에서는 "Patrick" <= key_col < "Patricl" 을 가진 레코드만 고려된다. 두 번째 문장에서는 "Pat" <= key_col < "Pau" 을 가진 레코드만 고려된다.

다음의 SELECT 문은 인덱스를 사용하지 않는다:

mysql> select * from tbl_name where key_col LIKE "%Patrick%"; mysql> select * from tbl_name where key_col LIKE other_col;

첫번째 문장에서 LIKE 값은 와일드카드 문자로 시작하고 있다. 두 번째 문장에서는 LIKE값이 상수가 아니다.

WHERE 문에서 최적화하기

일반적으로 느린 SELECT ... WHERE 문을 빠르게 하려면 가장 먼저 확인해야 할 것이 인덱스 추가 문제이다. 다른 테이블사이에서 모든 레퍼런스는 일반적으로 인덱스에 의해 수행된다. SELECT 문에서 어떤 인덱스를 사용하는지 결정하기 위해 EXPLAIN 명령을 사용할 수 있다.

mysql에서 수행하는 최적화는 다음과 같다. 불필요한 삽입어 제거

((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b ANDc) OR (a AND b AND c AND d)

상수 폴딩(folding)

(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

상수 조건 제거(상수 폴딩때문에 필요)

(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6

인덱스에서 사용되는 상수 표현은 한번에 계산된다.

(Constant expressions used by indexes are evaluated only once.) WHERE 절이 없는 단일 테이블의 COUNT(*)는 테이블 정보에서 직접 값을 가져온다.단일 테이블에서 사용된 NOT NULL 표현도 이와 같이 수행된다.
유효하지 않은 상수 표현은 미리 제거된다. mysql은 불가능하고 해당하는 레코드가 없는SELECT 문을 빠르게 감지한다. GROUP BY 나 그룹 함수(COUNT(), MIN() ...)을 사용하지 않으면 HAVING은 WHERE에 합쳐진다.

HAVING 절에서는 인덱스를 사용하지 못함. 그러므로 가능한 HAVING절을 사용하지 않는 것이 속도면에서 좋다
각 서브 조인에서 빠르게 WHERE 문을 계산하고 가능한 한 레코드를 제외하도록 간소하게 WHERE 문이 만들어진다.

mysql은 일반적으로 최소한의 레코드를 찾기 위해 인덱스를 사용한다.

=, >, >=, <, <=, BETWEEN 그리고 'something%'처럼 앞이 와일드카드로 시작하지 않는 LIKE 문 등을 사용하여 비교를 할 때 인덱스를 사용한다. 10.4 절에서 설명하였듯이 like를 사용할 때와일드카드로 시작하는 like 문을 사용하면 인덱스를 사용하지 않는다. 일정한 단어로만 시작하는 컬럼에서 자료를 찾을 때 유용할 것이다.

다음의 WHERE 문은 인덱스를 사용한다.:

... WHERE index_part1=1 AND index_part2=2
... WHERE index=1 OR A=10 AND index=2 /* index = 1 OR index = 2 */
... WHERE index_part1='hello' AND index_part_3=5
/* optimized like "index_part1='hello'" */

다음의 WHERE 문은 인덱스를 사용하지 않는다.:

... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* No index */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */

- 질의에서 다른 테이블보다 모든 상수 테이블을 먼저 읽는다.

상수 테이블은 다음과 같다.

빈 테이블이나 1개의 레코드만 있는 테이블
WHERE 문에서 UNIQUE 인덱스나 PRIMARY KEY를 사용하고 모든 인덱스는 상수 표현으로 된 테이블

다음의 테이블은 상수 테이블로 사용된다.

mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id;

모든 가능성을 시도하여 테이블을 조인하는데 가장 좋은 조인 조합을 찾는다.

ORDER BY나 GROUP BY의 모든 컬럼이 동일한 테이블에서 나오면 조인을 할 때 이 테이블이 먼저선택된다
ORDER BY 문과 다른 GROUP BY 문이 있을 때, 또는 ORDER BY 나 GROUP BY가조인 큐의 첫번째 테이블이 아닌 다른 테이블의 컬럼을 포함하고 있으면 임시 테이블을 만든다.

각 테이블 인덱스를 찾고 레코드의 30%미만을 사용하는 (best) 인덱스가 사용된다. 그런인덱스가 없으면 빠른 테이블 검색이 사용된다.

어떤 경우에는 mysql은 데이터 파일을 조회하지 않고 인덱스에서 레코드를 읽을 수 있다. 인덱스에서 사용한 모든 컬럼이 숫자라면 질의를 처리하는데 단지 인덱스 트리 만을 사용한다.

각 레코드가 출력되기 전에 HAVING 절에 맞지 않는 레코드는 건너뛴다.

다음은 매우 빠른 질의의 예이다

mysql> SELECT COUNT(*) FROM tbl_name;
mysql> SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;
mysql> SELECT MAX(key_part2) FROM tbl_name
WHERE key_part_1=constant;
mysql> SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10;
mysql> SELECT ... FROM tbl_name
ORDER BY key_part1 DESC,key_part2 DESC,... LIMIT 10;

다음의 질의는 인덱스 트리만을 사용하여 값을 구한다.(인덱스 컬럼은 숫자라고 가정):

mysql> SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; mysql> SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 and key_part2=val2; mysql> SELECT key_part2 FROM tbl_name GROUP BY key_part1;

다음의 질의는 개별적인 정열을 하지 않고 정렬된 순서대로 열을 가져오는 데 인덱스를 사용한다:

mysql> SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... mysql> SELECT ... FROM tbl_name ORDER BY key_part1 DESC,key_part2 DESC,...