I need to be able to track API usage in order to generate reports or charts later. I've been struggling to decide whether to use regular MySQL or something like MongoDB or Elastic Search which I don't have any experience with at all.
The records I will insert will be
user_id (int) request_time (datetime) ip_address(varchar)
- I will need to count total records per user_id per given time
- I will need to grab all ips from user_id per given time
- I will be inserting around 400k records per day
- I need to be sure this will be able to handle at least 1 million records per day for inserts
Inserts are sent to a queue server (Redis) that will do single inserts to whatever system we decide to use.
What do you think will be the best solution for this scenario? It's worth mentioning that we are trying to find the least expensive way to do this.
Update: A million records using bigint for Ips will take approx 45mb making a total of 1350mb monthly