1

I have seen many questions similar to the one I asked. A few of them said that its ok to poll, and that doesn't affect the database performance significantly, and some are still unanswered. What I want to know is this :

  • Can I do something similar to ASP.NET's sqldependency, which helps to find out when changes are made to the database WITHOUT polling.
  • I know polling is ok to do, might not affect database performance, but I need to know the update as soon as it is made, so I feel I need something better than just polling every 1 second.
  • As an add on point, the updates that are detected at the server then have to be pushed (Ajax Push - I'm using Ajax Push Engine) to all the clients who are subscribed to a particular channel.

Thanks & Regards, Thothathri Srinivasan

wittythotha
  • 3,956
  • 4
  • 19
  • 18
  • If you really want it purely without polling, by definition you'd have to have the MySQL daemon signal your listening application when something changed. If your definition can be less pure, other solutions are possible: a small local daemon that monitors the binary log, the database table files, a collection of triggers, .... – Konerak Jun 27 '11 at 22:14

4 Answers4

3

Redis PubSub

I hoped triggers could help you out, but after a little examination I (highly) have my doubts.

You could achieve your goal using Redis pubsub. Redis is an extremely fast data structure server. Publish the database changes to a channel and let the interested clients detect the changes by subscribing to that same channel(in real-time).

Downloading/Installing Redis is really easy and as extra bonus you will also "get an open source, advanced key-value store. It is often referred to as a data structure server since keys can contain strings, hashes, lists, sets and sorted sets."

Installing it is probably the best way, but if you can't, you could use the free http://redistogo.com plan to talk the Redis server with the limitation that you will only get 5MB to store your data and that it will not be persisted to disc.

Justine M.
  • 359
  • 8
  • 24
Alfred
  • 60,935
  • 33
  • 147
  • 186
  • Any sort of message queue should be able to serve the same purpose (beanstalk, AMQ, RabbitMQ, etc.) – Frank Farmer Jun 27 '11 at 23:14
  • 1
    It could but in my opinion pubsub serves this problem a lot better than a message queue because we can/probably have multiple listeners. – Alfred Jun 27 '11 at 23:26
  • I had a look at Redis Pub/Sub. Its pretty impressive no doubt, I'm using APE to do the same. The problem isn't about publishing/subscribing to a channel. The problem is that when changes are made to a db, I want to get to know of the same without having to poll every 5 seconds. I'm not making the changes in the db using php. There is this common db that any one of 3 firms can change. I need to know whenever the change is made, without having to poll as the frequency of change would be 5 seconds sometimes, sometimes only a week. Any ideas for that Alfred n Frank Farmer? – wittythotha Jun 28 '11 at 00:28
  • Or maybe http://stackoverflow.com/questions/1467369/invoking-a-php-script-from-a-mysql-trigger/1467405#1467405 – Alfred Jun 28 '11 at 01:04
0

You can use a server push with an implementation like websockets or comet.

Naftali
  • 144,921
  • 39
  • 244
  • 303
  • I know to do that, but I just want to be notified when the database itself is changed. – wittythotha Jun 27 '11 at 21:46
  • @wittythotha -- so add that to the backend and only send the push on an update – Naftali Jun 27 '11 at 21:49
  • @Neal - I think I should have been a bit more clear - Ok, so the database can be updated at anytime, by anyone. If I have a database that two or more organizations are using, and one of them decide to update, I want the other organizations to get to know of this. Having said this, the main organization might update it once a week, sometimes more frequently, and sometimes within 5 seconds itself. If i poll, I would be doing a lot of work that is not needed, so is there a way of letting the other two organizations know of the db change? I can then push to channels on those two organizations. – wittythotha Jun 27 '11 at 21:58
  • well you can have php search an update file, and if there is any new entries then push out new info – Naftali Jun 27 '11 at 22:01
  • hmm... well essentially I would have to poll that update file too then right? so its the same as polling the DB... – wittythotha Jun 27 '11 at 22:08
0

I like Alfred's pubsub idea. I would think about the database as a subscriber to the insert events, not as the canonical version. Maybe this isn't possible with your existing codebase. You might also be able to monitor the binary logs on the database server to watch changes come in and push this information to the subscribers.

Joshua Martell
  • 7,074
  • 2
  • 30
  • 37
0

I think Q4M is exactly what you want http://q4m.github.com/tutorial.html

hetaoblog
  • 1,990
  • 5
  • 26
  • 34