High load consuming mysql command

Question

I have a magento on dedicated server but from the past few days the website is giving me hard times. The host provider says its because of Database & asks me to optimize the data. Which i have also optimized. Further this following command on process manager show high consumption of CPU.

"/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --open-files-limit=10000 --pid-file=/var/lib/mysql/xxx-xxx-xxx-xxx.webhostbox.net.pid"

Please let me know what is this command doing & where could be it is coming from. Eagerly waiting for some advise on this.

Regards

Answer 1

Please enable the MySQL slow log and check out the queries that end up there. More info, can be read here: https://dev.mysql.com/doc/refman/5.7/en/slow-query-log.html

This would be the next step in finding the root cause, assuming there is a code problem in Magento.

Another approach could be running mysqltuner, a free Perl script that given insights and advice on which settings to change. You can get the script here: https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl

This is assuming there is a configuration problem in MySQL.