Comparison of the three popular MySQL branches

Brief introduction

MySQL is one of the most popular free open source programs in history. It is the backbone of the database for thousands of websites and can be used as a testament to the exponential growth of the Internet over the past 10 years.

So, if MySQL is really that important, why is there a growing number of high-end derivatives of core MYSQ products? This is because MySQL is a free open source application, so developers can always get their code, modify the code to their own ideas, and then distribute the code themselves. For a long time, there is no trusted MySQL branch in the developer's own production environment. However, this situation soon changed. Several branches have attracted many people's attention.

Why do you want to branch?

Why do I need to branch MySQL? This is a very reasonable question. Thousands of websites rely on MySQL, and for many people, it seems like a good solution. But that's usually the case, and for many people it's not necessarily for everyone. This has prompted some developers to develop a better solution to their needs. What could be better than converting a good solution into a perfect solution?

Let's look at the details of these branches looking for change. Some branches think that MySQL has become too bloated to provide functionality that many users would never be interested in, sacrificing the simplicity of performance. If people are particularly happy with the more streamlined MySQL 4, why add additional complexity to MySQL 5? For this branch, a better MySQL branch should be simpler, faster, and therefore less powerful, but it will make these features extremely fast and keep the target audience in mind, in this case the target audience is the high availability site.

For other branches, MySQL does not provide enough new functionality, or it is too slow to add new features. They may think that MySQL does not have a development situation with the target market for high availability sites, which run on multi-core processors with large amounts of memory. As anyone familiar with MySQL knows, MySQL offers two storage engines: MyISAM and InnoDB. This branch believes both of these storage engines do not provide what they need, so they create a new storage engine that works well for their goals.

In addition, the top goal of some of the branches is to be a replacement for MySQL, where you can easily access their branches without changing any code. The branch uses the same code and interface as MySQL, making the transition very easy. However, another branch claims that it is incompatible with MySQL and needs to change the code. The maturity of each branch is different, and some branches claim to be ready to go into production, while others claim that they are far from being able to reach the highest goal.

Finally, there is still little certainty about how MySQL will evolve under Oracle. Oracle acquired Sun and also acquired MySQL, and now Oracle controls the MySQL product itself and leads the development community to develop new products. Because Oracle already has a business database, there are concerns that they may not have enough resources to keep MySQL in the lead. So many branches are also the result of these potential concerns, and they worry that MySQL, as a leading free open source database, may have too few features, too slow release cycles, and more expensive support.

XtraDB

XTRADB is a standalone product, but it is still considered a branch of MySQL. XTRADB is actually a storage engine for MySQL-based databases. XTRADB is considered an additional storage engine for the standard MyISAM and InnoDB that have become part of MySQL. MySQL 4 and 5 install each table using the default MyISAM storage engine. InnoDB is also a relatively new choice for storage engines, where database administrators and developers can select the storage engine type based on each table when establishing a database. The main difference between the two storage engines is that MyISAM does not provide transactional support, and InnoDB provides transactional support. Other differences are many subtle performance differences, and InnoDB provides a number of subtle performance improvements compared to MyISAM, and provides greater reliability and security when dealing with potential data loss. It seems that InnoDB is a more suitable storage engine for future improvements, so since version 5.5, MySQL has changed the default storage engine from MyISAM to InnoDB.

Based on these advantages, the INNODB storage engine itself splits a branch, an updated storage engine called XTRADB. How new is this storage engine? It was first released by Percona 3 years ago, so it's relatively new. It is designed specifically for modern high-availability Web sites that run on modern servers. It is designed to run on a server with more than 10 or more cores and large memory (32GB and more). Any company can purchase these types of servers from a server management company, so the database should be designed to take full advantage of these servers.

The XTRADB branch has another goal, which is to become a simple alternative to the InnoDB storage engine, so that users can easily switch their storage engine without changing any existing application code. XTRADB must be backwards compatible with InnoDB to provide all the new features and improvements that they want to add. They have achieved this goal.

How fast is the speed of xtradb? One of the performance tests I found showed that it could handle 2.7 times times more transactions per minute than the built-in MySQL 5.1 InnoDB engine. (see Resources). Speed is obviously a factor not to be overlooked, especially when considering alternatives.

Percona

XTRADB offers some great improvements over the built-in MySQL storage engine, but it is not a standalone product and cannot easily be placed into an existing MySQL installation. Therefore, if you want to use this new engine, you must use the product that provides it.

Percona server is a product that is released by Percona, the leading MySQL consulting firm. Percona Server is a standalone database product that provides users with the ability to swap out their MySQL installation and swap in Percona server products. By doing so, you can take advantage of the XTRADB storage engine. Percona server claims to be fully compatible with MySQL, so in theory you don't need to change any code in the software. This is really a big advantage, and it's a great place to control quality when you're looking for fast performance improvements. Therefore, a good reason to adopt the Percona server is to use the XTRADB engine to minimize code changes as much as possible.

In addition, they are the original authors of the XTRADB storage engine. Percona uses this code as an open source code, so you can find it in other products, but the original creator of the engine is the same person who wrote the product, so you can use this information at your own whim.

The following is a Percona server declaration from their own web site:

