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.

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.

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.

Being more of a DBA than a sysadmin myself, I’ve noticed that there is a surprising dearth of information regarding how to actually get nagios set up to monitor MySQL, especially if you’re not experienced with nagios.

In my own experience, MySQL monitoring often ends up being a homegrown thing, especially if you’re a small shop. It is definitely a good idea to consolidate all of your monitoring, DB-related or not, into one solution: roll-your-own monitoring solutions can be brittle and burn you in the end. One example that comes to mind, a few years back i had a quick perl-based script to monitor replication and set it to mail a few people based on some threshold. Took me 1 hour to write and worked well enough, except, six months later, when some package upgrades were done on the machine I had the monitoring script running on, the mailer stopped working, and we found out the hard way about an alert situation with our replication.

Nagios

Nagios is something your systems/operations people are probably familiar with. In case you are the systems/ops person, it’s a 15-20 minute process to get nagios installed on a typical ubuntu machine. Make sure to set up nagios with the embedded perl interpreter in case you decide to make use of this feature in the future. Once you have it set up and monitoring, here’s how you can set up nagios to monitor your mysql databases:

You can grab some perl-based nagios plugins to monitor mysql here:

http://www.shinguz.ch/MySQL/nagios-mysql-plugins-0.3.tar.gz

Untar the plugins into /usr/local/nagios/libexec

If you want to check out how a particular plugin is executed, run it from the command line as follows:

vmware@vmware-desktop:~/dl/nagios-mysql-plugins-0.3$ perl check_repl_mysql_seconds_behind_master.pl --help

SYNOPSIS

check_repl_mysql_seconds_behind_master.pl flags

DESCRIPTION

Nagios plugin to check how many seconds slave is behind master...

FLAGS

help, ?      Print this help.
host, h      Host where database is located (default localhost)
password, p  Password of user root (default ><)
port         Port where database listens to (default 3306)
user, u      User for connecting to the database (default root)
critical|c   Seconds for critical level (default 600)
warning|w    Seconds for warning level (default 60)

PARAMETERS

none

As an example, let’s say we want to monitor the lag of a particular replicator. Nagios operates on thresholds, the first stage being a warning, the second being a critical alert.

Adding a Nagios Command

In your /usr/local/nagios/etc/objects/commands.cfg file, add the following “command” entry — you should see others like it. This specifies a type of command you will run on a given host:

# 'check_repl_mysql_seconds_behind_master' command definition
define command{
command_name    check_repl_mysql_seconds_behind_master
command_line    $USER1$/check_repl_mysql_seconds_behind_master.pl --host=$HOSTADDRESS$ --password=$ARG1
}

In this case we’re just using most of the default parameters, but if we wanted to specify a different critical or warning threshold, we could add it with –critical=$ARG2, etc.

Then, if you’re monitoring localhost (probably not in most cases) you would just add the following entry to /usr/local/nagios/etc/localhost.cfg:

define service{
use                             local-service         ; Name of service template to use
host_name                       localhost
service_description             MySQL Replication
check_command                   check_repl_mysql_seconds_behind_master!"<password>"
notifications_enabled           0
}

If you want to add another host to be monitored from this nagios instance, you can specify a new configration file, say, db01.cfg and write that to your etc/objects directory. Then add an entry to /usr/local/nagios/etc/nagios.cfg as follows, after making the appropriate adjustments to the hostname in the service:

cfg_file=/usr/local/nagios/etc/objects/db01.cfg

If everything has been configured correctly, you should be able to restart nagios with /etc/init.d/nagios restart, and then go into your nagios console and see the configuration entries for your new command and service, and also see if your slaves are far behind

Once you have one service added in this way, adding new ones becomes a lot easier. I hope to investigate some more of the performance monitoring features of the nagios mysql plugins, but i’ll leave that for a future blog post.

Read/write operations/second:



Transactions/second:

Somewhat as I expected, mysql-proxy presents a bit of overhead when load is light. With only 8 concurrent threads, running against the proxy pointing to 4 equal database servers is slower than hitting one server itself.

As load goes up, however, you can see that the proxy scales reasonably well, while the accesses to the direct systems begin to tank rapidly after 64 concurrent threads (I lost some of my 128 thread results inadvertently and the instances are destroyed I’m fairly comfortable in interoplating the result to be roughly in between 64 and 256 )

Somewhat unsettling, however, was the fact that sysbench or mysql proxy failed to work beyond about 500 concurrent threads. My debug output for sysbench just stops here:

