{"id":3312,"date":"2013-04-26T10:06:34","date_gmt":"2013-04-26T00:06:34","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=3312"},"modified":"2013-04-26T10:06:34","modified_gmt":"2013-04-26T00:06:34","slug":"a-few-notes-on-innodb-in-mysql-5-7-1","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/26\/a-few-notes-on-innodb-in-mysql-5-7-1\/","title":{"rendered":"A few notes on InnoDB in MySQL 5.7.1"},"content":{"rendered":"<p>I&#8217;ve started poking around the MySQL 5.7.1 source tree (although just from tarball as I don&#8217;t see a BZR tree yet). I thought I&#8217;d share a few thoughts:<\/p>\n<ul>\n<li>InnoDB temporary tables. Not REDO logged. What does this mean? It&#8217;s a huge step in removing the dependency on MEMORY and MyISAM engines for temporary tables used in query execution. With InnoDB temporary tables there is no reason for MEMORY engine to continue to exist, there is absolutely no way in which it is better.<\/li>\n<li><span style=\"line-height: 15px;\">InnoDB temp tables aren&#8217;t insert buffered<br \/>\nThis probably doesn&#8217;t really matter as you&#8217;re not going to be doing REDO logging for them (plus things are generally short lived)&#8230; but it could be a future area for performance improvement<\/span><\/li>\n<li>The NO_REDO log mode appears to be implemented fairly neatly.<\/li>\n<li>Improvements in innodb read only mode. What does this mean? Maybe we can finally get rid of the oddity of compressed read only MyISAM tables on read only media. (on the other hand, CDs and DVDs aren&#8217;t exactly a modern form of software distribution).<\/li>\n<li>Some of the source code comments have improved.. it&#8217;s getting easier to understand InnoDB. I&#8217;d still make the argument that if you need source code comments you&#8217;re code isn&#8217;t clear enough&#8230; but any step is an improvement. (that being said, InnoDB was always easier than the server)<\/li>\n<li>There is some pretty heavy refactoring of lock0lock.cc &#8211; I really need to sit down and poke at it a bunch.<\/li>\n<li>The shared tablespace code (innodb system tablespace) has been heavily refactored. This also introduces tablespaces for temporary tables &#8211; and it appears to be implemented in the correct way.<\/li>\n<\/ul>\n<p>I need to look into things a bunch more, and it&#8217;ll be really useful to see a bzr tree to better understand some of the changes.<\/p>\n<p>More to come later, but that&#8217;s my quick look.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve started poking around the MySQL 5.7.1 source tree (although just from tarball as I don&#8217;t see a BZR tree yet). I thought I&#8217;d share a few thoughts: InnoDB temporary tables. Not REDO logged. What does this mean? It&#8217;s a &hellip; <a href=\"https:\/\/www.flamingspork.com\/blog\/2013\/04\/26\/a-few-notes-on-innodb-in-mysql-5-7-1\/\">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_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":[528,257,628],"class_list":["post-3312","post","type-post","status-publish","format-standard","hentry","category-code","category-mysql","tag-5-7-1","tag-innodb","tag-mysql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-Rq","jetpack-related-posts":[{"id":3801,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/09\/19\/mysql-architecture\/","url_meta":{"origin":3312,"position":0},"title":"Some current MySQL Architecture writings","author":"Stewart Smith","date":"2014-09-19","format":false,"excerpt":"So, I've been looking around for a while (and a few times now) for any good resources that cover a bunch of MySQL architecture and technical details aimed towards the technically proficient but not MySQL literate audience. I haven't really found anything. I mean, there's the (huge and very detailed)\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":2147,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/09\/30\/what-was-innodb\/","url_meta":{"origin":3312,"position":1},"title":"What was InnoDB+?","author":"Stewart Smith","date":"2010-09-30","format":false,"excerpt":"Yes, I said InnoDB+ with a plus sign at the end (also see the first comment here). Please note that this blog post is only based on public information. It has absolutely nothing in it that I only could have learned from back when I worked at Sun or MySQL\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":3268,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/03\/15\/innodb_sys_fields-vs-innodb_sys_columns\/","url_meta":{"origin":3312,"position":2},"title":"INNODB_SYS_FIELDS vs INNODB_SYS_COLUMNS","author":"Stewart Smith","date":"2013-03-15","format":false,"excerpt":"In MySQL 5.6 we have two new INFORMATION_SCHEMA tables for InnoDB that are likely going to cause confusion: INNODB_SYS_FIELDS and INNODB_SYS_COLUMNS. You may think these are likely to just be aliases of each other in order to make your life easier. However... These are not the same thing. The INNODB_SYS_FIELDS\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":2353,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/04\/12\/innodb-and-memcached\/","url_meta":{"origin":3312,"position":3},"title":"innodb and memcached","author":"Stewart Smith","date":"2011-04-12","format":false,"excerpt":"I had a quick look at the source tree (I haven't compiled it, just read the source - that's what I do. I challenge any C\/C++ compiler to keep up with my brain!) that's got a tarball up on labs.mysql.com for the memcached interface to innodb. A few quick thoughts:\u2026","rel":"","context":"In &quot;General&quot;","block_context":{"text":"General","link":"https:\/\/www.flamingspork.com\/blog\/category\/general\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":2229,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/12\/13\/persistent-index-statistics-for-innodb\/","url_meta":{"origin":3312,"position":4},"title":"Persistent index statistics for InnoDB","author":"Stewart Smith","date":"2010-12-13","format":false,"excerpt":"In browsing the BZR tree for lp:mysql-server, I noticed some rather exciting code had been merged into the Innobase code. You may be aware that InnoDB will do some index dives when opening a table to get some statistics about the indexes that can help the optimiser make good query\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":3752,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/06\/03\/mysql-5-6-on-power-patch-available\/","url_meta":{"origin":3312,"position":5},"title":"MySQL 5.6 on POWER (patch available)","author":"Stewart Smith","date":"2014-06-03","format":false,"excerpt":"The following sentence is brought to you by IBM Legal. The postings on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. Okay, now that is out of the way.... If you're the kind of person who follows the MySQL bugs database closely or subscribes\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\/3312","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=3312"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3312\/revisions"}],"predecessor-version":[{"id":3314,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/3312\/revisions\/3314"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=3312"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=3312"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=3312"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}