Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people do not know what to do about the interpretation of MySQL show status key results and suggestions to improve performance. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Example:

The show status results are as follows:

Mysql > show status

+-+ +

| | Variable_name | Value |

+-+ +

| | Aborted_clients | 3353 | |

| | Aborted_connects | 14 | |

| | Binlog_cache_disk_use | 10751 | |

| | Binlog_cache_use | 10079768 | |

| | Binlog_stmt_cache_disk_use | 0 | |

| | Binlog_stmt_cache_use | 46 | |

| | Bytes_received | 888 |

| | Bytes_sent | 79686 | |

| | 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_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_check | 0 | |

| | Com_checksum | 0 | |

| | Com_commit | 0 | |

| | Com_create_db | 0 | |

| | Com_create_event | 0 | |

| | Com_create_function | 0 | |

| | Com_create_index | 0 | |

| | Com_create_procedure | 0 | |

| | Com_create_server | 0 | |

| | Com_create_table | 0 | |

| | Com_create_trigger | 0 | |

| | Com_create_udf | 0 | |

| | Com_create_user | 0 | |

| | Com_create_view | 0 | |

| | Com_dealloc_sql | 0 | |

| | Com_delete | 0 | |

| | Com_delete_multi | 0 | |

| | Com_do | 0 | |

| | Com_drop_db | 0 | |

| | Com_drop_event | 0 | |

| | Com_drop_function | 0 | |

| | Com_drop_index | 0 | |

| | Com_drop_procedure | 0 | |

| | Com_drop_server | 0 | |

| | Com_drop_table | 0 | |

| | Com_drop_trigger | 0 | |

| | Com_drop_user | 0 | |

| | Com_drop_view | 0 | |

| | Com_empty_query | 0 | |

| | Com_execute_sql | 0 | |

| | Com_flush | 0 | |

| | Com_get_diagnostics | 0 | |

| | Com_grant | 0 | |

| | Com_ha_close | 0 | |

| | Com_ha_open | 0 | |

| | Com_ha_read | 0 | |

| | Com_help | 0 | |

| | Com_insert | 0 | |

| | Com_insert_select | 0 | |

| | Com_install_plugin | 0 | |

| | Com_kill | 0 | |

| | Com_load | 0 | |

| | Com_lock_tables | 0 | |

| | Com_optimize | 0 | |

| | Com_preload_keys | 0 | |

| | Com_prepare_sql | 0 | |

| | Com_purge | 0 | |

| | Com_purge_before_date | 0 | |

| | Com_release_savepoint | 0 | |

| | Com_rename_table | 0 | |

| | Com_rename_user | 0 | |

| | Com_repair | 0 | |

| | Com_replace | 0 | |

| | Com_replace_select | 0 | |

| | Com_reset | 0 | |

| | Com_resignal | 0 | |

| | Com_revoke | 0 | |

| | Com_revoke_all | 0 | |

| | Com_rollback | 0 | |

| | Com_rollback_to_savepoint | 0 | |

| | Com_savepoint | 0 | |

| | Com_select | 1 | |

| | Com_set_option | 0 | |

| | Com_signal | 0 | |

| | Com_show_binlog_events | 0 | |

| | Com_show_binlogs | 0 | |

| | Com_show_charsets | 0 | |

| | Com_show_collations | 0 | |

| | Com_show_create_db | 0 | |

| | Com_show_create_event | 0 | |

| | Com_show_create_func | 0 | |

| | Com_show_create_proc | 0 | |

| | Com_show_create_table | 0 | |

| | Com_show_create_trigger | 0 | |

| | Com_show_databases | 0 | |

| | Com_show_engine_logs | 0 | |

| | Com_show_engine_mutex | 0 | |

| | Com_show_engine_status | 0 | |

| | Com_show_events | 0 | |

| | Com_show_errors | 0 | |

| | Com_show_fields | 0 | |

| | Com_show_function_code | 0 | |

| | Com_show_function_status | 0 | |

| | Com_show_grants | 0 | |

| | Com_show_keys | 0 | |

| | Com_show_master_status | 0 | |

| | Com_show_open_tables | 1 | |

| | Com_show_plugins | 0 | |

| | Com_show_privileges | 0 | |

| | Com_show_procedure_code | 0 | |

| | Com_show_procedure_status | 0 | |

| | Com_show_processlist | 2 | |

| | Com_show_profile | 0 | |

| | Com_show_profiles | 0 | |

| | Com_show_relaylog_events | 0 | |

