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.
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
Rate Per Second = RPS
Suggestions to consider for your my.ini [mysqld] section
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
You will find these changes will significantly reduce footprint and CPU busy.
For additional suggestions, view profile, Network profile for contact info and free downloadable Utility Scripts to improve performance.