1

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

  • Let us continue this discussion in chat. – Vishal Jul 24 at 17:27
  • Additional information request - from V 5.7.18 host. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 4 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; r F) SHOW ENGINE INNODB STATUS; G) SELECT name, count FROM information_schema.innodb_metrics ORDER BY name; for server workload tuning analysis to provide suggestions and comparison to V 8.n.nn capacities being used. – Wilson Hauck Jul 25 at 11:24
1

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.

| improve this answer | |
  • Thanks for your efforts, I have applied the mentioned settings but still portal loads slowly, Do you think query cache could be an issue here? In an earlier version of MySQL, we used query cache – Vishal Jul 24 at 8:20
  • Yes, Query Cache does not exist in any MySQL V 8.n.nn. Near the end of our Links page we have a link to a free Utility to assist with identifying queries needing indexes. When you say loads slow, how many seconds to display first page? – Wilson Hauck Jul 24 at 12:49
  • I have compared the table's indexes with old and new MySQL DB's, Indexes are intact. On of the website which has db size of 50GB loads under 2 mins with old MySQL but same website takes almost 13 minutes to load when new mysql server is used. – Vishal Jul 24 at 12:49
  • Are you 'opening every table' thinking you are getting data into RAM for faster retrieval later? Can you share the URL? – Wilson Hauck Jul 24 at 12:54
  • The code which we use to connect to new and old MySQL is the same. I set query_cache_type=0 in older version of MySQL websites still load fast, I guess the issue is not related to query cache then. No, It's a UAT environment so websites have restricted access. Thanks for showing interest in resolving the issue – Vishal Jul 24 at 13:50

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.