2013-05-16 - [GRASE-Hotspot] Database improvements for large databases

Header Data

From: Tim White <ti***8@gmail.com>
Message Hash: 61f7200d2527df2eaa15530f1822e03378c4bc9e4294c4a3e0c5a25afca8d11f
Message ID: <5195B332.2040900@gmail.com>
Reply To: N/A
UTC Datetime: 2013-05-16 21:33:54 UTC
Raw Date: Fri, 17 May 2013 14:33:54 +1000

Raw message 

If anyone has large databases, a "known" problem has been fixed in trunk 
today.

Basically, when a user logs in, or fails to login, include every 
computer that gets a DHCP lease, an entry is put in the postauth table. 
If you don't care about the details in this table, a quick fix is to 
drop the table and recreate it. This table can grow without bounds, due 
to there not being any cron cleanup code.

Today, the code has been written! It took some time as we do care about 
some of that data for diagnostics purposes, so writing a good clean 
query proved difficult. In the end, we got PHP to do a SELECT and then 
execute a DELETE for each row, as this proved much much quicker, with it 
only taking about 2 minutes to delete 1/2 million entries in a test 
database. The alternative was everything in 1 query, which was taking 
over 2 minutes to delete a few hundred entries, just due to how SQL does 
subqueries etc.

The changes are in revision 359, and for anyone who has a really large 
database, you may want to apply it now before the next release.

It also has a minor fix for deleting expired users.

It took a radius database dump from >400Mb down to around 2Mb, so it 
should help you if you have a very busy network.

Tim

Thread