Looking at MySQL from an IDS perspective: Introduction

This is a new series published on The Informix Zone. The purpose of this series is to take a deeper look at the popular open source DBMS MySQL that is now owned by SUN Microsystems.

MySQL's Popularity and Weakness

It is no secret that MySQL is very popular primarily in web based environments that mainly use the GPL based MySQL Community Edition. Slowly SUN makes it's way into the enterprise with the MySQL Enterprise Edition subscription as well. It is also no secret that MySQL has scaling problems when the data as well as the number of concurrent client sessions increases. The bad thing is that MySQL doesn't scale well on today's common and cost efficient Intel or AMD based multicore SMP machines:

MySQL's answer to scalability limitations: Scaling Out

The solution to circumvent MySQL's scaling problems is in many cases a scale out approach by replicating the data to other MySQL instances running on different machines. Those instances are normally readonly as there is only a uni-direction replication support available (no update anywhere). The application needs to be aware of the replication slaves and decide between write and read transactions to distribute them to the appropriate instance (primary or slave). Scale out works to a certain extent but depending on the mass of machines you need, your environment is getting more and more complex and administration intensive. Another possible solution is the MySQL Cluster product. However there are still many restrictions, which I will discuss separately, that makes MySQL Cluster only a choice for some very limited environments.

SUN's bumpy road to increase MySQL scalability

SUN is aware of the MySQL scaling limitations and is addressing them. I'm not saying that they are not able to do that. They have a lot of experience in those areas and some smart engineers that are able to improve the software to better meet the requirements of modern multicore machines. However it will take quite some time and the situation isn't getting easier for them as their main transactional engine InnoDB is now owned by the competition (big O). Yes, they are working on alternatives like Falcon (InnoDB replacement) or Maria (transactional support for MyISAM tables). But again it will take quite some time until they become as stable and efficient enough to be considered to be the foundation of a critical production environment.

Different type of users

One big difference between MySQL and IDS that I currently see are the type of peoples using the DBMS. MySQL is the M in the LAMP - Linux Apache MySQL Perl/PHP/Python - stack. That is one of the reasons that it has a high popularity among (young) programmers especially in those web based environments. But a major part of the programmers are not DBA's and they tend to to disregard some important aspects of a DBMS like a robust backup/recovery implementation, scalability or ACID characteristics.

IDS on the other side is very popular among experienced DBA's that praise it's reliability, scalability and compact administration. It is also popular among programmers that embed it as part of their application. But it hasn't the same popularity that MySQL has in web based environments.

During the next issues of this series I'll go into more detail concerning the differences and similarities as well as advantages and disadvantages of MySQL versus IDS technology.

Stay tuned !