Hello, I'm having some issues with the number of queries that Freeswitch generates per-second. Currently, I have about 160 phones registered to FS. These are almost all behind NAT, and all of them have to share presence (there are 2 profiles depending on which network they are on), as well as get options pings and that sort of thing. Database is MySQL 5.5. I'm seeing 18 queries per second, mostly complicated internal joins on tables like sip_dialogs and sip_subscriptions. Freeswitch is Version 1.0.head (git-7531fed 2011-08-17 11-27-20 -0500) and hardly breaking a sweat, while the database server is at 100% CPU load. If I stop the internal profiles, the database server goes right back down. When I start up the profiles again, the load slowly climbs back up to near 100%. The net result of this is that sofia doesn't always return it's options ping, (so other circuits think it's down), and we get slow / no call setup. Any suggestions as to how I can reduce this? -Tim
On Mon, 2011-10-24 at 17:36 -0400, Tim St. Pierre wrote:
Database is MySQL 5.5. I'm seeing 18 queries per second, mostly complicated internal joins on tables like sip_dialogs and sip_subscriptions.
Freeswitch is Version 1.0.head (git-7531fed 2011-08-17 11-27-20 -0500) and hardly breaking a sweat, while the database server is at 100% CPU load.
You need to optimize your database. Most likely you need to index tables that are used in joins (I doubt further optimization is needed for such a small dataset and such a light load). I don't use MySQL, but I recall it has a way to log slow queries. You should turn this on and use the output to figure out the right queries to optimize. Don't blindly create needless indexes as this will actually slow it more. Cliff
make sure you have it setup so it can do transactions or multiple statements. On Mon, Oct 24, 2011 at 5:11 PM, Cliff Wells <cliff@develix.com> wrote:
On Mon, 2011-10-24 at 17:36 -0400, Tim St. Pierre wrote:
Database is MySQL 5.5. I'm seeing 18 queries per second, mostly complicated internal joins on tables like sip_dialogs and sip_subscriptions.
Freeswitch is Version 1.0.head (git-7531fed 2011-08-17 11-27-20 -0500) and hardly breaking a sweat, while the database server is at 100% CPU load.
You need to optimize your database. Most likely you need to index tables that are used in joins (I doubt further optimization is needed for such a small dataset and such a light load). I don't use MySQL, but I recall it has a way to log slow queries. You should turn this on and use the output to figure out the right queries to optimize. Don't blindly create needless indexes as this will actually slow it more.
Cliff
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
-- Anthony Minessale II FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
For the transactions, what would be required to change? Any tweaks in the ODBC driver/DSN settins? Hynek On Tue, Oct 25, 2011 at 4:49 PM, Anthony Minessale < anthony.minessale@gmail.com> wrote:
make sure you have it setup so it can do transactions or multiple statements.
On Mon, Oct 24, 2011 at 5:11 PM, Cliff Wells <cliff@develix.com> wrote:
On Mon, 2011-10-24 at 17:36 -0400, Tim St. Pierre wrote:
Database is MySQL 5.5. I'm seeing 18 queries per second, mostly complicated internal joins on tables like sip_dialogs and sip_subscriptions.
Freeswitch is Version 1.0.head (git-7531fed 2011-08-17 11-27-20 -0500) and hardly breaking a sweat, while the database server is at 100% CPU load.
You need to optimize your database. Most likely you need to index tables that are used in joins (I doubt further optimization is needed for such a small dataset and such a light load). I don't use MySQL, but I recall it has a way to log slow queries. You should turn this on and use the output to figure out the right queries to optimize. Don't blindly create needless indexes as this will actually slow it more.
Cliff
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
-- Anthony Minessale II
FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire
AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch
FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
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 don't particularly like mysql so I don't know the exact answer, but I do know that by default it disables transactions and multi statements as a knee-jerk solution to injection and that it's some numeric param deep in some config file. So I would just google for "enable transactions mysql" or something like that. You can tell if it matters if freeswitch complains about transactions disabled when it connect to the db for the first time. On Tue, Oct 25, 2011 at 9:55 AM, Hynek Cihlar <hynek.cihlar@gmail.com>wrote:
For the transactions, what would be required to change? Any tweaks in the ODBC driver/DSN settins?
Hynek
On Tue, Oct 25, 2011 at 4:49 PM, Anthony Minessale < anthony.minessale@gmail.com> wrote:
make sure you have it setup so it can do transactions or multiple statements.
On Mon, Oct 24, 2011 at 5:11 PM, Cliff Wells <cliff@develix.com> wrote:
On Mon, 2011-10-24 at 17:36 -0400, Tim St. Pierre wrote:
Database is MySQL 5.5. I'm seeing 18 queries per second, mostly complicated internal joins on tables like sip_dialogs and sip_subscriptions.
Freeswitch is Version 1.0.head (git-7531fed 2011-08-17 11-27-20 -0500) and hardly breaking a sweat, while the database server is at 100% CPU load.
You need to optimize your database. Most likely you need to index tables that are used in joins (I doubt further optimization is needed for such a small dataset and such a light load). I don't use MySQL, but I recall it has a way to log slow queries. You should turn this on and use the output to figure out the right queries to optimize. Don't blindly create needless indexes as this will actually slow it more.
Cliff
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
-- Anthony Minessale II
FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire
AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch
FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
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
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
-- Anthony Minessale II FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
Of course that MySQL supports and enables transaction by default. It is the SQL statements that must tell the DB when to start a transaction, commit, rollback. I had an issue with batch statements to be enabled on the ODBC connection, once on, it works ok. Hynek On Tue, Oct 25, 2011 at 5:00 PM, Anthony Minessale < anthony.minessale@gmail.com> wrote:
I don't particularly like mysql so I don't know the exact answer, but I do know that by default it disables transactions and multi statements as a knee-jerk solution to injection and that it's some numeric param deep in some config file. So I would just google for "enable transactions mysql" or something like that.
You can tell if it matters if freeswitch complains about transactions disabled when it connect to the db for the first time.
On Tue, Oct 25, 2011 at 9:55 AM, Hynek Cihlar <hynek.cihlar@gmail.com>wrote:
For the transactions, what would be required to change? Any tweaks in the ODBC driver/DSN settins?
Hynek
On Tue, Oct 25, 2011 at 4:49 PM, Anthony Minessale < anthony.minessale@gmail.com> wrote:
make sure you have it setup so it can do transactions or multiple statements.
On Mon, Oct 24, 2011 at 5:11 PM, Cliff Wells <cliff@develix.com> wrote:
On Mon, 2011-10-24 at 17:36 -0400, Tim St. Pierre wrote:
Database is MySQL 5.5. I'm seeing 18 queries per second, mostly complicated internal joins on tables like sip_dialogs and sip_subscriptions.
Freeswitch is Version 1.0.head (git-7531fed 2011-08-17 11-27-20 -0500) and hardly breaking a sweat, while the database server is at 100% CPU load.
You need to optimize your database. Most likely you need to index tables that are used in joins (I doubt further optimization is needed for such a small dataset and such a light load). I don't use MySQL, but I recall it has a way to log slow queries. You should turn this on and use the output to figure out the right queries to optimize. Don't blindly create needless indexes as this will actually slow it more.
Cliff
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
-- Anthony Minessale II
FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire
AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch
FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
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
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
-- Anthony Minessale II
FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire
AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch
FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
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
Hi Anthony, which db engine do you prefer, this as recommendation for us, for better performance with FS? Thanks in advance Luis Jiménez Luis Jimenez -----Original Message----- From: Anthony Minessale <anthony.minessale@gmail.com> Sender: freeswitch-users-bounces@lists.freeswitch.org Date: Tue, 25 Oct 2011 10:00:29 To: FreeSWITCH Users Help<freeswitch-users@lists.freeswitch.org> Reply-To: FreeSWITCH Users Help <freeswitch-users@lists.freeswitch.org> Subject: Re: [Freeswitch-users] High load on database server 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
On Tue, 2011-10-25 at 17:50 +0000, Luis Jimenez wrote:
Hi Anthony, which db engine do you prefer, this as recommendation for us, for better performance with FS?
We use PostgreSQL 8.4. Our system currently processes around 350 concurrent calls during peak hours and PostgreSQL barely notices (every call accesses the database to get the dialplan for around 5000 numbers). We also access the same database for CDR logging and application-specific data, so the number of concurrent database connections is actually somewhat higher than that. That being said, unixODBC 2.2 will often segfault under that load, so be wary of that (it appears reliable up to about 300 connections, seems to reliably fall over at 330). We're in the process of testing 2.3.0 which is reportedly better in this regard. Anyway, I don't think choice of database is the issue here. I don't care for MySQL, but I don't think it should be any sort of bottleneck for your application. If you have poorly written or unindexed queries/joins, they will be slow on whatever database you throw at it. Also, does MySQL run on the same system as FreeSWITCH? Have you checked for basic issues such as memory pressure (or the inverse, failing to tune MySQL to utilize available resources), slow disk throughput, etc? Questions about database performance have no "one answer fits all" solution, so more data would need to be gathered than you've provided here. Regards, Cliff
That being said, unixODBC 2.2 will often segfault under that load, so be wary of that (it appears reliable up to about 300 connections, seems to reliably fall over at 330). We're in the process of testing 2.3.0 which is reportedly better in this regard.
This is unfortunate! I was planning to cluster several freeswitch instances through the DB server. But if the unixODBC crashes under high load, it is a major issue in this regard as there are not many options left to implement the cluster. Hynek
On 10/26/2011 06:49 AM, Hynek Cihlar wrote:
That being said, unixODBC 2.2 will often segfault under that load, so be wary of that (it appears reliable up to about 300 connections, seems to reliably fall over at 330). We're in the process of testing 2.3.0 which is reportedly better in this regard.
This is unfortunate! I was planning to cluster several freeswitch instances through the DB server. But if the unixODBC crashes under high load, it is a major issue in this regard as there are not many options left to implement the cluster.
If you use Red Hat RHEL or CentOS and there is a crash with stock unixODBC (2.2) then a bug should be reported at bugzilla.redhat.com. In my experience such bugs do get fixed. Regards, Patrick
if you have older unixodbc edit /etc/odbcinst.ini and add the param Threading=0 to each db entry. I like postgres better but that's mostly a personal preference from watching both of them grow up over the years. The only thing that crashes in ODBC is bad drivers typically. With mysql you often need to specify the "threadsafe" one by editing odbc.ini and adding in the _r to the name of the mysql driver .so file. In general postgres to me appears to care more about multithreaded environments and with the above threading=0 change works pretty well. On Wed, Oct 26, 2011 at 5:40 AM, Patrick Lists < freeswitch-list@puzzled.xs4all.nl> wrote:
On 10/26/2011 06:49 AM, Hynek Cihlar wrote:
That being said, unixODBC 2.2 will often segfault under that load, so
be
wary of that (it appears reliable up to about 300 connections, seems
to
reliably fall over at 330). We're in the process of testing 2.3.0
which
is reportedly better in this regard.
This is unfortunate! I was planning to cluster several freeswitch instances through the DB server. But if the unixODBC crashes under high load, it is a major issue in this regard as there are not many options left to implement the cluster.
If you use Red Hat RHEL or CentOS and there is a crash with stock unixODBC (2.2) then a bug should be reported at bugzilla.redhat.com. In my experience such bugs do get fixed.
Regards, Patrick
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
-- Anthony Minessale II FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
On Wed, 2011-10-26 at 11:12 -0500, Anthony Minessale wrote:
if you have older unixodbc edit /etc/odbcinst.ini and add the param Threading=0 to each db entry.
In general postgres to me appears to care more about multithreaded environments and with the above threading=0 change works pretty well.
I tried this (after googling a bit and coming across an older thread you responded to) and it didn't change anything. Crashes at ~330 concurrent calls like clockwork. This was with unixODBC 2.14 and PostgreSQL 8.4 odbc driver. Regards, Cliff
try the version 2.3.0 of ODBC (compiled from sources) I can't tell you if it will work because I never had more than 20 councurrent calls but it should be better anyeway ----- Original Message ----- From: "Cliff Wells" <cliff@develix.com> To: "FreeSWITCH Users Help" <freeswitch-users@lists.freeswitch.org> Sent: Thursday, October 27, 2011 2:02 AM Subject: Re: [Freeswitch-users] High load on database server
On Wed, 2011-10-26 at 11:12 -0500, Anthony Minessale wrote:
if you have older unixodbc edit /etc/odbcinst.ini and add the param Threading=0 to each db entry.
In general postgres to me appears to care more about multithreaded environments and with the above threading=0 change works pretty well.
I tried this (after googling a bit and coming across an older thread you responded to) and it didn't change anything. Crashes at ~330 concurrent calls like clockwork. This was with unixODBC 2.14 and PostgreSQL 8.4 odbc driver.
Regards, Cliff
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
On Thu, 2011-10-27 at 02:15 -0400, Madovsky wrote:
try the version 2.3.0 of ODBC (compiled from sources)
Actually, I went ahead and built an rpm for 2.3.0 (I needed to satisfy the dependency for postgresql-odbc rpm). Unfortunately I haven't yet been able to test up to 330 concurrent calls since then because we ended up splitting the traffic across multiple servers to resolve the issue so no server is seeing more than 80-100 concurrent calls at this point. Cliff
On Tue, 2011-10-25 at 11:32 -0700, Cliff Wells wrote:
On Tue, 2011-10-25 at 17:50 +0000, Luis Jimenez wrote:
Hi Anthony, which db engine do you prefer, this as recommendation for us, for better performance with FS?
We use PostgreSQL 8.4. Our system currently processes around 350 concurrent calls during peak hours and PostgreSQL barely notices (every call accesses the database to get the dialplan for around 5000 numbers). We also access the same database for CDR logging and application-specific data, so the number of concurrent database connections is actually somewhat higher than that.
That being said, unixODBC 2.2 will often segfault under that load, so be wary of that (it appears reliable up to about 300 connections, seems to reliably fall over at 330). We're in the process of testing 2.3.0 which is reportedly better in this regard.
Just a short follow up on the stability of unixODBC 2.3.0 vs 2.2.x. We consistently saw segmentation faults at 330+ concurrent calls with 2.2.x. This morning I observed one of those sames systems handling over 365 concurrent calls with 2.3.0 without crashing. Nothing else has changed. Regards, Cliff
Tuning MySQL would be the first thing I would do. Download and run this small script, http://mysqltuner.pl and it would show you some configurable parameters of MySQL which may greatly enhance your DB performance. On Tue, Oct 25, 2011 at 5:49 PM, Anthony Minessale <anthony.minessale@gmail.com> wrote:
make sure you have it setup so it can do transactions or multiple statements.
On Mon, Oct 24, 2011 at 5:11 PM, Cliff Wells <cliff@develix.com> wrote:
On Mon, 2011-10-24 at 17:36 -0400, Tim St. Pierre wrote:
Database is MySQL 5.5. I'm seeing 18 queries per second, mostly complicated internal joins on tables like sip_dialogs and sip_subscriptions.
Freeswitch is Version 1.0.head (git-7531fed 2011-08-17 11-27-20 -0500) and hardly breaking a sweat, while the database server is at 100% CPU load.
You need to optimize your database. Most likely you need to index tables that are used in joins (I doubt further optimization is needed for such a small dataset and such a light load). I don't use MySQL, but I recall it has a way to log slow queries. You should turn this on and use the output to figure out the right queries to optimize. Don't blindly create needless indexes as this will actually slow it more.
Cliff
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
-- Anthony Minessale II
FreeSWITCH http://www.freeswitch.org/ ClueCon http://www.cluecon.com/ Twitter: http://twitter.com/FreeSWITCH_wire
AIM: anthm MSN:anthony_minessale@hotmail.com GTALK/JABBER/PAYPAL:anthony.minessale@gmail.com IRC: irc.freenode.net #freeswitch
FreeSWITCH Developer Conference sip:888@conference.freeswitch.org googletalk:conf+888@conference.freeswitch.org pstn:+19193869900
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
Use Memcache The recommended approach for using memcached and MySQL is: Write scale the database by sharding. Partition data across multiple servers so more data can be written in parallel. This avoids a single server becoming the bottleneck. Front MySQL with a memcached farm to scale reads. Applications access memcached first for data and if the data is not in memcached then the application tries the database. This removes a great deal of the load on a database so it can continue to perform it's transactional duties for writes. In this architecture the database is still the system of record for the true value of data. Use MySQL replication for reliability and read query scaling. There's an effective limit to the number of slaves that can be supported so just adding slaves won't work as scaling strategy for larger sites. Using this approach you get scalable reads and writes along with high availability. Given that MySQL has a cache, why is memcached needed at all? The MySQL cache is associated with just one instance. This limits the cache to the maximum address of one server. If your system is larger than the memory for one server then using the MySQL cache won't work. And if the same object is read from another instance its not cached. The query cache invalidates on writes. You build up all that cache and it goes away when someone writes to it. Your cache may not be much of a cache at all depending on usage patterns. The query cache is row based. Memcached can cache any type of data you want and it isn't limited to caching database rows. Memcached can cache complex complex objects that are directly usable without a join.
participants (9)
-
Anthony Minessale
-
Cliff Wells
-
EdPimentl
-
Hynek Cihlar
-
Luis Jimenez
-
Madovsky
-
Muhammad Naseer Bhatti
-
Patrick Lists
-
Tim St. Pierre