Web Hosting Support Accusing You Of Slow MySQL Queries? Read This

Originally Posted By: Hubert Nguyen | UBERGIZMO

It happens to thousands of people each day: for some mysterious reason, their website –maybe a WordPress or Drupal site- has become very slow overnight (I mean 3X to 10X slower than usual).

The administrative area of the back-office takes many seconds to load (more than 2 seconds starts to be bad), and the whole thing becomes a pain to use. In extreme cases, the whole page could even time-out and return an “unable to serve page” message, which is every webmaster’s nightmare.

After contacting your hosting company’s tech support, a technician takes a look at several log files, and returns with the dreaded answer: “I see some slow queries in your MySQL slow query log file, please optimize your application”.

Read: “it’s your fault, fix it”. But is your site really causing this? Maybe not.

Basic pre-requisites

As it is the case with any hosting issue, it’s always best to do whatever is within your power to trace the source of the problem. Keep in mind that most hosting support staff are not there to support your web app, but just make sure that the server runs normally (check your terms). Any hosting company that will officially support your app is definitely worth looking at, because many of the day-to-day problems come from the app layer.

Here are a couple of easy steps that may help you find the problem by yourself:

Have some clear performance metric to monitor/improve:
1. Number of SQL queries (16-80 id within normal range)
2. Page generation time (php performance)
  1. the time it took for the server to build the page from the database and template – in seconds. This is usually less than one second, between 0.3s and 0.7s).
3. Plug-ins like WP Page Load Stats or WP Super Cache output that information.
Try disabling plug-ins, starting by the least critical ones, and see if the metrics improve
Switch to one of the WordPress default theme like TwentyFifteen. I know, it sucks if you are in production. The alternative is to build a clone of your server.
Important: If your CPU usage is very high, or if your server is under heavy load:
1. Make sure that you have some kind of caching plugin installed and working (most people can install and setup WP Super Cache in a few minutes).
2. Without caching, even a moderately trafficked site can be stressed for resources

If the server load is too severe, all the metrics will be skewed or completely wrong, so the detective work will be harder. If you don’t have access to Linux CPU usage statistics, look at the page views of your site in WordPress Stats, Google Analytics (it has real-time stats) or other analytics tool. If you see a handful of people browsing the site, it’s unlikely that you consume a lot of resources – but it really depends on your site’s configuration.

You can clone the server, or scale it up enough to have some data that make sense for debugging purposes. Alternatively, find what’s consuming the CPU and solve that problem first.

Possible causes for MySQL slow queries

