Mysql Overview 2009
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Mysql Overview 2009 as PDF for free.
More details
- Words: 3,876
- Pages: 52
Sun Microsystems Inc. MySQL in Mexico
Henrique Leandro MySQL LatAm [email protected] +55 11 7603 2516
Agenda Introduction to MySQL MySQL Enterprise MySQL Server Architecture and Technology Scalability and High Availability with MySQL User and Administrator Tools MySQL Roadmap Oracle Migration to MySQL
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
About MySQL • 14 years old • 400+ employees • 750 partners • 60K downloads/day • Customers across every major operating system, hardware vendor, geography, industry, and application type
Leadership, innovation, market acceptance
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
About the company: MySQL, Inc., is the world's second-largest independent open-source company, with more than 400 employees in over 25 countries. MySQL currently holds 49 percent of the open-source database market (Ovum, 2006) and has 400 blue-chip customers. As one of the industry's fastest-growing open-source databases, MySQL is actively used by virtually every Web 2.0 and e-commerce company in existence today. MySQL is headquartered in Cupertino, CA, and Uppsala, Sweden, with more than 400 employees located in 25 countries around the world. More than 750 partners (solutions.MySQL.com/solutions/partners/)
More than 100 resellers and distributors
More than 400 ISVs and IHV technology partners
More than 175 systems integrators and consultants
Partners with industry leaders including Dell, HP, IBM, Microsoft, and Oracle (current Sun partner)
Partners with leading open-source vendors including Red Hat, Ubuntu, Zend, Alfresco, JasperSoft, OpenOffice, Hyperic, Pentaho, SugarCRM, Asterisk, Zimbra, and Eclipse
Overview of MySQL in LatAm • • • • • •
MySQL entered LatAm in 2008 Many Users with 5+ years of experience 70,000 downloads each day – LatAm has 11,000 per month Trained Pre-sales, post sales, and delivery LAN.com, UNAM, Esmas (Televisa), Globo.com Support: http://www.sun.com/contact/support.jsp
Sun Confidential: Internal Only
4
Industry-Leading Customers
Web / Web 2.0
On Demand, SaaS, Hosting
Government
Telecommunications
Enterprise 2.0
Open-source is powering the Web
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL's customer base — centered in five core areas including Web 2.0, SaaS, enterprise/government, telco, and OEM/embedded markets — makes it an important fit for Sun and will help to strengthen Sun's existing customer relationships, specifically among those requiring the flexibility and ease of use of open-source systems. Describe how these 2.0 companies are using MySQL to provide platforms for users to collaborate/share information and how they scale and provide for HA.
MySQL Enterprise Solution Database
Monitoring
Support
• MySQL Enterprise Server 5.1! • Rapid Updates / Service Packs • Hot Fix Program • Indemnification • Subscription:
• MySQL Enterprise • License (OEM): • Global Monitoring• ofEmbedded All Servers Server • Built-in Advisors, Expert Advice • Support • Query Analyzer – NEW! • MySQL • Specialized Scale-Out Help Cluster CarrierGrade • Training • Online Self help Knowledge Base • Consulting • 24 x 7 x 365 Problem Resolution • Consultative Help• NRE • High Availability and Scale Out
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Value for the Customer
MySQL Enterprise Offering Platinum Gold Silver Basic
• Enterprise Server • Ensures Uptime/ Reliability • Regular Service Packs • Self-help Support • Production Support Product Support
Copyright 2008 Sun Microsystems
Gold + Silver +
Basic + Monitors servers Proactive Notifications Expert Advice Optimizes MySQL Config. settings • Ensures Recoverability • Tightens Security • Extends Support • • • •
Server Monitoring
• Monitors and Optimizes Memory Usage • Replication Monitoring • Ensures proper Replication configuration • Rapid Problem Resolution • (Hot Fix builds) Replication
MySQL - The Online Database of the 3rd Millennium
• Ensures Performance and Availability • Guards against unplanned schema change • Optimizes/Tunes • Custom Queries • Database Design • Custom Code
Higher Performance
MySQL Enterprise Customer Center https://enterprise.mysql.com/main.php
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL Enterprise Monitor • Consolidated view • Auto discovery • Customizable • Reduce risk • Query Analysis – New! • More effective DBAs “Virtual MySQL DBA” Assistant Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Point to make: MySQL Monitoring & Advisor Service = “Virtual MySQL DBA” There are 3 main features, each with business rewarding features…
Enterprise Monitor Architecture Customer Datacenter Enterprise Dashboard -Web-based, JSPs Service Manager -Java servlets exposed as web services -Linux, Solaris, Mac OSX and MS Windows MySQL Servers w/Agent
Repository -MySQL DB -historical performance data for analysis Service Agent -lightweight C application -for all Enterprise platforms
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL Query Analyzer – New!
Does not rely on Slow Query Log or Show Processlist Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Added Value of MySQL Enterprise
Query analysis
Load balancer Enterprise manager
Best practices rules 24x7 support Monitoring
Knowledge base
Copyright 2008 Sun Microsystems
Hot fixes
Open-source server with pluggable APIs
Service packs
MySQL - The Online Database of the 3rd Millennium
Modern enterprises needing a proven and trusted platform for database management should investigate MySQL Enterprise, as no other open-source database software in the world supplies all the unique advantages and benefits as does the MySQL Enterprise solution. The combination of the powerful MySQL Enterprise Server, all the enterprise-ready services and production support, and a total cost of ownership that simply has no peer in the database industry make MySQL Enterprise the obvious choice for modern businesses looking for top-notch data management capabilities. MySQL Enterprise Server 5.0 delivers new enterprise features, including: ACID Transactions to build reliable and secure business-critical applications
Stored Procedures to improve developer productivity
Triggers to enforce complex business rules at the database level
Views to ensure sensitive information is not compromised
Information Schema to provide easy access to metadata
Distributed Transactions (XA) to support complex transactions across multiple databases
Pluggable Storage Engine Architecture for maximum flexibility
Archive Storage Engine for historical and audit data
Federated Storage Engine to create a single logical database from many physical servers
Emergency Hot Fix Builds
MySQL Architecture MySQL Server
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Many choose MySQL Enterprise because it offers a new and different paradigm of database management. One key technical differentiator between MySQL and other database platforms — whether they are proprietary or open source — is the pluggable storage engine architecture of MySQL. The MySQL pluggable storage engine architecture enables a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements. The pluggable storage engine architecture provides a standard set of management and support services that are common among all underlying storage engines. The storage engines themselves are the components of the database server that actually perform actions on the underlying data that is maintained at the physical-server level. This efficient and modular architecture provides large performance and manageability benefits for those wishing to specifically target a particular application need — such as data warehousing, transaction processing, high-availability/ cluster situations, etc. — while enjoying the advantage of utilizing a set of interfaces and services that are independent of any one storage engine. The technical advantage of MySQL Enterprise is clearly evident in this respect, as unnecessary overhead is avoided when only certain engines are chosen for application use.
Why Move to 5.1? Great new features Business Intelligence/Data Warehousing • Table/Index Partitioning • Better XML Handling – XPath • Archive engine enhancements
High Availability •Row-based and hybrid Replication
Easier Manageability • Task Scheduler • Dynamic General/Slow Log • Return of embedded library (libmysqld)
Higher Performance • Better user session and problem SQL identification • New Performance/Load Testing Utility • SQL Profiler Utility
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
Why Move to 5.1? Key New Features • • • • •
Partitioning Row-based and Hybrid Replication Task Scheduler Embedded Library Better Performance Utilities
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Partitioning • Perfect for data warehouses or other VLDB situations • Increases performance – only needed partitions are accessed • Eases space/data management burden on DBA • Supports range, hash, key, list, and composite methods • Supported by all storage engines in MySQL • More options than Microsoft, DB2, or Sybase partitioning
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Row-Based Replication • Statement-based replication great for most cases ... > ... but sometimes causes issues
• Row-based replication replicates the actual data values > and offers safest method
• Hybrid replication (‘mixed’) is also available
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Task Scheduler • Built-in scheduler for running SQL-based jobs • Easy to use • One-time or recurring tasks • Don’t have to write OS scripts that invoke SQL tasks and then schedule them via CRON or MS task scheduler
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Embedded Library • Allows Smallest Possible MySQL Footprint • Embedded library (libmysqld) returns to 5.1 • Embeds into actual written program • Scale database binary footprint down to 3MB
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Performance Utilities • Slow and General query log > Dynamic > SQL-based
• SQL Statement Profiler Utility (command line) > Helps developers discover exactly where a SQL statement is
spending its time and where problems are
• Benchmarking utility - mysqlslap > Helps developers perform load testing
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
SQL Profiler Utility • Turn on/off dynamically to capture user issued SQL • Understand steps MySQL Server takes to satisfy query • Gain insight into CPU, IO, timing, and other diagnostics • Store queries for future review
SQL
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
SQL Profiler Example mysql> show profiles; +----------+------------+-----------------------------------------------+ |
1 | 0.00044700 | select count(*) from client where broker_id=2 |
+----------+------------+-----------------------------------------------+ mysql> show profile for query 1; +--------------------+------------+ | Status
| Duration
|
+--------------------+------------+ | (initialization)
| 0.00006300 |
| Opening tables
| 0.00001400 |
| System lock
| 0.00000600 |
| Table lock
| 0.00001000 |
| init
| 0.00002200 |
| optimizing
| 0.00001100 |
| statistics
| 0.00009300 |
…
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Scalability Strategy Vertical
Horizontal
MySQL Replication
Applications
Virtual IP
Master
HA Slave
Scale-out Slave
Scale-out Slave
Replication
Replication
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Scale-out Slave
Shared Disk Clusters • • • • • • • •
Solaris Cluster OpenSolaris Open HA Cluster Red Hat Enterprise Server Novell Suse Enterprise Server Veritas Cluster Server Linux HA with Heartbeat HP Serviceguard Microsoft Cluster Server
Active/Passive server or process configuration • Links servers with 3rd party software and virtual IP
Applications
MySQL Server Nodes
Reliability • Commonly used solution Fault Tolerance • No single point of failure with appropriate hardware Shared Storage
High Availability • Data handled by a SAN or NAS and always available • Automatic fail-over Simplified Management • Cluster management utilities Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
DRBD Distributed Replicated Block Device •Runs over standard IP networks •Distributed storage •Similar to network RAID
Applications
Synchronous Characteristics •No special networking components (except Heartbeat) •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
Active Node
Virtual IP
Passive Node
DRBD
Sun StorageTek Availability Suite for Solaris/OpenSolaris Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL Cluster Client Applications Application Layer Access through native API Interface/SQL Layer MySQL Server
MySQL Server
Data Layer Management Server Data Nodes
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Management Client
MySQL Proxy • Lightweight binary application between the client and the server • Shipped with an embedded Lua interpreter http://www.lua.org ? ? ? ? ?
Load Balancing Failover Logging Query rewriting Scale-out and Partitioning
Copyright 2008 Sun Microsystems
Check if there is an European customer
MySQL - The Online Database of the 3rd Millennium
Database Management Tools
ZManda Recovery Manager for MySQL
• • • •
Global backup and recovery solution Easy to install and configure Web-based interface Graphical point-and-click management
Copyright 2008 Sun Microsystems
• Security, compression, backup policy management, and more ... • Handles all backup methods • Logical, RAW, Snapshot, Replication • Point in time recovery • Support for LVM, ZFS, Veritas, Netapp etc.
MySQL - The Online Database of the 3rd Millennium
ZRM - MySQL Implementation Aware …….
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Workbench Database modeling tool, used to design and create new database schemas, document existing databases and even perform complex migrations to MySQL. Features: •Fast OpenGL based canvas •Database reverse Engineering •Database/Model synchronization •SQL script generation •Visual representation of tables •Selectable notations for diagram
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL 6.0 The “What” • • • • • •
Falcon Engine Online Backup (version 1.0) Online Add Column Replication Conflict Detection Optimizer Enhancements Better Performance Info
Transactional engine Cross engine, non-blocking Cluster only Cluster only Faster subqueries/joins Diagnostics and more
The “When” • Last Alpha available now • Upcoming betas will roll out other 6.0 features • GA scheduled for Q4 2009
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL 7.0 The “What” • • • • • •
Security Groups/Roles Transparent Data Encryption Data Auditing External Authentication Online Alter Table Optimizer Enhancements
Easier privilege mgmt Table/Column level For compliance O/S level authorizations Online DDL changes More subquery changes
The “When” • Alpha's expected late-2009 • GA expected Q4 of 2010
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Do Storage Engines Make a Difference…? MyISAM Inserts Per Second
User Load
InnoDB Inserts Per Second
Archive Inserts Per Second
1
3,203.00
2,670.00
3,576.00
4
9,123.00
5,280.00
11,038.00
8
9,361.00
5,044.00
13,202.00
16
8,957.00
4,424.00
13,066.00
32
8,470.00
3,934.00
12,921.00
64
8,382.00
3,541.00
12,571.00
Archive has 50% more INSERT throughput over MyISAM and 255% more than InnoDB…! Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
36
But don’t vendors have similar options…? Load 3 million rows into a table via procedure
Oracle10g Compressed Table
InnoDB
MyISAM
Archive
3.03 Minutes
3.05 Minutes
1.49 Minutes
1.13 Minutes
Archive has 60% better performance than Oracle10g compressed table … !
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
37
CSV in Action Want to instantly load 1 billion rows into MySQL…? mysql> create table … engine=csv; [mysql@linux1 total 785848 -rw-rw---- 1 -rw-rw---- 1 -rw-rw---- 1
gim]$ ls -l mysql mysql 35 May mysql mysql 0 May mysql mysql 291367350000 May
1 1 1
client_detail_csv.CSM client_detail_csv.CSV flatdata.dat
[mysql@linux1 gim]$ mv flatdata.dat client_detail_csv.CSV mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from client_detail_csv; +-------------+ | count(*) | +-------------+ No way to do this | 1000000000 | External Table … +-------------+ Copyright 2008 MySQL AB
with an Oracle
The World’s Most Popular Open Source Database
38
Contrasting Oracle and MySQL Area Memory Caches
Redo/Undo Logs
MySQL
Oracle
Microsoft SQL Server
• MyISAM key caches
• Data cache (variants)
• Buffer cache
• InnoDB data cache • InnoDB log cache
• Log buffer • Shared Pool
• Dictionary cache • Falcon caches
• Java Pool • Large Pool
• SQL cache • Misc caches (lock, connection, workspace, etc.)
• Query Cache • User caches
• PGA • Query cache (11g)
• InnoDB Undo Space
• Undo Tablespace (9i+)
• TempDB (2005+)
• InnoDB Logs
• Redo Logs
• Transaction Logs
• Falcon Log
• Archive Logs
• Binary Log Data Storage
• Tablespaces
• Tablespaces
• Filegroups
• Table/Index Files
• Datafiles
• Files
• Cost-based
• Cost-based
• Format files Optimizer
Copyright 2008 MySQL AB
• Cost-based
The World’s Most Popular Open Source Database
39
Why Migrate to MySQL? Strong Feature Set Feature
MySQL
Oracle
Standard Heap Tables and B-Tree Indexes
Partitioned Tables/Indexes (MySQL has more partitioning options)
ACID Transaction Support
Row-Level Locking, MVCC (readers don’t block writers)
Server-Enforced Referential Integrity
Advanced Indexing (Clustered, Full-Text)
Robust datatype support (BLOB’s, varchar, datetime, numerics, etc.)
Replication
Stored Procedures, Triggers, Functions, Cursors, Updateable Views
Highly-Available Clustered Database
Cost-based Optimizer
Backup with Point-in-Time Recovery
Terabyte Database Size Capable
Open Source
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
40
Why Migrate to MySQL? Strong Feature Set MySQL does not have all the features of Oracle but… • MySQL isn’t designed to be the most feature rich RBDMS but rather one that… • Offers 80-90% of the features at 10% the cost of proprietary (90% TCO savings) • Provides very high-performance and rock-solid reliability • Easiest to install, setup, configure, maintain, and use • Easily co-exists with Oracle in the data center
Choose MySQL When it Makes Sense and Oracle When it Makes Sense Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
41
Oracle to MySQL Migration Scenarios • Full application migrations • Partial application migrations (lookup data, web session info, etc.) • Business Intelligence migrations (data warehouse, marts, etc.) • Data archiving migrations (storing historical data, etc.) • Embedded database applications requiring smaller footprint
New
Web Customer
Copyright 2008 MySQL AB
n actio Trans
Data
Sh o Ac ppin cou g, nt I Ses nfo sio rm n, atio n
The World’s Most Popular Open Source Database
42
Migration Considerations – What Engine? Feature Storage limits Transactions
MyISAM No
Falcon
NDB
110TB
Yes
Archive No
InnoDB 64TB
Memory Yes
No
Yes
Yes
No
Yes
No
Table
MVCC
Row
Row
Row
Table
MVCC snapshot read
No
Yes
No
No
Yes
No
Geospatial support
Yes
Yes
No
Yes
Yes
No
Data caches
No
Yes
Yes
No
Yes
NA
Index caches
Yes
Yes
Yes
No
Yes
NA
Compressed data
Yes
No
No
Yes
No
No
Locking granularity
Storage cost (relative to other engines)
Small
Med
Med
Smallest
Med
NA
Memory cost (relative to other engines)
Low
High
High
Low
High
High
Bulk insert speed
High
Med
High
Highest
Med
High
Replication support
Yes
Yes
Yes
Yes
Yes
Yes
Foreign Key support
No
Yes
No
No
Yes
No
Built-in Cluster/High-availability support
No
No
Yes
No
No
No
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
43
Security and Datatype Considerations Security Item
Oracle
MySQL
Built-in Accounts
SYS, SYSTEM, others
Root.
Object Privileges Handled
GRANT, REVOKE
GRANT, REVOKE. Host consideration
Privilege Granularity
Column, Row
Column (row via view)
Group Management
ROLES
On Roadmap
SSL Supported
Yes
Yes
Encryption
Transparent, RC4, DES, AES, etc.
AES, DES, MD5 SHA1; 128/256
Auditing
Audit subsystem
Handled via logging
Oracle Datatype
MySQL Datatype
Oracle Datatype
MySQL Datatype
CHAR, VARCHAR(2), NCHAR, NVARCHAR
CHAR, VARCHAR, TEXT
NUMBER, DECIMAL
INT, BIGINT, DECIMAL
LONGTEXT
NUMBER (P,S), DECIMAL (P,S)
DECIMAL (P,S)
CLOB, LONG RAW
MEDIUMBLOB
REAL, DOUBLE
DECIMAL
LONG RAW, BLOB
BLOB
FLOAT
DOUBLE
BFILE
None
DATE, TIMESTAMP
DATETIME,TIMESTAMP
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
44
Miscellaneous Migration Considerations Objects in Oracle that do not have a complement in MySQL include: • TYPEs • Synonyms • Materialized Views (coming in upcoming version) • Dimensions • Sequences (MySQL uses AUTOINCREMENT columns) • Bitmap Indexes • Global Partitioned Indexes (coming soon) • Packages (although MySQL does have stored procedures) • Java Stored Procedures • DDL and Statement-Based Triggers (MySQL has Row-Based)
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
45
Migration Toolkit – How Does it Work? 1. 2. 3. 4. 5. 6.
Connect to and Reverse Engineer Oracle Source Connect to MySQL Target Select Schemas/Objects to Migrate Customize MySQL Target Schemas/Objects Transform Oracle Schemas/Objects to MySQL Schemas/Objects Move Data from Oracle to MySQL
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
46
Oracle-to-MySQL Object Translation Schema
Migration Toolkit
Database
Table
Migration Toolkit
Table
Index
Migration Toolkit
Index
View
Migration Toolkit
View
Stored Procedure
manual
Stored Procedure
Function
manual
Function
Trigger
manual
Trigger
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
47
Every Oracle schema becomes a MySQL database. Stored Procedures, Functions and Trigger must be re-written manually and converted from PL/SQL to ANSI SPL.
Other Oracle->MySQL Migration Considerations • For large data migrations, ensure all proper MySQL parms are set (Migration Toolkit will do automatically for you). • Enable sql_mode option in MySQL 5.0 and above for maximum data integrity • Look into 3rd party management tools (Quest, Embarcadero, etc.) to manage both Oracle & MySQL • Ensure to mirror Oracle needs in MySQL (autogrowth files, binary logging for point-in-time recovery, etc.) • Ensure MySQL memory defaults are not used for demanding systems • Look into MySQL Enterprise Monitor to manage multiple MySQL Servers • When tuning migrated SQL code, utilize slow query log to catch bad SQL • Periodically check MySQL error log just like Oracle alert log • Look to MySQL Enterprise for production/data center applications
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
48
Every Oracle schema becomes a MySQL database. Stored Procedures, Functions and Trigger must be re-written manually and converted from PL/SQL to ANSI SPL.
Apps
Third Party Support – Standard Apps eShop & Commerce
Content Management
Session Management
User Directory
Blogs & Forums
Search Engines
Tools
Administration Transactional Replication Application Development Business Intelligence
Platforms
Database App – Web Servers & Load Balancers Storage Operating System Hardware
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
49
Apps
Third Party Support – Data Warehouse Operational Data Store
Source Systems
ETL Process
Data Warehouse
Metadata Repository
Data Mart
Tools
Reporting & Analytics ETL Replication Development & Admin
Platforms
Database Storage (SAN – NAS) Operating System
Hardware
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
50
For More Information • MySQL 5.1 Download Page: > http://dev.mysql.com/downloads/mysql/5.1.html
• MySQL 5.1 Technical White Paper > http://www.mysql.com/why-mysql/white-papers/whatsnew-
mysql-51.php
• MySQL Enterprise: > http://www.mysql.com/products/enterprise/
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
MySQL from Sun: The Platform for the Web Economy www.sun.com/mysql [email protected]
Thank you very much for your time today!
Henrique Leandro MySQL LatAm [email protected] +55 11 7603 2516
Agenda Introduction to MySQL MySQL Enterprise MySQL Server Architecture and Technology Scalability and High Availability with MySQL User and Administrator Tools MySQL Roadmap Oracle Migration to MySQL
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
About MySQL • 14 years old • 400+ employees • 750 partners • 60K downloads/day • Customers across every major operating system, hardware vendor, geography, industry, and application type
Leadership, innovation, market acceptance
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
About the company: MySQL, Inc., is the world's second-largest independent open-source company, with more than 400 employees in over 25 countries. MySQL currently holds 49 percent of the open-source database market (Ovum, 2006) and has 400 blue-chip customers. As one of the industry's fastest-growing open-source databases, MySQL is actively used by virtually every Web 2.0 and e-commerce company in existence today. MySQL is headquartered in Cupertino, CA, and Uppsala, Sweden, with more than 400 employees located in 25 countries around the world. More than 750 partners (solutions.MySQL.com/solutions/partners/)
More than 100 resellers and distributors
More than 400 ISVs and IHV technology partners
More than 175 systems integrators and consultants
Partners with industry leaders including Dell, HP, IBM, Microsoft, and Oracle (current Sun partner)
Partners with leading open-source vendors including Red Hat, Ubuntu, Zend, Alfresco, JasperSoft, OpenOffice, Hyperic, Pentaho, SugarCRM, Asterisk, Zimbra, and Eclipse
Overview of MySQL in LatAm • • • • • •
MySQL entered LatAm in 2008 Many Users with 5+ years of experience 70,000 downloads each day – LatAm has 11,000 per month Trained Pre-sales, post sales, and delivery LAN.com, UNAM, Esmas (Televisa), Globo.com Support: http://www.sun.com/contact/support.jsp
Sun Confidential: Internal Only
4
Industry-Leading Customers
Web / Web 2.0
On Demand, SaaS, Hosting
Government
Telecommunications
Enterprise 2.0
Open-source is powering the Web
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL's customer base — centered in five core areas including Web 2.0, SaaS, enterprise/government, telco, and OEM/embedded markets — makes it an important fit for Sun and will help to strengthen Sun's existing customer relationships, specifically among those requiring the flexibility and ease of use of open-source systems. Describe how these 2.0 companies are using MySQL to provide platforms for users to collaborate/share information and how they scale and provide for HA.
MySQL Enterprise Solution Database
Monitoring
Support
• MySQL Enterprise Server 5.1! • Rapid Updates / Service Packs • Hot Fix Program • Indemnification • Subscription:
• MySQL Enterprise • License (OEM): • Global Monitoring• ofEmbedded All Servers Server • Built-in Advisors, Expert Advice • Support • Query Analyzer – NEW! • MySQL • Specialized Scale-Out Help Cluster CarrierGrade • Training • Online Self help Knowledge Base • Consulting • 24 x 7 x 365 Problem Resolution • Consultative Help• NRE • High Availability and Scale Out
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Value for the Customer
MySQL Enterprise Offering Platinum Gold Silver Basic
• Enterprise Server • Ensures Uptime/ Reliability • Regular Service Packs • Self-help Support • Production Support Product Support
Copyright 2008 Sun Microsystems
Gold + Silver +
Basic + Monitors servers Proactive Notifications Expert Advice Optimizes MySQL Config. settings • Ensures Recoverability • Tightens Security • Extends Support • • • •
Server Monitoring
• Monitors and Optimizes Memory Usage • Replication Monitoring • Ensures proper Replication configuration • Rapid Problem Resolution • (Hot Fix builds) Replication
MySQL - The Online Database of the 3rd Millennium
• Ensures Performance and Availability • Guards against unplanned schema change • Optimizes/Tunes • Custom Queries • Database Design • Custom Code
Higher Performance
MySQL Enterprise Customer Center https://enterprise.mysql.com/main.php
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL Enterprise Monitor • Consolidated view • Auto discovery • Customizable • Reduce risk • Query Analysis – New! • More effective DBAs “Virtual MySQL DBA” Assistant Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Point to make: MySQL Monitoring & Advisor Service = “Virtual MySQL DBA” There are 3 main features, each with business rewarding features…
Enterprise Monitor Architecture Customer Datacenter Enterprise Dashboard -Web-based, JSPs Service Manager -Java servlets exposed as web services -Linux, Solaris, Mac OSX and MS Windows MySQL Servers w/Agent
Repository -MySQL DB -historical performance data for analysis Service Agent -lightweight C application -for all Enterprise platforms
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL Query Analyzer – New!
Does not rely on Slow Query Log or Show Processlist Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Added Value of MySQL Enterprise
Query analysis
Load balancer Enterprise manager
Best practices rules 24x7 support Monitoring
Knowledge base
Copyright 2008 Sun Microsystems
Hot fixes
Open-source server with pluggable APIs
Service packs
MySQL - The Online Database of the 3rd Millennium
Modern enterprises needing a proven and trusted platform for database management should investigate MySQL Enterprise, as no other open-source database software in the world supplies all the unique advantages and benefits as does the MySQL Enterprise solution. The combination of the powerful MySQL Enterprise Server, all the enterprise-ready services and production support, and a total cost of ownership that simply has no peer in the database industry make MySQL Enterprise the obvious choice for modern businesses looking for top-notch data management capabilities. MySQL Enterprise Server 5.0 delivers new enterprise features, including: ACID Transactions to build reliable and secure business-critical applications
Stored Procedures to improve developer productivity
Triggers to enforce complex business rules at the database level
Views to ensure sensitive information is not compromised
Information Schema to provide easy access to metadata
Distributed Transactions (XA) to support complex transactions across multiple databases
Pluggable Storage Engine Architecture for maximum flexibility
Archive Storage Engine for historical and audit data
Federated Storage Engine to create a single logical database from many physical servers
Emergency Hot Fix Builds
MySQL Architecture MySQL Server
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Many choose MySQL Enterprise because it offers a new and different paradigm of database management. One key technical differentiator between MySQL and other database platforms — whether they are proprietary or open source — is the pluggable storage engine architecture of MySQL. The MySQL pluggable storage engine architecture enables a database professional to select a specialized storage engine for a particular application need while being completely shielded from the need to manage any specific application coding requirements. The pluggable storage engine architecture provides a standard set of management and support services that are common among all underlying storage engines. The storage engines themselves are the components of the database server that actually perform actions on the underlying data that is maintained at the physical-server level. This efficient and modular architecture provides large performance and manageability benefits for those wishing to specifically target a particular application need — such as data warehousing, transaction processing, high-availability/ cluster situations, etc. — while enjoying the advantage of utilizing a set of interfaces and services that are independent of any one storage engine. The technical advantage of MySQL Enterprise is clearly evident in this respect, as unnecessary overhead is avoided when only certain engines are chosen for application use.
Why Move to 5.1? Great new features Business Intelligence/Data Warehousing • Table/Index Partitioning • Better XML Handling – XPath • Archive engine enhancements
High Availability •Row-based and hybrid Replication
Easier Manageability • Task Scheduler • Dynamic General/Slow Log • Return of embedded library (libmysqld)
Higher Performance • Better user session and problem SQL identification • New Performance/Load Testing Utility • SQL Profiler Utility
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
Why Move to 5.1? Key New Features • • • • •
Partitioning Row-based and Hybrid Replication Task Scheduler Embedded Library Better Performance Utilities
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Partitioning • Perfect for data warehouses or other VLDB situations • Increases performance – only needed partitions are accessed • Eases space/data management burden on DBA • Supports range, hash, key, list, and composite methods • Supported by all storage engines in MySQL • More options than Microsoft, DB2, or Sybase partitioning
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Row-Based Replication • Statement-based replication great for most cases ... > ... but sometimes causes issues
• Row-based replication replicates the actual data values > and offers safest method
• Hybrid replication (‘mixed’) is also available
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Task Scheduler • Built-in scheduler for running SQL-based jobs • Easy to use • One-time or recurring tasks • Don’t have to write OS scripts that invoke SQL tasks and then schedule them via CRON or MS task scheduler
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Embedded Library • Allows Smallest Possible MySQL Footprint • Embedded library (libmysqld) returns to 5.1 • Embeds into actual written program • Scale database binary footprint down to 3MB
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
5.1 Performance Utilities • Slow and General query log > Dynamic > SQL-based
• SQL Statement Profiler Utility (command line) > Helps developers discover exactly where a SQL statement is
spending its time and where problems are
• Benchmarking utility - mysqlslap > Helps developers perform load testing
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
SQL Profiler Utility • Turn on/off dynamically to capture user issued SQL • Understand steps MySQL Server takes to satisfy query • Gain insight into CPU, IO, timing, and other diagnostics • Store queries for future review
SQL
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
SQL Profiler Example mysql> show profiles; +----------+------------+-----------------------------------------------+ |
1 | 0.00044700 | select count(*) from client where broker_id=2 |
+----------+------------+-----------------------------------------------+ mysql> show profile for query 1; +--------------------+------------+ | Status
| Duration
|
+--------------------+------------+ | (initialization)
| 0.00006300 |
| Opening tables
| 0.00001400 |
| System lock
| 0.00000600 |
| Table lock
| 0.00001000 |
| init
| 0.00002200 |
| optimizing
| 0.00001100 |
| statistics
| 0.00009300 |
…
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Scalability Strategy Vertical
Horizontal
MySQL Replication
Applications
Virtual IP
Master
HA Slave
Scale-out Slave
Scale-out Slave
Replication
Replication
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Scale-out Slave
Shared Disk Clusters • • • • • • • •
Solaris Cluster OpenSolaris Open HA Cluster Red Hat Enterprise Server Novell Suse Enterprise Server Veritas Cluster Server Linux HA with Heartbeat HP Serviceguard Microsoft Cluster Server
Active/Passive server or process configuration • Links servers with 3rd party software and virtual IP
Applications
MySQL Server Nodes
Reliability • Commonly used solution Fault Tolerance • No single point of failure with appropriate hardware Shared Storage
High Availability • Data handled by a SAN or NAS and always available • Automatic fail-over Simplified Management • Cluster management utilities Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
DRBD Distributed Replicated Block Device •Runs over standard IP networks •Distributed storage •Similar to network RAID
Applications
Synchronous Characteristics •No special networking components (except Heartbeat) •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
Active Node
Virtual IP
Passive Node
DRBD
Sun StorageTek Availability Suite for Solaris/OpenSolaris Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL Cluster Client Applications Application Layer Access through native API Interface/SQL Layer MySQL Server
MySQL Server
Data Layer Management Server Data Nodes
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Management Client
MySQL Proxy • Lightweight binary application between the client and the server • Shipped with an embedded Lua interpreter http://www.lua.org ? ? ? ? ?
Load Balancing Failover Logging Query rewriting Scale-out and Partitioning
Copyright 2008 Sun Microsystems
Check if there is an European customer
MySQL - The Online Database of the 3rd Millennium
Database Management Tools
ZManda Recovery Manager for MySQL
• • • •
Global backup and recovery solution Easy to install and configure Web-based interface Graphical point-and-click management
Copyright 2008 Sun Microsystems
• Security, compression, backup policy management, and more ... • Handles all backup methods • Logical, RAW, Snapshot, Replication • Point in time recovery • Support for LVM, ZFS, Veritas, Netapp etc.
MySQL - The Online Database of the 3rd Millennium
ZRM - MySQL Implementation Aware …….
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Workbench Database modeling tool, used to design and create new database schemas, document existing databases and even perform complex migrations to MySQL. Features: •Fast OpenGL based canvas •Database reverse Engineering •Database/Model synchronization •SQL script generation •Visual representation of tables •Selectable notations for diagram
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL 6.0 The “What” • • • • • •
Falcon Engine Online Backup (version 1.0) Online Add Column Replication Conflict Detection Optimizer Enhancements Better Performance Info
Transactional engine Cross engine, non-blocking Cluster only Cluster only Faster subqueries/joins Diagnostics and more
The “When” • Last Alpha available now • Upcoming betas will roll out other 6.0 features • GA scheduled for Q4 2009
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
MySQL 7.0 The “What” • • • • • •
Security Groups/Roles Transparent Data Encryption Data Auditing External Authentication Online Alter Table Optimizer Enhancements
Easier privilege mgmt Table/Column level For compliance O/S level authorizations Online DDL changes More subquery changes
The “When” • Alpha's expected late-2009 • GA expected Q4 of 2010
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Do Storage Engines Make a Difference…? MyISAM Inserts Per Second
User Load
InnoDB Inserts Per Second
Archive Inserts Per Second
1
3,203.00
2,670.00
3,576.00
4
9,123.00
5,280.00
11,038.00
8
9,361.00
5,044.00
13,202.00
16
8,957.00
4,424.00
13,066.00
32
8,470.00
3,934.00
12,921.00
64
8,382.00
3,541.00
12,571.00
Archive has 50% more INSERT throughput over MyISAM and 255% more than InnoDB…! Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
36
But don’t vendors have similar options…? Load 3 million rows into a table via procedure
Oracle10g Compressed Table
InnoDB
MyISAM
Archive
3.03 Minutes
3.05 Minutes
1.49 Minutes
1.13 Minutes
Archive has 60% better performance than Oracle10g compressed table … !
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
37
CSV in Action Want to instantly load 1 billion rows into MySQL…? mysql> create table … engine=csv; [mysql@linux1 total 785848 -rw-rw---- 1 -rw-rw---- 1 -rw-rw---- 1
gim]$ ls -l mysql mysql 35 May mysql mysql 0 May mysql mysql 291367350000 May
1 1 1
client_detail_csv.CSM client_detail_csv.CSV flatdata.dat
[mysql@linux1 gim]$ mv flatdata.dat client_detail_csv.CSV mysql> flush tables; Query OK, 0 rows affected (0.00 sec) mysql> select count(*) from client_detail_csv; +-------------+ | count(*) | +-------------+ No way to do this | 1000000000 | External Table … +-------------+ Copyright 2008 MySQL AB
with an Oracle
The World’s Most Popular Open Source Database
38
Contrasting Oracle and MySQL Area Memory Caches
Redo/Undo Logs
MySQL
Oracle
Microsoft SQL Server
• MyISAM key caches
• Data cache (variants)
• Buffer cache
• InnoDB data cache • InnoDB log cache
• Log buffer • Shared Pool
• Dictionary cache • Falcon caches
• Java Pool • Large Pool
• SQL cache • Misc caches (lock, connection, workspace, etc.)
• Query Cache • User caches
• PGA • Query cache (11g)
• InnoDB Undo Space
• Undo Tablespace (9i+)
• TempDB (2005+)
• InnoDB Logs
• Redo Logs
• Transaction Logs
• Falcon Log
• Archive Logs
• Binary Log Data Storage
• Tablespaces
• Tablespaces
• Filegroups
• Table/Index Files
• Datafiles
• Files
• Cost-based
• Cost-based
• Format files Optimizer
Copyright 2008 MySQL AB
• Cost-based
The World’s Most Popular Open Source Database
39
Why Migrate to MySQL? Strong Feature Set Feature
MySQL
Oracle
Standard Heap Tables and B-Tree Indexes
Partitioned Tables/Indexes (MySQL has more partitioning options)
ACID Transaction Support
Row-Level Locking, MVCC (readers don’t block writers)
Server-Enforced Referential Integrity
Advanced Indexing (Clustered, Full-Text)
Robust datatype support (BLOB’s, varchar, datetime, numerics, etc.)
Replication
Stored Procedures, Triggers, Functions, Cursors, Updateable Views
Highly-Available Clustered Database
Cost-based Optimizer
Backup with Point-in-Time Recovery
Terabyte Database Size Capable
Open Source
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
40
Why Migrate to MySQL? Strong Feature Set MySQL does not have all the features of Oracle but… • MySQL isn’t designed to be the most feature rich RBDMS but rather one that… • Offers 80-90% of the features at 10% the cost of proprietary (90% TCO savings) • Provides very high-performance and rock-solid reliability • Easiest to install, setup, configure, maintain, and use • Easily co-exists with Oracle in the data center
Choose MySQL When it Makes Sense and Oracle When it Makes Sense Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
41
Oracle to MySQL Migration Scenarios • Full application migrations • Partial application migrations (lookup data, web session info, etc.) • Business Intelligence migrations (data warehouse, marts, etc.) • Data archiving migrations (storing historical data, etc.) • Embedded database applications requiring smaller footprint
New
Web Customer
Copyright 2008 MySQL AB
n actio Trans
Data
Sh o Ac ppin cou g, nt I Ses nfo sio rm n, atio n
The World’s Most Popular Open Source Database
42
Migration Considerations – What Engine? Feature Storage limits Transactions
MyISAM No
Falcon
NDB
110TB
Yes
Archive No
InnoDB 64TB
Memory Yes
No
Yes
Yes
No
Yes
No
Table
MVCC
Row
Row
Row
Table
MVCC snapshot read
No
Yes
No
No
Yes
No
Geospatial support
Yes
Yes
No
Yes
Yes
No
Data caches
No
Yes
Yes
No
Yes
NA
Index caches
Yes
Yes
Yes
No
Yes
NA
Compressed data
Yes
No
No
Yes
No
No
Locking granularity
Storage cost (relative to other engines)
Small
Med
Med
Smallest
Med
NA
Memory cost (relative to other engines)
Low
High
High
Low
High
High
Bulk insert speed
High
Med
High
Highest
Med
High
Replication support
Yes
Yes
Yes
Yes
Yes
Yes
Foreign Key support
No
Yes
No
No
Yes
No
Built-in Cluster/High-availability support
No
No
Yes
No
No
No
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
43
Security and Datatype Considerations Security Item
Oracle
MySQL
Built-in Accounts
SYS, SYSTEM, others
Root.
Object Privileges Handled
GRANT, REVOKE
GRANT, REVOKE. Host consideration
Privilege Granularity
Column, Row
Column (row via view)
Group Management
ROLES
On Roadmap
SSL Supported
Yes
Yes
Encryption
Transparent, RC4, DES, AES, etc.
AES, DES, MD5 SHA1; 128/256
Auditing
Audit subsystem
Handled via logging
Oracle Datatype
MySQL Datatype
Oracle Datatype
MySQL Datatype
CHAR, VARCHAR(2), NCHAR, NVARCHAR
CHAR, VARCHAR, TEXT
NUMBER, DECIMAL
INT, BIGINT, DECIMAL
LONGTEXT
NUMBER (P,S), DECIMAL (P,S)
DECIMAL (P,S)
CLOB, LONG RAW
MEDIUMBLOB
REAL, DOUBLE
DECIMAL
LONG RAW, BLOB
BLOB
FLOAT
DOUBLE
BFILE
None
DATE, TIMESTAMP
DATETIME,TIMESTAMP
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
44
Miscellaneous Migration Considerations Objects in Oracle that do not have a complement in MySQL include: • TYPEs • Synonyms • Materialized Views (coming in upcoming version) • Dimensions • Sequences (MySQL uses AUTOINCREMENT columns) • Bitmap Indexes • Global Partitioned Indexes (coming soon) • Packages (although MySQL does have stored procedures) • Java Stored Procedures • DDL and Statement-Based Triggers (MySQL has Row-Based)
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
45
Migration Toolkit – How Does it Work? 1. 2. 3. 4. 5. 6.
Connect to and Reverse Engineer Oracle Source Connect to MySQL Target Select Schemas/Objects to Migrate Customize MySQL Target Schemas/Objects Transform Oracle Schemas/Objects to MySQL Schemas/Objects Move Data from Oracle to MySQL
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
46
Oracle-to-MySQL Object Translation Schema
Migration Toolkit
Database
Table
Migration Toolkit
Table
Index
Migration Toolkit
Index
View
Migration Toolkit
View
Stored Procedure
manual
Stored Procedure
Function
manual
Function
Trigger
manual
Trigger
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
47
Every Oracle schema becomes a MySQL database. Stored Procedures, Functions and Trigger must be re-written manually and converted from PL/SQL to ANSI SPL.
Other Oracle->MySQL Migration Considerations • For large data migrations, ensure all proper MySQL parms are set (Migration Toolkit will do automatically for you). • Enable sql_mode option in MySQL 5.0 and above for maximum data integrity • Look into 3rd party management tools (Quest, Embarcadero, etc.) to manage both Oracle & MySQL • Ensure to mirror Oracle needs in MySQL (autogrowth files, binary logging for point-in-time recovery, etc.) • Ensure MySQL memory defaults are not used for demanding systems • Look into MySQL Enterprise Monitor to manage multiple MySQL Servers • When tuning migrated SQL code, utilize slow query log to catch bad SQL • Periodically check MySQL error log just like Oracle alert log • Look to MySQL Enterprise for production/data center applications
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
48
Every Oracle schema becomes a MySQL database. Stored Procedures, Functions and Trigger must be re-written manually and converted from PL/SQL to ANSI SPL.
Apps
Third Party Support – Standard Apps eShop & Commerce
Content Management
Session Management
User Directory
Blogs & Forums
Search Engines
Tools
Administration Transactional Replication Application Development Business Intelligence
Platforms
Database App – Web Servers & Load Balancers Storage Operating System Hardware
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
49
Apps
Third Party Support – Data Warehouse Operational Data Store
Source Systems
ETL Process
Data Warehouse
Metadata Repository
Data Mart
Tools
Reporting & Analytics ETL Replication Development & Admin
Platforms
Database Storage (SAN – NAS) Operating System
Hardware
Copyright 2008 MySQL AB
The World’s Most Popular Open Source Database
50
For More Information • MySQL 5.1 Download Page: > http://dev.mysql.com/downloads/mysql/5.1.html
• MySQL 5.1 Technical White Paper > http://www.mysql.com/why-mysql/white-papers/whatsnew-
mysql-51.php
• MySQL Enterprise: > http://www.mysql.com/products/enterprise/
Copyright 2008 Sun Microsystems
MySQL - The Online Database of the 3rd Millennium
Talk about % of folks surveyed who have gone to 5.0
MySQL from Sun: The Platform for the Web Economy www.sun.com/mysql [email protected]
Thank you very much for your time today!
