Issues with MySQL 8.0.17


Recently we upgraded MySQL version on our UAT database server to 8.0.17 from 5.7.18.

We are facing issues with our web application, Website is loading slowly if there are more users accessing it I mean even if 10-20 users open application simultaneously. We didn’t face this issue when using the previous version of MySQL 5.7.18.

The main difference in MySQL settings is query cache, It’s been deprecated in MySQL 8.0.17, We were using query cache in the previous version could this be a cause? The total size of the database is about 1TB. We are not experiencing any high CPU or Memory issues at the moment.

When reverted back to MySQL 5.7.18 we don’t observe slowness in website loading.

Machine Details:

RAM : 8GB, OS : Windows Server 2012, SSD : 2TB

MySQL Settings from my.ini [Most of them were the same in the older version as well]

default-character-set=utf8mb4 skip_ssl event_scheduler=OFF collation-server = utf8mb4_unicode_ci init-connect='SET NAMES utf8mb4' character-set-server = utf8mb4 port=3306 default-storage-engine=MYISAM sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION" log-output=FILE skip-log-bin lower_case_table_names=1 max_connections=1500 table_open_cache=2000 tmp_table_size=16M thread_cache_size=9 myisam_max_sort_file_size=100G myisam_sort_buffer_size=32M key_buffer_size=3584M read_buffer_size=512K wait_timeout = 480 read_rnd_buffer_size=1M skip-innodb innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=8M innodb_buffer_pool_size=72M innodb_log_file_size=48M innodb_thread_concurrency=8 innodb_autoextend_increment=64M innodb_buffer_pool_instances=8 innodb_concurrency_tickets=5000 innodb_old_blocks_time=1000 innodb_open_files=300 innodb_stats_on_metadata=0 innodb_file_per_table=1 innodb_checksum_algorithm=0 back_log=70 flush_time=0 join_buffer_size=256K max_allowed_packet=1060M max_connect_errors=100 open_files_limit=4110 sort_buffer_size=1M table_definition_cache=1400 binlog_row_event_max_size=8K wait_timeout = 480 sync_master_info=10000 sync_relay_log=10000 sync_relay_log_info=10000 loose-local-infile = 1 

Note: We don’t use InnoDB tables in our application all tables are in MyISAM, I aware that all internal tables are in InnoDB since Mysql version 8. Also, we are not using replication.

In production, we expect about 200-500 users accessing portals simultaneously can anyone please suggest changes in settings for our application loading time to decrease.

SHOW GLOBAL VARIABLES

