Long-Polling AJAX requests

[eluser]slowgary[/eluser]
Hey party people.

I'm tinkering with writing a server-push style application. Since most server-push technologies aren't widely supported, I've settled on long-polling AJAX requests. There's just not a lot of resources out there on the server-side implementation. That's hopefully where you come in.

This is what I have so far:

Code:

<?php

$count = 0;

while($count++ < 12)
{
    if($result = mysql_fetch_assoc(mysql_query("SELECT * FROM testing LIMIT 1")))
    {
        mysql_query("DELETE FROM testing WHERE id = ".$result['id']);
        die($result['text']);
    }
    else
    {
        sleep(5);
    }
}
echo 0;
?&gt;

It's a turd so far, but I just wanted to get the basic gist of it. I just have so many questions. I read that sleep() time doesn't get counted against PHP's max execution time. Does it free up the server to process other requests? It must use SOME cpu cycles to at least keep track of the time. Is there a more efficient way to do this?

Also, I figure if the user refreshes the page, this script would still run until there's new data. If a bunch of these 'rogue' scripts were running and no new data came in, they'd waste lots of cycles. That's why I threw the $count in (originally is was while(true), yuck.). It's just a safeguard so that a single instance of those script will never live for more than a minute or so. Does this seem like a sound idea? Is there a better way to do that?

Lastly, this is a simplified example, but in real world usage it would likely need to track the time of an event, instead of just deleting old events. Otherwise the app would not work for more than 1 person. The problem is, how can you adequately track the time? If I create a timestamp at the top of the script and search for rows that happen after the timestamp, there's still the chance that the client could miss new data that gets inserted between AJAX calls. What's the best way to overcome this?

I hope this all makes sense. It's new to me, but very interesting. Much more fun than the average business app (CRUD, CRUD and more CRUD).

[eluser]bretticus[/eluser]
Ugghhh, never a fun kettle of fish. Have you read up on COMET methods?

[eluser]slowgary[/eluser]
I'm not sure what a kettle of fish is, but I find this very fun

I have seen that article, and one other. They were they only examples I'd seen of the server side of things, and partially what I'd based mine on, but it still just didn't sit right for those reasons listed above.

After talking with a friend much smarter than me, I have a few answers. To my questions. For anyone else on this quest, here they are.

1. Performance of sleep()
==============================
The call to sleep() indeed does not use any CPU, which makes this method acceptable performance-wise. It also isn't counted against your script's max execution time.

2. Rogue infinite while loops
==============================
I'm not 100% sure on this one, and some of the info I got still hasn't sunk into my mind, but there are several reasons that were explained to me that make this a non-issue. The obvious possibility is that this script could hit the max execution time threshold. This should probably be taken into consideration when writing this type of script. Getting a timestamp at the top and checking to see if(now() - timestamp > max execution time) would be one way to handle it. The other factor here is at the socket level. I could be wrong but I think when the browser leaves the site it sends notification to the server that it's closing the connection, and the script is killed.

3. Data for multiple users
==============================
In my simple example above, I just deleted the data after getting it and that took care of making sure that you don't send old data over and over to the user. In a real-world example though, you'll likely have multiple users who need the data, so deleting is not an option. You'd need to track it with time. The idea here would be to track the time that new data is added, and the time that a user last checked for data. By comparing these two, you can always know which data is new and which data is old for a particular user. I initially had issues figuring this out because my idea was to save a timestamp at the beginning of the PHP script, and only fetch data that had been added after. The problem is that new data might be added between AJAX requests, and the user would never receive it. The solution is obvious, you'd need to store the last time of update per user, and update that timestamp every time the user gets new data.


I hope this helps someone else.

[eluser]slowgary[/eluser]
I was also thinking... for good performance it'd really be best to use a caching system. I've been looking into write-through caching. Write-through caching removes the need to retrieve data from the database. On a database write, the cache is first updated, then the database is updated from the cache. On a read, only the cache is accessed.

