Mysql 튜닝 - 서버 모니터링

리눅스 시스템 최적화 튜닝(4)

응용프로그램에서의 최적화 – DB와 Mysql

http://tunelinux.pe.kr http://database.sarang.net

지금까지는 시스템의 운영과 관리를 하기 위한 모니터링 방법 및 최적화에 대하여 소개를 하였다. 우리가 서버를 운영하는 것은 최종적으로 다른 사용자에게 특정한 서비스를 제공하기 위한 것이다. 여기에서 서비스라고 한다면 웹뿐만 아니라 DNS, FTP, MAIL, DB 등 여러가지가 있을 것이다. 이번 강좌에서는 현재 리눅스에서 많이 사용되고 있는 공개형 데이터베이스인 Mysql을 중심으로 최적화에 대한 설명을 하겠다.

리눅스 시스템 모니터링과 최적화

2001/8 안정된 시스템 관리를 위한 시스템 모니터링 툴 제대로 쓰기

2001/9 네트워크 모니터링 툴 익히기

2001/10 운영체제와 커널 차원에서의 튜닝과 보안

2001/11 응용프로그램에서의 최적화 – DB와 Mysql

2001/12 시스템 모니터링 자동화

0. Mysql에 대하여

Mysql은 1979년 Mysql 개발 회사인 TcX에서 데이터베이스 프로그램으로 처음 사용했다. 초반에는 1내부적으로 사용하기 위한 용도였으나 이후 기능을 보강하면서 공개적으로 사용할 수 있게 되었다. 1996년 5월 Mysql 1.0 버전이 발표되었고 1996년 10월 Mysql 3.11.1 버전이 솔라리스 플랫폼의 배포판으로 일반인에게 공개되었다. 한달후에 리눅스 바이너리와 소스 배포판이 공개되었다.

국내에서는 90년대 중반까지 리눅스나 FreeBSD 등 공개운영체제를 사용했던 사람들의 경우 주로 msql이나 PostgreSQL을 사용했다. 필자 또한 msql을 이용하여 웹과 DB연동을 공부했었고 이후에는 주로 PostgreSQL을 사용했다. 그러다가 90년대 후반에 들면서 인터넷의 발전과 함께 Mysql 사용자가 대폭 늘어나게 되었다. 필자가 무식(!)하게 vi로 Mysql 영문 매뉴얼을 일부 번역했던 것도 99년초였다. 그러나 예전의 리눅스와 마찬가지로 폭넓은 사용자에 비하면 관련 한글 자료와 서적은 현재도 매우 부족한 상태이다. 위안점이라면 인터넷의 다양한 사이트를 통하여 활발하게 정보가 오고가고 있는 중이지만 주로 질문답 형태로 좀더 많은 사용자가 참여하여 다양한 자료를 만들 필요성이 있다.

일반적으로 최근에 리눅스 등을 통해 데이터베이스를 접하는 경우 Mysql을 많이 사용한다. 그런데 Mysql은 웹에서 빠른 속도와 성능으로 사용자가 급격하게 늘어나고 있지만 일반적인 관계형 DB에서 지원하는 여러가지 기능이 빠져있다. 이에 해당하는 것이 sub-select, 트랜잭션, 스토어드 프로시저와 트리거, 뷰, 외래키 등이다. 물론 최근 버전의 Mysql에서는 트랜잭션을 지원하지만 이는 기본으로 지원하는 것은 아니며 필요한 사람이 직접 설정해서 사용을 해야 한다. 위의 기능들이 빠져있다고 해서 데이터베이스로서 문제가 있다기보다는 해당 데이터베이스의 특성에 맞는 서비스에 적절히 사용을 하면 된다. 가장 많은 질문을 하는 부분이 트랜잭션 부분인데 Mysql 개발자의 경우는 처음 설계시 트랜잭션보다는 속도에 대한 필요성이 더 컸기 때문에 트랜잭션 부분을 제외하였다. 이는 대부분의 애플리케이션이 트랜잭션 없이도 정상적인 동작이 가능하기 때문이다. 그대신 업데이트 로그 등을 통하여 보완 작업을 하고 있다. 트리거의 경우도 이와 비슷하다. 트리거는 특별한 이벤트가 발생하였을 때 실행되는 스토어드 프로시져로 보면 된다. 예를 들어 트랜잭션 테이블에서 레코드가 삭제되고 모든 트랜잭션이 지워질 때 상응하는 테이블을 삭제할 수 있는 스토어드 프로시저를 사용할 수 있다. 그러나 이렇게 되면 필요하지 않은 경우에도 트리거가 사용이 될 수 있어서 일반적으로 속도가 느려진다. Mysql은 이렇게 속도에 영향을 미칠 수 있는 부분을 제거하여 빠른 속도를 내고 있다. 그러므로 자신의 서비스에서 사용하려는 데이터베이스의 특성이 얼마나 중요한가라는 판단을 해야한다. 참고로 소형 DBMS에서는 회복과 병행수행을 지원하는 않는 경우가 많다. 즉 병행수행은 발생하지 않으며, 회복은 사용자의 문제로 생각한다. 그러므로 사용자가 데이터베이스의 예비 사본을 준비하고 고장이 발생하면 작업을 다시 해야 한다. 트리거 같은 경우는 자료의 무결성 보장을 위해 필요한 것이다.

