oracle mysql documentstore - · pdf filephp / java /.net /perl / ... mysql 5.7 sysbench...
TRANSCRIPT
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Oracle MySQL DocumentStoreSQL, NoSQL and JSON …
Delivering the best of both worlds with MySQL
Carsten ThalheimerSales Consultant MySQL [email protected]
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Basics of Business AdministrationSomething went wrong…
2
Let‘s assume we turn back time to end of the 19th century, what is one of the major business in the aero of the US East cost?
Ice trading (1894)
- U.S. ice trade employed an estimated 94,000 people- Renenue 1,5 Billion$+, compared with today [$ in 2015] - In 1903, the business reduced to <8% of 1899
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Mobile Network Supporting Over 800 Million Subscribers
1.7 Billion Active Users100 TB of User Data for PayPal
IDs Processed for 1 Billion Citizens
850 Million Candy Crush Game Plays/Day
2 Billion Events/Day for Booking.com
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Why MySQL …
• Part of the open source LAMP stack
• Millions of product installations
• Commercial Offerings (Enterprise)
• 20+ Years available; 10+ Years InnoDB and 5+ Years MySQL within Oracle!
• An estimated 70% of Oracle customers also use MySQL
Oracle Confidential – Internal/Restricted/Highly Restricted 4
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Source: http://db-engines.com/en/ranking(Status: July 2017)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL Community EditionServer runs on MS Windows, Linux, Solaris and other OSesSupport for common
developmentPHP / Java /.NET /Perl / Python /ODBC /Ruby
Clients and Applications
InnoDB Storage Engine
mysqld process
NoSQL
Simple access patterns
Compromise on consistency for performance
Ad-hoc data format
Simple operation
SQL
Complex queries with joins
ACID transactions
Well defined schemas
Rich set of tools
Replication and Clustering
1. SQL for rich queries, fully transactional
2. Key-value access to InnoDBvia Memcached API
3. MySQL a Documentstore
Flexible Storage Engine InnoDB Default!
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
M S
S
S
S
M
write clients read clientsread clients
write clients
Read scale-out / HA / Backup / Reporting / Geo redundancy
What is Replication Used For?
Tuesday, October 20, 2015 Oracle Confidential – Restricted 7
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL - A Relational database
8
like Microsoft SQL , DB2, Sybase, Postgres …
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL InnoDBhttp://jfg-mysql.blogspot.ch/2017/07/how-far-with-mysql-mariadb.html
• ACID compliant and fully transactional with ROLLBACK/COMMIT/Foreign Keys
• Build in Clustering available
• A typical MySQL database is normally in the three digit GB range up 1-2 TB
• Theoretical limit of the maximum tablespace size is 64TB.
• It can serve more than 1000 columns per table and can
• Contain a maximum of 64 secondary indexes.
• A row can have a size of up to 64Kb (less BLOBs).
• InnoDB supports encryption and compression.
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.7 Sysbench Benchmark: OLTP Read Write1.5x Faster than MySQL 5.63x Faster than MySQL 5.5
0
100'000
200'000
300'000
400'000
500'000
600'000
700'000
8 16 32 64 128 256 512 1'024
Qu
eri
es
pe
r Se
con
d
Connections
MySQL 5.7: Sysbench OLTP Read Write
MySQL 5.7
MySQL 5.6
MySQL 5.5
Intel(R) Xeon(R) CPU E7-8890 v34 sockets x 18 cores-HT (144 CPU threads)2.5 Ghz, 512GB RAMLinux kernel 3.16
10
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
82% Faster than MySQL 5.6
0
20'000
40'000
60'000
80'000
100'000
120'000
8 16 32 64 128 256 512 1'024
Co
nn
ect
/ s
ec
Connections
MySQL 5.7: Sysbench OLTP Read Only
MySQL 5.7
MySQL 5.6
MySQL 5.5
Intel(R) Xeon(R) CPU E7-8890 v34 sockets x 18 cores-HT (144 CPU threads)2.5 Ghz, 512GB RAMLinux kernel 3.16
100K Connect / Sec
MySQL 5.7 Sysbench Benchmark: Connection Requests
11
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL - A Relational database
Example
12
like Microsoft SQL , DB2, Sybase, Postgres …
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL - A Relational database MySQL
MySQL - A Key Value Store DB
13
like Microsoft SQL , DB2, Sybase, Postgres …
a bit like Redis …
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL : InnoDB, NoSQL (Memcached)
1 Million QPS
14
Intel(R) Xeon(R) CPU E7-4860 x86_644 sockets x 10 cores-HT (80 CPU threads)2.3 GHz, 512 GB RAMOracle Linux 6.5
Same app can leverage:
▪ Key-value access to InnoDB
via Memcached API
▪ SQL for rich queries, fully transactional
0
500'000
1'000'000
1'500'000
2'000'000
8 16 32 64 128 256 512 1'024
Qu
eri
es
pe
r Se
con
d
Connections
MySQL 5.7: Sysbench OLTP Read Only (SQL Point Selects)
MySQL5.7
MySQL5.6
MySQL5.5
1,600,000 QPS
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | Copyright © 2015, Oracle and/or its affiliates. All rights reserved. 15
Key value storeInnoDB & Memcached - Configured Schema
prefix key values
<@@town.Berlin,DE|3.8>
key value
Name db_table Key-col Val-cols
town cities town code,population
Config tables
town ... code population
Berlin ... DE 3.8
cities
Application view
SQL view
<@@town.Berlin,DE|3.8>
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL - A Relational database MySQL
MySQL - A Key Value Store DB
Example
16
like Microsoft SQL , DB2, Sybase, Postgres …
a bit like Redis …
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL - A Relational database MySQL
MySQL - A Key Value Store DB
MySQL 5.7 (ff) - A DocumentStore
17
like Microsoft SQL , DB2, Sybase, Postgres …
a bit like Redis …
a bit like MongoDB …
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Document Oriented Databases
• An object that can represent structured data
• Structure is implicit in the document; usually no external/central schema
• JSON (=JavaScript Object Notation)
– Compact, popular and standardized
– Can be represented natively in many languages (JavaScript, Python, etc.)
• Other popular encoding formats are XML, YAML etc
What is a Document?
18
A JSON Document:
{"_id": "AUT","Name": "Austria","GNP": 211860,"IndepYear": 1918,"demographics": {
"LifeExpectancy": 77.699,"Population": 8091800
},"geography": {
"Continent": "Europe","Region": "Western Europe","SurfaceArea": 83859
},"government": {
"GovernmentForm": "Federal Republic","HeadOfState": "Van der Bellen"
}}
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Document Oriented Databases
• Schemaless: No centralized database schema
– Data model enforcement and validation (if any) at application layer
– Simpler schema updates (no ALTER TABLE penalty)
• NoSQL APIs: Simpler programming interfaces
– No specialized language for queries and data manipulation
– Complex queries handled at application layer (no complex SELECTs, JOINs)
– Document in, document out, manipulations at client side
• Scalability, but some drawbacks:
– Limited database features (no foreign keys, no transactions, etc.)
– Weak consistency guarantees
19
Usability & Scalability
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Why not…
• Have both schema-less and schema in the same technology stack?
• One that checks all the boxes of all stakeholders:
20
Developers:[ x ] Schemaless or/and Schema[ x ] Rapid Prototyping/Simpler APIs[ x ] Document Model[ x ] Transactions
Operations:[ x ] Performance Management/Visibility[ x ] Robust Replication, Backup, Restore[ x ] Comprehensive Tooling Ecosystem[ x ] Simpler application schema upgrades
Business Owner:[ x ] Don’t lose my data = ACID transactions[ x ] Capture all my data = Extensible/Schemaless[ x ] Products On Schedule/Time to Market = Rapid Development
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
JSON features in MySQL 5.7 [Oct. 15]
• Native JSON data type
➢ Native internal binary format for efficient processing & storage. Up to 10x faster than storing as text
• Built-in JSON functions
➢Allowing you to efficiently store, search, update, and manipulate Documents
• Indexing of Documents using Generated Columns ➢Automatically uses the best “functional” index available for even faster results
• JSON Comparator & New inline syntax for easy SQL integration
➢Allows for easy integration of Document data within your SQL queries
Introduced in MySQL 5.7
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
The JSON Type
22
CREATE TABLE employees (data JSON);
INSERT INTO employees VALUES ('{"id": 1, "name": "Jane"}');
INSERT INTO employees VALUES ('{"id": 2, "name": "Joe"}');
SELECT * FROM employees;
+---------------------------+
| data |
+---------------------------+
| {"id": 1, "name": "Jane"} |
| {"id": 2, "name": "Joe"} |
+---------------------------+
2 rows in set (0,00 sec)
Introduced in MySQL 5.7
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
JSON Object Creation
23
SELECT JSON_OBJECT('id', id,
'street', feature->"$.properties.STREET",
'type', feature->"$.type"
) AS json_object
FROM features ORDER BY RAND() LIMIT 3;
+--------------------------------------------------------+
| json_object |
+--------------------------------------------------------+
| {"id": 122976, "type": "Feature", "street": "RAUSCH"} |
| {"id": 148698, "type": "Feature", "street": "WALLACE"} |
| {"id": 45214, "type": "Feature", "street": "HAIGHT"} |
+--------------------------------------------------------+
3 rows in set (3.11 sec)
Introduced in MySQL 5.7
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Native Performance Comparison
24
# as JSON typeSELECT DISTINCTfeature->"$.type" as json_extractFROM features;+--------------+| json_extract |+--------------+| "Feature" |+--------------+1 row in set (1.25 sec)
Unindexed traversal of 206K documents
# as TEXT typeSELECT DISTINCT feature->"$.type" as json_extractFROM features;+--------------+| json_extract |+--------------+| "Feature" |+--------------+ 1 row in set (12.85 sec)
Explanation: Binary format of JSON type is very efficient at searching. Storing as TEXT performs over 10x worse at traversal.
Using short cut for JSON_EXTRACT.
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Generated Columns (cont.)
• Used for “functional index”• ALTER TABLE features ADD feature_type VARCHAR(30) AS (feature->"$.type") ;
• Query OK, 0 rows affected (0.01 sec)
• Records: 0 Duplicates: 0 Warnings: 0
• ALTER TABLE features ADD INDEX (feature_type);
• Query OK, 0 rows affected (0.73 sec)
• Records: 0 Duplicates: 0 Warnings: 0
• Both types of computed columns permit for indexes to be added.
25
Introduced in MySQL 5.7
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Virtual vs. Stored Performance
• Approximate worst case scenario via a table scan:
26
SELECT DISTINCT feature_type FROM
features;
+--------------+
| feature_type |
+--------------+
| "Feature" |
+--------------+
VIRTUAL-TEXT (9.89 sec)STORED-TEXT (0.22 sec)VIRTUAL-JSON (0.85 sec)STORED-JSON (0.24 sec)
Clarification: Since indexes are materialized (stored) themselves, the real-life case for STORED is when generating the column is computationally expensive and you can not use indexes effectively.
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
What is the MySQL Document Store?
"An easy, straight forward way to work with JSON documents in MySQL"
27
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
What is the MySQL Document Store?
"An easy, straight forward way to work with JSON documents in MySQL“
SELECT JSON_OBJECT(
'name', JSON_EXTRACT(doc,'$.name'),
'zip', JSON_EXTRACT(doc, '$.address.zip'))
FROM `order`
WHERE (JSON_UNQUOTE(JSON_EXTRACT(doc,'$.address.zip')) IN
('91234','94231'));
order.find("address.zip in ('91234', '94231')").
patchFields({'name':'name', 'zip':'address.zip'});
28
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL Document Store
• Native JSON Documents in MySQL 5.7
– Schema-less Document Storage
• MySQL Shell
– Javascript, Python, SQL modes
• X Protocol – Implemented by X Plugin to Extend MySQL Server as a Document Store
• X Dev API
– SQL and Document CRUD Operations
– Implemented in Connector/Node.js, Connector/J, Connector/Net
Introduced in MySQL 5.7.12
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Architecture
30
MySQL
Plugins
X Protocol Plugin Memcached PluginCore
X ProtocolStd Protocol
X Protocol33060
Std Protocol3306
SQL API CRUD API
X and StdProtocols
MySQLShell
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL Shell [5.7.12+]
31
A single unified client for all administrative and operations tasks
• Multi-Language: JavaScript, Python, and SQL
– Naturally scriptable
• Supports both Document and Relational models
• Exposes full Development and Admin API
”MySQL Shell provides the developer and DBA with a single intuitive, flexible, and powerful interface for all MySQL related tasks!”
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 32
tomas@localhost $ mysqlsh --uri root@localhost/test
Creating an X Session to root@localhost:33060/test
Enter password:
Default schema `test` accessible through db.
…
Currently in JavaScript mode. Use \sql to switch to SQL mode and execute
queries.
mysql-js> db.createCollection("posts");
<Collection:posts>
mysql-js> db.posts.add({"title":"Hello World", "text":"First post!"})
Query OK, 1 item affected (0.03 sec)
mysql-js> db.posts.find("title = 'Hello World'").sort(["title"]);
[
{
"_id": "8202bda28206e611140b3229389b6526",
"text": "First post!",
"title": "Hello World"
}
]
1 document in set (0.01 sec)
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 33
Collections are tables
Tables with:- JSON column- Generated Column
Create a Collection
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
How does the Document Store work?Architecture - Components
34
Application Connector MySQLX Plugin
DevAPI Protobuf / X Protocol TCP/IP SQL
InnoDB
- Commands serialized into Protobuf messages on the client side- Transported via new "X Protocol" to the server- Collections are stored as InnoDB tables
ACID compliance, transactions, replication, row locking etc all work as in plain MySQL
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
What is the MySQL Document Store [extended]?
• Starting from MySQL 5.7
– JSON SQL Datatype + JSON SQL functions
• But it is much more:
– Scale-Out - A way to prepare applications for massive scale-out– First step to an out-of-the-box sharding solution
– Ease-of-Use - A new approach for designing and writing MySQL database apps– A new querying interface called DevAPI, based on CRUD
– db.products.find(“name like :n”).bind(“n”, searchString).execute().fetch_all();
– Fast prototyping
– No schema change headaches
35
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Scaling MySQL – What is available today?
• Vertical Scaling (scaling a single machine instance) – Available Today
– 1M QPS
– Multi-TB databases
• Read Scale-Out – Available Today– Already solved since more
than 10 years
– Big companies run hundreds or thousands of async/ssync read slaves
36
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL - A Relational database MySQL
MySQL - A Key Value Store DB
MySQL 5.7 (ff) - A DocumentStore
37
like Microsoft SQL , DB2, Sybase, Postgres …
a bit like Redis …
a bit like MongoDB …
???
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
What’s next?
Copyright © 2017, Oracle and/or its affiliates. All rights reserved.
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
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.
Die folgenden Informationen sind zur Skizzierung der grundsätzlichen Produktausrichtung von Oracle bestimmt. Sie sind nur für reine Informationszwecke gedacht und dürfen nicht in einen Vertrag aufgenommen werden. Es besteht keine Verpflichtung Material, Code oder Funktionalitäten zu liefern und die Informationen dürfen nicht als Grundlage für Einkaufsentscheidungen herangezogen werden.
Die Entwicklung, Produktplanung und die zeitliche Koordinierung von Produkteigenschaften oder Funktionalitäten bleibt in der alleinigen Verantwortung von Oracle.
Safe Harbour Statement
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
Scaling MySQL – Write Scale-Out
• Myth: Relational databases don't scale for big data
• Truth: Build your database using document model principles, and a RDBMS will scale as well!
• Relationally designed databases are hard to scale horizontally (shard)
• Foreign keys, transactional semantics, JOINs, strong global consistency, etc. … make it difficult to partition the data across servers
• MySQL Document Store will make it easy to build big scale databases• Applications and database are designed in a way to simplify sharding
• Certain features are avoided (or used carefully)
40
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 41
Read Scale-Out
Async Replication + Auto Failover
Write Scale-Out
Sharding
S1
S2
S3
S4
MySQL Vision – 4 Steps
Timeline
MySQL Document Store
Relational & Document Model
MySQL HA
Out-Of-Box HA
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL
InnoDBcluster
MySQL InnoDB Cluster – Architecture - S2
M
M M
MySQL Connector
Application
MySQL Router
MySQL Connector
Application
MySQL Router
MySQL Shell
HA
Group Replication
: dba.deploySandboxInstance(x): dba.deploySandboxInstance(xx): dba.deploySandboxInstance(xxx): \connect root@localhost:x: mc = dba.createCluster("mycluster"): mc.addInstance("root@localhost:xx"): mc.addInstance("root@localhost:xxx"): dba.configureLocalInstance("root@localhost:x"): dba.configureLocalInstance("root@localhost:xx"): dba.configureLocalInstance("root@localhost:xxx")
: sudo mysqlrouter --bootstrap localhost:x --user=root: sudo mysqlrouter&
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
S1 S2 S3 S4 S…
M
M M
MySQL Connector
Application
MySQL Router
MySQL Connector
Application
MySQL Router
MySQL Shell
HA
MySQL InnoDB Cluster – Architecture - S3 MySQL
InnoDBcluster
Read-Only Slaves
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
S1 S2 S3 S4 S…
M
M M
MySQL Connector
Application
MySQL Router
MySQL Connector
Application
MySQL Router
MySQL Shell
HA
Rep
licaS
et (
Shar
d 1
)
S1 S2 S3 S4 S…
M
M M
MySQL Connector
Application
MySQL Router
HA
Rep
licaS
et (
Shar
d 2
)
S1 S2 S3 S4
M
M M
HA
Rep
licaS
et (
Shar
d 3
)
MySQL Connector
Application
MySQL Router
MySQL InnoDB Cluster – Architecture - S4MySQL
InnoDBcluster
…
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. |
MySQL 8.0 (DMR), a new star is born…When?
• MySQL 8.0: Native Data Dictionary
– No FRM/DB.OPT/TRG/TRN/PAR - MyISAM not required
• User Management DDLs Atomic ( 100% InnoDB)
• Security Roles
• Support for the latest Unicode 9.0, UTF8MB4 as the default character set
• Persist Configuration and track configuration changes
• Performance Schema Indexes
– Implementation of indexes tricks the optimizer into better execution plan
Beta
Copyright © 2017, Oracle and/or its affiliates. All rights reserved. | 46
Carsten Thalheimer
Sales Consultant MySQL GBU([email protected])
Learn More
MySQL Bootcamps (Existing Oracle Partners only) Free MySQL Workshop eDelivery.oracle.co