MySQL Overview: Technology etc. etc.

judgedrunkshipΔιακομιστές

17 Νοε 2013 (πριν από 3 χρόνια και 8 μήνες)

198 εμφανίσεις

1

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database





MySQL
Overview:


Technology etc. etc.



John Bradford

Senior Sales Engineer

jbradford@mysql.com


2

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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


3

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Database Market Share: Revenue


IBM, Oracle, & Microsoft are Leaders


Gartner, May 2005


Notes:



Majority of IBM share resides on mainframe (z/OS)



Overall market grew 10.4% in 2004


Oracle, 33.70
IBM , 34.10
Microsoft,
20.00
Teradata, 2.90
Others, 7.00
Sybase, 2.30
4

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Yahoo Futures
-

Databases

http://buzz.research.yahoo.com

5

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Open Source Adoption


75% Using or Considering Using Open Source

6

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Linux Adoption


$35 billion in revenues by 2008 (Source: IDC)

Revenue Growth
2Q 2004
-10.00%
0.00%
10.00%
20.00%
30.00%
40.00%
50.00%
60.00%
Windows
Unix
Other
Linux

Revenue
2Q 2004
0
1000
2000
3000
4000
5000
Windows
Unix
Other
Linux
$ Millions
Source: Gartner

7

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Database Adoption on Linux


Database Revenue on Linux grew 118% to $650M in 2004

8

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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 1
st


9

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Performance


Part 2

10

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Popular Technology Stacks



MySQL

Linux

Apache

MySQL

MySQL

Linux

or

Solaris

Windows

Apache

Tomcat

JBoss

IIS

Apache

JBoss

Java

.net / C#

Perl

LAMP

J2EE

.NET

11

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Open Source Timeline

1986

Larry Wall

develops

Perl

1991

FreeBSD 1.0

Released

1993

Linus Torvalds

creates Linux

Van Rossum

releases

Python

1990

1994

Lerdorf

releases

PHP

1994

1995

MySQL

releases

first

GPL version

Apache

Web Server

is released

Etc…

12

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Source: Forrester Research

Open Source Databases

13

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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

14

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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


Average 40,000 downloads per day.


More alpha and beta users means a better product.


Some customers already using v5 in production!


v5

15

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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

16

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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

“Reasoning's inspection study shows
that the code quality of MySQL was
six times better than that of
comparable proprietary code.




17

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

MySQL Network



Scripts/Manual



Freeware



Enterprise Class

The best of both worlds: Open Source Freedom and Software Protection


Publicly Available




MySQL Software (community edition)



Release early & often



Bleeding edge



MySQL Network




MySQL Software



Certified Software



Maintenance updates



Update Advisor



Technical Alert Advisor



Knowledge Base



Production Support



Indemnification



From the developers that built MySQL

18

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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


Suse Enterprise Server 9

Windows Server 2000/2003



Intel
-
IA32 (Xeon)



Intel
-
IA64 (Itanium)

Solaris 9/10



Sparc 32/64

HP UX 11/11i



Intel
-
IA64 (Itanium)



Intel
-
IA32 (Xeon)



Intel
-
IA64 (Itanium)



Intel
-
EMT32/64 (Nacona)



AMD
-
64 (Opteron)



Intel
-
IA32 (Xeon)



Intel
-
IA64 (Itanium)



Intel
-
EMT32/64 (Nacona)



AMD
-
64 (Opteron)

19

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

MySQL Software Priorities

Lower

TCO

* Robert Lemos CNET News.com Feb 4, 2005

Reliability

Study found
comparatively fewer
bugs in MySQL*

Ease of Use

15 Minute Rule

Up and running in 15 minutes

Performance

MySQL, Sun and BEA WebLogic Set
New World Records for Speed &
Price/Performance in SPEC
Benchmarks

20

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

MySQL Feature Highlights

ANSI
-
Compliant 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

21

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

What’s New?



MySQL 5.0



Stored Procedures



Cluster query push down



Query optimizations



Archive Engine



InnoDB storage improvements



SQL Mode



Triggers



Views



Precision Math



Distributed Transactions



Cluster object support



Migration Toolkit



Information Schema



Cursors



