May 07 2007
Mysql or Postgres for Freeradius?
We are not trying to build a full comparative of Mysql and Postgresql in this article. This subject has been discussed many times and everyone has his own opinion on this. Some directions may be given and only a few critirias will be analysed to be used with Freeradius.
Criteria
If we check what’s already been done to compare Mysql and Postgresql, many criterias could be considered. The database containing Freeradius records is very simple, contains neither transactions, nor triggers, nor views. What should Freeradius database offer? This answer to this is pretty simple as well: disponibility and speed will be key factors to take a decision.
Mysql and Postgresql both offer some solutions – similar – to answer to needs of applications requiring high disponibility. Replication seems to be a good choice as it is possible to specify different servers for accounting and authentication (writes and reads in other words). Write accesses can be sent to the Master and reads to the slaves.
As both databases offer high availability options, the choice will be made based on the queries speed. Mysql is popular for its speed (especially for reads) but others will mention that Postgresql robustness in a concurrential environment would be better.
Tests
We are going to try to determine the number of transactions that each database is able to perform in a given time. Results will be compared to a basic setup that gets usernames from a text file. Conditions will be strictly identical. Among others:
- They will run on the same machine: a Pentium II with 384M RAM
- Mysql and Postgresql are compiled with the same compiler and equivalent options (Let’s mention flag -O3)
- Tables structure contains identical indexesSQL code (taken from Freeradius documentation) can be found here for Mysql and Postgresql.
- Pools of 50 connections are created
We measure the execution time to authenticate 50000 users with the radclient tool in concurrent access.
time /usr/local/bin/radclient -p 1000 -q -s -f radius.test 127.0.0.1 auth test
Results are pretty amazing:
| Storage | Text File | PostgreSQL | MySQL |
|---|---|---|---|
| Time | 5’13.552” | 1’46.598” | 0’35.146” |
| transactions/s | 159 | 469 | 1422 |
Here is a graphical visualisation of the number of transactions processed per second.
Reading from Postgresql as well as Mysql, is much faster than in a text file. This can be explained by indexes creation within tables. This difference would be smaller and could even be the other way around for a database containing a much smaller number of users.
Mysql realises excellent results processing three times more transactions than Postgresql.
Conclusion
These performance tests should not be interpreted as such, as users logging with an incorrect password are not considered. Some other aspects are not taken in considerations. Let’s mention the time spent accounting, pre and post-authentification processes. Nevertheless, results would be pretty close to these in a Master-Slave environment where reads would be sent to another server than writes.