[Freeswitch-users] Storing CDR UUIDs in MySQL

Mon Feb 18 05:08:48 MSK 2013

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