mod_callcenter overloading database when using MySQL via unixODBC
I have 2 FS servers sharing a mysql database via ODBC. There are 2 queues loaded in mod_callcenter and there is absolutely zero activity around mod_callcenter. The mysql.log is showing many queries a few times per second like: "select 1" "SELECT queue,uuid,session_uuid,cid_number,cid_name,joined_epoch,(1329162473-joined_epoch)+base_score+skill_score AS score, state, abandoned_epoch FROM members WHERE state = 'Waiting' OR state = 'Abandoned' OR (serving_agent = 'ring-all' AND state = 'Trying') ORDER BY score DESC" Even if i run "callcenter_config queue unload support1@default" to unload the queues from both FS servers, the mysql.log still doing the queries. The only way the queries stop is if i do "unload mod_callcenter" Here is my callcenter.conf: <configuration name="callcenter.conf" description="Call Center Module"> <settings> <param name="odbc-dsn" value="freeswitch:freeswitch:freeswitch"/> </settings> <queues> <queue name="support1@default"> <param name="strategy" value="longest-idle-agent"/> <param name="moh-sound" value="local_stream://moh/8000"/> <param name="time-base-score" value="system"/> <param name="max-wait-time" value="0"/> <param name="max-wait-time-with-no-agent" value="0"/> <param name="max-wait-time-with-no-agent-time-reached" value="5"/> <param name="tier-rules-apply" value="false"/> <param name="tier-rule-wait-second" value="300"/> <param name="tier-rule-wait-multiply-level" value="true"/> <param name="tier-rule-no-agent-no-wait" value="false"/> <param name="discard-abandoned-after" value="60"/> <param name="abandoned-resume-allowed" value="false"/> </queue> <queue name="support2@default"> <param name="strategy" value="longest-idle-agent"/> <param name="moh-sound" value="local_stream://moh/8000"/> <param name="time-base-score" value="system"/> <param name="max-wait-time" value="0"/> <param name="max-wait-time-with-no-agent" value="0"/> <param name="max-wait-time-with-no-agent-time-reached" value="5"/> <param name="tier-rules-apply" value="false"/> <param name="tier-rule-wait-second" value="300"/> <param name="tier-rule-wait-multiply-level" value="true"/> <param name="tier-rule-no-agent-no-wait" value="false"/> <param name="discard-abandoned-after" value="60"/> <param name="abandoned-resume-allowed" value="false"/> </queue> </queues> </configuration>
I don't see much about an overload here, a few queries per second should be hardly noticable on the MySQL instance? /Peter ________________________________________ Från: freeswitch-users-bounces@lists.freeswitch.org [freeswitch-users-bounces@lists.freeswitch.org] för Vik Killa [vipkilla@gmail.com] Skickat: den 13 februari 2012 20:57 Till: FreeSWITCH Users Help Ämne: [Freeswitch-users] mod_callcenter overloading database when using MySQL via unixODBC I have 2 FS servers sharing a mysql database via ODBC. There are 2 queues loaded in mod_callcenter and there is absolutely zero activity around mod_callcenter. The mysql.log is showing many queries a few times per second like: "select 1" "SELECT queue,uuid,session_uuid,cid_number,cid_name,joined_epoch,(1329162473-joined_epoch)+base_score+skill_score AS score, state, abandoned_epoch FROM members WHERE state = 'Waiting' OR state = 'Abandoned' OR (serving_agent = 'ring-all' AND state = 'Trying') ORDER BY score DESC" Even if i run "callcenter_config queue unload support1@default" to unload the queues from both FS servers, the mysql.log still doing the queries. The only way the queries stop is if i do "unload mod_callcenter" Here is my callcenter.conf: <configuration name="callcenter.conf" description="Call Center Module"> <settings> <param name="odbc-dsn" value="freeswitch:freeswitch:freeswitch"/> </settings> <queues> <queue name="support1@default"> <param name="strategy" value="longest-idle-agent"/> <param name="moh-sound" value="local_stream://moh/8000"/> <param name="time-base-score" value="system"/> <param name="max-wait-time" value="0"/> <param name="max-wait-time-with-no-agent" value="0"/> <param name="max-wait-time-with-no-agent-time-reached" value="5"/> <param name="tier-rules-apply" value="false"/> <param name="tier-rule-wait-second" value="300"/> <param name="tier-rule-wait-multiply-level" value="true"/> <param name="tier-rule-no-agent-no-wait" value="false"/> <param name="discard-abandoned-after" value="60"/> <param name="abandoned-resume-allowed" value="false"/> </queue> <queue name="support2@default"> <param name="strategy" value="longest-idle-agent"/> <param name="moh-sound" value="local_stream://moh/8000"/> <param name="time-base-score" value="system"/> <param name="max-wait-time" value="0"/> <param name="max-wait-time-with-no-agent" value="0"/> <param name="max-wait-time-with-no-agent-time-reached" value="5"/> <param name="tier-rules-apply" value="false"/> <param name="tier-rule-wait-second" value="300"/> <param name="tier-rule-wait-multiply-level" value="true"/> <param name="tier-rule-no-agent-no-wait" value="false"/> <param name="discard-abandoned-after" value="60"/> <param name="abandoned-resume-allowed" value="false"/> </queue> </queues> </configuration> _________________________________________________________________________ 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 !DSPAM:4f3969df32768328019793!
It is bogging down my MySQL server. On Mon, Feb 13, 2012 at 3:07 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
I don't see much about an overload here, a few queries per second should be hardly noticable on the MySQL instance?
<snip>
If your MySQL server won't handle load for a couple of queries per second you'll really need to tweak the MySQL configuration. I didn't look much more into this though, there might of course also be a missing index or something like that. Start by tweaking the MySQL server (default config is more or less useless for any real work), especially the InnoDB buffer pool size is important. Also enable slow query logging, and log queries without indexes - this might give you a hint for what you need to do. /Peter ________________________________________ Från: freeswitch-users-bounces@lists.freeswitch.org [freeswitch-users-bounces@lists.freeswitch.org] för Vik Killa [vipkilla@gmail.com] Skickat: den 13 februari 2012 21:22 Till: FreeSWITCH Users Help Ämne: Re: [Freeswitch-users] mod_callcenter overloading database when using MySQL via unixODBC It is bogging down my MySQL server. On Mon, Feb 13, 2012 at 3:07 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
I don't see much about an overload here, a few queries per second should be hardly noticable on the MySQL instance?
<snip> _________________________________________________________________________ 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 !DSPAM:4f396faf32766962924742!
It's actually doing like 50 queries per second... i'd just like to understand if this is normal and if so, why? On Mon, Feb 13, 2012 at 3:39 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
If your MySQL server won't handle load for a couple of queries per second you'll really need to tweak the MySQL configuration. I didn't look much more into this though, there might of course also be a missing index or something like that.
Start by tweaking the MySQL server (default config is more or less useless for any real work), especially the InnoDB buffer pool size is important. Also enable slow query logging, and log queries without indexes - this might give you a hint for what you need to do.
/Peter ___________
<snip>
I just looked quickly on the code, and there is a thread created at load time, that will execute this query once every 100ms, so this should cause 10 queries per second, and because of the db connection verification in FS it will also cause a "SELECT 1" before each query. So you should have 20 queries per second. I'm not sure if this could be done in a different way, the author would have to answer that. However, this should not cause any problems on a properly configured MySQL server. You should be able to handle thousands of queries per second on a MySQL server. /Peter ________________________________________ Från: freeswitch-users-bounces@lists.freeswitch.org [freeswitch-users-bounces@lists.freeswitch.org] för Vik Killa [vipkilla@gmail.com] Skickat: den 13 februari 2012 21:47 Till: FreeSWITCH Users Help Ämne: Re: [Freeswitch-users] mod_callcenter overloading database when using MySQL via unixODBC It's actually doing like 50 queries per second... i'd just like to understand if this is normal and if so, why? On Mon, Feb 13, 2012 at 3:39 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
If your MySQL server won't handle load for a couple of queries per second you'll really need to tweak the MySQL configuration. I didn't look much more into this though, there might of course also be a missing index or something like that.
Start by tweaking the MySQL server (default config is more or less useless for any real work), especially the InnoDB buffer pool size is important. Also enable slow query logging, and log queries without indexes - this might give you a hint for what you need to do.
/Peter ___________
<snip> _________________________________________________________________________ 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 !DSPAM:4f3975d632761156120760!
20 queries per second * number of FS servers, in this case 20 * 2 = 40 queries per second... I'm just running this in a testing environment but it definitely bogs down the mysql server. On Mon, Feb 13, 2012 at 4:05 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
I just looked quickly on the code, and there is a thread created at load time, that will execute this query once every 100ms, so this should cause 10 queries per second, and because of the db connection verification in FS it will also cause a "SELECT 1" before each query. So you should have 20 queries per second.
I'm not sure if this could be done in a different way, the author would have to answer that. However, this should not cause any problems on a properly configured MySQL server. You should be able to handle thousands of queries per second on a MySQL server.
/Peter
<snip>
Follow the steps below and tweak the MySQL server. Especially make sure you have enough RAM and that you have increased the InnoDB buffer pool size. As I said, in a properly configured server this shouldn't be even noticeable. /Peter ________________________________________ Från: freeswitch-users-bounces@lists.freeswitch.org [freeswitch-users-bounces@lists.freeswitch.org] för Vik Killa [vipkilla@gmail.com] Skickat: den 13 februari 2012 22:15 Till: FreeSWITCH Users Help Ämne: Re: [Freeswitch-users] mod_callcenter overloading database when using MySQL via unixODBC 20 queries per second * number of FS servers, in this case 20 * 2 = 40 queries per second... I'm just running this in a testing environment but it definitely bogs down the mysql server. On Mon, Feb 13, 2012 at 4:05 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
I just looked quickly on the code, and there is a thread created at load time, that will execute this query once every 100ms, so this should cause 10 queries per second, and because of the db connection verification in FS it will also cause a "SELECT 1" before each query. So you should have 20 queries per second.
I'm not sure if this could be done in a different way, the author would have to answer that. However, this should not cause any problems on a properly configured MySQL server. You should be able to handle thousands of queries per second on a MySQL server.
/Peter
<snip> _________________________________________________________________________ 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 !DSPAM:4f397c8032769964980433!
I'm beginning to wonder if mod_callcenter works across multiple FS boxes... I've getting an error like: 2012-02-13 16:39:41.210506 [ERR] mod_callcenter.c:1399 Member SomeUA <1004> with uuid 3c6825b2-568b-11e1-842f-f7320940deaa in queue support1@default is gone just before we assigned an agent Anybody know what that means? Also has anybody used mod_callcenter across more than one instance of FS? On Mon, Feb 13, 2012 at 4:35 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
Follow the steps below and tweak the MySQL server. Especially make sure you have enough RAM and that you have increased the InnoDB buffer pool size. As I said, in a properly configured server this shouldn't be even noticeable.
/Peter <snip>
I had a similar problem with MySQL not being able to keep up. I changed the engine from innoDB to MEMORY and my CPU load went from 99% to 4%, and my disk IO had a similar drop. alter table XXX engine='MEMORY'; Do you really need the calls in your queue to persist? The only caveat - if you are running a multi-master mysql setup, restarting a server will propagate a "delete from XXX" query that flushes the tables, so keep that in mind. -Tim Vik Killa wrote:
It is bogging down my MySQL server.
On Mon, Feb 13, 2012 at 3:07 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
I don't see much about an overload here, a few queries per second should be hardly noticable on the MySQL instance?
<snip>
_________________________________________________________________________ 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
InnoDB should perform almost as good as well - but you will need to tweak it. If you have as much RAM (reserved for MySQL) as the size of your database it will also use memory only, except when writing UPDATE/INSERT/DELETE of course. It's a common missunderstanding that you can install a database server with default settings and just go on. It's just like you would install FreeSWITCH and never modify the dialplan . it's a good start, but you will surely need to make changes to get it working the way you want. Anyway, this is not a database mailing list, so I won't go any further than this :) /Peter ________________________________________ Från: freeswitch-users-bounces@lists.freeswitch.org [freeswitch-users-bounces@lists.freeswitch.org] för Tim St. Pierre [fs-list@communicatefreely.net] Skickat: den 14 februari 2012 02:33 Till: FreeSWITCH Users Help Ämne: Re: [Freeswitch-users] mod_callcenter overloading database when using MySQL via unixODBC I had a similar problem with MySQL not being able to keep up. I changed the engine from innoDB to MEMORY and my CPU load went from 99% to 4%, and my disk IO had a similar drop. alter table XXX engine='MEMORY'; Do you really need the calls in your queue to persist? The only caveat - if you are running a multi-master mysql setup, restarting a server will propagate a "delete from XXX" query that flushes the tables, so keep that in mind. -Tim Vik Killa wrote:
It is bogging down my MySQL server.
On Mon, Feb 13, 2012 at 3:07 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
I don't see much about an overload here, a few queries per second should be hardly noticable on the MySQL instance?
<snip>
_________________________________________________________________________ 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
_________________________________________________________________________ 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 !DSPAM:4f39b88132761488312585!
Tim: So you actually have mod_callcenter running on more than FS instance with a shared DB ? On Mon, Feb 13, 2012 at 8:33 PM, Tim St. Pierre <fs-list@communicatefreely.net> wrote:
I had a similar problem with MySQL not being able to keep up.
I changed the engine from innoDB to MEMORY and my CPU load went from 99% to 4%, and my disk IO had a similar drop.
alter table XXX engine='MEMORY';
Do you really need the calls in your queue to persist? The only caveat - if you are running a multi-master mysql setup, restarting a server will propagate a "delete from XXX" query that flushes the tables, so keep that in mind.
-Tim
Vik Killa wrote:
It is bogging down my MySQL server.
On Mon, Feb 13, 2012 at 3:07 PM, Peter Olsson <peter.olsson@visionutveckling.se> wrote:
I don't see much about an overload here, a few queries per second should be hardly noticable on the MySQL instance?
<snip>
participants (3)
-
Peter Olsson
-
Tim St. Pierre
-
Vik Killa