1. 가능하면 지속 연결을 사용하거나 연결 풀을 사용하라

데이터베이스에 연결하거나 연결을 해제할 때에도 오버헤드가 발생한다. 연결 및 연결해제 개념은 8장에서 주로 다루고, 각 언어(펄, 파이썬, C, 자바/JDBC)별 연결 및 연결 해제방법은 해당 언어에 대한 장에서 별도로 다룬다. 일단 기본적인 아이디어는 연결과 연결 끊기 회수를 줄여보자는 것이다. 특히 페이지에 대한 요청이 들어올 때마다 CGI나 PHP 스크립트에서 그 페이지와 관련된 정보를 불러오기 위해 데이터베이스에 연결해야 하는 웹 애플리케이션에서는 이러한 문제가 매우 중요하다. 지속 연결(persistent connection)이나 연결 풀(connection pool)을 사용하면 연결 및 연결해제 과정에서 생기는 오버헤드를 줄여서 애플리케이션의 성능을 향상시킬 수 있다. 하지만 연결 풀이나 지속 연결을 너무 많이 사용하면 MySQL 서버에서 시스템 자원을 너무 많이 잡아 먹기 때문에 다른 문제를 일으킬 수도 있다.

연결 풀과 지속연결은 각 언어에 따라 다른 방식으로 처리한다. 예를 들어 PHP에서는 mysql_pconnect() 함수로 지속 연결을 할 수 있다

2. 인덱스를 사용하라

데이터베이스에 있는 데이터는 디스크에 저정된다. 데이터를 읽고 갱신하는 작업은 결국 디스크 입/출력 작업이다. SQL 질의 튜닝의 목적은 입/출력 회수를 최소화하는 것이다. 질의를 튜닝할 때 쓰이는 주무기는 바로 인덱스이다.

3. EXPLAIN 을 사용하여 index를 만들자.

캘리포니아주(주를 나타내는 약어가 CA)의 이름을 구할 때에는 다음과 같이 하면 된다.

SELECT state_name FROM State WHERE state_cd = 'CA';

EXPLAIN SELECT를 실행시킨면 질의가 어떻게 실행되는지 알 수 있다:

 mysql> EXPLAIN SELECT state_name FROM State where state_cd = 'CA';

 +--------------------------------------------------------------------------+
 | table | type | possible_keys | Key  | key_len | ref  | rows | Extra      |
 +--------------------------------------------------------------------------+
 | state | ALL  | NULL          | NULL | NULL    | NULL | 50   | where used |
 +--------------------------------------------------------------------------+
 1 row in set (0.00 sec)

WHERE 절에서 state_cd를 사용하므로 그 열에 대해 인덱스를 만들고 EXPLAIN_SELECT를 다시 실행시켜보자:

 mysql> CREATE INDEX st_idx ON State (state_cd);
 .
 .
 mysql> EXPLAIN SELECT state_name FROM State WHERE state_cd = 'CA';

 +-----------------------------------------------------------------------------+
 | table | type | possible_keys | Key    | key_len | ref   | rows | Extra      |
 +-----------------------------------------------------------------------------+
 | state | ALL  | st_idx        | st_idx | 2       | const | 1    | where used |
 +-----------------------------------------------------------------------------+

 1 row in set (0.00 sec)

key 열을 보면 MySQL에서 새로 만든 인덱스를 사용하고 있다는 것을 알 수 있다. 결과적으로 처리해야 할 행의 개수가 50개에서 한 개로 줄어들었다.

4. mysql의 변수를 조절하라

일반적으로 MySQL을 튜닝할 때에는 다음 두 변수가 가장 중요하다.

table_cache

table_cache는 MySQL 테이블 캐시의 크기를 조절하는 변수이다. 이 값을 키우면 매번 파일을 열거나 닫을 필요 없이 동시에 더 많은 테이블을 열어두 수 있다.

key_buffer_size

key_buffer_size는 인덱스를 담아두는 버퍼의 크기를 조절하는 변수다. 이 값을 키우면 인덱스 생성 및 수정 성능이 향상되고, 더 많은 인덱스 값을 메모리에 저장할 수 있다.

