For the past couple days, I’ve been trying to figure out how I can keep our flagship sites up during sudden spikes of incoming traffic. We have moved to LiteSpeed Web Server two months ago and it does help; however the bottleneck is back to MySQL. So recently I enabled slow-queries log on MySQL server to get started on query optimization. I found some wordpress plugins (recent comment, featured entry) are in fact causes slow queries.

So we have slow queries problem fixed, and during the month of May, we received another big wave of traffic, close to 300k unique for the day. The server is still able to handle the traffic but it was crawling, and server load reached as high as 9.1 according to top. MySQL took as much as 75 percent of the server resources and then we knew we need to get another machine dedicated to MySQL alone.

We went with our sponsors at The Planet. During the provision of the new machine, we have a little misunderstanding and the server was setup in a different data center hundreds of miles away. Latency was not all pretty and communication between the web server and MySQL server is slow. So in short notice, The Planet tried their best to find the nearest machine to our current server, and problem solved.

Was I happy? Yes I was, but wait! The servers are not cross-connected and every connection is counted towards our bandwidth quota. Within day one, we used up 320GB of bandwidth. The Planet does not offer cross-connect cable according to one of the sales rep, so we decided to cancel the dedicated MySQL server. Private racks will be our only options if we ever wanted to connect directly between our Web and MySQL server. At the end, we decided to do just that and hoping for another big wave of traffic to give our new servers setup a kick and dry run test.