Deadlocks in Freeradius MySQL Database
Hi, I am testing freeradius version 3 with RedHat Enterprise Server 7.4 and MySQL 5.6.35, even in a newly fresh/initialized database I get these messages in radius.log, has anyone else been through this situation yet? what would be the best choice of operating system, database and freeradius version that recommend as more stable? ############################################################################### ERROR: (1526654) sqlippool: ERROR: rlm_sql_mysql: ERROR 1213 (Deadlock found when trying to get lock; try restarting transaction): 40001 [root@rad-a-01 radius]# cat radius.log | wc -l 197357 [root@rad-a-01 radius]# cat radius.log | grep Deadlock | wc -l 2468 [root@rad-a-01 radius]# rpm -qa | grep freeradius freeradius-3.0.13-8.el7_4.x86_64 freeradius-mysql-3.0.13-8.el7_4.x86_64 freeradius-utils-3.0.13-8.el7_4.x86_64 freeradius-ldap-3.0.13-8.el7_4.x86_64 [root@rad-a-01 radius]# uname -a Linux rad-a-01 3.10.0-693.5.2.el7.x86_64 #1 SMP Fri Oct 13 10:46:25 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux ############################################################################### Thanks, Claudio.
On Nov 4, 2017, at 8:45 AM, Claudio Carvalho <claudio@nextdigitalservices.com.br> wrote:
I am testing freeradius version 3 with RedHat Enterprise Server 7.4 and MySQL 5.6.35, even in a newly fresh/initialized database I get these messages in radius.log, has anyone else been through this situation yet?
Yes. MySQL isn't really the best choice. When FreeRADIUS tries to grab one IP address, MySQL locks the entire table. This isn't the best approach...
what would be the best choice of operating system, database and freeradius version that recommend as more stable?
PostgreSQL won't have this problem. It has row-level locks. Alan DeKok.
Thank you Alan. Claudio Carvalho -----Mensagem original----- De: Freeradius-Users [mailto:freeradius-users-bounces+claudio=nextdigitalservices.com.br@lists.freeradius.org] Em nome de Alan DeKok Enviada em: sábado, 4 de novembro de 2017 11:42 Para: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Assunto: Re: Deadlocks in Freeradius MySQL Database On Nov 4, 2017, at 8:45 AM, Claudio Carvalho <claudio@nextdigitalservices.com.br> wrote:
I am testing freeradius version 3 with RedHat Enterprise Server 7.4 and MySQL 5.6.35, even in a newly fresh/initialized database I get these messages in radius.log, has anyone else been through this situation yet?
Yes. MySQL isn't really the best choice. When FreeRADIUS tries to grab one IP address, MySQL locks the entire table. This isn't the best approach...
what would be the best choice of operating system, database and freeradius version that recommend as more stable?
PostgreSQL won't have this problem. It has row-level locks. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
What engine are you using? InnoDB or MyISAM? You should use the first one. As Alan said try also PostgreSQL. If I'm not wrong also MySQL with InnoDB should have row-level locks. GB
Hi AemNet, I'm using InnoDB in MySQL. Even using postgresql occurred some deadlocks in my test (100K accounts loading and 6hs of accounting(600s interval)), In 296031 radius.log lines, again happened 37 occurrences, the value was small and it seems to me that it administered by freeradius himself and the ip allocation was not lost. Sun Nov 5 12:08:21 2017 : ERROR: (4666465) sqlippool: ERROR: rlm_sql_postgresql: ERROR: deadlock detected Sun Nov 5 12:08:21 2017 : ERROR: (4666465) sqlippool: ERROR: rlm_sql_postgresql: DETAIL: Process 5083 waits for ShareLock on transaction 15919788; blocked by process 5054. Sun Nov 5 12:08:21 2017 : ERROR: (4666465) sqlippool: ERROR: rlm_sql_postgresql: Process 5054 waits for ShareLock on transaction 15919790; blocked by process 5083. Sun Nov 5 12:08:21 2017 : ERROR: (4666465) sqlippool: ERROR: rlm_sql_postgresql: HINT: See server log for query details. Sun Nov 5 12:08:21 2017 : ERROR: (4666465) sqlippool: ERROR: rlm_sql_postgresql: CONTEXT: while locking tuple (3309,126) in relation "radippool" Best regards, Claudio Carvalho -----Mensagem original----- De: Freeradius-Users [mailto:freeradius-users-bounces+claudio=nextdigitalservices.com.br@lists.freeradius.org] Em nome de AemNet Enviada em: segunda-feira, 6 de novembro de 2017 05:46 Para: freeradius-users@lists.freeradius.org Assunto: Re: Deadlocks in Freeradius MySQL Database What engine are you using? InnoDB or MyISAM? You should use the first one. As Alan said try also PostgreSQL. If I'm not wrong also MySQL with InnoDB should have row-level locks. GB - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I may have some time to look into sqlippool performance...but in the meantime you may need to tweak your postgres configs to make the server more optimal. there are several free tools that you can run against your PGSQL to see what can be tweaked (after the DB has been running for some hours and slammed by queries...get better results that way) eg pgtune (http://pgfoundry.org/projects/pgtune/) this will tweak your buffers, memory usage, cpu usage etc alan
Thank you Alan for your tips, in my tests I received a large number of this message, has anyone had this occurrence in your freeradius + Postgresql? Sun Nov 5 19:05:26 2017 : Error: rlm_sql_postgresql: 22P02: INVALID TEXT REPRESENTATION [root@rad-a-01 radius]# cat radius.log|grep "REPRESENTATION"| wc -l 1231 freeradius-3.0.13-8 Red Hat Enterprise 7.4 postgresql10-server-10.0-1PGDG Claudio Carvalho -----Mensagem original----- De: Freeradius-Users [mailto:freeradius-users-bounces+claudio=nextdigitalservices.com.br@lists.freeradius.org] Em nome de Alan Buxey Enviada em: segunda-feira, 6 de novembro de 2017 14:20 Para: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Assunto: Re: Deadlocks in Freeradius MySQL Database I may have some time to look into sqlippool performance...but in the meantime you may need to tweak your postgres configs to make the server more optimal. there are several free tools that you can run against your PGSQL to see what can be tweaked (after the DB has been running for some hours and slammed by queries...get better results that way) eg pgtune (http://pgfoundry.org/projects/pgtune/) this will tweak your buffers, memory usage, cpu usage etc alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Nov 6, 2017, at 11:54 AM, Claudio Carvalho <claudio@nextdigitalservices.com.br> wrote:
Thank you Alan for your tips,
in my tests I received a large number of this message, has anyone had this occurrence in your freeradius + Postgresql?
Sun Nov 5 19:05:26 2017 : Error: rlm_sql_postgresql: 22P02: INVALID TEXT REPRESENTATION
I haven't seen that before. Are you sure you're using the postgresql queries? And... as always, run it in debug mode to see what's going on. That should be your FIRST step. Alan DeKok.
Thank you Alan, I was running a test with 100K users and I did not realize the occurrence, only after the end of the test during the deep analysis of the logs, yes, I am using postgresql queries, next night I will run a new test and in this test the debug will be active . freeradius-3.0.13-8.el7_4.x86_64 freeradius-postgresql-3.0.13-8.el7_4.x86_64 Regards, Claudio -----Mensagem original----- De: Freeradius-Users [mailto:freeradius-users-bounces+claudio=nextdigitalservices.com.br@lists.freeradius.org] Em nome de Alan DeKok Enviada em: segunda-feira, 6 de novembro de 2017 15:01 Para: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Assunto: Re: RES: Deadlocks in Freeradius MySQL Database On Nov 6, 2017, at 11:54 AM, Claudio Carvalho <claudio@nextdigitalservices.com.br> wrote:
Thank you Alan for your tips,
in my tests I received a large number of this message, has anyone had this occurrence in your freeradius + Postgresql?
Sun Nov 5 19:05:26 2017 : Error: rlm_sql_postgresql: 22P02: INVALID TEXT REPRESENTATION
I haven't seen that before. Are you sure you're using the postgresql queries? And... as always, run it in debug mode to see what's going on. That should be your FIRST step. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (4)
-
AemNet -
Alan Buxey -
Alan DeKok -
Claudio Carvalho