[MySQL]MySQL 파라메터 튜닝

mysqltuner.pl

# ibdata 파일 저장 디렉토리

innodb_data_home_dir = /usr/local/mysql/data

 

# 파일 갯수를 적당히 늘려 한개 파일에 부하가 걸리지 않도록 합니다.

# DB최대 용량은 20G로 설정했습니다. 최대 용량은 제한이 없습니다.

# 파일 한개가 512M 로 시작하며 ibdata1, ibdata2, ibdata3 파일에 골고루 데이타 입력합니다.

  512M 가 넘어서게되면 용량이 점차 늘어나며, ibdata 파일들 용량 총합이 20000M 일 경우

  테이블에 lock 이 걸리며 더이상 쓰기가 되지 않습니다.

innodb_data_file_path = ibdata1:512M;ibdata2:512M;ibdata3:512M:autoextend:max:20000M

innodb_log_group_home_dir = /usr/local/mysql/data

 

# innodb_buffer_pool_size는 메모리의 1/2 정도를 사용합니다.

innodb_buffer_pool_size = 2G

innodb_additional_mem_pool_size = 8M

innodb_log_file_size = 512M

innodb_log_buffer_size = 4M

 

# 리플리케이션의 경우 1로 아닌경우엔 2로합니다. (속도향상)

# 1초마다 log 를 기록합니다.

innodb_flush_log_at_trx_commit = 2

innodb_lock_wait_timeout = 50

 

# caching 데이타는 실시간으로 disk 적용하지 않습니다.

innodb_flush_method = O_DSYNC

 

# 스레드의 두배 조금 안되게 잡아주는것이 적정값입니다.

innodb_thread_concurrency = 12

 

 

6203776_tuning-primer.txt

이 스크립트는 "SHOW STATUS LIKE..."와 "SHOW VARIABLES LIKE..."정보를 얻어낸다. 바로 이 설정값과 운영에 따라 나온 수치를 비교하여 적정한 값을 권장해준다. DB서버는 반드시 운영중이어야 하며, 최소 2일 이상 운영한 상태를 권장하고 있다. 스크립트는 Linux, Solaris, FreeBSD, MacOS에서 사용 가능.

DB 업타임, 평균 QPS
Slow Query Log
Max Connections
Worker Threads
Key Buffer
Query Cache
Sort Buffer
Joins
Temp Tables
Table (Open & Definition) Cache
Table Locking
Table Scans (read_buffer)
Innodb Status (사용중인 경우만)

connection 튜닝확인

status에서 -
Aborted_clients - 클라이언트 프로그램이 비 정상적으로 종료된 수
Aborted_connects - MySQL 서버에 접속이 실패된 수
Max_used_connections - 최대로 동시에 접속한 수
Threads_cached - Thread 수
Threads_connected - 현재 연결된 Thread 수
Threads_created - 접속을 위해 생성된 Thread 수
Threads_running - Sleeping 되어 있지 않은 Thread 수

variables에서 -
wait_timeout - 종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
thread_cache_size - thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다. 이건 byte등의 사이즈가 아닌 수이다.

[connect] 튜닝
max_connections - 최대 동시 접속 가능 수 = Max_used_connections
Cache Miss Rate(%) = Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100

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 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.

wait_timeout 을 my.cnf에서 수정했는데 변경확인하려면
mysql> show global variables;
혹은 set session wait_timeout = 30;

--------------------------------------------------------------------------------
주요 옵션 (show varialbles like '...%' 이용)
(1) table_cache (디폴트 64): 사용하는 테이블에 대한 핸들러를 캐시에 저장, opend_tables 항목이 높을 경우 table_cache를 높인다.
(2) thread_cache_size(디폴트 0) : 재 사용을 위해 보관할 쓰래드 수, thread_created가 높을 경우 증가된다. 클라이언트가 커넥션 풀을 쓰는 경우는 의미가 없다.
(3) max_connections(기본 100) : 허용 가능한 최대 동시 접속수, 쓰레드 관련 메모리 튜닝시 매우 중요, 각각의 커넥션은 최소 thread_stack의 사이즈만큼의 메모리 사용
(4) connect_timeout : connect요청받고 몇초간 기다릴지
(5) net_buffer_length : 클라이언트에 보내는 초기 메시지의 바이트 수 조절한다.
(6) max_allowd_packet : 서버 / 클라이언트간 최대 전송 가능 패킷 크기를 설정한다., Text나 bloc컬럼이 많이 있거나, 리플리케이션을 하는 경우 최소 16M이상 잡아야 한다.
(7) back_log (디폴트 50) : connection이 대량으로 몰릴 때 큐에 대기가능한 커넥션의 갯수
(8) wait_timeout : 커넥션의 타임아웃
(9) net_retry_count : 통신이 잘못되어 실패할 때, 몇 번까지 재시도할지
(10) max_connect_error : 계속적으로 문제를 발생시키는 클라이언트를 몇번째 재시도후 block할지 지정. 한번 block되면, 서버를 재시작하거나 flush host명령을 실행하기 전까지 접속 불가 일반적인 경우.. 내부에서만 쓸 경우가 많아서 크게 잡아놓음
나의 경우는 다음과 같이 지정
max_connections를 1000, 이때 thread_stack은 196608, thread_cache_size는 4.
connect_timeout : 5, max_allowed_packet : 33,553,408(리플리케이션 적용) , back_log는 50
max_user_connections : 0 , max_connect_errors 99999999

