Database design - keeping user historical averange for 25 000 users
I have a database with approx 25 000 users in 1 table. The table cointains the username and few other colums which cointains their related information of their current speed (crypto hashing speed) , their balance etc. This database gets updated every 7.5 minutes with new snapshot data. Currently I just keep the latest data (the latest update/insert to the database). This works fine. Now I would like to continue develeoping it and keep statistics for each and every user and be able to for example show the averange speed for each user. Their averange hashing speed, averange balance over a week and a month etc. How would this be possible? Any starting suggestions? I did not come up with any solution by myself so that is why I am asking. The statistical week and month average does not have to be very precise. Thanks!
I'd use RRD concept That way you have predictable growth of the DB which directly proportional to the number of users only. Any sort of reports would be super fast and easy. You would be able to zoom-in into numbers, although with less and less precision as time goes.
Split time into buckets: 1m, 5m, 1h, 1day, 1week Let's say you have new snapshot every 10min. Allocate space (columns) to keep:
- last 6 real measurements (to cover 1min)
- 5 columns to hold data averaged over 1min
- 20 columns to hold data averaged over 5min
- 24 columns to hold data averaged over 1h
- 7 columns to hold data averaged over 24h
When you have a new snapshot, write it into columns type #1 in round-robin fashion. Once you about to overwrite the first one - compute average and write data into columns type #2. Write type #2 in the same round-robin fashion. Same for all the types above. On every step you average (or use any other aggregation function) and move data up. This move triggers another averaging and moving up.
At any given time you have a view on averaged values based on 1m, 5m, 1h, 1day, 1week time frame.
You don't even need to implement it in MySQL or whatever database of your choice - use available RRD tools.