New Features of MySQL 5.5

 

MySQL has entered the Oracle product system and gained more R & D investment. The next-generation MySQL product mysql5.5 is coming soon. Compared with the previous version 5.1, it will be improved in many features, which are summarized as follows:

1. Change the default storage engine to InnoDB

As a mature and efficient transaction engine, InnoDB is widely used, but the default engine of MySQL and earlier versions is MyISAM. This time, mysql5.5 has finally kept pace with the times and changed the default database storage engine to InnoDB, and introduced InnoDB
Plugin 1.0.7. The benefits of this update to the database are obvious: InnoDB's data recovery time has been shortened from one or even several hours to several minutes (InnoDB plugin
1.0.7, InnoDB plugin 1.1, uses the red-black tree for recovery ). InnoDB plugin
Supports compressed data storage, saves storage, increases memory hit rate, and supports adaptive flush checkpoint, which can avoid unexpected performance bottlenecks in databases in some scenarios.

Multi rollback segments:
Originally, InnoDB only had one segment and only 1023 of the concurrency was supported. It has now expanded to 128 segments, thus solving the high concurrency limit.

2. Multi-Core Performance Improvement

Replace lock_open mutex with metadata locking (MDL) Framework
(Lock) to solve the performance bottleneck of MySQL and earlier versions on multi-core processors. The official comments will continue to enhance the support for MySQL multi-processor, until MySQL performance is "not limited by the number of processors"

3. Enhanced Replication

The MySQL replication feature is widely used by Internet companies. As the most practical and simple extension method of MySQL, the asynchronous replication method in the past has become somewhat inadequate, for some users, "asynchronous replication" means data risks in extreme cases. mysql5.5 will support semi-sync for the first time (semi-sync
Replication) will generate more and more reliable MySQL high-availability solutions. In addition, slave fsync tunning; relay log resume uption
Recovery and replication heartbeat will also be implemented.

4. Enhanced Table Partitioning

MySQL 5.5 partition is definitely good news for users, more easy to use enhancements, and truncate
The partition command can save a lot of time for DBA, sometimes for end users:

1) Non-integer column partition: Anyone who has used MySQL partition should have encountered many problems, especially in the face of non-integer column partition, MySQL
5.1 only integer column partitions can be processed. If you want to partition a date or string column, you have to use a function to convert it. Very troublesome, while MySQL
5.5 adds two partition methods, rang and list partitioning, and adds a columns keyword to the new function. In MySQL
Another headache for using partitions in MySQL 5.1 is the date type (that is, the date columns). You cannot use them directly. You must use year or to_days to convert these columns, but in MySQL
In 5.5, the situation has changed a lot. Now we can partition directly in the date column, and the method is also very simple;

2) Multi-column partitioning: The columns keyword allows the string and date columns to be defined as partition columns, and allows multiple columns to define a partition;

3)
Availability enhancement: truncate partition. One of the most attractive features of partitioning is the ability to instantly remove a large number of records. DBAs like to store historical records in partition tables partitioned by date, so that they can regularly Delete outdated historical data. However, when you need to remove part of the data in the partition, it is not that simple. There is no problem in deleting the partition, but it is a headache if you are clearing the partition, to remove all data in the partition, keep

Partition itself, you can: Use the delete statement, but we know that the performance of the delete statement is poor. Use drop
The partition statement is followed by an eorganize partitions statement to re-create a partition, but the cost of doing so is much higher than that of the previous method. MySQL
5.5 introduced truncate partition, which is similar to the drop partition statement, but it retains the partition itself, which means that the partition can be reused. Truncate
Partition should be a required tool in the DBA toolbox;

4)
More fine-tuning functions: to_seconds: The partition enhancement package has a new function for processing the date and datetime columns. Using the to_seconds function, you can convert the date/time column to the number of seconds since 0 years, if you want to partition at intervals less than one day, this function can help you.

5. Insert buffering if no data is found in the buffer pool, directly buffer the data to avoid additional IO; Delete &
Purge buffering is the same as insert. If the buffer pool is not hit, buffer up first to avoid extra Io.

6. Support for native AIO on Linux

The above features will be included in MySQL 5.5 community edition. in MySQL Enterprise Edition, apart from the above updates, Oracle also enhances more practical enterprise-level functions, including:

1. Online physical hot standby

MySQL Enterprise Edition will include InnoDB
Hotbackup (this may be a new highlight of MySQL and inndb after many years) to solve the problem that MySQL could not perform reliable online real-time physical backup in the past. InnoDB
Hot Backup does not require you to shut down your server or apply any locks or affect other common data operations. This is a good message for MySQL DBAs.

2. MySQL Enterprise monitor 2.2 & Oracle Enterprise Monitor

Yes, you are not mistaken. MySQL can be replaced by Oracle Enterprise
Monitor monitoring, which is not complicated to implement, but is unlikely to change in the past. MySQL Enterprise Edition Monitor (MySQL Enterprise
Monitor) has been greatly enhanced, the version is updated to 2.2, the occupation of MySQL Server resources is reduced to the point that can be ignored, integrated monitoring, alarm, SQL statement analysis and provide optimization suggestions, compared with some open-source monitoring solutions of MySQL, MySQL becomes more reliable for enterprise customers.

3. MySQL workbench

In the past, the graphic interface tool of MySQL was very flattering. Of course, it also provided market space for many MySQL management tools. Now Oracle intends to make MySQL easier to use than SQL Server.
Workbench is an ER/database modeling tool designed for MySQL. It can be used to design and create database diagrams, create database documents, and perform complex MySQL
Therefore, the built-in workbench makes MySQL easier and more efficient to use.

4. Important reminder about the future: MySQL will be able to use Oracle's management tools. Oracle is smarter than the MySQL Community imagined, isn't it? Of course MySQL
5.5 we haven't seen this change yet, but the change is already on the schedule. The MySQL Community edition can also be managed by Oracle management tools, provided that you must be a user of the Oracle database.