Slow mysql performance after moving to ipb

Question

I recently moved from vbulletin to IPB and my current MySQL setup configuration seems to be having issues with it as the site suffers slow speeds. I am not sure which MySQL settings need to change to help with the load/speed.

The server is 2 x 2.4GHz Octa-Core E5-2630 v3 Haswell Xeon, Ram: 96 GB Here is the result of mysqltuner. Thank you

https://docs.google.com/document/d/1WMo6SmHQ36QRnXGqwAPQSPGTpVbLwJDIqTe16U65LbA/edit?usp=sharing

Answer 1

Rate Per Second = RPS

Suggestions to consider for your my.cnf [mysqld] section based on data from MySQLTuner report and your question content.

connect_timeout=20  # from 10 to avoid aborted connections
thread_cache_size=100  # from ? to reduce threads_created of 143 in 20h
innodb_buffer_pool_size=36G  # from 10G to support all INNODB data and indexes
innodb_change_buffer_max_size=50  # from 25 percent to support ETL activity
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function

These 5 suggestions are just the beginning of configuation management opportunities. View profile, Network profile for free downloadable Utility Scripts to assist with performance tuning.

Answer 2

wow, thank you so much. The above suggestions helped a lot with performance I think. Thank you so much, Wilson.

I know the hard drives are ssd, I don't think I have any NVME devices.

Here is the information you asked. Thank you very much in advance.

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


SHOW GLOBAL VARIABLES: https://pastebin.com/y4iGazLZ


SHOW FULL PROCESSLIST: https://pastebin.com/kJ2pcNQ2


STATUS: https://pastebin.com/7DTq76hV


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


ulimit: https://pastebin.com/9B93EXS7


iostat: https://pastebin.com/hs7WDgq9


top: https://pastebin.com/nXshBwR0 


mysqltuner: https://pastebin.com/9Ff4kKKh