[Freeswitch-users] Situation with freeswitch dbh + odbc (mysql) in lua

[John Nash]

Ultimately it turned out to be one costly query, I had to fix that and did
not see this issue since then. (running 500 calls).

On Sat, Nov 14, 2015 at 8:18 PM, John Nash <john.nash778 at gmail.com> wrote:

> one more test observation. I specified readtimeout to 10 seconds and on
> database server allocated only 50 connections limit to the user which is
> used in freeswitch and let the issue replicate and found that there is no
> query failure because of timeout, however dbh is unable to get new
> connections after a while.
>
>
>
> On Sat, Nov 14, 2015 at 5:44 AM, Michael Giagnocavo <mgg at giagnocavo.net>
> wrote:
>
>> Just consider your failure case: What do you do when the HTTP endpoint or
>> DB is down? In most cases, if CDRs are really important, you’ll write to
>> disk then queue them up and insert them when the service is up again. Which
>> makes ya wonder why bother supporting two paths in the first place? Just
>> always write the CDRs to disk, then pick them up. Note that you’ll wanna
>> patch xml-cdr as it just writes to the file in-place, so you don’t know
>> when it’s done. (It should instead write to a tmp file then move when
>> finished.) Then queue them whatever way you like (with an actual message
>> queue, or just with a loop that tries to insert the cdr and delete the
>> file.)
>>
>>
>>
>> If you feel good enough you can always keep a local service up alongside
>> FS, you might run a local DB or message queue and go direct from FS to that.
>>
>>
>>
>> -Michael
>>
>>
>>
>> *From:* freeswitch-users-bounces at lists.freeswitch.org [mailto:
>> freeswitch-users-bounces at lists.freeswitch.org] *On Behalf Of *John Nash
>> *Sent:* Friday, November 13, 2015 12:49 PM
>> *To:* FreeSWITCH Users Help <freeswitch-users at lists.freeswitch.org>
>> *Subject:* Re: [Freeswitch-users] Situation with freeswitch dbh + odbc
>> (mysql) in lua
>>
>>
>>
>> Thank you very much for the comments. Yes it had to be the long running
>> queries and the way lua handles connections, it creates a new connection if
>> existing pool is all in use. Apart from dialplan lua to play balance, I am
>> also logging CDR in database in reporting hook (lua scripts again) which
>> now I am thinking of scrapping , will XML-curl or XML-CDR be a better
>> option to post CDR into remote database?
>>
>>
>> Memcache is an excellent suggestion but I do not see any command in Lua
>> api documentation on how to use it. I will do more searching.
>>
>> I also want to avoid calls stuck as CS_HIBERNATE (which i think is
>> because in hangup hook I am using lua script to log CDR and in case DB is
>> non responsive however I am using luarun so theoritically it should not
>> stuck right?)
>>
>>
>>
>>
>>
>>
>>
>> On Fri, Nov 13, 2015 at 7:55 PM, Luis Daniel Lucio Quiroz <
>> luis.daniel.lucio at gmail.com> wrote:
>>
>> If they take too long go for indexing and memcached
>>
>> Le 13 nov. 2015 8:37 AM, "John Nash" <john.nash778 at gmail.com> a écrit :
>>
>> Thank you for sharing ideas ..
>>
>> 1- I have made sure that dbh is released even in cases of query error.
>>
>> 2- In mysql processlist I see lot of connections but all in sleep mode.
>>
>> My stored procedure is complicated but it has all select statements and I
>> run with least possible locking isolation. But I am not really sure on if
>> with time or because of some other application stored procedure is taking
>> long to to execute hence each new request creating more dbh handles. I am
>> also using readtimeout as 10 seconds in odbc.ini so that in case stored
>> procedure takes time, i get error and then release handle.
>>
>>
>>
>>
>>
>> On Fri, Nov 13, 2015 at 6:49 PM, Florent Krieg <flokrrr at gmail.com> wrote:
>>
>> Hello John,
>>
>>
>>
>> If you are under heavy load, could this be a db issue? Like the db engine
>> taking long to return the data, hence queries are getting queued one after
>> the other and finally reach a few hundreds of it?
>>
>> Or you are right and your Lua script sometimes doesn't release the
>> handler as it just shall... To check this, you could for instance log a
>> debug message before and after doing your db query, and check after a few
>> minutes if you have the equivalent of 'before' and 'after' query
>> messages... If more before than after, then something is happening.
>>
>>
>>
>> You could do a 'show processlist;' on the MySQL cli too to understand
>> what are exactly the few hundreds of open connections.
>>
>>
>>
>> Also, is your stored procedure complicated? I'm not DBA but if it's too
>> tricky, you could imagine to move the load on the Lua processing instead of
>> MySQL. I have the same use cases on my environment and I made sure my MySQL
>> queries are dumb as possible and I take the benefit of Lua to process the
>> data returned.
>>
>>
>>
>> I'm just dropping some ideas that I'm thinking about now. Nothing very
>> precise though (sorry...).
>>
>> Florent
>>
>>
>>
>> 2015-11-13 12:49 GMT+01:00 John Nash <john.nash778 at gmail.com>:
>>
>> I am calling a lua script from dialplan which does following steps ...
>>
>> 1- Gets freeswitch dbh handle (odbc mysql)
>>
>> 2- Runs a mysql stored procedure which returns balance remaining amount
>> for the subscriber who made call.
>>
>> 3- Save the amount in session variable and released dbh handle
>>
>> 4- Play sound files as per output received and saved in step 3
>>
>> Now upto 200 calls and first 2-3 hours of start all work fine. I can see
>> in Database that script uses 32 connections approx at a given time (I think
>> connection pooling works) but suddenly connections start increasing and go
>> upto 300+ and because of that database stops responding and i have to kill
>> freeswitch to make production DB responsive again.
>>
>> I am not sure where and what I should check. Can someone give me pointers
>> on what I can check?..Or I am doing something fundamentally wrong.
>>
>> Can there be such a case where lua scripts hang and never release
>> connection?
>>
>>
>>
>>
>>
>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://confluence.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
>>
>>
>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://confluence.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
>>
>>
>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://confluence.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
>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://confluence.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
>>
>>
>>
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>>
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://confluence.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/20151123/3b97cafc/attachment.html