ISPs and telecoms are often legally required to keep user session data for long periods of time. However, keeping these records can result in enormous databases tables which significantly affect the performance of your RADIUS system. This article explores how to optimize database performance so that you can maintain operational efficiency while storing years of user activity data.
Database performance challenges with large amounts of data
Database performance typically slows down as the size of the database increases. This limitation presents challenges for organizations 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 serve millions of users, historical databases can quickly mushroom into terabytes of data.
If the RADIUS deployment uses a single database table that 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.
Separating current database from historical databases
We recommend maintaining separate databases for the historical data and the current data, especially in situations where the historical databases contain large amounts of information. This approach helps optimize performance for both active users and backend database operations.
The design is very straightforward.
- The “currently online” database maintains records for one or two months.
- Once or twice a day, an automated process runs SQL queries to find all the old, closed sessions in the current database, 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 how users interact with the network.
Benefits of database separation for data storage
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 response times, 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.
Database schema and usage patterns
It is worth noting that both the current and historical databases will typically have the same schema, often using a primary key to uniquely identify each session. However, their usage patterns are very different:
- The historical database serves as long-term data storage, with potentially hundreds of millions of rows containing sensitive data like IP addresses. 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 who have gained access. The transactions on this SQL server 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 to maintain resource utilization efficiency!
The division of usage between the databases is most effective when applied to organizations that manage large amounts of user data and serve millions of users and sessions every day. Most commonly, large ISPs and telecoms typically encounter this situation.
Monitoring your solution
To ensure your database separation strategy is working effectively, implement monitoring tools that track:
- SQL query performance
- Resource utilization on both database servers
- Response times for authentication requests
- Growth rates of both databases
Regular monitoring helps maintain operational efficiency and allows you to proactively address any performance issues before they impact users.
Need more help?
InkBridge Networks has been at the forefront of network security for over two decades, tackling complex challenges across various protocols and infrastructures. Our team of seasoned experts has encountered and solved nearly every conceivable network security issue. If you're looking for insights from the architects behind some of the internet's most foundational authentication systems, you can request a quote for network security solutions here.
Related Articles
Separating RADIUS Authentication and Accounting Functionality
Many ISP networks have Authentication and Accounting handled by the same databases. This configuration works for many situations, especially small and low-load systems. There are times, however, when it is beneficial to separate the functions of Authentication and Accounting. Doing so can increase performance, and scalability.
How Authentication Protocols Work
Choosing an authentication protocol is one of the most important decisions when designing a RADIUS ecosystem.
There are a variety of authentication protocols to choose from, each with their own set of advantages, disadvantages, and constraints. In general, we recommend using PAP whenever possible. It is compatible with all known back-end databases, and it has no known security issues.