MySQL 5.1 Partitions & FreeRADIUS

[Fajar A. Nugraha]

On Tue, May 26, 2009 at 3:35 PM, Tim O'Donovan <tim at icukhosting.co.uk> wrote:
> Hi,
>
> Has anyone had any success in implementing MySQL 5.1's partitions to
> increase backend performance for FreeRADIUS?

You mean partitioning the radacct table? Succesfull, sure (at least on
test phase).
Increase performance, depends.

You see, we discovered there areas where database performance could be
a problem:
- reads (during autz & auth, and for external apps using radius data)
- writes (during acct)
- deletion of old records

For reads, we've done some optimizations (like switching to Innodb and
giving it enough buffer pool, using triggers to create a small
"radonline" table with online users for simultanious use check, etc.)
which has given excellent results. Using partitioning in this scenario
doesn't reveal any improvement (since we don't read radacct directly
during auth anymore).

Writes are harder. We discovered that during peak loads the disks are
IOPS-bound, and the only thing that would improve performance was to
add more disks. We ended up modifying the billing system so that the
load were more evenly spread.

As for deletion of old records, admittedly partitioning would be great
here (if you partition it per monthly or so). However there's the
problem of unique keys (short version: if you have unique key on
AcctUniqueID, you might have a problem when trying partitioning by
AcctStartTime or AcctStopTime).

In the end we decided it was not worthed, so (for now) the partition
setup stays in dev servers only.

-- 
Fajar