{"id":2229,"date":"2010-12-13T14:37:45","date_gmt":"2010-12-13T04:37:45","guid":{"rendered":"http:\/\/www.flamingspork.com\/blog\/?p=2229"},"modified":"2010-12-13T14:37:45","modified_gmt":"2010-12-13T04:37:45","slug":"persistent-index-statistics-for-innodb","status":"publish","type":"post","link":"https:\/\/www.flamingspork.com\/blog\/2010\/12\/13\/persistent-index-statistics-for-innodb\/","title":{"rendered":"Persistent index statistics for InnoDB"},"content":{"rendered":"

In browsing the BZR tree for lp:mysql-server<\/a>, I noticed some rather exciting code had been merged into the Innobase code.<\/p>\n

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 plans.<\/p>\n

The problem being that this is many disk seeks. It means that on server restart, you have to spend a whole bunch of time seeking around the disk reading index pages.<\/p>\n

Not any more.<\/p>\n

There is now code merged in to store the calculated statistics in a table inside InnoDB so that these index dives don’t have to happen on startup.<\/p>\n

Originally, this looked like it was going to make it into InnoDB+<\/a>. The good news is that it’s now in a public source tree. I look forward to when it hits a stable release.<\/p>\n

(hopefully somebody other than me can beat me to it and write a nice description of the algorithms involved… the code is pretty easy to follow, so it shouldn’t be hard)<\/p>\n","protected":false},"excerpt":{"rendered":"

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 … 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_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":[75,14],"tags":[100,70,392,377,628,148],"class_list":["post-2229","post","type-post","status-publish","format-standard","hentry","category-drizzle-work-et-al","category-mysql","tag-bzr","tag-drizzle","tag-index","tag-innobase","tag-mysql","tag-statistics"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5a6n8-zX","jetpack-related-posts":[{"id":2227,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/12\/13\/innobase-1-1-3-in-drizzle\/","url_meta":{"origin":2229,"position":0},"title":"Innobase 1.1.3 in Drizzle","author":"Stewart Smith","date":"2010-12-13","format":false,"excerpt":"In case you haven't heard yet, I've merged in the latest InnoDB from MySQL 5.5.7 into Drizzle. The innobase plugin is now based on InnoDB 1.1.3. This gets a lot of bug fixes and improvements from 1.1.2 (and on 1.1.1). Enjoy!","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":2330,"url":"https:\/\/www.flamingspork.com\/blog\/2011\/03\/24\/drizzle-online-backup-with-xtrabackup\/","url_meta":{"origin":2229,"position":1},"title":"Drizzle online backup with xtrabackup","author":"Stewart Smith","date":"2011-03-24","format":false,"excerpt":"For backups, historically in the MySQL world you've had mysqldump (a SQL dump, means on restore you have to rebuild indexes), InnoDB Hot Backup (proprietary, but takes a copy of the InnoDB data files, so restore is much quicker), LVM snapshots (various scripts exist, does have larger IO impact, requires\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":2147,"url":"https:\/\/www.flamingspork.com\/blog\/2010\/09\/30\/what-was-innodb\/","url_meta":{"origin":2229,"position":2},"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 "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":3312,"url":"https:\/\/www.flamingspork.com\/blog\/2013\/04\/26\/a-few-notes-on-innodb-in-mysql-5-7-1\/","url_meta":{"origin":2229,"position":3},"title":"A few notes on InnoDB in MySQL 5.7.1","author":"Stewart Smith","date":"2013-04-26","format":false,"excerpt":"I've started poking around the MySQL 5.7.1 source tree (although just from tarball as I don't see a BZR tree yet). I thought I'd share a few thoughts: InnoDB temporary tables. Not REDO logged. What does this mean? It's a huge step in removing the dependency on MEMORY and MyISAM\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":3801,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/09\/19\/mysql-architecture\/","url_meta":{"origin":2229,"position":4},"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 "mysql"","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":3664,"url":"https:\/\/www.flamingspork.com\/blog\/2014\/02\/04\/ghosts-of-mysql-past-part-2\/","url_meta":{"origin":2229,"position":5},"title":"Ghosts of MySQL Past: Part 2","author":"Stewart Smith","date":"2014-02-04","format":false,"excerpt":"This continues on from my post yesterday and also contains content from my linux.conf.au 2014 talk (view video here). Way back in May in the year 2000, a feature was added to MySQL that would keep many people employed for many years - replication. In 3.23.15 you could replicate from\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\/2229","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=2229"}],"version-history":[{"count":2,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2229\/revisions"}],"predecessor-version":[{"id":2231,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/posts\/2229\/revisions\/2231"}],"wp:attachment":[{"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/media?parent=2229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/categories?post=2229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flamingspork.com\/blog\/wp-json\/wp\/v2\/tags?post=2229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}