intermittent high myqld load on wordpress/openlitespeed droplet

not sure how to debug this, its a large wordpress site with a big database and showprocesslist isn’t giving me many clues, how do i set up a slowquery log on mariadb via ssh/phpmyadmin? and are the some basic mysql settings i can change that may resolve it?


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

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 -O
  • Then execute it:

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:

This was mini tutorial was posted from @bobbyiliev in this question in our community:

Hope that this helps! Regards, Alex

Hi @gabba

You can follow this to setup slow-query-log-in-mysql.

Can you also share connection numbers by command:

netstat -nat | awk '{print $6}' | sort | uniq -c | sort -n

Sometimes top output can also help us to narrow down the issue.

Best, Eric

Great, good to know that you fixed it. Yes it is.

@eleu Mysql seems to have settled down, optimized a few DBs and that seemed to do something.

regarding multiple lsphp instances in top, as the site is under traffic this is how it should appear isn’t it?

yes i do the wordpress plugin is enabled and looks to be working fine, its just mysqld which goes between 70% - 300% cpu.

I’ll try setting slow query log via my.cnf and see if that shows anything as the global setting seems to have no effect.

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.


yes i’m thinking the same must be a horrible wordpress query from some inherited plugin but still having no joy with slow query log, it should be enabled yet there’s nothing in it:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘long_query_time’; ±----------------±---------+ | Variable_name | Value | ±----------------±---------+ | long_query_time | 4.000000 | ±----------------±---------+ 1 row in set (0.001 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘slow_%’; ±--------------------±--------------------------------+ | Variable_name | Value | ±--------------------±--------------------------------+ | slow_launch_time | 2 | | slow_query_log | ON | | slow_query_log_file | /var/log/mysql/mariadb-slow.log | ±--------------------±--------------------------------+ 3 rows in set (0.001 sec)

Hey @gabba

have you altered the value for long_query_time? Also, having a 100%+ value for CPU utilisation isn’t abnormal. It just means that a little over 1 core is in use. That’s what you want, right?

The likelihood is MySQL has a poorly written query or a missing index. With the slow log enabled and working you should be able track this down.


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