MySQL Storage Engine Overview

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

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

63 εμφανίσεις

1

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database





MySQL

Storage Engine Overview




Dr. Charles A. Bell

Senior Software Developer

cbell@mysql.com


2

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Agenda


MySQL AB


Technology Stacks


MySQL Server Architecture


Pluggable Storage Engines


Server Internals


Q & A




3

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database



Founded in 1995


Operations in 22 countries


10+ million product installations


50,000 downloads each day


Dramatically reduces TCO of database management


Bundled by more than 100 SW and HW companies


Sold by partners such as HP, Dell, Novell, and others

MySQL AB

4

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Chosen by Successful, Modern Companies



Embedded


”Batteries included” database in
software applications


Network elements




High Volume Web Sites


Dynamic content


eCommerce


Gaming & entertainment


Scale Out




Enterprise


Data Warehousing


High
-
Volume Transactions


Departmental


Intranet/Extranet


Scale Out

5

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

6

Copyright 2006 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.




7

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

8

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Supported Technology Platforms


Operating

System

Web &
Application

Server

Database

Hardware

Storage



Internet
Information

Server

Apache

Tomcat



Programming

Languages

Perl







HP
-
UX

9

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

MySQL Architecture

10

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











11

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Pluggable Storage Engine Architecture


Storage Engines are available on a
per table

basis


Changing from one storage engine to another can be
done via a simple SQL command:



ALTER TABLE mytable ENGINE=MyISAM;

Innodb

MyISAM

12

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

13

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Storage Engines


MyISAM


Default MySQL engine


No practical limits on data storage


Very efficient storage


Easily handles high
-
speed data loads


Has B
-
tree, R
-
tree, and Full
-
text Indexes


Supported by special index memory caches


Offers compressed data option


Supports geospatial operations


Uses table level locks


Does not do transactions


Backup/point
-
in
-
time recovery supported

Fast Facts

Best Use Cases


High
-
traffic Web sites


Data warehouses

MySQL Server

MyISAM

14

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Storage Engines


InnoDB


Provides ACID transaction support


64TB data storage limit per tablespace


Higher storage cost


Slower data load speed than most other engines


Offers MVCC/Snapshot read


Has B
-
tree and clustered indexes


Supported by special data & index memory caches


Provides foreign key support


Does not offer compressed data option


Uses row level locks and has custom isolation levels


Has crash recovery


Backup/point
-
in
-
time recovery supported

Fast Facts

Best Use Cases


Online transaction processing applications

MySQL Server

Innodb

15

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Storage Engines


Cluster (NDB)


Transaction support


All data and index reside in main memory


Memory limitation removed for tables in 5.1


High data load speed


Offers MVCC/Snapshot read


Has B
-
tree indexes


Very fast primary key lookup capabilities


Offers 99.999% uptime


Shared nothing architecture


Has high
-
speed API for access as well as SQL API


Online backup/point
-
in
-
time recovery supported

Fast Facts

Best Use Cases


Highly available, always
-
on/up applications


Fast directory/key lookup applications

MySQL Server

Cluster

(NDB)

16

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Storage Engines


Archive


New in 5.0


Provides automatic data compression


Offers storage savings up to 80%


No practical storage limit


Fastest data load speed of any storage engine


Offers MVCC/Snapshot read


No index support


Has special insert buffer for fast insert speed


Only supports INSERT and SELECT operations


Uses row level locks


Backup/point
-
in
-
time recovery supported

Fast Facts

Best Use Cases


Historical data warehouses


Data archiving applications


Data auditing

MySQL Server

Archive

Archive

17

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Storage Engines


Federated


New in 5.0


Allows creation of one logical database from many physical


Acts as “pointer” from one database to another target object


No special middleware needed for remote data access


Speed of operations depends on network/misc. factors


Actions constrained by target engine object properties


Security handled through federated table definition


All SQL operations supported (as per target object)

Fast Facts

Best Use Cases


Distributed database environments


Data Mart environments

MySQL Server

Federated

18

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Storage Engines


Other


Memory tables reside in RAM; data is lost on shutdown


Memory tables support both B
-
tree and hash indexes


BDB tables offer transaction support with COMMIT/ROLLBACK


Merge tables are collections of underlying MyISAM tables


Merge tables offer one form of data partitioning


Custom storage engines may be plugged into MySQL

Fast Facts

Best Use Cases


Memory: fast lookups for data objects


BDB: Online transaction processing


Merge: Large databases with partitioned data


Custom: special application situations

MySQL Server

Memory

BDB

Merge

Custom

19

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Storage Engines


Coming Soon

New Transactional Storage Engines:



Maria



Falcon



others still…

MySQL Server

Memory

BDB

Merge

Custom

20

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

MySQL Architecture


Parser/Optimizer


Validates user’s privileges on
accessing database objects and
executing SQL calls.