여기서 주의할 점은 위에서 말을 한대로 최근 리눅스를 통하여 데이터베이스를 처음 사용하는 경우 Mysql을 많이 사용하지만 이 때문에 데이터베이스가 가진 여러가지 기능들을 좁게 보지는 말라는 것이다. 실제 데이터베이스를 운영하는 경우 트랜잭션 처리부분도 매우 중요한 부분이며 현재 사용하지는 않는다고 하더라도 꼭 알고 있어야 하는 부분이며 스토어드 프로시져, 트리거, 뷰 등도 일반적으로 다른 데이터베이스에서는 많이 사용되고 있고 유용한 기능들이다. Mysql의 경우 데이터베이스를 처음 사용하는 사람에게는 설치와 관리가 다른 데이터베이스에 비하여 쉬운 편이지만 거꾸로 다른 데이터베이스에서 지원하는 여러가지 개념이나 기능을 맛보지 못한다는 단점이 있다. 그런 면에서 국내에서 가장 많이 사용되고 있는 오라클을 포함하여 인포믹스, 사이베이스, IBM의 DBII 등의 상용 데이터베이스도 모두 리눅스에서 지원되고 있으므로 이에 대한 운영경험을 해보는 것도 필요하며 완전한 공개형 DBMS인 PostgreSQL도 관심을 가져보면 좋을 것이다. PostgreSQL의 경우 객체지향 기능을 가지고 있는 관계형 데이터베이스 시스템으로 관계형 모델, 고수준 확장성, 객체지향 등의 특징을 가지고 있고 공개 DBMS로는 드물게 트랜잭션을 이전부터 지원해왔다.

그림 1 MySQL 사이트

2. 데이터베이스 튜닝

Mysql에 대한 설명에 들어가기 전에 일반적인 데이터베이스 튜닝에 대한 설명을 먼저 한다. 여기서는 오라클의 튜닝 문서 내용을 중심으로 설명하겠다. 비록 DB는 다르지만 튜닝 전반에 대한 이해를 하는 데에는 도움이 될 것이고 이러한 내용을 참고로 하여 Mysql에 적용을 할 수 있다.

ㅇ 튜닝시기와 중요성

데이터베이스 튜닝은 불필요한 일을 최소화하고 보다 적은 비용으로 더 많은 결과를 내고자 하는 활동을 말한다. 그런데 튜닝을 응용 프로그램을 개발 완료하고 실제 서비스 이전에 성능 시험 단계에서 진행하는 것으로 판단을 할 경우 문제가 생길 여지가 많다. 실제 개발을 모두 완료하고 나서는 어떤 문제가 생긴다고 하더라도 그 원인을 찾기가 매우 어렵고 시스템에서 병목 현상이 생겨도 그러한 문제를 해결하기 위해서는 더 많은 비용과 작업이 들어간다. 데이터베이스 튜닝은 프로젝트를 진행하는 처음부터 마지막 단계까지 계속 진행해야 할 부분이다. Design, Development, Production 으로 프로젝트 단계를 나눌 경우 디자인 단계부터 튜닝에 대한 투자를 해야 그 효과가 크다. 그림 1과 그림 2는 Oracle8i Tuning Release 문서에서 제시하고 있는 그래프로 튜닝을 언제 해야지 그 효과를 볼 수 있고 비용을 절약할 수 있는지 보여준다.

