1. mysql은 동적으로 변화 가능한 옵션은 3가지로 나눌 수 있다. - session : 조정된 값은 오직 현재 커넥션에만 영향을 미친다. - global : 조정된 값이 전체 서버에 영향을 미친다. - both : 값을 변화시킬 때 session/global을 반드시 명기해야 한다.
mysql> show global variables like 'query%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +------------------------------+---------+ 7 rows in set (0.00 sec)
mysql> show session variables like 'query%'; +------------------------------+---------+ | Variable_name | Value | +------------------------------+---------+ | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 0 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +------------------------------+---------+ 7 rows in set (0.00 sec)
2. 변경된 옵션 적용하기 set global(또는 session) parameter =value - 동작 중에 변경된 옵션은 서버 재 시작 전까지미만 적용된다. - 영구히 변경하려면, /etc/my.cnf 파일을 수정해야 한다. - super 권한이 있어야 바꿀 수 있다.
3. 주요 옵션 (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 staus like 'thread..%' 이용) (1) Connection관련 - Max_used_connections (720) : 피크 타임의 동시 접속 수, 튜닝의 제일 중요한 요소임 - Bytes_received (3656293909) / Bytes_sent (1293843766) : 모든 클라이언트와 전송된 바이트의 총합 - connections( 2045524 ) : 시도된 connection의 합 - Aborted_connects (40979) : 어플리케이션 또는 mysql의 커넥션 관련 옵션이 잘 못 될 수 있다. (2) Thread 관련 -thread_created:서버 시작후, 현재까지 만들어진 총 쓰레드의 갯수 -thread_connected: 현재 열려있는 쓰레드수 -thread_cached : 재사용 가능한 동작 중이지 않은 쓰레드 -thread_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가 높은 것이 훨씬 높다. (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를 비교하여 쿼리 캐시 적중률을 계산하여 성능을 최적화 할 수 있다
http://0903.tistory.com/51
1. mysql은 동적으로 변화 가능한 옵션은 3가지로 나눌 수 있다.
- session : 조정된 값은 오직 현재 커넥션에만 영향을 미친다.
- global : 조정된 값이 전체 서버에 영향을 미친다.
- both : 값을 변화시킬 때 session/global을 반드시 명기해야 한다.
mysql> show global variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
7 rows in set (0.00 sec)
mysql> show session variables like 'query%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
+------------------------------+---------+
7 rows in set (0.00 sec)
2. 변경된 옵션 적용하기
set global(또는 session) parameter =value
- 동작 중에 변경된 옵션은 서버 재 시작 전까지미만 적용된다.
- 영구히 변경하려면, /etc/my.cnf 파일을 수정해야 한다.
- super 권한이 있어야 바꿀 수 있다.
3. 주요 옵션 (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 staus like 'thread..%' 이용)
(1) Connection관련
- Max_used_connections (720) : 피크 타임의 동시 접속 수, 튜닝의 제일 중요한 요소임
- Bytes_received (3656293909) / Bytes_sent (1293843766) : 모든 클라이언트와 전송된 바이트의 총합
- connections( 2045524 ) : 시도된 connection의 합
- Aborted_connects (40979) : 어플리케이션 또는 mysql의 커넥션 관련 옵션이 잘 못 될 수 있다.
(2) Thread 관련
-thread_created:서버 시작후, 현재까지 만들어진 총 쓰레드의 갯수
-thread_connected: 현재 열려있는 쓰레드수
-thread_cached : 재사용 가능한 동작 중이지 않은 쓰레드
-thread_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가 높은 것이 훨씬 높다.
(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를 비교하여 쿼리 캐시 적중률을 계산하여 성능을 최적화 할 수 있다