mysql 튜닝 최적화

mysql 2013.08.08 16:14


[출처] http://lonmoon.tistory.com/324


mysql> show status;

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


mysql> show variables like '%max_connection%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

 

mysql> show variables like '%wait_timeout%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |  종료전까지 요청이 없이 기다리는 시간 ( TCP/IP 연결, Shell 상의 접속이 아닌 경우 )
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+

 

mysql> show variables like '%thread%';
+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| innodb_purge_threads                    | 0                         |
| innodb_read_io_threads                  | 4                         |
| innodb_thread_concurrency               | 0                         |
| innodb_thread_sleep_delay               | 10000                     |
| innodb_write_io_threads                 | 4                         |
| max_delayed_threads                     | 20                        |
| max_insert_delayed_threads              | 20                        |
| myisam_repair_threads                   | 1                         |
| performance_schema_max_thread_classes   | 50                        |
| performance_schema_max_thread_instances | 1000                      |
| pseudo_thread_id                        | 33865                     |
| thread_cache_size                       | 8                         |  thread 재 사용을 위한 Thread Cache 수로써, Cache 에 있는 Thread 수보다 접속이 많으면 새롭게 Thread를 생성한다.
| thread_concurrency                      | 16                        |
| thread_handling                         | one-thread-per-connection |
| thread_stack                            | 262144                    |
+-----------------------------------------+---------------------------+

 

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |   최대 동시 접속 가능 수
+-----------------+-------+

 

 

Cache Miss Rate(%) =  Threads_created / Connections * 100
 mysql> show status like '%thread%';
 mysql> show status like '%connect%';

 1.08% = 753 / 69671 * 100
 1.1% = 11 / 962 * 100

Connection Miss Rate(%) = Aborted_connects / Connections * 100
 mysql> show status like '%connect%';

 0.02% = 14 / 69671 * 100


Connection Usage(%) = Threads_connected / max_connections * 100
 mysql> show status like '%thread%';
 mysql> show variables like '%connect%';
 
 7.28% = 11 / 151 * 100
 2% = 4 / 200 * 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 과정을 생략하게 되어 좀 더 빠르게 접속을 하실 수 있습니다.

 

 

. Memory 튜닝

mysql> show status like '%key%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Com_assign_to_keycache | 0       |
| Com_preload_keys       | 0       |
| Com_show_keys          | 0       |
| Handler_read_key       | 0       |
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 307043  | Key Cache에서 사용되고 있지 않은 Block 수
| Key_blocks_used        | 12864   |
| Key_read_requests      | 1305206 | Key Block 읽기 요청수
| Key_reads              | 198507  | Key Block 읽기 요청시 Disk을 읽은 수
| Key_write_requests     | 55      |
| Key_writes             | 55      |
+------------------------+---------+

1. status

key_block_unused - Key Cache에서 사용되고 있지 않은 Block 수
key_reads - Key Block 읽기 요청시 Disk을 읽은 수
key_read_requests - Key Block 읽기 요청수

 


mysql> show variables like '%key%';     
+--------------------------+----------------------+
| Variable_name            | Value                |
+--------------------------+----------------------+
| delay_key_write          | ON                   |
| foreign_key_checks       | ON                   |
| have_rtree_keys          | YES                  |
| key_buffer_size          | 402653184            | 인덱스를 메모리에 저장하는 버퍼의 크기
| key_cache_age_threshold  | 300                  |
| key_cache_block_size     | 1024                 |
| key_cache_division_limit | 100                  |
| max_seeks_for_key        | 18446744073709551615 |
| ssl_key                  |                      |
+--------------------------+----------------------+


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)

 

Key Buffer Usage = 1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
 mysql> show status like '%key%';
 mysql> show variables like '%key%';
  
 0.211 = 1 - ((307095 * 1024) / 402653184)
 0.189 = 1 - ((850422 * 1024) /  1073741824)

 

Key_reads/Key_read_requests Rate(%) =  Key_reads/Key_read_requests * 100
 mysql> show status like '%key%';

  15% = 207265/1374958 * 100
  17% = 9709 / 54200 * 100
  2699 / 12898 * 100
  

 

Key_reads/Key_read_requests Relative Rate(%) = (1- ^Key_reads/^Key_read_requests) * 100
* ^Key_Reads = Current Key_Rreads - Previous Key_Reads

 = 1 - 8455/

 


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

 


.적용

[www@smson www]$ vi /etc/my.cnf 
# The MySQL server

[mysqld]
skip-name-resolve
key_buffer_size = 1024M

max_allowed_packet = 1M
table_open_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

wait_timeout = 120


참고:  mysql 튜닝 툴
wget http://mysqltuner.com/mysqltuner.pl
chmod +x ./mysqltuner.pl
./mysqltuner.pl

Trackback 0 : Comment 0