System stat :
Show Status (Show global status) OR information_schema.GLOBAL_STATUS 테이블을 조회
Session stat :
SHOW SESSION STATUS OR information_schema.SESSION_STATUS 테이블 조회
System Stat :
mysql> show status ;
+-----------------------------------------------+---------------------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------------------+
| Aborted_clients | 34 |
| Aborted_connects | 17 |
| Binlog_cache_disk_use | 861 |
| Binlog_cache_use | 910366 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 30 |
| Bytes_received | 868 |
| Bytes_sent | 33096 |
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_instance | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| 너무 많아서 중략 ... |
| Threads_created | 37 |
| Threads_running | 2 |
| Uptime | 531524 |
| Uptime_since_flush_status | 531524 |
| validate_password_dictionary_file_last_parsed | 2016-06-23 13:25:19 |
| validate_password_dictionary_file_words_count | 0 |
+-----------------------------------------------+---------------------------------+
358 rows in set (0.00 sec)
stat 지표가 너무 많아서 다 보기 힘들 때는 발생하지 않은 데이터는 보지 않게, value > 0인 데이터를 보는 쿼리는 다음과 같다.
mysql> show global status where Value > 0 ;
+-----------------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| Aborted_clients | 90 |
| Aborted_connects | 18 |
| Binlog_cache_disk_use | 861 |
| Binlog_cache_use | 2919454 |
| Binlog_stmt_cache_use | 30 |
| Bytes_received | 1515703002 |
| Bytes_sent | 19133054451 |
| Com_admin_commands | 2077 |
| Com_analyze | 1 |
데이터가 많아서 중략....
| Max_used_connections | 36 |
| Max_used_connections_time | 2016-06-29 19:36:06 |
| Ongoing_anonymous_transaction_count | 1 |
| Open_files | 247 |
| Open_table_definitions | 138 |
| Open_tables | 790 |
| Opened_files | 1548 |
| Opened_table_definitions | 164 |
| Opened_tables | 849 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_not_cached | 31238663 |
| Qcache_total_blocks | 1 |
| Queries | 320563961 |
| Questions | 8294121 |
| Select_full_join | 758808 |
| Select_full_range_join | 1 |
| Select_range | 96991 |
| Select_scan | 1023919 |
| Sort_merge_passes | 12 |
| Sort_rows | 418072 |
| Sort_scan | 120845 |
| Table_locks_immediate | 593484 |
| Table_open_cache_hits | 60664694 |
| Table_open_cache_misses | 849 |
| Table_open_cache_overflows | 27 |
| Threads_connected | 32 |
| Threads_created | 44 |
| Threads_running | 22 |
| Uptime | 603788 |
| Uptime_since_flush_status | 603788 |
| validate_password_dictionary_file_last_parsed | 2016-06-23 13:25:19 |
+-----------------------------------------------+---------------------+
124 rows in set, 9 warnings (0.00 sec)
mysql> select * from information_schema.GLOBAL_STATUS ;
ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56'
mysql> select @@show_compatibility_56 ;
+-------------------------+
| @@show_compatibility_56 |
+-------------------------+
| 0 |
+-------------------------+
1 row in set (0.00 sec)
mysql> set show_compatibility_56=1 ;
ERROR 1229 (HY000): Variable 'show_compatibility_56' is a GLOBAL variable and should be set with SET GLOBAL
mysql> set global show_compatibility_56=1 ;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from information_schema.GLOBAL_STATUS ;
+-----------------------------------------------+------------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------------------------+------------------------------+
| ABORTED_CLIENTS | 53 |
| ABORTED_CONNECTS | 18 |
| BINLOG_CACHE_DISK_USE | 861 |
| BINLOG_CACHE_USE | 1429773 |
| BINLOG_STMT_CACHE_DISK_USE | 0 |
| BINLOG_STMT_CACHE_USE | 30 |
| BYTES_RECEIVED | 988366379 |
| BYTES_SENT | 10284664907 |
| COM_ADMIN_COMMANDS | 1830 |
| COM_ASSIGN_TO_KEYCACHE | 0 |
| COM_ALTER_DB | 0 |
| COM_ALTER_DB_UPGRADE | 0 |
| COM_ALTER_EVENT | 0 |
| COM_ALTER_FUNCTION | 0 |
| COM_ALTER_INSTANCE | 0 |
| 중략 ... |
| SSL_SERVER_NOT_AFTER | Jun 18 04:13:22 2026 GMT |
| SSL_SERVER_NOT_BEFORE | Jun 20 04:13:22 2016 GMT |
| SSL_SESSION_CACHE_HITS | 0 |
| SSL_SESSION_CACHE_MISSES | 0 |
| SSL_SESSION_CACHE_MODE | Unknown |
| SSL_SESSION_CACHE_OVERFLOWS | 0 |
| SSL_SESSION_CACHE_SIZE | 0 |
| SSL_SESSION_CACHE_TIMEOUTS | 0 |
| SSL_SESSIONS_REUSED | 0 |
| SSL_USED_SESSION_CACHE_ENTRIES | 0 |
| UPTIME_SINCE_FLUSH_STATUS | 540609 |
| VALIDATE_PASSWORD_DICTIONARY_FILE_LAST_PARSED | 2016-06-23 13:25:19 |
| VALIDATE_PASSWORD_DICTIONARY_FILE_WORDS_COUNT | 0 |
+-----------------------------------------------+------------------------------+
363 rows in set, 1 warning (0.01 sec)
Session Stat :
mysql> show session status ;
+-----------------------------------------------+------------------------------+
| Variable_name | Value |
+-----------------------------------------------+------------------------------+
| Aborted_clients | 53 |
| Aborted_connects | 18 |
| Binlog_cache_disk_use | 861 |
| Com_alter_function | 0 |
| Com_alter_instance | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_change_repl_filter | 0 |
|중략... |
| Ssl_verify_depth | 0 |
| Uptime | 540942 |
| Uptime_since_flush_status | 540942 |
| validate_password_dictionary_file_last_parsed | 2016-06-23 13:25:19 |
| validate_password_dictionary_file_words_count | 0 |
+-----------------------------------------------+------------------------------+
363 rows in set (0.00 sec)
mysql> show session status where value > 0 ;
+-----------------------------------------------+---------------------+
| Variable_name | Value |
+-----------------------------------------------+---------------------+
| Aborted_clients | 90 |
| Aborted_connects | 18 |
| Binlog_cache_disk_use | 861 |
| Binlog_cache_use | 2919454 |
| Binlog_stmt_cache_use | 30 |
| Bytes_received | 313 |
| Bytes_sent | 24942 |
| Com_show_status | 4 |
| Connections | 429 |
| Created_tmp_files | 33 |
| Flush_commands | 1 |
| Innodb_buffer_pool_pages_data | 6914 |
| Innodb_buffer_pool_bytes_data | 113278976 |
| Innodb_buffer_pool_pages_flushed | 6731706 |
| Innodb_buffer_pool_pages_free | 1024 |
| Innodb_buffer_pool_pages_misc | 254 |
| Innodb_buffer_pool_pages_total | 8192 |
| Innodb_buffer_pool_read_requests | 835724306 |
| Innodb_buffer_pool_reads | 5014310 |
| Innodb_buffer_pool_wait_free | 2329 |
| Innodb_buffer_pool_write_requests | 286604619 |
| Innodb_data_fsyncs | 3263730 |
| Innodb_data_read | 88226140672 |
| Innodb_data_reads | 5384922 |
| Innodb_data_writes | 9699906 |
| Innodb_data_written | 227282774528 |
| Innodb_dblwr_pages_written | 6227932 |
| Innodb_dblwr_writes | 91534 |
| Innodb_log_write_requests | 17300730 |
| Innodb_log_writes | 2809697 |
| Innodb_os_log_fsyncs | 2816713 |
| Innodb_os_log_written | 14925315584 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 217961 |
| Innodb_pages_read | 5384891 |
| Innodb_pages_written | 6733149 |
| Innodb_row_lock_time | 8052795 |
| Innodb_row_lock_time_avg | 38 |
| Innodb_row_lock_time_max | 51459 |
| Innodb_row_lock_waits | 211185 |
| Innodb_rows_deleted | 969624 |
| Innodb_rows_inserted | 30863229 |
| Innodb_rows_read | 165544419 |
| Innodb_rows_updated | 46835502 |
| Innodb_num_open_files | 34 |
| Innodb_available_undo_logs | 128 |
| Key_blocks_used | 6698 |
| Key_read_requests | 78845842 |
| Key_reads | 8174209 |
| Key_write_requests | 10 |
| Key_writes | 10 |
| Max_used_connections | 36 |
| Max_used_connections_time | 2016-06-29 19:36:06 |
| Ongoing_anonymous_transaction_count | 1 |
| Open_files | 247 |
| Open_table_definitions | 138 |
| Open_tables | 790 |
| Opened_files | 1548 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_not_cached | 31238843 |
| Qcache_total_blocks | 1 |
| Queries | 320564653 |
| Questions | 4 |
| Table_locks_immediate | 593664 |
| Threads_connected | 32 |
| Threads_created | 44 |
| Threads_running | 22 |
| Uptime | 604013 |
| Uptime_since_flush_status | 604013 |
| validate_password_dictionary_file_last_parsed | 2016-06-23 13:25:19 |
+-----------------------------------------------+---------------------+
71 rows in set, 9 warnings (0.00 sec)
mysql> show session status where Variable_name like 'Open%' ;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| Open_files | 247 |
| Open_streams | 0 |
| Open_table_definitions | 138 |
| Open_tables | 790 |
| Opened_files | 1548 |
| Opened_table_definitions | 0 |
| Opened_tables | 0 |
+--------------------------+-------+
7 rows in set (0.00 sec)
mysql> select * from information_schema.SESSION_STATUS ;
+-----------------------------------------------+-----------------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-----------------------------------------------+-----------------------------+
| ABORTED_CLIENTS | 53 |
| ABORTED_CONNECTS | 18 |
| BINLOG_CACHE_DISK_USE | 861 |
| BINLOG_CACHE_USE | 1453315 |
| BINLOG_STMT_CACHE_DISK_USE | 0 |
| BINLOG_STMT_CACHE_USE | 30 |
| BYTES_RECEIVED | 508 |
| BYTES_SENT | 21535 |
| COM_ADMIN_COMMANDS | 0 |
| COM_ASSIGN_TO_KEYCACHE | 0 |
| COM_ALTER_DB | 0 |
중략....
| SSL_FINISHED_CONNECTS | 0 |
| SSL_SERVER_NOT_AFTER | Jun 18 04:13:22 2026 GMT |
| SSL_SERVER_NOT_BEFORE | Jun 20 04:13:22 2016 GMT |
| SSL_SESSION_CACHE_HITS | 0 |
| SSL_SESSION_CACHE_MISSES | 0 |
| SSL_SESSION_CACHE_MODE | Unknown |
| SSL_SESSION_CACHE_OVERFLOWS | 0 |
| SSL_SESSION_CACHE_SIZE | 0 |
| SSL_SESSION_CACHE_TIMEOUTS | 0 |
| SSL_SESSIONS_REUSED | 0 |
| SSL_USED_SESSION_CACHE_ENTRIES | 0 |
| SSL_VERIFY_DEPTH | 0 |
| UPTIME | 541021 |
| UPTIME_SINCE_FLUSH_STATUS | 541021 |
| VALIDATE_PASSWORD_DICTIONARY_FILE_LAST_PARSED | 2016-06-23 13:25:19 |
| VALIDATE_PASSWORD_DICTIONARY_FILE_WORDS_COUNT | 0 |
+-----------------------------------------------+-----------------------------+
363 rows in set, 1 warning (0.00 sec)