Mysql4all Blog

Ivan Zoratti's blog on MySQL and around

Oracle 11g and MySQL Enterprise: get the best of both worlds

with 9 comments

In the last 9 months, every time I visited a customer, or I participated to a meetup group or I simply had a chat with a non-MySQL colleague, I heard the very same question: “when should I use MySQL and when should I use Oracle?”. The most audacious dared to ask how difficult it is to migrate from one database to another. Some more conservative DBAs and developers asked me if and how it is possible to integrate both database servers in the same environment or solution.

As you can imagine, there are not simple answers to these questions. There are certainly some guidelines that may help in making a decision around the use of MySQL, Oracle, or any other RDBMS.

When should I use Oracle 11g and when should I use MySQL Enterprise?

The ultra-simplistic answer can be summarised in:

  • Go for Oracle 11g with ERP and CRM systems, Data Warehouses and sophisticated applications that require lots of features built into the database.
  • Go for MySQL Enterprise when you want to power Online applications (especially LAMP apps), you need to create “quick&dirty”  Data Marts, or you want to easily embedded or bundle the database in your application.

Does it mean that I should not use MySQL to power a Data Warehouse? No, I do not mean that, but if you are an Oracle DBA, you may be disappointed in not finding a Parallel Query Option (PQO) or bitmap indexes in MySQL. Or maybe you want to partition a table with 10 billion rows and move these partitions from one table to another, remove one partition for some time and then reattach it to a table. If you manage a relatively small DW with few users, all these features may be irrelevant, but if you have some Terabytes on a proper big iron, you will definitely take these elements into consideration.

Conversely, if you need to create a data mart to do some analysis and reporting, then MySQL may be just perfect. You can install a small VM or a standalone server, have MySQL up and running in minutes and then you can start bulk loading data on MyISAM tables. By the time you are loading your data, your colleage would be on dialog window #23 of the Oracle 11g Installer (if you can manage to set up a proper X11 environment to make it running). Oh, and of course you can pack the MyISAM tables, or you can use a columnar storage engine.

