I would like to start this telling the reader that this is going to be the first of some blog posts I’m planning to exploit subjects around MySQL 8.0, as I have been testing its features. As I’m an Oracle ACE Director, part of the Oracle ACEs program, I received from my friend Fred Deschamps, currently the Oracle community Manager for MySQL, the early access to the binary as well as a briefing of the new features, changes and deprecations. I would like to say that I’ve got pretty excited with many of the coming features and changes for existing features available on 5.6/5.7 and I’m going to write more about some of the hot topics published here by Oracle MySQL 8.0. Just for the records and just in case you get curious, the operating system I’m using for this and other blog posts related to MySQL 8.0 is CentOS 7 with Kernel 3.10.0-229.el7.x86_64.

Current status of mysql.service:

[root@mysql80drm1 vagrant]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2016-08-28 01:51:51 CEST; 2s ago
Process: 16304 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
Process: 16229 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 16307 (mysqld)
CGroup: /system.slice/mysqld.service
└─16307 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
Aug 28 01:51:46 mysql80drm1 systemd[1]: Starting MySQL Server...
Aug 28 01:51:51 mysql80drm1 systemd[1]: Started MySQL Server.

As expected behavior since MySQL 5.7.6, the initial root account temporary password is generated on error log and must be changed on the first access as that temporary password is set as expired. Due to password validation plugin be enabled by default, you need to chose a good password to be able to change the root account one. Mine is P@ssw0rd to streamline it at this point.

[root@mysql80drm1 vagrant]# cat /var/log/mysqld.log | egrep "A temporary password is generated for root@localhost"
2016-08-27T23:51:47.582177Z 4 [Note] A temporary password is generated for root@localhost: aLpaL<?3p>T=

[root@mysql80drm1 vagrant]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 8.0.0-dmr

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
 affiliates. Other names may be trademarks of their respective
 owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER root@localhost IDENTIFIED BY 'P@ssw0rd';
 Query OK, 0 rows affected (0.00 sec)

mysql> \q
 Bye

MySQL 8.0 DMR 1 – Transaction Data Dictionary

When I started reading the document shared by Oracle for ACE regarding the coming changes for MySQL 8.0, I need to re-read it to really believe that the old I_S has gone. Additionally, all those files containing metadata persisted on disk do not exists anymore, so, “the FRM, TRG, PAR files are gone”.

mysql> create database wb;
 Query OK, 1 row affected (0.00 sec)

mysql> use wb;
 Database changed

mysql> \! ls -lh /var/lib/mysql/wb
 total 144K
 -rw-r----- 1 mysql mysql 144K Sep 11 02:07 t1.ibd