There's no cache, but here's a more current version of what I have:

Code:

list($last_updated) = mysql_fetch_array(mysql_query("SELECT last_updated FROM user WHERE id = ".(int)$_POST['user_id']));

while(1)
{
    if($result = mysql_query("SELECT * FROM data WHERE timestamp > {$last_updated} LIMIT 1"))
    {
        mysql_query("UPDATE user SET last_updated = NOW() WHERE id = ".(int)$_POST['user_id']);
        $result = mysql_fetch_assoc($result);
        die($result['text']);
    }
    else
    {
        sleep(5);
    }
}

[eluser]8th wonder[/eluser]
I’m not sure what a kettle of fish is, but I find this very fun wink

[eluser]bretticus[/eluser]
[quote author="slowgary" date="1252738420"]I was also thinking... for good performance it'd really be best to use a caching system. I've been looking into write-through caching. Write-through caching removes the need to retrieve data from the database. On a database write, the cache is first updated, then the database is updated from the cache. On a read, only the cache is accessed.[/quote]

APC or memcached might work well.

Very interesting. I have always been curious on how to handle these. And I haven't had the need to try yet. Did you get this working correctly? If so, do you have issues with script timeouts and connection pooling (what about the script abort?)

Thanks.

:cheese: Kettle of fish: A troublesomely awkward or embarrassing situation. An unpleasant or messy predicament.

...I think that is an old saying that was stuck somewhere in the back of my brain that day.

[eluser]slowgary[/eluser]
It works, but definitely needs tweaking. If there's no new data for a while, eventually the script hits the max_execution_time. I'd probably just get that time limit at the top and check it on each loop, throwing a "request for new request" (if that makes sense) if the max execution has been reached. Although it would have to be before the max time, so I'm not sure how to handle that

Something I've also come across is that new data cannot be tracked by time. I put together a basic chat room to test this, but if I send 3 inputs really quickly, they all go into the database with the same timestamp, which means that clients only get 1 of them.

My current attempt is below, I haven't had a lot of time to play with it. It works, but somehow still manages to fill my error log extremely quickly. It needs work.

HTML:

Code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

&lt;html &gt;

&lt;head&gt;
    &lt;title&gt;Testing&lt;/title&gt;
    &lt;meta http-equiv='Content-Type' content='text/html; charset=ISO-8859-1' /&gt;
    &lt;style type='text/css'&gt;
    body {
        background: #CF9;
    }

    #chat {
        position: relative;
        width: 400px;
        height: 400px;
        background: #F6F6F6;
        border: 1px solid #693;
    }

    #update {
        position: absolute;
        top: 0;
        left: 0;
        bottom: 32px;
        width: 400px;
        overflow-y: scroll;
    }

    #handle {
        display: none;
        position: absolute;
        width: 94px;
        left: 0;
        bottom: 0;
        height: 24px;
        line-height: 24px;
        padding: 3px;
        font-size: 18px;
        outline: none;
        border: none;
        border-top: 1px solid #999;
        background: #EEE;
    }

    #input {
        position: absolute;
        width: 394px;
        left: 0;
        right: 0;
        bottom: 0;
        height: 24px;
        line-height: 24px;
        padding: 3px;
        font-size: 18px;
        outline: none;
        border: none;
        border-top: 1px solid #999;
        background: #FFF;
    }

    #chat_form {
        width: 400px;
        margin: 0;
        padding: 0;
    }

    #chat_form input[type=submit] {
        display: none;
    }

    .text {
        display: none;
        padding: 5px;
        font-size: 12px;
        font-family: sans-serif;
        line-height: 1em;
    }

    .handle {
        color: #090;
        font-weight: bold;
    }
    &lt;/style&gt;
    [removed][removed]
    [removed]
        $(function(){
            var $update = $('#update');
            var $input = $('#input');
            var $handle = $('#handle');

            $input.focus();

            $(document).bind('focus, click', function(){
                $input.focus();
            });

            $('#chat_form').bind('submit', function(event){
                event.preventDefault();
                $.post(
                        '/input.php',
                        { text : $input.val(), handle: $handle }
                    );
                $input.val('');
            });

            var poll = function(){
                $.post(
                        '/output.php',
                        { user_id : 1 },
                        function(data){
                            if(data.status == 'success')
                            {
                                $("<div class='text'><span class='handle'>" + data.handle + ": </span>" + data.text + '</div>')
                                    .appendTo($update)
                                    .fadeIn('fast');
                            }
                            poll();
                        },
                        'json'
                    );
            };

            setTimeout(function(){poll();}, 1);
        });
    [removed]
