intermittent high myqld load on wordpress/openlitespeed droplet

Thanks Eric, i’m trying to get slow query log working, those instructions created the file, but when i try a select sleep(*) it doesn’t log.

top has this, mysqld running at 186% CPU:

1425 mysql 20 0 3001136 664064 23420 S 186.0 8.1 740:13.25 mysqld 22295 www-data 20 0 518980 77740 54780 R 16.6 1.0 1:51.13 lsphp 16219 www-data 20 0 517728 88396 68780 S 15.9 1.1 13:16.54 lsphp 20982 www-data 20 0 516996 78032 58876 S 6.3 1.0 3:44.15 lsphp 21448 www-data 20 0 515220 74960 55676 S 5.0 0.9 3:04.05 lsphp 22082 www-data 20 0 512792 70164 53756 S 3.7 0.9 1:48.50 lsphp 23215 www-data 20 0 512816 70620 54396 S 3.7 0.9 0:30.58 lsphp 23519 root 20 0 44556 4152 3476 R 0.3 0.1 0:00.03 top

and netstat:

1 CLOSE_WAIT 1 Foreign 1 established) 12 LISTEN 36 ESTABLISHED 83 TIME_WAIT

Hi @gabba ,

From the top result, I saw some lsphp, may I know if you have lscache enabled? It can helps PHP and mysql resources saving from guest visit.

Best

Hello, @gabba

What you can also do is to use the MySQLTuner script.

The MySQLTuner is a script written in Perl and allows you to quickly test your MySQL configuration and it gives you suggestions for adjustments to increase performance and stability.

According to the official GitHub page, it supports 300 indicators for MySQL/MariaDB/Percona Server in this last version.

To run the script you could do the following:

• SSH to your Droplet
• Download the script:

wget http://mysqltuner.pl/ -O mysqltuner.pl

• Then execute it:

perl mysqltuner.pl

The script would run multiple checks against your MySQL instance, all checks done by MySQLTuner are documented here.

Also as stated in the official documentation, it is still extremely important for you to fully understand each change you make to a MySQL database server. If you don’t understand portions of the script’s output, or if you don’t understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust.

As a good practice make sure to always test your changes on staging environments before implementing them on your production database.

On the same note, if you want to have a worry-free MySQL hosting and focus on your application, I would recommend trying out the DigitalOcean Managed Databases:

https://www.digitalocean.com/products/managed-databases-mysql/

This was mini tutorial was posted from @bobbyiliev in this question in our community: https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability

Hope that this helps! Regards, Alex