Wednesday, July 1, 2009

Differences between Oracle and MySQL

Some key differences for DBAs between Oracle and MySQL database servers include:
  • Different tools used to manage and monitor database servers.
  • Oracle architecture is process based, MySQL architecture is thread based.
  • Different tools used for backup and recovery.
  • Database specific SQL syntax.
  • Database specific SQL functions.
  • Different syntax for stored routines. MySQL has no packages.
  • MySQL routines are not compiled and run in each session thread and not in global memory.
  • MySQL only supports row-level triggers.
  • Different startup and shutdown processes.
  • Oracle RAC is a shared disk solution while MySQL Cluster is a shared nothing solution.
  • The default configuration for MySQL is very lenient in terms of data integrity. A MySQL DBA must tighten down data integrity for it to work like traditional databases.
  • With Oracle, the CREATE DATABASE command is used to create the physical storage for the database server.
  • With MySQL, the mysql_install_db script is used to create the physical storage for the database server.
  • The term "database" in Oracle means all the physical files associated with an instance.
  • The term "database" in MySQL means a schema. The term database and schema can be used interchangeably in MySQL.
  • In Oracle a database user owns all the schema objects.
  • In MySQL the database schema owns all the schema objects.
  • Oracle supports role based security. With MySQL, scripts and stored routines are used to programatically organize security permissions.
  • Oracle has a lot more feature functionality that makes it very popular.
  • MySQL has a lot less functionality than Oracle that makes it very popular.
  • Oracle has tons of options for creating a table. Different characteristics and behavior are defined with these options.
  • The key with MySQL tables are defining a storage engine with a table. The storage engine defines characteristics such as row level locking versus table level locking, referential integrity, support for different types of indexes and features. Different optimizations, storage, tuning and backup and recovery are required for each storage engine. Storage engines are a KEY feature in MySQL.
  • MySQL supports different ISOLATION levels of READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ AND SERIALIZABLE.
Oracle DBAs are not just managing Oracle databases any longer. They are supporting, SQL Server, MySQL, Business applications, application servers in a lot of Oracle environments. The more software you can support in an organization the more marketable you are. Its important that Oracle DBAs be able to support the corporate environments that are running database software from multiple vendors and the software surrounding databases.

There are reasons that MySQL customers like the light weight, easy to use MySQL database server. If an Oracle DBA compares MySQL to Oracle feature by feature they are going to be very disappointed. An Oracle DBA needs to look at the feature/functionality that makes MySQL so popular. The feature/functionality is not tons of features, its that it is light weight, fast and easy to manage.

The closest analogy I can make to an experienced Oracle DBAs, is that a MySQL database server is similar in management to what an Oracle version 7 database was like. MySQL uses cache hit ratios, has minimal wait events and DBAs still need to write scripts to surround the MySQL database.

4 comments:

Camila said...

George

you post is very fair. I am in the same situation, i used to work with Oracle and now we are using MySQL in a lot of web projects, and i must say it has a great performance in these environments, especialy because it is light weight and easy to scale architecture.

George Trujillo said...

Camila, thanks for your feedback. I was asked specifically to put together a short list of highlights to help Oracle DBAs get their hands around MySQL. Oracle shops are not going to replace Oracle with MySQL. However a lot of new projects are starting up that MySQL is a strong solution for. I am seeing a lot of Oracle DBAs needing to support MySQL on new startup projects.

Logchild said...

yeah,you're right.but,another thing,mysql has many storage engines.such as myisam,innodb,memory,dnb etc.
but oracle doesn't

George Trujillo said...

Logchild, thanks for the comment. You are right, :) storage engines needs to be there. I will add it to the list.
George