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.