mysql 5.1 is nearing release, with the present release candidate 5.1.24.

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.

Over the years, the database world has been buzzing with the strategic threat posed to the established players by upstart open-source database systems. Oracle and IBM would no longer be able to gouge defenseless small and medium-sized businesses of non-trivial portions of their IT budgets for a mere database licence. Oracle, IBM and Microsoft, for their part, have tried their best to respond to this threat, but it is clear that they cannot simply squash open-source products, but rather evolve with the changing landscape.

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.