Transcript MySQL Cluster - UniForum Chicago

MySQL Overview:
Technology etc. etc.
John Bradford
Senior Sales Engineer
[email protected]
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
1
Perspective
“We're both in the transportation business. We have a
747, and they have a Toyota.”
Comparison of Oracle and MySQL made by Oracle president, Charles
Phillips, at the Vortex Conference in October 2004
“There are many more Toyotas sold than 747s. Also,
Toyota is a very profitable company.”
MySQL CEO Marten Mickos, responding to Mr. Phillips’ statement
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
2
Database Market Share: Revenue
IBM, Oracle, & Microsoft are Leaders
Sybase, 2.30
Others, 7.00
IBM , 34.10
Teradata, 2.90
Microsoft,
20.00
Oracle, 33.70
Gartner, May 2005
Notes:
• Majority of IBM share resides on mainframe (z/OS)
• Overall market grew 10.4% in 2004
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
3
Yahoo Futures - Databases
http://buzz.research.yahoo.com
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
4
Open Source Adoption
75% Using or Considering Using Open Source
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
5
Linux Adoption
$35 billion in revenues by 2008 (Source: IDC)
Revenue
Revenue Growth
2Q 2004
2Q 2004
60.00%
5000
50.00%
$ Millions
4000
40.00%
3000
30.00%
2000
20.00%
1000
10.00%
0
0.00%
Windows
Unix
Other
Linux
-10.00%
Window s
Unix
Other
Linux
Source: Gartner
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
6
Database Adoption on Linux
Database Revenue on Linux grew 118% to $650M in 2004
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
7
Performance – Part 1
• eWeek’s database benchmark test showed:
– MySQL has top overall performance and scalability
– MySQL excelled in stability, easy of tuning, and connectivity
– MySQL offered high throughput – tied for 1st
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
8
Performance – Part 2
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
9
Popular Technology Stacks
LAMP
J2EE
.NET
Java
.net / C#
Perl
MySQL
Copyright 2005 MySQL AB
MySQL
Apache
Apache
Tomcat
JBoss
Linux
Linux
or
Solaris
MySQL
IIS
Apache
JBoss
Windows
The World’s Most Popular Open Source Database
10
Open Source Timeline
Larry Wall
develops
Perl
Linus Torvalds
creates Linux
1986
1991
1990
Van Rossum
releases
Python
Apache
Web Server
is released
1994
1993
FreeBSD 1.0
Released
MySQL
releases
first
GPL version
1995
1994
Lerdorf
releases
PHP
Etc…
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
11
Open Source Databases
Source: Forrester Research
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
12
Evans Data Corp
SANTA CRUZ, CA, October 18, 2005 - Open source database deployments are up more
than 20% in the last six months, according to Evans Data’s Fall 2005 Database
Development Survey. MySQL use, for example, has increased by more than 25% in six
months and is approaching a majority in the database space, with 44% of developers
using the open source database.
Database security is an important facet of database development, Evans Data found that
proprietary database servers are almost twice as likely to have suffered a security
breach in the last year compared to open source database servers. The most likely security
breach for a proprietary database was a network intrusion and, for open source databases,
the most likely breach was a user authentication breach.
“We continue to see the maturation of open source databases reflected by the continually
increasing levels of adoption,” said John Andrews, Evans Data’s President. “In a number of
our ratings categories, we’re seeing open source databases meeting or exceeding
proprietary databases.”
Overall deployments of open source databases have grown 20 per cent.
Source: http://www.evansdata.com/n2/pr/releases/EDCDB05_02.shtml
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
13
The World’s Most Popular Open Source DB
• Exceeded the 2M download mark for v5 in August.
• As of September 22, 2005:
– MySQL Server 5.0.x
• 2,213,943 total downloads
• 1,007,795 in 2005
– MySQL Server 4.1.x
• 7,277,437 downloads
• 5,701,907 in 2005
v5
• Average 40,000 downloads per day.
• More alpha and beta users means a better product.
• Some customers already using v5 in production!
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
14
MySQL Products
DB Server
Connectors & APIs
GUI Tools
Linux
Solaris
FreeBSD
OpenBSD
MacOSX
HP-UX
AIX
Netware
SCO
Irix
QNX
Windows
Connector/J
Connector/MXJ
Connector/ODBC
Connector/NET
C API (included in the
server)
Query Browser
Administrator
Migration Toolkit
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
15
Second Generation Open Source
•
MySQL AB is a profitable company
– Develops the software in-house; community helps test it
– Owns source code, copyrights and trademarks
– Targets the “commoditized” market for databases
•
“Quid Pro Quo” dual licensing for OEM market
– Cost-effective commercial licenses for commercial use
– Open source GPL license for open source projects
•
Annual MySQL Network subscription for Enterprise, Web and OEM
development/testing
– Per server annual subscription
– Includes support, alert and update advisors, Knowledge Base,
Certified/Optimized Binaries
•
MySQL Support
– Worldwide 24 x 7 support
– Training and certification
– Consulting
Copyright 2005 MySQL AB
“Reasoning's inspection study shows
that the code quality of MySQL was
six times better than that of
comparable proprietary code. ”
The World’s Most Popular Open Source Database
16
MySQL Network
The best of both worlds: Open Source Freedom and Software Protection
Publicly Available
• MySQL Software (community edition)
• Release early & often
• Bleeding edge
• Scripts/Manual
• Freeware
• Enterprise Class
Copyright 2005 MySQL AB
MySQL Network
 MySQL Software
 Certified Software
 Maintenance updates
 Update Advisor
 Technical Alert Advisor
 Knowledge Base
 Production Support
 Indemnification
 From the developers that built MySQL
