Sunday, September 23, 2007

Looking at MySQL and Oracle















I am going to build a list of features, commands and concepts from MySQL and Oracle. This comparison will not go into the pros and cons or which database has more options or features. The goal is to give someone a feel for the differences. I am a big evangelist for Oracle and MySQL technologies. This is a list I will build over a period of time.

The following are websites for downloads, documentation, code, blogs, forums, whitepapers, newsletters, etc. Be careful about going to these sites you will get hooked and spend hours on these two great sites.
Feature/Functionality

The Oracle Database Server is the flagship of large enterprise databases. Oracle excels at the level of data warehouses or running large enterprise applications like the financials for a Fortune 100 company. At the same time MySQL is generating a lot of momentum because customers are seeing they can implement it at the fraction of the cost and complexity of Oracle. Each database has their place in the market in which they fit well.

MySQL does not have all the functionality of the Oracle database server, but that is also what makes it so attractive. Not everybody needs all the functionality that comes with the Oracle database server. Most companies I go to are paying for all of Oracle's functionality but only using a very small percentage of what it can offer. Also, as web applications become more popular more functionality is being built in the middle-tier versus using database functionality.

With MySQL it is either free or its commercial licenses are a fraction of Oracle's cost. MySQL environments usually require less hardware, resources and money to get them up and running. The cost comparison makes it a great option for Small and Medium Businesses (SMB), government and state organizations and for projects that do not need the enterprise flagship functionality of Oracle. As more and more web applications are being built, J2EE and Internet developers are building more functionality in the middle-tier, negating a lot of the functionality that comes with an enterprise database server.

I've been very impressed with how fast MySQL can process different types of SQL statements and how good of a job the optimizer does in the MySQL environment. The code is lighter and more efficient because it does not have as many options to wade through. Sometimes being light and nimble can offer tremendous advantages for application developers that understand how to leverage the functionality of MySQL.

Products

Below is a list of popular products used with MySQL and Oracle:
MySQL is an open source solution. Products like Eclipse, Apache and JBoss are used to offer a multi-tiered web solution using MySQL. MySQL usually uses other open source products to provide a full multi-tiered offering. LAMP or a derivative of LAMP is seen in MySQL multi-tiered environments.

Oracle offers a complete line of products with JDeveloper, Oracle Application Server, SQL Developer, etc. Third party products are available as well (i.e. Toad, SQL Navigator, UniCenter, ...). Open source products such as JBoss and Eclipse can also be used in Oracle environments. The big difference is that Oracle offers a large line of vendor commercial products. The table below only shows Oracle specific products under the Oracle section.


This table will provide an example of what a MySQL and Oracle environment may look like.













































MySQL Oracle
Eclipse Oracle JDeveloper
Struts, Spring
Application Development Framework
J2EE, PHP, Ruby on Rails,
Groovy, .NET
J2EE

Hibernate TopLink
Apache, JBoss Oracle Application Server
TCP/IP, Unix Sockets,
Shared Memory, NT Pipes
Oracle Net Services
Mysqladmin, MySQL Administrator,
Phymyadmin
Enterprise Monitor
OEM Database Control



MySQL Query Browser, Toad, NavicatOracle SQL Developer

mysql client tool
mysqlimport
mysqlcheck
mysqldump
myisamchk
myisampack
SQL*Plus client tool
SQL*Loader
dbverify
Data Pump, RMAN
dbms_repair package

MySQL (GPL)
MySQL Enterprise
Oracle Express
Oracle Standard
Oracle Enterprise
OpenLDAP, Fedora Directory Server Oracle Internet Directory


Note: The reader needs to understand the products are usually not always equivalent. Some examples include:
  • Oracle environments also support Ruby on Rails, .NET, Groovy, .NET, etc. However, the big push by Oracle is to use J2EE since Oracle Fusion applications run using J2EE. Also, organizations usually do not purchase a 100K or 1M Oracle license to run Ruby on Rails for their enterprise. Yet, Oracle does offer Oracle Express, a free database up to 4GB of storage where PHP, Ruby on Rails become a better option.
  • Mysqldump is a very simple tool for creating a backup. Oracle Data Pump and Recovery Manager (RMAN) are tools that are more enterprise level tools.
  • MySQL does not need additional networking software to work. It uses built-in networking protocols like TCP/IP. Oracle Net Services runs on top of TCP/IP. Oracle Net Services may or may not be free depending on what you are using on the client side. I realize Oracle has EZ Connect but it is not a replacement for Oracle Net Services.

Databases

One of the differences between MySQL and Oracle is how they treat the term database. A MySQL database is closer to an Oracle schema and all the storage for that schema. I’ll add an architectural picture of MySQL and Oracle that will help with this.

MySQL
  1. The MySQL physical server environment is made up of all the individual databases, binary log, and additional files and logs.
  2. A MySQL database is how to organize a group of database objects such as tables, views, triggers, etc.
  3. There would be a separate MySQL database for a human resource and a sales system to organize each set of objects separately.

Oracle:
  1. In Oracle the physical server environment is a database that encompasses all the physical files (storage) for a specific Oracle server (instance). This includes all files for data files, index files, redo log files, archive log files, etc.
  2. Oracle organizes database objects in a schema. A schema is an Oracle user and all tables, views, sequences, synonyms, stored procedures, etc. the objects and resources owned by the user.
  3. Oracle would use a separate “schema” for storing information for a human resource and a sales system if they were in the same database.
Summary:
A MySQL server environment is made up of server memory, background processes associated with multiple databases.

An Oracle server environment is made up of server memory and background processes (instance) associated with a single database organized with multiple schemas.

