Database performance drop, after upgrade to MySQL 8.0.20

After upgraded the MySQL from version 5.7 to 8.0, I found out that the database performance is significant drop.

Before upgrade the MySQL the CPU usage is stable around 30%+-, but after upgraded the CPU usage is become unstable and frequently having large spike.

And recently I test out something very interesting, I'm keep run a same query for a few time, and found out that the duration taken becomes longer and longer. as per picture shown below.

I had read a lot of article and stack overflow post, but none of the solution is really get help. So hope that someone can share some idea or experience on tuning the MySQL8.0 with me.

Will very appreciate it.

Please let me know if needed any info for further investigate.

Config my.ini:-

key_buffer_size = 2G
max_allowed_packet = 1M

;Added to reduce memory used (minimum is 400)
table_definition_cache = 600

sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 2G
;Path to mysql install directory
basedir="c:/wamp64/bin/mysql/mysql8.0.20"
log-error="c:/wamp64/logs/mysql.log"
;Verbosity Value  1 Errors only, 2  Errors and warnings , 3 Errors, warnings, and notes
log_error_verbosity=2
;Path to data directory
datadir="c:/wamp64/bin/mysql/mysql8.0.20/data"


;slow_query_log = ON
;slow_query_log_file = "c:/wamp64/logs/slow_query.log"

;Path to the language
;See Documentation:
; http://dev.mysql.com/doc/refman/5.7/en/error-message-language.html
lc-messages-dir="c:/wamp64/bin/mysql/mysql8.0.20/share"
lc-messages=en_US

; The default storage engine that will be used when create new tables
default-storage-engine=InnoDB
; New for MySQL 5.6 default_tmp_storage_engine if skip-innodb enable
; default_tmp_storage_engine=MYISAM

;To avoid warning messages
secure_file_priv="c:/wamp64/tmp"
skip-ssl

explicit_defaults_for_timestamp=true

; Set the SQL mode to strict
sql-mode=""
;sql-mode="STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE,NO_AUTO_CREATE_USER"

;skip-networking

; Disable Federated by default
skip-federated

; Replication Master Server (default)
; binary logging is required for replication
;log-bin=mysql-bin

; binary logging format - mixed recommended
;binlog_format=mixed

; required unique id between 1 and 2^32 - 1
; defaults to 1 if master-host is not set
; but will not function as a master if omitted
server-id = 1

; Replication Slave (comment out master section to use this)

; New for MySQL 5.6 if no slave
skip-slave-start


; The InnoDB tablespace encryption feature relies on the keyring_file
; plugin for encryption key management, and the keyring_file plugin
; must be loaded prior to storage engine initialization to facilitate
; InnoDB recovery for encrypted tables. If you do not want to load the
; keyring_file plugin at server startup, specify an empty string.
early-plugin-load=""

;innodb_data_home_dir = C:/mysql/data/
innodb_data_file_path = ibdata1:12M:autoextend
;innodb_log_group_home_dir = C:/mysql/data/
;innodb_log_arch_dir = C:/mysql/data/

; You can set .._buffer_pool_size up to 50 - 80 %
; of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 4G

; Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 16M
innodb_log_buffer_size = 8M
innodb_thread_concurrency = 64
innodb_flush_log_at_trx_commit = 2

log_bin_trust_function_creators = 1;

innodb_lock_wait_timeout = 120
innodb_flush_method=normal
innodb_use_native_aio = true

innodb_flush_neighbors = 2
innodb_autoinc_lock_mode = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
; Remove the next comment character if you are not familiar with SQL
;safe-updates

[isamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer_size = 2M
write_buffer_size = 2M

[myisamchk]
key_buffer_size = 256M ;20M hys
sort_buffer_size_size = 20M
read_buffer_size = 2M
write_buffer_size = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
port = 3306
skip-log-bin
default_authentication_plugin= mysql_native_password

max_connections = 400
max_connect_errors = 100000

innodb_read_io_threads = 32
innodb_write_io_threads = 8
innodb_thread_concurrency = 64

Hardware:- Ram: 16GB CPU: 4 Cores 3.0 Ghz

SHOW GLOBAL STATUS: https://pastebin.com/FVZrgnTw

SHOW ENGINE INNODB STATUS: https://pastebin.com/Rewp84Gi

SHOW GLOBAL VARIABLES: https://pastebin.com/3v6cM6KZ

Rate Per Second = RPS

Suggestions to consider for your my.ini [mysqld] section It is unusual to have more than 1 [mysqld] section in the my.ini configuration the section you have near the end of you my.ini could be moved to be just before [mysqldump] to avoid confusion.

innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
key_buffer_size=16M  # from 1G to conserve RAM - you are not using MyISAM data tables
read_rnd_buffer_size=64K  # from 2M to reduce handler_read_rnd_next RPS of 1,872,921
innodb_io_capacity=900  # from 200 to more of your rotating drive IOPS capacity

You should find query completion time and CPU busy reduced with these changes.

select_scan averages 41 RPS and is caused by indexes not being available, causing delays.

For additional suggestions, view profile, Network profile for contact info, FAQ, additional tips and free downloadable Utility Scripts to assist with performance tuning.