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.
It is therefore critical to optimize the SQL database for the specific requirements of each site. There is no "one size fits all" solution to a RADIUS ecosystem. Whether the database is MySQL, Oracle, or PostgreSQL, each has their own unique benefits and drawbacks. It is therefore critical to get expert advice in these optimizations.
Realistically, if your site has 10,000 users, then most commodity systems will be fine. You can buy a generic server system, and install Ubuntu or Rocky Linux. Then, install FreeRADIUS and the database of your choice. It will be fine, and it will work. This process shouldn't take more than a day or so for someone who's experienced with Linux system administration.
User credentials or authorization information (e.g., names and passwords) can be stored in simple tables, and FreeRADIUS comes with a decent schema. Accounting data can be stored for short-term connections or for long-term historical analysis. Again, FreeRADIUS comes with an efficient schema that scales to 10's of 1000's of users.
When you get to the 100K+ user threshold, system resiliency matters more than performance. It becomes critical to have multiple systems for redundancy. Using multiple systems is also a standard way to "shard" your data, so that you can get increased performance.
At the 1M+ user threshold, overall network architecture is more important than any one component. It's just not possible to download any product (FreeRADIUS or $$$$ paid), and then have it magically scale to 1-10M users. Networks and operating systems just don't work that way.
That being said, there are some simple checklists that you can go through to ensure that your SQL database is following best industry practices:
- Use the default FreeRADIUS schemas for authentication and accounting. They define a number of indexes which ensure that all queries are fast and efficient.
- Periodically delete old accounting data. Even an efficient index can't cope with a database that has 100M+ rows!
- Use a load balancer with RADIUS and database sharding to get more redundancy, and scalability.
- Use separate SQL database for authentication and accounting. That way if one fails, it doesn't affect the other one.
- Build "use specific" SQL databases. If you have to run complex monthly billing or usage queries against SQL, use a separate database. Keep a "live" database for the RADIUS server, and a "monthly" database for the billing and usage queries. That way when the billing database is at 100% capacity with complex queries, it won't affect the RADIUS server.
- If you need to keep long-term accounting data, move it to a separate SQL database.
You might see a common theme in the above recommendations: Designing a solution with multiple systems means increased stability and scalability.
We've used these techniques (and more) to run FreeRADIUS on many sites with 1M to 10M users. In our experience, FreeRADIUS is generally the least resource-hungry part of the system. That reality means that most of the time building a complex system will be spent on overall system design and database optimization.!
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
Client Case Study: RADIUS SQL
We were called in to help our client who had database performance issues with their custom schema and queries. We made updates to the database tables and indices and reconfigured the RADIUS server appropriately. The output meant a 300 times increase in the performance of the original system with no customer application updates required.
What does "unresponsive child" error message mean
Like any system, FreeRADIUS provides error messages to inform administrators of problems within the FreeRADIUS server itself. Sometimes however, error messages that are logged by FreeRADIUS are actually reporting an indication that something is wrong with one of the connected systems. A common cause of some of these errors is an unexpectedly slow database.