Tweaking the indexes on the table might help so that fewer rows get locked by the transaction, and tuning the InnoDB engine. Essentially you want to speed the queries up so that the locked rows get released faster so that the query doesn't wait until it times out. InnoDB also flushes the transaction log to disk on every commit (required for ACID) which limits you to probably ~200 transactions per second, which could also cause issues if the database is used for stuff other than FreeSWITCH. The batched statements you're using should help with that since it means fewer commits, but you also need to look at what else is happening on the server.

SHOW ENGINE INNODB STATUS will show some useful information, it should include a log of the last lock wait timeout error which'll include the query that timed out and what had the rows it was waiting for locked. That might  help too.


On 20 November 2013 17:10, Robert Hadley <robert.hadley@teotech.com> wrote:

Hi Freeswitch Users,

 

I am using recent stable branch, the 1.2.14 release.  I am using mysql with odbc.  I am getting occassional lock wait timeout errors making calls ringing multiple endpoints or even when phones register.

 

Running on CentOS 5.7 on 4 CPU 8 Thread server.

 

ODBC has OPTION=67108864 enables batched statements.

 

2013-11-20 08:58:29.401918 [ERR] switch_odbc.c:514 ERR: [insert into sip_authentication (nonce,expires,profile_name,hostname, last_nc) values('9f3ba9e3-3190-4267-9a41-db18c51ad8e7', 1384970318, 'internal', 'TeoProxy-3.teotechnologies.com', 0)]

[STATE: HY000 CODE 1205 ERROR: [unixODBC][MySQL][ODBC 5.2(w) Driver][mysqld-5.5.30-log]Lock wait timeout exceeded; try restarting transaction]

2013-11-20 08:58:29.401918 [ERR] switch_core_sqldb.c:585 ODBC SQL ERR [STATE: HY000 CODE 1205 ERROR: [unixODBC][MySQL][ODBC 5.2(w) Driver][mysqld-5.5.30-log]Lock wait timeout exceeded; try restarting transaction]

insert into sip_authentication (nonce,expires,profile_name,hostname, last_nc) values('9f3ba9e3-3190-4267-9a41-db18c51ad8e7', 1384970318, 'internal', 'TeoProxy-3.teotechnologies.com', 0)

 

What is the fix for this please.

 

Thanks,

Robert

 

 


_________________________________________________________________________
Professional FreeSWITCH Consulting Services:
consulting@freeswitch.org
http://www.freeswitchsolutions.com

FreeSWITCH-powered IP PBX: The CudaTel Communication Server
http://www.cudatel.com

Official FreeSWITCH Sites
http://www.freeswitch.org
http://wiki.freeswitch.org
http://www.cluecon.com

FreeSWITCH-users mailing list
FreeSWITCH-users@lists.freeswitch.org
http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
http://www.freeswitch.org