Advice on reducing server overhead of rapidly called PHP script from AJAX

2 Views

I’m writing a chat program for a site that does live broadcasting, and like you can guess with any non application driven chat it relies on a looping AJAX call to get new information (messages) in my case once every 2 seconds. My JSON that is being created via PHP and populated by SQL is of some concern to me, while it shows no noticeable impact on my server at present I cannot predict what adding several hundred users to the mix may do. 

<?PHP
require_once("../../../../wp-load.php");

global $wpdb;

$table_name = $wpdb->prefix . "chat_posts";

$posts = $wpdb->get_results("SELECT * FROM ". $table_name ." WHERE ID > ". $_GET['last'] . " ORDER BY ID");
echo json_encode($posts);
?>

There obviously isn’t much wiggle room as far as optimizing the code itself, but I am a little worried about how well the WordPress SQL engine is written and if it will bog my SQL down once it gets to the point where it is receiving 200 requests every 2 seconds. Would caching the json encoded results of the DB query to a file then age checking it against new calls to the PHP script and either re-constructing the file with a new query or passing the files contents based on its last modification date be a better way to handle this? At that point I am putting a bigger load on my file-system but reducing my SQL load to one query every 2 seconds regardless of number of users.

Read More

Or am I already on the right path with just querying the server on every call?

Post Views: 2

Related posts

1 comment

  1. So this is what I came up with, I went the DB only route for a few tests and while response was snappy, it didn’t scale well and connections quickly got eaten up. So I decided to write a quick little bit of caching logic. So far it has worked wonderfully and seems to allow me to scale my chat as big as I want.

    $cacheFile = 'cache/chat_'.$_GET['last'].'.json';

if (file_exists($cacheFile) && filemtime($cacheFile) + QUERY_REFRESH_RATE > time())
{
    readfile($cacheFile);
} else {
    require_once("../../../../wp-load.php");
    $timestampMin = gmdate("Y-m-d H:i:s", (time() - 7200));

    $sql= "/*qc=on*/" . "SELECT * FROM ". DB_TABLE ."chat_posts WHERE ID > ". $_GET['last'] . " AND timestamp > '".$timestampMin."' ORDER BY ID;";
    $posts = $wpdb->get_results($sql);

    $json = json_encode($posts);
    echo $json;
    file_put_contents($cacheFile,$json);
}

    Its also great in that it allows me to run my formatting functions against messages such as parsing URL’s into actual links and such with much less overhead.

Comments are closed.