&lt;/head&gt;

&lt;body&gt;

<div id='chat'>
    <div id='update'>
    </div>

    &lt;form action='#' method='post' id='chat_form'&gt;
        <div>
            &lt;input type='text' id='handle'/&gt;
            &lt;input type='text' id='input'/&gt;
            &lt;input type='submit'/&gt;
        </div>
    &lt;/form&gt;
</div>

&lt;/body&gt;

&lt;/html&gt;

INPUT:

Code:

<?php
    if(isset($_POST['text']))
    {
        mysql_connect('localhost', 'username', 'password');
        mysql_select_db('databasename');
        mysql_query("INSERT INTO data VALUES(DEFAULT, '".mysql_real_escape_string($_POST['text'])."', '".time()."')");
    }
?>

[eluser]slowgary[/eluser]
OUTPUT:

Code:

<?php

mysql_connect('localhost', 'username', 'password');
mysql_select_db('databasename');

list($last_updated) = mysql_fetch_array(mysql_query("SELECT last_updated FROM user WHERE id = ".(int)$_POST['user_id']));
die(json_encode(array('status' => 'success', 'handle' => 'dude', 'text' => "SELECT * FROM data WHERE timestamp > '{$last_updated}' ORDER BY timestamp LIMIT 1")));

while(1)
{
    $result = mysql_query("SELECT * FROM data WHERE timestamp > '{$last_updated}' ORDER BY timestamp LIMIT 1");
    if(mysql_num_rows($result))
    {
        $result = mysql_fetch_assoc($result);
        mysql_query("UPDATE user SET last_updated = '".(intval($result['timestamp']) + 1)."' WHERE id = ".(int)$_POST['user_id']);
        die(json_encode(array('status' => 'success', 'handle' => 'dude', 'text' => stripslashes($result['text']))));
    }
    else
    {
        sleep(5);
    }
}
?>

DB:

Code:

CREATE TABLE IF NOT EXISTS `data` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `text` varchar(255) NOT NULL,
  `timestamp` varchar(16) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `timestamp` (`timestamp`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `last_updated` varchar(16) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

INSERT INTO `user` (`id`, `last_updated`) VALUES (1, '0');

If you get the urge to toss this up somewhere and play with it, please do. I'll have a lot more free time very soon and will attempt to create the best possible implementation of this for an app I'll be writing. I'm surprised that there's not currently a good PHP package/resource for this already.

[eluser]BrianDHall[/eluser]
Couple things to look at that may be of use to you:

Connection Handling in PHP

And the point of the above, Register Shutdown Function - you can have PHP run a function when a script shuts down for any reason, including closing the browser or even when the script times out!

These I found very interesting when I first found them, but I never used them as I gave up server-push over HTML with PHP as too big in the pain in the ass to bother with

[eluser]slowgary[/eluser]
Beautiful resources!!! Thank you very much for that Brian, that's a gem I would not have stumbled on myself. I will post a revision soon that includes logic to handle timeouts and disconnects.

As a side note, my shared host must not have a max time set for PHP scripts... one of these scary infinite loops got away and has been running for DAYS!! My error_log grows at a rate of about 2K per second! Crazy, no?