Count requests in different intervals

[chkaufmann 17]

I run a Rest API and I need to monitor the number of requests per user in different intervals (5sec, 1min, 1hour, 1day).

 

Before I try to build my own structure, is there a common way to do this?

 

Since there may be several 100 users online at the same time, using a log table and an SQL database is not an option (too slow). And keeping a list of all requests of the last 24 hours in memory isn't either because there are some users with several 10'000 requests per day.

 

Regards
Christian

 

[skyzoframe[hun] 4]

SQL triggers will be the solution. CRUD monitoring in one table, with a timestamp and user ID. ( Create, Read, Update, Delete)

[FPiette 383]

It is enough to store the request count per 5 sec interval. The other intervals can be computed form the 5 sec interval.

To count for the 5 sec interval, you can increment a counter (in memory or in SQL table), rounding the timestamp to the nearest 5 sec and using that rounded value plus the user ID to locate the record to update (or insert if not already existent). At regular interval, you can delete all counters older than the desired retention time. For performance reason, you may buffer the counter in memory for one or more 5 sec intervals and then flush the memory to the database to secure the data.

 

With that scheme; you have at most 12 * 60 * 24 = 17280 counter per day per active user. 2 bytes (short int) is enough if no use has more than 65535 requests per day. If they can do more, then you need more bytes per counter.

[Fr0sT.Brutal 900]

1 hour ago, chkaufmann said:

Since there may be several 100 users online at the same time, using a log table and an SQL database is not an option (too slow). And keeping a list of all requests of the last 24 hours in memory isn't either because there are some users with several 10'000 requests per day.

I'd say your numbers are ridiculously small for any modern DB. 1M records per day is nothing.

Just post events to separate DB writer thread, use transactions to pack inserts into batches and don't bother with any other optimizations.

F.ex., I have ~250 inserts to Firebird DB per second (21.5M per day)

Edited November 2, 2022 by Fr0sT.Brutal

[chkaufmann 17]

4 hours ago, Fr0sT.Brutal said:

I'd say your numbers are ridiculously small for any modern DB. 1M records per day is nothing.

Yes, for logging this is not a problem. But if I want to add limits to number of requests I have to check all four values. This means four SQL select on the log table for each request and this is not cheap anymore because most of the REST responses I build with data from objects already kept in memory.

Therefore I need an in memory data structure with this info for each user.

[SwiftExpat 65]

Sounds like you want RRDTool, at least from a structure / consolidation function perspective.

https://oss.oetiker.ch/rrdtool/doc/index.en.html

[Steven Kamradt 20]

I would use a simple record with an int for 5 seconds, minute, hour and day, an expires time for seconds, minute, hour, day.  At each request, see if it has gone past the expiration time (set on the first request for that period) and if so then subtract the amount from the next fields, reset the expiration time for the and log the 5 second interval to the db.  If you have hit a limit at any point across the steps then you can deny the request.  On startup, initialize your record from the 5 second db intervals for the previous day, hour, minute and 5 second interval.  This gives you the ability to reporting against the 5 second intervals, and keeps what is in memory to a minimum.  Of course there are some issues that you may need to consider.  What happens if you load balance multiple servers to handle increasing growth? How does it handle hundreds of thousands of single requests from unique users rather than hundreds of thousands of multiple requests from a few users?