ora mysql bothgetting the best of both worlds with oracle 11g and mysql enterprise
TRANSCRIPT
1
Tuesday, 21 September 2010
<Insert Picture Here>
Getting the Best of Both Worlds withOracle Database 11g and MySQL EnterpriseIvan ZorattiSales Consulting Manager EMEA - Oracle MySQL
Tuesday, 21 September 2010
3
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.
Tuesday, 21 September 2010
Pluggable Storage Engine Architecture
Tuesday, 21 September 2010
5
<Insert Picture Here>
Agenda
• Intro to MySQL •MySQL Architecture•MySQL Enterprise•MySQL for Online Applications•When It Makes Sense to Use MySQL•Oracle 11g and MySQL integration
Tuesday, 21 September 2010
6
Intro to MySQL
Tuesday, 21 September 2010
Oracle’s Strategy:Complete. Open. Integrated.
• Built together• Tested together• Managed together• Serviced together• Based on open standards• Lower cost• Lower risk• More reliable
Tuesday, 21 September 2010
Industry’s most complete LAMP Stack
Oracle Ent. Linux & VM
MySQL
Apache, Glassfish
Eclipse, Netbeans
Applications
Tuesday, 21 September 2010
MySQL Today
• In Development/Production since 1995• Acquired with Sun Microsystems• Leading Open Source Database for online apps • 12M+ server installations worldwide• 70,000 downloads each day• Dramatically reduces Database TCO• Performance, Reliability, Ease of Use
Tuesday, 21 September 2010
MySQL is Powering the Web
SaaS, Hosting
Web OEM / ISV’s
Telecommunications
MySQL’s Industry Leading Customers
Enterprise 2.0
Tuesday, 21 September 2010
The Top 20 most popular Websites
...and many more: Flickr, Second Life, Craigslist, Slashdot, LiveJournal, Digg, Del.icio.us, Pricegrabber.com, Weather.com etc.
1.Google2.Facebook3.YouTube4.Yahoo!5.Windows Live6.Baidu7.Wikipedia8.Blogger9.Twitter10.MSN
11.QQ.com12.Yahoo! JP13.Google IN14.Taobao15.Amazon.com16.Sina17.Google DE18.Google HK19.Wordpress20.Google UK
Source: Alexa Top Sites - 1 September 2010
Tuesday, 21 September 2010
MySQL Server Offering and Licensing
EnterpriseGPL
• Enterprise Tools• Services to ensure the maximum
quality, uptime, and performance of MySQL Enterprise systems• 24x7 support• Consultative services• Self-help knowledge aids
• Extended Lifecycle Support• Optional Custom builds and service
patches
CommercialLicence
• For OEMs and ISVs • Frees from applying
GPL rules to source code and IP
• Deeply embedded or bundled
Embedded Server
CommunityGPL
• Available under GPL• Software tested by the
Community• No Maintenance SLA• No Support• No ISV Certification
Community Server
Tuesday, 21 September 2010
MySQL Architecture
Tuesday, 21 September 2010
Pluggable Storage Engine Architecture
Tuesday, 21 September 2010
MySQL Replication• Typical Master/Slave replication environment• Multiple replication topologies are supported
• 1 Master : many Slaves, Circular, Master/Master etc. • Available for all platforms• Enabled for all storage engines• Asynchronous or Semi-Synchronous with Heartbeat• Statement Based (SBR), Row Based (RBR) or mixed-format (MIXED)• Replicate data from one MySQL server/storage engine combination to a
different MySQL Server/storage engine combination• Perform backups using a slave server and continue to process updates on the
master while the backup is being made on the slave• 1-minute configuration
Application Slave Master
Tuesday, 21 September 2010
MySQL Replication
• Data written on the master is also written into the binary log
• The I/O thread on the slave collects logs from the master binary log and writes a relay log on the slave
• The SQL thread on the slave reads the relay log and apply the writes on the slave
• Slave writes are optionally added to the binary log on the slave
• If the semi-sync plugin is installed and enabled, the master waits until the I/O thread on one of the semi-sync slave has written the transaction to disk, or until it receives a timeout, then it returns the ACK to the application.
Tuesday, 21 September 2010
MySQL Cluster/NDB Storage Engine
• Cost• Use commodity hardware to accommodate the
growth of users, traffic, and data• Fault Tolerance
• No single point of failure• High Availability
• Data is replicated across nodes and always available• Automatic fail-over
• Scalability• Distributes large workloads• Replicas for Read, Partitions for Write• Supports “Scale Out”
• High Performance• Load balanced• Memory or Disk based storage engine• Designed high volume/small transactions
• Simplified Management• Cluster management utilities• Commodity components
Applications
MySQL Server Nodes
Management Server & Client
Data Nodes
Tuesday, 21 September 2010
MySQL Cluster /NDB Storage Engine
MySQL Cluster Mgmt
Clients
MySQL Cluster Data Nodes
MySQL Cluster Application Nodes
MySQL Cluster Mgmt
Tuesday, 21 September 2010
MySQL Server 5/MySQL Cluster 7Reliability
• SQL Mode• Triggers & Views• Precision Math• Increased object support• Enteprise Monitoring Tool
• Row-Based Replication• Disk-based Cluster• Cluster Replication with
Conflict Resolution• Enterprise Query Analyser
• Default InnoDB (Barracuda)• Semi-Sync Replication• Enterprise Connectors• Enterprise Support
Diagnostics• MySQL Cluster on Windows• Fast InnoDB Recovery
Ease of Use
• Instance Manager • Information Schema• Cursors• Enterprise Backup & Recovery
Manager
• XML/XPath Support• Task Scheduler• Storage Engine Plug-in API• CSV Storage Engine
•SIGNAL/RESIGNAL• NDB_INFO Schema• MySQL Custer Manager• MySQL Workbench 5.2• MySQL Cluster Connectors• MySQL Enterprise Backup
Performance
• Stored Procedures• Cluster query push down• Query optimisations• Archive Engine• InnoDB storage improvements
• Table/Index Partitioning• Full-Text Index Improvements• Faster ALTER TABLE• Faster ADD/DROP Index• Parallel Data Import
• Multiple Buffer Pool and Rollback Segment
• Eliminated Lock bottlenecks• Extended Change Buffering &
Purge Scheduling•PERFORMANCE_SCHEMA• Multithreaded Cluster
5.0
5.1
5.5RC
Tuesday, 21 September 2010
MySQL and DRBDDistributed Replicated Block Device
• Asynchronous, Semi-Synchronous or Synchronous
• Runs over standard IP networks• Distributed storage• Similar to network RAID
• Characteristics• No special networking components• Excellent performance (blocks vs. rows of data)• Manages inconsistencies of data during a failure• Hides the complexity of many recovery actions• Linux heartbeat manages fail over and virtual IPs
Applications
Virtual IPActive Node Passive Node
DRBD/AVS
Tuesday, 21 September 2010
MySQL and Shared Storage Clusters
• Active/Passive server or process configuration• Links servers with 3rd party software
and virtual IP• Reliability
• Commonly used solution• Fault Tolerance
• No single point of failure with appropriate hardware
• High Availability• Data handled by a SAN or NAS
and always available• Automatic fail-over
• Simplified Management• Cluster management utilities
MySQLServer Nodes
Shared Storage
Applications
Tuesday, 21 September 2010
Distributed CachingMemcache
• Memcache is a high-performance, distributed memory object caching system, generic in nature, but intended for use in speeding up dynamic web applications by alleviating database load.
• Created by Danga Interactive for LiveJournal
• Available on Linux, FreeBSD, Solaris, Windows, OS X• Simple UDP protocol, Libevent based• Integration with all the major RDBMSs, Web & App Servers and
Languages
• Used (amongst many) by LiveJournal, Fotolog, Facebook, Amazon, Yahoo, YouTube, Wikipedia etc.
Tuesday, 21 September 2010
MySQL Enterprise
Tuesday, 21 September 2010
• Subscription:• MySQL Enterprise
• License (OEM):• Embedded Server• Support
• MySQL Cluster Carrier-Grade
• Training• Consulting• NRE
Database
Monitoring
Support
• MySQL Enterprise Server • Monthly Rapid Updates• Quarterly Service Packs• Hot Fix Program• Extended End-of-Life
• Global Monitoring of All Servers• Web-Based Central Console• Built-in Advisors, Expert Advice• MySQL Query Analyzer • Specialised Scale-Out Help
• 24 x 7 x 365 Problem Resolution• Online Self help Knowledge Base• Consultative Help• Bug Escalation Program
MySQL Enterprise: Commercial Offering
http://www.mysql.com/products/enterprise/Tuesday, 21 September 2010
• Single, consolidated view into entire MySQL environment
• Monitor MySQL Sessions, Connections, Replication Latency
• Improve Application Performance
• Gain Immediate Visibility into Your Replication Topologies
• Customisable rules-based monitoring and alerts
• Identifies problems before they occur
• Makes it easier to scale out without requiring more DBAs A Virtual MySQL DBA Assistant!
MySQL Enterprise Monitor
http://www.mysql.com/products/enterprise/monitor.htmlTuesday, 21 September 2010
MySQL Enterprise Monitor ArchitectureIndividual Servers w/Agent
Master-Slave Replication
Enterprise Dashboard
Service Manager
Repository
Collects MySQL and OS specific metrics, Query
content/stats
Holds historical MySQL and OS data, Query Statistics
Advisor Monitoring Event based Alerts
Advice/Corrective Actions
ConfigurationAdvisor SchedulingAlerts/NotificationsVisual MonitoringQuery Analysis
Service Agent written in C and supports all MySQL Enterprise platforms
Service Manager written in Java servlets exposed as web services. Supports Linux, Solaris, Mac OSX and Microsoft Windows
Enterprise Dashboard Web-based, written in JSP
Repository holds historical performance data for analysis
Tuesday, 21 September 2010
MySQL Enterprise Monitor Dashboard
Tuesday, 21 September 2010
MySQL Query Analyzer
• Centralised monitoring of Queries across all servers
• No reliance on Slow Query Logs, SHOW PROCESSLIST;, vmstat, etc.
• Aggregated view of query execution counts, time, and rows
• Saves time parsing atomic executions for total query expense
Tuesday, 21 September 2010
Monitoring Queries with MySQL Enterprise Proxy Server
** MySQL Proxy is optionally deployed and configured as a plugin with the MySQL Agent
List of components to download & configure:
1. MySQL Enterprise Monitor & Query Analyzer2. MySQL Proxy & Agent
(SQL statements & result sets)
MySQL DatabaseApplication Server
3306
18080
6446
(MySQL, OS monitoring data, SQL performance data: statements, examples, EXPLAINs, aggregated stats)
1. MySQL Enterprise Monitor (Service Manager, Dashboard)
2. Proxy Server& MySQL Agent **
Tuesday, 21 September 2010
Monitoring Queries with Connector/J, .NET
1. MySQL Enterprise Monitor & Query Analyzer2. MySQL Agent3. Connector/J v5.1 or newer/ Connector/NET v6.2 or newer4. MySQL Enterprise Plugin for Connector/J, NET
List of components to download & configure:
MySQL Database
(SQL statements & result sets)
(MySQL & OS monitoring data)
3. Connector/J, NET
4. Plugin for Connector/J, NET
(SQL performance data: statements, examples, EXPLAINs, aggregated
stats)
3306
18080
2. MySQL Agent
1. MySQL Enterprise Monitor (Service Manager, Dashboard)
Application Server
Tuesday, 21 September 2010
MySQL Query Analyzer
Tuesday, 21 September 2010
MySQL forOnline Applications
Tuesday, 21 September 2010
Vertical vs Horizontal Scalability
Vertical Scale-Up•Expensive SMP hardware•Closed source software•Platform lock-in•“Fork Lift” to increase capacity
Horizontal Scale-Out•Commodity Intel / AMD•Open source software•Platform freedom•Add commodity servers to increase capacity
Tuesday, 21 September 2010
Read-intensive Application ScalabilityReplication
Applications
Master
Replication
Slaves
Tuesday, 21 September 2010
Read-intensive Application ScalabilityApplication
Servers
Relay Servers
Slave Servers
Read
Write
Tuesday, 21 September 2010
Write-intensive Applications Scalability
Handle multiple connections for:
> Authentication> Session Management> Logging/Click Stream> Management and
Administration> Application Data
> Separate low persistency andhigh persistency data
–High Persistency– Catalogues– Wish Lists– Recommendations– Forums, Messages, Posts,
Comments etc.
–Low Persistency– Baskets and temporary selections– Queues, such as orders,
documents that change status etc.– Short Messages
Tuesday, 21 September 2010
Sharding
ApplicationServers
Slave Servers
Master Servers0 1 2 3 4
0
1
2
3
4
Read
Write
Tuesday, 21 September 2010
Front End Database Infrastructure
Tuesday, 21 September 2010
Front End Database Infrastructure
Authorization& Profile
NDBUID/PWD/SecurityAccount SettingsPersonalization
Tuesday, 21 September 2010
Front End Database Infrastructure
Authorization& Profile
NDB
SessionManagement
NDB
SessionBasket/Trolley/Cart
Current Configuration
Tuesday, 21 September 2010
Front End Database Infrastructure
Authorization& Profile
NDB
SessionManagement
NDB
Transactional Application Data
InnoDB
Orders, InvoicesWish Lists
Forum PostsMessages
Tuesday, 21 September 2010
Front End Database Infrastructure
Authorization& Profile
NDB
SessionManagement
NDB
Transactional Application Data
InnoDBNon-Transactional Data
MyISAM
CataloguesRecommendations
ReviewsComments
Tuesday, 21 September 2010
Front End Database Infrastructure
Authorization& Profile
NDB
SessionManagement
NDB
Transactional Application Data
InnoDBNon-Transactional Data
MyISAM Logging
ArchiveClick Stream
Users’ Activity
Tuesday, 21 September 2010
Front End Database Infrastructure
Authorization& Profile
NDB
SessionManagement
NDB
Transactional Application Data
InnoDBNon-Transactional Data
MyISAM Logging
ArchiveGeneric, Distributed
Caching
Tuesday, 21 September 2010
When It MakesSense to Use MySQL
Tuesday, 21 September 2010
Assuming you know Oracle DB...
• Clients• 99% of the operating and development environments have a
MySQL connector, a MySQL module or plug-in
• Security• No external authentication at the moment
• Connections• MySQL handles connect/disconnect easily• Permanent connections are great but not required
Tuesday, 21 September 2010
Assuming you know Oracle DB...
• Data Definition Language (DDL)• Standard SQL is available• Online ALTERs are not available in the standard MySQL server
• MySQL Replication is often used to manage changes and scheduled downtime
• Basic Operations (standard DML)• Standard SQL is available• High performance with multiple INSERTs and REPLACE• “Hints” are “Rules”
• USE INDEX• FORCE INDEX• IGNORE INDEX
Tuesday, 21 September 2010
Assuming you know Oracle DB...
• Stored Procedures and Routines• Basic language, used primarily to avoid network hops• UDFs are more efficient
• Referential Integrity• Available with InnoDB• Do you always need it?
• Storage Engines• Multiple options• Select the best engine for the job
Tuesday, 21 September 2010
Assuming you know Oracle DB...
• High Availability• Multiple options depending on complexity, required HA and
budget• Mixed technologies
• Disaster Recovery and Geographical Replication• MySQL Replication is the recommended solution
• Extended Features• Usually they are not the primary reason to choose MySQL• “Basic” XML, Text, Spatial features
• Are they good enough for you?
Tuesday, 21 September 2010
Myths
• No Referential Integrity
• Lack of Performance
• Lack of Scalability
• Lack of High Availability
Tuesday, 21 September 2010
Reality
• Basic SQL features
• Limited Procedural Language
• Limited extensions
• “Standard” indexing• BTree and Hash
• No RAC-like architecture
Tuesday, 21 September 2010
You should use MySQL for...• Online Applications
• The “M” in the LAMP stack• The most common database on the Internet• The most used and default backend for online environments,
solutions and frameworks• Data Marts
• Easy and quick to install.• Create - Use - Remove• High performance data load and queries• Compatibility with Open Source and Commercial BI and
Reporting Tools• Embedded Database
• Lightweight with small footprint• Wide range of utilization• Simple to bundle or to deeply embed
Tuesday, 21 September 2010
You should not use MySQL for...
• ERP Solutions• No certification or compatibility• Missing extended features• Not scalable for a typical SMP environment
• Data Warehouse and Business Intelligence Solutions• The optimizer works fine on relatively simple queries
• Subquery optimization... well, it’s not optimal :(• Missing some of the advanced indexing and partitioning features• Missing parallel queries
Tuesday, 21 September 2010
Oracle 11g and MySQL Integration
Tuesday, 21 September 2010
Integration Aspects
• Application Integration• Information and services exchanging• Information and services sharing
• Systems Integration• Systems co-location and co-existence• Systems Administration• Systems Monitoring
• Data Integration• Batch Data Transfer and Exchange• Real Time Data Replication• Real Time Data Sharing
Tuesday, 21 September 2010
Application Integration
• Information Exchange• Messaging systems• Internal component in C/C++, Perl/PhP/Python, Java & .NET
• Integration through caching distributed systems• Memcache is the standard distributed caching mechanism in the
online world, also useful for information exchange• Memcache is available on almost all the client environments and
as plug-in as database functions
Tuesday, 21 September 2010
Application Integration
• Information Exchange• Messaging systems• Internal component in C/C++, Perl/PhP/Python, Java & .NET
• Integration through caching distributed systems• Memcache is the standard distributed caching mechanism in the
online world, also useful for information exchange• Memcache is available on almost all the client environments and
as plug-in as database functions
mysql> select memc_set( '2', 'BCD');+-----------------------+| memc_set( '2', 'BCD') |+-----------------------+| 0 |+-----------------------+1 row in set (0.00 sec)
mysql> select memc_get( '1' );+-----------------+| memc_get( '1' ) |+-----------------+| ABC | +-----------------+1 row in set (0.00 sec)
mysql> select memc_set( md5('MYSQLselect * from t1 limit 10'),'...');...mysql> select memc_get( md5('ORACLEselect * from t1 limit 10'))\G...
Tuesday, 21 September 2010
Distributed Caching
Memcached Cluster
Tuesday, 21 September 2010
Systems Integration
• Instances co-location• File Exchange
• Text, Fixed length, CSV, XML
• Oracle Commitment• Oracle Enterprise Manager (CY 2011)• Oracle Audit Vault (CY 2011)• Oracle Secure Backup (CY 2011)
• Pythian MySQL Management Plug-in for OEM• http://www.pythian.com/documents/pythian-mysql-plug-in-datasheet.pdf
Tuesday, 21 September 2010
Data IntegrationBatch Transfer
• Oracle Data Integrator
• SQL*PLUS/SQL*LOADER - mysqldump/mysqlimport
• CSV files/tables access
• Informatica Products Suite
Tuesday, 21 September 2010
Data IntegrationReal Time Replication
Oracle GoldenGate 11g
• Real Time (CDC - Change Data Capture)Oracle ➜ MySQL
• ETL - Extraction Transformation & LoadingMySQL ➜ OracleOracle ↔ MySQL
• Platforms:• Linux, Windows, Solaris
Tuesday, 21 September 2010
Data IntegrationReal Time Replication
HIT Software DBMoto 7
• Real Time (CDC - Change Data Capture)Oracle ➜ MySQLMySQL ➜ OracleOracle ↔ MySQL
• Refresh, Mirroring and Synchronization• Based on Oracle and MySQL Logs
• Windows Only
http://www.hitsw.com/products_services/dbmoto/DBMoto_Factsheet.pdfTuesday, 21 September 2010
Data IntegrationReal Time Data Connection
• Fast, Real Time, Difficult to scale
• Oracle Heterogenous Services
• MySQL User Defined Functions• Set of MySQL Functions executable at instance level to access
an Oracle DB
• Memcached Sharing• Application Level• Database Level
• Memcached Access from the DB / DB Functions & Triggers
Tuesday, 21 September 2010
64
Tuesday, 21 September 2010
65
Tuesday, 21 September 2010