그림 2 애플리케이션 개발 기간 중의 튜닝 비용

그림 3 애플리케이션 개발 기간 중의 튜닝 효과

그런데 짧은 프로그램 개발 기간때문에 데이터베이스의 속도나 효율성 등을 고려하지 못한채 개발이 진행되는 경우가 많은 것이 현실이다. 개발 자체만도 벅찬데 데이터베이스에 있는 여러가지 기능은 커녕 기본적인 SQL만 익히고 개발에 들어가는 경우도 많다. 이러한 경우 문제가 발생하고 나서 그러한 문제들을 해결하는데 많은 시간을 투여하게 되고 어떤 경우에는 처음부터 데이터베이스 설계를 다시 들어가는 경우도 생긴다. 굳이 규모가 있는 데이터베이스가 아니라 간단하게 며칠정도에 짤 수 있는 프로그램도 작성하고 나서 비효율성 때문에 다시 뜯어고치는 경우가 많은데 하물며 서비스의 규모가 커질수록 데이터베이스 설계의 중요성은 커지고 있다.

튜닝의 경우도 크게 보면 DB관리자, 응용 프로그램 개발자, 하드웨어의 관리자등으로 나눌 수 있는데 각자 맡은 분야에서 협력하여 진행하여야 한다. DB관리자의 경우 DB 소프트웨어의 효율을 극대화해야 할 책임이 있고 응용 프로그램 개발자는 SQL을 튜닝하고 응용프로그램에서 시스템 리소스를 적절히 활용할 수 있도록 프로그램을 개발해야 하며 하드웨어 관리자의 경우 하드웨어를 효율적으로 운영할 수 있도록 튜닝 작업을 진행해야한다. 추가적으로 더 세분화한다면 업무규칙을 튜닝하고 전체 시스템 구성을 책임질 응응 프로그램 설계자, 효율적인 데이터베이스 스키마 디자인을 할 데이터베이스 설계자 등으로 나눌 수 있을 것이다.

ㅇ 튜닝 절차

오라클에서는 튜닝 절차를 아래와 같이 10단계로 보고 있다. 먼저 나오는 단계가 그만큼 더 중요하다고 볼 수 있다. 각 단계는 그 이후 단계에 영향을 미친다. 예를 들어 5번째 단계에서 SQL문을 재작성하는 경우 7번째 단계인 메모리 할당에 영향을 미친다. 오라클에서는 공유 영역에 이전에 사용된 같은 SQL문장이 있는지 확인하고 없으면 다시 컴파일, 권한 검사, 문법 오류 등을 검사해서 공유 영역에 올려놓는다. 그러므로 공유 영역에 있는 SQL문장은 위와 같은 과정을 거칠 필요가 없으므로 성능 향상에 도움이 되는 것이다. 디스크 I/O의 경우 8번째 단계에서 튜닝 작업을 진행하는데 디스크 I/O는 버퍼 캐쉬의 크기와 연관이 있기 때문에 7번째 메모리 할당 단계와 연관을 가진다.

첫번째 단계는 사용자의 요구를 분석하고 업무를 튜닝하는 단계인데 이는 기술적인 문제만은 아니며 사용자의 요구에 무조건 응용 프로그램을 마치는 것이 아니라 효율적인 업무 처리를 위하여 업무 처리 과정 자체를 개선해야 할 필요성도 고려를 해야 한다. 예를 들어 특정한 데이터에 접근하는 사용자의 경우 단순하게 조회만 필요한 사람이 있으며 이를 수정, 삭제 등 조작을 해야 할 사람이 있을 것인데 이러한 경우에는 업무를 조정하고 사용자마다 다른 권한을 주도록 업무를 변경하면 불필요한 정보접근을 막고 수행 속도도 빠르게 할 수 있다.

그림 4 튜닝 절차

