Infrastructure at your Service

Grégory Steulet

Oracle Open World 2018 D2: Peter Zaitsev – MySQL 8 Field Report

As a former MySQL Consultant during this second day (24.10.2018) I couldn’t miss a session given by Peter Zaitsev founder of Percona and assuming the role of CEO. This session named MySQL 8 Field Report is a kind of summary of all new features encapsulated in MySQL 8.

Peter Zaitsev

During the first slides, Peter presented performance related to¬†utf8mb4¬†since it’s the default characterset in version 8. These slides had an Oracle logo on the botom that why I¬†prefer make some tests before speaking about these results. However according to these slides there is a strong performance increase on OTLP database in RO as well as in RW compared to MySQL 5.7.

Security

In terms of security Peter spoke about:

  • Roles
  • Breakdown of Super Privileges
  • Password history
  • Faster cached-SHA2 Authentication
  • skip-grants blocks remote connections
  • Multiple Addresses for¬†bind address¬†(8.0.13)
  • Require Password for Password Change¬†(8.0.13)
  • Redo and Undo Logs are now encrypted if Table Encryption is enabled

In the trend of autonomous database MySQL 8 is able to tune automatically the following parameters:

  • innodb_buffer_pool_size
  • innodb_log_file_size
  • innodb_flush_method

if you set¬†innodb_dedicated_server¬†to auto-tune. However as explained in the documentation:¬†“Only consider enabling this option if your MySQL instance runs on a dedicated server where the MySQL server is able to consume all available system resources. Enabling this option is not recommended if your MySQL instance shares system resources with other applications.”

Partial In-Place Update for JSON and invisible Index

It’s not anymore required to do a full rewrite of a field in MySQL 8 you can now update field in JSON object. However only update and removal of element is supported. Full support has been added in maintenance releases.

Thanks to invisible indexes you can test impact of dropping indexes before actually dropping them. You can use use_invisible_indexes to use invisible indexes in a session.

Improved Optimizer Cost Model

Peter gave us an interesting link regarding MySQL 8.0 Optimizer, the unofficial MySQL 8.0 Optimizer Guide. I really advice you to have a look on this very interesting website.

Performance Schema

About performance schema MySQL 8.0 provides the following:

Resource Groups

“MySQL supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group.” –¬†MySQL Documentation

According to Peter’s slides MYSQL 8.0 is about 100% faster (select and update) with resource groups.

Developer features

  • Instant Add Column (add column without rebuilding table)
Alter table t1 add column d int default 1000, algorithm=instant;
  • Better Handlinf of Hot Row Contention
  • Descending flag in index definition is no more ignored
    • Allows efficient handling of ORDER BY A ASC, B DESC queries
  • JSON to Table Conversion (Labs)
  • Much Better¬†GIS
  • Functions in DEFAULT (8.0.13)
Create table t2 (a binary(16) default uuid_to_bin(uuid()));
Create index idx1 ON t1 ((col1+col2));
  • MySQL Document Store
    • Full Text Indexing
    • GeoJSON Support

As a summary Peter concludes by telling that MySQL 8 looks like release to be excited about and has a lot of new features both for Devs and Ops.

 

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Grégory Steulet
Grégory Steulet

Chief Financial Officer (CFO) and Partner Manager
Delivery Manager