3. 주요 환경변수 (show status like 'thread..%' 이용)
(1) Connection관련
- Max_used_connections (720) : 피크 타임의 동시 접속 수, 튜닝의 제일 중요한 요소임
- Bytes_received (3656293909) / Bytes_sent (1293843766) : 모든 클라이언트와 전송된 바이트의 총합
- connections( 2045524 ) : 시도된 connection의 합
- Aborted_connects (40979) : 어플리케이션 또는 mysql의 커넥션 관련 옵션이 잘 못 될 수 있다.

[Thread] 튜닝 -
-threads_created:서버 시작후, 현재까지 만들어진 총 쓰레드의 갯수
-threads_connected: 현재 열려있는 쓰레드수
-threads_cached : 재사용 가능한 동작 중이지 않은 쓰레드
-threads_running : sleeping 상태가 아닌 쓰레드 수
[master]
| Threads_cached | 43 |
| Threads_connected | 23 |
| Threads_created | 66 |
| Threads_running | 3 |
[slave]
| Threads_cached | 120 |
| Threads_connected | 383 |
| Threads_created | 1681 |
| Threads_running | 1 |
- slow_launch_threads :쓰래드 생성에 걸린 시간이 slow_launch_time 이상 걸린 쓰래드의 수. Slow_launch_time은 기본 2초이다. 0에 가까와야 좋은 것이다. 내 db는 0이군.
*쓰레드 캐쉬의 적중률은 threads_created/connections로 계산 가능
A DB(캐슁용) : 1681/1505789 : 0.1%의 쓰레드 캐쉬의 적중률을 보여준다 (현재 thread_cache_size : 4)
B DB(어드민용) : 179348 / 2046107 : 8.7%의 쓰레드 캐쉬의 적중률을 보여준다 (현재 thread_cache_size : 128 )
=>thread_cache_size나 높여볼까나? 확실히 thread_cache_size가 높은 것이 훨씬 높다.

[thread_cache_size]
먼저 이 값은 bytes 단위 등의 크기가 아닙니다. 정수 단위의 '수'를 의미합니다. (mysql에서 단위를 헷갈리게 하는 부분임)
mysql 클라이언트가 mysql 서버에 접속할때(Connections) thread를 생성하는데 cache에 thread가 있으면 생성하지
않고 그 thread 를 사용합니다.

클라이언트에서 접속이 끊어지면(disconnects) MySQL 서버는 이전의 클라이언트 thread 수가 thread_cache_size 값보다
작으면 현재 thread를 cache 에 올려놓습니다.

cache에 올려놓은 thread는 Threads_cached로 확인할 수있습니다.(이 정보가 빠져있군요)
cache에 있는 thread 재사용수 설정 ==> thread_cache_size입니다. 즉 얼만 만큼의 thread 수를 cache에 올려놓을
것인지 결정하는 항목입니다.

여기에서 cache에 있는 thread를 사용하지 않고 새로운 thread가 생성되면 Threads_created 값이 1씩증가하고, cache에 있는 thread를 용할 경우는 Threads_created 값은 증가하지 않습니다.기준은 Questions가 아니고 Connections 입니다.

즉 Connections 값과 Threads_created 값, 그리고 현재 MySQL 서버가 얼마정도로 바쁜지를 파악하여 이 thread_cache_size 값을 조절해 줘야 합니다. 기본값은 thread_cache_size = 0 입니다.이 의미는 Connections 이 이루어질때 cache에 있는 thread를 사용하지 말고 새로운 thread를 생성하여 사용하라는 의미입니다.