| | Com_show_slave_hosts | 0 | |

| | Com_show_slave_status | 0 | |

| | Com_show_status | 11 | |

| | Com_show_storage_engines | 0 | |

| | Com_show_table_status | 0 | |

| | Com_show_tables | 0 | |

| | Com_show_triggers | 0 | |

| | Com_show_variables | 5 | |

| | Com_show_warnings | 0 | |

| | Com_slave_start | 0 | |

| | Com_slave_stop | 0 | |

| | Com_stmt_close | 0 | |

| | Com_stmt_execute | 0 | |

| | Com_stmt_fetch | 0 | |

| | Com_stmt_prepare | 0 | |

| | Com_stmt_reprepare | 0 | |

| | Com_stmt_reset | 0 | |

| | Com_stmt_send_long_data | 0 | |

| | Com_truncate | 0 | |

| | Com_uninstall_plugin | 0 | |

| | Com_unlock_tables | 0 | |

| | Com_update | 0 | |

| | Com_update_multi | 0 | |

| | Com_xa_commit | 0 | |

| | Com_xa_end | 0 | |

| | Com_xa_prepare | 0 | |

| | Com_xa_recover | 0 | |

| | Com_xa_rollback | 0 | |

| | Com_xa_start | 0 | |

| | Compression | OFF |

| | Connection_errors_accept | 0 | |

| | Connection_errors_internal | 0 | |

| | Connection_errors_max_connections | 0 | |

| | Connection_errors_peer_address | 0 | |

| | Connection_errors_select | 0 | |

| | Connection_errors_tcpwrap | 0 | |

| | Connections | 711349 | |

| | Created_tmp_disk_tables | 0 | |

| | Created_tmp_files | 1052 | |

| | Created_tmp_tables | 6 | |

| | Delayed_errors | 0 | |

| | Delayed_insert_threads | 0 | |

| | Delayed_writes | 0 | |

| | Flush_commands | 1 | |

| | Handler_commit | 0 | |

| | Handler_delete | 0 | |

| | Handler_discover | 0 | |

| | Handler_external_lock | 0 | |

| | Handler_mrr_init | 0 | |

| | Handler_prepare | 0 | |

| | Handler_read_first | 0 | |

| | Handler_read_key | 0 | |

| | Handler_read_last | 0 | |

| | Handler_read_next | 0 | |

| | Handler_read_prev | 0 | |

| | Handler_read_rnd | 0 | |

| | Handler_read_rnd_next | 868 |

| | Handler_rollback | 0 | |

| | Handler_savepoint | 0 | |

| | Handler_savepoint_rollback | 0 | |

| | Handler_update | 0 | |

| | Handler_write | 862 |

| | Innodb_buffer_pool_dump_status | not started |

| | Innodb_buffer_pool_load_status | not started |

| | Innodb_buffer_pool_pages_data | 2559033 | |

| | Innodb_buffer_pool_bytes_data | 41927196672 | |

| | Innodb_buffer_pool_pages_dirty | 1682 | |

| | Innodb_buffer_pool_bytes_dirty | 27557888 | |

| | Innodb_buffer_pool_pages_flushed | 18146851 | |

| | Innodb_buffer_pool_pages_free | 8201 | |

| | Innodb_buffer_pool_pages_misc | 54198 | |

| | Innodb_buffer_pool_pages_total | 2621432 | |

| | Innodb_buffer_pool_read_ahead_rnd | 0 | |

| | Innodb_buffer_pool_read_ahead | 14299811 | |

| | Innodb_buffer_pool_read_ahead_evicted | 580 |

| | Innodb_buffer_pool_read_requests | 1860737979861 | |

| | Innodb_buffer_pool_reads | 6326772 | |

| | Innodb_buffer_pool_wait_free | 0 | |

| | Innodb_buffer_pool_write_requests | 363623332 | |

| | Innodb_data_fsyncs | 15016048 | |

| | Innodb_data_pending_fsyncs | 1 | |

| | Innodb_data_pending_reads | 0 | |

| | Innodb_data_pending_writes | 0 | |

| | Innodb_data_read | 338241392640 | |

| | Innodb_data_reads | 20647009 | |

| | Innodb_data_writes | 28867196 | |

| | Innodb_data_written | 665827024896 | |

| | Innodb_dblwr_pages_written | 18146851 | |

| | Innodb_dblwr_writes | 3470670 | |

| | Innodb_have_atomic_builtins | ON |

