Thursday, August 10, 2017

Why we still need MyISAM (for read-only tables)

TL;DR: we still need MyISAM and myisampack because it uses less space on disk (half of compressed InnoDB) !

In the previous post, I shared my experience with InnoDB table compression on a read-only dataset. In it, I claimed, without giving much detail, that using MyISAM and myisampack would result is a more compact storage on disk. In this post, I will share more details about this claim.MyISAM is the original storage engine of MySQL. It is a modified version of ISAM for MySQL (My-ISAM). It was replaced by InnoDB as the default storage engine in MySQL 5.5 (first GA release in December 2010). MyISAM is sometimes qualified as "not under active development", unsupported, unmaintained, deprecated, end-of life, and/or other unflattering adjectives. There are even people writing about the end of MyISAM. However in some situations and even if it does not support good concurrency, neither transactions nor foreign keys, MyISAM (and myisampack) can still be very useful. The end of MyISAM would leave a gap in MySQL feature-set. Let's see why.

In the previous post, I described a replicated system storing thousands of read-only tables. Those tables are written as non-compressed InnoDB table on the master and they reach slaves via standard replication. After being written, those tables are converted locally (not writing to the binary logs) to compressed InnoDB tables. This post-compression significantly reduces the storage needed for this system.

MyISAM and myisampack could replace InnoDB table compression on this system. The tables would still be written on the master using the InnoDB storage engine (for replication crash safety), but MyISAM and myisampack would be used instead of InnoDB compression for reducing disk footprint. As compression is done on a copy of the table (atomically swapping/renaming tables when completed), the lack of transaction support in MyISAM is not a problem: if a crash happens during compression, the operation can be restarted from the beginning without losing any data. Also, as the tables are read-only, compressed MyISAM tables can be used (after packing a table using myisampack, the table becomes read-only).

We can use myisampack because our dataset is read-only !

In the previous post, I described a log table where I am keeping information about the size of each compression round. For testing MyISAM and myisampack, I used an extra slave where I added four fields to the compression log table: myisam_size, myisam_size2, mypack_size and mypack_size2. The structure of table becomes the following:

We need two fields for MyISAM and myisampack sizes because, contrarily to InnoDB that stores data in a single .ibd file, MyISAM and myisampack store data in two files: .MYD for the data and .MYI for indexes. I could have stored the sum of both values in a single field but this would have lost some information that could be useful in the future (feel free to ask me creative questions/queries on that data in the comments).

Before going further, let's look again at the InnoDB statistics for the MySQL instance analysed in the previous post:

So myisampack average compression ratio on the initial size (InnoDB uncompressed) is 10.42, which is 2.3 times better than InnoDB compressed. I do not have formal data on the next statement, but I also think that myisampack takes less time to compress a table, especially that with InnoDB and to obtain the best compression ratio, we have to try many KEY_BLOCK_SIZE, which takes time.

One last thing before closing: let's check if there are cases where MyISAM or myisampack is using more disk space than InnoDB and InnoDB compression:

I think this clearly shows that we still need MyISAM and myisampack for efficient storage of read-only tables.

A disclaimer: I am not yet using MyISAM and myisampack for the storage of this particular dataset in production. However, I might switch from InnoDB compression to MyISAM and myisampack because, as shards are filling-up, it might be better to change the storage format than to allocate more shards/hardware for this system.

-Regarding the lack of checksums, you are right that InnoDB's page checksum allows to avoid corruption in the storage by failing a read if the disks are lying to us. This could easily be worked around by having a more reliable storage stack that provides checksums, ZFS as an example.

-Regarding the lack of a partitioning solution, you are right and I do not have a good solution to that. Using non-partitioned MyISAM (I do not have partitions on the tables in this system) or using a fork of MySQL that is still supporting partition for MyISAM could be a way out, but it is not completely satisfactory in the general case.

-Regarding non-efficient backups, I guess you are talking about hot backups, because doing a cold backup by stopping the database (or a slave) and doing a file-system snapshot is easy. Taking a hot backup of a read-only MyISAM table is also easy: just copying the data files is enough. So for my system, backing-up all InnoDB tables using a standard hot backup solution, and then making a copy of all MyISAM table would give a good backup that can be restored with minimal post-processing. The general case for hot backup is still complicated though.

-Regarding lack of clustered indexes, I have to confess that I am not very knowledgable about MyISAM internals and that I do not fully understand what you mean. Is a PK lookup in MyISAM doing a full scan ?

- data corruption can occur at many levels. Verifying them at the storage or filesystem level indeed detects corruption in some cases, but not the others. For instance, network-level corruption while copying and restoring a backup over the network would not be detected at the storage or filesystem levels.

- yes, I was referring to hot backups. XtraBackup, for example, copies all MyISAM tables under FTWRL in the most general case, which may be painful.

- PK lookups on a non-clustered index require extra steps to fetch data, index scans may result in random rather than sequential I/O, etc. Of course, the actual impact depends on the workload. It's just something to keep in mind.

I do not know how well TokuDB and MyRocks will compress this data, we still have to test. But TokuDB and MyRocks do not come out of the box on Oracle MySQL. I think TokuDB is included by default in Percona Server and in MariaDB, but I do not know how well it is supported. MyRocks is (or will soon be) in MariaDB and Percona Server, but I do know how it will work with 200K+ tables (column-family-wise).

Very good analysis, JF!Compressed MyISAM is going to be the only cheap alternative for read-only tables coming MySQL 8.0 (until it is deprecated and then dismissed, that is.)

The other viable case for slave-side storage was archive + partitioning, which compresses data up to 90% and allows statistical queries (i.e. aggregate queries based on the partitioning fields) to be as fast as InnoDB, if not faster. This solution is now dead, as partitioning is only supported for InnoDB. Now archive tables are only good for queries that need to scan the whole table for statistics, or access records via PK (only auto_incremented integer keys allowed, though).

* As with most things, this entire discussion is about a niche market. Although the typical database has a few tiny readonly tables, very few production systems have lots of big readonly tables. (And hence the admonition to turn off the Query Cache.)

* 200K tables is awful on the OS. (This will be magically fixed in 8.0.)

* Partitioning is rarely useful. In most cases, some tweaking to the indexes can turn a partitioned table into a plain table with equal or slightly better performance. And less disk space.

* InnoDB already has tweaks to allow for non-transactional temp tables; seems like this could be extended to readonly tables?

I also once had a system storing thousands of write-once read-only tables. Each table contained 24hrs of log data and most of the time was unlikely to be read more than 3 times before it was purged, when it was read it would typically be to aggregate all data in the table, so a SELECT operation needing to perform a complete table scan was not a problem. I mention it because the compression ratio obtained with ARCHIVE table format was better than a myisampacked table.