반응형
MySQL Database의 경우 Oracle 이나 MS SQL Server에 비해서 대용량의 자료를 처리하는 경우가 적기에 튜닝에 필요성이 적은 것 같습니다. 그러나 웹이라는 환경은 많은 사용자가 동시에 접속을 할 수 있기에 항상 모니터링과 최적화는 기본이라고 생각합니다.
본 강좌에서는 기본적인 모니터링 방법과 Connection과 Memory 부분에 대한 튜닝 방법을 소개하도록 하겠습니다.
가. 모니터링 및 초기화 명령어
- show status – MySQL 데이타베이스의 현재 상황
- show Processlist – MySQL 프로세스 목록
- show variables – 설정 가능한 모든 변수 목록
- flush logs – MySQL의 로그파일 초기화
- flush status – MySQL 상태정보 초기화
- flush thread – 쓰레드 캐시에 저장된 쓰레드 초기화
- flush tables – MySQL에 캐싱된 테이블 초기화
- flush privileges – 권한정보 재 설정
나. Connection 튜닝
1. status
- Aborted_clients – 클라이언트 프로그램이 비 정상적으로 종료된 수
- Aborted_connects – MySQL 서버에 접속이 실패된 수
- Max_used_connections – 최대로 동시에 접속한 수
- Threads_cached – Thread Cache의 Thread 수
- Threads_connected – 현재 연결된 Thread 수
- Threads_created – 접속을 위해 생성된 Thread 수
- Threads_running – Sleeping 되어 있지 않은 Thread 수
2. system variables
- wait_timeout – 종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
- thread_cache_size – thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
- max_connections – 최대 동시 접속 가능 수
그외에 status 또는 system variables 값은 참고의 Mysql 메뉴얼을 참조해 주십시요.
| mysql> show variables like '%max_connection%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 100 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> show status like '%connect%'; +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | Aborted_connects | 3782 | | Connections | 2961108 | | Max_used_connections | 90 | | Threads_connected | 1 | +----------------------+---------+ 4 rows in set (0.01 sec) mysql> show status like '%clients%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Aborted_clients | 2160 | +-----------------+-------+ 1 row in set (0.00 sec) mysql> show status like '%thread%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | Delayed_insert_threads | 0 | | Slow_launch_threads | 0 | | Threads_cached | 7 | | Threads_connected | 1 | | Threads_created | 1364 | | Threads_running | 1 | +------------------------+-------+ 6 rows in set (0.00 sec) |
Cache Miss Rate(%) = Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100
위의 경우는 Cache Miss Rate(%) = 0.05%, Connection Miss Rate(%) = 0.12%, Connection Usage(%) = 1%
3. 튜닝
- Connection Usage(%)가 100% 라면 max_connections 수를 증가시켜 주십시요. Connection 수가 부족할 경우 Too Many Connection Error 가 발생합니다.
- DB 서버의 접속이 많은 경우는 wait_timeout 을 최대한 적게 (10~20 정도를 추천) 설정하여 불필요한 연결을 빨리 정리하는 것이 좋습니다. 그러나 Connection Miss Rate(%) 가 1% 이상이 된다면 wait_timeout 을 좀 더 길게 잡는 것이 좋습니다.
- Cache Miss Rate(%) 가 높다면 thread_cache_size를 기본값인 8 보다 높게 설정하는 것이 좋습니다. 일반적으로 threads_connected 가 Peak-time 시 보다 약간 낮은 수치로 설정하는 것이 좋습니다.
- MySQL 서버는 외부로 부터 접속 요청을 받을 경우 인증을 위해 IP 주소를 호스트네임으로 바꾸는 과정을 수행하여 접속시에 불필요한 부하가 발생하게 됩니다. skip-name-resolve를 설정하시고 접속시에 IP 기반으로 접속을 하게 되면 hostname lookup 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.
다. Memory 튜닝
1. status
- key_block_unused – Key Cache에서 사용되고 있지 않은 Block 수
- key_reads – Key Block 읽기 요청시 Disk을 읽은 수
- key_read_requests – Key Block 읽기 요청수
2. system variables
- key_buffer_size – 인덱스를 메모리에 저장하는 버퍼의 크기
- table_cache – 전체 쓰레드가 사용할 오픈 가능한 테이블 수
- myisam_sort_buffer_size – 테이블 repair,Alter table,load data에 사용되는 버퍼 메모리 크기
- join_buffer_size – 조인을 위한 메모리 버퍼 크기
- record_buffer – 순차적인 검색을 위해 사용되는 메모리 버퍼 크기
- record_rnd_buffer – order by 절을 사용할 경우 디스크 사용을 피하기 위하여 사용하는 메모리 버퍼 크기
- sort_buffer – order by 와 group by에 사용되는 메모리 버퍼 크기
- tmp_table_size – group by 시 디스크를 사용하지 않고 임시 테이블을 만들기 위해 사용되는 메모리 크기
- key_cache_block_size – block 의 크기(bytes, 기본값 1024)
| mysql> show status like '%key%'; +------------------------+-----------+ | Variable_name | Value | +------------------------+-----------+ | Com_preload_keys | 0 | | Com_show_keys | 2945 | | Handler_read_key | 365020739 | | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 222601 | | Key_blocks_used | 231960 | | Key_read_requests | 847204435 | | Key_reads | 4195954 | | Key_write_requests | 25034738 | | Key_writes | 16452136 | +------------------------+-----------+ 10 rows in set (0.00 sec) |
Key Buffer Usage = 1 – ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
Key_reads/Key_read_requests Rate(%) = Key_reads/Key_read_requests * 100
Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100
* ^Key_Reads = Current Key_Rreads – Previous Key_Reads
3. 튜닝
- key_buffer_size는 총 메모리 크기의 25% 정도의 크기로 설정하는 것이 좋습니다.
- Key_reads/Key_read_requests Rate(%)은 일반적으로 1%보다 적습니다. 1% 보다 높다면 Key Cache가 아닌 디스크를 읽은 경우가 많다고 판단할 수 있습니다. 또한 Key_reads/Key_reads_requests Relative Rate(%) 값이 지속적으로 90% 이상일 경우는 key_buffer_size가 효율적으로 설정되어 있다고 생각하시면 됩니다. 하지만 데이터베이스가 엄청나게 크거나 여러 데이터를 골고루 많이 읽는 데이터베이스라면 아무리 많은 양의 키 캐시를 설정해도 90% 이상의 적중률을 얻을 수는 없습니다.
라. 적용
system variables은 my.cnf 또는 my.ini 파일을 수정 후 MySQL Server 를 재시작 해 주십시요.
| [www@smson www]$ vi /etc/my.cnf # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 wait_timeout = 120 ~~~ [root@smson mysql]# /usr/local/mysql/share/mysql/mysql.server restart |
마. 참고
- http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
- http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
- http://jeremy.zawodny.com/blog/archives/000173.html
- http://www.dbguide.net/know/know101003.jsp?IDX=887&catenum=14
- High Performance MySQL
- http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
- http://www.mysqlperformanceblog.com/mysql-performance-presentations/
※ 출처: http://www.albumbang.com/board/board_view.jsp?board_name=free&no=139
반응형
