Problem with MySQL and Accouting-On record

[Alan DeKok]

On Nov 19, 2020, at 7:14 AM, Ángel L. Mateo <amateo at um.es> wrote:
> 
> (0) sql: ERROR: rlm_sql_mysql: ERROR 1205 (Lock wait timeout exceeded; try restarting transaction): HY000
> (0) sql: SQL query returned: server error

  Well, that's bad.

> 	There is a lock timeout. I think the problem is:
> 
> * My database is huge, about 6GB

  There's no reason for that.  You need *a* database.  You don't need *one* database.

  The DB used by RADIUS should be a "production" database.  It should be kept small, to minimize these kinds of issues.

  Any "historical" information can go into a separate database.  If you're doing billing, or data analysis, it's OK for the queries to take 5 seconds.  It is definitely *not* OK for a RADIUS query to take 5 seconds.

> * The update uses a WHERE with 3 fields, acctstoptime, nasipaddress and acctstarttime. I'm not a DBA, but although I have indexes in these 3 fields, in this query I think that only the index for acctstoptime field is use.
> 
> 	I'm going to try to create an complex index for (acctstoptime, nasipaddress, acctstarttime) and check if this solves the problem.

  That should help a lot.  Also, split the database into "production" and "historical".  Copy data daily from "production" to "historical". and then delete the data from "production".

  If the RADIUS server doesn't use the information, then it shouldn't be in the database used by RADIUS.

  Alan DeKok.