Skip to: Site menu | Main content


 

My amazing adventures with Borat, Wordpress, MySQL and Linux

In the last few days my site’s traffic is constantly higher than normal. Much more so.
From simply examining the Web’s log file, it’s clearly related to a Borat news item I once posted.
Apparently searching for “Borat” in Google Images shows my Web site on the first results page, as I’ve cleverly linked a really cool Borat image back then.
Since Borat the movie is building up a lot of buzz, this means a huge amount of traffic to my site.



Anyhow, my Web site has been hellishly slow and unresponsive.
Even though the traffic is pretty high, still, my site shouldn’t be as near-dead as it is, it’s not as if I’ve been Slashdotted or anything.
So I started sniffing around a little – How do I go about “debugging” why my site is slow?

I decided to take a couple of minutes and “spy” on all the traffic coming out of my Web server. This is easily done by executing a command that displays the last lines of the Apache Web server log files as the log entries are added.

tail -f /var/log/apache2/access.log

The -f flag leaves the file open and updated. This essentially creates a monitor.
I looked for a few minutes. This, as I’ve mentioned above, immediately revealed that the most popular post is the “Borat” post and that the referring site is always Google images searching for Borat pictures.

Still, the traffic, while much higher than usual was by far not the highest I’ve ever seen, my site shouldn’t be as unresponsive.
I decided to make sure that I know exactly how many connections are created to my Web site.
I ran:

netstat -an |grep :80

The netstat command is a cool tool for examining network connections. The grep command filtered all the ports except for port 80 which is the Web port.
This displays the connections you have right now to Web server.
You can even run:

netstat -an |grep :80 |wc -l

Which uses the wc command that simply counts the number of lines for you, so you have the exact number of connections and not a list you need to count yourself.
Again, a decent amount of connections were made any given moment, but not anything my Web server shouldn’t be able to handle.


The next step was to see what software component, server or process, if any, are tasking the CPU and/or memory.
I was assuming it was the Apache server, but I had to check.
So I ran:

top

The top utility displays all the running processes on your system sorted by CPU usage.
This immediately discovered that my MySQL server is heavily stressed.
It was not the Web server at all, it was the database Wordpress uses that was very tasked. CPU usage for this process alone reached 60% sometimes.

Well, damn, what do I do now?
All I’ve done so far, documented above, is more or less basic Linux stuff which I’ve done before.
I know some SQL, but this has nothing to do with SQL, this is MySQL database administration, which I had no real prior experience with.
How the hell do I research this problem and go forward?

I searched Google for some time, and discovered the magic line that saved the day:

mysqladmin pr

mysqladmin is the command line administration tool.
The pr option asks to print the list of active threads in server. This lets you know essentially what MySQL server is doing at any given moment.
Great!
That was exactly what I was looking for.
You can even run:

mysqladmin -i3 pr

And the list will refresh all the time so you have a constant monitor and don’t need to manually run the command all the time.


By running this command the problem was immediately evident.
A single little relatively unknown Wordpress plugin – Most Wanted – was fu*king the hell out of the database.
It was constantly copying records to its own table and generally working a lot simply to know which are the most popular posts on my Web site.
The problem was obviously always there, only barely noticeable until I began reaching such a high and constant visitor number.

I simply uninstalled the plug-in and *POOF* my Web site is back to normal and working great.
I was really happy:
My Web site is back to normal and running great, I learned something new and I succeeded in a technical detective work and solving the problem.

I hope this helps some other people as well.
Oh, and go watch Borat. :-)


4 Responses to “My amazing adventures with Borat, Wordpress, MySQL and Linux”

  1. Rich Boakes Says:

    Hi Avi,

    That sounds like a useful performance analysis command; though I must admit I’m a bit baffled by your description – any chance you had it logging so we can see what the plugin was doing?

    I ask because you described that :”It was constantly copying records to its own table and generally working a lot simply to know which are the most popular posts on my Web site.”… and this seems strange, because MostWanted does not use it’s own table it only ever uses the StatTraq table.

    In normal use the only SQL that it uses is select statements, so there may be some MySQL thing which was causing a temporary table to be created… but like I say: baffled.

    So any logs of that performance issue might help a lot… :)

  2. Avi Says:

    Hey Rich,
    Sorry it took me some time to notice your comment.
    Unfortunately enough time has passed for me to forget the exact SQL query. But I do recall it something to do with moving records from StatTraq tables to TMP tables.
    I don’t know why or how, but it was definitely MostWanted that caused this issue, and removing it solved the problem.

    Your plug-in was great under “normal” load, but I guess for me it stopped working when the load got too big…

  3. LCD Projector Rentals Guy Says:

    This is my first comment over here. I like this blog a lot.
    I liked this blog entry the most though, the way you said it was just amazing!
    See ya Later ;)
    P.S. – CSS update?
    LCD Projector Rentals Guy

  4. SQL Tutorials Says:

    Does anyone know if there is another language or set of commands beside SQL for talking with databases?

    I’m working on a project and am doing some research thanks

Leave a Reply