New Data Dictionary:
An Internal Server API
That Matters
Alexander Nozdrin, Principle Software Developer
Copyright
Copyright©©2014,
2014,Oracle
Oracleand/or
and/oritsitsaffiliates.
affiliates.All
Allrights
rightsreserved.
reserved.|
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for
information purposes only, and may not be incorporated into any contract. It is not a
commitment to deliver any material, code, or functionality, and should not be relied upon
in making purchasing decisions. The development, release, and timing of any features or
functionality described for Oracle’s products remains at the sole discretion of Oracle.
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
2
MySQL Community Reception @ Oracle OpenWorld
Mingle with the MySQL community and the MySQL team from Oracle for
a fun and informative evening!
•
•
Time: September 30 (Tue) @ 7pm
Jillian’s at Metreon
175 Fourth Street, San Francisco, CA
At the corner of Howard and 4th st.; only 2-min walk from Moscone Center
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Agenda
1
What is a Data Dictionary?
2
The MySQL Traditional Data Dictionary
3
New Data Dictionary
4
Benefits for users
5
Q&A
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Data Dictionary
What is it
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Data Dictionary
Definition
• Metadata is information about user data
– User table structure
– Column definitions
– Index definitions
– Foreign key definitions
– Stored program definitions
...
• Data Dictionary collects all metadata in RDBMS
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Data Dictionary
Overview
CREATE TABLE customers(
id INT AUTO_INCREMENT
...
PRIMARY KEY (id),
INDEX ...
FOREIGN KEY ...
)
CREATE PROCEDURE p1(v INT)
SQL SECURITY INVOKER
BEGIN
...
END
Data Dictionary
Table Definitions
Time zones
SP Definitions
View Definitions
Plugins
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Privileges
Data Dictionary Definition
The ecosystem
MySQL Server
Query Executor
SQL
statement
Information
Schema
Parser
Client
Result
Optimizer
Data
Dictionary
SE
Performance
Schema
InnoDB
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
8
The MySQL Traditional Data Dictionary
MySQL 5.6 and earlier
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
The MySQL Traditional Data Dictionary
• A mix of files and tables:
– File based
• Tables: FRM
• Triggers: TRN, TRG
...
– Table based
• mysql.time_zone
...
• InnoDB has a separate data dictionary
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
The MySQL Traditional Data Dictionary
INFORMATION_SCHEMA
File
Scan
Table
Scan
Intern.
Access
Data Dictionary
Files
FRM
Archive
TRG
OPT
CSV
System tables (mysql.)
user
time_zone
proc
InnoDB internal data dictionary
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
InnoDB
11
The MySQL Traditional Data Dictionary
Problems
• Poor INFORMATION_SCHEMA performance
• Makes crash-safe / transactional DDL impossible
• Inconsistencies between files and tables
• Inconsistencies between DD in InnoDB and the server
• File-system dependency (lower-case-table-names)
• Makes replication of DDL statements difficult
• Too difficult to extend
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
12
New Data Dictionary
A great leap forward
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
New Data Dictionary : Main Features
• Stored in InnoDB tables
• Reliable & crash-safe
• Single repository of metadata
– for the MySQL server
– for Storage Engines
– for Plugins
• Redundancy
• INFORMATION_SCHEMA SQL VIEWs
– Queries can be optimized
– Improved performance
• Metadata versioning
• Extendable
– Simplify metadata upgrades
– Designed with plugins in mind
• Data Dictionary API
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
New Data Dictionary
INFORMATION_SCHEMA
Transition
File
Scan
Data Dictionary
Files
FRM
Archive
TRG
OPT
CSV
SQL
Table
VIEW
Scan
DD Table
System tables
(mysql.)
Intern.
Access
InnoDB internal dictionary
user
time_zone
proc
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
InnoDB
15
New Data Dictionary
Overview
INFORMATION
SCHEMA
Data Dictionary
InnoDB
Views
DD Table
User Table
Archive
User Table
CSV
User Table
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
16
New Data Dictionary
Architecture
Query Executor
Parser
Optimizer
Plugin
Plugin
Plugin
Data Dictionary External API
Data Dictionary Internal API
Data
Dictionary
Tablespace
Tablespace
InnoDB
User Table
Storage
Engine
Storage
Engine
Archive
Plugin
Plugin
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
17
New Data Dictionary
Data Dictionary tables
• WL#6379: Schema definitions for new DD
• InnoDB Data Dictionary Tablespace
• Designed with INFORMATION_SCHEMA in mind
• Ability to store SE-specific data
• Use PK / FK to ensure consistency
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
18
New Data Dictionary API
Design goals
• The only way to access Data Dictionary
– For the server core
– For Storage Engines
– For plugins
• Hard to misuse
• Internal API (non-stable) and external API (stable)
• Provide a way to handle SE specific data
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
19
New Data Dictionary
Redundancy
SDI : Serialized Dictionary Information
Data Dictionary
InnoDB
Privileges
Single User TS
General TS
System TS
User Table
User Table
User Table
User Table
Definition
SDI
SDI
SDI
Stored
Program
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
20
New Data Dictionary
FRM shipping for MySQL Cluster?
SELECT ... FROM t1
FRM
SDI
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
21
New Data Dictionary : Labs Release
• No FRM files
• New INFORMATION_SCHEMA
• Migrated to InnoDB:
– time zone tables
– help tables
– mysql.plugins
– mysql.servers
• http://labs.mysql.com
– Do NOT use it in production
– Install on a spare server
• MTR can be run
• Draft of Data Dictionary API
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
22
New Data Dictionary
Why does it matter for YOU?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
INFORMATION_SCHEMA performance improvements
Get per table size
Blog post by Shlomi Noach: http://tinyurl.com/y8cnj7o
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE,
SUM(DATA_LENGTH+INDEX_LENGTH) AS size,
SUM(INDEX_LENGTH) AS index_size
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'INFORMATION_SCHEMA') AND ENGINE IS NOT NULL
GROUP BY TABLE_SCHEMA, TABLE_NAME;
Version
Time
5.7.5-m15
0.38 sec
Labs Release
0.08 sec
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
24
Case: server crash
The problem
• There are some data files
• No / outdated backup
• FRM files lost
• How to use those data files?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
25
Case: server crash
Traditional Data Dictionary
New Data Dictionary
• “Move FRM files around”
• Self-descriptive tablespaces (SDI)
• CREATE TABLE t1 (...)
• Dedicated IMPORT statement
• ALTER TABLE t1
DISCARD TABLESPACE
• Goal: error-proof procedure
• ALTER TABLE t1
IMPORT TABLESPACE ...
• Easy to make mistakes
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
26
For Plugin Developers
• A way to access Data Dictionary
• Persistent Storage for plugins
– Store/restore custom data
• Plugins can extend INFORMATION_SCHEMA & PERFORMANCE_SCHEMA
– Add new tables
– Add new columns to existing tables
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
27
Data Dictionary
Summary
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Data Dictionary : Takeaways
• Fundamental component in RDBMS
• Critical for performance
• Critical for reliability
• Critical for scalability
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
29
Data Dictionary : Takeaways
The MySQL Traditional
Data Dictionary
New Data Dictionary
• Crash-safe InnoDB tables
• Mix of files and tables
• Single repository
• Server DD and InnoDB DD
• Inefficient INFORMATION_SCHEMA
• Difficult to extend
• INFORMATION_SCHEMA as VIEWs
• Designed to be extendable
• Aims for backward compatibility
• Huge reengineering
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Questions?
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
MySQL Community Reception @ Oracle OpenWorld
Mingle with the MySQL community and the MySQL team from Oracle for
a fun and informative evening!
•
•
Time: September 30 (Tue) @ 7pm
Jillian’s at Metreon
175 Fourth Street, San Francisco, CA
At the corner of Howard and 4th st.; only 2-min walk from Moscone Center
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Oracle University MySQL Training Services
Prepare Your Organization to Enable Reliable and High-Performance Web-Based Database Applications
Benefits
RECENTLY RELEASED
ALL NEW! MySQL Cluster Training
To Register your interest to influence the
schedule on this newly released course – go to
education.oracle.com/mysql and click on the
MySQL Cluster Course
“Training and team skill have the most significant impact
on overall performance of technology and success of
technology projects.” - IDC, 2013
Expert-led training to support your MySQL learning needs
 Flexibility to train in the classroom or online
 Hands-on experience to gain real world experience
 Key skills needed for database administrators and developers

Top Courses for Administrators and Developers
• MySQL for Beginners
 MySQL for Database Administrators
 MySQL Performance Tuning
 MySQL Cluster – NEW - Register Your Interest!
 MySQL and PHP - Developing Dynamic Web Applications
 MySQL for Developers
 MySQL Developer Techniques
Top Certifications
Premier Support customers eligible to
save 20% on learning credits.
 MySQL 5.6 Database Administrator
 MySQL 5.6 Developer
To find out more about available MySQL Training & Certification
offerings, go to: education.oracle.com/mysql
Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |
Thank You!
Copyright
Copyright©©2014,
2014,Oracle
Oracleand/or
and/oritsitsaffiliates.
affiliates.All
Allrights
rightsreserved.
reserved.|