{"id":3302,"date":"2013-04-20T10:02:21","date_gmt":"2013-04-20T00:02:21","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=3302"},"modified":"2013-04-19T10:16:23","modified_gmt":"2013-04-19T00:16:23","slug":"the-memory-storage-engine","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/20\/the-memory-storage-engine\/","title":{"rendered":"The MEMORY storage engine"},"content":{"rendered":"

I recently wrote about\u00c2\u00a0Where are they now: MySQL Storage Engines<\/a>\u00c2\u00a0and\u00c2\u00a0The MERGE storage engine: not dead, just resting\u00e2\u20ac\u00a6. or forgotten.<\/a>\u00c2\u00a0Today, it’s the turn of the MEMORY storage engine – otherwise known as HEAP.<\/p>\n

This is yet another piece of the MySQL server that sits largely unmaintained and unloved. The MySQL Manual even claims that it supports encryption<\/a>… with the caveat of having to use the SQL functions for encryption\/decryption rather than in the engine itself (so, basically, it supports encryption about as much as every other engine does).<\/p>\n

The only “recent” innovation in the MEMORY engine was the dynamic row patch that ended up making its way into Percona Server<\/a> (and isn’t enabled by default). This forced me to go and look at the code of the MEMORY engine again and I cannot possibly drink enough in my lifetime to erase the memory.<\/p>\n

The MEMORY engine is used by just about everybody as you probably have a SQL query somewhere that uses an in memory temporary table. I can, however, feel the comments being added to this post right now by people who use gdb to set server variables that not a single query in their systems use MEMORY….. (IIRC there have been some patches around that would throw an error rather than create a temporary table)<\/p>\n

We had a early version of the dynamic row format patch in Drizzle for a while… and if you turned it on, all sorts of things horrifically broke. It was a remarkably non-trivial amount of work to get that code to work properly and this is largely a testament to the “design” of the MEMORY engine.<\/p>\n

While it may be efficient or fast or something (likely on 1990s hardware and workloads), it misses the boat completely on the things that matter today: simultaneous access, MVCC, BLOB\/TEXT columns and transactions. Basically, it’s a engine that’s really only useful for a single connection in limited use cases…. and even then, it’s likely a good way to ruin things. MyISAM is better as at least on memory pressure things may be written out to disk sensibly…. and if InnoDB had a “don’t log this table” mode it would beat that absolute pants off it.<\/p>\n

It is, again, another part of the MySQL server that’s remarkably hard to pull out and replace with something different\/better. Why? Well, I wrote about it before:\u00c2\u00a0Refactoring Internal temporary tables (another stab at it)<\/a>. If it was easy, we’d likely have Tokyo Cabinet (via BlitzDB) or similar (some bit of code maintained by other people) doing the same job in Drizzle rather than this large chunk of code that nobody really cares about.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … 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,14],"tags":[525,628,635,279,80],"class_list":["post-3302","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-mysql","tag-memory","tag-mysql","tag-percona","tag-storage-engine","tag-storage-engine-api"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-Rg","jetpack-related-posts":[{"id":1931,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/05\/26\/blobs-in-the-drizzlemysql-storage-engine-api\/","url_meta":{"origin":3302,"position":0},"title":"BLOBS in the Drizzle\/MySQL Storage Engine API","author":"Stewart Smith","date":"2010-05-26","format":false,"excerpt":"Another (AFAIK) undocumented part of the Storage Engine API: We all know what a normal row looks like in Drizzle\/MySQL row format (a NULL bitmap and then column data): Nothing that special. It's a fixed sized buffer, Field objects reference into it, you read out of it and write the\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":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2010\/05\/rect28161-300x67.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":3304,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/05\/13\/the-mysql-cluster-storage-engine\/","url_meta":{"origin":3302,"position":1},"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":3685,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/02\/25\/ghosts-of-mysql-past-part-7-pbxt\/","url_meta":{"origin":3302,"position":2},"title":"Ghosts of MySQL Past, Part 7: PBXT","author":"Stewart Smith","date":"2014-02-25","format":false,"excerpt":"Recently, I\u00e2\u20ac\u2122ve 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, Part 4, Part 5 and Part 6. My feed feel off Planet MySQL for a bit so you may have missed those posts - so\u2026","rel":"","context":"In "General"","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1642,"url":"https:\/\/www.flamingspork.com\/blog\/2009\/05\/15\/mysql-storage-engine-sloccount-over-releases\/","url_meta":{"origin":3302,"position":3},"title":"MySQL Storage Engine SLOCCount over releases","author":"Stewart Smith","date":"2009-05-15","format":false,"excerpt":"For a bit more info, what about various storage engines over MySQL releases. Have they changed much? Here we're looking at the storage\/X\/ directory for code, so for some engines this excludes the handler that interfaces with the MySQL Server. You can view the data on the spreadsheet.","rel":"","context":"In "mysql"","block_context":{"text":"mysql","link":"https:\/\/www.flamingspork.com\/blog\/category\/work-et-al\/mysql\/"},"img":{"alt_text":"","src":"http:\/\/spreadsheets.google.com\/pub?key=rD5FOoJk29j9RK7sm0abSsg&oid=1&output=image","width":350,"height":200,"srcset":"http:\/\/spreadsheets.google.com\/pub?key=rD5FOoJk29j9RK7sm0abSsg&oid=1&output=image 1x, http:\/\/spreadsheets.google.com\/pub?key=rD5FOoJk29j9RK7sm0abSsg&oid=1&output=image 1.5x"},"classes":[]},{"id":3347,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/05\/24\/an-old-note-on-the-storage-engine-api\/","url_meta":{"origin":3302,"position":4},"title":"An old note on the Storage Engine API","author":"Stewart Smith","date":"2013-05-24","format":false,"excerpt":"Whenever I stick my head into the MySQL storage engine API, I'm reminded of a MySQL User Conference from several years ago now. Specifically, I'm reminded of a slide from an early talk at the MySQL User Conference by\u00c2\u00a0Paul McCullagh describing developing PBXT. For \"How to write a Storage Engine\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":1879,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/05\/24\/using-the-row-buffer-in-drizzle-and-mysql\/","url_meta":{"origin":3302,"position":5},"title":"Using the row buffer in Drizzle (and MySQL)","author":"Stewart Smith","date":"2010-05-24","format":false,"excerpt":"Here's another bit of the API you may need to use in your storage engine (it also seems to be a rather unknown. I believe the only place where this has really been documented is ha_ndbcluster.cc, so here goes.... Drizzle (through inheritance from MySQL) has its own (in memory) row\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":"https:\/\/i0.wp.com\/www.flamingspork.com\/blog\/wp-content\/uploads\/2010\/04\/row-300x85.png?resize=350%2C200","width":350,"height":200},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3302","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=3302"}],"version-history":[{"count":1,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3302\/revisions"}],"predecessor-version":[{"id":3303,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3302\/revisions\/3303"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3302"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3302"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3302"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}