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?

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.

×
9 answers

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

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

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 CLOSEWAIT
1 Foreign
1 established)
12 LISTEN
36 ESTABLISHED
83 TIME
WAIT

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.

A

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 ‘longquerytime’;
+—————–+———-+
| Variablename | Value |
+—————–+———-+
| long
query_time | 4.000000 |
+—————–+———-+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'slow_%’;
+———————+———————————+
| Variablename | Value |
+———————+———————————+
| slow
launchtime | 2 |
| slow
querylog | ON |
| slow
querylogfile | /var/log/mysql/mariadb-slow.log |
+———————+———————————+
3 rows in set (0.001 sec)

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

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.

@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?

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

Submit an Answer