El 17/11/20 a las 15:01, Alan DeKok escribió:
On Nov 17, 2020, at 8:10 AM, Ángel L. Mateo <amateo@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