Xaprb

Stay curious!

50 things to know before migrating Oracle to MySQL

with 68 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.
  11. There is zero MPP support.
  12. SMP is supported, but MySQL doesn’t scale well to more than 4 or 8 cores/CPUs.
  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.

Written by Xaprb

March 13th, 2009 at 12:43 am

Posted in SQL

Tagged with , ,

« Making Maatkit more Open Source one step at a time
Pop quiz: how can one slave break another slave »

68 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

Leave a Reply