It’s important to acknowledge that it could very well be your site’s fault, and therefore your fault. Here is a legitimately slow query (2.13 sec) although, things could be much worse… try it for yourself:

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS wp_posts.* FROM wp_posts INNER JOIN wp_term_relationships ON(wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON(wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) INNER JOIN wp_terms ON(wp_term_taxonomy.term_id = wp_terms.term_id) WHERE 1=1 AND wp_term_taxonomy.taxonomy = ‘post_tag’ AND wp_posts.post_type = ‘post’ AND (wp_posts.post_status = ‘publish’) GROUP BY wp_posts.ID ORDER BYwp_posts.post_date DESC LIMIT 0, 6

Reasons may include:

Database intensive plug-ins, like related posts… look for plugins that are executed every time you load a page
Very large database (2+ millions of rows)
Very high number of MySQL queries (hundreds per page)
High number of uncached page views (leads to high number of queries)
A mix of the above

With many shared hosting services, the database sits on a different “box” as the Apache/PHP box, so performance issues are more isolated. If you have a virtual private server (VPS), MySQL and PHP may reside in the same machine. This means that performance pressure from Apache/PHP can affect MySQL performance as well. This can make isolating the issue a little harder, so keep an eye on current CPU usage when checking query performance. If the CPU (or disk IO) is already saturated, this will throw false-positive

But if you haven’t changed anything (maybe an auto-update happened?) and if your site is relatively modest in size (database smaller than 200MB), you may not be choking the database server resources, especially if you use a well-tested application like WordPress, with plain settings and proven plug-ins.

There are a number of reasons for slow MySQL queries which are unrelated to your web app:

Hardware problem with the MySQL server
Connectivity problems with the MySQL server
Limited number of MySQL simultaneous connections (less than 10)
Inadequate configuration settings on the MySQL server
Network latency between the web server and the MySQL server
Shared resources are being abused by another customer
Slow server storage
And more…

In this case, the web hosting company should find and fix the problem. Again, check your terms, but it should be common sense.

Is your query poorly designed, or was it a one-time fluke?

Let’s go back to our original support answer:

“I see some slow queries in your MySQL slow query log file, please optimize your application”.

It’s simple: if your query is slow because of its design, then the slow performance should be consistent every time you perform the same query.

In the many years where I ran websites, I often found that known web apps like WordPress were performing reasonably well out of the box (without bad/slow plug-ins), and that it was very unlikely that they would generate horribly slow queries, especially for smaller sites with low traffic. In fact, many of them still work very well with sites that have 100,000+ pages in their database. You can use a plug-in such as Query Monitor to see if

The Query is executed for a specific slowpage
The Query performs as slowly as the log would suggest

If the suspect query is consistently slow, then its design is not performing. If it is reported as consistently fast in your tests, then the slow log recorded some fluke which is not representative of the actual performance, and should be ignored.

If you suspect that the PHP performance is negatively influencing the Query speed, you can isolate the query testing further by using a MySQL manager like phpMyAdmin to execute the query, outside of the PHP environment. This utility is often provided by web hosts.

Note: only execute read-only queries like SELECT for debugging purposes. You don’t want to add/erase some information… unless you know what you are doing. Also if you are worried that MySQL may cache queries, use SELECT SQL_NO_CACHE to force disable the cache for that specific query (more information about the no cache command).

By going into phpMyAdmin >Database>SQL and replaying the slow SQL Query from the log, you can see if the query is genuinely slow every time, or if the slow log just recorded an exceptional event that is not representative or the real performance.

It could also be that something else, like network connectivity between PHP and MySQL, is affecting the query performance in the web app. If you suspect a slow network, ask support to “ping” the MySQL server. Decent response times are between 0.5ms and 2ms.

Slow queries typically read a LOT of data (thousands, or tens of thousands of database rows), which is often why they are slow. Looking at the number of rows that the query looked at is always a good indication of how much data it needed to execute, and therefore how slow and memory-consuming it may be.

If replaying the query in phpMyAdmin consistently yields a much faster query time than what the slow log has recorded, then it is fair to assume that the query itself is not at fault, and that the problem lies elsewhere: you don’t have to change the code, or “optimize” the query.

You can now make a reasonable argument with the tech support that the slow log probably recorded an exceptional event and that your app is not at fault. Most of the time, they will agree, and start digging into the real issue.

Many support technicians are trained (or forced) to follow a pre-defined script. Not only this is usually a sign of poor technical support, it is also a waste of time. It is however the reality for millions of hosting customers out there.

The slow MySQL log can be an easy way out (blame the customer, move on). Sometime, they are right. Many times, they are not. With a bit of methodology, you can quickly check if the “slow” query is truly slow, or not. This has saved me a lot of time (and money!) when dealing with slow database issues.

Tweak MySQL performance yourself

If you have administrative access to MySQL, and want to edit the MySQL configuration file, you may want to install the MySQLTuner script (there’s also a very handy Windows version that can access a remote MySQL server that I use). It is a very cool tool that will analyze your current MySQL server and propose some configuration changes that are easy to do (backup your config file before making changes!).

Things like query cache size (excellent query cache article) etc… can drastically improve MySQL performance. It’s not for everyone, and way beyond the scope of this article. Thanks to Paul Strauss for suggesting it.

Conclusion

Debugging site performance problems is not always easy (extensive overview at WordPress.org), especially if you don’t have the admin skills, or data visibility, necessary to understand what’s happening to fix it. Dealing with web hosting support B.S just makes it that much harder.

Along the way, you will most likely have to pass the (unfortunately) typical support script that is going to “blame the customer” and you will step on a few landmines as you look for answers. If your tech support doesn’t do this, they are great and you should be glad to be working with them.

Modern web hosting also feature tools like New Relic that can be a godsend to analyze what’s going on. Without proper tools, tech support itself often has not more clue that you do… despite being their job.

If the “you have slow Queries” remark comes up, I hope that this article will save you time, and… sanity. Good luck, and don’t hesitate to drop questions in the comments.