Slow "update radacct ..." query

[Alan DeKok]

Marinko Tarlac wrote:
> I have some problems with freeradius and mysql as a database. radacct
> table is InnoDB and update queries (interim-updates) are terrible slow
> (10 - 50 sec). Interim-updates are triggered every 8 minutes so it isn't
> under heavy load. (max 500 users online @ same time)

  The MySQL database is slow.

> After I wrote a small benchmark script for testing this kind of queries
> (10 000 queries "UPDATE radacct SET ... "  with random integer values
> for acctsessiontime, acctinputoctets and acctoutputoctets) I can't
> reproduce this problem. My script takes 10 000 random rows from radacct
> table (table has almost 600 000 rows) and it is finished in 130-180
> seconds (executed 5 times and all results are between 130 and 180
> seconds). That means 0,013-0,018s/query (average)

  Have you checked the sqltrace file?  If not (or if it doesn't exist),
enable it, and use it.

> Slow query log is empty after this benchm. script. I checked radacct
> table later and I have exaclty 10 000 updated rows so the script is OK.
> 
> Any ideas how to trace this problem ?

  My guess is that you're not receiving accounting start packets.  In
this case, the SQL module tries to do an update, fails, and then does an
insert.

  It's likely that your test is updating *existing* radacct rows.  Try
changing it to do 10,000 inserts.  Odds are it will be slow.

  In this case, there really isn't a whole log of magic on the
FreeRADIUS side.  It does SQL queries by calling MySQL functions.  Once
execution has been passed to the MySQL function, it has really very
little to do with FreeRADIUS.

  Look at the sqltrace file, and try re-playing the queries there into a
*fresh* database.  i.e. wipe the radacct database, and let it run for a
while.  Then wipe the database *again*, and re-play the sqltrace file.

 Odds are that replaying it with the MySQL command-line client will be
just as slow as you saw with FreeRADIUS.

  Alan DeKok.