Converts all SQL calls to internal
database language.


Decides how best to service user’s
SQL request.


Supports all storage engines, which
means no special coding per storage
engine for particular needs.

Parser


Query Translation,


Object Privilege


Optimizer


Access Paths,

Statistics


21

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Q & A



22

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Query Trees & Query Execution

An Alternative Query Execution Mechanism

23

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Query Trees


(job = ‘Manager’)


(name=‘Sales’)
(EMP
emp.deptno = dept.deptno

DEPT)

EMP

DEPT


(job = ‘Manager’)


(name=‘Sales’)

emp.deptno = dept.deptno

Alternative
‘query

tree’

for

the

query

to

list

all

the

managers

that

work

in

the

sales

department
:

24

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Query Trees


Tree nodes are atomic operations


Project


Restrict


Join


Sort


Each node has 0
-
2 inputs


From 0
-
2 relations directly


From 0
-
2 children


25

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Query Trees

Π

Φ

Σ

Σ

SELECT Col1, Col2

FROM A JOIN

(SELECT * FROM C WHERE ColB = 7)

ON ColC

WHERE A.Col3 > 14

26

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Optimization Techniques


Cost
-
Based


Statistics, statistics, statistics


Manual offline computation


Piggy back


Strategies


Even distribution


Heuristic


Knowledge of operations


“Works Best”


Semantic


Knowledge of schema


Decisions based on behavior/relationships

27

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Heuristic Optimization Example

EMP

DEPT



(job =



Manager



)



(name=



Sales



)

emp.deptno = dept.deptno

EMP

DEPT



(job =



Manager



)



(name=



Sales



)

emp.deptno = dept.deptno

EMP

DEPT



(job =



Manager



)



(name=



Sales



)

emp.deptno = dept.deptno

EMP

DEPT

emp.deptno = dept.deptno



(job =



Manager



)



(

name=



Sales



)

EMP

DEPT

emp.deptno = dept.deptno



(job =



Manager



)



(

name=



Sales



)

EMP

DEPT

emp.deptno = dept.deptno



(job =



Manager



)



(job =



Manager



)



(

name=



Sales



)

EMP

DEPT

X



(job =



Manager



)



(name=



Sales



)



(emp.deptno = dept.deptno)

EMP

DEPT

X



(job =



Manager



)



(name=



Sales



)



(emp.deptno = dept.deptno)

EMP

DEPT

X



(job =



Manager



)



(name=



Sales



)



(emp.deptno = dept.deptno)

Optimised

Canonical Query

28

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Heuristic Algorithm

void QueryTree::HOptimization()

{


SplitRestrictWithJoin(Root);


SplitProjectWithJoin(Root);


SplitRestrictWithProject(Root);


pNode = FindRestriction(Root);


while (pNode != 0)


{



PushRestrictions(Root, pNode);



nNode = FindRestriction(Root);


}


pNode = FindProjection(Root);


while (pNode != 0)


{



PushProjections(Root, pNode);



nNode = FindProjection(Root);


}


pNode = FindNaturalJoin(Root);


while (pNode != 0)


{



PushNaturalJoins(Root, pNode);



nNode = FindNaturalJoin(Root);


}


BalanceJoins(Root);


PruneTree(0, Root);

}

29

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Optimization Using Query Trees

SELECT

P.
Name

FROM

Professor

P,
Teaching

T

WHERE

P.Id = T.
ProfId



AND

P.
DeptId

= ‘CS’
AND

T.
Semester

= ‘F1994’




Name
(

DeptId=‘CS’


Semester=‘F1994’
(
Professor

Id=ProfId

Teaching
))



Name




DeptId=‘CS’


Semester=‘F1994’




Id=ProfId

Professor

Teaching

30

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Query Optimizer


Heuristic optimization eliminates most inefficiencies
and generates near optimal queries which can be
executed directly from the internal representation
(query tree) without modification.


Test


Replace SELECT
-
PROJECT
-
JOIN optimizer in MySQL with
Heuristic Query Optimizer.


To find out the results you need to…


31

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Buy my book!

Expert MySQL

Since the enormous number of new features made available
with MySQL release 5.0, MySQL has been gaining steam as a
viable alternative to database behemoths like Oracle and IBM
DB2. MySQL users now have the ability to extend MySQL with
new SQL commands, optimize query execution, and embed
MySQL within low
-
resource environments like embedded
devices and kiosks.


Expert MySQL
, by Dr. Charles A. Bell, is the first book to
examine these opportunities in detail, showing you how to wield
maximum control over this powerful open source database.
You’ll learn how to create your own custom storage handlers,
ensuring maximum flexibility and speed within your specialized
applications. You’ll also gain valuable insight into MySQL’s
architecture and learn how to tweak its behavior through
custom changes to the source code.

32

Copyright 2006 MySQL AB

The World’s Most Popular Open Source Database

Q & A