[Freeswitch-users] Using mysql w/odbc getting Lock wait timeout exceeded occassionally for calls, registrations

[Steven Ayre]

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 at 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 at freeswitch.org
> http://www.freeswitchsolutions.com
>
> 
> 
>
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
>
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20131120/f976de98/attachment.html