이외에도 구체적인 튜닝 적용 방법, 애플리케이션의 형태(OLTP, Data Warehousing, 클라이언트/서버 등)에 따른 튜닝 방법 등을 고려해야 하는데 이에 대해서는 별도의 서적을 참고하기 바란다. 위에서 보았듯이 데이터베이스 튜닝이 단순한게 몇 개의 서버 파라미터 수정이나 SQL문 수정만으로 이루어지는 것은 아니며 여러가지 요소를 두루 고려해야 한다는 것이다.

3. Mysql 튜닝 개요

이 글에서 전체적인 Mysql 튜닝을 설명하기는 힘들며 큰 틀에서 주로 살펴보아야 할 부분들을 지적하겠다.

먼저 시스템 튜닝을 위해서 가장 중요한 부분 중 하나는 기본적인 시스템 설계이다. 일반적으로 주요 병목 지점이 되는 것은 디스크 탐색시간, 디스크 읽기/쓰기, CPU 사이클, 메모리 등으로 볼 수 있다. 자신이 어떤 서비스를 할 것인지 먼저 고려를 하고 현재의 시스템에서 병목 지점이 될 부분들을 미리 파악해야 한다.

성능 튜닝은 SQL/DB구조 설계/서버 세팅으로 나누어 볼 수 있다.

ㅇ SQL 튜닝 : EXPLAIN 과 select 명령을 이용하여 sql문이 어떻게 실행되는지 인덱스가 사용되는지 join은 어떤 식으로 구현되는지 등에 대한 정보를 알 수 있다. 질의문의 성능은 일반적으로 디스크 검색 횟수에 따라 측정이 가능하다. myisamchk 등의 프로그램을 이용하여 DB의 상태를 분석하고 인덱스에 따라 정렬을 하는 등의 최적화작업을 수행할 수 있다. Where, distinct, join, limit 등의 사용방법에 따라 최적화의 방법이 있으며 INSERT, UPDATE, DELETE 등의 질의를 수행하는 경우에도 효율적인 방법을 사용해야 한다. 예를 들어 텍스트 파일의 데이터를 입력할 경우 INSERT문을 사용하는 것보다는 LOAD DATA INFILE 명령을 이용하는 것이 훨씬 속도가 빠르며 여러 개의 레코드를 입력할 경우에는 개별적으로 입력을 하는 것보다 하나의 INSERT 문을 이용하는 것이 빠르다.

mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);

여기서 속도 차이가 생기는 것은 모든 INSERT 문이 완료되고 난 후에 한번에 인덱스 버퍼에 기록을 하기 때문이다.

ㅇ DB 구조 설계 : mysql은 데이터와 인덱스 파일을 별도로 두는 구조로 되어 있다. DB 구조를 설계하는 경우 가급적 데이터를 작게 만드는 것이 중요하다. 이는 적절한 칼럼 타입 선택, not null 사용, ENUM 사용등이 포함된다. 인덱스의 경우 DB의 성능에 많은 영향을 미치는데 무조건 인덱스를 쓴다고 성능이 빨라지는 것은 아니며 서비스의 특성에 따라 인덱스를 사용해야한다. 또한 인덱스를 사용한다고 해도 적절하게 SQL문을 작성하지 않으면 실제로는 사용을 하지 못하는 경우가 생길 수 있다. 인덱스 사용은 EXPLAIN 명령으로 확인을 할 수 있다. 서버관련 변수중 table_cache, max_connections 는 서버가 열 수 있는 최대 파일 개수와 연관을 가진다. Max_connections는 서버에 동시 접속할수 이는 클라이언트의 최대수를 지정하고 table_cache는 모든 스레드에서 열수 있는 테이블 숫자를 지정하므로 테이블과 연관된 파일 기술자와 관련이 있기 때문이다. 서버에서 열어야 할 테이블 개수를 미리 예측하여 수치를 조정하는 작업이 필요하다.

ㅇ 서버 세팅 : 주로 시스템 및 Mysql 서버와 관련한 세팅 부분이다. 현재 Mysql에서 다중 CPU기능을 잘 활용할 수 있는 운영체제로는 솔라리스와 리눅스를 추천하고 있다. 리눅스에서 2G 파일 한계는 커널 2.4에 들어오면서부터 제약이 없어졌다. Mysql 서버 세팅 관련한 변수는 따로 설명하겠다. 컴파일 및 링크시에는 정적(static)으로 링크하는 것이 더 빠른 속도를 낼 수 있으며 gcc보다는 pgcc를 사용하는 것이 더 효율적이다. TCP/IP 대신 유닉스 소켓을 사용하는 경우에 7.5%정도의 속도 향상 효과가 있다. 이외에 mysql에서 메모리와 DNS를 사용하는 방법에 대해 이해하고 있어야하고 mysql 서버와 관련한 세팅은 set 명령을 이용하여 조정할 수 있다. 기타 서버와 관련한 세팅은 심볼릭 링크, RAID 사용등과도 연관이 있다.