• Scalability: Handle more transactions and scale on powerful servers
• Performance: Percona server with XTRADB is extremely fast
• Reliability: Avoid damage and provide crash-safe (CRASH-SAFE) replication
• Management: Online backup, online form import/export
• Diagnostics: Advanced Analysis and detection
• Flexibility: Variable page size, improved buffer pool management

The final statement by the Percona team is that "Percona server is the closest version of the official MySQL enterprise release published by Oracle" and therefore differs from other branches that have changed a lot of the basic core MySQL code. One drawback of Percona server is that they manage their own code and do not accept the contributions of external developers in such a way as to ensure that they have control over the features contained in the product.

MariaDB

Another product that provides the XTRADB storage engine is the MARIADB product. It is very similar to the Percona product, but provides more underlying code changes, trying to provide more performance improvements than standard MySQL. MARIADB directly utilizes the XTRADB engine from the Percona, since they use the exact same engine, so there is no significant difference in using the storage engine every time.

In addition, MARIADB provides the standard storage engine provided by MySQL, namely MyISAM and InnoDB. So, in fact, it can be thought of as an extension set for MySQL, which not only provides all the features that MySQL provides, but also provides additional functionality. MARIADB also claims to be an alternative to MySQL, so when you switch from MySQL to MARIADB, you can install it without having to change any of the codebase.

Last but not least, the main creator of Mariadb is Monty Widenius, the original creator of MySQL. Monty established a company called Monty Program to manage the development of MARIADB, a company that hires developers to write and improve mariadb products. This is both a good thing and a bad thing: The upside is that they are the best of Maria's features and bug fixes, but the company is not for profit, but for product-driven, which can be problematic because a company that doesn't have a profit is not necessarily sustainable.

Drizzle

The last product introduced in this article is drizzle. Unlike the two products previously introduced, drizzle is very different from MySQL, and even claims that they are not a replacement for MySQL. They expect some major changes to MySQL and want to provide a great solution to solve high availability issues, even if that means changing the various aspects of MySQL that we're used to.

On the company's FAQ page, reading the questions provided, drizzle further emphasized its basic objectives. They were dissatisfied with some of the changes to the MySQL code after MySQL version 4.1, claiming that many developers did not want to spend extra money. They acknowledge that their products are not even compatible with SQL relational databases. This is really a lot different from MySQL.

With the habit of MySQL has changed so much, why should we consider this product? To be exact, the reason is the same as above, and drizzle is a major modification to the MySQL engine, which cleans up some poorly performing and unnecessary features, rewrites many of the code, optimizes them, and even converts the language from C to C + + to get the required code. In addition, drizzle did not end the modification, and the product was designed to take into account its target market, a multi-core server with a large content, a 64-bit machine running Linux, a server used in the cloud, a server hosting a Web site, and a server that receives tens of thousands of hits per minute. This is a fairly specific market. Is it too specific? Keep in mind that these types of companies are currently investing money in their databases, and if they can install drizzle instead of MySQL, then their server costs will be cut by half and can save a lot of money!

So, is everyone supposed to use drizzle? And so on, as drizzle repeatedly pointed out, it is incompatible with MySQL. So if you're using a MySQL platform now, you'll need to rewrite a lot of code to make drizzle work in your environment.

Although extra work is required to make it run, it is not as fast and easy to use as Percona or MARIADB. I introduced drizzle because, although it may not be your choice at the moment, it is likely to be a choice for some people in a few years. Because the goal of this article is to improve your understanding of the tools you use in the future, this is a great opportunity to introduce you to this product. Many leading DB experts believe that drizzle will be a choice for high availability database installations over the next 5 years.

Drizzle is a fully open source product that openly accepts the contributions of developers. It does not have a company that supports its development like MARIADB, nor does it have a large number of external developers who contribute to it, as Percona does. Drizzle has a good space for growth and offers some new features, but it may be necessary to rewrite most of the MySQL code.

Comparison chart

The following is an overview of the three MySQL branch products described in this article.

Conclusion

This article describes three new branches of MySQL products that are designed to address some of the problems they encounter when using MySQL. These three branches are free open source products. When you use them, you need to weigh their pros and cons based on the features that MySQL has provided. I believe that for most people who read this article, MySQL will still be the first choice to meet the needs of the database. I suspect that most readers of this article are the owner of a site that has 1,000,000 hits per hour. I would like to emphasize again that MySQL is still a very good product and a very good database for most use cases.

But for those who think their sites need more availability, scalability, and performance than current MySQL can offer, any of these 3 products may offer you the solution you need.---- Further, if you think your site will be a site that can make a lot of profit, consider using one of the three products to resolve them before the problem occurs.

Finally, the root cause of these MySQL branches is that some creators want to change some of the basic features of MySQL because they can't wait for MySQL to do the work themselves. In addition, Oracle's current situation threatens the future of MySQL, and many developers, including the original MySQL developers, are worried about the future of the product, and they are also concerned about whether Oracle will be put to the forefront of maintaining the product's leading database. These concerns seem to me to be reasonable, so keep these products in mind as we move towards the future.

Author Profile:

Michael Abernethy has worked with a variety of technologies and clients during his 12 years of technical career with Michael Abernethy. He now focuses on building better and more complex web applications, testing the limitations of the browsers in which they run, and trying to solve how to make Web applications easier to create and maintain. When he is free, he will accompany his children.

Comparison of the three popular MySQL branches