0

The below is the output result of mysqladmin for our mysql server which has been having consistent problems for months since we switched from a dedicated server to the cloud. In the switch, we changed some tuning parameters, and it seems possible that one of these parameters was messed up. I'm the defacto DBA, but don't know much. I'm trying to figure out how to interpret the below:

Server version      5.7.23-0ubuntu0.16.04.1-log
Uptime:         117 days 22 hours 28 min 44 sec

Threads: 29  Questions: 1969788526  Slow queries: 143857  Opens: 11865506181  Flush tables: 1  Open tables: 512  Queries per second avg: 193.311

+------------------------------------------------+--------

| Aborted_clients | 416
| Aborted_connects | 1639
| Binlog_cache_disk_use | 71808
| Binlog_cache_use | 21395190
| Binlog_stmt_cache_disk_use | 0
| Binlog_stmt_cache_use | 93
| Bytes_received | 1354770670850
| Bytes_sent | 3602027204751
| Com_admin_commands | 13164
| Com_assign_to_keycache | 0
| Com_alter_table | 71
| Com_analyze | 1
| Com_begin | 12066261
| Com_binlog | 0
| Com_change_db | 137117
| Com_commit | 22424711
| Com_create_table | 2
| Com_create_trigger | 0
| Com_create_udf | 0
| Com_create_user | 1
| Com_create_view | 0
| Com_dealloc_sql | 70
| Com_delete | 277919
| 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 | 74
| Com_explain_other | 0
| Com_flush | 119
| Com_get_diagnostics | 0
| Com_grant | 3
| Com_ha_close | 0
| Com_ha_open | 0
| Com_ha_read | 0
| Com_help | 0
| Com_insert | 105344680
| Com_insert_select | 259735
| Com_install_plugin | 0
| Com_kill | 8
| Com_load | 0
| Com_lock_tables | 6
| Com_optimize | 0
| Com_preload_keys | 0
| Com_prepare_sql | 74
| 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 | 1
| Com_replace_select | 0
| Com_reset | 0
| Com_resignal | 0
| Com_revoke | 0
| Com_revoke_all | 0
| Com_rollback | 28394
| Com_rollback_to_savepoint | 0
| Com_savepoint | 0
| Com_select | 1502927854
| Com_set_option | 50365426
| Com_signal | 0
| Com_show_binlog_events | 0
| Com_show_binlogs | 4
| Com_show_charsets | 7149
| Com_show_collations | 7149
| Com_show_create_table | 101370273
| Com_show_create_trigger | 0
| Com_show_databases | 8
| Com_show_engine_logs | 0
| Com_show_engine_mutex | 0
| Com_show_engine_status | 27
| Com_show_events | 0
| Com_show_errors | 0
| Com_show_fields | 101370995
| Com_show_function_code | 0
| Com_show_function_status | 0
| Com_show_grants | 4
| Com_show_keys | 589
| Com_show_master_status | 2
| Com_show_open_tables | 0
| Com_show_plugins | 0
| Com_show_privileges | 0
| Com_show_procedure_code | 0
| Com_show_procedure_status | 0
| Com_show_processlist | 5
| Com_show_profile | 0
| Com_show_profiles | 0
| Com_show_relaylog_events | 0
| Com_show_slave_hosts | 4
| Com_show_slave_status | 6
| Com_show_status | 38
| Com_show_storage_engines | 4
| Com_show_table_status | 136
| Com_show_tables | 88
| Com_show_triggers | 116
| Com_show_variables | 7287
| Com_show_warnings | 8047
| Com_show_create_user | 0
| Com_shutdown | 0
| Com_group_replication_start | 0
| Com_group_replication_stop | 0
| Com_stmt_execute | 74
| Com_stmt_close | 72
| Com_stmt_fetch | 0
| Com_stmt_prepare | 74
| Com_stmt_reset | 0
| Com_stmt_send_long_data | 0
| Com_truncate | 0
| Com_uninstall_plugin | 0
| Com_unlock_tables | 6
| Com_update | 57786921
| 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
| Com_stmt_reprepare | 0
| Compression | OFF
| Connections | 29435898
| Created_tmp_disk_tables | 114402068
| Created_tmp_files | 38275
| Created_tmp_tables | 2504646759
| Delayed_errors | 0
| Delayed_insert_threads | 0
| Delayed_writes | 0
| Flush_commands | 1
| Handler_commit | 1757942766
| Handler_delete | 52633379
| Handler_discover | 0
| Handler_external_lock | 27091706448
| Handler_mrr_init | 0
| Handler_prepare | 342205456
| Handler_read_first | 4803251242
| Handler_read_key | 4225308943902
| Handler_read_last | 40865194
| Handler_read_next | 6485209270841
| Handler_read_prev | 10366415789
| Handler_read_rnd | 171538509
| Handler_read_rnd_next | 3848495865600
| Handler_rollback | 48600
| Handler_savepoint | 0
| Handler_savepoint_rollback | 0
| Handler_update | 87510208
| Handler_write | 1055828705804
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |

| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 181025 2:58:45 |