The World’s Most Popular Open Source Database
17
MySQL Certified Binaries
•
•
•
•
Are available as part of MySQL Network ~ 2 times per year
Certified by MySQL engineers and QA process
Have passed security and defect analysis tests
New platforms added quarterly
Operating System
Certified Platforms
RedHat Enterprise Linux 3/4
 Intel-IA32 (Xeon)
 Intel-EMT32/64 (Nacona)
 Intel-IA64 (Itanium)  AMD-64 (Opteron)
Suse Enterprise Server 9
• Intel-IA32 (Xeon)
 Intel-EMT32/64 (Nacona)
• Intel-IA64 (Itanium)  AMD-64 (Opteron)
Windows Server 2000/2003
• Intel-IA32 (Xeon)
• Intel-IA64 (Itanium)
Solaris 9/10
• Sparc 32/64
HP UX 11/11i
• Intel-IA64 (Itanium)
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
18
MySQL Software Priorities
Performance
MySQL, Sun and BEA WebLogic Set
New World Records for Speed &
Price/Performance in SPEC
Benchmarks
Reliability
Ease of Use
15 Minute Rule
Study found
comparatively fewer
bugs in MySQL*
Up and running in 15 minutes
Lower
TCO
* Robert Lemos CNET News.com Feb 4, 2005
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
19
MySQL Feature Highlights
ANSICompliant SQL
Stored
Procedures
Triggers
Cursors
Sub-Queries
Joins
Datatypes
(varchar,
BLOB)
Security
Authentication
Data
Encryption
Data
Decryption
Views
Custom SQL
Functions
User-Defined
Functions
SQL
Functions
Geospatial
Support
B-Tree
Indexes
Hash
Indexes
R-Tree
Indexes
Clustered
Indexes
Full Text
Indexes
Point-in-Time
Recovery
Table Data
Compression
Row-Level
Locking
ACID
Transactions
Commit /
Rollback
Server-Based
Foreign Keys
Identity
Columns
ENUM
Columns
High-Speed
Data Load
Consistent /
MVCC Read
Management
Tools
Dynamic
Configuration
Replication
Support
Deadlock
Detection
Cluster
Support
Auto-Growth
Storage
UTF-8 /
Unicode
Full
Backup
Query
Cache
Performance
Tracing
SQL Parser
Query
Optimizer
Security SSL
Support
Lock
Isolations
OS Portable
Files
32/64 Bit
Support
24 Supported
Platforms
Index
Caches
Scale-Out
Capable
Precision
Math
Data
Dictionary
JDBC, ODBC,
.NET, API’s
Crash
Recovery
Logical
Backups
Trigger-Based
Foreign Keys
Memory
Tables
Storage
Engines
Table Data
Caches
ISV Cluster
Support
Client Access
Utilities
Compressed
Indexes
Enforced Data
Integrity
Embedded
Support
Connection
Pooling
User
Caches
Error
Logging
Migration
Tools
Table
Cache
EXPLAIN
Plans
Terabyte
Scale
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
20
What’s New?
Now
GA!
MySQL 5.0
Performance
•
•
•
•
•
Stored Procedures
Cluster query push down
Query optimizations
Archive Engine
InnoDB storage improvements
Faster
Copyright 2005 MySQL AB
Reliability
•
•
•
•
•
•
SQL Mode
Triggers
Views
Precision Math
Distributed Transactions
Cluster object support
Better
Ease of Use
•
•
•
•
Migration Toolkit
Information Schema
Cursors
Enhanced GUI Tools
Increased
Manageability
The World’s Most Popular Open Source Database
21
MySQL Performance: 5.0 vs. 4.1
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
22
MySQL Performance: 5.0 vs. Others
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
23
MySQL Architecture
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
24
Pluggable Storage Engine Architecture
• MySQL supports several storage engines that act as
handlers for different table types.
• Choose, create, or extend a storage engine that best
suits your applications unique requirements.
• What is most important to you?
- Read Intensive
- OLTP
- Transactions
- Performance
- Scalability
- Level of Concurrency
- Indexes Types
- Storage Utilization
- High Availability
- Replication
- Online Backups
- Data Warehousing
- Foreign Keys
- Small Footprint
- Row Level Locking
- Embedded
- Table Level Locking
- Clustering
• And if you change your mind?
mysql> ALTER TABLE mytable ENGINE=MyISAM;
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
25
Pluggable Storage Engine Architecture
*
* In MySQL 5.0 transactions are supported, however, the partial rollback of a transaction is not supported. Cluster supports the
READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE transaction isolation levels.
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
26
Federated Engine
What is it…?
• New storage engine in MySQL 5.0
• Purpose in life is to reference data on remote MySQL servers
• Distributed translation handled in DDL definition
• Accesses data in tables of any storage format of remote MySQL databases
• Sends queries over the network and formats return results
• Can create one logical database server from one-many physical servers
MySQL
Server B
MySQL
Server A
Select *
From LocalTab a,
RemoteTab b
Where a.col1=b.col1…
Copyright 2005 MySQL AB
Federated
Engine
The World’s Most Popular Open Source Database
27
Federated Engine
What are the benefits…?
• Can easily carry out data archiving/historical data offloading tasks
• No special middleware needed for remote access
• Transparent access for all DML actions and SELECT actions
• Ideal for integrating data without implementing a data warehousing scheme
Select *
From LocalTab a,
RemoteTab b
Where a.col1 = b.col1 …
Copyright 2005 MySQL AB
MySQL
Server A
Federated
Engine
MySQL
Server B
The World’s Most Popular Open Source Database
28
Federated Engine
Example...
mysql> CREATE TABLE client_transaction_hist (
-> client_transaction_id int(11) NOT NULL,
-> client_id int(11) NOT NULL,
-> investment_id int(11) NOT NULL,
-> action varchar(10) NOT NULL,
-> price decimal(12,2) NOT NULL
...
-> )ENGINE=FEDERATED
-> COMMENT='mysql://mysql:password@serv1:3306/gim/client_transaction_hist';
Query OK, 0 rows affected (0.14 sec)
mysql> CREATE VIEW client_transaction_all as
-> select * from client_transaction
-> union all
-> select * from client_transaction_hist;
Query OK, 0 rows affected (0.08 sec)
mysql> select count(*) from client_transaction_all;
+----------+
| count(*) |
+----------+
| 130725
|
+----------+
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
29
Federated Engine
What else do I need to know?
• Can only access other MySQL servers in 5.0
• Support for heterogeneous datasources coming
• Access speed can be negatively impacted by network influences
• Embedding passwords in federated definition is security risk
• Does not use query cache or transactions in 5.0
MySQL
Server B
MySQL
Server A
Select *
From LocalTab a,
RemoteTab b
Where a.col1 = b.col1 …
Copyright 2005 MySQL AB
Federated
Engine
The World’s Most Popular Open Source Database
30
Archive Engine
Business Benefits
Notes of Interest
• Helps retain historical data required by
government regulations
• Big performance gain; keep only archive
data on primary database
• Dramatic storage savings – reduces storage
costs:
– 1.6 Million Row MySQL Table
uncompressed: 112MB
– Compressed MyISAM 28MB (70+%
reduction)
– Archive 21MB - 80% reduction!
•
•
•
•
Copyright 2005 MySQL AB
Insert and Select only (no Updates/Deletes)
Very good for security needs; e.g., auditing
No indexes in current release
More capabilities to come…
The World’s Most Popular Open Source Database
31
Stored Procedures
Create Procedure P_1()
Begin
SELECT * FROM EMP;
End;
Business Benefits
Note of Interest
•
•
•
•
•
•
• Can handle standard output for SELECT, while
Oracle cannot…
• Non-robust error handling/exits
Embed business logic inside the database
Use database’s immense processing power
Reduces network traffic in data centers
Eases security administration
Allows pre-configured SQL tuning
Familiar syntax aids switchover costs
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
32
Stored Procedure Example
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
33
Triggers
Create trigger t_1()
Before insert on EMP
FOR EACH ROW
SET @C1 = NEW.C1 * .10;
Business Benefits
Note of Interest
• Audit user activities on database objects
• Enforce business logic on user’s actions
• Reduces network traffic in data centers
• Familiar syntax aids switchover costs
• Offers BEFORE and AFTER capabilities
• Does allow access to tables that the trigger
has not be defined on
• Does not work in cascading referential
integrity actions
• Only row-level in v5.0 (not statement level)
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
34
Trigger Example
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
35
Views
Business Benefits
Note of Interest
• Provide security layer over sensitive data
• Simplify access to complex data
• Provide ‘friendly’ name to ugly-named tables
• Updateable only if single table views (and still
restrictions apply)
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
36
View with Data Encryption Example
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
37
XA Support
• MySQL v5 supports distributed transaction processing:
– Two-phase commit with XA protocol available in InnoDB
– Commit grouping to improve performance
– XA JDBC driver
• In MySQL:
– the server acts as a transaction manager or resource manager
– storage engines can be implemented as a resource manager
Application
MySQL
 MySQL acts as RM
 Queries on ACID TX
