Ubuntu – MySQL takes ages to load (only when using WP)

Apache2command lineMySQL

I'm not sure what's happening but it's since a while that I'm experiencing using my dev site (running on a VM) on the local Ubuntu machine.
The site runs on a WordPress instance using MySQL setup to use innodb table on a separate folder.
I don't have permission problems, as far as I know.

It I try to use MySQL CLI I can query the database without problems; the same using phpmyadmin.
But when it comes to access via the site it takes ages and more often than not I need to stop the service.

And that's again another symptom something doesn't work as it should.
Stopping the service via sudo service mysql stop I can see it immediately stops (phpmyadmin can't log in anymore) but the command line prompt keep on hold like it is working.
The only way I have to get the CLI ready is to brutally stop the service with a CTRL+C.

Any idea of what's wrong?
Thanks

Best Answer

You can see if this is a MySQL issue by running (swap the caps out for your runtime variables):

mysql -uUSERNAME -pPASSWORD DATABASE "show processlist;"

...while actioning a request. It'll show you the running-at-that-time queries along with how long they've taken. If you have a particularly slow query, it'll be in there. If that's too quick, there are methods of logging queries.

It could also be Wordpress (or your theme, or whatever) hammering the database with small queries. As somebody who builds online systems I'm perfectly qualified to let you in on a secret: Wordpress sucks. Oh okay, that's not a secret. You need to do a lot to make it perform well. To set you off in the right direction (assuming it's not a poorly written template): caching plugins!

In the same vein, watch htop while making requests. PHP+Wordpress will likely monster through a huge chunk of CPU to generate the page. It could just be that which is taking so long. If you can't fix it with caching (and you're on a 64bit computer) you could try HHVM (the almost-drop-in replacement for PHP-CGI from Facebook). It's much, much faster but needs installing. It's not as simple as apt-get install lamp-server^.

And finally, it does sound like it could be a DNS issue. If you're specifying a domain name as your database address and its resolution is taking forever (or worse, failing half the time), that's going to stuff up a connection. Switch it to an IP (127.0.0.1 if it's local) and try again.

Related Question