High Frequency Ajax and MySQL Sleep Overload

Question

I am currently working on a light php framework to use with some high request ajax for my site, and have run into an interesting problem that has me completely stumped. The ajax is for a series of notifications, so the javascript sends off an ajax request for new information every 30 seconds. This ajax is active on every page of the entire site, so I realize its a lot of requests as several hundred users are browsing the site at any given moment, many with several windows open.

Syntax wise everything is perfect. The problem is, when I activated the ajax for my community, there is a build up of 30 - 40 SLEEP commands in the MySQL database. All of which seem to ignore the set timeout of 10 seconds. It effects the entire site's performance as a result.

My understanding is that left over sleep commands are the result of a connection that hasn't been closed. I am use the MySQLi object oriented class and have extended it with a few functions of my own. Everything is by the text book and double checked against the documentation, and I've made sure that every little piece of the MySQLi class is closed and released.

If anyone has any wisdom or experience with this type of effect, I would be most grateful for any advice.

Answer 1

Have you tried a push approach versus your current pull/polling approach? Take a look at Comet:

• Comet a new approach to AJAX applications
• Comet (wikipedia)
• Comet programming: Using AJAX to simulate server push
• Ajax Push Engine

However, there are some downsides, the main one being you have to maintain an open connection, and some browsers limit the number of connections you can maintain. The following article talks about the pros and cons of using Comet and suggests a hybrid-polling approach:

• The allure of Comet

Here is a stackoverflow question that talks about comet also:

• Compatibility of Comet with current technology

---