7

What I have

This is a prototype. I have a pool of 100 clients connected to the server via websockets reporting things and awaiting for commands. The server polls the commands database table of type MEMORY in a loop using a query with WHERE client_id=?. I can insert a combination of client_id+command to that table, and once I do that, the corresponding loop will match and SELECT it and pass it back to the client.

What's the problem

The approach sounds like it would work, but as far as I understand I'm talking about n simultaneous database connections and queries in an endless loop (n being the number of clients), which doesn't sound effective. It'd be much better to do one query in one loop and then somehow check the client_id, if any, and distribute the results to the corresponding clients.

This reminds me of the approach where you're selecting articles first and then for () {} the resultset and do separate queries to get the details foe each of the items, which results in n+1 queries being made. The solution to that is doing a big query with JOINs and also preloading the other data that doesn't fit into the main JOINed query. There should be the similarly more effective way to do the database polling too.

UPDATE: I found this answer in the related section, and it says pretty much the same thing:

Hammering your database isn't really a good idea. While I'm pretty sure you've realized this, others might not have. I remember a friend of mine tried to use a php script and a Javascript AJAX function in a loop for a semi-real time game. He very quickly realized that performance degraded as more people joined, simply because he was executing a ton of queries per second which hammered the database.

So polling the database for each client sounds as unscalable and ineffective as building an AJAX chat application.

What I'm asking for

I guess that every possible programming approach must have been named and covered by now, so what is this one called? What is the common advice/approach here?

  • 1
    Does this data need to be persisted? You could possibly use some in memory database like memcached or redis. – thorsten müller Oct 8 '15 at 9:40
  • Thank you, well, the only reason why I might need it persisted is for security reasons - just so I know who and when issued a specific command, but that can be saved in a separate database, not linked to this system. So basically yes, this is why I thought about using the MEMORY table type mentioned above, I guess it utilises the same logic as memcached right? – aexl Oct 8 '15 at 10:06
  • Yes, memory should do the same thing. Possibly still worth having a look at different db technologies since the differences can be surprising sometimes. A RDBMS may still introduce some overhead that other technologies can avoid. While 100 users are actually not that much, so it's difficult to say if it's worth the time since there are quite a few technologies that would be interesting here. RethingDB, InfluxDB, VoltDB to name just a few. – thorsten müller Oct 8 '15 at 10:15
  • Aren't you just recreating the logic of a message broker? Why not reuse one of the existing ones (e.g. Kafka)? – Joeri Sebrechts Oct 8 '15 at 11:57
4

You want a connection pool - 100 clients will be throttled to using a pool of a handful of DB connections so your DB will not get overloaded responding to many simultaneous requests.

You could try to optimise the calls but you will still need to allow the clients to access the DB through a mechanism identical to a connection pool, even if there is just 1 connection available in the pool, you can combine all the IDs from the pending clients together into a single where clause to pass to the DB and unpack the results to each client when it returns. You'll either have to track which client is in the request or ensure the results have a result for each requesting client as more clients will arrive while the DB query is in progress, when the results are distributed to the clients, you repeat with the new ones.

If you have a pool like this,and your circumstances allow it, you can also delay DB requests to a gentle rate, but hitting the DB repeatedly on a single connection doesn't impact performance anything like as much as 100 clients all jostling for attention simultaneously.

2

You have asked the wrong question. The right question is "Should I use a database table to implement a queue?" and the answer is NO. Use a robust distributed queueing implementation like Kafka.

Or your database may have a built-in queueing mechanism. Oracle does. Tom Kite devotes a page or two in one of his Oracle books describing why you should not use a database table as a queue.

1

Ideally you should be pushing to your websocket (through a http request or any queue mechanism as mentioned in the other answers) server at the time the writes happen to the database. That way you can completely avoid polling the database.

If you have no other go but to use the database, then execute one query which will get the information for all the clients. This will help you avoid writing queries in a loop. In your application you can seperate out the data for each client and emit through websockets.

0

Unless the queries the clients need to issue are complex, I wouldn't use an sql database here. There's quite a lot of overhead, even when usinga memory table: interprocess communication, interpreting the query, maintaining an index that probably supports query types you don't need (e.g. ranges), data marshalling, and so on.

Precisely how I would do it depends very much on the applications other requirements. If you don't foresee the need to scale to multiple servers, an in-process store (i.e. a javascript map in a shared variable) is by far the most efficient way of sharing data. Otherwise, I'd look at in-memory key value store databases, e.g. redis.

Above all else, I'd abstract data access so that changing between these options at a later date is easy.

0

IMO, you almost figured everything yourself already... I'll try to elaborate what kind of pattern my favorite framework uses for this kind of thing.

First you have your clients subscribe to the commands, that way your server knows which socket (client) wants the commands associated to which client_id. The server polls the database, every X seconds (milliseconds?) OR uses oplog tailing* to see if there are commands to be ran, if there are, it will send that command to the sockets subscribed according to the target client_id...

*oplog tailing is used in replicasets, it is a log of all operations done in a database. Tailing it can be more effective than pooling the database directly... Either using the tail to just know when a new document has been inserted and then knowing that the database should be queried will lower loads on large sets, but might not be needed.

  • In case anyone is curious specifically for PostgreSQL you can trivially use a trigger with listen/notify to push events up to the server application on any table operation. (Either sending the whole record or the new id for inserted record. It's up to you). kevin cline mentioned not using a database table as a queue which is good advice though if this is the case. (Especially if this is for a game where commands aren't persisted in a database generally). – Sirisian Oct 13 '15 at 16:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.