AWS Aurora performance degradation after upgrade from MySQL v5.6 to 8.0

Question

Kindly suggest which parameters to look for..

Following is the Performance Insights for our MySQL 8.0 AWS Aurora cluster..

We are consuming 3000+ queries per second and most queries return immediately.. we just upgraded from MySQL 5.6 to 8.0 and on 5.6 we didn't have such an issue..

Following is additional information:

Show GLOBAL STATUS; https://justpaste.it/20dx4

SHOW GLOBAL VARIABLES; https://justpaste.it/1sy4c

STATUS:

mysql> STATUS;
--------------
mysql  Ver 8.0.33 for macos12.6 on arm64 (Homebrew)

Connection id:      199671
Current database:   
SSL:            Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     8.0.26 Source distribution
Protocol version:   10
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:       3306
Binary data as:     Hexadecimal
Uptime:         1 day 2 hours 36 min 38 sec

Threads: 185  Questions: 329157339  Slow queries: 22588  Opens: 35941197  Flush tables: 3  Open tables: 10000  Queries per second avg: 3435.952
--------------


mysql> SHOW ENGINE INNODB STATUS;
Empty set (0.27 sec)

There are 25,000+ Databases on it.

8 Core, 64 GB RAM Machine hosted on AWS Aurora.

Answer 1

hacker315
To avoid PERMISSIONS CHECKING delays of query to SUM from information_schema.tables,

USE test;   # your test database 
DROP TABLE IF EXISTS cloned_tables; 
CREATE TABLE cloned_tables LIKE information_schema.tables; 
ALTER TABLE test.cloned_tables ENGINE=INNODB;  # to AVOID memory table
INSERT INTO cloned_tables SELECT * FROM information_schema.tables; 
SELECT NOW(); 
SELECT COUNT(*), sum(data_length), sum(index_length), sum(data_free) FROM cloned_tables; 
SELECT NOW(); 

In a few days you may want to DROP TABLE test.cloned_tables;

Please post TEXT results of the SUM request.

Thank you

Answer 2

Rate Per Second = RPS

Suggestions to consider for your ip 172-17-0-238 AWS Aurora Parameters Group (these are all dynamic variables and will apply to new processes)

read_rnd_buffer_size=32768  # from 256K to reduce handler_read_rnd_next RPS of 873,098 
innodb_io_capacity=500  # from 200 to use more of available SSD IOPS 
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function