Help me understand what tech support is saying, and how to act upon it.

June 24, 2010 12:46 PM

Help me understand the email I got from my web host's tech support. Warning: Contains words like "root" and "queries" and "processes."

So our site (link in profile) has been running crazy slow lately, intermittently - one minute you'll go there and it'll be fine, the next you'll get a database time-out, the next that "Now Playing" box in the top-right corner will be blank. The site runs Wordpress, and each post is a song in our real-time playlist.

I wrote to our web hosting company's tech support to ask them to look into it (we have a dedicated server with them, so our machine should be strong enough to handle the traffic we have). Here's what the reply was:

From a cursory examination, it appears that there are a large number of SQL queries being executed as the user root that are driving the load up on the server. Once the load is driven up, all other processes are slowed, which is most likely causing the timeouts. I have enabled slow query logging on your server to determine exactly how long these queries are taking and their potential to cause the timeouts. Typically, it is not advisable to run queries linked to your website or activity as root, as doing so can affect system stability if the queries are malformed, or security if they are executed remotely. We suggest that you have your developer examine the mysql queries that are running to have them optimized. Since you have root access to the server, you can view the slow query log located at /var/log/slowqueries.

Now, I can state with absolute certainty that nobody in my organization has root access (or even knows what that is, frankly), so it's nothing that we're doing. Must be something that Wordpress is doing, right? But what could it be? Any other questions I should be asking?

posted by jbickers to Computers & Internet (31 answers total)

Must be something that Wordpress is doing, right?

