<HTML>
<HEAD>
<TITLE>Re: [Freeswitch-users] Performance bottleneck</TITLE>
</HEAD>
<BODY>
<FONT SIZE="4"><FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'>Actually having evaluated MySQL for large scale environments it works well up to a point... Postgres on the other hand has much more mature replication, store procedures, triggers, a query cacher that insane and no need to use mysql’s so called ‘hash tables’ to get the data loaded into ram (pg does this automagically via its caching mechanism)<BR>
<BR>
Mix that with table partitioning and you have some fairly crazy numbers<BR>
<BR>
Real world deployments doing some pretty complex LCR shows that a small cluster (4 boxes) or old dell 2650s are able to sustain > 40K queries/sec (~10% being inserts for CDRs)<BR>
<BR>
When you get into this scale the real enterprise work that has been done on pgsql starts to show thru... Companies like Greenplum feed a good bit of their core performance tuning back to PgSQL... (for those that are wondering pgsql is bsd licensed so its actually license compatible w/ FreeSwitch say some someone want to look at replacing sqlite with a pgsql engine)<BR>
<BR>
<BR>
<BR>
<BR>
<HR ALIGN=CENTER SIZE="3" WIDTH="95%"><B>From: </B>Darren Schreiber <d@d-man.org><BR>
<B>Reply-To: </B><freeswitch-users@lists.freeswitch.org><BR>
<B>Date: </B>Tue, 12 Aug 2008 12:35:25 -0700<BR>
<B>To: </B><freeswitch-users@lists.freeswitch.org><BR>
<B>Subject: </B>Re: [Freeswitch-users] Performance bottleneck<BR>
<BR>
</SPAN></FONT><SPAN STYLE='font-size:11pt'><FONT COLOR="#0000FF"><FONT FACE="Arial">I dont know if this makes any sense - it's just an idea.<BR>
</FONT></FONT><FONT FACE="Calibri, Verdana, Helvetica, Arial"> <BR>
</FONT><FONT COLOR="#0000FF"><FONT FACE="Arial">If you're willing to take the hit of running MySQL, I know that it's replication features could potentially be used. You can have the primary MySQL server run in ramdisk and get all the performance benefits of doing so while also writing log files to the ram disk in a seperate area. Those logfiles can, using MySQL's built in replication features, be copied over to a backup server and played backup, giving you both a hot spare as well as a disk based backup.<BR>
</FONT></FONT><FONT FACE="Calibri, Verdana, Helvetica, Arial"> <BR>
</FONT><FONT COLOR="#0000FF"><FONT FACE="Arial">This does three things for you:<BR>
1) Gives you backup on disk, while preserving performance in RAM<BR>
2) Gives you a live backup that you can quickly shunt things over to if for some reason the primary dies<BR>
3) Allows you to handle spikes in volume. MySQL by default will just write to the log files and they can be played back later by the (slower) backup server, so a spike in volume of calls should not cause the server to slow down per say. There is a small risk your data will be lost if there is a failure for whatever is not copied over to the (slower) backup server, but that's unlikely to be that huge a lag (better then nothing).<BR>
</FONT></FONT><FONT FACE="Calibri, Verdana, Helvetica, Arial"> <BR>
</FONT><FONT COLOR="#0000FF"><FONT FACE="Arial">As to whether any of this applies (like why the heck you'd install MySQL on a ramdisk to start), I can't say. but it's a thought...Oh, and you need a lot of RAM ;-)<BR>
</FONT></FONT><FONT FACE="Calibri, Verdana, Helvetica, Arial"><BR>
<HR ALIGN=CENTER SIZE="3" WIDTH="100%"></FONT><FONT FACE="Tahoma, Verdana, Helvetica, Arial"><B>From:</B> Ken Rice [<a href="mailto:krice@suspicious.org]">mailto:krice@suspicious.org]</a> <BR>
<B>Sent:</B> Tuesday, August 12, 2008 11:44 AM<BR>
<B>To:</B> freeswitch-users@lists.freeswitch.org<BR>
<B>Subject:</B> Re: [Freeswitch-users] Performance bottleneck<BR>
</FONT><FONT FACE="Calibri, Verdana, Helvetica, Arial"><BR>
Actually I don’t know of any mechanism that will back up the DB... Where sqlite does work well for small to medium installations it only scales to a point... Sqlite does not reuse ‘nodes’ in the db on an update... It marks them as dead and creates a new entry... While this works ok on smaller tables w/ light to medium updates after a while you have to compress or vacuum the tables... This requires a table level lock with sqlite... FS does have some things built in to handle this, but under load this can cause the switch to appear to hang.<BR>
<BR>
Switching over to use something like Postgresql (my prefered db) helps out a good bit here, but keep in mind that in doing so you greatly increase the resources required for the db. Also don’t forget that pgsql has a similar mechanism on how it handles updates, just don’t forget to enable auto-vacuuming on pgsql... That is a discussion for a different list tho<BR>
<BR>
K<BR>
<BR>
<BR>
<HR ALIGN=CENTER SIZE="3" WIDTH="95%"><B>From: </B>Brian West <brian@freeswitch.org><BR>
<B>Reply-To: </B><freeswitch-users@lists.freeswitch.org><BR>
<B>Date: </B>Tue, 12 Aug 2008 13:24:40 -0500<BR>
<B>To: </B><freeswitch-users@lists.freeswitch.org><BR>
<B>Subject: </B>Re: [Freeswitch-users] Performance bottleneck<BR>
<BR>
Well putting the db in ram does help a bit but it has to keep states of everything going on and do extra work for that... its a heavy task in itself.<BR>
<BR>
On Aug 12, 2008, at 1:19 PM, Michael Collins wrote:<BR>
<BR>
</FONT></SPAN></FONT><BLOCKQUOTE><FONT SIZE="4"><FONT COLOR="#000080"><FONT FACE="Arial"><SPAN STYLE='font-size:10pt'>That begs the question… is there a mechanism in sqlite or Linux that allows for the RAM drive to be backed up periodically? That would be a cool feature to get documented for those power users like Ken! ;)<BR>
<BR>
-MC<BR>
<BR>
</SPAN></FONT></FONT></FONT></BLOCKQUOTE><FONT SIZE="4"><FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'><BR>
<BR>
</SPAN></FONT></FONT><FONT FACE="Helvetica, Verdana, Arial"><SPAN STYLE='font-size:9pt'>Brian West<BR>
sip:brian@freeswitch.org<BR>
<BR>
<BR>
<BR>
</SPAN></FONT><FONT SIZE="4"><FONT FACE="Calibri, Verdana, Helvetica, Arial"><SPAN STYLE='font-size:11pt'><BR>
<BR>
<HR ALIGN=CENTER SIZE="3" WIDTH="95%"></SPAN></FONT><FONT FACE="Consolas, Courier New, Courier"><SPAN STYLE='font-size:10pt'>_______________________________________________<BR>
Freeswitch-users mailing list<BR>
Freeswitch-users@lists.freeswitch.org<BR>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><BR>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
<BR>
<HR ALIGN=CENTER SIZE="3" WIDTH="95%">_______________________________________________<BR>
Freeswitch-users mailing list<BR>
Freeswitch-users@lists.freeswitch.org<BR>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><BR>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
</SPAN></FONT></FONT>
</BODY>
</HTML>