This a very good step for the product as we can now use I_S tables as the queries go to the same execution path as normal queries and not gather all the needed data on the query time or generate lots of disk seeks to responde to a query. Many blogs were written along the time since the mysql’s data dictionary appeared (http://www.technocation.org/content/how-tell-when-using-informationschema-might-crash-your-database-0).

The case is that, the current implementation of I_S is not useful when dealing with big instances, having lots of objects. As more objects you have in a mysql instance, as more risky become the queries against the data dictionary and this is one of the benefits I can see at this moment, as, when having I_S as Views, it’s going to improve the speed and make it stable when querying tables. Yet, about the new Data Dictionary, it’s good to have a transactional control, where reads completes independent of writes going on like DDL statements altering columns, for example. More information about this, http://mysqlserverteam.com/a-new-data-dictionary-for-mysql/. Morgan has written about the FRM files going away some time ago, http://www.tocker.ca/2014/07/30/beyond-the-frm-ideas-for-a-native-mysql-data-dictionary.html

If we compare the number of tables contained on the INFORMATION_SCHEMA between MySQL 5.7 and the 8.0, the latter has currently 6 additional tables. These tables on the new version will become VIEWS from the underlying tables that stores data in a dedicated dictionary tablespace and queries requesting metadata will go through the same process as any other regular query. Below we can see the New MySQL Data Dictionary architecture:

New Data Dictionary

Current DMR documentation compares what’;s avaulable on 5.7 and what’s coming with the New Data Dictionary on 8.0. Basically, 5.7 has all the .frm files for tables yet on disk in a persistent way. what was said to be an approximation of a data dictionary, but not yet centralized in one place. MySQL 8.0 has an explicit definition of what the data dictionary is and is not, namely an identified set of metadata tables stored in transactional storage (InnoDB). Some additional features can come soon regarding the names of the files as the engineers are thinking to use some internal identifiers to the file names, which will impact on the usage of a filename-safe encoding introduced on MySQL 5.1 which means that the “table name” that the storage engine gets is not the original table name, it is converted to be a safe filename. All the “troublesome” characters are encoded. You can check more about the assumptions about the schema definitions names clicking here, WL#6379. Yet on the same link, once can see the new tables’ definition.

We can think that, when one need to alter a column data type or even rebuild a table, data dictionary should be  accessible for reads and writes at the same time that other users are running some online schema changes. And this is the name of the new feature, Transactional Data Dictionary. I_S queries will run and be executed under different isolation level set by the user.

At the end, this is of a big benefit for DBAs that uses I_S as target of many scripts, having it as an impossible strategy due to the big number of objects on existing databases. I use to work daily with some customers that it’s prohibited to query I_S during business hours as it can crash the instance. I’m very happy to get this feature on MySQL 8.0 where I_S is now VIEWs of metadata tables and temporary tables and and preparation of TABLE_SHARE object upon every query execution; we know very well what is that, the scan of many files on disk to gather all the needed data to deliver result to the requester.

MySQL 8.0 DMR 1 – Invisible Indexes

One of the features that will add a good strategy to the sauce when you think about design review, focusing queries and table’s indexes is the Invisible Indexes, as a index can be marked as Visible or Invisible, being considered or not by the optimizer on query’s execution. As said on the DMR 1 docs, it should be a good topic to be considered when making a query more efficient. Below you can see things in action, considering the comments  for each row:

mysql> show tables;
+--------------+
| Tables_in_wb |
+--------------+
| t1 |
+--------------+
1 row in set (0.00 sec)

mysql> show create table t1;
+-------+----------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
 `i` int(11) DEFAULT NULL,
 KEY `i` (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select i from t1 use index(i)\G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 partitions: NULL
 type: index
possible_keys: NULL
 key: i
 key_len: 5
 ref: NULL
 rows: 1
 filtered: 100.00
 Extra: Using index
1 row in set, 1 warning (0.00 sec)

mysql> show index from t1\G
*************************** 1. row ***************************
 Table: t1
 Non_unique: 1
 Key_name: i
 Seq_in_index: 1
 Column_name: i
 Collation: A
 Cardinality: NULL
 Sub_part: NULL
 Packed: NULL
 Null: YES
 Index_type: BTREE
 Comment:
Index_comment:
 Visible: YES
1 row in set (0.01 sec)

We can make the above index invisible:

mysql> alter table t1 alter index i invisible;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show index from t1\G
*************************** 1. row ***************************
 Table: t1
 Non_unique: 1
 Key_name: i
 Seq_in_index: 1
 Column_name: i
 Collation: A
 Cardinality: NULL
 Sub_part: NULL
 Packed: NULL
 Null: YES
 Index_type: BTREE
 Comment:
Index_comment:
 Visible: NO
1 row in set (0.01 sec)

mysql> explain select i from t1 use index(i)\G
*************************** 1. row ***************************
 id: 1
 select_type: SIMPLE
 table: t1
 partitions: NULL
 type: ALL
possible_keys: NULL
 key: NULL
 key_len: NULL
 ref: NULL
 rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.00 sec)

Here, with this feature, you don’t need to remove an index to test queries, in case you think an index is a duplicate one as you can just make it visible or invisible.

MySQL 8.0 DMR 1 – MySQL System Database now in InnoDB

This work has started with MySQL 5.7 and now, they announced that this is completed. It was one of the most expected things on MySQL to make it full transactional and say a bye-bye to MyISAM. All the tables as below are in InnoDB, with the exception of the general and slow logs, that could impact server, writing too much data.

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE 
       FROM INFORMATION_SCHEMA.TABLES 
       WHERE TABLE_SCHEMA='mysql'\G
*************************** 1. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: column_stats
 ENGINE: InnoDB
*************************** 2. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: columns_priv
 ENGINE: InnoDB
*************************** 3. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: component
 ENGINE: InnoDB
*************************** 4. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: db
 ENGINE: InnoDB
*************************** 5. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: default_roles
 ENGINE: InnoDB
*************************** 6. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: engine_cost
 ENGINE: InnoDB
*************************** 7. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: func
 ENGINE: InnoDB
*************************** 8. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: general_log
 ENGINE: CSV
*************************** 9. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: gtid_executed
 ENGINE: InnoDB
*************************** 10. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: help_category
 ENGINE: InnoDB
*************************** 11. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: help_keyword
 ENGINE: InnoDB
*************************** 12. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: help_relation
 ENGINE: InnoDB
*************************** 13. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: help_topic
 ENGINE: InnoDB
*************************** 14. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: innodb_index_stats
 ENGINE: InnoDB
*************************** 15. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: innodb_table_stats
 ENGINE: InnoDB
*************************** 16. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: plugin
 ENGINE: InnoDB
*************************** 17. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: procs_priv
 ENGINE: InnoDB
*************************** 18. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: proxies_priv
 ENGINE: InnoDB
*************************** 19. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: role_edges
 ENGINE: InnoDB
*************************** 20. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: server_cost
 ENGINE: InnoDB
*************************** 21. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: servers
 ENGINE: InnoDB
*************************** 22. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: slave_master_info
 ENGINE: InnoDB
*************************** 23. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: slave_relay_log_info
 ENGINE: InnoDB
*************************** 24. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: slave_worker_info
 ENGINE: InnoDB
*************************** 25. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: slow_log
 ENGINE: CSV
*************************** 26. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: tables_priv
 ENGINE: InnoDB
*************************** 27. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: time_zone
 ENGINE: InnoDB
*************************** 28. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: time_zone_leap_second
 ENGINE: InnoDB
*************************** 29. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: time_zone_name
 ENGINE: InnoDB
*************************** 30. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: time_zone_transition
 ENGINE: InnoDB
*************************** 31. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: time_zone_transition_type
 ENGINE: InnoDB
*************************** 32. row ***************************
TABLE_SCHEMA: mysql
 TABLE_NAME: user
 ENGINE: InnoDB
32 rows in set (0,00 sec)

So, here, I presented three topics with reference of the new features coming with the new MySQL 8.0 DMR 1. I’m working on another post that will be released here within the coming days to show more new about that.

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.