<br><br><div class="gmail_quote">On Sun, Feb 17, 2013 at 11:02 PM, Steven Ayre <span dir="ltr"><<a href="mailto:steveayre@gmail.com" target="_blank">steveayre@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div class="im"><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">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?</blockquote>
<div></div><div><br></div></div>Ah, sorry - missed that somehow. :o)</blockquote><div><br></div><div>I *really* suck at writing these blog articles, and they aren't always a clean/easy read. I'd planned to have some really nice graphs and styling on the page, but time was my enemy!</div>
<div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><br><div>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. </div>
</div></blockquote><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>
<div><br></div><div>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.</div>
</div></blockquote><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div>That doesn't appear to be the case in your benchmarks, but it may be worth repeating with the UUID in a single field. </div>
</div></blockquote><div><div><br></div><div>Initially I'd planned to put all the UUIDs in a separate table and LEFT JOIN on them (hence the auto inc in the tests)</div><div><br></div><div><div>I guess it also depends on how often you're referencing the UUID multiple times, the more times it's used, the more suitable a second table with a JOIN would become for saving index/table space.</div>
</div><div><br></div><div>I agree these tests were not as extensive as they should have been, and probably should have included various JOIN statements, as well as inserting into table tables. I also hadn't tested doing a JOIN on a BINARY either - so might be worth adding this in.</div>
</div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div><br></div><div><br></div><div><br></div><div><br></div><div>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.</div>
</div></blockquote><div><br></div><div>I was considering posting this too, but I was more focusing on the ratio difference of the numbers, rather than the numbers themselves.</div><div><br></div><div>In this particular test though, MySQL was Percona 5.5 and, and configured with a very low innodb space / heap size, so essentially everything was being forced to disk to give a 'worst case' scenario.</div>
<div><br></div><div>It would be interesting to see the difference in these numbers using different MySQL configurations for sure</div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>
<div><br></div><div>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.</div>
</div></blockquote><div><br></div><div>I did a separate post about this too lol :D</div><div><br></div><div><a href="http://blog.simplicitymedialtd.co.uk/?p=225">http://blog.simplicitymedialtd.co.uk/?p=225</a></div><div><br>
</div><div>These days RAM is so cheap that we tend to just throw 128GB+ RAM into the server and scale up rather than out, the speed improvement you get from having your entire InnoDB database in memory is massive. And if your chassis won't support enough RAM, then SSDs offers a considerable improvement too.</div>
<div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>
<div><br><div>-Steve</div><div><div class="h5"><div><br></div><div><br></div><div><br><br><div class="gmail_quote">On 17 February 2013 22:13, Cal Leeming [Simplicity Media Ltd] <span dir="ltr"><<a href="mailto:cal.leeming@simplicitymedialtd.co.uk" target="_blank">cal.leeming@simplicitymedialtd.co.uk</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div>Thanks for the feedback Steve, </div><div><br></div>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?<div>
<div><br></div><div>Could you elaborate more on the UUIDs that may not be 128-bit? My google-fu failed me :/</div><span><font color="#888888"><div><br></div></font></span><div><span><font color="#888888">Cal</font></span><div>
<div><br><div><div><br><div class="gmail_quote">On Sun, Feb 17, 2013 at 9:11 PM, Steven Ayre <span dir="ltr"><<a href="mailto:steveayre@gmail.com" target="_blank">steveayre@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">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).<div>
<br><div>-Steve<div><br></div><div><br><br><div class="gmail_quote"><div>On 17 February 2013 20:55, Cal Leeming [Simplicity Media Ltd] <span dir="ltr"><<a href="mailto:cal.leeming@simplicitymedialtd.co.uk" target="_blank">cal.leeming@simplicitymedialtd.co.uk</a>></span> wrote:<br>
</div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div><div>Hi all,<div><div><br></div><div>Just spent a little while comparing the performance of heavy INSERT/SELECT performance against UUID fields in a MySQL database.</div>
</div><div><br></div><div>Full performance breakdown and write up can be found here;</div>
<div><a href="http://blog.simplicitymedialtd.co.uk/?p=437" target="_blank">http://blog.simplicitymedialtd.co.uk/?p=437</a></div><div><br></div><div>So far, the best option seems to be storing the UUID as a BINARY(16) - anyone have any thoughts on this?</div>
<div><br></div><div>Thanks</div><span><font color="#888888"><div><br>Cal</div>
</font></span><br></div></div><div>_________________________________________________________________________<br>
Professional FreeSWITCH Consulting Services:<br>
<a href="mailto:consulting@freeswitch.org" target="_blank">consulting@freeswitch.org</a><br>
<a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
<br>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
<a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
<br>
Official FreeSWITCH Sites<br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
<a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br></div></blockquote></div><br></div></div></div>
<br>_________________________________________________________________________<br>
Professional FreeSWITCH Consulting Services:<br>
<a href="mailto:consulting@freeswitch.org" target="_blank">consulting@freeswitch.org</a><br>
<a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
<br>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
<a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
<br>
Official FreeSWITCH Sites<br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
<a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br></blockquote></div><br></div></div></div></div></div></div>
<br>_________________________________________________________________________<br>
Professional FreeSWITCH Consulting Services:<br>
<a href="mailto:consulting@freeswitch.org" target="_blank">consulting@freeswitch.org</a><br>
<a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
<br>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
<a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
<br>
Official FreeSWITCH Sites<br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
<a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org" target="_blank">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br></blockquote></div><br></div></div></div></div></div>
<br>_________________________________________________________________________<br>
Professional FreeSWITCH Consulting Services:<br>
<a href="mailto:consulting@freeswitch.org">consulting@freeswitch.org</a><br>
<a href="http://www.freeswitchsolutions.com" target="_blank">http://www.freeswitchsolutions.com</a><br>
<br>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server<br>
<a href="http://www.cudatel.com" target="_blank">http://www.cudatel.com</a><br>
<br>
Official FreeSWITCH Sites<br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<a href="http://wiki.freeswitch.org" target="_blank">http://wiki.freeswitch.org</a><br>
<a href="http://www.cluecon.com" target="_blank">http://www.cluecon.com</a><br>
<br>
FreeSWITCH-users mailing list<br>
<a href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a href="http://lists.freeswitch.org/mailman/options/freeswitch-users" target="_blank">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a href="http://www.freeswitch.org" target="_blank">http://www.freeswitch.org</a><br>
<br></blockquote></div><br>