Round Robin Database with MySQL

posted in: database, mysql | 0

If you have loads of continuous data which you have to store and at the same time it is o.k. if old data gets removed after a while, a round robin database is your choice. Those databases allow the user to define a maximum number of records (or maximum number of storage space) and new data that would not fit into the allocated space, will simply overwrite the oldest record in the database. Basically a ring buffer on disk. The database won’t grow indefinitely and you don’t have to do extensive maintenance tasks.

Without any doubts, RRDTool is the most popular round robin database out there. But fitting RRDTool into an existing database landscape is sometimes not what you want – especially if you need backups, etc. which you’d have to setup twice – for your existing database and for RRDTool.

I’m using MySQL quite a lot and it would be nice to have a round robin database engine for MySQL. Unfortunately this engine doesn’t exist, yet.

Fortunately however, Oli Sennhauser has written a three-pages-paper which describes how to “simulate” a round robin database with MySQL using MyISAM tables and a database trigger. Nice one – check it out.