{"id":2036,"date":"2010-06-03T17:40:10","date_gmt":"2010-06-03T07:40:10","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=2036"},"modified":"2014-10-08T09:16:07","modified_gmt":"2014-10-07T23:16:07","slug":"new-create-table-performance-record","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2010\/06\/03\/new-create-table-performance-record\/","title":{"rendered":"New CREATE TABLE performance record!"},"content":{"rendered":"<p>\u00ef\u00bb\u00bf\u00ef\u00bb\u00bf4 min 20 sec<\/p>\n<p>So next time somebody complains about NDB taking a long time in CREATE TABLE, you&#8217;re welcome to point them to this :)<\/p>\n<ul>\n<li>A single CREATE TABLE statement<\/li>\n<li>It had ONE column<\/li>\n<li>It was an ENUM column.<\/li>\n<li>With 70,000 possible values.<\/li>\n<li>It was 605kb of SQL.<\/li>\n<li>It ran on <a href=\"http:\/\/www.drizzle.org\">Drizzle<\/a><\/li>\n<\/ul>\n<p>This was to test if you could create an ENUM column with greater than 2<sup>16<\/sup> possible values (you&#8217;re not\u00c2\u00a0supposed\u00c2\u00a0to be able to) &#8211; <a href=\"https:\/\/bugs.launchpad.net\/drizzle\/+bug\/589031\">bug 589031 has been filed<\/a>.<\/p>\n<p>How does it compare to MySQL? Well&#8230; there are other problems (<a href=\"http:\/\/bugs.mysql.com\/bug.php?id=54194\">Bug 54194 &#8211; ENUM limit of 65535 elements isn&#8217;t true<\/a> filed). Since we don&#8217;t have any limitations in Drizzle due to the FRM file format, we actually get to execute the CREATE TABLE statement.<\/p>\n<p>Still, why did this take <strong>four and a half minutes<\/strong>? I luckily managed to run <a href=\"http:\/\/poormansprofiler.org\/\">poor man&#8217;s profiler<\/a> during query execution. I very easily found out that I had this thread constantly running check_duplicates_in_interval(), which does a stupid linear search for duplicates. It turns out, that for 70,000 items, this takes approximately four minutes and 19.5 seconds. <a href=\"https:\/\/bugs.launchpad.net\/drizzle\/+bug\/589055\">Bug 589055 CREATE TABLE with ENUM fields with large elements takes forever<\/a> (where forever is defined as a bit over four minutes) filed.<\/p>\n<p>So I replaced check_duplicates_in_interval() with a implementation using a hash table (boost::unordered_set actually) as I wasn&#8217;t quite immediately in the mood for ripping out all of TYPELIB from the server. I can now run the CREATE TABLE statement in less than half a second.<\/p>\n<p>So now, I can run my test case in much less time and indeed check for correct behaviour rather quickly.<\/p>\n<p>I do have an urge to find out how big I can get a valid table definition file to though&#8230;. should be over 32MB&#8230;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u00ef\u00bb\u00bf\u00ef\u00bb\u00bf4 min 20 sec So next time somebody complains about NDB taking a long time in CREATE TABLE, you&#8217;re welcome to point them to this :) A single CREATE TABLE statement It had ONE column It was an ENUM column. &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2010\/06\/03\/new-create-table-performance-record\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[75,14],"tags":[314,85,312,70,313,628,109],"class_list":["post-2036","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-mysql","tag-boost","tag-bug","tag-create-table","tag-drizzle","tag-enum","tag-mysql","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-wQ","jetpack-related-posts":[{"id":2041,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/06\/29\/enum-now-works-properly-in-drizzle\/","url_meta":{"origin":2036,"position":0},"title":"ENUM now works properly (in Drizzle)","author":"Stewart Smith","date":"2010-06-29","format":false,"excerpt":"Over at the Drizzle blog, the recent 2010-06-07 tarball was announced. This tarball release has my fixes for the ENUM type, so that it now works as it should. I was quite amazed that such a small block of code could have so many bugs! One of the most interesting\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1262,"url":"https:\/\/www.flamingspork.com\/blog\/2008\/11\/22\/drop-table-fail-on-the-road-to-removing-the-frm\/","url_meta":{"origin":2036,"position":1},"title":"drop table fail (on the road to removing the FRM)","author":"Stewart Smith","date":"2008-11-22","format":false,"excerpt":"So... in removing the FRM file in Drizzle, I found a bit of a nugget on how drop table works (currently in the MySQL server and now \"did\" in Drizzle). If you DROP TABLE t1; this is what happens open the .frm file read first 10bytes (oh, and if you\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2310,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/03\/sql-oddity-alter-table-and-default-values\/","url_meta":{"origin":2036,"position":2},"title":"SQL Oddity: ALTER TABLE and default values","author":"Stewart Smith","date":"2011-03-03","format":false,"excerpt":"\u00ef\u00bb\u00bfSo, the MySQL (and Drizzle) ALTER TABLE syntax allows you to easily change the default value of a column. For example: CREATE TABLE t1 (answer int); ALTER TABLE t1 ALTER answer SET DEFAULT 42; So, you create a TIMESTAMP column and forgot to set the default value to CURRENT_TIMESTAMP. Easy,\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1763,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/12\/12\/the-table-protobuf-message-format\/","url_meta":{"origin":2036,"position":3},"title":"The Table protobuf message format","author":"Stewart Smith","date":"2009-12-12","format":false,"excerpt":"If you've ever opened up drizzled\/message\/table.proto in the Drizzle source tree you will have seen what's in the table message: the structure that describes a database table in Drizzle. Previously I've talked about the Table message more generally, giving a fair bit of history of the FRM file and how\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4180,"url":"https:\/\/www.flamingspork.com\/blog\/2016\/09\/27\/mysql-removes-the-frm-7-years-after-drizzle-did\/","url_meta":{"origin":2036,"position":4},"title":"MySQL removes the FRM (7 years after Drizzle did)","author":"Stewart Smith","date":"2016-09-27","format":false,"excerpt":"The new MySQL 8.0.0 milestone release that was recently announced brings something that has been a looooong time coming: the removal of the FRM file. I was the one who implemented this in Drizzle way back in 2009 (July 28th 2009 according to Brian)- and I may have had a\u2026","rel":"","context":"In &quot;code&quot;","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2303,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/16\/fixed-in-drizzle-no-more-gotchas\/","url_meta":{"origin":2036,"position":5},"title":"Fixed in Drizzle: No more &#8220;GOTCHA&#8217;s&#8221;","author":"Stewart Smith","date":"2011-03-16","format":false,"excerpt":"\u00a0 At the upcoming MySQL Conference and Expo, I'm going to give a Thursday afternoon (2pm) session entitled Fixed in Drizzle: No more \"GOTCHA's\". I plan to have a lot of fun with this session.. If you go back to the very start of when I started submitting code to\u2026","rel":"","context":"In &quot;drizzle&quot;","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2036","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/comments?post=2036"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2036\/revisions"}],"predecessor-version":[{"id":3839,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2036\/revisions\/3839"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=2036"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=2036"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=2036"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}