Slow "update radacct ..." query

[Marinko Tarlac]

@ Alan DeKok, A.L.M. Buxey ---- Thanks for your time

Alan DeKok wrote:
> 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.
>   
After some investigation, I found that accounting start packets was 
received before update packets and session was already in database 
before the update happen. (AcctStartTime < MySQL time in slow query log)
Also, after few tests I have:
- 10 000 row updates which doesn't exist was done in avg. 4 seconds 
(tested 3 times)
- 10 000 inserts in less then 9 seconds (avg. tested 3 times)

my.cnf is a combination of my-huge.cnf and optimization tips I get with:
- tunning-primer.sh (http://www.day32.com/MySQL/tuning-primer.sh),
- mytop,
- mysqlreport (http://hackmysql.com/mysqlreport) and
- mysqltuner.pl (http://wiki.mysqltuner.com/MySQLTuner)

Similar results I get with default my-huge.cnf
>   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.
>   
I'll try this and that is the reason why I installed FR2.1.6.

Another option is to update MySQL to latest available version 5.0.x (in 
5.0 generation) because there are some indication that 5.0.45 has some 
bugs with cache and InnoDB storage engine...