Database Design – How to Store Large Amount of Session Data

datadatabasedatabase-designMySQL

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?

Best Answer

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)}

Related Solutions

Database vs Flat files for rarely accessed data

Database engines are in principle designed to cope with huge amounts of data much faster than with raw data files, when you have to access data in a non-sequential manner.

You say that you have all the right indexes on your table to get an optimized access, so data_timestamp is certainly indexed. However, I see in your query example that you use:

GROUP BY DATE(data_timestamp), HOUR(data_timestamp),
    MINUTE(data_timestamp),
    param_id

This forces your database engine to convert the timestamp of every row matching the where of the query do date, which I suspect is very time consuming.

As I suppose that date and time are comonly used in your application, I'd suggest to consider a little bit of denormalization here to facilitate the database's job by precomputing the date (DATE type) and the time rounded to the minute (either TIME type or eventually a SMALLINT between 0000 and 2359). That's 5 bytes overhead per row. Create an index on them for accelerating the GROUP BY clause.

If this is not sufficient, make sure the server is correctly dimensioned for its big data challenge, and look if your DBMS is sufficiently well placed in benchmarks with other DBMS.

As a work around you also could consider using two tables: an active table for the last 30 days, and a second table with all the historical data older than 30 days. Some batch jobs would then move the expiring data from one table to the other.

JSON Strings – Storing Large Data for Many Users

Your scoping requirements (300K+ users, 100K+ bytes per user) suggest the use of a relational database such as MySQL, or a document data store like MongoDB, not a JSON array. JSON is primarily a data transfer mechanism, not a storage mechanism, and it lacks the necessary tools (indexes, querying) that you'll need to effectively work with that much data.

Best Answer

Related Solutions

Database vs Flat files for rarely accessed data

JSON Strings – Storing Large Data for Many Users

Related Topic