{"id":3338,"date":"2013-05-22T13:45:49","date_gmt":"2013-05-22T03:45:49","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=3338"},"modified":"2013-05-22T13:45:49","modified_gmt":"2013-05-22T03:45:49","slug":"some-storage-engine-features-you-only-get-if-youre-innodb","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2013\/05\/22\/some-storage-engine-features-you-only-get-if-youre-innodb\/","title":{"rendered":"Some storage engine features you only get if you&#8217;re InnoDB"},"content":{"rendered":"<p>I had reason to look into the extended secondary index code in MariaDB and MySQL recently, and there was one bit that I really didn&#8217;t like.<\/p>\n<p>MariaDB:<\/p>\n<blockquote><p><code>share-&gt;set_use_ext_keys_flag(legacy_db_type == DB_TYPE_INNODB);<\/code><\/p><\/blockquote>\n<p>MySQL:<\/p>\n<blockquote><p><code>use_extended_sk= (legacy_db_type == DB_TYPE_INNODB);<\/code><\/p><\/blockquote>\n<p>In case you were wondering what &#8220;legacy_db_type&#8221; actually does, let me tell you: it&#8217;s not legacy at all, it&#8217;s kind of key to how the whole &#8220;metadata&#8221; system in MySQL works. For example, to drop a table, this magic number is used to work out what storage engine to call to drop the table.<\/p>\n<p>Now, these code snippets basically kiss goodbye to the idea of a &#8220;pluggable storage engine&#8221; architecture. If you&#8217;re not InnoDB, you don&#8217;t get to have certain features. This isn&#8217;t exactly MySQL or MariaDB encouraging an open storage engine ecosystem (quite the opposite really).<\/p>\n<p>Having the MySQL server have this incredibly basic, busy and incomplete understanding of metadata has always been a bit of a mess. The code for reading a table definition out of the FRM file really does show its age, and has fingers all through the server.<\/p>\n<p>If somebody was serious about refactoring server code, you&#8217;d certainly be looking here, as this code is a major source of arbitrary limitations. However, if you have the server and the engine(s) both having separate views of what is the &#8220;correct&#8221; state of metadata you end up with a mess (anyone who has had InnoDB be out of sync with FRMs knows this one). I worry that the FRM code will be replaced with something even less understandable by humans, again making the mistake that the server knows the state of the engine better than the engine does.<\/p>\n<p>See Also:<\/p>\n<ul>\n<li><span style=\"line-height: 15px;\"><a href=\"http:\/\/s.petrunia.net\/blog\/\">Sergey Petrunia\u00e2\u20ac\u2122s blog<\/a>\u00c2\u00a0on the topic of extended keys:\u00c2\u00a0<a href=\"http:\/\/s.petrunia.net\/blog\/?p=74\">http:\/\/s.petrunia.net\/blog\/?p=74<\/a><br \/>\n<\/span><\/li>\n<li><a id=\"___hovercard_0\" href=\"http:\/\/www.blogger.com\/profile\/15144909942117839863\" rel=\"author\" data-gapiattached=\"true\" data-onload=\"true\" data-gapiscan=\"true\">Sergey Glukhov\u00c2\u00a0<\/a>blogs on the MySQL implementation:\u00c2\u00a0<a href=\"http:\/\/glukhsv.blogspot.com.au\/2012\/12\/innodb-extended-secondary-keys.html\">http:\/\/glukhsv.blogspot.com.au\/2012\/12\/innodb-extended-secondary-keys.html<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>I had reason to look into the extended secondary index code in MariaDB and MySQL recently, and there was one bit that I really didn&#8217;t like. MariaDB: share-&gt;set_use_ext_keys_flag(legacy_db_type == DB_TYPE_INNODB); MySQL: use_extended_sk= (legacy_db_type == DB_TYPE_INNODB); In case you were wondering &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2013\/05\/22\/some-storage-engine-features-you-only-get-if-youre-innodb\/\">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_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_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},"jetpack_post_was_ever_published":false},"categories":[76,14],"tags":[],"class_list":["post-3338","post","type-post","status-publish","format-standard","hentry","category-code","category-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-RQ","jetpack-related-posts":[{"id":3291,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/18\/where-are-they-now-mysql-storage-engines\/","url_meta":{"origin":3338,"position":0},"title":"Where are they now: MySQL Storage Engines","author":"Stewart Smith","date":"2013-04-18","format":false,"excerpt":"There was once a big hooplah about the MySQL Storage Engine Architecture and how it was easy to just slot in some other method of storage instead of the provided ones. Over the years I've repeatedly mentioned how this wasn't really the case and that it was remarkably non trivial.\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":3676,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/02\/14\/ghosts-of-mysql-past-part-5-the-era-of-acquisitions\/","url_meta":{"origin":3338,"position":1},"title":"Ghosts of MySQL Past Part 5: The Era of Acquisitions","author":"Stewart Smith","date":"2014-02-14","format":false,"excerpt":"This week I've been writing based on my linux.conf.au 2014 talk, which you can watch the recording of. Also see Part 1, Part 2, Part 3 and Part 4. My feed feel off Planet MySQL for a bit so you may have missed those posts. Now we head into the\u2026","rel":"","context":"In &quot;mysql&quot;","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4019,"url":"https:\/\/www.flamingspork.com\/blog\/2015\/12\/18\/power8-accelerated-crc32-merged-in-mariadb-10-1\/","url_meta":{"origin":3338,"position":2},"title":"POWER8 Accelerated CRC32 merged in MariaDB 10.1","author":"Stewart Smith","date":"2015-12-18","format":false,"excerpt":"Earlier on in benchmarking MySQL and MariaDB on POWER8, we noticed that on write workloads (or read workloads involving a lot of IO) we were spending a bunch of time computing InnoDB page checksums. This is a relatively well known MySQL problem and has existed for many years and Percona\u2026","rel":"","context":"In &quot;IBM&quot;","block_context":{"text":"IBM","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/ibm-work-et-al\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2253,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/01\/05\/is-your-storage-engine-buggy-or-the-database-server\/","url_meta":{"origin":3338,"position":3},"title":"Is your Storage Engine buggy or the database server?","author":"Stewart Smith","date":"2011-01-05","format":false,"excerpt":"If your storage engine returns an error from rnd_init (or doStartTableScan as it's named in Drizzle) and does not save this error and return it in any subsequent calls to rnd_next, your engine is buggy. Namely it is buggy in that a) an error may not be reported back to\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":3237,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/03\/08\/mysql-modularity-are-we-there-yet\/","url_meta":{"origin":3338,"position":4},"title":"MySQL modularity, are we there yet?","author":"Stewart Smith","date":"2013-03-08","format":false,"excerpt":"MySQL is now over four times the size than it was with MySQL 3.23. This has not come in the shape of plugins. Have we improved modularity over time? I decided to take LoC count for plugins and storage engines (in the case of Drizzle, memory, myisam and innobase are\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":3261,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/03\/14\/so-what-about-the-diffstat-of-mariadb-compared-to-mysql\/","url_meta":{"origin":3338,"position":5},"title":"So what about the diffstat of MariaDB compared to MySQL?","author":"Stewart Smith","date":"2013-03-14","format":false,"excerpt":"So, I've looked at what sloccount says on the differences between Oracle MySQL over versions of itself and the various MySQL branches around. What I haven't looked at is the diffstat. Firstly, let's look at MariaDB. I'm going to look at MariaDB 5.5.29 as compared to MySQL 5.5.29, both checked\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":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3338","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=3338"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3338\/revisions"}],"predecessor-version":[{"id":3339,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3338\/revisions\/3339"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3338"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3338"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3338"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}