5. 운영체제/하드웨어 튜닝

하드웨어 및 OS 튜닝에 대한 자세한 내용은 이 책의 범위를 벗어난다. 하지만 일반적으로 다음과 같은 점을 고려해야 한다.

일반적인 하드웨어 업그레이드를 통해 MySQL의 성능을 향상시킬 수 있다. 예를 들어, 시스템에 메모리를 추가하면 MySQL 캐시 및 버퍼에 더 많은 메모리를 할당할 수 있다. 더 빠른 디스크를 사용하면 I/O 속도로 빨라진다.
테이터베이스를 여러 개의 물리적 기기에 분산시키는 것도 도움이 된다. 예를 들어, 테이터 파일과 인덱스 파일을 서로 다른 디스크에 저장하면 성능을 향상시킬 수 있다.
정적 이진 파일이 동적 이진 파일보다 빠르다. MySQL을 만들때 동적으로 링크하지 않고 정적으로 링크하도록 설정하면 속도를 향상시킬 수 있다.

ㅇ 연결 취소 횟수가 많은 경우 네트워크에 문제가 있을 가능성이 있으므로 이에 대한 점검이 필요하다.

ㅇ Key_reads가 큰 경우는 인덱스를 메모리가 아닌 디스크에서 계속 불러오고 있는 상황이므로 시스템의 성능에 크게 문제가 될 수 있고 이 경우에는 key_buffer_size가 너무 작은 경우 발생할 수 있다. Key_reads/Key_read_request 를 계산하면 캐시 히트율을 계산할 수 있으며 일반적으로는 이 값이 0.01보다는 작아야한다. 그렇다고 key_buffer_size를 너무 크게 잡으면 메모리가 부족하여 페이징을 하게되고 시스템이 느려질 수 있다.

ㅇ Max_used_connections를 이용하여 실제 동시 접속자수를 판단하여 동시 접속 가능한 사용자를 조정한다. 이경우 동시접속자수를 무한정 늘리는 것이 아니라 시스템의 자원을 고려해야 한다.

ㅇ Opened_tables 가 클 경우에는 table_cache의 값이 적은 경우가 원인일 수 있으므로 table_cached를 늘려주어야한다.

ㅇ Select_full_join는 인덱스를 사용하지 않은 조인이므로 최대한 0에 가깝도록 조정해 주어야 한다. 조인에서 인덱스를 사용하고 있지 않다면 테이블 디자인을 변경하여 인덱스를 사용할 수 있도록 조정해주어야 한다.

ㅇ Slow_queries는 long_query_time에 지정한 시간보다 더 오래 걸리는 질의의 숫자이므로 질의문을 최적화해야 할 필요성이 있다. 이에 해당하는 로그만 따로 남길 수가 있으므로 해당하는 질의가 어떤 것인지 확인할 수 있다.

ㅇ Questions와 Uptime을 이용하여 초당 질의 횟수를 산출할 수 있다.

ㅇ Table_locks_waited가 매우 큰 경우에는 테이블 락 때문에 기다리는 시간이 많다는 것이고 시스템의 성능에 큰 영향을 미칠 것이다. 먼저 질의를 최적화하고 테이블을 여러 개로 나누거나 replication 기능을 이용하여 작업을 분산시킬 필요가 있다.

이와 관련하여 메모리 및 서비스 규모에 따라 mysql 배포 프로그램에 my.cnf 예제 파일이 들어있다. (my-huge.cnf  my-large.cnf  my-medium.cnf  my-small.cnf 등이다)

주로 시스템의 메모리와 연관을 지어 메모리 크기에 따라 기본설정을 하고 있으므로 각자 자신의 시스템에서 이를 기본으로 수정해나가면 된다. diff 등으로 비교를 해보면 알겠지만 성능과 관련이 있는 변수는 key_buffer, table_cache, sort_buffer, record_buffer 등이다.

이외에도 현재 서버에서 실행되고 있는 스레드에 대한 모니터링을 할 수 있다. –i 옵션을 이이용하면 같은 명령을 주기적으로 갱신하면서 확인할 수 있다. –r 옵션을 이용하면 정보를 갱신하였을 경우 차이점만을 보여준다.