Server

MySQL 5.6 my.cnf 글로벌 변수인지 세션변수인지 확인

by Progress posted Mar 24, 2016
?

단축키

Prev이전 문서

Next다음 문서

ESC닫기

가 크게 작게 위로 아래로 댓글로 가기 인쇄

MySQL 5.6 Reference Manual

 

Table 5.5 Status Variable Summary

Variable Name Variable Type Variable Scope
Aborted_clients integer GLOBAL
Aborted_connects integer GLOBAL
Audit_log_current_size integer GLOBAL
Audit_log_event_max_drop_size integer GLOBAL
Audit_log_events integer GLOBAL
Audit_log_events_filtered integer GLOBAL
Audit_log_events_lost integer GLOBAL
Audit_log_events_written integer GLOBAL
Audit_log_total_size integer GLOBAL
Audit_log_write_waits integer GLOBAL
Binlog_cache_disk_use integer GLOBAL
Binlog_cache_use integer GLOBAL
Binlog_stmt_cache_disk_use integer GLOBAL
Binlog_stmt_cache_use integer GLOBAL
Bytes_received integer GLOBAL | SESSION
Bytes_sent integer GLOBAL | SESSION
Com_admin_commands integer GLOBAL | SESSION
Com_alter_db integer GLOBAL | SESSION
Com_alter_db_upgrade integer GLOBAL | SESSION
Com_alter_event integer GLOBAL | SESSION
Com_alter_function integer GLOBAL | SESSION
Com_alter_procedure integer GLOBAL | SESSION
Com_alter_server integer GLOBAL | SESSION
Com_alter_table integer GLOBAL | SESSION
Com_alter_tablespace integer GLOBAL | SESSION
Com_alter_user integer GLOBAL | SESSION
Com_analyze integer GLOBAL | SESSION
Com_assign_to_keycache integer GLOBAL | SESSION
Com_begin integer GLOBAL | SESSION
Com_binlog integer GLOBAL | SESSION
Com_call_procedure integer GLOBAL | SESSION
Com_change_db integer GLOBAL | SESSION
Com_change_master integer GLOBAL | SESSION
Com_check integer GLOBAL | SESSION
Com_checksum integer GLOBAL | SESSION
Com_commit integer GLOBAL | SESSION
Com_create_db integer GLOBAL | SESSION
Com_create_event integer GLOBAL | SESSION
Com_create_function integer GLOBAL | SESSION
Com_create_index integer GLOBAL | SESSION
Com_create_procedure integer GLOBAL | SESSION
Com_create_server integer GLOBAL | SESSION
Com_create_table integer GLOBAL | SESSION
Com_create_trigger integer GLOBAL | SESSION
Com_create_udf integer GLOBAL | SESSION
Com_create_user integer GLOBAL | SESSION
Com_create_view integer GLOBAL | SESSION
Com_dealloc_sql integer GLOBAL | SESSION
Com_delete integer GLOBAL | SESSION
Com_delete_multi integer GLOBAL | SESSION
Com_do integer GLOBAL | SESSION
Com_drop_db integer GLOBAL | SESSION
Com_drop_event integer GLOBAL | SESSION
Com_drop_function integer GLOBAL | SESSION
Com_drop_index integer GLOBAL | SESSION
Com_drop_procedure integer GLOBAL | SESSION
Com_drop_server integer GLOBAL | SESSION
Com_drop_table integer GLOBAL | SESSION
Com_drop_trigger integer GLOBAL | SESSION
Com_drop_user integer GLOBAL | SESSION
Com_drop_view integer GLOBAL | SESSION
Com_empty_query integer GLOBAL | SESSION
Com_execute_sql integer GLOBAL | SESSION
Com_flush integer GLOBAL | SESSION
Com_get_diagnostics integer GLOBAL | SESSION
Com_grant integer GLOBAL | SESSION
Com_ha_close integer GLOBAL | SESSION
Com_ha_open integer GLOBAL | SESSION
Com_ha_read integer GLOBAL | SESSION
Com_help integer GLOBAL | SESSION
Com_insert integer GLOBAL | SESSION
Com_insert_select integer GLOBAL | SESSION
Com_install_plugin integer GLOBAL | SESSION
Com_kill integer GLOBAL | SESSION
Com_load integer GLOBAL | SESSION
Com_lock_tables integer GLOBAL | SESSION
Com_optimize integer GLOBAL | SESSION
Com_preload_keys integer GLOBAL | SESSION
Com_prepare_sql integer GLOBAL | SESSION
Com_purge integer GLOBAL | SESSION
Com_purge_before_date integer GLOBAL | SESSION
Com_release_savepoint integer GLOBAL | SESSION
Com_rename_table integer GLOBAL | SESSION
Com_rename_user integer GLOBAL | SESSION
Com_repair integer GLOBAL | SESSION
Com_replace integer GLOBAL | SESSION
Com_replace_select integer GLOBAL | SESSION
Com_reset integer GLOBAL | SESSION
Com_resignal integer GLOBAL | SESSION
Com_revoke integer GLOBAL | SESSION
Com_revoke_all integer GLOBAL | SESSION
Com_rollback integer GLOBAL | SESSION
Com_rollback_to_savepoint integer GLOBAL | SESSION
Com_savepoint integer GLOBAL | SESSION
Com_select integer GLOBAL | SESSION
Com_set_option integer GLOBAL | SESSION
Com_show_authors integer GLOBAL | SESSION
Com_show_binlog_events integer GLOBAL | SESSION
Com_show_binlogs integer GLOBAL | SESSION
Com_show_charsets integer GLOBAL | SESSION
Com_show_collations integer GLOBAL | SESSION
Com_show_contributors integer GLOBAL | SESSION
Com_show_create_db integer GLOBAL | SESSION
Com_show_create_event integer GLOBAL | SESSION
Com_show_create_func integer GLOBAL | SESSION
Com_show_create_proc integer GLOBAL | SESSION
Com_show_create_table integer GLOBAL | SESSION
Com_show_create_trigger integer GLOBAL | SESSION
Com_show_databases integer GLOBAL | SESSION
Com_show_engine_logs integer GLOBAL | SESSION
Com_show_engine_mutex integer GLOBAL | SESSION
Com_show_engine_status integer GLOBAL | SESSION
Com_show_errors integer GLOBAL | SESSION
Com_show_events integer GLOBAL | SESSION
Com_show_fields integer GLOBAL | SESSION
Com_show_function_code integer GLOBAL | SESSION
Com_show_function_status integer GLOBAL | SESSION
Com_show_grants integer GLOBAL | SESSION
Com_show_keys integer GLOBAL | SESSION
Com_show_master_status integer GLOBAL | SESSION
Com_show_ndb_status integer GLOBAL | SESSION
Com_show_new_master integer GLOBAL | SESSION
Com_show_open_tables integer GLOBAL | SESSION
Com_show_plugins integer GLOBAL | SESSION
Com_show_privileges integer GLOBAL | SESSION
Com_show_procedure_code integer GLOBAL | SESSION
Com_show_procedure_status integer GLOBAL | SESSION
Com_show_processlist integer GLOBAL | SESSION
Com_show_profile integer GLOBAL | SESSION
Com_show_profiles integer GLOBAL | SESSION
Com_show_relaylog_events integer GLOBAL | SESSION
Com_show_slave_hosts integer GLOBAL | SESSION
Com_show_slave_status integer GLOBAL | SESSION
Com_show_status integer GLOBAL | SESSION
Com_show_storage_engines integer GLOBAL | SESSION
Com_show_table_status integer GLOBAL | SESSION
Com_show_tables integer GLOBAL | SESSION
Com_show_triggers integer GLOBAL | SESSION
Com_show_variables integer GLOBAL | SESSION
Com_show_warnings integer GLOBAL | SESSION
Com_signal integer GLOBAL | SESSION
Com_slave_start integer GLOBAL | SESSION
Com_slave_stop integer GLOBAL | SESSION
Com_stmt_close integer GLOBAL | SESSION
Com_stmt_execute integer GLOBAL | SESSION
Com_stmt_fetch integer GLOBAL | SESSION
Com_stmt_prepare integer GLOBAL | SESSION
Com_stmt_reprepare integer GLOBAL | SESSION
Com_stmt_reset integer GLOBAL | SESSION
Com_stmt_send_long_data integer GLOBAL | SESSION
Com_truncate integer GLOBAL | SESSION
Com_uninstall_plugin integer GLOBAL | SESSION
Com_unlock_tables integer GLOBAL | SESSION
Com_update integer GLOBAL | SESSION
Com_update_multi integer GLOBAL | SESSION
Com_xa_commit integer GLOBAL | SESSION
Com_xa_end integer GLOBAL | SESSION
Com_xa_prepare integer GLOBAL | SESSION
Com_xa_recover integer GLOBAL | SESSION
Com_xa_rollback integer GLOBAL | SESSION
Com_xa_start integer GLOBAL | SESSION
Compression integer SESSION
Connection_errors_accept integer GLOBAL
Connection_errors_internal integer GLOBAL
Connection_errors_max_connections integer GLOBAL
Connection_errors_peer_addr integer GLOBAL
Connection_errors_select integer GLOBAL
Connection_errors_tcpwrap integer GLOBAL
Connections integer GLOBAL
Created_tmp_disk_tables integer GLOBAL | SESSION
Created_tmp_files integer GLOBAL
Created_tmp_tables integer GLOBAL | SESSION
Delayed_errors integer GLOBAL
Delayed_insert_threads integer GLOBAL
Delayed_writes integer GLOBAL
Firewall_access_denied integer GLOBAL
Firewall_access_granted integer GLOBAL
Firewall_cached_entries integer GLOBAL
Flush_commands integer GLOBAL
Handler_commit integer GLOBAL | SESSION
Handler_delete integer GLOBAL | SESSION
Handler_discover integer GLOBAL | SESSION
Handler_external_lock integer GLOBAL | SESSION
Handler_mrr_init integer GLOBAL | SESSION
Handler_prepare integer GLOBAL | SESSION
Handler_read_first integer GLOBAL | SESSION
Handler_read_key integer GLOBAL | SESSION
Handler_read_last integer GLOBAL | SESSION
Handler_read_next integer GLOBAL | SESSION
Handler_read_prev integer GLOBAL | SESSION
Handler_read_rnd integer GLOBAL | SESSION
Handler_read_rnd_next integer GLOBAL | SESSION
Handler_rollback integer GLOBAL | SESSION
Handler_savepoint integer GLOBAL | SESSION
Handler_savepoint_rollback integer GLOBAL | SESSION
Handler_update integer GLOBAL | SESSION
Handler_write integer GLOBAL | SESSION
Innodb_available_undo_logs integer GLOBAL
Innodb_buffer_pool_bytes_data integer GLOBAL
Innodb_buffer_pool_bytes_dirty integer GLOBAL
Innodb_buffer_pool_dump_status string GLOBAL
Innodb_buffer_pool_load_status string GLOBAL
Innodb_buffer_pool_pages_data integer GLOBAL
Innodb_buffer_pool_pages_dirty integer GLOBAL
Innodb_buffer_pool_pages_flushed integer GLOBAL
Innodb_buffer_pool_pages_free integer GLOBAL
Innodb_buffer_pool_pages_latched integer GLOBAL
Innodb_buffer_pool_pages_misc integer GLOBAL
Innodb_buffer_pool_pages_total integer GLOBAL
Innodb_buffer_pool_read_ahead integer GLOBAL
Innodb_buffer_pool_read_ahead_evicted integer GLOBAL
Innodb_buffer_pool_read_requests integer GLOBAL
Innodb_buffer_pool_reads integer GLOBAL
Innodb_buffer_pool_wait_free integer GLOBAL
Innodb_buffer_pool_write_requests integer GLOBAL
Innodb_data_fsyncs integer GLOBAL
Innodb_data_pending_fsyncs integer GLOBAL
Innodb_data_pending_reads integer GLOBAL
Innodb_data_pending_writes integer GLOBAL
Innodb_data_read integer GLOBAL
Innodb_data_reads integer GLOBAL
Innodb_data_writes integer GLOBAL
Innodb_data_written integer GLOBAL
Innodb_dblwr_pages_written integer GLOBAL
Innodb_dblwr_writes integer GLOBAL
Innodb_have_atomic_builtins integer GLOBAL
Innodb_log_waits integer GLOBAL
Innodb_log_write_requests integer GLOBAL
Innodb_log_writes integer GLOBAL
Innodb_num_open_files integer GLOBAL
Innodb_os_log_fsyncs integer GLOBAL
Innodb_os_log_pending_fsyncs integer GLOBAL
Innodb_os_log_pending_writes integer GLOBAL
Innodb_os_log_written integer GLOBAL
Innodb_page_size integer GLOBAL
Innodb_pages_created integer GLOBAL
Innodb_pages_read integer GLOBAL
Innodb_pages_written integer GLOBAL
Innodb_row_lock_current_waits integer GLOBAL
Innodb_row_lock_time integer GLOBAL
Innodb_row_lock_time_avg integer GLOBAL
Innodb_row_lock_time_max integer GLOBAL
Innodb_row_lock_waits integer GLOBAL
Innodb_rows_deleted integer GLOBAL
Innodb_rows_inserted integer GLOBAL
Innodb_rows_read integer GLOBAL
Innodb_rows_updated integer GLOBAL
Innodb_truncated_status_writes integer GLOBAL
Key_blocks_not_flushed integer GLOBAL
Key_blocks_unused integer GLOBAL
Key_blocks_used integer GLOBAL
Key_read_requests integer GLOBAL
Key_reads integer GLOBAL
Key_write_requests integer GLOBAL
Key_writes integer GLOBAL
Last_query_cost numeric SESSION
Last_query_partial_plans integer SESSION
Max_used_connections integer GLOBAL
Ndb_api_bytes_received_count integer GLOBAL
Ndb_api_bytes_received_count_session integer SESSION
Ndb_api_bytes_received_count_slave integer GLOBAL
Ndb_api_bytes_sent_count integer GLOBAL
Ndb_api_bytes_sent_count_session integer SESSION
Ndb_api_bytes_sent_count_slave integer GLOBAL
Ndb_api_event_bytes_count integer GLOBAL
Ndb_api_event_bytes_count_injector integer GLOBAL
Ndb_api_event_data_count integer GLOBAL
Ndb_api_event_data_count_injector integer GLOBAL
Ndb_api_event_nondata_count integer GLOBAL
Ndb_api_event_nondata_count_injector integer GLOBAL
Ndb_api_pk_op_count integer GLOBAL
Ndb_api_pk_op_count_session integer SESSION
Ndb_api_pk_op_count_slave integer GLOBAL
Ndb_api_pruned_scan_count integer GLOBAL
Ndb_api_pruned_scan_count_session integer SESSION
Ndb_api_pruned_scan_count_slave integer GLOBAL
Ndb_api_range_scan_count integer GLOBAL
Ndb_api_range_scan_count_session integer SESSION
Ndb_api_range_scan_count_slave integer GLOBAL
Ndb_api_read_row_count integer GLOBAL
Ndb_api_read_row_count_session integer SESSION
Ndb_api_read_row_count_slave integer GLOBAL
Ndb_api_scan_batch_count integer GLOBAL
Ndb_api_scan_batch_count_session integer SESSION
Ndb_api_scan_batch_count_slave integer GLOBAL
Ndb_api_table_scan_count integer GLOBAL
Ndb_api_table_scan_count_session integer SESSION
Ndb_api_table_scan_count_slave integer GLOBAL
Ndb_api_trans_abort_count integer GLOBAL
Ndb_api_trans_abort_count_session integer SESSION
Ndb_api_trans_abort_count_slave integer GLOBAL
Ndb_api_trans_close_count integer GLOBAL
Ndb_api_trans_close_count_session integer SESSION
Ndb_api_trans_close_count_slave integer GLOBAL
Ndb_api_trans_commit_count integer GLOBAL
Ndb_api_trans_commit_count_session integer SESSION
Ndb_api_trans_commit_count_slave integer GLOBAL
Ndb_api_trans_local_read_row_count integer GLOBAL
Ndb_api_trans_local_read_row_count_session integer SESSION
Ndb_api_trans_local_read_row_count_slave integer GLOBAL
Ndb_api_trans_start_count integer GLOBAL
Ndb_api_trans_start_count_session integer SESSION
Ndb_api_trans_start_count_slave integer GLOBAL
Ndb_api_uk_op_count integer GLOBAL
Ndb_api_uk_op_count_session integer SESSION
Ndb_api_uk_op_count_slave integer GLOBAL
Ndb_api_wait_exec_complete_count integer GLOBAL
Ndb_api_wait_exec_complete_count_session integer SESSION
Ndb_api_wait_exec_complete_count_slave integer GLOBAL
Ndb_api_wait_meta_request_count integer GLOBAL
Ndb_api_wait_meta_request_count_session integer SESSION
Ndb_api_wait_meta_request_count_slave integer GLOBAL
Ndb_api_wait_nanos_count integer GLOBAL
Ndb_api_wait_nanos_count_session integer SESSION
Ndb_api_wait_nanos_count_slave integer GLOBAL
Ndb_api_wait_scan_result_count integer GLOBAL
Ndb_api_wait_scan_result_count_session integer SESSION
Ndb_api_wait_scan_result_count_slave integer GLOBAL
Ndb_cluster_node_id integer GLOBAL | SESSION
Ndb_config_from_host integer GLOBAL | SESSION
Ndb_config_from_port integer GLOBAL | SESSION
Ndb_conflict_fn_epoch integer GLOBAL
Ndb_conflict_fn_epoch_trans integer GLOBAL
Ndb_conflict_fn_epoch2 integer GLOBAL
Ndb_conflict_fn_epoch2_trans integer GLOBAL
Ndb_conflict_fn_max integer GLOBAL
Ndb_conflict_fn_max_del_win integer GLOBAL
Ndb_conflict_fn_old integer GLOBAL
Ndb_conflict_last_stable_epoch integer GLOBAL
Ndb_conflict_reflected_op_discard_count integer GLOBAL
Ndb_conflict_reflected_op_prepare_count integer GLOBAL
Ndb_conflict_refresh_op_count integer GLOBAL
Ndb_conflict_trans_conflict_commit_count integer GLOBAL
Ndb_conflict_trans_detect_iter_count integer GLOBAL
Ndb_conflict_trans_reject_count integer GLOBAL
Ndb_conflict_trans_row_conflict_count integer GLOBAL
Ndb_conflict_trans_row_reject_count integer GLOBAL
Ndb_epoch_delete_delete_count integer GLOBAL
Ndb_execute_count integer GLOBAL
Ndb_last_commit_epoch_server integer GLOBAL
Ndb_last_commit_epoch_session integer SESSION
Ndb_cluster_node_id integer GLOBAL
Ndb_number_of_data_nodes integer GLOBAL
Ndb_pruned_scan_count integer GLOBAL
Ndb_pushed_queries_defined integer GLOBAL
Ndb_pushed_queries_dropped integer GLOBAL
Ndb_pushed_queries_executed integer GLOBAL
Ndb_pushed_reads integer GLOBAL
Ndb_scan_count integer GLOBAL
Not_flushed_delayed_rows integer GLOBAL
Open_files integer GLOBAL
Open_streams integer GLOBAL
Open_table_definitions integer GLOBAL
Open_tables integer GLOBAL | SESSION
Opened_files integer GLOBAL
Opened_table_definitions integer GLOBAL | SESSION
Opened_tables integer GLOBAL | SESSION
Performance_schema_accounts_lost integer GLOBAL
Performance_schema_cond_classes_lost integer GLOBAL
Performance_schema_cond_instances_lost integer GLOBAL
Performance_schema_digest_lost integer GLOBAL
Performance_schema_file_classes_lost integer GLOBAL
Performance_schema_file_handles_lost integer GLOBAL
Performance_schema_file_instances_lost integer GLOBAL
Performance_schema_hosts_lost integer GLOBAL
Performance_schema_locker_lost integer GLOBAL
Performance_schema_mutex_classes_lost integer GLOBAL
Performance_schema_mutex_instances_lost integer GLOBAL
Performance_schema_rwlock_classes_lost integer GLOBAL
Performance_schema_rwlock_instances_lost integer GLOBAL
Performance_schema_session_connect_attrs_lost integer GLOBAL
Performance_schema_socket_classes_lost integer GLOBAL
Performance_schema_socket_instances_lost integer GLOBAL
Performance_schema_stage_classes_lost integer GLOBAL
Performance_schema_statement_classes_lost integer GLOBAL
Performance_schema_table_handles_lost integer GLOBAL
Performance_schema_table_instances_lost integer GLOBAL
Performance_schema_thread_classes_lost integer GLOBAL
Performance_schema_thread_instances_lost integer GLOBAL
Performance_schema_users_lost integer GLOBAL
Prepared_stmt_count integer GLOBAL
Qcache_free_blocks integer GLOBAL
Qcache_free_memory integer GLOBAL
Qcache_hits integer GLOBAL
Qcache_inserts integer GLOBAL
Qcache_lowmem_prunes integer GLOBAL
Qcache_not_cached integer GLOBAL
Qcache_queries_in_cache integer GLOBAL
Qcache_total_blocks integer GLOBAL
Queries integer GLOBAL | SESSION
Questions integer GLOBAL | SESSION
Rpl_semi_sync_master_clients integer GLOBAL
Rpl_semi_sync_master_net_avg_wait_time integer GLOBAL
Rpl_semi_sync_master_net_wait_time integer GLOBAL
Rpl_semi_sync_master_net_waits integer GLOBAL
Rpl_semi_sync_master_no_times integer GLOBAL
Rpl_semi_sync_master_no_tx integer GLOBAL
Rpl_semi_sync_master_status boolean GLOBAL
Rpl_semi_sync_master_timefunc_failures integer GLOBAL
Rpl_semi_sync_master_tx_avg_wait_time integer GLOBAL
Rpl_semi_sync_master_tx_wait_time integer GLOBAL
Rpl_semi_sync_master_tx_waits integer GLOBAL
Rpl_semi_sync_master_wait_pos_backtraverse integer GLOBAL
Rpl_semi_sync_master_wait_sessions integer GLOBAL
Rpl_semi_sync_master_yes_tx integer GLOBAL
Rpl_semi_sync_slave_status boolean GLOBAL
Rsa_public_key string GLOBAL
Select_full_join integer GLOBAL | SESSION
Select_full_range_join integer GLOBAL | SESSION
Select_range integer GLOBAL | SESSION
Select_range_check integer GLOBAL | SESSION
Select_scan integer GLOBAL | SESSION
Slave_heartbeat_period numeric GLOBAL
Slave_last_heartbeat   GLOBAL
Slave_open_temp_tables integer GLOBAL
Slave_received_heartbeats   GLOBAL
Slave_retried_transactions integer GLOBAL
Slave_running boolean GLOBAL
Slow_launch_threads integer GLOBAL | SESSION
Slow_queries integer GLOBAL | SESSION
Sort_merge_passes integer GLOBAL | SESSION
Sort_range integer GLOBAL | SESSION
Sort_rows integer GLOBAL | SESSION
Sort_scan integer GLOBAL | SESSION
Ssl_accept_renegotiates integer GLOBAL
Ssl_accepts integer GLOBAL
Ssl_callback_cache_hits integer GLOBAL
Ssl_cipher string GLOBAL | SESSION
Ssl_cipher_list string GLOBAL | SESSION
Ssl_client_connects integer GLOBAL
Ssl_connect_renegotiates integer GLOBAL
Ssl_ctx_verify_depth integer GLOBAL
Ssl_ctx_verify_mode integer GLOBAL
Ssl_default_timeout integer GLOBAL | SESSION
Ssl_finished_accepts integer GLOBAL
Ssl_finished_connects integer GLOBAL
Ssl_server_not_after integer GLOBAL | SESSION
Ssl_server_not_before integer GLOBAL | SESSION
Ssl_session_cache_hits integer GLOBAL
Ssl_session_cache_misses integer GLOBAL
Ssl_session_cache_mode string GLOBAL
Ssl_session_cache_overflows integer GLOBAL
Ssl_session_cache_size integer GLOBAL
Ssl_session_cache_timeouts integer GLOBAL
Ssl_sessions_reused integer GLOBAL | SESSION
Ssl_used_session_cache_entries integer GLOBAL
Ssl_verify_depth integer GLOBAL | SESSION
Ssl_verify_mode integer GLOBAL | SESSION
Ssl_version string GLOBAL | SESSION
Table_locks_immediate integer GLOBAL
Table_locks_waited integer GLOBAL
Table_open_cache_hits integer GLOBAL | SESSION
Table_open_cache_misses integer GLOBAL | SESSION
Table_open_cache_overflows integer GLOBAL | SESSION
Tc_log_max_pages_used integer GLOBAL
Tc_log_page_size integer GLOBAL
Tc_log_page_waits integer GLOBAL
Threads_cached integer GLOBAL
Threads_connected integer GLOBAL
Threads_created integer GLOBAL
Threads_running integer GLOBAL
Uptime integer GLOBAL
Uptime_since_flush_status integer GLOBAL
validate_password_dictionary_file_last_parsed datetime GLOBAL
validate_password_dictionary_file_words_count integer GLOBAL

 

