Music@life

Features/Functionality

MySQL

Oracle

Strengths

Price/Performance Great performance when applications leverage architecture.

Aircraft carrier database capable of running large OLTP and VLDBs.

Database Products

Enterprise ($) – supported, more stable.

Community (free) – more leading edge.

Enterprise ($$$$)

Standard ($$)

Standard One  ($)

Express (free) – up to 4GB

Application Perspective

Web applications often don’t leverage database server functionality. Web apps more concerned with fast reads.

More you do in the database the more you will love Oracle with compiled PL/SQL, XML, APEX, Java, etc.

Administration

Can be trivial to get it setup and running.  Large and advanced configurations can get complex.

Requires lots of in-depth knowledge and skill to manage large environments.  Can get extremely complex but also very powerful.

Popularity

Extremely popular with web companies, startups, small/medium businesses, small/medium projects.

Extremely popular in Fortune 100, medium/large enterprise business applications and medium/large data warehouses.

Application Domains

(most popular)

Web (MySQL excels)

Data Warehouse

Gaming

Small/medium OLTP environments

Medium/Large OLTP and enterprise applications.  Oracle excels in large business applications (EBS, Siebel, PeopleSoft, JD Edwards, Retek, ...)

Medium/Large data warehouse

Development Environments

(most common)

1) PHP

2) Java

3) Ruby on Rails

4) .NET

5) Perl

1) Java

2) .NET

3) APEX

4) Ruby on Rails

5) PHP

Note: Oracle focusing on Java for next generation business applications. 

Database Server

(Instance)

Database Instance stores global memory in mysqld background process.

User sessions are managed through threads.

Database instance has numerous background processes dependent on configuration.  System Global Area is shared memory for SMON, PMON, DBWR, LGWR, ARCH, RECO, etc.

Sessions are managed through server processes.

Database Server

(Physical Storage)

Made up of database schemas.

Each storage engine stores information differently.

Common storage engines:

MYISAM – stores data in .FRM, .MYD and .MYI files.

InnoDB – stores data in a common tablespace or individual tablespaces per table.

Binary logs are used for point-in-time recovery

Uses tablespaces for system metadata, user data and indexes.  Common tablespaces include:

SYSTEM

SYSAUX

USER DATA

USER INDEXES

TEMPORARY

UNDO

Redo and archive log files are used for point in time recovery.

Tables

Tables use storage engines.  Each storage engine provides different characteristics and behavior.

A few tables with tons of features.

Partitioning

Free, basic features

$$$ with lots of options

Replication

Free, relatively easy to setup and manage.  Basic features but works great.  Great horizontal scalability.

$$$, lots of features and options.  Much higher complexity with a lot of features.  Allows a lot of data filtering and manipulation.

Transactions

InnoDB and upcoming Falcon and Maria storage engines

Regular and Index only tables support transactions.

Backup/Recovery

No online backup built-in.

Replication

OS Snapshots

InnoDB Hot Backup

Recovery Manager (RMAN) supports hot backups and runs as a separate central repository for multiple Oracle database servers.

Export/Import

Easy, very basic.

More features.

Data Dictionary (catalog)

Information_schema and mysql database schemas offer basic metadata.

Data dictionary offers lots of detailed information for tuning.  Oracle starting to charge for use of new metadata structures.

Management/Monitoring

$, MySQL Enterprise Monitor offers basic functionality.

Additional open source solutions.

May also use admin scripts.

$$$$, Grid Control  offers lots of functionality.

Lots of 3^rd party options such as BMC, Quest, Embarcadero and CA.

Storage

Each storage engine uses different storage. Varies from individual files to tablespaces.

Tables managed in tablespaces.  ASM offers striping and mirroring using cheap fast disks.

Stored Procedures

Very basic features, runs interpreted in session threads.  Limited scalability.

Advanced features, runs interpreted or compiled.  Lots of built in packages add significant functionality.  Extremely scalable.