| | Innodb_log_waits | 23 | |

| | Innodb_log_write_requests | 164460745 | |

| | Innodb_log_writes | 7043515 | |

| | Innodb_os_log_fsyncs | 7225284 | |

| | Innodb_os_log_pending_fsyncs | 1 | |

| | Innodb_os_log_pending_writes | 0 | |

| | Innodb_os_log_written | 71105871872 | |

| | Innodb_page_size | 16384 | |

| | Innodb_pages_created | 1429908 | |

| | Innodb_pages_read | 20644117 | |

| | Innodb_pages_written | 18146851 | |

| | Innodb_row_lock_current_waits | 11 | |

| | Innodb_row_lock_time | 1639372785 | |

| | Innodb_row_lock_time_avg | 1540 | |

| | Innodb_row_lock_time_max | 51737 | |

| | Innodb_row_lock_waits | 1064015 | |

| | Innodb_rows_deleted | 489355 | |

| | Innodb_rows_inserted | 23323344 | |

| | Innodb_rows_read | 2231512760378 | |

| | Innodb_rows_updated | 71091492 | |

| | Innodb_num_open_files | 1044 | |

| | Innodb_truncated_status_writes | 0 | |

| | Innodb_available_undo_logs | 128 | |

| | Key_blocks_not_flushed | 0 | |

| | Key_blocks_unused | 6696 | |

| | Key_blocks_used | 6698 | |

| | Key_read_requests | 4852138201 | |

| | Key_reads | 85481134 | |

| | Key_write_requests | 1285475383 | |

| | Key_writes | 110483395 | |

| | Last_query_cost | 10.499000 | |

| | Last_query_partial_plans | 1 | |

| | Max_used_connections | 294 |

| | Not_flushed_delayed_rows | 0 | |

| | Open_files | 51 | |

| | Open_streams | 0 | |

| | Open_table_definitions | 732 |

| | Open_tables | 1842 | |

| | Opened_files | 1821736 | |

| | Opened_table_definitions | 0 | |

| | Opened_tables | 0 | |

| | Performance_schema_accounts_lost | 0 | |

| | Performance_schema_cond_classes_lost | 0 | |

| | Performance_schema_cond_instances_lost | 0 | |

| | Performance_schema_digest_lost | 0 | |

| | Performance_schema_file_classes_lost | 0 | |

| | Performance_schema_file_handles_lost | 0 | |

| | Performance_schema_file_instances_lost | 0 | |

| | Performance_schema_hosts_lost | 0 | |

| | Performance_schema_locker_lost | 0 | |

| | Performance_schema_mutex_classes_lost | 0 | |

| | Performance_schema_mutex_instances_lost | 0 | |

| | Performance_schema_rwlock_classes_lost | 0 | |

| | Performance_schema_rwlock_instances_lost | 0 | |

| | Performance_schema_session_connect_attrs_lost | 0 | |

| | Performance_schema_socket_classes_lost | 0 | |

| | Performance_schema_socket_instances_lost | 0 | |

| | Performance_schema_stage_classes_lost | 0 | |

| | Performance_schema_statement_classes_lost | 0 | |

| | Performance_schema_table_handles_lost | 0 | |

| | Performance_schema_table_instances_lost | 0 | |

| | Performance_schema_thread_classes_lost | 0 | |

| | Performance_schema_thread_instances_lost | 0 | |

| | Performance_schema_users_lost | 0 | |

| | Prepared_stmt_count | 0 | |

| | Qcache_free_blocks | 1 | |

| | Qcache_free_memory | 1031352 | |

| | Qcache_hits | 0 | |

| | Qcache_inserts | 0 | |

| | Qcache_lowmem_prunes | 0 | |

| | Qcache_not_cached | 188110198 | |

| | Qcache_queries_in_cache | 0 | |

| | Qcache_total_blocks | 1 | |

| | Queries | 1116883223 | |

| | Questions | 20 |

| | Select_full_join | 0 | |

| | Select_full_range_join | 0 | |

| | Select_range | 0 | |

| | Select_range_check | 0 | |

| | Select_scan | 6 | |

| | Slave_heartbeat_period | 0.000 | |

| | Slave_last_heartbeat |

| | Slave_open_temp_tables | 0 | |

| | Slave_received_heartbeats | 0 | |

| | Slave_retried_transactions | 0 | |

| | Slave_running | OFF |

| | Slow_launch_threads | 0 | |

| | Slow_queries | 0 | |

| | Sort_merge_passes | 0 | |