DEBUG: mysql_real_connect(0×9adec70, “ip-10-251-75-160″, “sbtest”, “(null)”, “sbtest”, 4040, “(null)”, CLIENT_MULTI_STATEMENTS)
DEBUG: mysql_init(0×9ae51b0)
DEBUG: mysql_options(0×9ae51b0, MYSQL_READ_DEFAULT_

On the servers themselves, I can see the 125+ connections to each individual box, and all looks normal. I tried tweaking some of the thread settings in ulimit and sysbench itself to no avail, but didn’t have a ton of time to play with it. I decided the results were already interesting enough and will see if I can revisit this another time.

Another reason i am not so concerned is that more than one proxy can be utilised in many typical environments, so the ability of a single mysql-proxy instance to handle 512+ concurrent connections might not be show-stopper.

If anyone out there has done some similar tests with mysql proxy, i welcome your feedback!

Before promoting 5.1 to some beefier hardware, I’ve got a few large tables sitting on a small SATA-based machine. Noticing that a few were growing faster than I expected, i decided to pause my loading and convert another one of them to the archive storage engine to save some space.

I certainly saved space.

By trying to do too much at one time, I had some other temp files lying around on the same filesystem, and my conversion to the archive storage engine failed at about 80% through with my disk full.

mysql> alter table w_stats engine = Archive;
ERROR 1030 (HY000): Got error -1 from storage engine

mysql 5.1 has an unfortunate bug that I discovered, and reported, that causes the unfinished archive files to remain after the failed conversion, and not the original data. My disk space problems are no longer much of a problem

Now I know that this was basically my fault, and that I should have been more careful with my system, but I would have preferred maybe an embarassing error message, a slap on the wrist, not the loss of a 150GB table! For example:

mysql> alter table w_stats engine = Archive;
ERROR 1030 (HY000):  df -h is your friend

Well, it sounded funny in my head at least…

Now, these results come with the following caveats:

- These are not extensive, robust benchmarks, they’re just a few tests I ran against my very specific workload. I encourage people to try the different schedulers on different workloads and hardware and share results!
- These are test results with a large, sequential I/O workload that is very different from the random I/O intensive tests that most people are interested in.
- These are test results against a single ATA drive. These results are probably irrelevant in a hardware RAID context

In a future blog post, I will try to dig a bit deeper into these results and try the tests against hardware and software RAID. What i think can be taken from this already, and the referenced articles, is that CFQ should probably be used if your kernel has direct knowledge of the physical layout of your disks, eg. through LVM. Otherwise, deadline or even noop is probably your best bet if you are using hardware RAID, putting the burden on the hardware to decide how best to execute reads and writes.

the mission

I decided to convert a relatively large statistics table (750m rows, 140GB in size in about 10 partitions) on a test machine from MyISAM to the Archive storage engine. After a long conversion process, my data, on disk, ended up being about 21GB, for an impressive compression ratio of 6.7:1.

Prior to MySQL 5.1, one of the drawbacks to the archive storage engine was that you could not index it; however, with partition pruning, you can get yourself a “free” index on a large archive table by splitting it into date-based chunks, whether by day, week, month or year, depending on the volume of data you want to make accessible.

I decided to see how long it would take to run a simple aggregate query against a portion of one of the partitions I had created.

mysql> explain partitions select count(*) from stats where StatsTime between ‘2007-05-01′ and ‘2007-05-31′;
+—-+————-+————–+————+——+—————+——+———+——+———-+————-+
| id | select_type | table        | partitions | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+—-+————-+————–+————+——+—————+——+———+——+———-+————-+
|  1 | SIMPLE      | stats        | p_2007q2   | ALL  | NULL          | NULL | NULL    | NULL | 59558354 | Using where |
+—-+————-+————–+————+——+—————+——+———+——+———-+————-+
1 row in set (0.00 sec)

On disk, the partition was about 1.7G:

-rw-rw—- 1 mysql mysql 1.7G Jun 16 17:16 stats#P#p_2007q2.ARZ

After issuing the query, I watched iostat. The file being accessed was completely absent from filesystem cache because i had previous run a long query against a large MyISAM table. This is what I saw, fairly consistently:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
24.96    0.00    0.75    0.88    0.00   73.42

Device:         rrqm/s   wrqm/s   r/s   w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sda              48.55     0.40 62.44  0.60     7.36     0.00   239.32     0.39    6.20   5.42  34.20


As expected with a highly-compressed table, the query speed is CPU-limited: the disk can go much faster but we are fully utilizing one of the four cores (notice 25% cpu utilization).

Back-of-the-envelope calculations suggest that, at 7.3mb/s read throughput from disk, and a 1.7G file to be read, that the query will complete in about:

1700mb / 7.3mb/s = 232s = 3m52s

mysql> select count(*) from stats where StatsTime between ‘2007-05-01′ and ‘2007-05-31′;
+———-+
| count(*) |
+———-+
|        0 |
+———-+
1 row in set (3 min 52.19 sec)

Dead on!

the possibilities

What is interesting is that from this we can deduce the effective throughput of an archive table based on how much data the disk is feeding the cpu.

If we attempted to run such a query on the original MyISAM partition (about 11.5GB), in order to complete the result in 232s, we would require a throughput of :

11500 / 232 = 49mb/s

50mb/s is not lightning fast, it’s about what you’d expect from a single SATA disk, but it’s still fairly good considering the amount of compression we’re getting.

But wait! You still have 3 more cores left on the machine. Now to confirm another hypothesis:

Testing two simultaneous queries, against the same partition:

select count(*) from stats where StatsTime between ‘2007-05-01′ and ‘2007-05-29′;
select count(*) from stats where StatsTime between ‘2007-05-01′ and ‘2007-05-30′;

Most of the data was in fs cache, so our iostat looked like this for most of the time:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
50.70    0.00    0.85    0.00    0.00   48.45

Device:         rrqm/s   wrqm/s   r/s   w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await  svctm  %util
sda               0.00     0.50  0.00  0.80     0.00     0.01    13.00     0.00    0.38   0.37   0.03
hda               0.00     0.00  0.00  0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

Final runtime for both queries was 208s and 217s; suggesting that the queries were almost perfectly parallelized.

If we needed to run a beefy calculation against a year’s worth of data, for example, we could run four parallel queries (one per core per quarter) and aggregate the results for each query in a separate client. Now our “equivalent” 50mb/s throughput on one core has become 50mb/s * 4 = 200mb/s.

If you get a slightly faster disk, you could fully utilize an 8-core box for a throughput of 400mb/s.

And if your disk space is 750GB, factoring in the compression ratio, you are storing the equivalent of 5TB of non-compressed data with a potential read throughput of 200mb/s. Not too shabby for low-end hardware!

The most important new feature, in my eyes, is the new partitioning capability. When I get some time, I will write up a more complete post on my experiences so far with 5.1 partitioning, but I am going to try to keep the turnover on posts a bit higher, and post smaller things on here more regularly.

Partitioning has the potential to make large tables in mysql manageable once again. This is music to the ears of anyone that has had the misfortune of having to learn, the hard way, about MyISAM’s often painfully slow “Repair by keycache” loading and repairing of large tables with unique keys. Add that to MyISAM’s propensity to table corruption, especially with large tables, and you have a ticking timebomb on many pre-5.1 servers out there. If you are lucky, you can repair a 10GB table in a few hours, something you don’t want to have to wait around for at 3am which is invariably when those big tables get screwed up and you have to repair them.

Of course, many people use application-level partitioning to deal with this problem, or used the inadequate MERGE storage engine to deal with this. But partitioning at the database level has the potential to make things just so much easier. If you partition wisely, you can turn a 10GB behemoth table into a series of 500mb chunks, each of which is (I’m hoping here) less likely to corrupt, and far more manageable by MySQL individually.

Beware of this bug though! It’s duplicated many times, and might catch you off guard if you plan to be an early adopter of 5.1. Essentially, you can’t really repair or optimize partitioned tables, at least not directly. The documentation still contains references to the ALTER TABLE … REPAIR PARTITION statements which don’t work at all, as of 5.1.24. For eg, with my partitioned InnoDB table tst_p:

mysql> optimize table tst_p;
+-------------+----------+----------+-----------------------------------------------------------+
| Table       | Op       | Msg_type | Msg_text                                                  |
+-------------+----------+----------+-----------------------------------------------------------+
| stats.tst_p | optimize | note     | The storage engine for the table doesn't support optimize |
+-------------+----------+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table tst_p repair partition p_pre2007;
ERROR 1178 (42000): The storage engine for the table doesn't support repair partition

Luckily, there is a workaround. Using ALTER TABLE REORGANIZE PARTITION, you can effectively rebuild the partition which will optimize/repair it. I’ve yet to test this with a truly corrupted table, however. The downside of this is that you have to have the partition definition handy. Luckily it seems to be smart enough to stop you from making a mess — notice the error in the first one, and the second statement goes through correctly:

mysql> alter table tst_p reorganize partition p_pre2007 into (partition p_pre2007 values less than (to_days('2007-12-31')));
ERROR 1520 (HY000): Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range
mysql>
mysql> alter table tst_p reorganize partition p_pre2007 into (partition p_pre2007 values less than (to_days('2007-01-01')));
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

As I get more time i’ll post more cool partitioning tricks.

the countered threat from Oracle

Oracle made some strategic purchases in the past few years to establish a foothold in the embedded and front-end database market by acquiring Sleepycat (maintainers of BerkeleyDB) and InnoBase (makers of InnoDB storage engine for MySQL). These two also happened to provide the only two transactional backends for MySQL, whlie InnoDB is the only one to be used widely in practice. While this was looked upon as a disaster for MySQL, it was really not a major issue for a couple of reasons:

• The InnoDB source has been GPL’d, so even if Innobase were to completely abandon maintenance of the codebase (which it has not), the community can step in to fix bugs. For now, the status quo prevails
• MySQL acquired Netfrastructure (sp?) and begun the process of porting the backend to the new Falcon transactional storage engine
• MySQL also began the process of fixing up some of the severe defecits in the MyISAM storage engine and branded it Maria

So, from a strategic perspective, it looks like MySQL is taking the right steps to counter the threat from Oracle. An infusion of money from Sun will speed up development on many of these initiatives.

a general picture of the database landscape

Let’s take a step back for the moment and take an unscientific look at a few players in the current database market. This is not rigorous, exhaustive market research, it’s just my observations over the past 9-10 years or so:

Teradata: The data-warehousing champ, a reputation for high quality but also prohibitively expensive for many with large data volumes

DB2: Strong in the institutional market, mainframes and data warehousing; not much use in the web/internet world

Oracle: The jack of all trades. Expensive, but no CTO would ever be fired for picking Oracle for almost any purpose, whether its an OLTP system or data warehouse

PostgreSQL: The Betacam of open source databases. Highly-functional, stable and scalable. Over the years, unfortunately saddled with a somewhat-unjustified reputation for being slow and difficult to use (in comparison to MySQL) and a militant userbase that spends an inordinate amount of time bashing MySQL instead of evangelising its capabilities

MySQL: A simple, fast database with a reduced featureset that works well for web applications

My belief is that MySQL owes its popularity mostly due to the fact it is perceived to be very fast when using it to build simple apps. Over the years it became the ‘default’ web database, with most hosting providers using it as the backend in combination with PHP. Many are now providing PostgreSQL hosting, but this wasn’t always the case.

mysql vs. postgresql

While they are both OSS databases, MySQL and PostgreSQL are very different. PostgreSQL has its roots in academa and the defense industry, and a trip into the source code is like a trip down a memory from a computer science class. MySQL, on the other hand, feels and has been developed much more like a commercial product, with a focus on functionality and speed and less interest in elegance and standards compliance. These are sweeping generalisations of course.

My reason for bringing up this comparison is to make the following important assertion:

For a particular project, given the choice, DBAs choose PostgreSQL, developers choose MySQL.

DBAs like tablespaces. Good query optimizers. Tables that don’t randomly corrupt.

Developers like databases that reduce their need to think. A system that will let you send 100 queries over 100 separate connections without any apparent overhead compared to sending 100 queries over the same connection.

I come to this conclusion after nearly 2.5 years as a MySQL “DBA” working for companies of various sizes in the internet industry. MySQL, in many cases, has been reduced to a glorified flat file system, and many non-junior developers do not even understand the most basic SQL optimization.

It’s not hard to see why MySQL is far more popular than PostgreSQL, given that developers are more numerous and higher up the application stack.

Amazon SimpleDB and Google BigTable

The users of MySQL may prove to be a fickle bunch, however. Oracle never was never that much of a threat all along, and two unlikely competitors in the database space will change the rules of the game. Developers like the simplicity and avoiding-of-thinking they get with MySQL. Once a site gets to a certain scale, the database become a major bottleneck. Complex yet surprisingly-robust sharding architectures have been developed to deal with scaling MySQL beyond the capacity of one machine, but this is not for the faint of heart.

BigTable and SimpleDB look ready to take developers back to the simpler days when MySQL was a fast, reliable persistent store, allowing them to focus on their strengths. HBase, while still very alpha-ish, also holds great promise. Many people have criticised BigTable and SimpleDB for being, well, just a big table and a simple database. But that’s precisely what MySQL was, and it did it quite well for a long time. Developers in the internet age simply don’t care about the things DBAs and database developers of a previous era did. They want three things: performance, availability and more performance. Strict ACID compliance is simply not that important in an age when entire internet empires are built off of clicks worth as little as $0.07 and page views worth $0.00001 — but high availability is important.

the pincer

Sun has acquired MySQL at a time when the old guard of the database world is becoming more aggressive, and the new guard of software-as-a-service providers are swooping into the space to appeal to IT managers initially happy with the open source licensing, but not thrilled with the non-trivial total cost of ownership (read: paying a DBA who must continually wake up at 230 to repair your corrupted MyISAM tables)

prognosis

Well, let’s put it this way — as someone that abandoned the proprietary database world a few years ago to work solely with open-source technologies, I feel that I need to start learning more about HBase, BigTable and the like to survive in this marketplace. MySQL may not have seen this strategic threat coming, but they better start working, quickly, to make MySQL scale better and more easily, or it will begin to lose its place as the “default” database of new web applications.