Mysql Database storage engine and branch status analysis _mysql

Source: Internet
Author: User
Tags ibm database mysql book mysql code mysql in mysql version rollback mysql backup percona
MySQL with the corresponding creators and internal developers of the departure, creating a variety of engines and branches, so that MySQL hopes to continue to flourish.

In MySQL after 2008 years of Sun's acquisition and the 2009 Oracle acquisition of Sun, the basic stagnation of the situation, in the foreseeable future, MySQL is sure to be shelved by Oracle and gradually destroyed by the snow. MySQL with the corresponding creators and internal developers of the departure, creating a variety of engines and branches, so that MySQL hopes to continue to flourish.

This article describes the current MySQL in addition to the main MyISAM, InnoDB, Heap (Memory), NDB and other engines and other engine development and status, as well as the MySQL backbone of the situation outside the branch, For our future better use of MySQL or other branches to build an understanding of the foundation.

MySQL Storage engine Introduction

Falcon Storage Engine

The Falcon storage engine is the storage engine that MySQL had high hopes for, mainly in the face of the Innobase company that Oracle acquired at the time, to replace a InnoDB storage engine. The Falcon engine's main driver was master Jim Starkey, who began developing in 2006 and released a beta version by 2008, and has so far not gone mainstream. In mid-2008, Falcon's main architect, Jim Starkey, announced his resignation from MySQL and joined a start-up company Nimbusdb as CEO to design and develop a relational/semantic database running on cloud computing, based on the current NoSQL market development in 2010. , his choice is correct, but the result is that Falcon into a position of no lead, leading to a state of poor performance and Half-Life.

The Falcon engine is a MySQL AB company based on Netfrastrucure's product development (Netfrastrucure company was acquired by MySQL AB), Falcon's original goal was to embed MySQL 6.0 in place of the InnoDB engine, Basically many function designs are designed according to InnoDB's goal.

Falcon is a multiple-CPU, developed with a modern hardware environment with large memory and database operational characteristics of typical Web applications, the main features include multiple versioning concurrency control, improved acid support, a B + tree index that supports prefix compression, data page compression (stored in compressed form on disk, in memory to Non-compressed form storage), group submissions, etc. In terms of functionality, there is nothing new, and the general implementation of a transaction-type storage engine must have some features (many advanced features such as multiple tablespaces, partitions, etc.), but its architecture has a lot of unique.

Some test results on the Web Falcon performance is still very bad, write speed is MyISAM 1/10 ~ 1/20,select optimization also have problems, added the index feel will also perform a full table scan. So, I finally feel Falcon is a cup of engine.

Falcon Characteristics: http://dev.mysql.com/doc/falcon/en/se-falcon-features.html

Falcon Test: http://blog.gslin.org/archives/2008/02/12/1425/

Falcon Manual: http://dev.mysql.com/doc/falcon/en/

SOLIDDB Storage Engine

