[Oracle] MYSQL 점검

[Oracle] MYSQL 점검하기

• Connection Usage(%)

Connection Usage(%) = Threads_connected(현재 연결된 Thread 수) / max_connections(최대 동시 접속 가능 수) * 100                        

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    |           |

| Connections           |           |

| Max_used_connections |        |

| Threads_connected  |           |

+----------------------+---------+

4 rows in set (0.01 sec)

• Connection Miss Rate(%)

Connection Miss Rate(%) = Aborted_connects(MySQL 서버에 접속이 실패된 수) / Connections * 100

mysql> show status like '%clients%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| Aborted_clients |          |

+-----------------+-------+

1 row in set (0.00 sec)

mysql> show status like '%Connections%';

• Cache Miss Rate(%)

Cache Miss Rate(%) =  Threads_created(접속을 위해 생성된 Thread 수) / Connections * 100

mysql> show status like '%thread%';

+------------------------+-------+

| Variable_name          | Value |

+------------------------+-------+

| Delayed_insert_threads |       |

| Slow_launch_threads    |       |

| Threads_cached          |       |

| Threads_connected      |       |

| Threads_created          |       |

| Threads_running         |       |

+------------------------+-------+

6 rows in set (0.00 sec)

• Connection Health

Connection Usage (Min : 0, MAX : 6)

mysql> Show status like 'threads_connected';

Traffic (Min : 0, MAX : 65.345)

mysql> Show status like 'bytes_sent';

Number of SQL Queries (Min : 0, MAX : 24)

mysql> Show status like 'com_select';

• Query Cache hit rate

Query Cache hit rate = Qcache_hits / (Qcache_hits + Qcache_inserts) * 100

mysql> Show status like '%Qcache%;

• Key reads rate(%)

Key_read / Key_read_requests * 100

mysql> Show status like '%Key%;

• Table 사용량 Check

DB 사용량 : 

SQL> SELECT table_schema "Database Name", SUM(data_length+index_length)/1024/1024 "Database Size (MB)" 

2> FROM information_schema.TABLES 

3> GROUP BY table_schema;

Table 사용량 :

SQL>SELECT concat( table_schema, '.', table_name ) table_name, 

2>concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 

3>concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 

4>concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size

5>FROM information_schema.TABLES;