[Freeswitch-users] Storing CDR UUIDs in MySQL

Mon Feb 18 05:13:58 MSK 2013

Cool Jay! Last time I looked at mysql that wasn't available... (and yes it
was quite a long time ago)

On 2/17/13 8:08 PM, "jay binks" <jaybinks at gmail.com> wrote:

> your very right Ken ... .
> however Mysql has partitions with InnoDB backend.
> 
> I do the exact same thing and it helps a heap !
> 
> On 18 February 2013 11:45, Ken Rice <krice at freeswitch.org> wrote:
>> 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.
>> 
>> 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...
>> 
>> 
>> On 2/17/13 6:48 PM, "Steven Ayre" <steveayre at gmail.com> wrote:
>> 
>> 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.
>> 
>> 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.
>> 
>> Sent from my iPad
>> 
>> 
>> 
>> On 17 Feb 2013, at 23:47, "Cal Leeming [Simplicity Media Ltd]"
>> <cal.leeming at simplicitymedialtd.co.uk> wrote:
>> 
>> Ah, yeah that changes the playing field for sure.
>> 
>> 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.
>> 
>> 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.
>> 
>> 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?
>> 
>> Cal
>> 
>> On Sun, Feb 17, 2013 at 11:15 PM, Steven Ayre <steveayre at gmail.com> wrote:
>> 
>> +1
>> 
>> 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
>> $UUID@$SERVER.COM <http://SERVER.COM>  - far longer than 128bits, and using
>> characters other than 0-9A-F so that it can't be folded into bits.
>> 
>> 
>> In this case a CHAR column becomes most flexible and the BIGINT column gives
>> you the speed advantage of the BINARY.
>> 
>> -Steve
>> 
>> 
>> 
>> On 17 February 2013 22:39, Seven Du <dujinfang at gmail.com> wrote:
>> 
>> 
>> you can use any arbitrary string as uuid, say
>> 
>> originate {origination_uuid=my-uuid-that-is-not-128-bit}user/1000 Š.
>> 
>> 
>> 
>> On Monday, February 18, 2013 at 6:13 AM, Cal Leeming [Simplicity Media Ltd]
>> wrote:
>> 
>> 
>> Thanks for the feedback Steve,
>> 
>> 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?
>> 
>> Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu
>> failed me :/
>> 
>> Cal
>> 
>> On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <steveayre at gmail.com> wrote:
>> 
>> 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).
>> 
>> -Steve
>> 
>> 
>> 
>> On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd]
>> <cal.leeming at simplicitymedialtd.co.uk> wrote:
>> 
>> Hi all,
>> 
>> Just spent a little while comparing the performance of heavy INSERT/SELECT
>> performance against UUID fields in a MySQL database.
>> 
>> Full performance breakdown and write up can be found here;
>> http://blog.simplicitymedialtd.co.uk/?p=437
>> 
>> So far, the best option seems to be storing the UUID as a BINARY(16) -
>> anyone have any thoughts on this?
>> 
>> Thanks
>> 
>> Cal
>> 
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>> 
>> 
>> 
>> 
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>> 
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at 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 at freeswitch.org
>> http://www.freeswitchsolutions.com
>> 
>> 
>> 
>> 
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>> 
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at 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 at freeswitch.org
>> http://www.freeswitchsolutions.com
>> 
>> 
>> 
>> 
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>> 
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at 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 at freeswitch.org
>> http://www.freeswitchsolutions.com
>> 
>> 
>> 
>> 
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>> 
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at 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 at freeswitch.org
>> http://www.freeswitchsolutions.com
>> 
>> 
>> 
>> 
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>> 
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at 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 at freeswitch.org
>> http://www.freeswitchsolutions.com
>> 
>> 
>> 
>> 
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>> 
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> <http://lists.freeswitch.org/mailman/options/freeswitch-users>
>> http://www.freeswitch.org
>> 
>> 
>> ________________________________
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>> 
>> 
>> 
>> 
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>> 
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>> 
>> 
>> --
>> Ken
>> http://www.FreeSWITCH.org
>> http://www.ClueCon.com
>> http://www.OSTAG.org
>> irc.freenode.net #freeswitch
>> 
>> _________________________________________________________________________
>> Professional FreeSWITCH Consulting Services:
>> consulting at freeswitch.org
>> http://www.freeswitchsolutions.com
>> 
>> 
>> 
>> 
>> Official FreeSWITCH Sites
>> http://www.freeswitch.org
>> http://wiki.freeswitch.org
>> http://www.cluecon.com
>> 
>> FreeSWITCH-users mailing list
>> FreeSWITCH-users at lists.freeswitch.org
>> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
>> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
>> http://www.freeswitch.org
>> 
> 
> 
> 
> --
> Sincerely
> 
> Jay
> 
> _________________________________________________________________________
> Professional FreeSWITCH Consulting Services:
> consulting at freeswitch.org
> http://www.freeswitchsolutions.com
> 
> 
> 
> 
> Official FreeSWITCH Sites
> http://www.freeswitch.org
> http://wiki.freeswitch.org
> http://www.cluecon.com
> 
> FreeSWITCH-users mailing list
> FreeSWITCH-users at lists.freeswitch.org
> http://lists.freeswitch.org/mailman/listinfo/freeswitch-users
> UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users
> http://www.freeswitch.org

-- 
Ken
http://www.FreeSWITCH.org
http://www.ClueCon.com
http://www.OSTAG.org
irc.freenode.net #freeswitch