[Freeswitch-users] Storing CDR UUIDs in MySQL

Mon Feb 18 04:45:47 MSK 2013

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20130217/4d39a3d1/attachment-0001.html 