| Innodb_buffer_pool_resize_status |
| Innodb_buffer_pool_pages_data | 313229
| Innodb_buffer_pool_bytes_data | 5131943936
| Innodb_buffer_pool_pages_dirty | 978
| Innodb_buffer_pool_bytes_dirty | 16023552
| Innodb_buffer_pool_pages_flushed | 8590250
| Innodb_buffer_pool_pages_free | 8262
| Innodb_buffer_pool_pages_misc | 6149
| Innodb_buffer_pool_pages_total | 327640
| Innodb_buffer_pool_read_ahead_rnd | 0
| Innodb_buffer_pool_read_ahead | 303530
| Innodb_buffer_pool_read_ahead_evicted | 0
| Innodb_buffer_pool_read_requests | 26856935971072
| Innodb_buffer_pool_reads | 304296
| Innodb_buffer_pool_wait_free | 0
| Innodb_buffer_pool_write_requests | 4292568734
| Innodb_data_fsyncs | 31123982
| Innodb_data_pending_fsyncs | 0
| Innodb_data_pending_reads | 0
| Innodb_data_pending_writes | 0
| Innodb_data_read | 10002846208
| Innodb_data_reads | 610655
| Innodb_data_writes | 38339619
| Innodb_data_written | 397157095424
| Innodb_dblwr_pages_written | 8390289
| Innodb_dblwr_writes | 543675
| Innodb_log_waits | 0
| Innodb_log_write_requests | 196703019
| Innodb_log_writes | 29155331
| Innodb_os_log_fsyncs | 29182283
| Innodb_os_log_pending_fsyncs | 0
| Innodb_os_log_pending_writes | 0
| Innodb_os_log_written | 118934181888
| Innodb_page_size | 16384
| Innodb_pages_created | 382512
| Innodb_pages_read | 610519
| Innodb_pages_written | 8590462
| Innodb_row_lock_current_waits | 0
| Innodb_row_lock_time | 7111105
| Innodb_row_lock_time_avg | 4320
| Innodb_row_lock_time_max | 51061
| Innodb_row_lock_waits | 1646
| Innodb_rows_deleted | 52633379
| Innodb_rows_inserted | 2211335475
| Innodb_rows_read | 12988670090784
| Innodb_rows_updated | 62207650
| Innodb_num_open_files | 127
| Innodb_truncated_status_writes | 0
| Innodb_available_undo_logs | 128
| Key_blocks_not_flushed | 0
| Key_blocks_unused | 53585
| Key_blocks_used | 8
| Key_read_requests | 2077
| Key_reads | 184
| Key_write_requests | 684
| Key_writes | 20
| Last_query_cost | 0.000000
| Last_query_partial_plans | 0
| Locked_connects | 0
| Max_execution_time_exceeded | 0
| Max_execution_time_set | 0
| Max_execution_time_set_failed | 0
| Max_used_connections | 37
| Max_used_connections_time | 2018-11-27 20:52:01
| Not_flushed_delayed_rows | 0
| Ongoing_anonymous_transaction_count | 0
| Open_files | 3
| Open_streams | 0
| Open_table_definitions | 343
| Open_tables | 512
| Opened_files | 859373
| Opened_table_definitions | 488
| Opened_tables | 11867165737
| 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 | 63
| Performance_schema_hosts_lost | 0
| Performance_schema_index_stat_lost | 0
| Performance_schema_locker_lost | 0
| Performance_schema_memory_classes_lost | 0
| Performance_schema_metadata_lock_lost | 0
| Performance_schema_mutex_classes_lost | 0
| Performance_schema_mutex_instances_lost | 0
| Performance_schema_nested_statement_lost | 0
| Performance_schema_prepared_statements_lost | 0
| Performance_schema_program_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_table_lock_stat_lost | 0
| Performance_schema_thread_classes_lost | 0
| Performance_schema_thread_instances_lost | 0
| Performance_schema_users_lost | 0
| Prepared_stmt_count | 0
| Qcache_not_cached | 26282360
| Queries | 1983838412
| Questions | 1983825241
| Select_full_join | 2510
| Select_full_range_join | 0
| Select_range | 133055836
| Select_range_check | 0
| Select_scan | 4834557364
| Slow_launch_threads | 0
| Slow_queries | 144451
| Sort_merge_passes | 8779
| Sort_range | 5238452
| Sort_rows | 344471904
| Sort_scan | 24075234
| Table_locks_immediate | 11412632
| Table_locks_waited | 0
| Table_open_cache_hits | 1881438893
| Table_open_cache_misses | 11867165733
| Table_open_cache_overflows | 11867164202
| Tc_log_max_pages_used | 0
| Tc_log_page_size | 0
| Tc_log_page_waits | 0 | Threads_cached | 3 | Threads_connected | 26 | Threads_created | 1761 | Threads_running | 4 | Uptime | 10254430 | Uptime_since_flush_status | 10254430

  • The problem is lagging on the front end. Queries have slowed down significantly and the result is a 20-50x slowdown on the front end. We have a chat that used to update every 200ms that now takes up to 10 seconds (yes I know using mysql for chat is a bad idea, and we're working on that, but that doesn't explain the global slowdown) – scl Feb 20 at 19:23
  • I need to see SHOW VARIABLES and how much RAM you have; then I can critique it. – Rick James Mar 4 at 23:04
  • Please post TEXT results of SHOW GLOBAL VARIABLES; to pastebin.com and share the links with us. How much RAM does your server have available? – Wilson Hauck Mar 9 at 20:15
  • @scl Any chance you can post to pastebin.com and share the pastebin link for the results of SHOW GLOBAL VARIABLES; ? And how much RAM is on the server? Disclaimer: I am the author of www.mysqlservertuning.com listed in my profile, Network profile. – Wilson Hauck Mar 16 at 13:34
  • @scl Posting SHOW GLOBAL VARIABLES; to pastebin.com and sharing the link would allow specific tuning Suggestions to improve your instance performance. – Wilson Hauck Mar 23 at 23:54
0

After increasing the table_open_cache until the rate of the statistic Table_open_cache_misses / Table_open_cache_overflows stays largely still for a minute.

After that you'll need to look at the queries in your slow query log. Statistics (Handler_read_rnd_next) shows a large number of full range scans. Which cannot be fixed by tuning.

You rarely can tune yourself out of a performance problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.