<HTML>
<HEAD>
<TITLE>Re: [Freeswitch-users] Storing CDR UUIDs in MySQL</TITLE>
</HEAD>
<BODY>
<FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'>Why don’t you do something like look at something other than mysql like PostgreSQL, we insert millions of CDRs per day there and don’t have performance issues due to Table Partitioning, this allows us to cram well over 100million new rows/month into a table, partitioned daily along something like a call_date field (derived by dropping the time from a full time/date stamp). This also allows us to do really speedy reports without having to deal with what happens if you do something like select count(*) from cdr where call_date = “2013-02-15”; this would be a monster of a query on a table with say 90 days of CDRs at 4million CDRs/day (360million CDRs total) but by using the table partitions with a date constraint, the query planer eliminates partitions that do not match the date constraint the speeds along the query.<BR>
<BR>
The caveat to this is if you don’t give it some sort of date constraint you end up looking across many partitions for the data... <BR>
<BR>
<BR>
On 2/17/13 6:48 PM, "Steven Ayre" <<a href="steveayre@gmail.com">steveayre@gmail.com</a>> wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'>You could take a SHA1, but then might get collisions. That can happen with UUIDs too, but since it includes the MAC address and time stamp is less likely and would be more predictable.<BR>
<BR>
The originate_uuid may get collisions depending on what you pick, and the callid puts it outside your control entirely - in those situations you might need to handle collisions anyway.<BR>
<BR>
Sent from my iPad<BR>
<BR>
<BR>
<BR>
On 17 Feb 2013, at 23:47, "Cal Leeming [Simplicity Media Ltd]" <<a href="cal.leeming@simplicitymedialtd.co.uk">cal.leeming@simplicitymedialtd.co.uk</a>> wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'>Ah, yeah that changes the playing field for sure.<BR>
<BR>
The problem with CHAR is the INSERT speed though, and I've seen similar issues on other builds too.. after you push past a few million rows, it really starts to slow down.. If the database was in memory, it might not be such an issue though.<BR>
<BR>
In some ways it 'feels cleaner' using a separate table and then JOIN on a BIGINT autoinc column, as the thought of JOIN'ing and GROUP BY on a BINARY seems wrong. <BR>
<BR>
Another option would be to take an SHA1 hash of the UUID, then convert that from hex to bin, and put that in the BINARY field.. this way you can guarantee each UUID will be the same size, not worry about changes in data schema, and get the INSERT speed benefits of the BINARY too - thoughts?<BR>
<BR>
Cal<BR>
<BR>
On Sun, Feb 17, 2013 at 11:15 PM, Steven Ayre <<a href="steveayre@gmail.com">steveayre@gmail.com</a>> wrote:<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'>+1<BR>
<BR>
Also as an example the 'inbound-use-callid-as-uuid' parameter I mentioned before is a Sofia profile parameter that uses the Call-ID header value from the INVITE sent by the caller as the UUID for the channel. That could be useful for example in matching up channels against SIP traces as you only need to store 1 ID not 2. I frequently see ones for example of <a href="$UUID@$SERVER.COM">$UUID@$SERVER.COM</a> <<a href="http://SERVER.COM">http://SERVER.COM</a>> - far longer than 128bits, and using characters other than 0-9A-F so that it can't be folded into bits.<BR>
<BR>
In this case a CHAR column becomes most flexible and the BIGINT column gives you the speed advantage of the BINARY.<BR>
<BR>
-Steve<BR>
<BR>
<BR>
<BR>
On 17 February 2013 22:39, Seven Du <<a href="dujinfang@gmail.com">dujinfang@gmail.com</a>> wrote:<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'> <BR>
you can use any arbitrary string as uuid, say<BR>
<BR>
originate {origination_uuid=my-uuid-that-is-not-128-bit}user/1000 ….<BR>
<BR>
<BR>
<BR>
<FONT COLOR="#A0A0A8">On Monday, February 18, 2013 at 6:13 AM, Cal Leeming [Simplicity Media Ltd] wrote:<BR>
</FONT> <BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'> <BR>
Thanks for the feedback Steve, <BR>
<BR>
In ref to auto inc, actually, I have! :) In the code on the blog, it shows that the tables uses a BIGINT auto increment for each row, and the performance was still good. However, using this approach introduces an unnecessary JOIN and uses more index and table space - unless I've missed something?<BR>
<BR>
Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/<BR>
<BR>
Cal<BR>
<BR>
On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <<a href="steveayre@gmail.com">steveayre@gmail.com</a>> wrote:<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'>Another possibility you haven't looked at is having a table of UUIDs with a BIGINT auto_increment PK and using that as keys in other takes with a foreign key relationship (whether defined or implied). That would give you a smaller integer (hence faster select/insert/join/etc) that's still guaranteed to be unique. Though the auto_increment INSERT could then become a bottleneck (hint run a recent 5.5/5.6 MySQL version at the very least since that bottleneck while still present was very much reduced).<BR>
<BR>
-Steve<BR>
<BR>
<BR>
<BR>
On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] <<a href="cal.leeming@simplicitymedialtd.co.uk">cal.leeming@simplicitymedialtd.co.uk</a>> wrote:<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'>Hi all,<BR>
<BR>
Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.<BR>
<BR>
Full performance breakdown and write up can be found here;<BR>
<a href="http://blog.simplicitymedialtd.co.uk/?p=437">http://blog.simplicitymedialtd.co.uk/?p=437</a><BR>
<BR>
So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?<BR>
<BR>
Thanks<BR>
<FONT COLOR="#888888"><BR>
Cal<BR>
</FONT><BR>
_________________________________________________________________________<BR>
Professional FreeSWITCH Consulting Services:<BR>
<a href="consulting@freeswitch.org">consulting@freeswitch.org</a><BR>
<a href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a><BR>
<BR>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<BR>
<a href="http://www.cudatel.com">http://www.cudatel.com</a><BR>
<BR>
Official FreeSWITCH Sites<BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
<a href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a><BR>
<a href="http://www.cluecon.com">http://www.cluecon.com</a><BR>
<BR>
FreeSWITCH-users mailing list<BR>
<a href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><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>
</SPAN></FONT></BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'><BR>
<BR>
_________________________________________________________________________<BR>
Professional FreeSWITCH Consulting Services:<BR>
<a href="consulting@freeswitch.org">consulting@freeswitch.org</a><BR>
<a href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a><BR>
<BR>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<BR>
<a href="http://www.cudatel.com">http://www.cudatel.com</a><BR>
<BR>
Official FreeSWITCH Sites<BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
<a href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a><BR>
<a href="http://www.cluecon.com">http://www.cluecon.com</a><BR>
<BR>
FreeSWITCH-users mailing list<BR>
<a href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><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>
</SPAN></FONT></BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'><BR>
_________________________________________________________________________<BR>
Professional FreeSWITCH Consulting Services:<BR>
<a href="consulting@freeswitch.org">consulting@freeswitch.org</a><BR>
<a href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a><BR>
<BR>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<BR>
<a href="http://www.cudatel.com">http://www.cudatel.com</a><BR>
<BR>
Official FreeSWITCH Sites<BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
<a href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a><BR>
<a href="http://www.cluecon.com">http://www.cluecon.com</a><BR>
<BR>
FreeSWITCH-users mailing list<BR>
<a href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><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>
</SPAN></FONT></BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'> <BR>
<BR>
<BR>
<BR>
<BR>
_________________________________________________________________________<BR>
Professional FreeSWITCH Consulting Services:<BR>
<a href="consulting@freeswitch.org">consulting@freeswitch.org</a><BR>
<a href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a><BR>
<BR>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<BR>
<a href="http://www.cudatel.com">http://www.cudatel.com</a><BR>
<BR>
Official FreeSWITCH Sites<BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
<a href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a><BR>
<a href="http://www.cluecon.com">http://www.cluecon.com</a><BR>
<BR>
FreeSWITCH-users mailing list<BR>
<a href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><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>
</SPAN></FONT></BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'><BR>
<BR>
_________________________________________________________________________<BR>
Professional FreeSWITCH Consulting Services:<BR>
<a href="consulting@freeswitch.org">consulting@freeswitch.org</a><BR>
<a href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a><BR>
<BR>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<BR>
<a href="http://www.cudatel.com">http://www.cudatel.com</a><BR>
<BR>
Official FreeSWITCH Sites<BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
<a href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a><BR>
<a href="http://www.cluecon.com">http://www.cluecon.com</a><BR>
<BR>
FreeSWITCH-users mailing list<BR>
<a href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><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>
</SPAN></FONT></BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'><BR>
_________________________________________________________________________<BR>
Professional FreeSWITCH Consulting Services:<BR>
<a href="consulting@freeswitch.org">consulting@freeswitch.org</a><BR>
<a href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a><BR>
<BR>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<BR>
<a href="http://www.cudatel.com">http://www.cudatel.com</a><BR>
<BR>
Official FreeSWITCH Sites<BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
<a href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a><BR>
<a href="http://www.cluecon.com">http://www.cluecon.com</a><BR>
<BR>
FreeSWITCH-users mailing list<BR>
<a href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><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> <<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></BLOCKQUOTE><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'><BR>
<HR ALIGN=CENTER SIZE="3" WIDTH="95%"></SPAN></FONT><FONT SIZE="2"><FONT FACE="Consolas, Courier New, Courier"><SPAN STYLE='font-size:10pt'>_________________________________________________________________________<BR>
Professional FreeSWITCH Consulting Services:<BR>
<a href="consulting@freeswitch.org">consulting@freeswitch.org</a><BR>
<a href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a><BR>
<BR>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<BR>
<a href="http://www.cudatel.com">http://www.cudatel.com</a><BR>
<BR>
Official FreeSWITCH Sites<BR>
<a href="http://www.freeswitch.org">http://www.freeswitch.org</a><BR>
<a href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a><BR>
<a href="http://www.cluecon.com">http://www.cluecon.com</a><BR>
<BR>
FreeSWITCH-users mailing list<BR>
<a href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><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></BLOCKQUOTE><FONT SIZE="2"><FONT FACE="Consolas, Courier New, Courier"><SPAN STYLE='font-size:10pt'><BR>
</SPAN></FONT></FONT><FONT FACE="Monaco, Courier New"><SPAN STYLE='font-size:11pt'>-- <BR>
Ken<BR>
<FONT COLOR="#0000FF"><U><a href="http://www.FreeSWITCH.org">http://www.FreeSWITCH.org</a><BR>
<a href="http://www.ClueCon.com">http://www.ClueCon.com</a><BR>
<a href="http://www.OSTAG.org">http://www.OSTAG.org</a><BR>
</U></FONT>irc.freenode.net #freeswitch<BR>
</SPAN></FONT>
</BODY>
</HTML>