Download - Thu 1100 duncan_john_color
![Page 1: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/1.jpg)
Copyright © 2011 Oracle Corp. 1
![Page 2: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/2.jpg)
<Insert Picture Here>
MySQL Cluster with and without SQLJohn David DuncanSenior Software Engineer, Oracle Corp.
2
![Page 3: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/3.jpg)
<Insert Picture Here>
Program Agenda
•Overview of MySQL Cluster•SQL interface•NoSQL interfaces–mod_ndb–ClusterJ–Memcache
3
![Page 4: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/4.jpg)
Copyright © 2011 Oracle Corp.
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.
4
![Page 5: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/5.jpg)
Copyright © 2011 Oracle Corp.
MySQL Cluster OverviewACID Compliant Relational Database
SQL & NoSQL interfaces
Write-Scalable & Real-TimeDistributed, auto-partitioning (sharding), multi-master
99.999% Availability Shared-nothing, integrated clustering & sub-second recovery, local & geographic replication, on-line operations
Low TCOOpen-source, management & monitoring tools, scale-out on commodity hardware
5
![Page 6: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/6.jpg)
Copyright © 2011 Oracle Corp.
NDB Data Nodes
API Nodes
Node Group 1 Node Group 2
The Basics
6
![Page 7: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/7.jpg)
Copyright © 2011 Oracle Corp.
Geographic Replication
Cluster 1
SynchronousreplicationCluster 2
MyISAM MyISAM InnoDB
Asynchronousreplication
7
![Page 8: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/8.jpg)
Copyright © 2011 Oracle Corp.
MySQL Cluster – Users & ApplicationsHA, Transactional Services: Web & Telecoms
http://www.mysql.com/customers/cluster/
• Web• User profile management• Session stores• eCommerce• On-Line Gaming• Application Servers
• Telecoms• Subscriber Databases (HLR/HSS)• Service Delivery Platforms• VoIP, IPTV & VoD• Mobile Content Delivery• On-Line app stores and portals• IP Management• Payment Gateways
8
![Page 9: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/9.jpg)
Copyright © 2011 Oracle Corp.
<Insert Picture Here>
Node Group 1 Node Group 2
SQL interface
9
![Page 10: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/10.jpg)
The long road to SQL JOIN performance
• Initial integration (2004)– Move the data to the query– ... one row at a time– ... one loop iteration in a nested-loop join = 1 network trip
• 2005 - 2009 – Improve efficiency ...– within the existing framework of the MySQL optimizer– e.g. Batch Key Access, push-down filters
• MySQL Cluster 7.2 (2011)– Adaptive Query Localization– Algorithmic changes to optimizer– Push the majority of the query to the data nodes
10
![Page 11: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/11.jpg)
Copyright © 2011 Oracle Corp.
Results: Adaptive Query Localization in MySQL Cluster 7.2
Before: 48.68 sec
mysql> SELECT COUNT(*) FROM residents, postcodes, towns WHERE residents.postcode=postcodes.postcode AND postcodes.town=towns.town AND towns.county="Berkshire";
+----------+ | COUNT(*) | +----------+ | 40001 | +----------+
• After: 2.02 sec
11
![Page 12: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/12.jpg)
Copyright © 2011 Oracle Corp.
<Insert Picture Here>NoSQL interfaces
Node Group 1 Node Group 2
mod_ndb
ClusterJ
Memcache
12
![Page 13: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/13.jpg)
Copyright © 2011 Oracle Corp.
Node Group 1
Node Group 2
SQL and NoSQL Together
Schema creation
Reporting
Analytics
Real-Time Operations
13
![Page 14: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/14.jpg)
Copyright © 2011 Oracle Corp.
<Insert Picture Here>mod_ndb
14
![Page 15: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/15.jpg)
Copyright © 2011 Oracle Corp.
DataNode
Apache
NDB API
15
DataNode
mod_ndb
http://mod-ndb.googlecode.com/
• First released March 2007• Use REST and JSON to manage data
stored in MySQL Cluster• Open source project based on C++
NDBAPI
![Page 16: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/16.jpg)
Copyright © 2011 Oracle Corp.
DataNode
httpd.conf
<Location /ndb/app/car> SELECT * from cars WHERE PRIMARY KEY = $id; Format JSON</Location>
Apache
NDB API
16
DataNode
mod_ndb
![Page 17: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/17.jpg)
Copyright © 2011 Oracle Corp.
GET /ndb/app/car?id=371 HTTP/1.1Client
17
DataNode
Apache
NDB API
DataNode
mod_ndb
![Page 18: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/18.jpg)
Copyright © 2011 Oracle Corp.
HTTP/1.1 200 OKETag: 700847d8b5b0901a2f2451efc4c4Content-type: application/jsonContent-length: 90
{ "car_id" : 371, "tag" : "807AHC", "state" : "OR", "make" : "Dodge", "year" : 1984}
Client
18
GET /ndb/app/car?id=371 HTTP/1.1
DataNode
Apache
NDB API
DataNode
mod_ndb
![Page 19: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/19.jpg)
Copyright © 2011 Oracle Corp.
my_car = JSON.parse(reply);
reply = XMLHttpRequest.get();
19
Browser
The view from Javascript
DataNode
Apache
NDB API
DataNode
![Page 20: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/20.jpg)
mod_ndb
http://mod-ndb.googlecode.com/
• Very small user community• Not officially supported by a vendor• Not as widely functional as Cluster/J• Not as fast as NDB+Memcache• Moves an unusually large portion of application logic into httpd.conf• Could use a major upgrade (maybe later this year?)
20
![Page 21: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/21.jpg)
Copyright © 2011 Oracle Corp.
<Insert Picture Here>ClusterJ
21
![Page 22: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/22.jpg)
ClusterJ
• High Performance, Light Weight, Easy to Use Direct Connection– In the style of Hibernate / JPA / JDO
• Shared Data storage with:– MySQL server– Native C++ applications– Other ClusterJ applications
• Domain Object Model DataMapper pattern– Data is represented as domain objects– Domain objects are separate from business logic– Domain objects are mapped to database tables
• Does not support relationships – Look at JDO / JPA for these modeling patterns
22
![Page 23: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/23.jpg)
Copyright © 2011 Oracle Corp.
Domain Object Model Mapping
• Tables map to Persistent Interfaces / Classes• Columns map to Persistent Properties
– column names default to property name• Rows map to Persistent Instances• Annotations on Interfaces / Classes customize
mappings• User chooses to write:
– User interface (ClusterJ then generates implementation class)– Persistent class (ClusterJ provides base implementation class)
23
![Page 24: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/24.jpg)
Copyright © 2011 Oracle Corp.
ClusterJ – Generated Class@PersistenceCapable(table="employee")public interface Employee {
long getId(); void setId(int id);
@Column(name="full_name") String getName(); void setName(String value);
int getSalary(); void setSalary(int value);
Integer getAge(); void setAge(Integer value);}
24
![Page 25: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/25.jpg)
Copyright © 2011 Oracle Corp.
ClusterJ – Dynamic Objectpublic class Employee extends DynamicObject {
public String table() { return "employee";
long getId() { return (Long)get(0); } void setId(long value) { set(0, value); }...// other fields and behavior}
25
![Page 26: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/26.jpg)
Copyright © 2011 Oracle Corp.
Numeric Column Mapping• Java boolean, Boolean
– BIT(1)• Java byte, Byte
– BIT(1) to BIT(8)– TINYINT
• Java short, Short– BIT(1) to BIT(16)– SMALLINT– YEAR
• Java int, Integer– BIT(1) to BIT(32)– INT
• Java long, Long– BIT(1) to BIT(64)– BIGINT– BIGUNSIGNED
• Java float, Float– FLOAT
• Java double, Double– DOUBLE
• Java BigDecimal– NUMERIC– DECIMAL
• Java BigInteger– NUMERIC– DECIMAL
26
![Page 27: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/27.jpg)
Copyright © 2011 Oracle Corp.
Date Column Mapping
• Java util Date– DATETIME– TIMESTAMP– TIME– DATE
• Java sql Date– DATE
• Java sql Time– TIME
• Java sql Timestamp– DATETIME– TIMESTAMP
27
![Page 28: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/28.jpg)
Copyright © 2011 Oracle Corp.
Variable Size Column Mapping
• Java String– CHAR– VARCHAR– TEXT
• Java byte[ ]– BINARY– VARBINARY– BLOB
28
![Page 29: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/29.jpg)
Copyright © 2011 Oracle Corp.
ClusterJ Features
• Character Set Translation (all MySQL charsets)• Automatic detection of primary keys, indexes• Compound Primary Keys• Ordered (btree) indexes• Unique (hash) indexes• Automatic use of partition key• Multi-threaded applications
29
![Page 30: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/30.jpg)
Copyright © 2011 Oracle Corp.
ClusterJ Limitations• No Relationships
– primitive types only• No Multi-table inheritance
– single table per persistent interface or class• No joins in queries
– column comparisons and boolean operators• No Table creation
– user needs to create tables and indexes• No Lazy Loading
– entire record is loaded at one time, including LOBs
30
![Page 31: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/31.jpg)
Copyright © 2011 Oracle Corp.
ClusterJ Interfaces
• SessionFactory– Instance per connection to cluster
• Session– Instance per "user"– persist(), remove(), update(), write()– Find by ID
• Transaction– Instance per Session– begin(), commit(), rollback()
• Query– Multiple instances per Session
31
![Page 32: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/32.jpg)
Copyright © 2011 Oracle Corp.
ClusterJ User View
SessionFactory Configuration Properties
Session andTransaction
Domain Object Domain
Object
Domain Object
Domain Object
Session andTransaction
Domain Object Domain
Object
Domain Object
Domain Object
Session andTransaction
Domain Object Domain
Object
Domain Object
Domain Object
32
![Page 33: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/33.jpg)
Copyright © 2011 Oracle Corp.
ExampleSession session;void getSession() { session = sessionFactory.getSession();}Employee createEmployee(long id, String name, int salary, int age) { Employee employee = session.newInstance(Employee.class); employee.setId(id); employee.setName(name); employee.setSalary(salary); employee.setAge(age); session.persist(employee); return employee;}
33
![Page 34: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/34.jpg)
Copyright © 2011 Oracle Corp.
ExampleTransaction transaction;
void getTransaction() { transaction = session.getTransaction();}
void createEmployees() { getTransaction(); transaction.begin(); createEmployee(1, "Amos", 10000, 44); createEmployee(2, "Barbara", 14000, 48); createEmployee(3, "Chuck", 78000, 61); createEmployee(4, "Dave", 3000, 22); transaction.commit();}
34
![Page 35: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/35.jpg)
Query
• Builder pattern• Similar to JPA criteria query• Compare column values to parameters:
– equal, lessEqual, greaterEqual, lessThan, greaterThan, in– comparison with null
• Combine terms using boolean operators:– or, and, not
• Execution is optimized to use indexes– primary or unique key lookup– ordered scan for complete or partial keys– table scan if no index can be used
35
![Page 36: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/36.jpg)
Copyright © 2011 Oracle Corp.
QueryDomainType qemp = builder.createQueryDefinition(Employee.class) ;
Predicate geAge = qemp.get("age") .greaterEqual(qemp.param("ageFloor"));Predicate leSalary = qemp.get("salary") .lessEqual(qemp.param("salaryCap"));
qemp.where(geAge.and(leSalary));
Query query = session.createQuery(qemp) ;
query.setParameter("ageFloor",33);query.setParameter("salaryCap", 44000);
List<Employee> results = query.getResultList() ;
Query Example
36
![Page 37: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/37.jpg)
Copyright © 2011 Oracle Corp.
Performance
37
![Page 38: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/38.jpg)
Copyright © 2011 Oracle Corp.
<Insert Picture Here>Memcache API
38
![Page 39: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/39.jpg)
Copyright © 2011 Oracle Corp.
Memcached Overview:Two levels of hashing
Memcache
httpd memcached
memcached
memcachedmemcache key
PHP/Perl
friends:12389
hash key to find data
hash key to pick server
39
![Page 40: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/40.jpg)
Copyright © 2011 Oracle Corp.
Cache hit
Memcache
httpd
memcachedPHP/Perl
friends:12389
hash key to find data
VALUE friends:12389 0 31\r\n101, 11009, 11150, 55881, 77798 \r\n
hash key to pick server
40
![Page 41: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/41.jpg)
Copyright © 2011 Oracle Corp.
Cache miss: fetch from DB
Memcache
httpd
memcachePHP/Perl hash key to find data
hash key to pick server
NOT FOUND
mysql
MySQL Slave
SELECT friend_id FROM user_friends WHERE user_id = ?
memcached
41
![Page 42: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/42.jpg)
Copyright © 2011 Oracle Corp.
Expected Latency & Throughput
memcache
httpd
PHP/Perl
MySQL Slave
1,000s of operations/sec.~ 2 ms round trip
10,000s of operations/sec.
~ 200 µs round trip
mysql
memcached
42
![Page 43: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/43.jpg)
Copyright © 2011 Oracle Corp.
Goals • Access stored data directly from memcache clients
– Memcached perspective: • MySQL Cluster is a write-scalable, replicated data store
– with reliable in-memory storage,– plus on-disk storage when data is too big for
memory.– MySQL Cluster perspective:
• memcache is a high performance API– providing easy access to in-memory data,– plus an extra layer of caching when data is on disk.
43
![Page 44: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/44.jpg)
Copyright © 2011 Oracle Corp.
Goals• Support existing schemas and all MySQL data types• Cache MySQL Cluster data inside memcached when
desired– with automatic cache management– and flexibility to fine-tune (or disable) the cache policies
• Support the whole memcache protocol• Achieve superior performance
– latency as expected from memcached– throughput as expected from memcached
44
![Page 45: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/45.jpg)
Copyright © 2011 Oracle Corp.
A Key Prefix
user:1248
the prefix the database key
45
![Page 46: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/46.jpg)
Copyright © 2011 Oracle Corp.
Standard Tables in ndbmemcache• meta
– stores configuration schema version (for upgrade compatibility); consider it to be read-only
• ndb_clusters• containers
– existing tables where data is stored
• cache_policies– rules describing how it can be accessed
• key_prefixes• memcache_server_roles• last_memcached_signon• demo_table
46
![Page 47: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/47.jpg)
Copyright © 2011 Oracle Corp.
A key-prefix mapping
Memcache key
prefixCluster Container Cache
Policy
47
![Page 48: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/48.jpg)
Copyright © 2011 Oracle Corp.
keyprefix Cluster Con-
tainerCache Policy
ServerRole
ID
A memcache server role
keyprefix Cluster Con-
tainerCache Policy
keyprefix Cluster Con-
tainerCache Policy
keyprefix Cluster Con-
tainerCache Policy
48
![Page 49: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/49.jpg)
Copyright © 2011 Oracle Corp.
Measured Latency memcachetest -t 2 -M 7000 -c 25000
49
![Page 50: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/50.jpg)
Copyright © 2011 Oracle Corp.
Measured Throughput memslap
50
![Page 51: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/51.jpg)
Copyright © 2011 Oracle Corp.
Limitations of Memcache API
• The size of stored values is limited to the MySQL Cluster row size (without BLOBs)– This is about 13KB (up from 8KB in 7.1)
51
![Page 52: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/52.jpg)
Copyright © 2011 Oracle Corp.
<Insert Picture Here>
Program Review
Overview of MySQL ClusterHighly available Write-scalable
SQL interfaceRedesigned in 7.2
NoSQL interfacesmod_ndb, ClusterJ, Memcacheplus: C++ NDBAPI, LDAP, etc.
52
![Page 53: Thu 1100 duncan_john_color](https://reader037.vdocuments.us/reader037/viewer/2022103021/55d56058bb61eb32038b465c/html5/thumbnails/53.jpg)
Copyright © 2011 Oracle Corp. 53