가. 모니터링 및 초기화 명령어

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



~~~