CODEWITHSUNDEEP
phpjquerymysqlajax
Ryan
Ryan

Reputation: 133

PHP+AJAX with MySQL - Query every 2 seconds, too many in TIME_WAIT

I have a basic HTML file, using jQuery's ajax, that is connecting to my polling.php script every 2 seconds.

The polling.php simply connections to mysql, checks for ID's newer than my hidden, stored current ID, and then echo's if there is anything new. Since the javascript is connecting every 2 seconds, I am getting thousands of connections in TIME_WAIT, just for my client. This is because my script is re-connecting to MySQL over and over again. I have tried mysql_pconnect but it didn't help any.

Is there any way I can get PHP to open 1 connection, and continue to query using it? Instead of reconnecting every single time and making all these TIME_WAIT connections. Unsure what to do here to make this work properly.

Upvotes: 2

Views: 1940

Answers (2)

John Parker
John Parker

Reputation: 54425

There's no trivial way of doing this, as pconnect doesn't work across multiple web page calls. However, some approaches to minimise the database throughput would be:

  1. Lower the polling time. (2 seconds is perhaps a bit excessive?)

  2. Have a "master" PHP script that runs every 'n' seconds, extracts the data from the database and saves it in the appropriate format (serialised PHP array, XML, HTML data, etc.) in the filesystem. (I'd recommend writing to a temp file and then renaming over the existing one to minimise any partial file collection issues.) The Ajax requested PHP page would then simply use the information in this data file.

In terms of executing the master PHP script, you could either use cron or simply let the user who first requests the page when the contents of file is deemed too stale. (You could use the data file's timestamp for this purpose via the filemtime function.) I'd personally use the latter approach, as cron is overkill for this purpose.

You could take this even further and use memcached instead of a flat file, etc. if so required. (That said, it would perhaps be an over-complex solution at this stage of affairs.)

Upvotes: 0

Ryan
Ryan

Reputation: 133

I actually ended up doing basic Long Polling. I made a simple PHP script to to an infinite while loop, and it queries every 2 seconds. If it finds something new, it echoes it out, and breaks the loop. My jquery simply ajax connects to it, and waits for a reponse; on reponse, it updates my page, and restarts the polling. Very simple!

PS, the Long Polling method also reduces browser memory issues, as well as drastically reduces the TIME_WAIT connections on the server.

Upvotes: 1

Related Questions