2

I recently moved from a Dreamhost shared hosting account to a Linode 512mb VPS. The switch has been rather smooth for normal day to day operation of my site but some problems with MySQL have cropped up. A little background on my setup because I'm not sure if the problem is with MySQL or not:

  • Ngnix 0.7.65
  • Ubuntu 10.04 LTS
  • MySQL 5.1.41

The problem I'm having is that whenever executing a query that is not part of the sites normal operation...(this could be anything from optimizing a table, to doing a SELECT COUNT, to running a delete query on a set of data)...prevents the entire web server from loading any pages. All pages on my site do have database calls, but pages that do not interact with the table I am working on still won't load. If you try and visit a page while one of these queries is executing the page will attempt to load for about a minute or so before Nginx throws the error "502 Bad Gateway." Obviously this isn't as much of a problem for a COUNT query that takes less than 5 seconds, but optimizing tables can bring the site from loading pages for 5+ minutes.

We have a script that runs nightly which pulls the top 100,000 records from our main table and stores it in a separate table for a rankings page. On Dreamhost when this script was executing the rankings pages of our site would be empty but the rest of the site would continue operating, however, on the VPS no page on the site will load until the ~6 minute query is done.

If anymore info would help please let me know and I would be happy to provide. I've searched this site and Google pretty relentlessly trying to find anybody else who had experienced a problem similar to this but can't find anything. Do you guys think this is simply a problem with MySQL, or could it be caused my Nginx? (on Dreamhost I had Apache).

Thanks.

alexbaumhoer
  • 21
  • 1
  • What MySQL storage type are you using? myISAM or InnoDB or anything else? – Amir Pakdel Aug 29 '10 at 09:28
  • MyISAM is the format for all the tables. I tried dumping the data into a separate database and converting the tables over to InnoDB to see if that would help but again during the conversion process my site would hang so I ended up killing it. – alexbaumhoer Aug 29 '10 at 15:37
  • any write ops (delete or optimize as you mention) require an entire table lock with myisam. If your web code is trying to update the same tables then there's your pause of death. http://dev.mysql.com/doc/refman/5.1/en/table-locking.html –  Aug 29 '10 at 15:49
  • And just to further expand on this, converting your tables are going to lock them as well, in the end you'll definitely want to be using InnoDB, though, MyISAM is a very old engine that's slowly being phased out. – Martin Fjordvald Aug 30 '10 at 05:22
  • you don't have to dump/import the db unless you have a very old mysql. 5.1 perfectly support "alter table my_table engine=innodb". This usually takes just a few minutes. – treffer Aug 17 '11 at 12:47

1 Answers1

3

Connect to your SQL server and run the query: "show processlist". This will show you all the running queries and their current state. You should expect to see almost all of them in the state LOCKED, which means that they're waiting for one of the other queries to finish. The ones that aren't LOCKED are your candidates for being the culprit.

tylerl
  • 14,885
  • 7
  • 49
  • 71