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).