티스토리 뷰

Oracle

[Oracle] MYSQL 점검

정짱 정뚱띵 2018.02.08 17:28

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

댓글
공지사항
«   2018/08   »
      1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31  
Total
118,121
Today
38