[Freeswitch-users] Storing CDR UUIDs in MySQL

[Steven Ayre]

It's worth mentioning that the speedup I expect is BIGINT vs CHAR.

BINARY(16) is a 128bit number vs 64bit BIGINT number - similar enough I
wouldn't expect much difference and without requiring the extra JOIN not
surprising to me that BINARY is faster. It's just less usable since it
requires conversion, but that's a trivial task to do within your
application.

-Steve




On 17 February 2013 23:02, Steven Ayre <steveayre at gmail.com> wrote:

> 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)req
>
> 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/a2552686/attachment-0001.html