What is the best solution for storing and retrieving logs (≈ 1,000 rows/sec)?

Question

Nginx

Greetings, comrades!
There is an nginx server, it receives about 500 - 1,500 requests per second.
For each of these requests, I want to save time and several of my parameters.
And everything would be fine if it were not also necessary to group it by minutes / hours / days and be able to show the user both grouped and a sheet.
Something similar to a regular access log, only with the possibility of grouping and adequate issuance.
If suddenly someone faced similar and can tell success stories - I will be grateful.
All the best and cheer for us!

Answer 1

In your case, I would screw some intermediate buffer.
If you write raw data directly to the database, there is a risk of locking / slowing down the writer from the brakes of the database, or simply simply losing the logs.
In similar tasks, we set a buffer in the form of redis and wrote and read keys from the LIST object using the RPUSH \ LPOP commands.
Thus, if there was a surge in the number of logs (the appearance of "hot" content on the portal), and they did not have time to write to the database, we only saw an increase in the number of records in the queue, and the fact that the data coming for analysis is somewhat "old". At the same time, the data itself was not lost, and neither side was locked.
If you are not afraid of a 30% overhead on data storage and new products, take a bunch of logstash + elasticsearch + kibana.
Using logstash, you read the log, parse it on the fly, extracting only the necessary parts and send it to ES, which indexes and adds the whole thing with timestamps.
And kibana gives a pretty interface for browsing, with graphs / pie charts, etc.

Answer 2

I think that's what I'll do. I will try to fasten MySQL for example.
I wonder if he can handle 2000 inserts per second?