mysql-성능튜닝 #2

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를 비교하여 쿼리 캐시 적중률을 계산하여 성능을 최적화 할 수 있다.