tables included
5.0
Copyright 2005 MySQL AB
TX Scope
MySQL
TX Scope
Other XA RM
XA Engine1
XA Engine 2
 Included in app TX
 MySQL acts as TM
 Storage engines act
as RM
 Different XA engine
can be in same TX
5.1
The World’s Most Popular Open Source Database
38
MySQL Cluster For “Five 9’s” HA
•
•
•
•
•
Distributed, in-memory cluster and database
ACID transactions with check pointing, logging and recovery
No single point of failure, automatic fail-over
Enables increased capacity for reads and write actions
Database transparently fragmented over several nodes
Client Access
MySQL S1
• All queries load balanced between Nodes
• NDBCluster storage engine in MySQL
handles load node selection
• Server “Group” use Same Virtual IP Address
• Load Balance New Clients to Servers
Management Node
• Config, start, stop of Nodes
Node 1
Node 2
Node 3
Node 4
MySQL Sn
• Node Group acts as single “Unit” to MySQL
• Same data is replicated between all nodes
• Can have different Node Groups replicating
different “fragments” of data
• Each node resides on different machine
Node Group
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
39
MySQL Replication
• Basic replication architecture: designates one server
as a MASTER and other servers as SLAVES
• SLAVE “pulls” from server with dynamic reconfig
• Only pulls changes from binary log
Client Access
• Write queries sent to MASTER
• Application balances reads on SLAVEs
MASTER
MySQL Server
MySQL Server
• Stores binary TX logs
• Slave index tracking
SLAVE 1
MySQL Server
...
...
MySQL Server
Linux
Copyright 2005 MySQL AB
SLAVE 2
SLAVE n
Windows or other OS
The World’s Most Popular Open Source Database
40
MASTER-MASTER Replication Architecture
• You can achieve the benefits of a shared-storage
architecture without shared storage.
Client Access
ACTIVE
• All write queries sent to ACTIVE
• Application balances reads on BOTH servers
• Responsible for detecting node failure
• Can use OSS HA detection software: “heartbeat”
PASSIVE
MySQL Server
MySQL Server
• Configured as MASTER
• Configured as SLAVE
• Configured as MASTER
• Configured as SLAVE
• PASSIVE SLAVE “pulls” updates from ACTIVE MASTER
• ACTIVE SLAVE “waits” on PASSIVE MASTER, but nothing ever pulled
• If ACTIVE fails, PASSIVE becomes ACTIVE
• When the server re-boots, it becomes PASSIVE
• ACTIVE / PASSIVE designation determined merely by request routing
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
41
Ease Of Use
“Tackling the installation and setup of MySQL isn’t something that typically requires much effort,
regardless of the platform. As an example, the engineers of Embarcadero Technologies recently
compared the installation of an Oracle9i database on a new Red Hat Fedora Core machine with a
MySQL 4.0.18 install on the same machine. With Oracle, it took over three hours to find and
download all the necessary patches, configure the machine, and work through the installation.
With MySQL, the engineers were finished with their installation and at a MySQL client prompt,
ready to work, in seven minutes. After the installation, the setup and maintenance process is
typically straightforward and simple. MySQL provides a number of sample configuration files
that can quickly be used for different environments (small, large, etc.).”
Data Lifecycle Management Series — Migrating to MySQL
Embarcadero Technologies
August 2004
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
42
MySQL 5.1 – Alpha TBD
• Row-based replication
• Range, List, Hash and Sub partitioning
• Full Text Search Enhancements
• Join improvements
• Disk-based Cluster tables
• Replication support for Cluster
• Internal Scheduler
• Xpath (XML) Support
• And more…
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
43
Local User Groups: http://dev.mysql.com/user-groups
2006 User Conference: http://www.mysqluc.com/
Questions?
John Bradford
[email protected]
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
44
Copyright 2005 MySQL AB
The World’s Most Popular Open Source Database
45