728x90
반응형

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)

 

반응형

+ Recent posts