MySQL versus Oracle Features/Functionality

I love working with MySQL and Oracle technologies.  Both of them are great solutions.  The point is to find the right environment to leverage what they do well for the best price/performance/scalability you are looking for.

  • MySQL is relatively light-weight, can be extremely fast when applications leverage architecture.  Lots of features stay free as the database servers grow such as replication and partitioning.
  • Oracle offers lots of feature/functionality for solving complex problems.  Supports large OLTP environments as well as VLDBs.

 Here are some of the feature comparisons from a DBA perspective.

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 3rd 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.

 From my perspective, you almost can't compare Oracle and MySQL because they are two different type of products.

  • Oracle has tons of features from XML, user-defined types, lots of database management tools.  Oracle:
    • Can be used with Oracle Express, a database that has tons of functionality for the beginning project to running the largest business applications in the world. 
    • Has tons of features that usually minimize the need for 3rd party software.
    • Oracle is currently placing a major emphasis in two areas: 
      1. Enterprise business applications running Oracle Business Suite, Siebel, JD Edwards, PeopleSoft, Retek, etc.  Oracle's next generation Oracle Fusion Applications are going to have a major impact on business applications in the next few years.
      2. Oracle Fusion Middleware and the technology surrounding the middle-tier with application servers, J2EE, BPEL, SOA, XML, Web Services.  The ability to get disparate applications to work together are a key to Oracle Fusion applications.  Oracle's purchase of BEA shows the emphasis Oracle is placing on the middle-tier to run business applications.
  • MySQL excels when high speed reads can be used for web, gaming and small/medium data warehouses and OLTP systems.   MySQL:
    • Can also be used with startup companies and smaller projects because it can be simple to use at a low cost. 
    • Does not have a fraction of features of Oracle but for the companies using MySQL they are often building most of the functionality in the middle-tier and do not need lots of functionality in the database. 
    • Does not have a lot of the surrounding software to support a MySQL environment.  3rd party software and open software are often used to run MySQL in an enterprise environment.  Nagios and Memcache are used a lot in MySQL environments.
In this blog I am not trying to say MySQL or Oracle is better than the other.  They both have features where customers can benefit.   I enjoy working with both and would highly recommend both database servers.


Comments:

1.Oracle VS Mysql
Oracle 11G Mysql 5
1.Installation very dam slow. Very Fast
2.Create database 15 minute? Very Fast
\*\* Replication of db takes time on mysql but also fast
3.RAM min 250 ram after tweak Min 20 mb Larger application larger ram
4.Store proc cool Lame store proc.Cannot parse array to function/proc .Since table is array?Need oop here.
5.installtion file to large? small
6.Tweak sucks.installation again just open the mysql administration
7.The most worst one database one service in windows ? One service only(windows
\*\* The non programmer view

Posted by hafizan on May 29, 2008 at 05:34 PM MDT #

I enjoyed a lot your comparison between mysql and Oracle, I would give more importance to the power of Mysql on multicharacter enviroments in international projects, like international websites :). You can give a table, even a column is own characterset and collation, in Oracle this character set is more archaic and complex with the NLS configuration, and you can't give a column a codification different that the table or database!

Posted by Pedro on May 30, 2008 at 06:02 AM MDT #

Hi George, still teaching Oracle courseware?

I like the comparisons for the most part, but it DOES seem to make light of the weaknesses of mysql and overplays some of its features that turn out to be pretty darn weak. I mean, shucks, even if you move everything but data storage out of the database, you're still left with protecting the data... The standard response to the lack of industrial strength backup and recovery (especially in replicated environments) is "it's really good for read-only environments. It is so fast with them!"

At the end of the day, I just don't see read-only web apps... in fact, they tend to write a lot of little stuff in most situations, and those darn users still don't seem to like having their data go missing, and those stuffy business owners don't like to loose things like requests for more information and orders.

My point? \*\*Data recovery is king.\*\*
If you're doing something where you care enough to store it in a database in the first place you need a database that is dependable, reliable and able to recover well from failures internally and from external failures.

In the "$" space, I see one database that stands out here -- Ingres (ingres.com). You could also make the argument that Oracle's Express edition has a play, but the other limitations and the lack of support throw that out the window IMHO. Otherwise, your only choice is Oracle to go to that next level. SQL-Server if you don't mind being stuck in the M$ vortex (NOT for me).

What do you think?

Posted by Mike S. on May 31, 2008 at 10:41 AM MDT #

Hi Mike!

Good hearing from you and good comments. I can appreciate your perspective and you raise a lot of good points. The objective of the feature comparison was to give people a feeling for each of the products. I didn't want to get into a detailed analysis of each product.

The point I was trying to make is that organizations often have different requirements for a project. Depending on the project, there are different options where I think both Oracle and MySQL can be a great solution. Oracle is the Mercedes Benz or Rolls Royce of databases and the features of RAC, RMAN and Streams can do a lot to protect your data. Yet, there are projects where MySQL can be a very strong solution at a fraction of the cost. Having flexibility from a cost perspective and as well as reduced management and relateive ease of administration can be a strategic advantage as well.

Ingres is an interesting story. You're right, there are some very solid features within Ingres. For that matter, look at Informix, it is a database server that is incredibly fast that has very interesting technology. Yet, for different reasons Ingres and Informix are not getting a lot of traction and I don't see them as gaining traction. Databases like Ingres and Informix are databases with intriguing and solid features that aren't gaining market share. As a "database" person, I'd have a hard time picking a database server that is not getting any traction in the market place. You are probably setting a project up for a database migration sometime in the future.

From my perspective on projects I see:
Oracle as the aircraft carrier of database servers, and I don't see that changing anytime soon. Oracle's efforts to grow in the middle-tier and with business applications has them firmly entrenched for years.
SQL Server and DB2 I see organizations pick because they are a Microsoft or IBM shop and I don't see that changing much in the near future.
MySQL right now has the tipping point among database servers. They are now stepping into deeper waters with absolutely incredible potential, but at the same time the deeper the waters, the bigger the sharks.

Posted by George Trujillo on June 01, 2008 at 11:39 AM MDT #

Hi all, does anyone know compare info about audit? Tks. Rgds.

Posted by Jorge Sanchez on September 25, 2008 at 06:55 PM MDT #

Hi,

Which is the most count of dates I could move with MySQL?, I want to work with 3,000,000 of dates approximately, it could be possible with mysql or do i need oracle, this is to work with data minning, which of these will you recommend me.......?

Posted by Ana on October 22, 2008 at 09:59 AM MDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

This blog is my perspective of Sun in relation to Oracle, MySQL, Oracle Fusion Middleware, Education and Virtual Reality platforms. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today