mysql 성능측정

mysql> show status like '%Thread%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 4     |
| Threads_connected      | 5     |
| Threads_created        | 18    |
| Threads_running        | 1     |
+------------------------+-------+

 

mysql> show status like '%CONNECT%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 17    |
| Connections              | 12135 |
| Max_used_connections     | 16    |
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 5     |
+--------------------------+-------+

 

Cache Miss Rate(%) = Threads_created / Connections * 100
Connection Miss Rate(%) = Aborted_connects / Connections * 100
Connection Usage(%) = Threads_connected / max_connections * 100

 

Cache Miss Rate(%) = 5 / 12135 * 100 = 0.04% (thread_cache_size를 기본값보다 높게 설정한다)
Connection Miss Rate(%) = 17 / 12135 * 100 = 0.14% (1%이상이라면 wait_timeout을 길게 잡는것이 좋음)
Connection Usage(%) = 5 / 16 * 100 = 31.25% (100%라면 max_connection 수를 증가시켜야 함)

 

 

Key_blocks_not_flushed          0
Key_blocks_unused               209926
Key_blocks_used                 113433
Key_read_requests               24330925
Key_reads                       113433
Key_write_requests              5830430
Key_writes                      5817276
key_buffer_size                 402653184                                                                     
key_cache_age_threshold         300                                                                           
key_cache_block_size            1024                                                                          
key_cache_division_limit        100   

 

key_buffer_size는 총메모리 크기의 25%가 적절함
Key Buffer Usage = 1 - ((Key_blocks_unused x 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_read/key_read_requests) * 100

 

Key Buffer Usage = 1 - ((209926 x 1024) / 402653184) = 0.4661
Key_reads/Key_read_requests Rate(%) = 113433 / 24330925 * 100 = 0.47% (1%보다 높다면 key cache가 아닌 디스크를 읽은 경우가 많다)
Key_reads/Key_read_requests Relative Rate(%) = (  1- 113433 / 24330925 ) * 100 = 99.53%
(90%이상의 경우 key_buffer_size가 효율적으로 설정되어 있다)

[출처] mysql 성능측정|작성자 그레고리