3

I have a db with over 5 million rows and for each row i have to do a http post to a server with some parameters at maximum rate of 500 connections. each post request takes 12 secs to process. so as old connections are completed i have to do new ones and maintain ~500 connection. I have to then update DB with values returned from these webcalls. How do I make the webcalls as above?

My App is in PHP. Can I use PHP or should I switch to something else for this.

user557348
  • 139
  • 1
  • 12

2 Answers2

2

Actually you can definitely do this with PHP using a technique called long-polling. Basically how it works is the client machine pings the server and says "Do you have anything for me" and the server sees that it does not. Instead of responding it holds onto the request and responds when it has something to send.

Long polling is a method that is used by both DrupalChat and the APE project (AJAX Push Engine). http://drupal.org/project/drupalchat
http://www.ape-project.org/

Here is some more info on push tech: http://en.wikipedia.org/wiki/Push_technology and http://en.wikipedia.org/wiki/Comet_%28programming%29

And here is a stackoverflow post about it: How do I implement basic "Long Polling"?

Now I have to say that 12 seconds is really dang long for a DB query to run. It sounds like either the query needs to be optimized or the DB does (or both). Have you normalized the database and setup good table and inter-table indexing?

Now as for preventing DB update collisions you need to use transactions (which both PostGres and newer versions of MySQL offer along with most enterprise DB systems). Transactions will allow you to rollback db changes and reserve table IDs and things like that. http://en.wikipedia.org/wiki/Database_transaction

Community
  • 1
  • 1
Patrick
  • 3,302
  • 4
  • 28
  • 47
1

PHP isn't the right tool to make long-running scripts, since it by default has a maximum execution time which is pretty short. You might look into using python for this task. Also note that you can call external scripts from PHP (such as python scripts) using the system() function, if the only reason you're using PHP is to make it easy to integrate a web front-end.

However, you [b]can[/b] do this in php with a cron-job by simply having your php script only handle a single row at a time, and have the cron-job call the php script every second. Just maintain the index into the table elsewhere (either elsewhere in the DB or just write the number to a file)

If you wanted to saturate your 500 connection limit, have your script do 40 rows at a time. 40 rows / second is roughly 500 rows / 12 seconds

Mala
  • 14,178
  • 25
  • 88
  • 119
  • 2
    To add to that: the default `max_execution_time` for PHP as a webserver module is 30 seconds, I believe. The default for PHP CLI (standalone commandline program) is no limit, since setting a limit would be pretty troublesome for non-web tasks. Default memory limits would still apply to PHP CLI, however, which is something to keep in mind if maintaining 500 simultaneous resource connections. – Wiseguy Jan 28 '11 at 17:25