How do I balance 100 clients checking the same database table in a loop?

Question

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?

Answer 1

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.

Answer 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.

Answer 3

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.

Answer 4

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.

Answer 5

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.