Women in Technology

Hear us Roar



Weblog:   Graying MySQL, and MySQL learns a second language (early conference report )
Subject:   MySQL is to DW what PostgreSQL is to OLTP
Date:   2005-04-19 08:05:48
From:   adamsj
Response to: MySQL is VHS to Postgresql's Beta though

I agree with you fifty percent, merlyn. PostgreSQL is the clear choice over MySQL for any sort of OLTP. Only now is MySQL adding features which are needed for OLTP.


However, those same features are of little or no value to MySQL as a DW platform--they're cruft. Why in the world would a properly designed data warehouse require triggers?


I understand why every RDBMS is now adding such featurs--after all, Oracle has them, so everyone else needs them, too. I wouldn't use Oracle for a DW on a bet, though. It's not capable of scaling to a sufficient size for a large enterprise, and it's not a good vslue for a smaller business. I do understand why MySQL, partnering with SAP, might feel the need to match Oracle feature for feature.


That's a shame. MySQL is built nicely for high performance in the DW space. Adding views was absolutely necessary, and even updatable views have their place. (Last year, I had to use updatable views to get around an incredibly stupid, incredibly popular etl product's inability to do what we needed.) But most of what MySQL has picked up in database features is unuseful for DW.

Full Threads Newest First

Showing messages 1 through 6 of 6.

  • MySQL is to DW what PostgreSQL is to OLTP
    2005-04-19 11:52:20  danielhanks [View]

    "I wouldn't use Oracle for a DW on a bet, though. It's not capable of scaling to a sufficient size for a large enterprise".

    What???

    Can you elaborate on that statement? What do you mean by scale in that context?
    • John W. Adams photo Probably I'm showing my bias, or my limited experience
      2005-04-19 12:07:35  John W. Adams | O'Reilly Blogger [View]

      I've had the good (or bad) fortune to never work on a data warehouse of under a terabyte. If you want something really big like, oh, Southwestern Bell has, you need DB2 or Teradata (SWB's choice).

      I don't work extensively with Oracle, but in my limited experience, it runs out of horsepower somewhere near a terabyte. Maybe the newer parallelism features will fix this.

      One session I really wanted to attend this year was Multi-Terabyte Data Warehouse and MySQL. Having seen the challenges involved in getting systems of that size to perform efficiently, and thus being a skeptic when someone claims they can do so, I'd really like to hear what he's got to say.
      • Probably I'm showing my bias, or my limited experience
        2005-04-19 13:45:18  danielhanks [View]

        I see. Where I work we have an 11T data warehouse running on Oracle. I don't work directly with it, but it does provide daily reports, so it must be working ok enough.

        Just my $0.02.
        • John W. Adams photo Is this a single installation, or do you use datalink to go from machine to machine?
          2005-04-19 14:20:28  John W. Adams | O'Reilly Blogger [View]

          I can't argue with a working installation, and my Oracle skills are rusty at best.


          But I'm curious--can a user open up SQLPlus and (assuming appropriate privileges) join two arbitrary tables without intervention by an administrator? If not, by my lights it's not a data warehouse.

          • Is this a single installation, or do you use datalink to go from machine to machine?
            2005-12-16 10:59:50  Tabooyah [View]

            I worked as a business intelligence analyst for a number of years where one of my primary duties was to perform not just 2 joins, but up to a dozen joins in order to collect data and format it into meaningful reports. Oracle has a number of great features that allow you to perform adhoc queries against million and billion-row tables very quickly. One such feature is materialized views which allows you to precompute dimensional summaries much like a MOLAP tool and store them in the database which can be access and just like a normal view. Another nifty Oracle feature is table partitioning which allows localized indexes and logic data grouping which cut query times down significantly. Analytic workspaces are another great Oracle DW feature which require more heavy lifting at the application layer.

            Anyone claming that Oracle isn't a well-suited DW application obviously has little experience with the product or isn't using it correctly.
          • Is this a single installation, or do you use datalink to go from machine to machine?
            2005-12-16 11:00:21  Tabooyah [View]

            I worked as a business intelligence analyst for a number of years where one of my primary duties was to perform not just 2 joins, but up to a dozen joins in order to collect data and format it into meaningful reports. Oracle has a number of great features that allow you to perform adhoc queries against million and billion-row tables very quickly. One such feature is materialized views which allows you to precompute dimensional summaries much like a MOLAP tool and store them in the database which can be access and just like a normal view. Another nifty Oracle feature is table partitioning which allows localized indexes and logic data grouping which cut query times down significantly. Analytic workspaces are another great Oracle DW feature which require more heavy lifting at the application layer.

            Anyone claming that Oracle isn't a well-suited DW application obviously has little experience with the product or isn't using it correctly.

Showing messages 1 through 6 of 6.