Exposing MyRocks Internals Via System Variables: Part 7, Use Case Considerations
(In the previous post, Part 6, we covered Replication.)
In this final blog post, we conclude our series of exploring MyRocks by taking a look at use case considerations. After all, having knowledge of how an engine works is really only applicable if you feel like you’re in a good position to use it.
Advantages of MyRocks
Let’s start by talking about some of the advantages of MyRocks.Compression
MyRocks will typically do a good job of reducing the physical footprint of your data. As I mentioned in my previous post in this series about compression, you have the ability to configure compression down to the individual compaction layers for each column family. You also get the advantage of the fact that data isn’t updated once it’s written to disk. Compaction, which was also previously covered, takes data changes and merges them together. The result of this is less free space within data pages, meaning that the size of the data file on disk is a better representation of the size of the data set. You can read more about this on this page of the MyRocks wiki. I noted in my previous post on compression that a common mistake that I’ve seen in the past is the adoption of technologies or engines where there is an ability to reduce your storage footprint, particularly in moments where you may be running out of disk space. Fight that urge and consider all the facts in regard to the engine before adopting.Write-Optimized
If your use case is write-heavy, then MyRocks may be a good solution for you given the fact that it writes data in a log structure based on data changes and then relies on compaction to clean up the data after the fact as it makes its way through compaction layers, thus creating deferred write amplification. This removes a lot of random reads and writes that would be required for storage engines that are reliant on b-tree. You can read more about write optimization with MyRocks on this page of code.fb.com. You can even take this a step further by saying that MyRocks is write-optimized at the cluster level considering it supports read free replication.Better performance when the active data set doesn’t fit in memory
There are benchmark tests that have been performed that show that MyRocks can outperform InnoDB in the case when the active data set (the data most commonly accessed) does not fit entirely in memory. For InnoDB, this would mean that there is data that is frequently accessed that cannot all fit in the buffer pool. For MyRocks, we know that different caches are used for writes and reads, but for all intents and purposes we can say that the case would be when there is data that is frequently accessed that doesn’t fit in the block cache. You can read more about this by checking out this blog post by Vadim Tkachenko on the Percona Blog.Backups
When I originally drafted this installment of the blog series, I actually had backups listed as a drawback. The reason is that while you were able to do hot backups of MyRocks datasets using myrocks_hotbackup , you would not be able to use that tool to backup any data that was stored using any other storage engine. This was a common problem with TokuDB enabled instances and thus forced us to fall back to snapshot-based backups when we came across these systems. However, on May 9th, 2019, Percona announced in their 8.0.6 release of xtrabackup that MyRocks would be supported in the product, allowing you to create backups for systems that used both InnoDB and MyRocks. This is especially important considering that in MySQL 8.0, the data dictionary is now stored in database tables within MySQL, replacing the existing .frm method, and those tables are stored using InnoDB .Drawbacks of MyRocks
Now let’s cover some of the drawbacks and limitations of MyRocksRange Lookups
MyRocks gives you some fantastic advantages when it comes to reads in your database that are filtered based on const operators like ‘IN’ and ‘=’ given that it can use bloom filters to increase the velocity of getting that data to disk by invalidating data files without needing to access them beyond reading the filter blocks if they aren’t already in memory. This changes when you need to look for data in ranges. There is no way to take advantage of standard bloom filters for this, and you may need to spend a lot of time decompressing data and loading it into memory to find what you need. This issue is exacerbated in the case that you need to do full table scans given that you will need to access multiple versions of the table data across the various compaction layers and then take on the overhead of the system having to determine the latest version of the data for each record. That’s not to say that range lookups in your use case would completely invalidate MyRocks as a potential solution for you. There are optimizations of RocksDB that allow for more efficient range lookups, and even prefix bloom filters that can be beneficial. You can read more about this by reading this blog post on why RocksDB was selected as the underlying storage engine for CockroachDB.No Online DDL
Currently MyRocks does not support Online DDL, whereas InnoDB has supported this to some degree for quite some time . You can read more about this limitation by checking out this bug in the MyRocks github project.No Foreign Keys
Foreign keys are currently not supported in MyRocks. To be completely candid though, this isn’t a deal breaker for me. When I was a database developer and schema designer, I was a big-time supporter of foreign keys because I’m a control freak and don’t like developers messing with the reliability of my data relationships. Now that I’ve been on the DBA side for a while, I can see the overhead that foreign keys create. They offer that lovely peace of mind when it comes to data relationships, but at a noteworthy cost. Even more so when you apply them to something like Galera. Given the overhead cost of foreign keys, I’m seeing more and more cases where they are being removed unless data relationships are incredibly critical. You may find that you can live without them completely and, as such, you may not be dissuaded from using MyRocks.No Transportable Tablespaces
Having the ability to physically move your data at the table level without having to do a logical export / import doesn’t really seem like a big deal until you need it. This is going to be a difficult challenge to overcome considering how you have multiple tables in a single column family, plus the complexities that come with having multiple compaction layers. I have a feeling that a lack of transportable tablespaces with MyRocks is likely something we’re going to have to deal with for a long time.Select For Update Not Supported With Repeatable Read Isolation Level
"Select for update" is not available with repeatable read, but is available with read-committed. This is important as you need to understand how this will impact your explicit exclusive lock requests.So when do I consider it?
I would suggest considering MyRocks if you’re working with….- A large OLTP dataset where your active data set doesn’t fit into memory
- Write-intensive workloads
- High concurrency reads that don’t filter on range