즉 매우 바쁜 MySQL 서버가 아니면 0 또는 2 값 정도면충분합니다.

그러나 MySQL 서버가 상당히 바쁘고(STATUS == busy 이상) 이 값이 작으면 Connections이 이루어질때마다 새로운 thread가 생성된다는 단점(?)이 있습니다.

MySQL 영문 매뉴얼에서는 Threads_created 값이 크면(big)thread_cache_size 값을 올려주라고 권고하지만 얼마만큼이 그 기준인지에 대해서는 따로 언급이 없습니다.다만 (Threads_created / Connections)값을 계산해 보라는 정도입니다.

그렇다면 성능면측에서 생각해보면, 서버가 상당히 바쁘면 새로운 thread 를 생성하여 사용하는것 보다는 기존의 cache 에 있는 thread를 재사용하는것이 더 유리합니다.그러나 성능 좋은 thread 실행(implementation) 시스템에서는 그리 크게 낫은 성능은 없습니다.

어째튼 서버가 매우 바쁜 정도라면 이 값을 어느 정도까지 올려주는 것이 좋습니다.

본 프로그램에서는 (Threads_created / Connections)값이 0.01 즉 1% 이상이면 thread_cache_size 값을 올려주라고comments하고 있습니다.보통 none interactive 모드에서,하나의 커넥션이 맺어지고 exit 까지는 대략 1초 미만이 대부분입니다. 길게 1초라고 가정하고,

얼마자 자주 커넥션이 들어오는지(초당 커넥션)를 계산하면,
커넥션 관련 통계
(
cps = Connecions / Uptime ; // 초당 커넥션 수
spc = Uptime / Connections ; // 커넥션 주기(초)
cpq = Question / Connections ; // 커넥션당 요청 쿼리 수
)
cps 값으로 알 수 있습니다.

평균적인 계산이기 때문에 이 cps 값이 필요로 하는 thread_cache_size값이 됩니다. 이 cps 값도 한계가 있죠.. max_connections 이 제한점이기 때문에..따라서,

ccps : ceil(Connecions / Uptime); // 올림
최소 : (1 초 * ccps) + 0
권장 : (2 초 * ccps) + 1
최대 : (3 초 * ccps) + 2
극대 : (4 초 * ccps) + 3
이 정도로 계산하면 됩니다.

단, 계산된 thread_cache_size 도 한계가 있습니다.당연히 max_connections 보다 작아야 하고, 1 절에서 구한 LTP 에 의해서
(LPT * cps) 보다 작아야 합니다.또한 back_log 보다 작아야 합니다.

mysql> SET GLOBAL thread_cache_size = 값;

참조 : http://blog.naver.com/PostView.nhn?blogId=parkjy76&logNo=30096122678

(3) Handler

일반적인 해석
- handler_read_first가 높은 경우 많은 풀 인덱스 스캔이 이루어짐
- handler_read_next가 높은 경우 풀 인덱스 스캔과 레인지 스캔이 이루어짐
- handler_read_rnd가 높은 경우 많은 풀 테이블 스캔과 레인지 스캔이 이루어짐
- handler_read_key가 높은 경우 인덱스를 읽는 경우가 많음
[A DB(캐슁용)] => 별 튜닝 요소가 없다.
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 19 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 25 |
[B DB(어드민용)] =>튜닝 요소가 많다. 많은 풀 테이블 스캔과 레인지 스캔이 이루어지는 것 같다.
| Handler_commit | 199415 |
| Handler_delete | 269322 |
| Handler_discover | 0 |
| Handler_read_first | 67565 |
| Handler_read_key | 13910141 |
| Handler_read_next | 429593667 |
| Handler_read_prev | 2196569 |
| Handler_read_rnd | 372568 |
| Handler_read_rnd_next | 2211096685 |
| Handler_rollback | 1415609 |
| Handler_update | 595584 |
| Handler_write | 2026479 |

