Software Engineering Stack Exchange is a question and answer site for professionals, academics, and students working within the systems development life cycle. It only takes a minute to sign up.
Sign up to join this community
Current project that i'm working has a requirement to capture data relevant to a session and store. Purpose of this is to use these data for the analytics. Clients would use these data to generate graphs, chart and etc.
For each client, 10 data points are captured within a minute(10 rows within a minute). As per the project requirements, table would exceed millions of rows within one week.
Current solution is to store the data in a Mysql database? Would this be the proper solutions?
Databases can store many (hundreds of) millions of rows and even dozen of billions of them. You need just to create the appropriate database schema, and you should care about good indexing.
Of course you need enough disk space. This is the most important issue (and you need a careful estimate of the required disk capacity).
BTW (without knowing your actual requirements), by guessing a kilobyte per row, your database will certainly fit in a single terabyte disk (and more probably, only a few hundreds gigabytes of disk). You probably want the index to fit in RAM (so you might want a server with perhaps a few dozens of gigabytes)
You could use MySQL or PostGreSQL, but you could even use SQLite (see this answer). However, if you have multiple clients accessing (concurrently) the same database, you'll better use a real RDBMS server (e.g. PostGreSQL or MySQL), but not SQLite. If you want a lot (e.g. hundreds) of simultaneous connections, you may want to dimension appropriately your server (e.g. several disks, or SSDs to improve I/O bandwidth, or more RAM).
(I don't feel that your case involves a large database - I'll call it a medium-sized one, but I am not a DB expert; however, notice that PostGreSQL, MySQL and even SQLite all claim -see this for MySQL, this for PostGreSQL, this for SQLite- to be able to deal with multi-terabyte databases)
Redis is known as a cache server; and while it is great for that, Redis' real strength is as a session store. Redis is the clear choice for large and intensive session stores.
However, it is not clear to me that you are storing session data at all; but rather, it seems you are storing data about a session. There is a subtle, but important difference there. So, from your description, I think you could use a regular sql database without issues.
10 rows a minute per user is well within the capabilities of most mainstream relational databases. If you are really worried about performance, you can give each user their own database table, this will eliminate locking concerns.