show status 결과중에서 Com_XXX 로 시작하는 값들은 모두 mysql 쿼리와 관련된 값이라고 보시면 됩니다.

DB 서버가 읽기가 많은지 쓰기가 많은지 확인하면 됩니다.

 

Handler_XXX 로 시작되는 값들은 튜닝에 중요한 정보를 제공해 주므로 반드시 한번씩 확인해보시기 바랍니다.

 

Handler read prev문서 923.1 k The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY … DESC.
Handler read rnd문서 6.8 M The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler read rnd next문서 3.9 M The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

위의 값들의 값이 시간에 따라서 계속 증가한다면 DB 테이블들이 설계가 잘못되어 있다고 보시면 됩니다.

특히 read_rnd 나 read_rnd_next의 경우에는 index 를 사용하지 않는 테이블들을 조인할 경우나 index key 없이 select 하는 경우등 이므로 테이블들 구조와 DB프로그램의 쿼리문들을 잘 살펴보시기 바랍니다.

 

음... 결국 쿼리에 문제가 있다?

 

* Key_xx 은 MyISAM DB와 관계 되는 값으로(Innodb에도 영향을 주는지는 확인하지 못했으니 확인부탁드립니다.)

Key read ratio % 0.01 % Key cache miss calculated as rate of physical reads compared to read requests (calculated value)
Key read requests문서 374.4 M The number of requests to read a key block from the cache.
Key reads문서 44.5 k The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.

 

