have benchmarked and written<\/a> about it.<\/p>\nCompression<\/h4>\n
Using the new Barracuda<\/strong> table format, table data can be compressed. Compression depends on the type of data you have in your table, and in KEY_BLOCK_SIZE<\/strong>. I have found tables with lots of textual data to compress well, to about 25% volume (that is, reduction of 75%), and strictly integer-typed tables (like an a-2-b connecting table) to compress poorly.<\/p>\nI have seen an InnoDB 50GB database shrink into some 12GB only. Wow! That meant a server which only had RAID 1 two 72GB disks, and which was dangerously filled up with disk space, could now accommodate the database, a backup, and then some!<\/p>\n
Compression does not only occur on disk: when pages are loaded to memory, they are loaded compressed. This means the same innodb_buffer_pool_size<\/strong> you had, now holds a lot more data.<\/p>\nThere is no compression for the undo buffer.<\/p>\n
INFORMATION_SCHEMA<\/h4>\n
There’s lot’s of useful data in the new INFORMATION_SCHEMA<\/strong> tables. Very interesting questions can be answered:<\/p>\n\n- How many transactions are open right now?<\/li>\n
- What’s the state of each transaction?<\/li>\n
- What queries are being run right now?<\/li>\n
- Are any transaction waiting on locks? Which locks? Held by which transactions?<\/li>\n
- What kind of locks are currently held? On which tables?<\/li>\n
- How much time has been spent on compression\/uncompress?<\/li>\n
- How many compression operations have occurred?<\/li>\n
- How many pages are allocated? How many are free?<\/li>\n<\/ul>\n
Fast index creation<\/h4>\n
Just to be clear, this is not<\/em> non-blocking index creation. It’s just fast<\/em>. What’s fast<\/em>? Well, slow<\/em> index creation is what we’re used to: to add an index we ALTER TABLE<\/strong>, thereby creating a new, temporary tablespace, into which the entire table’s data is copied. With fast index creation, table data is left untouched. The new index is created by scanning the primary key and to-be indexed values. Thus, it requires less I\/O operations.<\/p>\nDropping an index is an instantaneous operation.<\/p>\n","protected":false},"excerpt":{"rendered":"
I wish to present some compelling reasons to use the InnoDB plugin. The plugin is a drop-in replacement for “normal” InnoDB tables; enabling many new features. It is the outcome of a long termed silence from InnoBase (Oracle), which were thought to be neglecting the InnoDB engine. I’m going to leave out “performance” for the […]<\/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,"enabled":false},"version":2}},"categories":[5],"tags":[14],"class_list":["post-951","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-innodb"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-fl","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/951","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=951"}],"version-history":[{"count":16,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/951\/revisions"}],"predecessor-version":[{"id":1109,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/951\/revisions\/1109"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=951"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=951"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=951"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}