Round Robin Data Storage in MySQL
If you want to store large amount of volatile data (e.g. log file entries) in a database with a constant storage memory footprint and no maintenance to purge the old entries, a round robin database is the best solution. But how to implement it in MySQL?
Some of the heaviest tables in our database are tables which do some event logging. Let’s look at a table which log the most recent visitors on a member profile. Look at that ugly output:
mysql> SHOW TABLE STATUS like "profile_visits_log" \G;
*************************** 1. row **********************
Name: profile_visits_log
Rows: 6'226'066
Avg_row_length: 21
Data_length: 130'747'386
Index_length: 393'205'760
It grows and grows… And the queries get slower and slower.
This is the case because we are keeping a lot of old, unused data. For example, on my member profile there’s data back to September 2007:
So we have to get rid of that old data. For example we can do it like Xing and store the n most recent entries for each user. Drop the rest:
Now, how do we do that in the most elegant way?
At 24./25. of September 2008 there will be the