BT

InnoDB Enhancements for MySQL 5.7

| by Jonathan Allen Follow 577 Followers on Mar 23, 2015. Estimated reading time: 3 minutes |

MySQL 5.7 comes with over 20 enhancements to InnoDB. In this, the first of our reports on MySQL 5.7, we will cover some of the highlights such as temp table performance, spatial indexes, and full text search parsers.

Normally changing the data type for column is quite expensive, to the point where the whole table needs to be copied into a new structure. With 5.7, you can now grow varChar columns as long as either of these criteria are met:

  • Both the original size and new size is less than 256 characters.
  • Both the original size and new size are greater than or equal 256 characters.

Columns can grow in this fashion, but cannot be shrunk without a full table copy. Interestingly enough, one can add a new column and drop an old one in-place, making this restriction somewhat odd.

Temp Tables

InnoDB based temp tables are no longer stored in normal system tables. Instead, a new table called INNODB_TEMP_TABLE_INFO is created the first time someone queries against it. Since examining temp tables is a rare operation, most instances of MySQL will probably never create this table and thus see a performance boost.

Non-compressed temp tables are now stored in their tablespace. By default this tablespace is stored in DATADIR, but can be overridden if you wish to have a dedicated drive for them tables. (This is a standard practice for other database engines such as SQL Server.) Compressed temp tables get one tablespace per table and are stored in TMPDIR.

A new log format has been introduced for temp tables. This format still supports undo operations, which are necessary for rolling back transactions, but eliminates the “redo” capability. In normal tables, the ability to perform a redo operation is necessary for crash recovery. Since temp tables are deleted on startup anyways, redo support is just means unnecessary bookkeeping.

If you have multiple undo logs, then you also gain the ability to truncate them. Multiple logs are required so that as each log is being truncated, the other logs can take over the server load.

Spatial Types

Spatial types are now natively supported by InnoDB using the DATA_GEOMETRY type. Previously they would be stored as binary BLOB data.

Along with this is the ability to create spatial indexes.

Full Text Search

Parsing is an important consideration for any full text engine. While many users can get away with just the built-in parser, some require support for languages or file formats that are not offered “out of the box”. In order to address this, InnoDB tables now supports a plug-in architecture for its full text engine. To prove the concept, these new parsers were created:

A character-based ngram full-text parser that supports Chinese, Japanese, and Korean (CJK), and a word-based MeCab parser plugin that supports Japanese were introduced in MySQL 5.7.6, for use with InnoDB tables.

Once installed, the parser will need to be explicitly associated with the indexes that need to use it.

Indexes

Bulk loading is now used when creating or rebuilding indexes.

This method of index creation is also known as a “sorted index build”. This enhancement, which improves the efficiency of index creation, also applies to full-text indexes. A new global configuration option, innodb_fill_factor, defines the percentage of space on each page that is filled with data during a sorted index build, with the remaining space reserved for future index growth. For more information, see Section 14.13.18, “Bulk Load for CREATE INDEX”.

Partitioning

Partitioning is now natively supported by InnoDB. In the past, partitioning required the use of the ha_partition handler, which requires one handler object per partition instead of one per table.

Partitioned tables are also easier to move using the Transportable Tablespace feature.

Traditionally, this has not been possible for InnoDB tablespaces because all table data was part of the system tablespace. In MySQL 5.6 and higher, the FLUSH TABLES ... FOR EXPORT syntax prepares an InnoDB table for copying to another server; running ALTER TABLE ... DISCARD TABLESPACE and ALTER TABLE ... IMPORT TABLESPACE on the other server brings the copied data file into the other instance.

Fusion-io Non-Volatile Memory

When using the Fusion-io Non-Volatile Memory filesystem, MySQL can switch from using InnoDB’s doublewrite buffer to atomic writes. “The InnoDB doublewrite buffer is automatically disabled for system tablespace files (ibdata files) located on Fusion-io devices that support atomic writes”.

Rate this Article

Adoption Stage
Style

Hello stranger!

You need to Register an InfoQ account or or login to post comments. But there's so much more behind being registered.

Get the most out of the InfoQ experience.

Tell us what you think

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread
Community comments

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Allowed html: a,b,br,blockquote,i,li,pre,u,ul,p

Email me replies to any of my messages in this thread

Discuss

Login to InfoQ to interact with what matters most to you.


Recover your password...

Follow

Follow your favorite topics and editors

Quick overview of most important highlights in the industry and on the site.

Like

More signal, less noise

Build your own feed by choosing topics you want to read about and editors you want to hear from.

Notifications

Stay up-to-date

Set up your notifications and don't miss out on content that matters to you

BT