Re: MySQL 5.7 performance issue with 300 GB Innodb

[Øystein Grøvlen]

Hi,

It seems you are doing a lot of table scans and joins without indexes. Hence, I recommend to investigate whether the queries that spend the most time in your system could be improved. A good place to start is to run "SELECT * FROM sys.statement_analysis LIMIT 10". It will show the top 10 queries wrt total execution time.

Given that you have allocated most of the memory on your system for the InnoDB buffer pool, and have significantly increased the size of many memory buffers, you should also make sure that the there is no memory swapping on your system.

I also recommend setting "innodb_flush_method=O_DIRECT" to avoid using extra memory for the file system cache.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway