TOP 10 MySQL 8.0 features for DBAs & OPS

Today, let’s have a look at the TOP 10 new features in MySQL 8.0 that will improve DBA’s life.

To shrink the list to 10 only items wasn’t an easy task, but here is the top 10:

1. Temporary Tables Improvements
2. Persistent global variables
3. No more MyISAM System Tables
4. Reclaim UNDO space from large transactions
5. UTF8 performance
6. Removing Query Cache
7. Atomic DDLs
8. Faster & More Complete Performance Schema (Histograms, Indexes, …) and Information Schema
9. ROLES
10. REDO & UNDO logs encrypted if tablespace is encrypted

Temporary Tables Improvements

Since 5.7, all internal temporary tables are created in a unique shared tablespace called “ibtmp1“.

Additionally, the metadata for temp tables will also be stored in memory (not anymore in .frm files).

In MySQL 8.0, the MEMORY storage engine will also be replaced as default engine for internal temporary tables (those created by the Optimizer during JOIN, UNION, …) by the TempTable storage engine. This new engine provides more efficient storage for VARCHAR and VARBINARY columns (with Memory the full maximum size is allocated).

Persistent Global Variables

With MySQL 8.0 it is now also possible to set variables and make the change persistent to server’s reboot. I’ve written a dedicated blog post that you can check for more information.

Combined this syntax and the new RESTART command, makes very easy to configure MySQL from its shell. This is a cloud friendly feature!

No more MyISAM System Tables

With the new native Data Dictionary, we won’t need MyISAM system tables anymore ! Those tables and the data dictionary tables are now created in a single InnoDB tablespace file named mysql.idb in the data directory. This means that if you don’t explicitly use MyISAM tables (which is totally inadvisable if you care about your data) you can have a MySQL instance without any MyISAM table.

Reclaim UNDO space from large transactions

In MySQL 5.7, we already added the possibility to truncate undo spaces (innodb_undo_log_truncate, disabled by default). In MySQL8, we changed the undo disk format to support a huge number of rollback segments per undo tablespaces. Also, by default, the rollback segments are now created in two separate undo tablespaces instead of the InnoDB system tablespace (2 is now the minimum and this setting is now dynamic). We also deprecated the variable to set that value (innodb_undo_tablespaces) as we will provide SQL commands giving DBAs a real interface to interact with UNDO Tablespaces too.

Automatic truncation of undo tablespaces is also now enabled by default.

UTF8 Performance

The default character set has changed from latin1 to utf8mb4 as UTF8 is now much faster up to 1800% faster on specific queries ! Emojis are everywhere now and MySQL supports them without problem ! 🐬

Removing Query Cache

The first thing I was always advising during a performance audit was to disable the Query Cache as it didn’t scale by design. The MySQL QC was creating more issues than it solved. We decided to simply remove it in MySQL 8.0 as nobody should use it. If your workload requires a Query Cache, then you should have a look to ProxySQL as Query Cache.

Atomic DDLs

With the new Data Dictionary, MySQL 8.0 now supports Atomic Data Definition Statements (Atomic DDLs). This means that when a DDL is performed, the data dictionary updates, the storage engine operation and the writes in the binary log are combined into a single atomic transaction that is either fully executed or not at all. This provides a better reliability where unfinished DDLs don’t leave any incomplete data.

Faster & More Complete Performance Schema (Histograms, Indexes, …) and Information Schema

Many improvements were made to Performance Schema like fake indexes or histograms.

With the contribution of fake indexes, queries like SELECT * FROM sys.session became 30x faster. Tables scans are now avoided as much as possible and the use of indexes improves a lot the execution time. Additionally to that, Performance Schema also provides histograms of statements latency. The Optimizer can also benefit form these new histograms.

Information Schema has also been improved by the use of the Data Dictionary. No more .frm files are needed to know the table’s definition. Also this allow to scale to more than 1.000.000 tables !

ROLES

SQL Roles have been added to MySQL 8.0. A role is a named collection of privileges. Like user accounts, roles can have privileges granted to and revoked from them. Roles can be applicable by default or by session. There is also the possibility to set roles to be mandatory.

REDO & UNDO logs encrypted if tablespace is encrypted

In MySQL 5.7, it was possible to encrypt an InnoDB tablespace for tables stored in file-per-table. In MySQL 8.0 we completed this feature by adding encryption for UNDO and REDO logs too.

And once again, the list of improvements doesn’t finish here. There are many other nice features. I would like to list below some other important ones (even if they are all important of course 😉 )

1. persistent auto increment
2. InnoDB self tuning
3. JSON performance
4. Invisible Indexes
5. new lock for backup
6. Resource Groups
7. additional metadata into binary logs
8. OpenSSL for Community Edition too

Please check the online manual to have more information about all these new features.

!! UPDATE !!

As we keep improving MySQL 8.0 along releases, there is another reason to upgrade to MySQL 8.0, maybe one of the most required features when having to deal with large tables. Developers won’t be afraid to add columns to their schema anymore and DBAs/OPS won’t be afraid to run them or won’t need to use 3rd party tools to perform this (in the past) complicated task : Instant ADD Column !

InnoDB INSTANT ADD COLUMN

Thank to the new Data Dictionary it’s now possible to perform a metadata change and add a new column directly at the end of a table without having to rebuild it ! This is the end of ALTER statements running for ever and blocking everything every time a developer wanted to add a column to a table.

And this is only the beginning of the journey to instant DDLs.