Mysql 효과적으로 사용하기 [1]

회사에서 mysql로 과도한 DB를 돌리면서 데이타베이스의 튜닝에 대해 관심을 가지게 되었다.
오라클 같은 경우는 대용량 데이타베이스로서의 활용능력이 좋아 대용량의 데이타를 돌려도 mysql에 비해서 좋은 성능을 낸다.

물론 mysql이 뛰어난 데이타베이스라는 것은 인정하나 아직은 대용량의 DB를 돌리기에는 무리가 있다.

오랜만에 강좌를 쓰면서 mysql을 어떻게 하면 효과적으로 대용량 DB로 활용할 수 있는 지를 이야기 해보겠다.

mysql의 튜닝에 관해서는 데이타베이스 사랑넷에서 많은 도움을 받았음을 미리 밝힌다.
사실 여기서 이야기하는 것은 단순한 설정을 통한 튜닝이며 보다 난이도 높은 튜닝은 컴파일 전 소스를 직접 만져야지만 가능한 부분이 있다.
그 부분에 대해서도 다루어 볼 생각은 있으나 아직은 그정도의 실력이 안되므로 그냥 넘어가고 후일을 기약해보겠다.

튜닝에 관현한 부분은 mysql의 공식 메뉴얼에도 자세하게 설명은 되어 있다.

10GB가 넘는 데이타를 돌리면서 쌓은 노하우를 공개해 보겠다...

그럼 서론은 이쯤에서 하고 본격적인 mysql을 이용하여 대용량의 데이타 베이스를 다루어 보겠다.

1> MYSQL 튜닝(?)

a. 설치 과정에서의 튜닝

mysql 메뉴얼에서는 컴파일 및 링크시에 정적(Static)로 하는 것이 속도를 더 빠르게 할 수 있으며 gcc보다 pgcc를 이용하여 컴파일 하는 것이 더 빠르며 TCP/IP보다 소켓을 이용하는 경우가 더 빠르다고 설명하고 있다.

메뉴얼에 적혀 있는 Full Configure Option은 다음과 같다.

CFLAGS="-O3 -mpentiumpro" CXX=gcc CXXFLAGS="-O3 -mpentiumpro 
-felide-constructors -fno-exceptions -fno-rtti" ./configure 
--prefix=/usr/local/mysql --enable-assembler 
--with-mysqld-ldflags=-all-static

그래로 사용해도 무방하며 제가 사용하는 방법은 다음과 같습니다.

CC="/usr/local/pgcc/bin/pgcc" CFLAGS="-O6 -mpentiumpro 
-mstack-align-double -fomit-frame-pointer -march=pentiumpro" 
CXX="/usr/local/pgcc/bin/pgcc" CXXFLAGS="-O6 -mpentiumpro -march=pentiumpro 
-mstack-align-double -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" LDFLAGS="-static" 
./configure --prefix=/usr/local/mysql --enable-assembler 
--localstatedir=/val/mysql 
--with-charset=euc_kr 
--with-extra-charsets=none 
--with-mysqld-user=mysql 
--sysconfdir=/etc 
--with-innodb 
--enable-thread-safe-client 
--with-mysqld-ldflags=-all-static 
--with-client-ldflags=-all-static

! pgcc란 Pentium용으로 최적화된 gcc다. 
http://goof.com/pcg/binaries-linux.html 에서 바이너리를 받을 수 있다.

pgcc는 레드햇의 경우 기본으로 포함된 패키지가 아니므로 설치해야만 한다.

처음 해보는 사람이라면 좀 헤멜 수도 있지만 그다지 어렵지는 않으니 필요한 부분을 잘 선택해서 사용하면 된다.

컴파일이 힘들다면 mysql에서 배포하는 바이너리를 이용하는 것도 좋은 방법이다.
mysql에서 배포하는 바이너리는 모두 pgcc로 컴파일되었으며 최적화된 configure를 사용했다고 한다.

물론 컴파일하는 것이 속도는 더 빠르다.

! pgcc를 이용한 컴파일은 mysql에만 한정된 것이 아니다. php, apache 등에도 pgcc를 이용하여 컴파일 하면 속도 향상의 효과를 볼 수 있다.

위의 방법의 컴파일만으로는 눈에띄는 속도의 향상을 느낄 수는 없었다..
물론 수치상으로는 빨라진 것은 분명하였다. 

b. my.cnf 의 설정 변경을 통한 튜닝

my.cnf 파일의 설정을 변경하므로서의 튜닝은 상황에 따라서는 매우 좋은 방법이라는 생각이 든다.
그런데 새로운 시스템에서는 어떻게 설정해야 할까 고민되는 부분이기도 하다.

개인적으로는 처음에는 mysql에서 기본으로 제공하는 my-huge.cnf, my-large.cnf, my-medium.cnf, my-small.cnf 을 사용하는 것을 추천한다.

물론 사용상황에 따른 변경은 해줘야 겠지만 말이다.

현재 mysql이 돌아가고 있다는 것을 가정하에 설명을 해보겠다.

#mysqladmin variables

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을 사용할 수 있으므로 지속적으로 모니터링하여 설정값을 변경해주어야 한다.

#mysqladmin 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 의 값이 성능에 가장 큰 영향을 주나 무조건 크다고 성능이 좋아지는 것은 아니다.
시스템에 맞게 설정하는 것이 중요하다.

[출처] 아낌없이 주는 사랑 | 마게님 (http://comager.blog.me/60040577018)