Probably, if Wordpress was set up to run as root (which it shouldn't be).

Who did your Wordpress install and setup? You should forward your ISP's email to that person.
posted by enn at 12:50 PM on June 24, 2010

Response by poster: I set it up, but I'm definitely not a Linux person, so if I set it up as root, I did so inadvertently.

Are you suggesting that if it were not set up as root, it would do the queries faster?
posted by jbickers at 12:52 PM on June 24, 2010

No, I'm not suggesting that, just that running web applications that are open to the internet as root is dangerous.

There could be any number of things causing the performance problems — a poorly-engineered plugin in Wordpress, the database configuration, the Wordpress configuration, etc. You'd need to look at the queries logged by your ISP to get a sense of where the problem might lie.
posted by enn at 12:58 PM on June 24, 2010

I can't solve your running-as-root problem, but if you're running a high traffic wordpress blog, you might want to check out a cacheing system (such as WP SuperCache) to cut down on the # queries necessary.
posted by shownomercy at 1:06 PM on June 24, 2010

It's a security thing. Root is the UNIX term for administrator rights. Root has permission to do everything, including mailing out paypal scams and serving paypal scam web pages. Server admins tend to be paranoid about such things because this happens all the time, and nobody practices recovery processes until it's too late. The idea of running an effective antivirus on an infected is a fantasy, hence the trepidation. Running as not root means wordpress will have less access to do bad things like drop all tables or send out email.

But to the problem in question: It's not clear to me that your tech support guy is right. The situation he's pointing out is that your disks are busy because there's a lot of big requests being made to mySQL. However, this should only affect stuff that needs a lot of disk or mySQL. In top, stuff blocked on disk access is listed as "iowait", and stuff blocked on cpu is just "wait".

The file techsupport mentioned, /var/log/slowqueries, will have a list of all the mySQL requests that take a long time. It's probably be only one or two wordpress plugins that are poorly written, and you may be able to guess them from the queries. For example, it could be WP Super Cache. A comment in your site's front page: <!-- WP Super Cache is installed but broken. The path to wp-cache-phase1.php in wp-content/advanced-cache.php must be fixed! -->
posted by pwnguin at 1:13 PM on June 24, 2010

Just to clear something up since it sounds like you're confused:

So your root is the main folder all that your other content is in. It's essentially: www.DomainName.com.

Basically it sounds like the tech is saying your database exists on the root level of your server and the requests from the database is slowing down your website.

I'm not sure, though. I've never seen root = user before and that's what it sounds like he's talking about.
posted by royalsong at 1:16 PM on June 24, 2010

royalsong: "I'm not sure, though. I've never seen root = user before and that's what it sounds like he's talking about."

You might want to read this page. It's pretty common to run Wordpress on Linux, Apache and MySQL, and I'm assuming that's what you've got here.
posted by pwnguin at 1:20 PM on June 24, 2010

On *nix systems, royalsong, root is the name of the admin account. That's what the letter's talking about, not the location of WordPress within the filesystem.
posted by The Winsome Parker Lewis at 1:20 PM on June 24, 2010

It's probably be only one or two wordpress plugins that are poorly written, and you may be able to guess them from the queries.

Yeah, I have had issues like this, and it was because of plugins (and my own bad code) making a ton of queries on every page load, which seemed fine in testing, but made the server burst into flames when exposed to heavy traffic.

The "root" thing sounds like a red herring, honestly.
posted by drjimmy11 at 1:21 PM on June 24, 2010

SQL queries being executed as the user root

means the default mysql user, "root", not the system superuser "root". There's no necessary connection between system users and mysql users. He's saying "you should create another database user" (in that part of it, that's sort of an aside from his diagnosis of the problem).
posted by mendel at 1:23 PM on June 24, 2010

+1 to what mendel said, you need a user with restricted permission on SQL rather than root -- this also helps protect against injection attacks by preventing certain queries from being run, no matter what.

This would not cause your performance problem, though, that's probably just some slow operation.
posted by zvs at 1:27 PM on June 24, 2010

Response by poster: So I just got off the phone with tech support, and they say, "Yeah, you've got a ton of SQL queries going on there - you must have a ton of traffic!"

Only thing is, we don't. So it's not that.

However, we do create a lot of posts - a new post every 3 minutes on average. Tech support seems to think this could be the problem. Is that possible? What to do about it, if so?
posted by jbickers at 1:29 PM on June 24, 2010

Response by poster: Also, there are only three plugins running on the site: Akismet, the Facebook "Like" plugin and the degradable HTML5 player. Those last two were just installed a few weeks ago, and this problem predates them.

So I'm pretty certain it's not a plugin.
posted by jbickers at 1:32 PM on June 24, 2010

My gut instinct is that one post every 3 minutes isn't something that should overwhelm a database. Especially a dedicated one. If one post triggers other plugins and writes though, that can be a problem. Or if your setup is acting much faster than 3 minutes (3 seconds?).

You say the site is not high traffic, but how are you measuring traffic to the site? I didn't see any analytics code in the front page, and I could imagine Askismet nuking a lot of spam comments you never notice.
posted by pwnguin at 1:40 PM on June 24, 2010

Response by poster: Akismet caught about 2,000 spam comments so far for June. As for analytics, it was there before but we are futzing around with accounts right now. But this problem goes back to a time when we had analytics running, and our traffic was nowhere near where we wanted it to be.
posted by jbickers at 1:52 PM on June 24, 2010

Then I'm out of ideas. You need to remote connect to the server and take a look at /var/log/slowqueries. That's gonna be a much better hypothesis generator than further conversation with tech support.
posted by pwnguin at 2:02 PM on June 24, 2010

I was completely wrong. I also run a wordpress site on linux, through multiple hosting services. They all call the root account an admin account.

But that was also shared hosting.

My apologies. At least I've learned something new!
posted by royalsong at 2:44 PM on June 24, 2010

Queries can be slow because they are large in number or they can be slow for other reasons, such as having to do a table scan because an index is not available.

Really, there is no way to tell you anything based on this generic information. You need to dive into specifics and start looking at what the actual queries are, how long they're taking to execute, what part of the site is generating them, and whether they can be optimized.
posted by Rhomboid at 4:18 PM on June 24, 2010

+1 WP-Super-Cache. It takes some time to get configured and there are a lot of options but my guess is it will solve your problem.
posted by artlung at 6:32 AM on June 25, 2010

Response by poster: In the hopes that you good folks are still checking this thread: We just checked the slow query log, and it is empty. !??!?!?
posted by jbickers at 7:24 AM on July 1, 2010

Is the slow query log enabled? It isn't "on" by default. Now, why your host would tell you to look at it if it's not enabled, I don't know.

I am guessing that this is just a case of a mediocre host (and by mediocre I mean underpowered in terms of memory and processing on either or both: HTTP server, MySQL server).
posted by artlung at 8:29 AM on July 1, 2010

Response by poster: Definitely enabled. The hosting company is one of the larger ones (InMotion), and they've been stellar about everything. Definitely not a case of being underpowered. It has to be a case of me not having a switch thrown properly somewhere.
posted by jbickers at 8:56 AM on July 1, 2010

Stellar, but you have a mysteriously slow operating system. Right.

What's the value of long_query_time, and if you some of the actions that are slow, can you force an entry to appear in the slow query log?

And have you enabled WP Super Cache?
posted by artlung at 9:17 AM on July 1, 2010

Response by poster: Will have to check on the long_query_time value.

As for wp-supercache, we've tried it and it not only has no value, it breaks the site. That "Now Playing" box in the top-right corner always pulls the title of the most recent song/post and displays it. It's in the sidebar, so it's on every page, so there are no pages anywhere on the site that would benefit from caching.

(Unless I've misunderstood what the point of wp-supercache is)
posted by jbickers at 12:05 PM on July 1, 2010

Yes, you have misunderstood. I'm frustrated about the fact that you did follow up to say "WP-SuperCache has no value," because while that may be true for you, a few of the dozen or so sites I run would have fallen over if I had not had it on when they got linked to from dooce and boingboing.

Okay, what's WordPress do? It turns data stored in a MySQL database into posts and pages for you. That means for every page WordPress is assembling a page and running a whole bunch of queries. It gets your site name from one table, your posts from another, comments from another, and that's just for starters.

The more pageviews (of posts or pages), the more database load.

Caching plugins say, "let's hit that database less often!" that way, if Slashdot, or BoingBoing or DaringFireball or Fark or 4Chan link to your site you're not increasing database load proportionally to the number of users. WP-Cache or SuperCache store the pages as static files, already assembled and ready to serve. You can tune the expiry so that a page is no older than a day, or an hour, or 15 minutes old. In so doing, you assure that you are not hitting the database so much, but also, so that if there are new comments on a post that people can see them, rather than showing an older page. It's a bit more work and adds a new layer to administer, but it increases the likelihood that under load your site won't die. It also has a chance of alleviating the "slowness" you're seeing on your site.
posted by artlung at 12:50 PM on July 1, 2010

It's been long enough that perhaps the slow query log was rotated (moved to /var/log/slowqueries.1 or /var/log/slowqueries.1.gz.).

Or it could be that the technician you spoke to was wrong, or didn't configure slow query logging quite the way one expects. It could be not mysql related; perhaps some other disk or database activity happens. Maybe there's a backup scheduled for midnight and the system clock is off. Maybe there's a popular RSS / podcast feed or some other script that doesn't run javascript hammering your site; running your apache logs through an analyzer like webalizer will pick up more traffic than analytics will "count".

Or maybe it's related to however you solved this. Multiply one query a minute times a few dozen listeners and it might get unpleasant fast. If you think it's disk related related, perhaps installing and running iotop will enlighten.
posted by pwnguin at 4:55 PM on July 1, 2010

Are you sure it's timeouts and not connection failures that cause the page to render strangely? The MySQL max_connections setting is crucially important to performance because it determines the maximum number of simultaneous clients that may connect, which combined with query time sets the limit for query rate. If PHP tries to connect to the DB and there are no slots left the mysql_connect function will return failure and you will often see that error string on a website that's overloaded -- or if error reporting is disabled (or if the @ prefix is used) then the resulting page will just be blank.

To fix this, first of all see how your PHP error reporting is configured, and make sure it goes to a log file somewhere that you can review and check for mysql_connect failures. If that is happening you need to tweak the MySQL configuration. I'm a little perplexed about the setup here since you said you don't have root access but generally changing MySQL configuration parameters like this one requires root access. If you have no control over MySQL configuration then it sounds like you need to either get someone to fix that for you or you're on a shared server and you need to upgrade to a larger plan that allows more DB connections.
posted by Rhomboid at 6:28 PM on July 1, 2010

Rhomboid: "I'm a little perplexed about the setup here since you said you don't have root access "

I don't think he ever said that.
posted by pwnguin at 12:36 AM on July 2, 2010

OP said, "I can state with absolute certainty that nobody in my organization has root access" -- how else am I to interpret that?
posted by Rhomboid at 3:08 AM on July 2, 2010

Ah, I interpreted it to mean that nobody else in the organization had access -- it's a dedicated box, so someone's gotta have rights to set the damn thing up. The message from tech support implied they do have it available, so it's not impossible to get root access if nobody knows how. But this is starting to wade into territory that's best resolved by an expert on site.
posted by pwnguin at 2:47 PM on July 2, 2010

OP stated that it's hosted with InMotion. Lack of root access suggests one of their shared plans, rather than VPS or dedicated. Though that is just a guess.
posted by artlung at 3:42 PM on July 2, 2010