MySQL and Oracle XE

So lately I’ve been playing around with Oracle XE and MySQL.  It’s funny, most people don’t like to talk to me about MySQL — they seem to assume that since I’m an Oracle guy, I’ll either hate MySQL or not know anything about it.  How can you be a competent database person nowadays and not at least be familiar with MySQL and Postgres in addition to Oracle and/or SQL Server?  Beats me.

I’ve got nothing against MySQL at all — I understand why a lot of people use it (cost, religion, etc).  Within my organization I like to look at what the decision factors are around choosing MySQL and see if / how Oracle and/or Oracle XE matches up.  With XE, cost certainly goes away, so that shouldn’t really be a barrier.  The only barrier I’ve come up against is the 4GB database size limit in XE — seems a little small to me (I understand why there’s a limit) — I would think that 16GB would have been a better limit.

That being said, I’ve been playing with ways to convert MySQL databases into Oracle (XE or otherwise).  It’s kind of fun, since most of the Internet articles detail stuff going the other way.  We’ve got an Oracle XE database installed alongside a MySQL database on a Windows box for testing purposes.  (It’s also a place where I can work with XE and some of the neato web interfaces for administering it).

I’ll talk more about our experiences in future posts, but here’s some of our initial work:

I thought it might be easy to convert from MySQL to Oracle using database links over ODBC (heck, it’s a Windows box — ODBC is just sitting there ready to be used).  And, so far, so good — it was ridiculously easy to set up Oracle’s Heterogeneous Services (even if the name sounds complicated and threatening) :-)  I owe a lot of thanks to these simple crib notes.

Once I had it set up, I could easily select from and describe the tables on the MySQL server.  A couple of notes though:

  1. select count(*) produced really weird results — definately not correct.  Even select count(1) did too — I needed to reference an actual column name in the MySQL table in order to get correct results.
  2. A lot of the stuff in MySQL appears case sensitive — so you get used to enclosing table and column names in double quotes in order to reference them correctly.
  3. That includes username and password attributes in database links.

We’ll be trying some “create table as select” commands later on — I’m looking forward to seeing how we handle type conversions and text / long fields (I’m guessing not so well on the long fields).

For reference, we’re using the following versions: MySQL Server 4.1, MySQL ODBC Driver 3.51, and Oracle XE (Universal) 10.2.0.1.

Questions, anyone?

While not strictly an Oracle-related site, I found this Q&A site to be interesting.  I’ve seen a couple of attempts at Oracle Q&A wikis, but I like th interface on this one….

Jarred

Lately I feel like our systems are running like tops — so it gets harder and harder to identify areas to improve. 

I’m also finding that I have less patience with systems I get to “inherit”.  Everyone is probably familiar with “inherited” systems — these are the ones that were “outsourced” last year, or came in through acquisitions (and were written by folks with “little or no budget”).  And now they are reaching the limits of their scalability as they get loaded up with real production data or become more popularly useful.

A side note on developing without a “large budget”.  I recently met with a development group at another company and I was talking about how at my company we have a database engineering team (nee “Development DBAs”) that is responsible for all SQL and PLSQL work.  Our DB team is part of the overall development group and works closely with Java and Web developers to write all of the necessary database code.  We have roughly 5 DBAs to support about 80 developers.  In other words, our development organization is roughly 5% DBAs :-).

The company I was meeting with was very small — maybe 10 developers.  And one of their senior developers was aghast at the idea that Java and Web developers had to “wait” for Database developers to write SQL and PLSQL code.  “I’ve never worked anywhere that had such a LARGE team of Database developers”, he said.  “That doesn’t make sense — our Java and PHP people write their OWN SQL — why do you need Database developers?”.  Needless to say, I’ve been asked many times to provide advice on how to “fix” the database that their app runs on, since it’s not running very well. :-|

Anyway, back to “inherited” systems.

Maybe it’s because I’ve been spoiled by our environment where everything is fairly well modularized and just about everything is in PLSQL stored procedures — all SQL and PLSQL code is written by the database engineering team and their isn’t any SQL in the Java or Web tier — but, I’m amazed at systems that don’t do that and abuse other concepts besides.

I know I sometimes get “religious” about requiring all database access code in stored procedures.  But actually, that’s often just a “going in” position.  I can compromise — as long as the database access code is sufficiently modularized so that at some point in the future we can possibly move it into stored procedures.  You remember modular programming, don’t you?  You don’t?  You know — the idea that you write libraries of functions and “link” to them or call them through defined APIs?  I’ve been amazed at the amount of “inline” SQL I’ve been seeing lately — which leads to the “We can’t possible convert ALL of our SQL to stored procedures — we’d have to look at HUNDREDS of files to find all of them”.  Somehow this becomes an indictment of stored procedures — instead of invoking disapprobation for the poor non-modular programming practice in the first place.

I’ll close with a final example — this on resource usage.  Back in the day :-) I had the opportunity to work on client server systems — and everything was about latency and roundtrips.  I think today some people forget that even a web-based application which talks to a database still has client / server attributes.  The web server is a client to the database server (when it’s interacting with it).  As such, you should probably minimize roundtrips and latency between them.  I recently encountered a system which made an assumption that the latency between the 2 would essentially be zero and as such didn’t worry so much about reducing rountrips.  Simple actions on web pages would routinely require 100-200 roundtrips.  This would not have been tolerated “back in the day”.  And the one day when the database was relocated to an out-of-town data center, suddenly “data response time” became a problem.  :-|  Again, where was the outcry?  Over the profligate number of roundtrips?  No.  It was — “Why is the database so slow now?” :-)

(I used to advocate that developers work on systems that are slower and smaller than the ones customers will eventually deploy on — you’d be surprised at how fast and small the resulting code is.  Even today I prefer that my development databases have slower disks and CPUs than in production — keeps me working hard on writing more efficient code).