Situation with freeswitch dbh + odbc (mysql) in lua
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?
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@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
I have ven working with MySQL since 3.x Check the following A close connections in lua B rise max connections in my.cnf C. Make connections time out minutes instead hours This is in my.cfn D rise threads E rise table caching F use indexes. Correct ones G add memcached support to your lua script where you can You will see a better performance. Le 13 nov. 2015 8:20 AM, "Florent Krieg" <flokrrr@gmail.com> a écrit :
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@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@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@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
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@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@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@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@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
If they take too long go for indexing and memcached Le 13 nov. 2015 8:37 AM, "John Nash" <john.nash778@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@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@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@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@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@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@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
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@gmail.com> wrote:
If they take too long go for indexing and memcached Le 13 nov. 2015 8:37 AM, "John Nash" <john.nash778@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@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@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@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@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@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@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@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@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
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@lists.freeswitch.org [mailto:freeswitch-users-bounces@lists.freeswitch.org] On Behalf Of John Nash Sent: Friday, November 13, 2015 12:49 PM To: FreeSWITCH Users Help <freeswitch-users@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@gmail.com<mailto:luis.daniel.lucio@gmail.com>> wrote: If they take too long go for indexing and memcached Le 13 nov. 2015 8:37 AM, "John Nash" <john.nash778@gmail.com<mailto:john.nash778@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@gmail.com<mailto:flokrrr@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@gmail.com<mailto:john.nash778@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@freeswitch.org<mailto:consulting@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@lists.freeswitch.org<mailto: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 _________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org<mailto:consulting@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@lists.freeswitch.org<mailto: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 _________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org<mailto:consulting@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@lists.freeswitch.org<mailto: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 _________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org<mailto:consulting@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@lists.freeswitch.org<mailto: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
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@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@lists.freeswitch.org [mailto: freeswitch-users-bounces@lists.freeswitch.org] *On Behalf Of *John Nash *Sent:* Friday, November 13, 2015 12:49 PM *To:* FreeSWITCH Users Help <freeswitch-users@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@gmail.com> wrote:
If they take too long go for indexing and memcached
Le 13 nov. 2015 8:37 AM, "John Nash" <john.nash778@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@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@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@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@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@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@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@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@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@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@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
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@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@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@lists.freeswitch.org [mailto: freeswitch-users-bounces@lists.freeswitch.org] *On Behalf Of *John Nash *Sent:* Friday, November 13, 2015 12:49 PM *To:* FreeSWITCH Users Help <freeswitch-users@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@gmail.com> wrote:
If they take too long go for indexing and memcached
Le 13 nov. 2015 8:37 AM, "John Nash" <john.nash778@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@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@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@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@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@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@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@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@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@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@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
One more observation I see lot of channels with CS_HIBERNATE state as a result of show channels command. On Fri, Nov 13, 2015 at 7:06 PM, John Nash <john.nash778@gmail.com> wrote:
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@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@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@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@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@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@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
participants (4)
-
Florent Krieg
-
John Nash
-
Luis Daniel Lucio Quiroz
-
Michael Giagnocavo