Mysql – Are these stats reasonable? If not, where should I start

MySQLperformanceperformance-tuning

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

Best Answer

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.