The rumoured acquisition of Sun by IBM makes sense on many levels. IBM has always been a strong proponent of business-friendly open source and is probably more invested in Java than Sun itself. The indirect impact on recently-acquired MySQL is a bit personal for me, however.

Many moons ago, I worked in the IBM software lab in Toronto where DB2 UDB is primarily developed. I learned a great deal there, but something stirred within me, a desire for freedom and flexibility in software. I took a risk and moved to California, working for a number of start ups, believing that open source database technology (and software in general) was the wave of the future, and I wanted to work in companies that were willing to use OSS software.

To say the least, many of my then-current and former IBM colleagues that had taken cushy jobs with big companies to administer large DB2 installations, or were on track to do so, thought I was crazy. MySQL was a toy database, and PostgreSQL was just a research project! In retrospect I feel it was the right decision.

So, here we are, years later, and IBM may now end up owning MySQL.

On the positive side, I have no doubt of IBM’s commitment to open source and that they will continue to support MySQL with the tremendous resources it can bring to bear. It would be quite ironic if the Toronto lab had some people shift to MySQL development!

However, if IBM’s previous acquisitions are any indication, they will almost certainly push MySQL as a free “entry-level” database to a larger pool of customers and smooth the way for eventual upgrading to more expensive enterprise products. This is not inherently a bad thing, but it could translate into poor support and release-inflation (i.e. beta = alpha, GA = beta, etc) as a way to encourage captive customers to just bite the bullet and upgrade to DB2.

IBM may be able to squeeze quite a bit of profit out of MySQL in this way from enterprise customers who are used to this kind of abuse, but there are significant challenges:

They will need to go out of their way to convince skeptics like myself that quality and stability will not be compromised as MySQL gets integrated into IBM’s product offerings, otherwise MySQL users without deep pockets may begin defecting to postgres rather than be stuck with a key part of their infrastructure poorly-supported.

Then there is the still-unresolved problem of Oracle owning the maker of MySQL’s only viable transactional engine, InnoDB, an issue I wrote about last year when Sun acquired MySQL. With the release of the new InnoDB as a downloadable “plug-in”, Oracle has made it clear that they want to track and find as many of MySQL’s customers as they can.

Most important is that IBM will need to maintain MySQL’s reputation as the DB of choice for new web sites and other budget-conscious startups. Mindshare is everything. This is inherently difficult to track and acheive, because the next twitter will be developed by a few clever people who start up something for fun and just download all the OSS tools they need, based on what others before them did. Right now, typically, that’s a standard LAMP stack. If people starting up sites today begin to question the “M” part of LAMP (eg. swap postgresql for mysql), this effect on MySQL’s reputation and stature won’t be obvious for quite a while but it will be difficult to reverse.

I’d like to take a step back from technical issues to distill some of my thoughts on the challenges of data warehousing in the 21st century.

Having worked on a number of warehouse projects in different industries over the years, I’ve encountered many challenges, some failures, some successes. One thing is certain: all organizations that have a reasonable amount of data should be building a data warehouse if they don’t already have one. In 2009, given the economic atmosphere, no one wants to wait as long, or pay as much, as they did in 1999 to get one.

While this is a huge opportunity for open-source competitors like MySQL, it comes with big challenges for an organization that thinks it will get a $10MM warehouse (in 1999 dollars) for $300,000 (2009 dollars).

My contention is that in a web-connected, high-traffic and high-speed world, a monolithic approach with a rigid set of requirements, and a project team isolated from the business through layers of product and project managers is an almost guarantee of a disastrous investment of resources into a product that will not satisfy the needs of an ever-changing business. Most of my reasons for believing this follow directly from the arguments made in favour of agile development in software engineering. In a data warehousing effort, I believe some of these reasons are even more compelling, yet many do not see a warehouse project as a typical example of something that can be made “agile”.

For those unfamiliar with agile development, I’ll crudely summarize it as follows: a project that is lightly specified and built with a continuous loop of user/business feedback is more flexible and produces better results. This usually takes the form of implementing small, usable features called “stories” every few weeks. They can be thought of as a series of small prototypes. The strongest argument for developing in this way is that requirements and the business environment can change rapidly. Users may know what they want at a given point in time (and even that’s a somewhat optimistic assumption) but may change their minds and produce more innovative results if they are able and encouraged to make course modifications after seeing initial prototypes.

In undertaking a new, or improved warehousing effort in an organization, it is crucial that users have access to and are encouraged to use new data that are being produced. This makes users more comfortable with the results they work with, easing over time any psychological aversion they may have to trusting new spins on numbers. More importantly, developers can write specified test cases until they are blue in the face, but nothing can replace the eyeballs of a user that can immediately tell when numbers do not look right.

In a monolithic approach, a bug can creep into the processing pipeline and cause many GB or TB of data to be incorrectly processed until someone notices it, if no one is working with the data. If users frequently make use of new data, bugs can be caught quickly and the time spent fixing them far less.

A working example

While this sounds good in theory, an entire book could be written on how examples of this could work in practice. I’ll just use a contrived example that i’m sure many can relate to.

Your company has grown quickly in the last couple of years, and has always collected in log form tons of data that never get used. Other priorities prevented much from being done with it, but recently someone in management read an article in a CIO/CTO magazine about “data mining” and now you’ve been stuck with the task of buiding a warehouse with all of this data.

