When a major ISPs RADIUS database slowed to a crawl under growing data loads, we restored full performance without replacing a single system. Here's how proper database architecture beat a hardware upgrade.
The problem: SQL server slow performance crippling a production RADIUS system
A major ISP had deployed a basic RADIUS server with an SQL database backend to handle network authentication and accounting for their growing user base. The initial implementation performed well during the first few months of operation. However, as the database accumulated session data and user records, the system's performance deteriorated to an unacceptable level.
The organisation faced a critical decision: invest in expensive hardware replacements and system upgrades, or find a way to restore performance using their existing infrastructure. The cost and disruption of a complete system overhaul made this option particularly unattractive, especially when the root cause of the performance degradation remained unclear.
What causes SQL server slow performance in RADIUS systems?
RADIUS servers handling authentication and accounting for ISPs and telecommunications providers face a unique challenge. Many organisations must retain user session data for law enforcement compliance, sometimes requiring six months to a year of historical records. These databases need to answer questions like "Who was using this IP address at 8:43 p.m. on 16 January 2024?"
As these compliance requirements accumulate historical data, the databases can grow into terabytes. Without proper database design, all that historical information directly impacts the performance of live authentication requests. The problem becomes particularly acute when authentication queries, accounting updates, and compliance reporting all compete for resources in the same database structure.
The generic approach to database design is straightforward and easy to implement, but it's typically slow. As performance requirements increase, simply scaling up hardware often proves insufficient. The real solution lies in redesigning how the database is structured and how the RADIUS server interacts with it.
The solution: strategic database redesign and optimisation
Rather than recommending expensive hardware replacements, InkBridge Networks took a different approach. We analysed how the RADIUS server was using the SQL database and identified significant opportunities for optimisation through architectural improvements.
The solution centred on three key principles:
Database structure redesign
We don't just add more hardware when a database slows down. We redesign the database structure itself to match the specific requirements of RADIUS authentication and accounting. This includes adding appropriate tables and indices that reflect how RADIUS actually queries the data, rather than using a one-size-fits-all approach.
Separation of concerns
Different types of data have different access patterns and performance requirements. User authentication credentials change infrequently but need extremely fast read access. Accounting data changes every second and requires complex reporting queries. IP address pools need rapid updates but have simpler data structures. By using the right database technology for each requirement, the entire system becomes more efficient.
Query optimisation
We updated the RADIUS SQL queries to take advantage of the new database structure. The generic queries that came with the basic installation were replaced with optimised queries specifically designed for the redesigned schema.
Implementation: reducing database load by a factor of 400
The implementation process involved several technical improvements that worked together to dramatically reduce the burden on the SQL server.
We redesigned the customer's database schema to separate different types of data based on their usage patterns and performance requirements. Historical data that rarely changes was separated from live session data that updates constantly. This separation meant that enormous indices containing years of compliance data no longer needed to be updated with every new user session.
The database indexing strategy was completely overhauled. Proper indices were added to match the actual query patterns of the RADIUS server, ensuring that the database could quickly locate the information it needed without scanning through millions of irrelevant records.
We also implemented a multi-database strategy where appropriate. For this particular deployment, different database technologies were used for different purposes based on their strengths. LDAP provided fast read access for user credentials, SQL handled the complex accounting queries needed for billing and compliance, and Redis managed the rapidly-changing IP address pool assignments.
The RADIUS server's SQL queries were rewritten to take full advantage of the new database structure. Instead of generic queries that worked for any schema but performed poorly at scale, the new queries were optimised specifically for how this system needed to operate.
These changes reduced the load on the SQL server by a factor of 400. This dramatic improvement meant the customer could continue using their existing hardware infrastructure rather than investing in costly replacements.
Results: performance restored with ten times the data
The redesigned system achieved its primary objective: restoring the performance level that the customer had experienced during the initial deployment. More impressively, the system maintained this performance even as the database grew to contain ten times as much data as before.
The customer avoided the significant capital expenditure and operational disruption that would have accompanied a complete system replacement. Their existing infrastructure, which had seemed inadequate just months earlier, now handled significantly higher loads without performance degradation.
Perhaps most importantly, the solution proved sustainable. As the customer's user base continued to grow and compliance requirements demanded ever-longer data retention periods, the properly architected system scaled appropriately. The database could accommodate growth without the performance cliff that had plagued the original implementation.
Key lesson: database architecture matters more than hardware specifications
Many organisations facing SQL server slow performance assume the solution lies in more powerful hardware or expensive commercial solutions. Whilst hardware upgrades can provide temporary relief, they often simply delay the inevitable performance problems without addressing the root cause.
The reality is that most network authentication systems spend far more time on database design and network architecture than on RADIUS configuration itself. InkBridge Networks typically dedicates approximately 75% of project time to these foundational elements, because even the most powerful RADIUS server (capable of processing 80,000 packets per second) will perform poorly if the database can't keep up.
This case demonstrates why proper database architecture proves more valuable than raw processing power. A well-designed database structure that separates concerns, uses appropriate indices, and matches database technology to usage patterns will outperform a poorly designed database running on hardware ten times as powerful.
For ISPs, telecommunications providers, and enterprises facing similar performance challenges, the lesson is clear: before investing in system replacements, evaluate whether your database architecture matches your actual requirements. The right structural changes can deliver dramatic performance improvements using your existing infrastructure.
Need help?
InkBridge Networks has been at the forefront of network authentication for over two decades, tackling complex database and architecture challenges across various protocols and infrastructures. Our team designed and maintains FreeRADIUS, the world's most widely deployed RADIUS server, and we've encountered and solved nearly every conceivable authentication system challenge. 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 authentication solutions here.
Related Articles
Separating Authentication from your RADIUS Accounting server
Many ISP networks and enterprise environments handle both Authentication and Accounting functions through the same RADIUS servers and databases. While this configuration works well for small and low-load systems, there are compelling reasons to separate these critical network security functions.
Optimizing SQL
Most telecommunications companies and internet service providers (ISPs) use SQL databases to store the bulk of their user information. While we spend a lot of time on RADIUS, the reality is that any commodity machine can easily handle tens of thousands of RADIUS packets a second. In contrast, many SQL databases cannot sustain that level of database writes.