Problem with MySQL and Accouting-On record

[Ángel L. Mateo]

El 17/11/20 a las 15:01, Alan DeKok escribió:
> On Nov 17, 2020, at 8:10 AM, Ángel L. Mateo <amateo at um.es> wrote:
>> 	Althought this workaround worked in my test environment, I'm still having problems in production.
> 
>    Then something's different in the configuration between the two systems.
> 
	Configuration is the same, but I think I have found the problem in 
production. Although this isn't the whole debug capture (I couldn't 
take) I found this:

(0) sql: EXPAND UPDATE radacct SET acctstoptime = 
FROM_UNIXTIME(%{integer:Event-Timestamp}), acctsessiontime	= 
'%{integer:Event-Timestamp}' - UNIX_TIMESTAMP(acctstarttime), 
acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}' WHERE 
acctstoptime IS NULL AND nasipaddress   = '%{NAS-IP-Address}' AND 
acctstarttime <= FROM_UNIXTIME(%{integer:Event-Timestamp})
(0) sql:    --> UPDATE radacct SET acctstoptime = 
FROM_UNIXTIME(1605668493), acctsessiontime	= '1605668493' - 
UNIX_TIMESTAMP(acctstarttime), acctterminatecause = 'NAS-Reboot' WHERE 
acctstoptime IS NULL AND nasipaddress   = '155.54.213.6' AND 
acctstarttime <= FROM_UNIXTIME(1605668493)
(0) sql: Executing query: UPDATE radacct SET acctstoptime = 
FROM_UNIXTIME(1605668493), acctsessiontime	= '1605668493' - 
UNIX_TIMESTAMP(acctstarttime), acctterminatecause = 'NAS-Reboot' WHERE 
acctstoptime IS NULL AND nasipaddress   = '155.54.213.6' AND 
acctstarttime <= FROM_UNIXTIME(1605668493)
(0) sql: ERROR: rlm_sql_mysql: ERROR 1205 (Lock wait timeout exceeded; 
try restarting transaction): HY000
(0) sql: SQL query returned: server error
rlm_sql (sql): Released connection (0)
(0)     [sql] = fail
(0)   } # accounting = fail
(0) detail (/datapool/radacct/eduroam/eduroam_sql): No response to 
request.  Will retry in 30 seconds
(0) Finished request

	There is a lock timeout. I think the problem is:

* My database is huge, about 6GB
* 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.

-- 
Angel L. Mateo Martínez
Sección de Telemática
Área de Tecnologías de la Información
y las Comunicaciones Aplicadas (ATICA)
http://www.um.es/atica
Tfo: 868889150
Fax: 868888337