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:

It’s a AWS EC2 instance,
RAM : 8GB,
OS : Windows Server 2012,
SSD : 2TB EBS Volume

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 MySQL settings for our application loading time to decrease.

SHOW GLOBAL VARIABLES : pastebin

SHOW GLOBAL STATUS : pastebin

SHOW ENGINE INNODB STATUS : pastbin

InnoDB Metrics : pastbin

max_connections=750  # from 1500 to conserve RAM and have 50% more capacity than your expected 500 users
thread_cache_size=100  # from 9 to minimize threads_created overhead
innodb_io_capacity=1900  # from 200 to enable more of your SSD IOPS capacity
read_rnd_buffer_size=64K  # from 1M to reduce handler_read_rnd_next RPS of 39,200