Slow "update radacct ..." query

[A.L.M.Buxey at lboro.ac.uk]

Hi,

> 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)
> 
> 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)
> 
> 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.

mysql isnt too hot - even with innoDB. postgres can have a quicker throughput
on such operations....however, you can speedmysql up a little further by the
addition (and removal!) of indexes for the table.  do some 'explain' commands
instead of straight 'update' to see what the operation does and why it takes
so long - usually its having to go through far too many records. you should
have this down to sub-seconds after some work. you may need to play with /etc/my.cnf
to give yourself much larger buffers and key space etc. you should have some
templates somewhere on your filesystem  eg my-large.cnf (locate my-large) to give
you some idea of play values.

alan