* Qcache_XXX 값들은 query cache와 관계되는 값들로 아래의 값들을 유심히 확인해보시면 됩니다.

Qcache free blocks문서 8 k 쿼리 캐시에 비어있는 메모리 블럭 수. 숫자가 크다면 파편화 문제가 있을 수 있으며, FLUSH QUERY CACHE 구문으로 해결할 할 수도 있습니다.
Qcache free memory문서 18.4 M 쿼리 캐시의 빈 메모리 양.
Qcache hits문서 781.9 k 캐시 히트 수.
Qcache inserts문서 274.5 k 캐시에 추가된 쿼리의 수.
Qcache lowmem prunes문서 10.1 k 새 쿼리를 캐시하기 위해 메모리에서 삭제된 캐시의 수. 이 값은 쿼리 캐시 사이즈를 조정하는데 도움이 될겁니다. 쿼리캐시는 LRU 알고리즘을 사용하여 삭제할 쿼리를 선택합니다.
Qcache not cached문서 427 캐시되지 않은 쿼리의 수(캐시 불가능한 것 또는 query_cache_type 설정때문에 캐시되지 않은 것).
Qcache queries in cache문서 19.7 k 캐시에 등록된 쿼리 수.
Qcache total blocks문서 51.3 k 쿼리 캐시의 총 블럭 수.

서버에 DB프로그램에서 사용하는 쿼리를 계속 날려보시면 히트률이 증가하는것을 볼 수 있습니다.

 

* sort_buffer_size - status 값중에서 Sort_merge_passes값을 확인해서 이 값들이 계속 증가한다면 sort_buffer_size가 작다는 이야기 이므로 늘려주시면 됩니다.

Sort merge passes문서 0

The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

 

Select_full_join, Select_range_check - show staus 결과 값이 0  이상인 경우에는 쿼리상 index 가 없이 table을 scan 하고 있다는 이야기 입니다.

Select full join문서 15 인덱스를 사용하지 않은 조인의 수. 이 값이 0이 아니라면 테이블의 인덱스들을 주의 깊게 체크하세요.
Select full range join문서 0 레퍼런스 테이블의 레인지 검색에 사용된 조인의 수.
Select range문서 53.4 k The number of joins that used ranges on the first table. (It's normally not critical even if this is big.)
Select range check문서 0 The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.)
Select scan문서 37.8 k 첫번째 테이블을 풀스캔한 조인의 갯수.

 

 

위로 아래로 댓글로 가기 인쇄