Xaprb

Stay curious!

50 things to know before migrating Oracle to MySQL

with 112 comments

A while back I was at a seminar on migrating database applications to MySQL. A lot of the attendees were Oracle users. Based on their questions, comments and conversations during lunch, I made the following list of things Oracle users need to know about migrating to MySQL. Most of these are “gotchas” that would be contraindications or require some thought about a workaround.

Note: this is not meant to be MySQL-bashing. Some of these limitations are going to be fixed in future versions of MySQL, but they generally apply to current GA version 5.1. Some things are possible to achieve by choosing one particular way to use the server, at the exclusion of other things (e.g. Cluster rules out foreign keys, spatial data types rules out transactions). I am posting this list at the request of some Oracle DBAs who asked me to produce it for them.

  1. Subqueries are poorly optimized.
  2. Complex queries are a weak point.
  3. The query executioner (aka query optimizer / planner) is less sophisticated.
  4. Performance tuning and metrics capabilities are limited.
  5. There is limited ability to audit.
  6. Security is unsophisticated, even crude. There are no groups or roles, no ability to deny a privilege (you can only grant privileges). A user who logs in with the same username and password from different network addresses may be treated as a completely separate user. There is no built-in encryption comparable to Oracle.
  7. Authentication is built-in. There is no LDAP, Active Directory, or other external authentication capability.
  8. Clustering is not what you think it is.
  9. Stored procedures and triggers are limited.
  10. Vertical scalability is poor. No longer true; with Percona XtraDB or MySQL 5.5 (unreleased) you get excellent scalability
  11. There is zero MPP support.
  12. SMP is supported, but MySQL doesn’t scale well to more than 4 or 8 cores/CPUs. No longer true; with Percona XtraDB or MySQL 5.5 (unreleased) you get excellent scalability
  13. There is no fractional-second storage type for times, dates, or intervals.
  14. The language used to write stored procedures, triggers, scheduled events, and stored functions is very limited.
  15. There is no roll-back recovery. There is only roll-forward recovery.
  16. There is no support for snapshots.
  17. There is no support for database links. There is something called the Federated storage engine that acts as a relay by passing queries along to a table on a remote server, but it is crude and buggy.
  18. Data integrity checking is very weak, and even basic integrity constraints cannot always be enforced.
  19. There are very few optimizer hints to tune query execution plans.
  20. There is only one type of join plan: nested-loop. There are no sort-merge joins or hash joins.
  21. Most queries can use only a single index per table; some multi-index query plans exist in certain cases, but the cost is usually underestimated by the query optimizer, and they are often slower than a table scan.
  22. There are no bitmap indexes. Each storage engine supports different types of indexes. Most engines support B-Tree indexes.
  23. There are fewer and less sophisticated tools for administration.
  24. There is no IDE and debugger that approaches the level of sophistication you may be accustomed to. You’ll probably be writing your stored procedures in a text editor and debugging them by adding statements that insert rows into a table called debug_log.
  25. Each table can have a different storage backend (“storage engine”).
  26. Each storage engine can have widely varying behavior, features, and properties.
  27. Foreign keys are not supported in most storage engines.
  28. The default storage engine is non-transactional and corrupts easily.
  29. Oracle owns InnoDB, the most advanced and popular storage engine.
  30. Certain types of execution plans are only supported in some storage engines. Certain types of COUNT() queries execute instantly in some storage engines and slowly in others.
  31. Execution plans are not cached globally, only per-connection.
  32. Full-text search is limited and only available for non-transactional storage backends. Ditto for GIS/spatial types and queries.
  33. There are no resource controls. A completely unprivileged user can effortlessly run the server out of memory and crash it, or use up all CPU resources.
  34. There are no integrated or add-on business intelligence, OLAP cube, etc packages.
  35. There is nothing analogous to Grid Control.
  36. There is nothing even remotely like RAC. If you are asking “How do I build RAC with MySQL,” you are asking the wrong question.
  37. There are no user-defined types or domains.
  38. The number of joins per query is limited to 61.
  39. MySQL supports a smaller subset of SQL syntax. There are no recursive queries, common table expressions, or windowing functions. There are a few extensions to SQL that are somewhat analogous to MERGE and similar features, but are very simplistic in comparison.
  40. There are no functional columns (e.g. a column whose value is calculated as an expression).
  41. You cannot create an index on an expression, you can only index columns.
  42. There are no materialized views.
  43. The statistics vary between storage engines and regardless of the storage engine, are limited to simple cardinality and rows-in-a-range. In other words, statistics on data distribution are limited. There is not much control over updating of statistics.
  44. There is no built-in promotion or failover mechanism.
  45. Replication is asynchronous and has many limitations and edge cases. For example, it is single-threaded, so a powerful slave can find it hard to replicate fast enough to keep up with a less powerful master.
  46. Cluster is not what you think it is. Maybe I already said that, but it bears repeating.
  47. The data dictionary (INFORMATION_SCHEMA) is limited and very slow (it can easily crash a busy server).
  48. There is no online ALTER TABLE.
  49. There are no sequences.
  50. DDL such as ALTER TABLE or CREATE TABLE is non-transactional. It commits open transactions and cannot be rolled back or crash-recovered. Schema is stored in the filesystem independently of the storage engine.

I hope this is helpful.

Further Reading:

Written by Xaprb

March 13th, 2009 at 12:43 am

Posted in SQL

Tagged with , ,

«
»

112 Responses to '50 things to know before migrating Oracle to MySQL'

