[Freeswitch-users] Storing CDR UUIDs in MySQL

[Steven Ayre]

>
> 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?


Ah, sorry - missed that somehow. :o)

In that case it might be simpler to store the UUID in a single field.
Comparing multiple fields I imagine would have a larger overhead than a
single comparison.

The extra table does uses more space, but you're shrinking the key size
enough that you're going to save that much in the other tables. It'd shrink
the corresponding indexes too, and that'll speed up index lookups (which'll
affect both INSERT and SELECT) - both because the comparison is smaller and
because you read more index entries in a single disk access. That may be
enough of a benefit that the queries are faster despite the additional JOIN.

That doesn't appear to be the case in your benchmarks, but it may be worth
repeating with the UUID in a single field.




You don't mention many details about your test setup - CPU, RAM, Disks,
MySQL configuration etc. All of which could have a large impact. The
version of MySQL / InnoDB will also have a large impact - there have been
many improvements between versions. 5.6 has just become GA, although few
people will be running it yet.

The main optimization for Innodb would be to set the buffer pool to be as
large as possible to minimize disk I/O. In an ideal world as large as your
entire dataset (or at least what you actively use) Though of course that's
not realistic in many cases.

-Steve




On 17 February 2013 22:13, Cal Leeming [Simplicity Media Ltd] <
cal.leeming at simplicitymedialtd.co.uk> 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
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20130217/86ff68bb/attachment.html