<div dir="ltr">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 <span style="font-family:Calibri,sans-serif;font-size:15px">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.</span><div>
<span style="font-family:Calibri,sans-serif;font-size:15px"><br></span></div><div><span style="font-family:Calibri,sans-serif;font-size:15px">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.</span></div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On 20 November 2013 17:10, Robert Hadley <span dir="ltr"><<a href="mailto:robert.hadley@teotech.com" target="_blank">robert.hadley@teotech.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-US" link="blue" vlink="purple">
<div>
<p class="MsoNormal">Hi Freeswitch Users,<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">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.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Running on CentOS 5.7 on 4 CPU 8 Thread server. <u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">ODBC has OPTION=67108864 enables batched statements.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">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', '<a href="http://TeoProxy-3.teotechnologies.com" target="_blank">TeoProxy-3.teotechnologies.com</a>',
0)]<u></u><u></u></p>
<p class="MsoNormal">[STATE: HY000 CODE 1205 ERROR: [unixODBC][MySQL][ODBC 5.2(w) Driver][mysqld-5.5.30-log]Lock wait timeout exceeded; try restarting transaction]<u></u><u></u></p>
<p class="MsoNormal">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]<u></u><u></u></p>
<p class="MsoNormal">insert into sip_authentication (nonce,expires,profile_name,hostname, last_nc) values('9f3ba9e3-3190-4267-9a41-db18c51ad8e7', 1384970318, 'internal', '<a href="http://TeoProxy-3.teotechnologies.com" target="_blank">TeoProxy-3.teotechnologies.com</a>', 0)<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">What is the fix for this please.<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal">Thanks,<u></u><u></u></p>
<p class="MsoNormal">Robert<u></u><u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><u></u> <u></u></p>
</div>
</div>
<br>_________________________________________________________________________<br>
Professional FreeSWITCH Consulting Services:<br>
<a href="mailto:consulting@freeswitch.org">consulting@freeswitch.org</a><br>
<a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
<br>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
<a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
<br>
Official FreeSWITCH Sites<br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
<a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br></blockquote></div><br></div>