Aborted_clients : 125 Aborted_connects : 18 Acl_cache_items_count : 0 Binlog_cache_disk_use : 0 Binlog_cache_use : 0 Binlog_stmt_cache_disk_use : 0 Binlog_stmt_cache_use : 0 Bytes_received : 302042435 Bytes_sent : 1270438334  Com_admin_commands : 3492 Com_assign_to_keycache : 0 Com_alter_db : 0 Com_alter_event : 0 Com_alter_function : 0 Com_alter_instance : 0 Com_alter_procedure : 0 Com_alter_resource_group : 0 Com_alter_server : 0 Com_alter_table : 32 Com_alter_tablespace : 0 Com_alter_user : 0 Com_alter_user_default_role : 0 Com_analyze : 0 Com_begin : 0 Com_binlog : 0 Com_call_procedure : 0 Com_change_db : 1228 Com_change_master : 0 Com_change_repl_filter : 0 Com_check : 0 Com_checksum : 0 Com_clone : 0 Com_commit : 0 Com_create_db : 1 Com_create_event : 0 Com_create_function : 0 Com_create_index : 0 Com_create_procedure : 0 Com_create_role : 0 Com_create_server : 0 Com_create_table : 34 Com_create_resource_group : 0 Com_create_trigger : 0 Com_create_udf : 0 Com_create_user : 0 Com_create_view : 0 Com_create_spatial_reference_system : 0 Com_dealloc_sql : 0 Com_delete : 1142 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_resource_group : 0 Com_drop_role : 0 Com_drop_server : 0 Com_drop_spatial_reference_system : 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_explain_other : 0 Com_flush : 1 Com_get_diagnostics : 0 Com_grant : 0 Com_grant_roles : 0 Com_ha_close : 0 Com_ha_open : 0 Com_ha_read : 0 Com_help : 0 Com_import : 0 Com_insert : 58 Com_insert_select : 0 Com_install_component : 0 Com_install_plugin : 0 Com_kill : 0 Com_load : 0 Com_lock_instance : 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_restart : 0 Com_revoke : 0 Com_revoke_all : 0 Com_revoke_roles : 0 Com_rollback : 0 Com_rollback_to_savepoint : 0 Com_savepoint : 0 Com_select : 1374120 Com_set_option : 1363 Com_set_password : 0 Com_set_resource_group : 0 Com_set_role : 0 Com_signal : 0 Com_show_binlog_events : 0 Com_show_binlogs : 0 Com_show_charsets : 0 Com_show_collations : 2 Com_show_create_db : 0 Com_show_create_event : 0 Com_show_create_func : 0 Com_show_create_proc : 0 Com_show_create_table : 103 Com_show_create_trigger : 0 Com_show_databases : 2 Com_show_engine_logs : 0 Com_show_engine_mutex : 0 Com_show_engine_status : 1 Com_show_events : 2 Com_show_errors : 0 Com_show_fields : 0 Com_show_function_code : 0 Com_show_function_status : 38 Com_show_grants : 0 Com_show_keys : 0 Com_show_master_status : 0 Com_show_open_tables : 0 Com_show_plugins : 0 Com_show_privileges : 0 Com_show_procedure_code : 0 Com_show_procedure_status : 38 Com_show_processlist : 0 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 : 4 Com_show_storage_engines : 2 Com_show_table_status : 39 Com_show_tables : 210 Com_show_triggers : 37 Com_show_variables : 3 Com_show_warnings : 0 Com_show_create_user : 0 Com_shutdown : 0 Com_slave_start : 0 Com_slave_stop : 0 Com_group_replication_start : 0 Com_group_replication_stop : 0 Com_stmt_execute : 0 Com_stmt_close : 0 Com_stmt_fetch : 0 Com_stmt_prepare : 0 Com_stmt_reset : 0 Com_stmt_send_long_data : 0 Com_truncate : 0 Com_uninstall_component : 0 Com_uninstall_plugin : 0 Com_unlock_instance : 0 Com_unlock_tables : 0 Com_update : 16311 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 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 : 1383 Created_tmp_disk_tables : 242 Created_tmp_files : 27 Created_tmp_tables : 14513 Current_tls_ca :  Current_tls_capath :  Current_tls_cert :  Current_tls_cipher :  Current_tls_ciphersuites :  Current_tls_crl :  Current_tls_crlpath :  Current_tls_key :  Current_tls_version : TLSv1,TLSv1.1,TLSv1.2 Delayed_errors : 0 Delayed_insert_threads : 0 Delayed_writes : 0 Flush_commands : 3 Handler_commit : 68079 Handler_delete : 295 Handler_discover : 0 Handler_external_lock : 4203749 Handler_mrr_init : 0 Handler_prepare : 0 Handler_read_first : 285016 Handler_read_key : 2656545 Handler_read_last : 1179 Handler_read_next : 11769318 Handler_read_prev : 0 Handler_read_rnd : 814737 Handler_read_rnd_next : 1041353718 Handler_rollback : 0 Handler_savepoint : 0 Handler_savepoint_rollback : 0 Handler_update : 8448 Handler_write : 514575 Innodb_buffer_pool_dump_status : Dumping of buffer pool not started Innodb_buffer_pool_load_status : Buffer pool(s) load completed at 200723  5:52:50 Innodb_buffer_pool_resize_status :  Innodb_buffer_pool_pages_data : 3382 Innodb_buffer_pool_bytes_data : 55410688 Innodb_buffer_pool_pages_dirty : 0 Innodb_buffer_pool_bytes_dirty : 0 Innodb_buffer_pool_pages_flushed : 4885 Innodb_buffer_pool_pages_free : 1024 Innodb_buffer_pool_pages_misc : 202 Innodb_buffer_pool_pages_total : 4608 Innodb_buffer_pool_read_ahead_rnd : 0 Innodb_buffer_pool_read_ahead : 616 Innodb_buffer_pool_read_ahead_evicted : 0 Innodb_buffer_pool_read_requests : 16466948 Innodb_buffer_pool_reads : 3086 Innodb_buffer_pool_wait_free : 0 Innodb_buffer_pool_write_requests : 63071 Innodb_data_fsyncs : 10668 Innodb_data_pending_fsyncs : 0 Innodb_data_pending_reads : 0 Innodb_data_pending_writes : 0 Innodb_data_read : 60724224 Innodb_data_reads : 3725 Innodb_data_writes : 16033 Innodb_data_written : 166877184 Innodb_dblwr_pages_written : 4708 Innodb_dblwr_writes : 149 Innodb_log_waits : 0 Innodb_log_write_requests : 36419 Innodb_log_writes : 10854 Innodb_os_log_fsyncs : 10170 Innodb_os_log_pending_fsyncs : 0 Innodb_os_log_pending_writes : 0 Innodb_os_log_written : 6671872 Innodb_page_size : 16384 Innodb_pages_created : 301 Innodb_pages_read : 3702 Innodb_pages_written : 4895 Innodb_row_lock_current_waits : 0 Innodb_row_lock_time : 0 Innodb_row_lock_time_avg : 0 Innodb_row_lock_time_max : 0 Innodb_row_lock_waits : 0 Innodb_rows_deleted : 265 Innodb_rows_inserted : 4798 Innodb_rows_read : 5583759 Innodb_rows_updated : 7049 Innodb_num_open_files : 17 Innodb_truncated_status_writes : 0 Innodb_undo_tablespaces_total : 2 Innodb_undo_tablespaces_implicit : 2 Innodb_undo_tablespaces_explicit : 0 Innodb_undo_tablespaces_active : 2 Key_blocks_not_flushed : 0 Key_blocks_unused : 2992463 Key_blocks_used : 940 Key_read_requests : 10174780 Key_reads : 1023 Key_write_requests : 103372 Key_writes : 2649 Locked_connects : 0 Max_execution_time_exceeded : 0 Max_execution_time_set : 0 Max_execution_time_set_failed : 0 Max_used_connections : 43 Max_used_connections_time : 2020-07-23 10:48:14 Mysqlx_aborted_clients : 0 Mysqlx_address : :: Mysqlx_bytes_received : 0 Mysqlx_bytes_sent : 0 Mysqlx_connection_accept_errors : 0 Mysqlx_connection_errors : 0 Mysqlx_connections_accepted : 0 Mysqlx_connections_closed : 0 Mysqlx_connections_rejected : 0 Mysqlx_crud_create_view : 0 Mysqlx_crud_delete : 0 Mysqlx_crud_drop_view : 0 Mysqlx_crud_find : 0 Mysqlx_crud_insert : 0 Mysqlx_crud_modify_view : 0 Mysqlx_crud_update : 0 Mysqlx_cursor_close : 0 Mysqlx_cursor_fetch : 0 Mysqlx_cursor_open : 0 Mysqlx_errors_sent : 0 Mysqlx_errors_unknown_message_type : 0 Mysqlx_expect_close : 0 Mysqlx_expect_open : 0 Mysqlx_init_error : 0 Mysqlx_notice_global_sent : 0 Mysqlx_notice_other_sent : 0 Mysqlx_notice_warning_sent : 0 Mysqlx_notified_by_group_replication : 0 Mysqlx_port : 33060 Mysqlx_prep_deallocate : 0 Mysqlx_prep_execute : 0 Mysqlx_prep_prepare : 0 Mysqlx_rows_sent : 0 Mysqlx_sessions : 0 Mysqlx_sessions_accepted : 0 Mysqlx_sessions_closed : 0 Mysqlx_sessions_fatal_error : 0 Mysqlx_sessions_killed : 0 Mysqlx_sessions_rejected : 0 Mysqlx_socket : UNDEFINED Mysqlx_ssl_accepts : 0 Mysqlx_ssl_active :  Mysqlx_ssl_cipher :  Mysqlx_ssl_cipher_list :  Mysqlx_ssl_ctx_verify_depth : 0 Mysqlx_ssl_ctx_verify_mode : 0 Mysqlx_ssl_finished_accepts : 0 Mysqlx_ssl_server_not_after :  Mysqlx_ssl_server_not_before :  Mysqlx_ssl_verify_depth :  Mysqlx_ssl_verify_mode :  Mysqlx_ssl_version :  Mysqlx_stmt_create_collection : 0 Mysqlx_stmt_create_collection_index : 0 Mysqlx_stmt_disable_notices : 0 Mysqlx_stmt_drop_collection : 0 Mysqlx_stmt_drop_collection_index : 0 Mysqlx_stmt_enable_notices : 0 Mysqlx_stmt_ensure_collection : 0 Mysqlx_stmt_execute_mysqlx : 0 Mysqlx_stmt_execute_sql : 0 Mysqlx_stmt_execute_xplugin : 0 Mysqlx_stmt_kill_client : 0 Mysqlx_stmt_list_clients : 0 Mysqlx_stmt_list_notices : 0 Mysqlx_stmt_list_objects : 0 Mysqlx_stmt_ping : 0 Mysqlx_worker_threads : 2 Mysqlx_worker_threads_active : 0 Not_flushed_delayed_rows : 0 Ongoing_anonymous_transaction_count : 0 Open_files : 1982 Open_streams : 0 Open_table_definitions : 1400 Open_tables : 1908 Opened_files : 291343 Opened_table_definitions : 5771 Opened_tables : 9232 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_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_longest_seen : 153 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 Queries : 1399458 Questions : 1397052  Secondary_engine_execution_count : 0 Select_full_join : 563 Select_full_range_join : 0 Select_range : 140367 Select_range_check : 0 Select_scan : 565665 Slave_open_temp_tables : 0 Slow_launch_threads : 0 Slow_queries : 5 Sort_merge_passes : 11 Sort_range : 0 Sort_rows : 740843 Sort_scan : 36916 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 : 1668995 Table_locks_waited : 23 Table_open_cache_hits : 1821667 Table_open_cache_misses : 9201 Table_open_cache_overflows : 7130 Tc_log_max_pages_used : 0 Tc_log_page_size : 0 Tc_log_page_waits : 0 Threads_cached : 9 Threads_connected : 1 Threads_created : 64 Threads_running : 1 Uptime : 26566 Uptime_since_flush_status : 26566