| | Sort_range | 0 | |

| | Sort_rows | 0 | |

| | Sort_scan | 0 | |

| | Ssl_accept_renegotiates | 0 | |

| | Ssl_accepts | 0 | |

| | Ssl_callback_cache_hits | 0 | |

| | Ssl_cipher |

| | Ssl_cipher_list |

| | Ssl_client_connects | 0 | |

| | Ssl_connect_renegotiates | 0 | |

| | Ssl_ctx_verify_depth | 0 | |

| | Ssl_ctx_verify_mode | 0 | |

| | Ssl_default_timeout | 0 | |

| | Ssl_finished_accepts | 0 | |

| | Ssl_finished_connects | 0 | |

| | Ssl_server_not_after |

| | Ssl_server_not_before |

| | Ssl_session_cache_hits | 0 | |

| | Ssl_session_cache_misses | 0 | |

| | Ssl_session_cache_mode | NONE |

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

| | Ssl_verify_mode | 0 | |

| | Ssl_version |

| | Table_locks_immediate | 114434187 | |

| | Table_locks_waited | 0 | |

| | Table_open_cache_hits | 0 | |

| | Table_open_cache_misses | 0 | |

| | Table_open_cache_overflows | 0 | |

| | Tc_log_max_pages_used | 0 | |

| | Tc_log_page_size | 0 | |

| | Tc_log_page_waits | 0 | |

| | Threads_cached | 15 |

| | Threads_connected | 128 | |

| | Threads_created | 1299 | |

| | Threads_running | 22 | |

| | Uptime | 753095 | |

| | Uptime_since_flush_status | 753095 | |

+-+ +

341 rows in set (0.00 sec)

Aborted_clients the number of connections that have been abandoned because the customer did not close the connection correctly.

The number of times Aborted_connects has attempted a connection to the MySQL server that has failed.

The number of times Connections attempted to connect to the MySQL server.

Created_tmp_tables the number of implicit temporary tables that have been created when the statement is executed.

The number of latency insertion processor threads being used by Delayed_insert_threads.

The number of rows written by Delayed_writes in INSERT DELAYED.

The number of rows written by Delayed_errors with INSERT DELAYED for which some errors (possibly duplicate key values) have occurred.

The number of times Flush_commands executed the FLUSH command.

The number of times Handler_delete requests to delete rows from a table.

The number of times Handler_read_first requests to read the first row in the table.

The Handler_read_key request number is based on the key reading line.

The number of times Handler_read_next requests to read a row based on a key.

The number of times Handler_read_rnd requests to read a row based on a fixed location.

The number of times Handler_update requests to update a row in the table.

The number of times Handler_write requests to insert a row into the table.

The number of blocks used by Key_blocks_used for keyword caching.

The number of times Key_read_requests requests to read a key value from the cache.

The number of times Key_reads physically reads a key value from disk.

Key_write_requests requests the number of times a keyword block is written to the cache.

The number of times Key_writes physically writes a key block to disk.

The maximum number of connections used simultaneously by Max_used_connections.

Not_flushed_key_blocks keyblocks that have been changed in the key cache but have not been emptied to disk.

The number of rows that Not_flushed_delayed_rows is waiting to write in the INSERT DELAY queue.

The number of Open_tables open tables.

The number of files opened by Open_files.

Number of Open_streams open streams (mainly used for logging)

The number of tables that Opened_tables has opened.

The number of queries sent by Questions to the server.

The number of queries that Slow_queries takes longer than long_query_time time.

The number of connections currently open by Threads_connected.

The number of threads that Threads_running is not sleeping.

How many seconds did the Uptime server work?

Recommendations for improving performance:

1. If the opened_tables is too large, you should make the table_cache in the parameter file my.cnf larger

two。 If the Key_reads is too large, you should make the key_buffer_size in the my.cnf larger. The cache failure rate can be calculated with Key_reads/Key_read_requests, which can be used in the MyISAM engine

3. If the Handler_read_rnd is too large, many queries in the SQL statements you write scan the entire table without playing the role of index keys

4. If the Threads_created is too large, increase the value of thread_cache_size in the parameter file my.cnf. You can use Threads_created/Connections to calculate the cache hit rate

5. If Created_tmp_disk_tables is too large, increase the value of tmp_table_size in my.cnf and replace disk-based temporary tables with memory-based temporary tables

After reading the above, have you mastered the interpretation of the key results of MySQL show status and the ways to improve performance? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.