In a monolithic, 1999-style approach, the CTO would have Oracle or IBM come in with a team of 10 highly-compensated consultants. A few feasability studies, $10MM and 2 years later, you’d have a brand-spanking-new data warehouse full of data that a large majority of which the spindles will not touch even once.

Luckily, we are in more enlightened, frugal times, and many companies now undertake this effort themselves after recovering from Oracle sticker shock. Here’s a series of starting tips to the company in the situation above:

• This is kind of a rule of thumb, but to begin with, forget about all historical data that is too old to impact payments or revenue. If your company reconciles payments and/or revenues, say, every 60 days, the degree of interest your analysts and management team have in data from 61 days ago compared to 59 days ago is probably substantial.
• Listen to the CTO’s and management team’s vision for what they want to do with all this data. Listen to their thoughts and keep them in the back of your mind, but alsoremember that Rome wasn’t built in a day
• Now that you’ve discarded the mental burden of having to come up with a way of scaling your computations on many years worth of data, get an off-the-shelf box with a couple of large hard drives, talk to your analysts about something interesting that can be done with the last 60 days worth of data with a maximum development time of 2-3 weeks. This will force the people that work with your data on a daily basis to decide what is most important to them. Be highly suspicous of those that say they need “everything”
• After 3 weeks, you should have a self-maintaining system, processing whatever it processes on its own and without your intervention. Show it to the anlaysts, and get their feedback. If all goes well, they will be excited to use the new data and it will very quickly becomea critical component of their jobs . Remember another rule of thumb: once users have been given data, and actively use it, you take it away from them at your peril!
• If your first “user story” was a success, most likely you will have to fight with the analysts over what data gets included next. Again, do not reprocess back data no matter how much they say it “would be useful” — stick to that 60-day window.
• Rinse. Repeat.
• After a few iterations, you should be in the enviable position of having a hacked together system running on an off-the-shelf box. And this is fine, because far more important things are solid — the user’s confidence in the data they are getting, the management team’s confidence that you are giving the business the data they need, and your confidence that what you are building is being used.
• Now sit the managers and analysts down for “the talk” in which you describe to them that you need the next 3 weeks to prevent the system from breaking. This may involve getting proper hardware, or simply re-architecting the system. They will hate you for it just like a child will hate you for taking their steady supply of candy away, but remember that you must be a responsible parent.
• Rinse. Repeat.
• Receive accolades.

I hope that no one takes my tips too seriously. Certainly referring to your users as “children” will not win you many allies. I also certainly do not advise a completely chaotic development model with zero planning. But I think those of you that have been involved in multi-year data warehouse projects with vast project teams doing gap analyses, feasibility studies and book-length project plans will agree that working on something collaboratively over a few short iterations can’t be all bad. You may even give the users something they want and not break the bank while you’re at it.

I have been using partitioning in MySQL 5.1 since it has been in late beta, and with the odd bump I’ve been quite satisfied with its stability and ease of use. Perhaps the days of maintaining nasty application logic to maintain tables like table_yyyymm manually are finally over!

While some control freaks out there might still prefer the painful approach of manually keeping dated tables and using merge tables/views to link them together, you still have a lot of flexibility in how you manage individual partitions with the ALTER TABLE REORGANIZE/CHANGE/ADD PARTITION commands.

One trick i’ve discovered to work pretty well, is that you can swap in and out partitions right out from under MySQL’s nose. This can be useful in some situations.

WARNING!! This is not supported by MySQL to the best of my knowledge, and possibly is a risky, bad idea. Proceed only if you think you know what you’re doing!

Let’s say we have a table big_table, with self-explanatory partitions

p_200811
p_200812
p_200901

In your data directory, assuming you are using MyISAM, you will see MySQL has created files like these:

big_table#P#p_200811.MYD/MYI
big_table#P#p_200812.MYD/MYI
big_table#P#p_200901.MYD/MYI

Now on the server, running live, to make sure MySQL releases any locks on files at the OS level:
LOCK TABLE big_table WRITE; # alternatively use FLUSH TABLES WITH READ LOCK to lock whole DB
FLUSH TABLE big_table;

Now let’s say you want to do some intensive processing on your November data set. You can simply copy  the file to a temporary location, or possibly to another machine:

cp $DATADIR/dbname/big_table*200811* /tmplocation

Then you can unlock your table with UNLOCK TABLES;

Let’s say you have a box you want to do this on. Simply create a table-structure only copy of your table on that machine (*exactly* the same layout!! Bad Things(TM) will probably happen if this gets confused). Then repeat the procedure above in MySQL and on the OS:

mysql> FLUSH TABLES WITH READ LOCK;
$ cp /tmploc/big_table*200811* $DATADIR_SECOND_BOX/dbname/ # this overwrites the empty files; make sure they are owned by “mysql” or the db owner!
mysql> UNLOCK TABLES;

After unlocking, mysql will reload the files, and assuming the permissions and table structures are all correct, doing a count(*) on your date range should confirm that the data has moved over successfully. Now apply your datafixes, and repeat the procedure to return the data back to the host machine!

One possible application of this approach could be to distribute a very large index rebuild to a number of machines if your tables are partitioned into several chunks.

Given that InnoDB is sensitive to the dbname, I don’t expect that this would be possible with innodb tables, although I have not tried. I expect it to work with the ARCHIVE engine but also have not confirmed.

Hoping to get some comments and suggestions on how to improve on this process, or hear about other people’s experience hacking at partitioned tables.