4. Mysql 서버 변수

mysql에서 mysqld 서버와 관련한 기본 버퍼 크기는 아래의 명령을 이용해 확인할 수 있다.

리스트 1. mysql 서버 변수 보기

$ mysqld –help

Possible variables for option --set-variable (-O) are:

back_log current value: 50

binlog_cache_size current value: 32768

connect_timeout current value: 5

delayed_insert_timeout current value: 300

delayed_insert_limit current value: 100

delayed_queue_size current value: 1000

flush_time current value: 0

interactive_timeout current value: 28800

join_buffer_size current value: 131072

key_buffer_size current value: 8388600

long_query_time current value: 10

lower_case_table_names current value: 0

max_allowed_packet current value: 1048576

max_binlog_cache_size current value: 4294967295

max_binlog_size current value: 1073741824

max_connections current value: 100

max_connect_errors current value: 10

max_delayed_threads current value: 20

max_heap_table_size current value: 16777216

max_join_size current value: 4294967295

max_sort_length current value: 1024

max_tmp_tables current value: 32

max_user_connections current value: 0

max_write_lock_count current value: 4294967295

myisam_sort_buffer_size current value: 8388608

myisam_max_extra_sort_file_size current value: 256

myisam_max_sort_file_size current value: 2047

net_buffer_length current value: 16384

net_retry_count current value: 10

net_read_timeout current value: 30

net_write_timeout current value: 60

open_files_limit current value: 0

query_buffer_size current value: 0

record_buffer current value: 131072

slow_launch_time current value: 2

sort_buffer current value: 2097144

table_cache current value: 64

thread_concurrency current value: 10

thread_cache_size current value: 0

tmp_table_size current value: 33554432

thread_stack current value: 65536

wait_timeout current value: 28800

Mysqld 서버가 현재 실행되고 있는 경우에는 아래 명령을 통해 실제 값을 확인할 수 있다.

$ mysqladmin variables

여기서 중요한 버퍼 및 환경 설정 변수에 대하여 설명하겠다.

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

ㅇ max_connections : 동시 접속 가능한 클라이언트 숫자. 동시접속자수를 늘리면 운영체제상에서 mysqld가 필요로하는 파일 지시자(descriptor)의 숫자만큼 값을 늘려야 한다.

ㅇ table_cache : 모든 스레드에서 열 수 있는 테이블의 숫자. mysql은 각 유일한 오픈 테이블에서 두개의 파일 디스크립터가 필요하다.

한번 연 테이블의 캐쉬는 table_cache까지 커질 수 있다.

ㅇ join_buffer_size : 인덱스를 사용하지 않는 조인의 full-join에서 사용하는 버퍼의 크기. 버퍼는 두 테이블 사이에서 각 full-join마다 한번 할당이 된다.

인덱싱을 추가하지 못할 때 조인 버퍼를 증가시키면 full join의 속도를 향상시킬 수 있다. (일반적으로 빠르게 조인을 하는 가장 좋은 방법은 인덱스를 추가하는 것이다)

ㅇ max_allowed_packet : 한 패킷의 최대 크기. 메시지 버퍼는 net_buffer_length 바이트로 초기화되지만 필요하면 최대 허용 패킷 바이트를 증가시킬 수 있다.

기본값은 큰 패킷을 잡기에는 작다. 거대 BLOB 컬럼을 사용한다면 값을 증가시켜야 한다. 사용자가 원하는 최대 blob만큼 크게  해야 한다

ㅇ max_join_size : 최대 조인 크기이상으로 레크드를 읽는 조인을 하면 에러가 난다.

만약 사용자가 where 문을 사용하지 않고 시간이 많이 걸리면서 몇백만개의 레코드를 읽는 조인을 수행하려 하면 이 값을 설정한다

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

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

