Log InISPs and telecoms are often legally required to keep user accounting data for long periods of time. However, keeping these records can result in enormous databases which then affect the performance of your RADIUS system. There are ways of optimizing the database so that you can keep high performance while maintaining years of accounting data.
Database performance slows down as the size of the database increases
This limitation is potentially bad news for organizations who are legally required to maintain years of historical data. At any point in time, law enforcement might request records of user activity from several years ago. For large ISPs and Telecoms who have millions of users, historical databases can quickly mushroom into terabytes of data.
If the RADIUS deployment uses a single database table which maintains both the historical data and live sessions, it will have enormous indexes that need to be updated with every new session. In a database with a hundred millions rows, adding a new row can take many seconds, where normally it should take less than a tenth of a second. This performance drop can create a huge bottleneck in the RADIUS system, and can prevent users from getting online.
We recommend maintaining separate databases for the historical data and the current data, especially in situations where the historical data is very large.
The design is very straightforward.
- The “currently online” database maintains records for one or two months
- Once or twice a day, an automated process finds all the old, closed sessions in the current database and copies them into the historical database, and then removes those records from the current database.
These operations will be efficient because they are mostly bulk transactions, and can update hundreds of rows efficiently. Furthermore, because the appends are not being performed on the database used by RADIUS, any slow transactions will not impact the RADIUS system, and will not affect user experience.
The result is that the current database can be maintained at a very small size, so that updates can be done within milliseconds. At the same time, the historical database is available for the occasional query from law enforcement, or for accounting queries during the billing cycle.
The idea behind these changes is that database queries in a RADIUS system must be fast to computers. However, database queries for law enforcement must be fast to people, The difference is that computers need millisecond latency, and humans are happy with multi-second latency. This fact means that the databases used by RADIUS must be small and fast, while the databases used for law enforcement can be large and slow.
It is worth noting that both the current and historical databases will typically have the same schema. However, their uses are very different.
The historical database serves as long-term data storage, with potentially hundreds of millions of rows. The actions on it will generally be append-only and there will be very few read transactions.
The current database is used for short-term storage, with about as many rows as users. The transactions on this database will generally be updating the same user session data over and over again.
When to use this solution
In scenarios where there is no requirement to archive user sessions for several years, or if the historical database is relatively small, is generally safe to put both sets of data into one table. However, the old data should be periodically deleted! The division of usage between the databases is most effective when applied to organizations that serve millions of users and sessions every day. Most commonly, large ISPs and Telecoms are in this situation.
Need more help?
Network RADIUS has been helping clients around the world design and deploy their RADIUS infrastructure for 20 years. We specialize in complex systems and have seen pretty much every variation and problem out there. If you want help from the people who wrote FreeRADIUS, visit our quote page to contact us for a consultation.
