mySQL 5.5

MySQL 5.5 is available but MySQL 5.1 is the default in Ubuntu Linux and a few other systems. What are the advantages of MySQL 5.5 over MySQL 5.1?

MySQL has the advantage of containing several storage engines providing you with what is effectively several different types of databases in one software package. You install MySQL then choose MyISAM or InnoDB or one of the other storage engines. InnoDB became the default in MySQL 5.1.

InnoDB

InnoDB is now the default database engine and is benefiting from the extra focus. MySQL 5.5 includes InnoDB enhancements to make better use of storage technology coupled with multiple processor cores. The main change splits a workload into two separate streams to let the workloads run on separate cores. Input/Output requests should start faster, producing more requests per second. You then need faster disks to serve those extra requests.

Multiple cores

There are several improvements to make better use of multiple core processors. You will not see a difference when you step up from 2 cores to 4 cores. The difference will be noticeable when you jump up to 12 cores, as provided in an AMD Operon chip.

The improvement is created by splitting workloads into multiple streams to let them run in parallel. Some of the workloads are controlled by parameters you can set to fit your hardware. There is more statistical information recorded to help you tune the workloads.

Benchmarks show there is little difference for 4 cores or less because there were already enough streams running in parallel. There is little benefit above 30 cores because there are not enough streams to service more cores.

Semisynchronous replication

This is a bit like backing up your data with RAID 1. You get at least two copies. The process works when you replicate from a master database to some slaves.

Think of a master database replicating updates to several slave databases to serve many Web servers. You commit a transaction on the master and the change is saved to the master before the transaction is completed. You have one safe copy. Then your master server dies and you have no safe copies.

Try that again with semisynchronous replication. You commit the transaction. MySQL updates the master but does not tell you the transaction is finished. Instead it waits for one slave to take a copy of the update. When the slave has secured a copy of the update and replied to the master, the master tells your program the transaction is complete. The master server dies. You simply switch the slave to become the master and carry on with 100 percent of your data.

This feature is definitely of value to people running transactions in a master/slave replication.

Windows

MySQL installs easily on Windows if you have administrative access. There are 32 bit and 64 bit versions for automatic installation.

Linux

Oracle own MySQL and Oracle sell a rebranded Red Hat Linux. MySQL is available for both. MySQL is also available in source code andgeneric forms for Linux and some Unixes.

MySQL is not available in Deb format for automatic installation in Debian or derivatives of Debian including Ubuntu and Linux Mint. Some applications are also available in a PPA library for automatic download bypassing the Debian and Ubuntu releases. MySQL is not available in PPA form.

There are promises to set up a PPA library and a Deb download and to fast track MySQL 5.5 into Debian.