Subscribe to comments with RSS

  1. Damn, I read this and REALLY want to be using Oracle instead of this buggy and limited piece of shit called MySql…

    Alexey Kovyrin

    13 Mar 09 at 12:53 am

  2. Hehe. A lot of the people at that event were using some Oracle feature heavily and couldn’t imagine how they could live without it. (Some were mandated by regulations to use features such as certain types of encryption and security, and it would indeed be hard to build that in MySQL).

    Xaprb

    13 Mar 09 at 1:24 am

  3. Holy Crap, Batman. Some of these things go back to Oracle 6 and even earlier – over 20 years ago. There is no way MySQL can ever catch up.

    BackInTheDayDBA

    13 Mar 09 at 2:08 am

  4. Nice list, Baron.
    Absence of significant performance optimization features, pushes lots of performance tuning into application design and this is actually a good thing! Though, being primarily an Oracle DBA, I join BackInTheDayDBA opinion. :-)
    The question still stands — does MySQL need to catch up?

    “35. There is nothing analogous to Grid Control.”

    Though, I wrote MySQL monitoring plug-in for Grid Control.

    Alex Gorbachev

    13 Mar 09 at 5:03 am

  5. No analytic functions.

    Paul Keenan

    13 Mar 09 at 5:10 am

  6. Hi! Pretty good list.

    I would like to propose an addition to

    #1, “…and you are advised to rewrite correlated EXISTS subqueries using an OUTER JOIN … WHERE col IS NULL construct”

    and

    #49, “…but with the AUTO_INCREMENT column modifier you can create an autoincrementing integer column without having to write a trigger to fetch values from a sequence”

    I would like to add

    #51: No index organized tables (IOTs), but InnoDB provides a clustered index implementation

    #52: There is no rownum pseudocolumn, although you can fake it. But if you need it to grab a chunk of the rows it is probably better to use the LIMIT construct

    #53: What was mentioned for #9 is especially true for server side CURSORs. You should make a point of avoiding these. If you need a cursor to rollup rows of text into lists, try GROUP_CONCAT.

    Roland Bouman

    13 Mar 09 at 6:11 am

  7. I have to wonder though how many people out there are stuck with using Oracle for fairly simple databases and application when they could be using a lot easier like MySQL.

    I get to use both (well, 3 really since we use Postgres too) and there are older oracle-based applications that use none of the major oracle features. In this situation Oracle ends up being the major problem in every issue that comes up with the application. Not to mention the massive overhead in terms of memory, CPU and storage that Oracle requires just to exist.

    Still, it’s an interesting list even if I’ve only just heard of some of this stuff. It’s good to know how much more knowledge you need to run Oracle well.

    Tim

    13 Mar 09 at 8:57 am

  8. Hi Tim,

    I am actually in this situation. In my case, Oracle is there because that is what used to be railed into the company whenever “a database” was needed.

    Oracle and the app as such do what they were designed to do, but for us too, flexibility is a major reason to move to open source.

    In my case, migration basically boils down to generating web pages with PHP instead of Oracle stored procedures. The app uses none of the advanced features lacking in MySQL, at least nothing that can’t be easily fixed in another way.

    Roland Bouman

    13 Mar 09 at 9:18 am

  9. There is nothing even remotely like RAC. If you are asking “How do I build RAC with MySQL,” you are asking the wrong question.

    Yes, well, for suitable use cases, MySQL Cluster will knock out RAC with one hand tied behind the back. (Those use cases being “heavy concurrency & simple queries”, such as subscriber database, web/ecommerce session database, messaging apps…) But now that I write this, I realize this actually emphasizes your point – it is nothing like RAC. (And yes, an app that suits RAC well will typically *not* run well with MySQL Cluster.)

    Regarding encryption, unsurprisingly we run against this sometimes in telco. I was surprised to find out transparent encryption is a relatively recent function even in Oracle.

    I notice some “differences” in the above list are not actually negative. (At least not to me.) I’ll sneak in another one: For InnoDB, the default transaction isolation level is repeatable read. (I hear Oracle “still” has read committed?)

    Henrik Ingo

    13 Mar 09 at 9:37 am

  10. Henrik, I agree with you 100%. I wrote that point because one person in the lunch line at the migration boot camp kept insistently asking “Yes, but how can I build RAC with MySQL?” and I kept saying “you’re asking the wrong question. You need to ask instead, what problem or requirement does RAC solve for me, and how can I build that with MySQL?” That was a relatively fruitless conversation so I excused myself.

    I didn’t mean it to be a negative list, just a “oh! Hmmm. Well, I’ll have to think about that.” list.

    Xaprb

    13 Mar 09 at 10:39 am

  11. “22. There are no bitmap indexes. Each storage engine supports different types of indexes. Most engines support B-Tree indexes.”

    I’d say there are no hash indexes too, since neither MyISAM nor InnoDB support them. Sure you can emulate them (there’s an example in your book), but users should be warned.

    Morgan Tocker

    13 Mar 09 at 12:12 pm

  12. [...] Schwartz of xaprb provides a handy segue, in the form of his article, 50 things to know before migrating Oracle to MySQL. “A while back I was at a seminar on migrating database applications to MySQL. A lot of the [...]

    Pythian Group - Blog

    13 Mar 09 at 12:26 pm

  13. Currently, there is nothing like RAC on MySQL, but that is changing. ScaleDB, is building a RAC-like shared-disk clustering engine for MySQL. I won’t say that it is a RAC “replacement” now (or in the near future), but it is quickly moving toward a RAC “alternative”. It also addresses many of the issues in the list. But I don’t want to over-hype it. Give it time, but it is heading in the right direction.

    Mike

    13 Mar 09 at 1:31 pm

  14. A few more deficiencies:

    You can define only one trigger per table per action.

    You can’t abort a trigger with a custom error (though this is finally changing).

    CHECK constraints are not supported by any storage engine. But the syntax is accepted and silently ignored.

    Bill Karwin

    13 Mar 09 at 2:02 pm

  15. Thanks for *bashing* my “Best Practices in Migrating to MySQL seminar” that promoted your list, — http://ronaldbradford.com/blog/best-practices-in-migrating-to-mysql-2009-02-22/

    I enjoyed reading your list, it is indeed accurate, and I could easily add 50 more if so motivated.

    Your opening comment about GIS, and also 34. Ditto for GIS/spatial types and queries, is inaccurate.

    GIS data types are supported by Innodb (the transactional engine) since 5.0.16 See — http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

    Re: 38. The number of joins per query is limited to 61. Well if I saw a query that had 61 tables, then I’d be physically sick, or ROTFLMHO.

    What is important here is to realize are some points.

    MySQL is an open source database, much younger in company age and a completely different philosophy towards a RDBMS solution. If you have lived with Oracle or another commercial RDBMS for the past 10 years, it may shock you that companies like Yahoo, FaceBook, Wikipedia, Google Ad Words/Ad Sense, Travelocity for example live with MySQL for day to day and critical operations without features you use or expect.

    Let us also not discount other Open Source databases that have been around longer. I use Ingres as an example, a RDBMS product I used in the 80s & 90s, now open source has many, many more features then MySQL. Does it make it better then MySQL. Better is a matter of interpretation.

    The greatest hurdle in accepting MySQL comes from existing established clients and users. New users of MySQL for large and successful systems (unless for some specific feature) live with MySQL.

    As I always say “Maximize the Strengths and minimize the Weaknesses” of your product.

    I could easily create a list, 50 Things I can do with MySQL that Oracle doesn’t provide/do/have.

    Ronald Bradford

    13 Mar 09 at 2:44 pm

  16. Oracle — much faster query processing: parallel query, hash join, async IO for sort and hash join, merge join, bitmap indexes, …

    Mark Callaghan

    13 Mar 09 at 3:11 pm

  17. > I could easily create a list, 50 Things I can do with MySQL that Oracle doesn’t provide/do/have.

    I’d like to see such a list – go for it!

    Greg

    13 Mar 09 at 4:27 pm

  18. There is in fact ALTER ONLINE TABLE support, albeit limited, for MyISAM and NDB and InnoDB tables.

    http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

    Matthew Montgomery

    13 Mar 09 at 4:42 pm

  19. Sure, but I disqualify it because the things you can do online are so few and engine-specific. The InnoDB support is also only for the early-adopter InnoDB plugin, not the one you can download from MySQL.

    Xaprb

    14 Mar 09 at 9:02 am

  20. See also http://twitter.com/turnstep/status/1323888000

    Xaprb

    14 Mar 09 at 9:48 am

  21. I think people miss the real value of this list; if you are evaluating moving to mysql from oracle for an existing application, it is next to impossible to get a good answer from a mysql expert, nor an oracle expert. This is a great list to begin evaluating and thinking about your application in that mindset.

    Another option to look at between Oracle and MySQL is Ingres (also OSS).

    The whole RAC question cracks me up. If you need RAC (or expect to need RAC), you simply shouldn’t be looking at MySQL by virtue of the rest of mysql’s limitations.

    Mike S.

    14 Mar 09 at 3:59 pm

  22. I wish I’d had this when I moved from working with Oracle databases to MySQL ones (I’m a PHP developer), I spent quite a few months looking for functions that didn’t exist. I now love MySQL apart from when I want aggregate functions

    LornaJane

    17 Mar 09 at 6:11 am

  23. These are features that no-one needs! Ok.. Ok.. These features will make our life easier but think about it.. If google, yahoo, facebook can run such heavily used systems using MySQL why can’t we? The last I heard Oracle Enterprise licence costs $40,000 per CPU. I would rather get 40 more servers and spread out the load!

    Web Developer

    17 Mar 09 at 10:08 am

  24. Ronald, I missed one of your points. True — since 5.0.16 other storage engines support spatial data types. But I can always store things in BLOB if there isn’t a native type that handles it. There are no spatial indexes in other storage engines — and that is what I would be interested in if I cared about spatial data.

    Xaprb

    17 Mar 09 at 4:40 pm

  25. [...] 50 hilfreiche Hinweise für eine Migration von  Oracle nach MySQL  findet man bei Xaprb. [...]

    Oracle 2 MySQL | Biggle's Blog

    17 Mar 09 at 6:07 pm

  26. [...] you are considering migrating from Oracle to MySQL, here are 50 things you might want to consider [...]

    Analytics Team » Blog Archive » Things to know before migrating from Oracle to MySQL

    17 Mar 09 at 11:01 pm

  27. Re: 38. The number of joins per query is limited to 61. Well if I saw a query that had 61 tables, then I’d be physically sick, or ROTFLMHO.

    Prepare to be sick, or roll on the flooor, as you prefer. This was, for example, just posted to the postgres lists for optimization advice just today:
    http://www.attiksystem.ch/postgresql/query_slower_after_analyze/before.txt

    Admittedly I suspect the poster could probably simplify their query.

    Gerg

    18 Mar 09 at 9:56 am

  28. Large queries like that are common when using tools (BI, OLAP, …) to generate queries. And it is much harder to get a tool to fix a query (add hints) then it is a human.

    Mark Callaghan

    18 Mar 09 at 10:10 am

  29. About that 61-table limit: there might be some more discussion about that in the future.

    Xaprb

    18 Mar 09 at 1:41 pm

  30. very valid list – but when price is factored in, the list seems more manageable. Free vs 40K per CPU
    (dual core x86). Can be had for 28K if you’re buying enough volume. A solid 4 CPU/32GB blade for $22K is nothing compared to the licensing needed for Oracle ($160K/$105K).

    That said, some apps need and use the feature set Oracle provides. Some apps don’t (like the cross-platform ones that start life as SQL Server) Those are the apps that need to support to MySQL.

    MySQL will catch up, just like MSSQL 2008 is now on par with 8i (less OPS).

    Not everyone needs a Ferrari or a Buick for that matter. Some needs are met with a Honda Fit.

    Joe

    19 Mar 09 at 2:04 am

  31. Great list. It has been my experience in the government industry (where it looks like the questions and desire for a list had come from) that most of the time, the true needs of these customers can be accomplished with the limited features of MySQL. The one exception to that rule may be external authentication. But by and large, I have seen so many cases where what Oracle gives you is absolute overkill. As if you bought a bazooka when you really needed a slingshot.

    One day I remember talking to one of our Oracle DBAs who had been having trouble setting up replication. He told me he’d been working on it for the past week and it still wasn’t entirely working. I told him that if the application they were supporting ran on MySQL, setting up replication could be easily done in 20 minutes (he really didn’t want to hear that then). Point is though, as others have already made mention, that simplicity is many times your best friend.

    Chris Musialek

    19 Mar 09 at 9:22 pm

  32. Chris, please don’t tell me how great MySQL replication is compare to Oracle. Don’t get me wrong, I like the simple idea of it and how much flexibility you get from that. However, implementation is quite sloppy and stability out of the box isn’t great. To make it as stable as Oracle physical standby implementation, you would need to add quite a few additional procedures. Some of third-party commercial products address stability of MySQL replication but that’s another story.

    Having said that, we did just recently completed the move of 1 TB Oracle RDB database (get this – not even modern Oracle RDBMS) to MySQL with fully automated HA setup, DR and several replicated slaves. Works like a charm but it took great deal of effort to have everything play together.

    The biggest disadvantage of MySQL replication is that it’s very difficult to detect potential failures so they can go silent and you discover them later. That won’t work for critical applications like tracking ATM transactions.

    But everything has its price so (besides the price tag) Oracle physical standby replication with its stability brings serious limitations on flexibility. There is, Oracle logical standby and Oracle Streams that give even more flexibility than MySQL but it’s far from elegant MySQL slave replication – much more complex and difficult to setup (especially Oracle Streams learning curve – enormously long).

    This is the comment from a DBA with primarily Oracle background but started to work with MySQL now. I guess it qualifies as biased. :)

    Alex Gorbachev

    19 Mar 09 at 9:37 pm

  33. Alex, oh absolutely not, my point wasn’t to say one thing was better than the other, just like the post, but simply to point out that they’re different. MySQL replication is far from elegant, no doubt (I agree with you on all your points), and it’s also simple. My point was that decisions should be based on what you need, and in the case of government clients, their needs are most of the time met with the limited feature set of MySQL. Your 1 TB database is the exception, not the rule. Most items I’ve seen don’t need five 9′s of availability, they’re not even close to 1 TB, they can do without functional columns, etc. Above all, they could use a dose of simplicity. A clear example of a good enough comparison of simplicity (knowing full well how we’re almost comparing apples to oranges) is with replication.

    Chris Musialek

    19 Mar 09 at 10:28 pm

  34. Chris, agree.

    re government clients – I was recently trying to talk to few and see if they are interested in one online webinar we are putting together regarding migration to open-source databases. Their response was very interesting – “we don’t consider any open-source products to cut licensing costs because our licenses comes subsidized from the government program so we consider it’s free”. Granted, that’s here in Australia and it must depend on state but after hearing that, I want to pay my taxes even less! :-)

    Alex Gorbachev

    19 Mar 09 at 10:50 pm

  35. [...] 50 Things to Know Before Migrating to MySQL – the article is targeted at Oracle DBAs, but the same concepts apply to SQL Server DBAs.  We take a lot of things for granted in a SQL Server environment. [...]

    SQL Server and Cloud Links for the Week | Brent Ozar - SQL Server DBA

    20 Mar 09 at 8:16 am

  36. Baron,

    Thanks for this list.

    I am using mysql for a small data warehouse (which could get big..). I’ve used postgres before (although more as an application developer rather than a dba/warehouse guy), and not i’m becoming a pretty heavy user of mysql.

    For many companies, we want to try out the free / open source dbs before paying for Oracle. To my that choice currently boils down to mysql and postgres.

    What would be really great is if that list could have a [postgres has this] for each item. I don’t know if you have the expertise to do this, or perhaps some other postgres expert could provide such a thing? That would really make this the ‘goto’ page for someone deciding on mysql/postgres/oracle.

    Thanks again – enjoy this site.

    Nishant Deshpande

    20 Mar 09 at 3:11 pm

  37. Nishant, I don’t have the expertise to write that list for PostgreSQL. I’ve read the PostgreSQL manual a few times front to back, but that’s far different from knowing the software in depth, which is what you need.

    See http://shoaibmir.wordpress.com/2009/03/23/features-compatibility-with-oracle/ for something like this list for PostgreSQL.

    But also note that there might be features NOT on my list which would be “oh! Really!” things for Oracle to PostgreSQL migrations. So just adding “PostgreSQL has this” to my list might not be a good approach anyway.

    Xaprb

    23 Mar 09 at 9:32 am

  38. I love the both databases.

    One could have written a document with the subject

    101 things you do not need when migrating MySQL to ORACLE.

    Leon Leslie

    23 Mar 09 at 10:30 am

  39. Anybody has seen a ~1.5TB OLTP production database running on Mysql Cluster?. We use inhouse software mostly on top of RAC and moving logic from PL/SQL to the application side isn’t out of question in case of migration….

    Ciro Iriarte

    29 Mar 09 at 4:23 am

  40. Ciro, I’m not MySQL Cluster expert (far from it) but it has lots of serious limitations (especially, compare to Oracle RAC). 5.0 has very severe database size limitations (without going into details).

    MySQL Cluster has very narrow area of usage so you most probably want to have different MySQL setup if you migrate. Why do you use RAC on Oracle? For HA or for saleability? How many nodes you’ve got? You migration driver to MySQL must be cost – have you considered other ways to reduce Oracle licensing?

    I mentioned above that we recently migrated 1TB OLTP to MySQL for one client – no MySQL cluster but we used another cluster setup for HA.

    Btw, we are doing a free webinar on database platform migration to open source databases (well, MySQL). Ping me if you are interested and I send you an invite.

    Alex Gorbachev

    29 Mar 09 at 6:34 pm

  41. I noticed this post was referenced in the following thread:

    http://archives.free.net.ph/thread/20090313.165944.2162c6da.en.html

    Xaprb

    31 Mar 09 at 10:29 pm

  42. It can be interesting to check it out. Mostly we would like to move because of licensing costs…

    Also would be a good idea to sometime move from big SPARC servers to a distributed database on x86 (probably in the same step), that’s why MySQL Cluster came to mind…

    I know MySQL is not Oracle, but maybe using DB just as datastore and moving all the logic to the application can make it a viable replacement.

    Good performance and redundancy are a must, this particular DB serves CRM and realtime call billing… DWH would be another monster to migrate, but that comes in second place…

    Do you have any numbers about that OLTP migration?

    Ciro Iriarte

    31 Mar 09 at 11:42 pm

  43. Data store is another name for the database. Eh? :)
    If it would be that easy, go with flat files on shared storage and write cluster in the application.

    I mentioned the webinar – it must answer quite a few of your questions and give more food for thoughts.

    Re numbers about this OLTP migration. Contact me privately. I don’t have a public case-study but can share some details if interested.

    Alex Gorbachev

    1 Apr 09 at 12:00 am

  44. @Ciro Iriarte

    If you’re considering migration to MySQL Cluster: please realize that MySQL Cluster is not a general purpose database. It is not “MySQL but with added availability and scalability features”. Rather, it is a completely separate distributed RDBMS (called NDB) that can be accessed through a MySQL server as a storage engine (also called NDB).

    There are some things that NDB can do very well – like many small concurrent writes, many single row lookups. There are also many things it cannot do very well, like joins and subqueries. So generally speaking, NDB is not a good fit for typical OLTP systems (although work is being done to improve support for these types of things)

    Roland Bouman

    1 Apr 09 at 3:25 am

  45. “[...] Some of these limitations are going to be fixed in future versions of MySQL”
    That’s what we heard 10 years ago as well. Please, spare us of kool-aid inflated optimism.

    MySQL + 10 years = poor workmanship, inflated egos in the development process, lack of focus in execution and planning and a user base without any reality touch whatsoever (see the Drizzle project). I bet you don’t want me to continue. Just read Roland Boumans’s entries and you’ll figure out by yourself how’s the general MySQL user base mentality.

    gigiduru

    22 Apr 09 at 3:40 pm

  46. @gigiduru,

    I don’t think I ever met you at least I can’t recall.

    If you are feeling disappointed using MySQL that’s fine but I don’t see why you need to drag me into your argument, especially if you’re comments are lacking any specifics.

    gigiduru

    23 Apr 09 at 10:58 am

  47. @false gigiduru aka Roland Bouman:

    We didn’t meet, nor do I want to. However, time passing by, I couldn’t help noticing your posts around internet on mysql related websites. Your posts especially reflected lack of touch with reality and a certain strong bias towards this piece of shit called Mysql, even inclined to deny what’s good in Oracle, sql server postgresql and other databases just to make mysql look good.
    With your kind of posts, specifics are not needed because you’re way too predictable.
    Please, for crying out loud, stop using mysql, get some braincells implanted in that cranial box of yours and start using oracle and bragg about it. Above all, stop drinking kool-aid.

    the real gigiduru

    23 Apr 09 at 11:25 am

  48. @gigiduru

    Look, you can’t disgree with me or be angry all you want – but don’t lie and claim I “deny what’s good in other databases”.

    If you wanna pick a fight, get your own space on the internet and stop polluting this thread with off-topic badmouthing.

    Roland Bouman

    23 Apr 09 at 12:01 pm

  49. I will leave the previous comments, but I will mention that this is not a Free Speech Zone. It’s my website and you have no “rights” here. Further foul-mouthing will be swiftly deleted.

    Xaprb

    23 Apr 09 at 2:13 pm

  50. As an IT Director, I’m responsible for selecting and implementing the right tool for the job while managing my overall TCO (P&L), SLAs, and general customer satisfaction. It is a tricky balancing act.

    In the early days of my company we were exclusively MySQL. We were very hard cost adverse and not very risk adverse. That was basically the incubation period with a few forward thinking beta customers which helped us prove the model.

    We grew the company on that platform for the next 5 years, effectively pushing MySQL to it’s limits. Many of the items on the list became constraints to growing my business and providing quality services. We had lots of work-a-rounds in both code and systems to keep things running. LOTS OF THEM.

    As our customer base (and revenue) grew, we started to become more risk adverse. We were also thinking about future growth.

    Could we make MySQL work? Sure, we’re smart people. We could distribute. We could write more code to overcome some of the limitations. We could get really creative and make it work. Heck, that’s how we got as far as we did.

    Ultimately, you need to step back from uber-tech land and start thinking about your business. What are your strategic objectives? What are your real needs that drive revenue, manage expense, or meet SLAs to name a few.

    Then make sure you understand what TCO is. It’s not just license fees or support costs. It’s everything you do to provide a product/service AND risk ($$) of not being able to do so.

    All the creative engineering time, custom code, and change comes at a cost to the business. Now I don’t know about you, but I want my smartest people focused on activities that grow revenue or keep my customers happy (by maintaining happy/stable systems), not making something work to avoid a capital expenditure.

    One thing that seemed to be absent from this thread; nobody is saying oracle can’t do the job. It’s just “expensive”. Expense is relative.

    Someone mentioned a number of $160K for oracle licenses in an earlier post. Let’s make it $350K to account for grid control (a must have), physical standby, and decent servers. That’s a big number. Very scary to a shop used to OSS.

    Now, let’s lease it over 36-months and run it as an operational expense. Your monthly cost is approx $10, 800/mth with a little interest burden included. Basically the cost of one higher-end FTE. Now analyze how much time across all your staff is going towards “making it work” versus expanding your product or services (growing revenue). Of course, we could write a separate paper on your TCO analysis, but this isn’t an necessarily MBA blog.

    In summary, first define the job (your objectives), then seek the right platform for the job. If you can’t afford the systems it really takes to run your business, then you might want to rethink your business.

    Tony Tarone

    23 Apr 09 at 3:13 pm

  51. Tony, I couldn’t agree more. FOSS (Free Open Source Software) is not free when it comes to TCO and in many cases it might be higher that TCO using commercial software simply because of additional spend on labor and resource to make FOSS work as business needs.

    Going a bit further, what I notice is that businesses (not all but many if not majority) are “spoiled” by “free” part of MySQL and it’s difficult for them to get used to paying *proper* $$ for MySQL support. Good production quality expert DBA for MySQL is not different from Oracle DBA except that it’s probably more difficult to find one these days (I mean MySQL DBA).

    On the other hand, most businesses using MySQL (in *absolute* numbers) are small (compare to commercial DB’s) and often inexperienced so have lots to learn.

    Alex Gorbachev

    23 Apr 09 at 4:17 pm

  52. I’ve definitely experienced the talent gap when seeking a DBA. That goes to business risk as well. It took us 6 months to find our first real MySQL DBA. I interviewed quite a few, but had a hard time finding one I was comfortable with. The main problem was being able to quantify their qualifications. With an Oracle DBA, it’s easy. MySQL, not so much.

    I now have 4 Oracle DBA’s that are cross trained to support my legacy MySQL customers, which are now small and manageable. All the big hitters have been migrated to Oracle.

    It’s been a real experience going from a heavy MySQL shop to an Oracle shop. We struggled with all the same things these other companies are dealing with. In fact, we stayed on MySQL about a year longer than we really should have. Ultimately we just had to evolve as a company and our capabilities had to evolve accordingly.

    While some companies have been able to tailor their business model and applications to leverage FOSS, not everyone is going to do so or can do so. I do serious batch processing and data warehousing, which beats the be-jesus out of my databases and storage. My OLTP traffic represents about 2% of my load. I simply can’t do what I do on MySQL anymore, at least with my large enterprise customers. Though the license fees still hurt, it’s a necessary evil and the right tool for the job.

    Tony Tarone

    23 Apr 09 at 4:41 pm

  53. I think a big draw to mySQL is it’s price or lack there-of if you’re going with the community edition. While you can compare tools and features all you want, in the end management will look at how much they’re going to need to spend versus what they’re getting for it. That along with what they need a database for.

    Unless you need all those features Oracle offers, i would say most people would be very happy with mySQL once they come to terms that it’s a different animal.

    dan

    29 Apr 09 at 3:51 pm

  54. Anyone who starts the decision making process by looking at price first is misguided. You must first determine what problem or need you are trying to address. What features/capabilities would solve that problem. Then what tools/systems are available to provide those features/capabilities.

    If the features/capabilities are serviced by MySQL, then great you win.

    If they can only be serviced by a commercial RDMS like Oracle, then you pay.

    If only a commercial RDMS can meet your needs, but you can’t afford it, then reassess the scope of your needs or get into another less demanding business.

    Of course, your needs are not necessarily static. My company used MySQL heavily for 5 years and for certain customers we still do. However, our needs changed as the demands for our services increased. We performed many creative work-a-rounds and custom code (which is not free) to extend the life of our free MySQL RDMS. Ultimately, we moved to Oracle as it could meet our new and growing needs whereas MySQL could not. Our services generate more than enough monthly income to offset the leased $13K/mth license/support fees. In fact, it enabled us to go after business that dwarfs the monthly expense of Oracle.

    Is MySQL a good RDMS. Absolutely, if employed consistent with it’s abilities, which is not much different from any other system or application.

    Tony Tarone

    29 Apr 09 at 4:19 pm

  55. You know, there’s an interesting parallel here. At Percona we sometimes get inquiries from people saying “I’m running a free [something] service and it has performance problems. I can’t afford your fees, but can you help me cheaply because I don’t make any money?”

    And the answer is generally that they have two problems. One is performance. But the much bigger problem is that they need to figure out how to make some money. If it’s a popular service it must have some value, and that needs to be converted into money to sustain the cost of operating the service. It’s not Percona’s job to subsidize anyone. We’d get laughed at if we tried to hire consultants with a line like “we need consultants, but we give away our services, so can you work for nothing?”

    This principle runs through many of the comments above. What are your needs? Great; now what will meet them? Great; now can you afford it, all things considered? If the scales don’t balance, then you have a different problem than just “does it have feature X.” The cause and effect, cost and benefit chain needs to make dollars and sense from start to finish.

    Understanding that problem, I think, is where the real value gets created. And that’s why people hire consultants.

    Xaprb

    29 Apr 09 at 10:36 pm

  56. “
    The cause and effect, cost and benefit chain needs to make dollars and sense from start to finish.

    Understanding that problem, I think, is where the real value gets created. And that’s why people hire consultants.
    ”

    Just trying to understand this…are you saying Percona consultants help figuring out what the business value is of websites / online services?

    Roland Bouman

    29 Apr 09 at 10:54 pm

  57. I think I got a little cerebral there.

    We help figure out the business value of our services. We constantly coach each other to look at the value to the customer.

    Here’s a really concrete example: if a customer is having severe performance problems and needs urgent help, we don’t waste time and money telling him everything that’s wrong with his /etc/my.cnf file. Having “old_passwords=1″ in my.cnf is worth noting, but it’s not a performance problem! In terms of real money, it’s probably not even worth the time it would take to type “old_passwords is bad because…” in an email.

    Here’s another example: some people (not us) always tell people to plan for sharding from the beginning in case it’s ever needed. Is that really in the customer’s best interests?

    That general attitude — what’s the benefit? — needs to be the predominant mindset. A lot of it is judgment: subject X is either worth discussing, or it’s not.

    Those two examples illustrate the benefit side, and I’ll leave the cost side out for brevity, but obviously learning/planning/implementing/maintaining sharding isn’t cheap.

    Xaprb

    30 Apr 09 at 12:27 am

  58. @Xaprb

    Well said. If I’m having performance problems with something free then presumably I’m unable to meet current demands or adequately grow my business. If that is in fact the case, then is my free solution really free? What is it costing me in customer satisfaction, transaction processing, or revenue growth?

    Tony Tarone

    30 Apr 09 at 1:20 am

  59. @Xaprb: Ok, I understand – thanks for clarifying.

    Regards, Roland.

    Roland Bouman

    30 Apr 09 at 3:19 am

  60. about #50: Since when is Oracle DDL transactional? AFAIK it also does an implicit commit for all DDL statements.

    Lukas

    4 May 09 at 4:41 pm

  61. The list is a study of the Oracle , and Mysql Databases.
    I believe the list is to be appreciated.

    But in my case I think two separate systems are designed to suit two deferent natures.

    When I need more functions and more security I will use oracle.

    But in other wise i will use mysql is easy and simple way to manage data.

    Thanks and regards.

    Mahee - Maheeshwara

    25 Nov 09 at 9:14 am

  62. The list is a study of the Oracle , and Mysql Databases.
    I believe the list is to be appreciated.

    But in my case I think two separate systems are designed to suit two deferent natures.

    When I need more functions and more security I will use oracle.

    But in other wise i will use mysql is easy and simple way to manage data.

    Thanks and regards.

    Mahee - Maheeshwara

    25 Nov 09 at 9:15 am

  63. Hey thanks for such an insight on mysql. We have a mysql 5.1 community server running and simple queries, with 3 joins were taking days(seriously) to execute. Well, I have been doing indexing, staging area to reduce the temporary memory load, it worked, but doing it for work to come doesn’t seem such a decent idea. But, can you let me know if the 5.5 is better handling about a 1gb join data.
    Also, there is no problem with the server config, we have a 4GB ram and mostly, I am the only user on it.
    Other question: Is there a difference between the query analyzer with enterprise,because it might so happen that we might just purchase the enterprise edition if it has better results.

    Rahul Neulkar

    18 Dec 09 at 1:50 pm

  64. Hey thanks for such an insight on mysql. We have a mysql 5.1 community server running and simple queries, with 3 joins were taking days(seriously) to execute. Well, I have been doing indexing, staging area to reduce the temporary memory load, it worked, but doing it for work to come doesn’t seem such a decent idea. But, can you let me know if the 5.5 is better handling about a 1gb join data.
    Also, there is no problem with the server config, we have a 4GB ram and mostly, I am the only user on it.
    Other question: Is there a difference between the query analyzer with enterprise,because it might so happen that we might just purchase the enterprise edition if it has better results.

    Rahul Neulkar

    18 Dec 09 at 1:50 pm

  65. there is no difference in the optimizer between enterprise and community edition. well there might be differences at times, but nothing too significant.

    as for joins. from my experience mysql does fun with joins until you hit 5 tables or more. then you often have to do nasty stuff like adding sql hints.

    i myself am currently working on a larger than usual data set (about 2 GB without indexes) and finding that i need a lot of work to really tune things.

    Lukas

    18 Dec 09 at 1:54 pm

  66. #5: There is limited ability to audit.
    #6: Security is unsophisticated, even crude. There are no groups or roles, no ability to deny a privilege (you can only grant privileges).

    Securich (http://www.securich.com) provides auditing for grants and revokes as well as password changes. It also provides roles, the ability to deny a privilege, use password history and configure password complexity etc.

    Darren Cassar

    4 Jan 10 at 1:17 pm

  67. Darren, I think we should be very precise about terminology. Securich doesn’t provide real roles. It helps you manage permissions in the same way that roles do — I would call it pseudo-roles. Ditto for denying privileges. This is not a criticism of Securich, I just want to clarify.

    Xaprb

    4 Jan 10 at 1:35 pm

  68. 49th Mysql Sequences, we have a solution in mysql but a bit heavy operation than the one in oracle. you can limit the values of sequences as in oracle by using CHECK conditions in mysql.

    http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

    look for LAST_INSERT_ID()

    Ningappa Koneri

    29 Jan 10 at 9:34 am

  69. #10 Vertical scalability is poor.

    Right on the mark.

    Also:
    MyISAM: there is no online table maintenance. If you want to update your stats (analyze/optimize) nothing else can write to the table.
    MyISAM: if you want to rebuild indexes it’s an all or nothing type deal. You’re rebuilding everything.
    MyISAM: ditto with an Alter table, Data/Indexes everything is rebuilt (in 5.0 at least).
    Innodb: Huge memory requirement 1:1 recommended.

    Replication is limited to 1 SQL thread.

    Jeremy White

    5 Mar 10 at 7:26 pm

  70. We have been using MySQL for OLTP type of work, but we are facing lots of issues of MySQL, for example, table locking that we have to keep killing our clients queries for monthly and yearly reports constantly, replication latency that has led to data discrepancy in 75% of our clients data, to say the least.

    It is really not an enterprise grade database at all.
    Even my husband’s music library of over 1000 songs can’t use mysql as it is too slow to find or play the music.

    Google uses mysql, but according to the presentation of Google at mysql conference of 2008, one single mysql database instance needs at least 128GB ram so that queries can perform and table locking will happen less!

    Mysql relies very, very heavily on upscale hardware to perform even though its cpu usage is never over 40% and load on the machine is < 10 all the time, because it really can't efficiently utilize the resources at all.

    sheila

    8 Mar 10 at 4:55 pm

  71. Sheila,

    At Pythian (www.pythian.com) those problems are typical of heavy usage with the default installation (using the MyISAM storage engine).

    MySQL can efficiently use resources, but the out-of-the-box configuration is usually not going to get you very far. That’s usually true with most applications, though. There are some basic tuning tools / tuning advice that will help a lot (I’m fond of http://launchpad.net/mysqltuner, but do a quick search on “mysql tuner” and you’ll find tools and general advice that you can use).

    Sheeri Cabral

    8 Mar 10 at 6:11 pm

  72. Sheila,
    Apparently Sheeri is more polite then I am. I think your deployment would benefit from expert MySQL consulting.

    Mark Callaghan

    8 Mar 10 at 6:19 pm

  73. Hi, Mark:

    Thanks for responding to my email and Sheeri, thank you too.

    We did hire a consultant and since we use Mysql like traditional database, without using the ‘sharding’ method, it really won’t work.

    Because innodb engine doesn’t allow big index and other limit due to disk space and memory limit, we just can’t convert all myisam to innodb engines.

    And also the single threaded slave replication has been difficult for us as replication keeps falling behind as we add more and more load on master.
    We are using master-to-master replication so it is complicated.

    Thanks for your comments. It is an honor talking with you.

    sheila

    8 Mar 10 at 8:17 pm

  74. I didn’t really realize that I had to find a mysql consultant for the musical library that runs on mysql at home.

    I don’t think it worth it.

    Logitech that provides the musical box is going to have a beta version that uses a different database very soon. I’ll just wait to see if it fixes the performance issue or not.

    And not sure if a consultant can fix the single threaded slave replication either.

    The answer that mysql consultant will probably give is that don’t run so many threads on the master! Slave won’t be able to catch up!

    innodb has to live in memory, so 128GB of ram as suggested by google is a starting point. That’s why I said that mysql has to rely heavily on upscale hardware to perform.

    Even a big Oracle data warehouse or a db2 enterprise database only needs 16GB memory to run.

    sheila

    8 Mar 10 at 11:25 pm

  75. Logitech should use SQLite. I have never been a fan of embedded MySQL.

    I never knew that InnoDB had to live in memory.

    I am very aware that people like to write queries that potentially do a lot of random disk reads and then are not happy with query response time. But the DBA and SQL authors are to blame for that.

    Your last response is kind of a troll, so this is it for me.

    Mark Callaghan

    8 Mar 10 at 11:37 pm

  76. This is getting pretty hilarious. I love the things people “know” about databases, and how freely they quote these “facts” to people who’ve forgotten more than I’ll ever learn.

    Xaprb

    9 Mar 10 at 1:02 am

  77. Mark:

    I agree that you have never said that innodb has to live in memory, but the 128GB ram suggestion has led me to believe that innodb needs to use so it can run.
    Recently, a query of a little table with 342KB in size couldn’t run at all and kept getting lock time out error, but I couldn’t find any locking issue.

    So someone else checked innodb buffer free pages and realized that there wasn’t any free page left in the innodb bufferpool. We increased the innodb bufferpool just by 100MB, and guess what the query using this little table immediately completed.

    Hence my assumption, innodb has to live in memory.

    Sorry to contradict you.

    sheila

    9 Mar 10 at 1:22 am

  78. sheila:

    Boxed software should be built so that end users do not need to manage it’s components. At least, (if it’s reasonably enterprise/flexible software) there should be guidelines on how to maintain components. So it’s Logitech problem – not MySQL.

    Regarding replication and etc – proper MySQL setup does require at least minimal amount of knowledge and based on what I read in your comments – this knowledge is definitely missing that knowledge in your organization.

    The failure to deploy MySQL in your environment seems to be caused by simple ignorance. I’m sorry if it sounds harsh but NONE of your conclusions are correct (and I’m not even a MySQL expert unlike Sheeri & Xaprb).

    Alex Gorbachev

    9 Mar 10 at 3:29 am

  79. Alex:

    I can feel your passion about mysql and I agree with you that we can’t be called experts of mysql in that regards because we have failed miserably to make the simple database mysql work the way that we have successfully made oracle, db2 and ms sql server to work.

    It really demands a heck of knowledge that we don’t seem to be able to obtain at all.

    For example, we can’t make slave to catch up as we use the master-to-master configuration.
    It really can’t catch up at all with one single thread against multi-thread master.

    And we only give the master very limited memory about 16G because we think that mysql is a simple database and doesn’t require lots of memory or cpu power to run.

    So we are completely wrong in that regards.

    Nice talking with you and thank you for your comment.

    sheila

    9 Mar 10 at 11:37 am

  80. sheila:
    I think there are few people on this thread (including me) who would be more than happy to help you do it right providing you don’t fall into the illusion that free open-source software comes with no cost to the enterprise. :)

    Alex Gorbachev

    9 Mar 10 at 12:17 pm

  81. sheila:
    Btw, I’m actually coming from Oracle background but I know enough about MySQL to be passionate about the technology and what it do for the business.

    Alex Gorbachev

    9 Mar 10 at 12:20 pm

  82. Sheila,

    As Alex and other have indicated, there are plenty of people in the community to see input from. It seems your system is not large or complex.

    As an Expert in MySQL, and for the record, the speaker of the presentation this blog was written about, there are so many mis-configured MySQL environments that can lead to poor performance and misleading information on the Internet, I off *FREE* advice in review of my.cnf, simply to eliminate bad configurations.

    I’d like to know the level of the “consultant” you hired and what they recommended.

    As Sheeri indicated, MySQL can run on many different types of configurations. I see production systems on 2G-4G of RAM regularly, and I’ve never actually seen a production DB with 128G of RAM so your claim is unfounded.

    Ronald Bradford

    9 Mar 10 at 12:29 pm

  83. Xaprb great page. Really enjoyed your comments Alex and Tony Tarone.

    Some other things I’ve noticed.

    #51 Table maintenance (analyze/optimize). MyISAM: no writes can be performed while you do these operations. Innodb: If you want to reorg an Innodb table you have to dump and reload. No one can access this data while you’re performing these operations. With large tables this can take quite some time and is unpredictable in completion time (especially if it repairs with keycache). Therefore becomes very hard/impossible to automate with even fairly small tables < 30GB.

    Well make smaller tables right? That is something to consider if you're used to operating with larger tables. This means dev time, admin time to accomplish (not free). Something to consider when moving from oracle.

    #52 If you alter a table it rebuilds all the data and indexes of the table (this is the case in 5.0.* i believe).

    #53 if you run analyze on a MyISAM table you cannot analyze individual indexes you have to rebuild all the indexes.

    #54 if you're using online backups with Oracle you'll need to consider that mysql relies heavily on your storage e.g : snapshots. There isn't a real good backup solution included with mysql. Replication is sometimes considered a backup but it has some gaps. You'll definitely need to spend some time re-thinking your backup solution moving from oracle. If you have a lot of horizontal scaling (as is hinted to from #10 above) then this challenge becomes larger as you may not be able to have a separate slave for all your hosts. This is a significant hit for your storage admin.

    #55 You may need to change the structure of your tables to match up with Innodb best practices. E.g. you need smaller primary keys. All index lookups first hit the primary key like a concatenated index.

    Sheila,
    What were you thinking you should have re-written mysql then everything would work nicely for you.

    Jeremy White

    9 Mar 10 at 12:35 pm

  84. Jeremy, you’re mixing some metaphors. InnoDB doesn’t use the key cache, and it’s possible to reorganize your InnoDB tables without dump/reload — just not possible to shrink ibdata1 without dump/reload. In practice innodb_file_per_table=1 solves all problems with tablespace management. Running ANALYZE TABLE doesn’t rebuild indexes, it analyzes their cardinality/distribution. Index lookups in InnoDB don’t access the primary key first, they access it last. Secondary index lookups return the primary key value, which is then used to look up the row via the primary key — but even this doesn’t happen unless the rest of the row is needed for the query.

    Xaprb

    9 Mar 10 at 1:01 pm

  85. Jeremy:

    I realized that I can make db2, oracle, ms sql server work, but I just seem to have bumped my head against the wall at mysql.

    The query against a little table of about 320KB wouldn’t even run with lock time out error (err msg very far from the truth, btw) till more memory was added into the innodb bufferpool.

    If someone still claims that because the configuration of mysql is wrong, they are not convincing me at all.

    Even if mysql configuration was wrong, this little query should still be able to run, regardless of the configuration.

    sheila

    9 Mar 10 at 2:46 pm

  86. innodb_file_per_table is a must for sure. However it still doesn’t change the fact that all applications will wait until a reorg is complete. Meaning writes to the table cannot occur while you’re doing this. As I said, even with small tables < 30GB this can be a very lenghty. However you perform the operation optimize/reorg/alter table xyz engine = innodb; the applications must wait until its complete which is a huge maintenance overhead change for oracle admins.

    Analyze does actually *rebuild* the index for MyISAM which is an all or nothing job. Analyze isn't really needed for Innodb as it keeps stats as it goes.

    Whether innodb accesses appends/prepends primary key doesn't remove the limitation. Also typically you're going to hit the actual row data unless you're storing the entire row in your index which kind of defeats the purpose.

    Jeremy White

    9 Mar 10 at 4:28 pm

  87. Jeremy, I still think you’re a bit mixed on some things, but it’s going off topic. To stay on topic and speak to what DBAs need to know before migrating, you are absolutely correct: Oracle DBAs need to know that almost anything that modifies a table is a blocking operation.

    If I may take things off-topic again, though, I encourage all such DBAs to read http://www.xaprb.com/blog/2010/02/07/how-often-should-you-use-optimize-table/

    Xaprb

    9 Mar 10 at 4:40 pm

  88. And worse yet .. not only is it blocking, but also painstakingly time and resource intensive since it effectively copies the entire table.

    Lukas

    9 Mar 10 at 4:48 pm

  89. I think we’re all missing something obvious here — yes, MySQL has lots of differences that are very negative to an Oracle DBA. However, there’s one very very big negative that Oracle has — the price tag.

    If you buy a $50,000 car, it will be a LOT better than a car that someone gives you for free, in almost all cases.

    (however, both cars will drive where you want to go, and many people will happily get a free car with no air conditioning instead of paying $50,000 for a car with air conditioning. Driving in summer is uncomfortable, but it’s FREE).

    Sheeri Cabral

    9 Mar 10 at 5:33 pm

  90. I’m not missing that at all. I have reflected over time that most people who discuss this post think it’s about the negatives, but I only meant for it to be a list of differences. It just happens that MySQL is a much less complex system than Oracle, so most of the list is “MySQL doesn’t have/do this and that.”

    Xaprb

    9 Mar 10 at 5:54 pm

  91. @Sheeri
    I think your analogy is a litte off. Mysql is more like a bike. As you wouldn’t want to go on certain roads with it. You could… but it would take you forever.

    Jeremy White

    9 Mar 10 at 8:05 pm

  92. #51 MySQL hasn’t made me breakfast in bed.
    #52 MySQL hasn’t fixed the crappy economy.

    Sheeri Cabral

    11 Mar 10 at 7:43 pm

  93. I think i will just use whatever i feel like using. Oracle or MySQL will get the job done. One will save other one won’t, but in the end it makes the money, if you know how to.

    There are good reasons why we need Oracle, as well MySQL. So bashing one over the other is just a child play or egocentric way of thinking.

    ?MMX

    28 Jun 10 at 12:02 am

  94. I think i will just use whatever i feel like using. Oracle or MySQL will get the job done. One will save other one won’t, but in the end it makes the money, if you know how to.

    There are good reasons why we need Oracle, as well MySQL. So bashing one over the other is just a child play or egocentric way of thinking.

    ?MMX

    28 Jun 10 at 12:03 am

  95. So much information on just one Post! Thank you Xaprb. And thanks to Alex, Jeremy, Roland, Sheeri for more interesting information.

    Now if only I could find a good FOSS tool to help me migrate that Oracle Database….

    Jay

    2 Jul 10 at 3:45 am

  96. Jay,

    I’d imagine that your biggest problem is not to move the data and schemas but change your application and organization’s skill set. Moving the schema and data is the least problematic.

    MySQL Migration Toolkit is one the most known tool.

    Alex

    Alex Gorbachev

    2 Jul 10 at 9:07 am

  97. I use Oracle and MySQL and know the limitations of MySQL and am OK with that. Am more curious about the initial comment early on raised where there are about 50 things MySQL has that Oracle doesn’t. Would like to know what they are.
    From my business perspective, the downside of MySQL has to do with its lack of Blob and Multimedia support. I have to use PHP to make up the difference (e.g. image manipulation, encryption).
    As for the “Free” price tag, I am using Oracle XE extensively, and even though there is a 4Gb limit, its amazing how much can be stored in 4Gb when you get creative (and store non key data) externally. Simple apps don’t usually require a large amount of data.

    Marcel Kratochvil

    7 Jul 10 at 2:58 am

  98. I am looking at using MySQL instead of oracle. One issue that I am not sure about. Does anyone know if MySQL can do http post like Oracles http_util package can do in a pl/sql?

    Eric

    10 Aug 10 at 2:18 pm

  99. I’ve always wondered how to justify the “price” of the non FLOSS and expensive alternative. It’s certainly interesting reading and like you say, it’s about pointing out differences. I still think MySQL happens to fulfil the needs of most organisations, and I believe you’d probably agree. Different products for different needs.

    Gigitrix

    18 Aug 10 at 12:38 pm

  100. “Data integrity checking is very weak, and even basic integrity constraints cannot always be enforced”.

    This is the main reason of not using MySQL on corporate enviroments appart from blogs, forums and basic web-apps.

    Anyway, you should note that the Oracle docs are among the ****BEST**** on the whole IT industry. “Oracle Concepts” is a masterpiece:

    http://download.oracle.com/docs/cd/E11882_01/server.112/e10713/toc.htm

    Bites

    19 Aug 10 at 7:53 pm

  101. I am so tired of the “Oh Google, Facebook use MySQL” argument.

    1. Google created BigTable
    2. Facebook released Casandra
    3. Flickr DualTree

    Ask these folks why? and how much $$/Time they spent on it.

    If your shop has the $$ or the resources (Highly Skilled Developers and Time) to dedicate creating/fixing the “database engine” then yes go for Open Source; alternatively if you want to concentrate on your core business and use technology as an enabler then commercial database is the way to go.

    There is no one size fits all!

    Look deeper

    9 Sep 10 at 9:33 am

  102. #

    I am so tired of the “Oh Google, Facebook use MySQL” argument.

    @#

    I am so sick of oracle bonehead thinking there paid software is better when in 90 percent of cases its just money putting these statements up.. NOTHING TO BACK IT ..

    In doing both i see oracle as a tool company not the leading DB .. mysql can handle this way better with hardware not APPL restrictions’

    Bunch of idiots.
    oracle junkies

    Necronn

    22 Sep 10 at 2:02 pm

  103. Keep it civil, please. If this turns into a flamewar I’ll delete comments all the way back to the origin. I am very interested in people contributing their views on things Oracle DBAs should be aware of as they are introduced to MySQL.

    Xaprb

    22 Sep 10 at 2:34 pm

  104. why migrating to mysql if we can make with oracle.
    i can’t understand why should do that?

    techicore

    25 Dec 10 at 7:50 am

  105. As has been shown, most database vendors can produce impressive TPC-C benchmarks because the schema and test case is well defined and contained.

    What defines a powerful database from a standard one is not whether it can get impressive results from well defined apps (e.g. facebook and google), but how well it can scale transparently. By this, I mean how easy is it to take an application (any application) and scale it via concurrent users, locking and data volume *without* having to make schema changes to accommodate this scalability. Simply, can you use your logical design as your physical design?

    The strength of the database is seen by how high a figure can be reached by DBA’s putting on indexes, memory changes, CPU, partitioning, storage management, before they say, the application design needs to be changed to get it to scale further. Application design change is much more expensive to do than database changes, so its a less attractive option and typically a last resort.

    This figure is a moving target, constantly getting higher with each new release.
    What you will find, is that a database like Oracle exceeds databases like MySQL, SQLServer, putting your data in a spreadsheet or raw text files.

    So I have no doubt MySQL can handle incredibly large volumes of data in google or facebook apps, but I know that if it was given some of the apps I have had to work on, it would be struggling to scale to handle anything near the data volume and concurrent user usage that Oracle can handle. And I will not even start on how MySQL shouldn’t be used for storing any unstructured data and multimedia inside it.

    So to to also add another controversial point, I would say its easier to migrate the vast majority of MySQL apps to Oracle with minimal to no change, but to migrate from Oracle to MySQL is going to require major changes, rewrites and design changes (unless its a very simple design).

    Marcel Kratochvil

    27 Dec 10 at 6:52 pm

  106. I have no doubt that Oracle has more/better features that are critical to supporting complex OLTP workloads. However, I also have no doubt that you are speculating on the complexity of the workloads that MySQL handles at some of the tier 1 web companies, especially the ones that you cited above.

    I am curious how MySQL/InnoDB compares to Oracle on price/performance. Alas, while we can publish results for InnoDB we cannot do the same for InnoDB. So all comparisons will remain in-house.

    Mark Callaghan

    27 Dec 10 at 7:01 pm

  107. Facebook…500 million users on MySQL…Winning…Done.

    Mr. Sheen

    5 Mar 11 at 1:02 am

  108. Hello, you can find a french translation of this post here : http://www.mysqlplus.fr/2011/03/50-choses-a-savoir-avant-de-migrer-de-oracle-vers-mysql/

    Thx
    Cédric

    Cédric

    14 Mar 11 at 6:35 am

  109. Most points on the list are not usable for most practical usage!
    What is DB? A storage! I don’t need any other junks!
    What is DBM? ?? + Manager! I don’t need any other junks!
    MySQL is enough for most practical situations!

    bbb

    16 Mar 11 at 10:38 am

  110. I worked with oracle for 10 years, and mysql for 2.

    I’m not a dba, but a developer. Oracle allows to put code and intelligence in the db, mysql does not. Oracle laughs at big ugly complex queries, while mysql shines at retrieving rows directly.

    However, the cost of joins, the inability to use indexes inside views or functions, and the unexplainable (or very time-hungry-to-solve) losses of performance are quite hard to work with.

    This changes my approach to programming. With mysql, performance is always a serious concern. With oracle, never.

    roselan

    13 Sep 11 at 8:38 am

  111. Since the original publishing of this summary in 2009, is there an updated version of the pros/cons of Migrating Oracle to MySQL? Or, is this the most updated summary?

    CKMate

    31 Jan 12 at 7:51 pm

  112. The effective employment of any tool is a function of the job/problem at hand and the skill of the worker(s) employing said tool. In the case of databases, I’ve seen poorly implemented Oracle solutions and wonderfully implemented MySql solutions. In general, it is management’s responsibility to align the problem (or objectives), with the tool, and the skill of their staff.

    Right now my company is doing stuff on Oracle I couldn’t even think about on MySQL. Fairly large Enterprise workloads in ETL, OLTP, and some emerging OLAP databases. Fortunately, I’m driving good revenue and have a talented/dedicated DBA team to support it. Basically, as my problem has evolved, so have the tools and people I use to keep pace.

    Tony Tarone

    31 Jan 12 at 11:03 pm

Leave a Reply