[MYSQL] my.cnf 의 설정 변경을 통한 튜닝

1.my.cnf 설정

+ key_buffer_size : 성능에 많은 영향을 주는 설정이다. 키버퍼는 인덱스블락에서 사용하는 버퍼의 크기이며 인덱스가 많은 테이블에서 insert, delete? 작업이 많다면 증가해 주는 것이 좋다.
+ max_connections : 동시 접속자수이다. 적당량으로 사용하는 것이 좋다. 너무 과하면 좋지 않다.
+ table_cache : 모든 쓰레드에서 열 수 있는 테이블 숫자이다.
+ join_buffer_size : full-join에서 사용하는 버퍼의 크기아다. full-join을 많이 사용하는 경우 늘려주는 것이 좋다. (왠만하면 인덱스를 사용한 조인을 하는 것이 속도가 빠르다)
+ max_join_size : 최대 조인의 크기이다. 크기 값보다 큰 조인인 경우는 에러가 난다.
+ record_buffer : 순차적인 검색을 하는 쓰레드에 할당하는 버퍼 크기로 순차적인 검색을 많이 하는 경우 증가시키는 것이 좋다
+ sort_buffer : 정렬시 각 쓰레드에 할당하는 버퍼크기. order by, group by를 빠르게 하려면 증가시키는 것이 좋다.
+ thread_cache_size : 이미 사용한 쿼리를 기억해 놓는 캐시의 할당 크기. 많이 할당 할 수록 좋으나 다른 설정을 위해 적당량으로 설정하는 것이 좋다. 같은 쿼리를 반복적으로 많이 하는 경우 크게 하는 것이 좋다.
+ tmp_table_size : 메모리 기반의 임시테이블을 디스크 기반의 임시테이블로 변환할 때의 크기. 복작한 쿼리를 실행할 경우 변환되는 경우가 많으며 여유있게 설정하는 것이 좋다. 단 디스크 용량을 생각해서..
+ wait_timeout : 연결을 끊기전 대기하는 시간

물론 이러한 부분들은 언제든지 유동성이 있게 설정하는 것이 중요하다.
처음 예측했던 것과는 다르게 mysql을 사용할 수 있으므로 지속적으로 모니터링하여 설정값을 변경해주어야 한다.

2). mysqladmin 을 이용한 상태 모니터링

 

[root@host ] mysqladmin -u root -p extended-status

이 명령어로 mysql의 사용량을 알수가 있다.

이렇게 알아낸 사용량으로 my.cnf를 변경하여야 하며 주기적으로 확인하여 설정 값에 문제가 있는지 확인하는 것이 좋다.

역시 중요한 부분들만 살펴 보겠다.

+ Aborted_clients : 클라이언트에서 연결이 적절하게 닫지 못해서 죽어서 취소된 연결 횟수이다. 이 값이 많다면 네트워크 연결에 문제가 있을 가능성이 있다. 네트웍을 확인해 보는 것이 좋으나 경우에 따라서 프로그래밍시 연결을 적절하게 끊어주지 않아 많이 발생하는 경우도 있으므로 크게 신경 쓸 필요는 없다.
+ Aborted_connects : 연결을 시도해서 실패한 횟수로 최대 동시접속자 수나 네트웍을 확인해보아야 한다.
+ Connections : 연결을 시도한 횟수로 사용령을 알 수 있다.
+ Key_reads : 디스크에서 물리적으로 키 블락을 읽어들인 횟수로 이 값이 크면 key_buffer_size이 너무 작게 설정되어 있는 경우가 대부분이며 이 값이 크면 성능에 큰 문제가 될 수 있으므로 key_buffer_size를 크게 설정해야 하며 시스템의 성능이 늘릴 수 없을 경우에는 시스템을 업그레이드 하는 것이 좋다. key_buffer_size이 시스템의 성능에 비해 너무 크게 잡을 경우 계속적인 페이징을 하게되어 더 느려질 수 있으므로 잘 고려해야 한다.
+ Key_reads, Key_read_request : Key_reads/Key_read_request로 계산하면 캐시 히트률을 알 수 있다. 0.01보다는 작아야 하며 무조건 작을 수록 좋다. 0.01보다 클 경우 key_buffer_size를 조정한다.
+ Max_used_connections : 최대 동시 접속자수이며 이 값을 보고 max_connections의 값을 조정한다.
+ Opened_tables : 열었던 테이블 수로 이 값이 너무 클 경우 table_cache의 값이 작은 경우일 수 있으므로 table_cache의 값을 늘려 주어야 한다.
+ Select_full_join : 인덱스를 사용하지 않은 조인으로 최대한 0에 가깝게 해주는 것이 좋으며 값이 너무 클 경우 테이블의 인덱스를 재설정해주거나 쿼리문은 수정해야 한다.
+ Questions : 서버로 보낸 질의 횟수로 이 값으로 앞으로의 사용량을 예측해보아 앞으로의 업그레으드의 계획을 세우는 것이 좋다. Questions와 Uptime을 이용하여 초당 질의 수를 알아볼 수 있다.
+ Table_locks_waited : 테이블 락을 위해 대기한 시간으로 최대한 작아야 한다. 설정 값만으로 줄일 수는 없으며 너무 클 경우 데이타베이스의 설계나 쿼리문에 문제가 있을 수 있으며 속도저하의 원인이 대부분 여기서 찾을 수 있다. 이부분은 뒤에 따로 다루어 보겠다.

my.cnf의 설정은 메모리와의 연관이 많다. 충분한 메모리가 있을 경우 다양한 설정을 할 수 있다.
변수는 key_buffer, table_cache, sort_buffer, record_buffer 의 값이 성능에 가장 큰 영향을 주나 무조건 크다고 성능이 좋아지는 것은 아니다.