17

I am using a node.js server to create a 'close to real-time' socket between my web app and a database. Currently I am using MySQL which I am polling every second in node to check if there are any changes to the table (based on a timestamp.)

I was wondering if there any specific techniques to doing something like this with MySQL? At the moment, I am just running a SQL query and using setTimeout before the next poll.

I know it's more common to use a NoSQL database in instances like this but I'm not really comfortable with the technology and I'd much rather use SQL.

Does anyone have any experience or tips for monitoring a SQL database with node?

Hanpan
  • 10,013
  • 25
  • 77
  • 115
  • 2
    I would use the memory (heap) storage engine. Then keeping an open connection and querying for changes all happens in RAM (memory) which is quite fast and should probably be fine. – haknick May 26 '11 at 22:29
  • Does that prevent me from using the table normally elsewhere? I need to be able to query the same table elsewhere in my app normally. – Hanpan May 26 '11 at 22:33
  • Not at all. However when the server shuts down (or if mysqld stops) all data is lost. Which means that if you want to keep persistence of the data you need to probably save it into another table in an interval or using some sort of logic. – haknick May 26 '11 at 22:37
  • http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html – haknick May 26 '11 at 22:37
  • http://www.igvita.com/2011/05/27/streamsql-event-processing-with-esper/ - a completely different toolset, but is the closest to what you want. You might reconsider switching to a database with a pubsub mechanism (Redis, CouchDB). – Ricardo Tomasi May 27 '11 at 19:22
  • I would so love to be able to do this right now "client.addlistener('insert','user_tb', function( error, row ) { ... } )" – thomas-peter Aug 18 '11 at 09:11

2 Answers2

11

I wouldn't personally use a polling mechanism for this. I think this is a pretty good use case for a pub/sub mq as a component on top of the database that allows consumers to subscribe to specific channels for change events on entities that they care about.

Ex:

  1. Someone requests that a model be changed
  2. Model broadcasts change event
  3. Queue up change to be persisted in the database
  4. Fire off a change set on a specific channel in a message queue for distribution to all interested parties

You can use a very simple in process pub/sub mechanism for this type of thing using nodes EventEmitter, and as you need to scale, have durability requirements, or need a cross language MQ you can go to a technology like rabbitmq, zeromq, etc. I've started to implement something very lightweight to do just this in one of my applications: https://github.com/jmoyers/mettle/blob/master/src/pubsub.coffee

It boils down to something like:

pubsub.sub('users.*', function(updates){
    // Interested party handles updates for user objects
});

That way you aren't putting stupid polling pressure on your database. In fact, change distribution is completely independent of writing to your database

Josh

Josh
  • 12,602
  • 2
  • 41
  • 47
4

I agree with the answer given by @Josh however if for whatever reason you are forced to monitor the state of a MySQL database then it is best to perform any polling server-side to significantly reduce the load on the server. One such technique I have used is to create a stored procedure that monitors some query result N times with a sleep. 

DELIMITER //

CREATE PROCEDURE waitForResults(OUT c INT)
BEGIN
  DECLARE n INT DEFAULT 20;
  DECLARE x INT;

  WHILE n > 0 DO
    SELECT SLEEP(0.5) INTO x;
    SELECT COUNT(*) FROM `jobs` INTO c;
    IF (c > 0) THEN SET n = 0;
    ELSE SET n = n - 1;
    END IF;
  END WHILE;

END //

DELIMITER ;

You can then query the database when this stored procedure returns. Knowing something has changed and then call the procedure again to wait on results.

Derrish Repchick
  • 1,049
  • 8
  • 5
  • If this runs on the database, how does it notify socket.io that a change has been found? – Colin Nov 17 '21 at 17:05