And regarding Online application, just the fact that 16 of 20 top web sites run primarily with MySQL (http://www.alexa.com/topsites – just remove Windows Live, MSN, Taobao and Amazon.com from the list) should give you confidence that MySQL is a good choice.

Should I migrate from one database server to the other?

It is always possible, but first I strongly recommend to answer to these questions:

  • Does my team have the right skills to use and support the destination database?
    Sometimes, the learning curve of a database server is not taken into a proper consideration. Developers may use the RDBMS in the wrong way and they may end up with issues, with terrible performance and big nightmares. This is particularly true when CIOs and IT managers think that a migration to MySQL would simply cut the licenses costs. Because MySQL is such a “simple” product, there is no need to learn, you just start coding. How wrong is that?
  • Have you considered, in your TCO, the man-time cost of a migration, the review of the infrastructure and the estimated cost of the disruptions caused by a significant change?
    The name of the game here is “underestimation”. IT managers often underestimate these costs, sometimes by an order of magnitude. They should be very generous in considering quite expensive disruptions, but most of all, they should bring in expertise that can confidently mitigate the risk of a migration (and consider the cost of this expertise). Thankfully, although it was a migration to a NoSQL solution, our friends at Twitter realised the potential impact of a change in technology just in time. Others did not realise that on time.
  • A migration may save money but it does not bring any value to the end user. Is it really worth the risk?
    Of course, if it is big money, the answer is yes. A new project is definitely preferable to a migration: new and updated features may win the users’ mind to sponsor the move.

Can I really integrate Oracle 11g and MySQL Enterprise today?

The answer is yes, definitely! But again, you need to understand what you mean for “integration”. I have identified three types of integration:

Application integration

Applications can “easily” interact with both databases. Things may be difficult if you use some frameworks, but in general you can push and pull data on both servers at the same time. Another typical approach is to use message queues or a common cache – Memcache may come handy here.

Systems integration

Systems can co-locate instances without any problem and shared storage among the instances can facilitate the integration. You can consider to exchange data through the file system, perhaps by using the CSV storage engine.

Systems integration is also systems management and monitoring integration. For this, you may be interested in knowing more about the use of Oracle Enterprise Manager (OEM) to monitor MySQL. Oracle is working on that and there will be a release expected in the next calendar year. If you need something quicker, you may have a look at the Pythian plug-in.

Data integration

Data integration is probably the most interesting part. I would consider three aspects here:

  • Data exchange: it is performed using a typical ETL approach. Both Oracle 11g and MySQL Enterprise can extract and load data in compatible formats – CSV or logical dumps. Data exchange should then be pretty straightforward.
  • Data replication: there different solutions to this approach, both unidirectional (only from MySQL to Oracle or from Oracle to MySQL) or bidirectional (between the two databases at the same time, although there is no conflict resolution). Oracle GoldenGate 11g may be a great choice if you need to replicate data from Oracle to MySQL: it is fast, portable and highly scalable. For bidirectional replication, Hit Software provided a software called dbMoto that can do the trick – although you should consider that the replicator runs only on windows at the moment.
  • Real time data access: what about federated data, i.e. accessing data on MySQL from an Oracle database and viceversa? Well, this is possible straight away from Oracle to MySQL by using Oracle Heterogeneous Services (OHS). From MySQL to Oracle you should use User Defined Functions that use an OCI client to access an Oracle database from a SQL statement. I will try to blog more about this approach.

Where can I find more information?

The first step may be to take a look at the presentation that I gave last Monday at the Oracle Open World. The slides are available here.

Sheeri Cabral video recorded the presentation and posted it here.

I will replicate this presentation in Europe on these dates:

I hope to see you there!

About these ads

Written by Ivan Zoratti

22 September 2010 at 6:32 pm

9 Responses

Subscribe to comments with RSS.

  1. Thanks for a good summary.

    Mark Callaghan

    22 September 2010 at 7:42 pm

    • Thanks for your great article. I have a question. In article you write
      “Oracle GoldenGate 11g may be a great choice if you need to replicate data from Oracle to MySQL”

      Can do use Oracle GoldenGate 11g to replicate data from MySQL to Oracle ?
      And Can you estimate about DBMoto -> fast, portable and highly scalable ???

      Nghia Nguyen Luong

      12 November 2010 at 3:34 am

      • At the moment Goldengate cannote replicate from MySQL to Oracle, but DBMoto can.

        Re DBMoto, I do not have benchmarks, but I am planning to test it thoroughly. There are some limitation in scalability, but I will be more detailed in the future.

        Thanks!
        -ivan

        Ivan Zoratti

        25 January 2011 at 9:45 pm

      • Thank soo much for you answer. But in the oracle forum https://forums.oracle.com/forums/message.jspa?messageID=9174413#9174413 mrmessin write “Ok Here is the offical Word from Oracle on this.
        Oracle GoldenGate now supports capture from MySQL 5.1.15 and later and oracle can be a target. Therefore if you are running MySQL 5.1.15 or later you can indeed use Golden Gate to replicate MySQL to Oracle when using GoldenGate 11g. ”
        Do you think he wrong ?

        Nghia

        23 December 2011 at 3:46 am

      • Hello Nghia,

        I do not think they are wrong. The material has been presented at Oracle Openworld 2010, so it is 15 months old.
        It is possible that GoldenGate does provide MySQL > Oracle replication now.

        My comments are:
        – 5.1.15 is a very old version, probably one 4 years old now and one of the first GA versions of 5.1. Current GA is 5.5, you should check if that post is un to date
        – In the meantime, other solutions are available, like Tungsten from Continuent – check http://www.continuent.com

        Thanks!
        -ivan

        Ivan Zoratti

        23 December 2011 at 9:19 am

  2. Actually Ivan,

    Great article, I have one note:

    The ability to exchange a partition with another table then reattach it later is available in MySQL as of 5.6.0.

    http://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html

    Granted, this version is still beta and that feature may be immature.

    Matthew Montgomery

    23 September 2010 at 12:32 am

    • Hi Matthew,

      Yes, that is a very interesting feature, and I completely forgot it was available in 5.6. As soon as it is stable, we will have another reason to say that we can use MySQL in more projects!

      Ivan Zoratti

      23 September 2010 at 6:11 am

  3. The video link is at:

    if you want to update your blog post.

    Sheeri Cabral

    28 September 2010 at 8:00 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: