We've got a situation where I have to deal with a massive influx of events coming in to our server, at about 1000 events per second, on average (peak could be ~2000).
Events come in faster than the DB connection pool can handle
The problem we have is that events come faster than the connection pool can handle. By the time one connection has finished the network roundtrip from the server to the DB, so it can get released back to the pool, more than
n additional events come in.
Eventually the events stack up, waiting to get saved and because there are no available connections in the pool, they time out and the whole system is rendered non-operational.
We've solved the emergency by emitting the offending high-frequency events at a slower pace from the clients, but we still want to know how to handle this scenarios in the event we need to handle that high-frequency events.
Other clients might want to read events concurrently
Other clients continuously request to read all the events with a particular key, even if they are not saved in the DB yet.
A client can query
GET api/v1/events?clientId=1 and get all the events sent by client 1, even if those events are not done saving in the DB just yet.
Are there any "classroom" examples on how to deal with this?
Enqueue the events on our server
We could enqueue the events on the server (with the queue having a maximum concurrency of 400 so the connection pool doesn't run out).
This is bad idea because:
- It will eat up available server memory. The stacked-up enqueued events will consume massive amounts of RAM.
- Our servers restart once every 24 hours. This is a hard limit imposed by Heroku. The server can restart while events are enqueued causing us to lose the enqueued events.
- It introduces state on the server, thus hurting scalability. If we have a multi-server setup and a client wants to read all the enqueued + saved events, we won't know on which server the enqueued events live.
Use a separate message queue
I assume we could use a message queue, (like RabbitMQ?), where we pump the messages in it and on the other end there is another server that only deals with saving the events on the DB.
I'm not sure if message queues allow querying enqueued events (that weren't saved yet) so if another client wants to read the messages of another client, I can just get the saved messages from the DB and the pending messages from the queue and concatenate them together so I can send them back to the read-request client.
Use multiple databases, each saving a portion of the messages with a central DB-coordinator server to manage them
Another solution we've though is to use multiple databases, with a central "DB coordinator/load balancer". Upon receiving an event it this coordinator would choose one of the databases to write the message to. This should allow us to use multiple Heroku databases thus upping the connection limit to 500 x number of databases.
Upon a read query, this coordinator could issue
SELECT queries to each database, merge all the results and send them back to the client that requested the read.
This is bad idea because:
- This idea sounds like ... ahem.. over-engineering? Would be a nightmare to manage as well (backups etc..). It's complicated to build and maintain and unless it's absolutely necessary it sounds like a KISS violation.
- It sacrifices Consistency. Doing transactions across multiple DB's is a no-go if we go with this idea.