Startup Files
MySQL - The my.cfg or my.ini is the startup file for a MySQL server environment.

Oracle - An init.ora or spfile.ora file is used to start an Oracle server instance.

Metadata
The way MySQL and Oracle DBAs access metadata is similar but totally different. :)

With MySQL there are a large number of SHOW commands that will display detailed information on MySQL databases (data schemas), table structures etc. With Oracle the data dictionary views of USER_%, ALL_%, DBA_%, V$, etc. are used. The SHOW command in Oracle is only or the SQL*Plus client interface that will display local SQL*Plus variables in terms of how the SQL*Plus client is configured.

Both environments allow GLOBAL (instance) and LOCAL (session) variables to be defined. Below MySQL commands (mysql>) and Oracle commands (SQL) for accessing metadata will be compared.

-- Display server parameters.
mysql> SHOW VARIABLES;
SQL> SELECT name, value FROM V$parameter;

-- Set the sort buffer size.
mysql> SET GLOBAL sort_buffer_size=20 * 1024 * 1024;
SQL> ALTER SYSTEM SET sort_area_size=20971520;

-- Display information on triggers.
mysql> SHOW TRIGGERS LIKE 'fin%'\G
SQL> SELECT trigger_name, trigger_body FROM dba_triggers
WHERE trigger_name LIKE 'FIN%';

Storage Options
MySQL uses storage engines. Each of these storage engines deals with transactions, memory allocations, etc. differently. Very important a DBA understand the ramifications between the different types of storage. Some of the more popular storage engines include
  • MyISAM
  • InnoDB
  • Memory
  • Federated
  • Archive
  • Blackhole
  • Falcon - New transactional storage available in MySQL 6.0.
MySQL stores data for tables in flat files that reside on the disk. RAW is available but not used much any more.
Oracle does not have the equvalent of storage engines. Oracle supports some of the following types of table storage:
  • HEAP - regular table (default). Supports ACID transactions as well as being used able to be used for data warehouses.
  • IOT - Index organized table. Storage is in a b-tree structure.
  • External - Table definition that is mapped to an external file.
Oracle storage is managed in tablespaces. Tablespaces can store multiple data segments (tables) and index segments (indexes). Tablespaces organize Oracle storage structures. Tablespaces can store their data using RAW devices, operating system files and Automatic Storage Management (ASM). ASM uses Oracle internal storage mechanisms to write directly to physical devices without a file system. ASM can perform striping and mirroring using an Oracle Logical Volume Manager (ASM instance).


Creating Tables
Both MySQL and Oracle both support:
Regular tables
Indexes
Temporary tables
Partitioning

The big difference is that MySQL storage engines determine the functionality of MySQL tables. Another big difference is that MySQL uses AUTO_INCREMENT functionality to increment primary keys and unique identifiers. Oracle uses sequences to increment unique identifiers.

Stored procedures and triggers
MySQL and Oracle both support stored procedures and triggers. The MySQL stored procedure functionality is fairly new. However, the core of the language for writing stored procedures is available in MySQL. Oracle's procedural language has been around since 1991, so it obviously is more mature.

What's nice is that both of them use an ADA like syntax, so the core syntax of both languages looks almost exactly alike. Going from Oracle PL/SQL to MySQL's stored procedures would be a very easy transition.

7 comments:

Kamran said...

George,

Thank you for this excellent resource. I am coming from a 7-year background of Oracle database development, and only just beginning to move into the MySQL realm - make that *any* non-Oracle realm. You just confirmed a conclusion i was beginning to come to regarding MySQL's somewhat more cavalier use of the term "database" as opposed to Oracle - that a MySQL "database" is more akin to an Oracle "schema," and that a "user" does not necessarily equate to a MySQL database as it does to an Oracle schema. Things have begun to click since then.

I will be definitely visiting your blog often for updates - keep up the great work!

Thanks,
Kamran Saeed
kamran underscore saeed at yahoo dot com

George Trujillo said...

Kamran,

Thanks for joining in the blog. You're right, although MySQL does not have all the functionality of Oracle, it is not a trivial database to learn.

The way MySQL works is different than Oracle so you don't build things the same way. For example to name a few:
1) Stored procedures are stored globally in the library cache and can be natively compiled. MySQL stored procedures are at the session level and currently do not support native compilation.
2) The library cache in Oracle and the query cache in MySQL work differently.

The differences between MySQL and Oracle do require that if you are making the transition that sufficient time be spent learning how each architecture works.

bachkoutou said...

thank you for this really excellent ressource. In fact, I am searching a comparison between ORACLE and MySQL concerning the storage ( is there a limit on mysql table storage? can we partitionate table like in ORACLE ? ) and also about the flow, is it really that ORACLE database supports more an important flow ? ( i.e: I have 5 millions connexions on the database, would my MySQL server support this flow ? ) or do I need an ORACLE database ( cluster ? )
PS : excuse my English :)

George Trujillo said...

MySQL can support very large tables. I'd need to verify the upper limit but I believe it can be up to 64TB. So its no limit you are likely to encounter soon.

MySQL's architecture supports horiziontal scalability. Some of the largest Internet companies in the world are using MySQL to provide their web content. MySQL's ability to provide very high read rates with replication is very well known.

Anonymous said...

Very Good Knowledge Base for Oracle DBA's , trying to understand MySQL.

andrew said...

Great article. I happen to totally follow your philosophy! Thanks, Andy

Stefan Lasiewski said...

Thanks for this article. I was trying to find the MySQL and Oracle definitions of "database". You answered my question, and more.