« Missing Link Daily
Missing Link Daily »

MySQL is playing funny buggers.

Posted by Jacques Chester on Monday, April 14, 2008

We’re running slowly because the database server our installation of Wordpress relies on has suddenly decided to go bananas. I’m trying to find out why but nothing really obvious is coming up. It’s got a solid 100% utilisation on one of our 4 CPUs but nothing appears on the process list. Any MySQL wonks out there who might have an idea of what in tapdancing buggery it’s up to?

Update: tried a few things to no avail. One of the scripts supplied by MySQL actually dropped the entire Club Troppo database and then choked, leaving it empty. Good thing I had a steaming fresh backup. Oh, but then Wordpress forgets a bunch of settings after being restored. Piddly stuff like which theme it’s using, or what plugins were enabled.

When you consider that the clowns who write this software secured $25 million in round B funding, the subprime crisis doesn’t seem like such a surprise any more.

In the end I had to manually kill MySQL and reboot the server.



ShareThis This entry was posted on Monday, April 14th, 2008 at 3:45 PM and filed under Site News. Follow comments here with the RSS 2.0 feed. Post a comment or leave a trackback.

14 Responses to “MySQL is playing funny buggers.”

  1. Clinton Roy said:

    Have you turned the slow log on?

    log_slow_queries = /var/log/mysql/mysql-slow.log

    that let me discover that one of our greylisting databases was terribly slow because it was
    using a silly backend for update heavy usage.

    Posted on 14-Apr-08 at 5:04 pm | Permalink
  2. Jacques Chester said:

    Clinton;

    I do have the slow query log turned on. Nothing in it looks like it should have maxed out the server for five minutes. The worst query in the last day took 6 seconds to do a complex join-and-aggregate over nearly a million records.

    Posted on 14-Apr-08 at 5:12 pm | Permalink
  3. TimT said:

    Old Jungle saying: never trust MySQL, Myspace, or MY DIERRHAERETIC ARSE.

    Posted on 14-Apr-08 at 5:21 pm | Permalink
  4. gilmae said:

    Which script choked, Jacques?

    Posted on 14-Apr-08 at 6:44 pm | Permalink
  5. Jacques Chester said:

    MySQL itself was jammed up. 100% utilisation of a single CPU, which was suggestive of a single query running at full blast — but no entry in the slow queries log to match.

    Posted on 14-Apr-08 at 7:46 pm | Permalink
  6. David Rubie said:

    Is it something silly like a background re-indexing operation? I’m not familiar with MySQL, but other (stupid) databases like Sybase and SQL Server can occasionally act stupid because of the type of scavenging scheme you are running to reclaim resources after (say) a big delete operation. Not all of them show up in the process list either. The other type of issue it might be is a query that is mis-hitting a cached query plan (SQL Server is awful for this) although you usually get 100% utilisation and a query sitting there in the 100% run state without returning anything.

    I’d suggest with any big database you want to rebuild the indexes weekly at a quiet time to avoid those kinds of issues.

    Posted on 14-Apr-08 at 9:10 pm | Permalink
  7. Jacques Chester said:

    David;

    I don’t believe MySQL does that automatically, but I reckon I might run the rebuild and optimise functions now anyway.

    Also, I think our on-server stats package might be a problem. It’s got about 850,000 records and not a single index on any column.

    Posted on 14-Apr-08 at 9:55 pm | Permalink
  8. gilmae said:

    meep!

    Posted on 15-Apr-08 at 4:53 am | Permalink
  9. Jacques Chester said:

    I’ve added an index to the id column and at some point I’ll need to dive into the queries to see what other indices are appropriate.

    Or I could do a data warehousing thing, dump it off for analysis on my own computer.

    Posted on 15-Apr-08 at 9:20 am | Permalink
  10. swio said:

    The computer people will have heard this one, but for any non-geaks reading this thread who wanted to understand how the experts fix problems (not taking a dig at anybody here)

    One day, a Mechanical Engineer, Electrical Engineer, Chemical Engineer and Computer Engineer�were driving down the street in the same car. All of a sudden, the car broke down.�

    The Mechanical Engineer said, “I think a rod broke.”�

    The Chemical Engineer said, “The way it sputtered at the end, I don’t think it’s getting gas.”�

    The Electrical Engineer said, “I think there was a spark and something is wrong with the electrical system.”�

    All three turned to the computer engineer and said, “What do you think?”�

    The Computer Engineer said, “I think we should all get out and get back in.”

    Or you could just run it on a mainframe ;-)

    swio - MF analyst/programmer

    Posted on 15-Apr-08 at 10:09 am | Permalink
  11. Dave Bath said:

    Just why I prefer to use Oracle or PostgreSQL whenever possible: scalable, good stats/optimizer functions, and reliable:

    wordpress-pg.sourceforge.net
    codex.wordpress.org/Using_Alternative_Databases

    Posted on 15-Apr-08 at 11:39 am | Permalink
  12. Jacques Chester said:

    Dave;

    That last port is from WP 1.2.

    Besides, one word: plugins.

    Two more words: Wordpress sucks.

    Posted on 15-Apr-08 at 12:16 pm | Permalink
  13. Chris said:

    The computer people will have heard this one, but for any non-geaks reading this thread who wanted to understand how the experts fix problems (not taking a dig at anybody here)

    My mum’s car at the moment has a problem where it sometimes starts making a thumping noise. The only way to fix it is to pull over, turn the car off and then on again. Everything is then fine.

    Posted on 15-Apr-08 at 6:23 pm | Permalink
  14. David Rubie said:

    Let your mum out of the boot Chris, if she rides up front she will be less inclined to thump.

    Posted on 16-Apr-08 at 9:46 am | Permalink

Leave a Reply

You must be logged in to post a comment.