ㅇ thread_cache_size : 재사용을 위해 캐쉬에 남겨둘 스레드 숫자

ㅇ tmp_table_size : 메모리 기반의 임시 테이블이 이 값을 넘으면 mysql은 자동으로 디스크 기반의 MyISAM 테이블로 변환한다.

매우 복잡한 group by 질의를 사용하고 메모리가 부족한 경우 값을 늘린다.

ㅇ wait_timeout : 연결을 끊기전 서버에서 대기하는 시간 (초)

위의 옵션은 실제 서버를 계속 주기적으로 모니터링하면서 적절한 값으로 변경을 하여야 한다.

현재 운영되고 있는 서버에 대한 통계 정보는 아래 명령을 통해 확인할 수 있다.

# mysqladmin status

Uptime: 562721 Threads: 1 Questions: 3581473 Slow queries: 502 Opens: 1472301 Flush tables: 1 Open tables: 8 Queries per second avg: 6.365

이 경우에는 간단한 요약정보만 나오며 상세한 정보는 extended-status 옵션을 이용하면 된다.

리스트 2. mysql 서버 상태보기

# mysqladmin extended-status

+--------------------------+----------+

| Variable_name | Value |

+--------------------------+----------+

| Aborted_clients | 0 |

| Aborted_connects | 0 |

| Bytes_received | 2038376 |

| Bytes_sent | 99190670 |

| Connections | 3946 |

| Created_tmp_disk_tables | 119 |

| Created_tmp_tables | 551 |

| Created_tmp_files | 0 |

| Delayed_insert_threads | 0 |

| Delayed_writes | 0 |

| Delayed_errors | 0 |

| Flush_commands | 2 |

| Handler_delete | 0 |

| Handler_read_first | 225 |

| Handler_read_key | 1079509 |

| Handler_read_next | 1710340 |

| Handler_read_prev | 56124 |

| Handler_read_rnd | 865824 |

| Handler_read_rnd_next | 2682384 |

| Handler_update | 193 |

| Handler_write | 112561 |

| Key_blocks_used | 1443 |

| Key_read_requests | 2472836 |

| Key_reads | 1441 |

| Key_write_requests | 243 |

| Key_writes | 215 |

| Max_used_connections | 9 |

| Not_flushed_key_blocks | 0 |

| Not_flushed_delayed_rows | 0 |

| Open_tables | 111 |

| Open_files | 217 |

| Open_streams | 0 |

| Opened_tables | 171 |

| Questions | 17780 |

| Select_full_join | 119 |

| Select_full_range_join | 0 |

| Select_range | 596 |

| Select_range_check | 0 |

| Select_scan | 4175 |

| Slave_running | OFF |

| Slave_open_temp_tables | 0 |

| Slow_launch_threads | 0 |

| Slow_queries | 0 |

| Sort_merge_passes | 0 |

| Sort_range | 197 |

| Sort_rows | 1039365 |

| Sort_scan | 2652 |

| Table_locks_immediate | 16315 |

| Table_locks_waited | 2 |

| Threads_cached | 6 |

| Threads_created | 11 |

| Threads_connected | 2 |

| Threads_running | 1 |

| Uptime | 863 |

+--------------------------+----------+

그러면 현재 Mysql 서버의 활동에 대한 상세한 통계 정보가 출력된다. 주요 옵션을 살펴보겠다.

ㅇ Aborted_clients : 클라이언트에서 연결을 적절하게 닫지 못하고 죽어서 취소된 연결 횟수

ㅇ Aborted_connects : 연결 시도 실패 횟수

ㅇ Connections : mysql 서버에 연결 시도한 횟수

ㅇ Key_blocks_used : 키 캐쉬에서 사용된 블락 횟수

ㅇ Key_read_requests : 캐쉬에서 키 블락을 읽어들인 요청 횟수

ㅇ Key_reads : 디스크에서 물리적으로 키 블락을 읽어들인 횟수

ㅇ Max_used_connections : 최대 동시 접속자수

ㅇ Open_tables : 현재 열린 테이블 숫자

ㅇ Open_files : 현재 연 파일 숫자

ㅇ Opened_tables : 열었던 테이블 숫자

