What’s New in MySQL 5.7? InnoDB, JSON, and Feature Highlights

1. InnoDB New Features

MySQL 5.7 introduces several InnoDB enhancements:

Buffer dump improvement : The innodb_buffer_pool_dump_pct parameter lets you control the percentage of pages dumped per buffer‑pool instance (default 100%). When system I/O is tight, InnoDB may lower this percentage automatically.

Multi‑threaded page cleaning : Controlled by innodb_page_cleaners (range 1‑64, default 4 from 5.7.8). If the value exceeds innodb_buffer_pool_instances, it is capped to the instance count.

Dynamic buffer‑pool resizing : From 5.7.5 you can resize innodb_buffer_pool_size online. Resizing occurs in chunk units (default 128 MiB). The new size must be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If not, MySQL rounds up to the next valid value.

Global tablespace : Allows tables from all databases to share a tablespace, reducing metadata memory compared with file‑per‑table tablespaces.

Default row format DYNAMIC : Since 5.7.9 the default InnoDB row format is DYNAMIC; you can control it with innodb_default_row_format.

Native partitioning : From 5.7.6 MySQL creates a single partition‑aware handler for all partitions, saving memory. Upgrade existing partitioned tables with ALTER TABLE ... UPGRADE PARTITIONING.

Truncate undo logs : Enabled with innodb_undo_log_truncate=ON (requires at least two undo tablespaces). This prevents uncontrolled growth of the ibdata file.

Online buffer‑pool resizing steps (illustrated in the image below):

Calculate the desired new buffer‑pool size.

Disable AHI and clear all index caches.

Traverse the free‑list to find reusable blocks.

Lock the entire buffer pool.

Move or delete chunks as needed.

Set innodb_buffer_pool_size to the new value.

Re‑enable AHI.

2. JSON Support

Starting with MySQL 5.7.8, a native JSON data type stores data in a binary format for fast value lookup. Insert and update operations validate JSON syntax, and four built‑in JSON functions are provided.

Typical workflow (illustrated with screenshots): create a table with a JSON column, insert JSON data, modify it, delete rows, and query specific keys.

3. sys Schema

MySQL 5.7 adds the sys schema, which provides convenient views that combine information from information_schema and performance_schema. It helps DBAs quickly locate performance bottlenecks. Example queries are shown in the official documentation.

4. Functional Enhancements

Optimizer hints : From 5.7.7 MySQL supports optimizer‑level hints (e.g., /*+ MAX_EXECUTION_TIME(1000) */) to influence execution plans. The hint types are documented with examples.

Trigger enhancements : Prior to 5.7 a table could have only one trigger per action/timing. New versions allow multiple triggers for the same action.

syslog support : MySQL can now write error logs directly to syslog on both Unix and Windows via the log_syslog option.

Virtual columns : MySQL 5.7 supports both virtual and stored generated columns. Virtual columns are computed on read and, since 5.7.8, can be indexed. Example syntax is shown below.

5. Summary

The article reviews MySQL 5.7’s InnoDB improvements, native JSON handling, sys schema, and a suite of functional enhancements such as optimizer hints, multi‑trigger support, syslog integration, and virtual columns. Upcoming posts will cover replication, GTID, performance tuning, and upgrade strategies.