4. 성능
(1) MySQL의 느린 응답을 나타낸다. => slow_queries와 slow_launch_threads
(2) 부하가 심하다는 것을 나타낸다 => threads_created, max_used_connections, opend_tables이 큰 경우 (이 경우 table_cache를 올리자)
(3) 인덱스를 많이 읽는다. => handler_read_key가 높은 경우 
(4) 락 경쟁과 관련된 항목 => table_locks_waited VS table_locks_immediate , 만약 table_locks_waited가 높으면, myisam에서 innodb로 변경하는 것이 좋다.
A DB(캐슁)
| Table_locks_immediate | 20498565 |
| Table_locks_waited | 0 |
B DB(어드민)
| Table_locks_immediate | 9500191 |
| Table_locks_waited | 12509 |
(5) 메모리에 적용할 수 없는 큰 임시 테이블이 많이 만들어졌다 => created_tmp_disk_tables
=> tmp_table_size를 올려서 과도한 DISK I/O가 발생하는 것을 막는다. 즉, 디스크를 안쓰고, 메모리를 쓰도록 한다.
A DB(캐슁) |Created_tmp_disk_tables | 0 (tmp_table_size | 67108864 )
B DB(어드민)| Created_tmp_disk_tables | 107 ( tmp_table_size | 33554432 )
(6) select쿼리가 최적화 되지 못했다 => Select_xxx
=> select_full_join과 select_range_check는 많거나 다른 인덱스가 필요하다.
(6) sort_xxx 환경변수들의 값이 크다 => 쿼리에 따라 ordering 및 grouping 비용 크다.
=> sort_buffer_size가 크게 설정한다. 25%이상의 성능 향상을 기대할 수 있다. sort_buffer에 데이터를 정렬 한 후 실제 결과값을 처리하는 것은 read_rnd_buffer_size에 영향을 준다.

5. 최적화
(1) Table Cache 최적화
- table_cache 값을 올릴 때, mysqld가 필요로 하는 file descriptor의 수가 증가한다.
- MyISAM 엔진을 사용하는 경우 열려있는 각각의 테이블에 대해 하나의 file descriptor가 필요하게 되고, 게다가 인덱스 파일을 위해 하나의 file descriptor가 더 필요하다.
- 대부분의 운영체제에서는 프로세스당 사용할 수 있는 file descriptor의 수가 제한되는데, 이를 위해서 ulimit을 이용해서 file descript를 최대한 unlimited로 수정하는 것이 좋다.
- Opend_table 환경변수가 높을 때, 그리고 FLUSH TABLE을 자주 수행하지 않는다면 table_cache를 높이는 것이 좋다.
(2) Table Scan 최적화
- 디스크 억세스를 줄이기 위해 read_buffer가 사용된다.
- read_buffer_size는 기본 128Kb의 크기를 가지고, 높을 수록 테이블 스캔의 성능을 높여준다.
- 높여주되 지나치게 큰 값을 잡는 것은 좋지 않다. 기본적으로 테이블 스캔을 수행하는 모든 쓰래드에 할당될 수 있다.
(3) Join
- 조인되는 컬럼에 인덱스가 존재하지 않을 경우 성능이 매우 떨어진다.
- 인덱스를 추가할 수 없는 경우 join_buffer_size를 높인다.
- 두 테이블 간에 풀 조인이 일어날 경우 하나의 join_buffer가 할당되고 인덱스가 적용되지 않는 조인의 테이블 수가 늘어나는 만큼의 join_buffer가 할당된다.
(4) querty cache
select 쿼리와 그 결과를 저장한다. Query_cache_size를 클 수록 쿼리 캐시 싸이즈도 커진다. 하지만, 테이블이 바뀌면, Query cache는 모두 reset된다.
query_cache_limit을 조정함으로써 쿼리 캐시에 저장될 최대 쿼리 크기를 조절할 수 있고, Query_cache_min_res_unit(block size)를 설정하여 쿼리 캐시의 조각화를 줄일 수도 있다. 기본값은 4K이다.
Qcache_hits 와 Com_select를 비교하여 쿼리 캐시 적중률을 계산하여 성능을 최적화 할 수 있다.
------------------------------------------------------------------------------------

[memory] 튜닝

status에서 -
key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
key_reads - Key Block 읽기 요청시 Disk을 읽은 수
key_read_requests - Key Block 읽기 요청수
Qcache_hits - 쿼리캐시히트숫자
Qcache_inserts - 쿼리캐시에 추가된 숫자
Qcache_not_cached - 캐시되지않는 쿼리의숫자

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)

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
Query Cache Hitrate(%)=(Qcache_hits / (Qcache_hits + Qcache_inserts + Qcache_not_cached)) * 100

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% 이상의 적중률을 얻을 수는 없습니다.
Query Cache Hitrate

variables은 my.cnf 에서 수정후 재시작