{"id":3295,"date":"2013-04-19T10:01:54","date_gmt":"2013-04-19T00:01:54","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=3295"},"modified":"2013-04-19T10:19:23","modified_gmt":"2013-04-19T00:19:23","slug":"the-merge-storage-engine-not-dead-just-resting-or-forgotten","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/19\/the-merge-storage-engine-not-dead-just-resting-or-forgotten\/","title":{"rendered":"The MERGE storage engine: not dead, just resting…. or forgotten."},"content":{"rendered":"

Following on from my fun post on Where are they now: MySQL Storage Engines<\/a>, I thought I’d cover the few storage engines that are really just interfaces to a collection of things. In this post, I’m talking about MERGE.<\/p>\n

The MERGE engine was basically a multiplexer down to a number of MyISAM tables. They all had to be the same, there was no parallel query execution and it saw fairly limited use. One of the main benefits was that then you could actually put more rows in a MyISAM table than your “files up to 2\/4GB” file system allowed. With the advent of partitioning, this really should have instantly gone away and been replaced by it. It wasn’t.<\/p>\n

It is another MySQL feature that exists likely due to customer demand at the time. It’s not a complete solution by any means, PARTITIONING is way more complete and universal…. and much harder to get right inside the MySQL server – which is why MERGE exists. It was easier to write a storage engine that wrapped MyISAM than it was to have any form of partitioning in the server.<\/p>\n

One advantage of MERGE tables is it means that you could parallelize myisamchk to repair your broken MyISAM tables after a crash. One step better than no crash safety is at least parallel recovery. The disadvantage being that you’re using MERGE and MyISAM tables.<\/p>\n

There is also the great security problem of MRG_MYISAM (the other name for MERGE tables): if you create a MyISAM table t1 and have a user able to access it, if they can create a MERGE table that accesses t1 (say m1) and you then revoke their access to t1, they’ll still be able to access t1 through m1.<\/p>\n

MERGE still seems to exist in MySQL 5.6 without even a warning that it’ll go away… which I suspect it will…. we long since got rid of it in Drizzle as, well, what you really want is a query rewrite engine that does views, partitioning etc etc.<\/p>\n

Can anyone think of a reason why you should still use MERGE tables in 2013? I can’t.<\/p>\n","protected":false},"excerpt":{"rendered":"

Following on from my fun post on Where are they now: MySQL Storage Engines, I thought I’d cover the few storage engines that are really just interfaces to a collection of things. In this post, I’m talking about MERGE. The … Continue reading →<\/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,1,14],"tags":[70,433,131,628,526],"class_list":["post-3295","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-general","category-mysql","tag-drizzle","tag-merge","tag-myisam","tag-mysql","tag-partitioning"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-R9","jetpack-related-posts":[{"id":3291,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/18\/where-are-they-now-mysql-storage-engines\/","url_meta":{"origin":3295,"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 "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3302,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/20\/the-memory-storage-engine\/","url_meta":{"origin":3295,"position":1},"title":"The MEMORY storage engine","author":"Stewart Smith","date":"2013-04-20","format":false,"excerpt":"I recently wrote about\u00c2\u00a0Where are they now: MySQL Storage Engines\u00c2\u00a0and\u00c2\u00a0The MERGE storage engine: not dead, just resting\u00e2\u20ac\u00a6. or forgotten.\u00c2\u00a0Today, it's the turn of the MEMORY storage engine - otherwise known as HEAP. This is yet another piece of the MySQL server that sits largely unmaintained and unloved. The MySQL Manual\u2026","rel":"","context":"In "drizzle"","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":3237,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/03\/08\/mysql-modularity-are-we-there-yet\/","url_meta":{"origin":3295,"position":2},"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 "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1636,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/05\/15\/size-of-storage-engines\/","url_meta":{"origin":3295,"position":3},"title":"Size of Storage Engines","author":"Stewart Smith","date":"2009-05-15","format":false,"excerpt":"For whatever reason, let's look at \"Total Physical Source Lines of Code\" from a recent mysql-6.0 tree (and PBXT from PBXT source repo): See the spreadsheet here. Raw data: Blackhole\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 336 CSV\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 1143 Archive\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 2960 MyISAM\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 34019 PBXT\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 41732 Maria\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 69019 InnoDB\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 82557 Falcon\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 91158 NDB\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 365272 NDB has a\u2026","rel":"","context":"In "drizzle"","block_context":{"text":"drizzle","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/drizzle-work-et-al\/"},"img":{"alt_text":"","src":"http:\/\/spreadsheets.google.com\/pub?key=r_gtZ-mKTsbSlp6anfnACXw&oid=1&output=image","width":350,"height":200},"classes":[]},{"id":3304,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/05\/13\/the-mysql-cluster-storage-engine\/","url_meta":{"origin":3295,"position":4},"title":"The MySQL Cluster storage engine","author":"Stewart Smith","date":"2013-05-13","format":false,"excerpt":"This is one close to my heart. I've recently written on other storage engines:\u00c2\u00a0Where are they now: MySQL Storage Engines,\u00c2\u00a0The MERGE storage engine: not dead, just resting\u00e2\u20ac\u00a6. or forgotten\u00c2\u00a0and The MEMORY storage engine. Today, it's the turn of MySQL Cluster. Like InnoDB, MySQL Cluster started outside of MySQL. Those of\u2026","rel":"","context":"In "code"","block_context":{"text":"code","link":"https:\/\/www.flamingspork.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1847,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/03\/15\/thoughts-on-thoughts-on-drizzle\/","url_meta":{"origin":3295,"position":5},"title":"Thoughts on Thoughts on Drizzle :)","author":"Stewart Smith","date":"2010-03-15","format":false,"excerpt":"Mark has some good thoughts on drizzle. I think they're all valid... and have some extra thoughts too: \"I have problems to solve today\". This is (of course) an active concern in my brain... If we don't have something out that solves some set of problems with reasonable stability and\u2026","rel":"","context":"In "drizzle"","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\/3295","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=3295"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3295\/revisions"}],"predecessor-version":[{"id":3305,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3295\/revisions\/3305"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3295"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3295"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3295"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}