Enhanced GUI Tools


Faster Better Increased


Manageability

Ease of Use

Reliability

Performance

Now
GA!

22

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

MySQL Performance: 5.0 vs. 4.1

23

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

MySQL Performance: 5.0 vs. Others

24

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

MySQL Architecture

25

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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



-

Replication

-

OLTP




-

Online Backups

-

Transactions



-

Data Warehousing

-

Performance



-

Foreign Keys

-

Scalability




-

Small Footprint

-

Level of Concurrency


-

Row Level Locking

-

Indexes Types



-

Embedded

-

Storage Utilization



-

Table Level Locking

-

High Availability



-

Clustering


And if you change your mind?



mysql> ALTER TABLE mytable ENGINE=MyISAM;

26

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

*
In MySQL 5.0 transactions are supported, however, the partial rollback of a transaction is not supported. Cluster supports th
e
READ_COMMITTED, REPEATABLE_READ, and SERIALIZABLE transaction isolation levels.


*

Pluggable Storage Engine Architecture

27

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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

Select *

From LocalTab a,


RemoteTab b

Where a.col1=b.col1…

MySQL

Server A

MySQL

Server B

Federated

Engine


28

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Federated Engine

Select *

From LocalTab a,


RemoteTab b

Where a.col1 = b.col1 …

MySQL

Server A

MySQL

Server B

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

29

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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 |

+
----------
+

Federated Engine

30

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Federated Engine

MySQL

Server A

MySQL

Server B

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

Select *

From LocalTab a,


RemoteTab b

Where a.col1 = b.col1 …

31

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Business Benefits


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!

Notes of Interest


Insert and Select only (no Updates/Deletes)


Very good for security needs; e.g., auditing


No indexes in current release


More capabilities to come…

Archive Engine

32

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Business Benefits




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

Note of Interest



Can handle standard output for SELECT, while
Oracle cannot…


Non
-
robust error handling/exits

Stored Procedures

Create Procedure P_1()

Begin


SELECT * FROM EMP;

End;

33

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Stored Procedure Example

34

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Business Benefits



Audit user activities on database objects


Enforce business logic on user’s actions


Reduces network traffic in data centers


Familiar syntax aids switchover costs

Note of Interest



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)

Triggers

Create trigger t_1()

Before insert on EMP

FOR EACH ROW

SET @C1 = NEW.C1 * .10;

35

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Trigger Example

36

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Business Benefits



Provide security layer over sensitive data


Simplify access to complex data


Provide ‘friendly’ name to ugly
-
named tables

Note of Interest



Updateable only if single table views (and still
restrictions apply)

Views

37

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

View with Data Encryption Example

38

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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

Other XA RM


Included in app TX

TX Scope

MySQL

XA Engine1


MySQL acts as TM


Storage engines act
as RM

XA Engine 2

TX Scope


Different XA engine
can be in same TX

5.1

5.0

39

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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


MySQL S
1

MySQL S
n

Client Access



Server “Group” use Same Virtual IP Address



Load Balance New Clients to Servers

Node 1

Node Group

Node 2

Node 3

Node 4


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



All queries load balanced between Nodes



NDBCluster storage engine in MySQL
handles load node selection

Management Node



Config, start, stop of Nodes

40

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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

MySQL Server



Stores binary TX logs



Slave index tracking

MySQL Server

Client Access



Write queries sent to MASTER



Application balances reads on SLAVEs

MASTER

MySQL Server

MySQL Server

SLAVE 1

SLAVE 2

SLAVE n

Linux

Windows or other OS

. . .

...

41

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

MASTER
-
MASTER Replication Architecture


You can achieve the benefits of a shared
-
storage
architecture without shared storage.

MySQL Server



Configured as MASTER



Configured as SLAVE

Client Access



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 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

ACTIVE

MySQL Server



Configured as MASTER



Configured as SLAVE

PASSIVE

42

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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

43

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

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…

44

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database

Questions?

John Bradford

jbradford@mysql.com

Local User Groups:

http://dev.mysql.com/user
-
groups

2006 User Conference:

http://www.mysqluc.com/

45

Copyright 2005 MySQL AB

The World’s Most Popular Open Source Database