Percona curbs MySQL memory hunger

Percona has updated its version of MySQL so that the software's storage engine consumes less working memory, potentially increasing its suitability for tasks requiring an in-memory database, the company announced last week.

The in-memory storage engine of Percona Server 5.5.15 now can hold all MySQL data types, including the previously unsupported BLOB and TEXT data types, which could also broaden its appeal to large-scale web services companies.

If a deployment "has very small memory tables, this version may save a few megabytes of memory. It's no big deal. But if you are using tens or hundreds of gigabytes of data in memory, that is where you will find that our changes have an advantage," said Percona founder and CEO Peter Zaitsev.

Founded in 2006, Percona bills itself as the largest and oldest independent MySQL services vendor outside of Oracle. Internet-facing companies such as GroupOn, RightNow, Answer.Com, 37Signals and the BBC have availed themselves of Percona's services. Percona Server is an open source version of MySQL with a number of enhancements for production use. Percona sells support for the server.

Quicker response times

For this release, the company modified the MySQL MEMORY storage engine, which was designed to store database tables in a server's working memory, eliminating the need to write each change to a disk, which can slow transaction speeds. For jobs such as hosting a website, MySQL Memory can significantly speed response times.

A number of limitations have hobbled the basic version of MySQL MEMORY, Zaitsev said. Most notably, MEMORY (formerly known as HEAP) can work only with a subset of data types allowable in MySQL, notably only those with fixed lengths.

With fixed-length data types, each entry to the database will be stored in an identically sized storage block. Because most entries won't use all the space in their storage blocks, this approach is inherently inefficient, due to all empty space it creates. "For many problems, you have to store strings of different lengths," Zaitsev said.

On disk drives, where space is relatively cheap, this waste is not a huge deal. On the more expensive RAM, however, such waste is costly.

In-memory databases

Percona Server 5.5.15, which is based on MySQL 5.5.15, comes with an enhanced version of MySQL MEMORY that can work with any MySQL data type, including variable-length data types such as BLOB and TEXT.

eBay principal developer Igor Chernyshev first developed this technology in 2008, in order to improve memory usage for that company's services. His version allowed entries encoded in the VARCHAR data type to be stored at their natural lengths, rather than at a larger fixed length. Percona then added support for the TEXT and BLOB data types, both of which can be used to store large strings of text.

How much memory this version will save will vary greatly from one implementation to another, Zaitsev said. It could cut as much memory requirements by as much half or even more, on the data types formerly used. It also opens the use of in-memory databases to organisations that only use variable-length data types before, and were not willing to convert the data to a fixed-length data type.

In-memory databases are growing increasingly popular as more organisations set up web-facing or real-time systems, which need to be as responsive as possible. Oracle's TimesTen, SAP's HANA database and VoltDB all serve this market as well.