The Only Reason My App Worked Was Due to a Slow Database

Check out my tech startup podcast TechZing, we've recorded over 220 episodes. Maximise your downtime by listening to us while you code, commute or do the dishes!

Things Are Not What They Seem

Astonishingly, the only reason my Ajax driven web app has been able to run consistently and successfully for the past two years is due to MySQL latency.

I know this because yesterday (with the help of Jonathon Hill) I optimized the database by moving it off the server and onto it’s own 4GB Rackspace instance. (Everything was running on a single 2GB Rackspace instance prior to this).

As soon as I got the new database up and running it was blazingly fast (using the Percona flavor of MySQL made it a breeze).

But today I noticed that the load average on the front end head server (now MySQLless) was running super high (100+) with customers being unable to login and generally use the service.

After some digging around I noticed that Ajax requests were being served wayyy faster allowing for each client to establish 5-10 concurrent ajax requests thus flooding the Apache child pool – amping up the load – and bringing down the site.

Semi-blocking Ajax Queue

To fix this I quickly implemented a semi-blocking queuing system for all Ajax requests.

Basically the JavaScript now queues all ajax requests the client sends and only makes one ajax call at a time UNLESS a call is blocked for more than one second, then it is allowed through.

This allows the javaScript to be asynchronous but severely throttles asynchronousness (if that is a word!) concurrency.

I was expecting this to slow things down but it seems to have really speeded things up from both a user and backend perspective.

But perhaps even more importantly the server load average has gone from 100+ down to 0.2.

I can only assume the reason the system has been working for the past two years is because MySQL latency was forcing the Apache child processes to run slower, with a knock on effect of Ajax responses being slower, with a knock on effect of chained Ajax requests being delayed thus creating a less asynchronous environment.

In other words, the only reason my app worked for the past two years was due to a slow database!

Posted by on April 10, 2012 in PHP, Pluggio

Comments

  • Did you try using nginx as a reverse proxy to apache? It seems to help with Apache’s resource consumption.

  • Fred says:

    Incredible. Thanks for posting detailing your experience. Do you have any further information on the semi-blocking ajax queuing system? Homegrown or included in some library?

    • Justin says:

      It’s home grown. Not sure how much use it can be but here’s the code. At the very least you can use it to get a conceptual template for how it works.

      var tm = 
      {
      
      	useAjaxQueue : true,
      	ajaxRunning : false,
      	ajaxQueue :[],
      	ajaxLastRun : 0,
      	ajaxMaxBlockMs: 1000,
      
      	runAjaxQueue : function()
      	{
      		if ( tm.ajaxQueue.length > 0 )
      		{
      			var ajaxCall = tm.ajaxQueue.shift();
      			tm.ajax
      			(
      				ajaxCall.sendVars,
      				ajaxCall.callbackFunc,
      				ajaxCall.statusMsg,
      				ajaxCall.hideLoader
      			);
      		}
      	},
      	
      	ajax : function ( sendVars, callbackFunc , statusMsg, hideLoader )
      	{
      		
      		if ( tm.useAjaxQueue && tm.ajaxRunning )
      		{
      			if ( tm.timeStamp() - tm.ajaxLastRun >= tm.ajaxMaxBlockMs )
      			{
      				tm.trace('ajax: forcing queue override: '+sendVars.action,'locks'+sendVars.action);
      			}
      			else
      			{
      				tm.trace('ajax: queuing: '+sendVars.action,'locks'+sendVars.action);
      				tm.ajaxQueue.push
      				({
      					sendVars : sendVars,
      					callbackFunc : callbackFunc,
      					statusMsg : statusMsg,
      					hideLoader : hideLoader
      				});
      				return;
      			}
      		}
      		
      		tm.ajaxLastRun = tm.timeStamp();
      		tm.trace('ajax: running: '+sendVars.action,sendVars.action);
      		tm.trace(sendVars,sendVars.action);
      		
      		tm.ajaxRunning = true;
      		
      		$.post
      		(
      			// Always go via root
      			"/",
      			
      			// Variables to send to the server
      			sendVars,
      
      			// Response from the server
      			function(response, textStatus)
      			{
      				
      				tm.trace('ajax: result: '+sendVars.action,sendVars.action+',running');
      				tm.trace(response,sendVars.action);
      				
      				tm.ajaxRunning = false;
      				
      				if ( tm.useAjaxQueue )
      				{
      					setTimeout(function(){tm.runAjaxQueue();},0);
      				}
      				
      				if ( typeof callbackFunc == 'function' )
      				{
      					callbackFunc(response);
      				}
      				else if ( callbackFunc !== undefined && callbackFunc !== false )
      				{
      					eval('tm.'+callbackFunc)(response);
      				}
      				
      			}, 
      			"json"
      		);
      		
      	},
      
      	timeStamp : function()
      	{
      		var date = new Date;
      		var timeStamp = date.getTime();
      		return timeStamp;
      	}
      }
      
  • PJ Brunet says:

    Sounds to me like Apache is the real problem. You could drop Apache and switch to NginX :D I’ve been using NginX since 2010 and it’s pretty simple–there’s not much to it really. When I look at “top” NginX does its business in less than 1/100th of a second. So it hardly uses any CPU time compared to PHP, MySQL or whatever you’re using.

  • warmwaffles says:

    Um rackspace’s cloudservers aren’t a great option for high traffic databases, as they start to slow to a crawl on I/O. Though they are going to be rolling out dedicated databases soon. When that is, I don’t know.

  • Jonathan says:

    For what you’re paying on that 4gb Rackspace setup, you can easily get three or four times the raw power with other very reputable hosts.

    It wouldn’t have the superficial appeal of cloud hosting, but it would have eliminated all of your performance problems, with a lot of room to grow.

  • Hi Justin,

    Nice to see you on HN. :) I agree with the others, though; Rackspace’s cloud isn’t the best solution for this setup. Definitely look into some dedicated hardware for this to help resolve I/O issues.

    -Erica

  • kl says:

    If you use Apache, then disable KeepAlive. It hurts client-side performance, but it’s the only way to prevent it from falling over when clients poll too often.

    A way around this is to use HAproxy in front of Apache, or switch to nginx or lighttpd which can pool connections much better.

  • Jacob Williams says:

    I’ve had this type of problem with apache before. It often helps to reduce the apache max clients so that the total memory they use is less than your real memory. Swapping really kills it.

    If you are using php or some other apache module based scripting engine, it may also be useful to use fastcgi, so that the apache process is not actually doing the hard stuff. Nginx works this way, so many people see the benefits of using fastcgi when they move to Nginx, but it might be easier to migrate your server to apache/fastcgi than to nginx.

    You could also put your ajax requests into a separate fastcgi pool with its own connection limit so that they aren’t competing with the other page services.

  • Comments closed