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.
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.
In terms of security Peter spoke about:
- 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:
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.
About performance schema MySQL 8.0 provides the following:
- (Fake) indexes for faster access
- Error Instrumentation
- Response Time Histograms (Global and Per Query Digest)
- Query Examples for Summary by Digest
“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.
- 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()));
- Functional indexes (8.0.13)
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.