{"id":1200,"date":"2016-09-12T13:09:58","date_gmt":"2016-09-12T16:09:58","guid":{"rendered":"http:\/\/wagnerbianchi.com\/blog\/?p=1200"},"modified":"2016-09-12T13:09:58","modified_gmt":"2016-09-12T16:09:58","slug":"mysql-8-0-dmr-new-features-part-1","status":"publish","type":"post","link":"http:\/\/wagnerbianchi.com\/blog\/?p=1200","title":{"rendered":"MySQL 8.0 DMR, new features, part 1"},"content":{"rendered":"

I would like to start this telling the reader that this is going to be the first of some blog posts I\u2019m planning to exploit subjects around MySQL 8.0, as I have been testing its features. As I\u2019m 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\u2019ve got pretty excited with many of the coming features and changes for existing features available on 5.6\/5.7 and I\u2019m 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\u2019m using for this and other blog posts related to MySQL 8.0 is CentOS 7 with Kernel\u00a03.10.0-229.el7.x86_64.<\/span><\/p>\n

Current status of mysql.service:<\/span><\/p>\n

[root@mysql80drm1 vagrant]# systemctl status mysqld.service
\n\u25cf mysqld.service - MySQL Server
\nLoaded: loaded (\/usr\/lib\/systemd\/system\/mysqld.service; enabled; vendor preset: disabled)
\nActive: active (running) since Sun 2016-08-28 01:51:51 CEST; 2s ago
\nProcess: 16304 ExecStart=\/usr\/sbin\/mysqld --daemonize --pid-file=\/var\/run\/mysqld\/mysqld.pid $MYSQLD_OPTS (code=exited, status=0\/SUCCESS)
\nProcess: 16229 ExecStartPre=\/usr\/bin\/mysqld_pre_systemd (code=exited, status=0\/SUCCESS)
\nMain PID: 16307 (mysqld)
\nCGroup: \/system.slice\/mysqld.service
\n\u2514\u250016307 \/usr\/sbin\/mysqld --daemonize --pid-file=\/var\/run\/mysqld\/mysqld.pid<\/code>
\nAug 28 01:51:46 mysql80drm1 systemd[1]: Starting MySQL Server...
\nAug 28 01:51:51 mysql80drm1 systemd[1]: Started MySQL Server.<\/code><\/p>\n

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.<\/span><\/p>\n

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

[root@mysql80drm1 vagrant]# mysql -u root -p
\nEnter password:
\nWelcome to the MySQL monitor. Commands end with ; or \\g.
\nYour MySQL connection id is 5
\nServer version: 8.0.0-dmr<\/code><\/p>\n

Copyright (c) 2000, 2016, Oracle and\/or its affiliates. All rights reserved.<\/code><\/pre>\n
Oracle is a registered trademark of Oracle Corporation and\/or its\r\n affiliates. Other names may be trademarks of their respective\r\n owners.<\/pre>\n
Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.<\/pre>\n
mysql> ALTER USER root@localhost IDENTIFIED BY 'P@ssw0rd';\r\n Query OK, 0 rows affected (0.00 sec)<\/pre>\n
mysql> \\q\r\n Bye<\/pre>\n
MySQL 8.0 DMR 1 – Transaction Data Dictionary<\/b><\/span><\/p>\n
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, \u201cthe FRM, TRG, PAR files are gone\u201d. <\/span><\/p>\n
mysql> create database wb;\r\n Query OK, 1 row affected (0.00 sec)<\/code><\/pre>\n
mysql> use wb;\r\n Database changed<\/pre>\n
mysql> \\! ls -lh \/var\/lib\/mysql\/wb\r\n total 144K\r\n -rw-r----- 1 mysql mysql 144K Sep 11 02:07 t1.ibd<\/pre>\n
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\u2019s data dictionary appeared (http:\/\/www.technocation.org\/content\/how-tell-when-using-informationschema-might-crash-your-database-0<\/span><\/a>). <\/span><\/p>\n
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\u2019s going to improve the speed and make it stable when querying tables. Yet, about the new Data Dictionary, it\u2019s 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,\u00a0http:\/\/mysqlserverteam.com\/a-new-data-dictionary-for-mysql\/<\/span><\/a>. Morgan has written about the FRM files going away some time ago,\u00a0http:\/\/www.tocker.ca\/2014\/07\/30\/beyond-the-frm-ideas-for-a-native-mysql-data-dictionary.html<\/span><\/a><\/span><\/p>\n
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:<\/span><\/p>\n
\"\"
New Data Dictionary<\/p><\/div>\n
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<\/a> 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.\u00a0MySQL 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<\/em>” characters are encoded. You can check more about the assumptions about the schema definitions names clicking here<\/a>, WL#6379. Yet on the same link, once can see the new tables’ definition.<\/p>\n
We can think that, when one need to alter a column data type or even rebuild a table, data dictionary should be \u00a0accessible 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.<\/p>\n
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.<\/p>\n
MySQL 8.0 DMR 1 – Invisible Indexes<\/b><\/p>\n
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 \u00a0for each row:<\/p>\n
mysql> show tables;\r\n+--------------+\r\n| Tables_in_wb |\r\n+--------------+\r\n| t1 |\r\n+--------------+\r\n1 row in set (0.00 sec)<\/pre>\n
mysql> show create table t1;\r\n+-------+----------------------------------------------------------+\r\n| Table | Create Table |\r\n+-------+----------------------------------------------------------+\r\n| t1 | CREATE TABLE `t1` (\r\n `i` int(11) DEFAULT NULL,\r\n KEY `i` (`i`)\r\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 |\r\n+-------+----------------------------------------------------------+\r\n1 row in set (0.00 sec)\r\nmysql> explain select i from t1 use index(i)\\G\r\n*************************** 1. row ***************************\r\n id: 1\r\n select_type: SIMPLE\r\n table: t1\r\n partitions: NULL\r\n type: index\r\npossible_keys: NULL\r\n key: i\r\n key_len: 5\r\n ref: NULL\r\n rows: 1\r\n filtered: 100.00\r\n Extra: Using index\r\n1 row in set, 1 warning (0.00 sec)<\/pre>\n
mysql> show index from t1\\G\r\n*************************** 1. row ***************************\r\n Table: t1\r\n Non_unique: 1\r\n Key_name: i\r\n Seq_in_index: 1\r\n Column_name: i\r\n Collation: A\r\n Cardinality: NULL\r\n Sub_part: NULL\r\n Packed: NULL\r\n Null: YES\r\n Index_type: BTREE\r\n Comment:\r\nIndex_comment:\r\n Visible: YES<\/strong>\r\n1 row in set (0.01 sec)<\/pre>\n
We can make the above index invisible:<\/p>\n
mysql> alter table t1 alter index i invisible;\r\nQuery OK, 0 rows affected (0.02 sec)\r\nRecords: 0 Duplicates: 0 Warnings: 0<\/pre>\n
mysql> show index from t1\\G\r\n*************************** 1. row ***************************\r\n Table: t1\r\n Non_unique: 1\r\n Key_name: i\r\n Seq_in_index: 1\r\n Column_name: i\r\n Collation: A\r\n Cardinality: NULL\r\n Sub_part: NULL\r\n Packed: NULL\r\n Null: YES\r\n Index_type: BTREE\r\n Comment:\r\nIndex_comment:\r\n Visible: NO<\/strong>\r\n1 row in set (0.01 sec)\r\n\r\nmysql> explain select i from t1 use index(i)\\G\r\n*************************** 1. row ***************************\r\n id: 1\r\n select_type: SIMPLE\r\n table: t1\r\n partitions: NULL\r\n type: ALL\r\npossible_keys: NULL\r\n key: NULL\r\n key_len: NULL\r\n ref: NULL\r\n rows: 1\r\n filtered: 100.00\r\n Extra: NULL\r\n1 row in set, 1 warning (0.00 sec)<\/pre>\n
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.<\/p>\n
MySQL 8.0 DMR 1 – MySQL System Database now in InnoDB<\/b><\/p>\n
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.<\/p>\n
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE \r\n       FROM INFORMATION_SCHEMA.TABLES \r\n       WHERE TABLE_SCHEMA='mysql'\\G\r\n*************************** 1. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: column_stats\r\n ENGINE: InnoDB\r\n*************************** 2. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: columns_priv\r\n ENGINE: InnoDB\r\n*************************** 3. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: component\r\n ENGINE: InnoDB\r\n*************************** 4. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: db\r\n ENGINE: InnoDB\r\n*************************** 5. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: default_roles\r\n ENGINE: InnoDB\r\n*************************** 6. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: engine_cost\r\n ENGINE: InnoDB\r\n*************************** 7. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: func\r\n ENGINE: InnoDB\r\n*************************** 8. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: general_log\r\n ENGINE: CSV\r\n*************************** 9. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: gtid_executed\r\n ENGINE: InnoDB\r\n*************************** 10. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: help_category\r\n ENGINE: InnoDB\r\n*************************** 11. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: help_keyword\r\n ENGINE: InnoDB\r\n*************************** 12. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: help_relation\r\n ENGINE: InnoDB\r\n*************************** 13. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: help_topic\r\n ENGINE: InnoDB\r\n*************************** 14. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: innodb_index_stats\r\n ENGINE: InnoDB\r\n*************************** 15. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: innodb_table_stats\r\n ENGINE: InnoDB\r\n*************************** 16. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: plugin\r\n ENGINE: InnoDB\r\n*************************** 17. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: procs_priv\r\n ENGINE: InnoDB\r\n*************************** 18. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: proxies_priv\r\n ENGINE: InnoDB\r\n*************************** 19. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: role_edges\r\n ENGINE: InnoDB\r\n*************************** 20. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: server_cost\r\n ENGINE: InnoDB\r\n*************************** 21. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: servers\r\n ENGINE: InnoDB\r\n*************************** 22. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: slave_master_info\r\n ENGINE: InnoDB\r\n*************************** 23. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: slave_relay_log_info\r\n ENGINE: InnoDB\r\n*************************** 24. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: slave_worker_info\r\n ENGINE: InnoDB\r\n*************************** 25. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: slow_log\r\n ENGINE: CSV\r\n*************************** 26. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: tables_priv\r\n ENGINE: InnoDB\r\n*************************** 27. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: time_zone\r\n ENGINE: InnoDB\r\n*************************** 28. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: time_zone_leap_second\r\n ENGINE: InnoDB\r\n*************************** 29. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: time_zone_name\r\n ENGINE: InnoDB\r\n*************************** 30. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: time_zone_transition\r\n ENGINE: InnoDB\r\n*************************** 31. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: time_zone_transition_type\r\n ENGINE: InnoDB\r\n*************************** 32. row ***************************\r\nTABLE_SCHEMA: mysql\r\n TABLE_NAME: user\r\n ENGINE: InnoDB\r\n32 rows in set (0,00 sec)<\/pre>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
I would like to start this telling the reader that this is going to be the first of some blog posts I\u2019m planning to exploit subjects around MySQL 8.0, as I have been testing its features. As I\u2019m an Oracle ACE Director, part of the Oracle ACEs program, I received from my friend Fred Deschamps, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1200"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1200"}],"version-history":[{"count":22,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1200\/revisions"}],"predecessor-version":[{"id":1222,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/1200\/revisions\/1222"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1200"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1200"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1200"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}