Five MySQL Database Storage Solutions

MySQL database storage options are rapidly changing with advances in technology and the diverse options offered by software providers. According to Mark Peters, senior analyst for Enterprise Strategy Group, “2016 is the year when everyone — vendors, users and commentators alike — will be publicly talking about the degree of genuine and dramatic change that is nascent in the storage ecosystem.”

Data storage is a key aspect of any database architecture. Each data storage method has its pros and cons. Although there have been several different ways in which databases traditionally store data — within flat files, in spreadsheets, or relational databases — there are many different options and, with those, different concerns. Choices you make regarding data storage will impact subsequent choices in the software used to organize the data.

The most commonly used MySQL storage engines are MyISAM, InnoDB, memory, NDB, CSV, and XtraDB. Of these, XtraDB is commonly used with Percona and with Galera. MyISAM, InnoDB, Memory, NDB, and CSV are used with MySQL.

1. MyISAM is a staple storage engine for MySQL. It’s very fast for data retrieval. One major caveat: table locking. MyISAM will lock up a table when backing up, inserting, updating, or deleting data, making it unavailable to other users. In a very high-transaction environment MyISAM becomes a bottleneck.
2. InnoDB is the default storage engine for MySQL. It has the benefit of being both transactional and ACID-compliant. Thanks to multi-version concurrency control, when InnoDB is modifying a row and another function is coming in, it’s able to mark the old row and archive it. The primary caveat: InnoDB doesn’t work well for fast access.
3. CSV (Comma-Separated Values) is a handy storage engine. This classic data storage format is always available. CSV data files can be opened in Excel or by a text editor. You can write a little MySQL script that will be easily able to access the data using no additional libraries.
4. Memory storage is very, very fast because the data and indexes are stored in memory. The problem is, the data is not stored anywhere physically. If there is a power loss — whether from a restart or catastrophic failure — the data is not retained.
5. NDB is a storage engine used exclusively with MySQL Cluster. It stores data similarly to the way in which data nodes do. Here, the data nodes are managed by the NDB administrative node.

Comparing Database Storage Engines

The Datavail whitepaper, MySQL Architecture Options, contains a chart comparing and contrasting the capabilities of these different storage solutions. Here are some of the highlights.

Storage Limit: InnoDB has a maximum of 48 terabytes; NDB is limited to a maximum of 3 TB; and MyISAM is limited by the disk and file size.

Data Compression: InnoDB allows data compression while neither NDB nor MyISAM do.

ACID-Compliance: If having ACID-compliant data storage is a priority, your choices narrow to InnoDB or NDB.

What is the best database architecture available to optimize your use of MySQL? Good question. The answer depends on your business requirements are.

You need to evaluate your specific database architecture needs — the required redundancy, speed, failover, and other parameters — against issues such as costs and available infrastructure. You will need to answer questions such as, “Where is MySQL going to be hosted?” “Will I have my own servers?” “Will it be hosted in the cloud or via a hosting service?” “Will I need more hardware?”

The easiest architecture to implement will be some variant of the master-slave architecture. The safest architecture to use is probably Galera Cluster, but you will need to weigh its use against performance. Your choice depends on what you need and want from a database. MySQL Cluster could be a solution for you, but so could Galera.

Think about what your organization’s needs are. Think about the different approaches and which of these might be the best option for your database environment. There is no single perfect solution for every organization or database.

MySQL Database Resources

If you are still uncertain about the best MySQL architecture options for your organization, Datavail can help you evaluate your current database environment against your company’s business priorities and future goals.

The Datavail website provides several whitepapers exploring the architecture options for MySQL and primary benefits related to using MySQL, including how to optimize MySQL for high availability, how to configure it for scalability, and how to use MySQL performance analysis tools.

With more than 400 database administrators worldwide, Datavail is the largest pure-play database services provider in North America. With 24×7 managed database services, including database design, architecture and staffing, Datavail can support your organization as it works with MySQL. Contact Datavail to discuss a custom MySQL solution designed for your enterprise.