ㅇ Select_full_join : 인덱스를 사용하지 않은 조인

ㅇ Questions : 서버로 보낸 질의 횟수

ㅇ Slow_queries : long_query_time 이상 넘어간 질의 횟수

ㅇ Sort_merge_passes : 정렬을 위해 병합한 숫자

ㅇ Table_locks_waited : 테이블 락을 위해 대기한 시간

위에서 나온 통계를 가지고 서버의 버퍼 및 환경변수를 적절하게 수정해야 한다. 예를 들면 다음과 같다.

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

ㅇ 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 옵션을 이용하면 정보를 갱신하였을 경우 차이점만을 보여준다.

리스트 3. 프로세스 모니터링하기

# mysqladmin -i3 processlist

+------+------+-----------+-------+---------+------+-------+------------------+

+------+------+-----------+-------+---------+------+-------+------------------+

+------+------+-----------+-------+---------+------+-------+------------------+

+------+------+-----------+-------+---------+------+-------+------------------+

+------+------+-----------+-------+---------+------+-------+------------------+

5. 마치며

Mysql 튜닝의 경우 SQL/DB구조 설계/서버 세팅으로 나눌 수 있다고 하였는데 위에서는 그중 서버 세팅 일부분에 대해서만 살펴보았다. 실제로 세부적으로 들어가면 여러가지 고려할 점들이 많지만 이번 강좌에서는 전반적인 큰 흐름에 대해서 살펴보았다. 우리가 이전 강좌에서도 먼저 시스템을 모니터링하고 나서 튜닝작업을 진행하였다. 이것은 데이터베이스에도 마찬가지이다. SQL문의 경우 EXPLAIN 이라는 명령을 통하여 실제로 질의문이 어떻게 실행되고 인덱스가 사용되는지, SQL문이 적절하게 사용되고 있는지를 확인해야하고 서버를 튜닝할 경우 mysqladmin 등의 모니터링 프로그램을 이용하여 현재 서버의 상태가 어떻고 어떻게 작동하고 있는지를 알고 있어야한다. 이러한 작업을 먼저 수행하고나서 튜닝작업이 진행될 수 있다. Mysql에서 I/O분산을 위해 replication을 사용하거나 UDF를 이용하여 사용자 함수를 작성하여 부하를 줄이는 등의 작업은 그 이후에 진행될 부분들이다.

어느정도 위에서 지적을 한대로 아직까지 한글로 된 Mysql 정보가 많은 것은 아니며 가장 정리가 잘 되어 있는 것은 Mysql 사이트에서 받을 수 있는 영문 매뉴얼이다. 영문 매뉴얼의 경우 최적화 관련한 부분의 경우도 정리가 아주 잘 되어있다. Mysql 프로그램은 소스가 공개되어있고 아주 활발하게 업그레이드가 되어서 계속 새로운 기능이 추가되고 있고 그에 따라 매뉴얼도 자주 갱신이 되는 편이다. 마지막으로 강조하고 싶은 것은 처음에 지적했던 대로 어떠한 데이터베이스를 가지고 작업을 하든지 처음 디자인 작업이 가장 중요하다는 것이다. Mysql 튜닝과 관련한 더 자세한 정보는 아래 참고자료를 이용하기 바란다.

## 참고자료

1. http://www.mysql.com

2. mysql 매뉴얼 번역본(문태준) : http://tunelinux.pe.kr/mysql/manual/ 번역작업은 1999년초에 된 것이며 10장이 튜닝과 관계있다

3. 아주 특별한 웹데이터베이스 MySQL & Web DB 연동 : 허정수 저. 베스트북 출간

4. MySQL Advanced Class : 허정수 저. 베스트북 출간

5. mysql 최적화 문서 : http://database.sarang.net/database/mysql/tuning/optimize_mysql.html (최영봉님 번역)

6. http://database.sarang.net

7. Oracle8i Tuning Release 8.1.5 : 오라클사 매뉴얼

8. Oracle Bible Ver.8.x 9장 오라클 튜닝: 홍준호외 저. ㈜영진출판사 출간

9. 데이터베이스 시스템 : 박석역. 홍릉과학출판사 출간

10. http://kldp.org/KoreanDoc/mSQL.zip : 김영민. DB전반에 강좌 및 msql 설명