The SolidDB storage engine is developed by solid information Technology (http://www.SolidDB.com), a transactional storage engine that utilizes MVCC for implementation. It supports both pessimistic and optimistic concurrency control, which is not currently supported by other storage engines. The MySQL version of SOLIBDB includes full support for foreign keys. It is very similar in many ways to InnoDB, for example, it uses a clustered index. SOLIDDB also includes an online backup feature with no additional overhead.

SOLIDDB has been acquired by IBM in 2008, primarily as part of an IBM database consolidation program, currently a role in the front-end data cache. IBM's acquisition of SOLIDDB was mainly due to the acquisition of TimesTen by Oracle in June 2005, which acquired solid information Technology's main competitor SOLIDDB, in order to rely on the memory database market.

The SOLIDDB product is a complete packaging program that includes the SOLIDDB storage engine, the MyISAM storage engine, and the MySQL server. The combination of SOLIDDB and MySQL appeared later in 2006. But the underlying technology and code was perfected by the company in the 15. Solid company guarantees and supports the entire product. It is based on the GPL protocol and provides a commercial version similar to the MySQL server form.

Performance, the SolidDB for MySQL open source database is once again proven to fully meet the requirements of high throughput, mission-critical applications for system performance and scalability.

But in the case of SolidDB being acquired by IBM, MySQL's acquisition of Oracle, basically SolidDB for MySQL is an engine that cannot continue to be used by MySQL, so it is also a MySQL engine with a cup.

Official website: http://www.ibm.com/software/data/SolidDB/

XTRADB Storage Engine

The XTRADB storage Engine is the Percona company's improved product for INNODB storage engine, the first release was released at the end of 2008. XTRADB is compatible with all features of InnoDB and is enhanced in terms of IO performance, lock performance, memory management, and more.

Percona is a MySQL technology consulting company, they have a well-known technology blog in the MySQL domain called the MySQL performance blog, and they wrote a very famous MySQL book called "High Performance MySQL", The Chinese version is also published. Their company also has a well-known MySQL backup tool called Xtrabackup.

XTRADB's design goal is also to replace InnoDB as a goal, based on InnoDB development, XTRADB 100% compatible InnoDB, which can often be considered xtradb as an upgrade or alternative version of InnoDB. In terms of performance, the current xtradb is very high, in most cases is also more stable, it is worth trying to use. The same xtradb is also a future feeling of a very promising storage engine, we should look forward to.

Performance test: http://www.mysqlperformanceblog.com/2009/07/14/performance-improvements-in-percona-5-0-83-and-xtradb/

Usage: http://www.ningoo.net/html/2009/xtradb_storage_engine.html

Engine Introduction: Http://www.percona.com/docs/wiki/percona-xtradb:start

Engine Download: http://www.percona.com/percona-builds/Percona-XtraDB/

Company website: http://www.percona.com

Performance Blog: http://www.mysqlperformanceblog.com

Maria Storage Engine

Maria was developed by the founder of MySQL, MyISAM's author Monty (Michael Widenius), named Maria because his third child was called Maria. Maria is a MySQL branch engine that Monty started developing at MySQL, after Sun acquired MySQL because of a disagreement with Sun on the MySQL team, then left Sun in early 2009 and set up Monty program Ab Company, dedicated to the development of the Maria engine, while developing a MySQL branch called MARIADB.

Maria is a MySQL storage engine that uses it to extend MyISAM so that files are not corrupted when the exception exits. Maria's main goal is to provide a crash recovery function as a better MyISAM. The longer-term goal is to become a full-featured transactional storage engine that supports acid, rollback, multiple-version concurrency control, row-level locks, group commits, and optionally unsupported transactions, eventually replacing MyISAM as the default storage engine for MySQL.

The Maria engine currently has a version of MySQL 5.1, basically an increase in the crash recovery function of the MyISAM, the use of table-level locks, but can do without conflict, that is, any type of update operations can be read at the same time, but multiple write operations cannot be concurrent.

Maria's Features:

Multi-version concurrency control, acid support

Enhanced backups can be done by copying logs

Efficient disk storage

Maria engine was first developed to replace the MyISAM storage engine, and now, as I understand some of the use of the company, the operation is very good, you can also try. Maria, with the current founder of MySQL, is also a very promising storage engine, worth looking forward to.

Maria Download: Http://askmonty.org/wiki/MariaDB:Download

Maria Handbook: Http://askmonty.org/wiki/Maria

PrimeBase XT (PBXT) storage engine

PBXT is the PrimeBase company's MySQL plug-in engine, its function and InnoDB similar, it is a transaction-type storage engine, and its design is very unique. One of its distinguishing features is how to use transaction logs and data files to prevent "write-ahead" logs, which can greatly reduce the cost of transaction submissions. This architecture gives Pbxt a lot of room to increase write concurrency, and tests show that it is faster than InnoDB in some specific operation. PBXT also uses MVCC and supports foreign key constraints, but it does not use a clustered index.

The main features are as follows:

MVCC: Multi-version concurrency control to make read operations unlocked

Transactional: Support startup start, commit and rollback and restore on

Acid standard: atomicity, consistency, isolation, persistence (a committed change cannot be lost)

Row-level locking: Update concurrent allowed maximum concurrent volume with row-level locks

Deadlock detection: Immediate notification If the client process has fallen into deadlock

Referential integrity: Support for foreign keys.

Write once: Pbxt avoids the schema double write usage log.

Blob stream: Streaming engine in a combined blob.

According to the test results, the PBXT storage engine version of TPS, with the growth of the number of threads, performance is more stable, performance is similar to InnoDB, in the long run, its goal is to be a replacement for the InnoDB storage engine. And now MARIADB this branch has put PBXT as a built-in storage engine, so it is an engine that you can try to use.

Performance test: HTTP://IMYSQL.CN/2008_07_25_INNODB_VS_PBXT

Engine Download: http://www.primebase.org/download/index.php

Official website: http://www.primebase.org/

MySQL Branch Introduction

MARIADB Database

MARIADB is a MySQL branch version that uses the Maria storage engine and is a free, Open-source database server developed by Monty Program AB, founded by the original MySQL author Michael widenius (Monty). Basically, MARIADB's history is the same as the Maria storage engine history I mentioned above. MARIADB's design goal is to replace MySQL Server. Monty is the initiator of the Open Source Database Federation (Open DB Alliance), so MARIADB is also a member of the Open Source database consortium.

The mariadb Maria storage engine, which replaces the MySQL MyISAM storage engine, uses the Percona xtradb engine to replace INNODB,MARIADB's storage engine, including the PrimeBase XT (PBXT) and Federatedx storage engine, MARIADB based on GPL 2.0 release.

Monty Widenius provides the branch MARIADB candidate version of MySQL. MARIADB 5.1 is fully compatible with MySQL 5.1, which was released as early as November 2008, adding a lot of new features and several new patches. Developers say the candidate version is very stable. Basically MYSQL,MARIADB solves many problems, such as the "Pool of Threads" feature, which provides troubleshooting for multiple data connections. The release version currently released by MARIADB is 5.1.44, and should basically be compatible with the version of MySQL 5.1.

Mariadb basically after the family, coupled with MySQL founder Monty's strength and appeal, is a very good substitute for MySQL, the future development of unlimited, it is worth our attempt to use.

Introduction to Storage engine in MARIADB:

Maria:http://askmonty.org/wiki/maria

Xtradb:http://www.percona.com/docs/wiki/percona-xtradb:start

pbxt:http://www.primebase.org/

Federatedx:https://launchpad.net/federatedx

MARIADB Download: http://askmonty.org/wiki/MariaDB:Download

MARIADB Website: http://askmonty.org

[Drizzle database]

Drizzle, is a database server derived from MySQL, a compact version of the MySQL branch, the purpose of the drizzle project is to build a "more refined, lighter, faster" version of MySQL, and its scalability and ease of use and MySQL equivalent, But in order to improve performance and scalability, it removed some features from the original core system. Drizzle is also a member of the Open Source Database Federation (Open DB Alliance).

MySQL's architect design director Brian Aker the drizzle at the O ' Reilly Open Source Convention (OSCON). Drizzle is a database project ("What if" project) that can provide support for specific categories of applications. Drizzle's design objectives:

    1. Web applications.
    2. Cloud computing components.
    3. A database with no business logic (also known as a stored procedure).
    4. Multi-core architecture.

Drizzle, a streamlined version of the MySQL branch, in the current MySQL code on the basic, the stored procedures, views, triggers, query caching, prepare statements, and so do not have the necessary functionality removed from the code, simplifying the data type and storage engine support, and bold refactoring. The ultimate goal is to simplify the MySQL code, rationalize the MySQL architecture, improve the MySQL code quality, improve the stability and performance of the system. Better suited to Web applications, cloud computing components, databases without business logic (also known as stored procedures), multicore architectures, and more.

The characteristics of drizzle are:

1. MySQL 6.0 based on the source tree

2. No additional library

3. Follow POSIX

4. Micro-Core Design

5. Pluggable architecture for views, stored procedures, UDF, storage engine, etc.

6. Sharding Technology across multiple nodes

7. Intelligent Agent

8. Multi-cpu/multi-core CPU

9. Optimized field types

10. Efficient Memory usage

11. No internal ACL, use Ldap/pam

12. No database data format

13. Organize the ordered make system

14. Default storage Engine is InnoDB

15. Remove Windows Compatibility

Drizzle the default storage engine is InnoDB, with fewer data types to support, and basically a design goal that is completely different from mariadb. MARIADB's design goal is to replace the MySQL database, while drizzle is basically a product that you can choose from MySQL, and basically the goal is to target future cloud computing and distributed Web Storage, which may not be too stable at present, and not suitable for shipping The camp environment is used, but it is quite worth looking forward to.

Drizzle use: http://database.51cto.com/art/200907/137239.htm

Drizzle Download: https://launchpad.net/drizzle

Drizzle website: http://drizzle.org/

Summary language

Basically, the current MySQL is still mainstream (MYISAM/INNODB), but the future development is unpredictable, and there are other than MySQL options, perhaps one day Oracle will completely eliminate MySQL, but we also have mariadb, Drizzle can choose, this is the power of open source.

Compared to several MySQL storage engines, Maria and XTRADB is worth everyone's current input gradually use the ranks, do some more testing, grayscale light, to obtain a reasonable result and then use is more appropriate. MySQL Database branch, MARIADB is also more worth trying to use, after all, the current drizzle is not too mature and stable, and not necessarily suitable for your business. I understand some of the domestic Internet companies are also using MARIADB, and the effect is good, we can also use their own situation.

Currently NoSQL movement in full swing, some business more suitable for the use of Key==>value or this is the bigtable type of data storage, perhaps MySQL is not the best, of course, choose the most suitable storage, perhaps the future of most database market will be nosql occupation, But I think the relational database is a very important storage way for the next few years.

When MySQL was acquired by Sun, and the whole open source world was in the process of being acquired by Oracle, it was a huge change, especially as the fate of MySQL had always been the concern of all those who used and loved the Open-source database, and in these commercial competitions, those open source fighters (such as Monty), In other ways, continue to develop MySQL this open source database. In the long run, there's always something going to go, like a Falcon storage engine, something that continues to evolve, such as mariadb or drizzle, but it has contributed to open source technology and added color to the database domain.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.