
MyRocks – A RocksDB storage engine with MySQL - guifortaine
http://myrocks.io
======
markpapadakis
FWIW, we used MyRocks (went from InnoDB to MyISAM, to MyRocks) for some mySQL
tables holding a few dozen million rows a few months ago.

InnoDB wasn't working out for us (INSERTs and SELECTs were too slow). MyISAM
fared a lot better; INSERTs were a lot faster(obviously?), and SELECTs some
50% faster. But it too wasn't as great as we hoped it'd be. It got to where it
was too slow for production use (some operations would take over 4s, which was
a deal breaker for us).

We then switched to MyRocks. It was great initially -- much smaller on-disk
footprint, INSERTs were fast, SELECTs were fast enough. But two weeks later it
also got to where it was too slow. Slower than InnoDB and MyISAM even; also,
our mySQL server would often starve for memory, and restarting it was the only
practical way to “fix” it.

We would DELETE from those tables every few hours because we were only
interested in the last 2 weeks worth of rows, so the dataset size was
constrained/bounded, so slow downs weren't a result of tables getting larger.

In the end, we just gave up on MySQL, wrote our own thing that stores and
accesses data on-disk directly. Disk footprint is over 2 orders of magnitude
smaller, and all operations take constant time(no more than 20ms at 99pc),
whereas in the past we ‘d get around 3s at best, and 10 or maybe 20s on
average(not even at 99pc).

This is not about us doing anything “better” or about rolling your own
alternative to generic datastores. It’s about MyRocks performance’s initially
being good, but deteriorating very quickly - and how it compares with InnoDB
and MyISAM, at least how it did for us. Also, using an RDBMS wasn't likely the
right choice for what we were doing anyway, but for various reasons that's
what we used.

It was in beta at the time, and I am sure there are tunables we could change
to maybe get a better performance, but we didn’t really bother with any of
that.

~~~
Twirrim
At any stage, did you speak to a DBA? Those are some _really_ weird choices
you made along the way.

~~~
hendzen
"We would DELETE from those tables every few hours because we were only
interested in the last 2 weeks worth of rows, so the dataset size was
constrained/bounded, so slow downs weren't a result of tables getting larger."

Yeah, this workload is hard on any LSM store, unless compaction is carefully
tuned so that the tombstones are cleaned up often enough. Most people would
recommend using partitioning...

~~~
markpapadakis
Yes, we 'd insert new rows every 30 minutes or so. Also, we did try
partitioning one of the 3 tables (which made sense) into multiple tables -- it
didn't improve the situation at all.

Again, there are probably tunables and practices specific to MyRocks that we
just didn't consider. We just didn't want to pursue this any further.

~~~
Shelnutt2
I believe hendzen was referring to using partition on a table in mysql.

Mariadb has an example walkthrough on this:
[https://mariadb.com/kb/en/library/partition-
maintenance/#par...](https://mariadb.com/kb/en/library/partition-
maintenance/#partition-maintenance-for-the-time-series-case) .

The only change that I'd recommend considering, is we use partition by list,
instead of range, and keep a weeks worth of daily future partitions. This
makes it so you don't have to reorganize the table. On the other hand it means
if you get future data it can fail and if your partition jobs have issues for
a week you can fail to load data all together.

Using partitions for time series data help also due to partition pruning,
[https://dev.mysql.com/doc/refman/5.7/en/partitioning-
pruning...](https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html)
.

~~~
markpapadakis
Thank you -- we didn't use table partitioning because we didn't have good
results in the past, though we should probably have tried it, and maybe it'd
have worked well. We just kind of gave up on mySQL use for that specific
problem after failing (again, maybe its entirely because of our inability to
use mySQL "properly") to get good results and moved on to something else.

------
nwrk
More juicy details is here

[https://github.com/facebook/mysql-5.6/wiki](https://github.com/facebook/mysql-5.6/wiki)

Also Percona backs it: [https://www.percona.com/blog/2016/10/24/announcing-
myrocks-i...](https://www.percona.com/blog/2016/10/24/announcing-myrocks-in-
percona-server-for-mysql/)

~~~
bstill
And MariaDB: [https://mariadb.com/kb/en/library/about-myrocks-for-
mariadb/](https://mariadb.com/kb/en/library/about-myrocks-for-mariadb/)

------
perlgeek
Since there seems to be a lot of confusion around transactions in MyRocks:

[https://github.com/facebook/mysql-5.6/wiki/Transaction-
Isola...](https://github.com/facebook/mysql-5.6/wiki/Transaction-Isolation)

> MyRocks supports two of the four transaction isolation levels: Read
> committed and Repeatable reads

Repeatable Reads in the default isolation level you get with InnoDB as well
(though you can also use "serializable", which MyRocks does not support):
[https://dev.mysql.com/doc/refman/5.7/en/innodb-
transaction-i...](https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-
isolation-levels.html)

MySQL does not support transactions that span multiple storage engines (to the
best of my knowledge), so you should try to avoid tables with different
storage engines within the same database. At least if you need transactions.

------
falcolas
Can anyone point at the performance differences with TokuDB? InnoDB is a great
starting point, but it has a lot of limitations that TokuDB does not have.

~~~
dbks
you might want to check out the tests conducted by Mark in his blog -
[https://smalldatum.blogspot.com/2017/12/io-bound-
linkbench-a...](https://smalldatum.blogspot.com/2017/12/io-bound-linkbench-
and-fast-server.html). There's a lot more there.

------
vadimberman
I couldn't find it in the docs (sorry if I missed something obvious), so a
question to those who know the subject better: will there be a way to plug in
an existing RocksDB database and run SQL queries, or they are going to be two
different incompatible flavours?

~~~
ddorian43
with what columns/serialization ?

~~~
vadimberman
No idea how it's implemented, but why not come up with dummy names like "key"
and "value"?

------
paulryanrogers
Anyone know if they are MVCC based? ACID? Mobile site is Spartan on details.

EDIT: They do mention it's not transactional.

~~~
antoncohen
Not transactional in what way?

Their "Limitations" doc doesn't mention that it lacks transactions
([https://github.com/facebook/mysql-5.6/wiki/MyRocks-
limitatio...](https://github.com/facebook/mysql-5.6/wiki/MyRocks-
limitations)). They have a "Transaction Isolation" doc that describes some
options and differences
([https://github.com/facebook/mysql-5.6/wiki/Transaction-
Isola...](https://github.com/facebook/mysql-5.6/wiki/Transaction-Isolation)).

~~~
paulryanrogers
Perhaps I misunderstood:

"Using mixed storage engines is not recommended in production because it is
not really transactional, ..."

[http://myrocks.io/docs/getting-started/#migrating-from-
innod...](http://myrocks.io/docs/getting-started/#migrating-from-innodb-to-
myrocks-in-production)

~~~
electrum
I believe that is saying that MySQL transactions across two storage engines
are not transactional, as that would require two-phase-commit (2PC) or
similar. It might also have implications for replication.

MyRocks itself definitely has transactions. As the wiki explains, it uses
snapshots (MVCC) for transaction isolation.

~~~
VintageCool
MySQL does have a system for two phase commit. I've seen it accidentally
triggered on a system that executed queries that joined two different engines
(InnoDB and TokuDB) and had the binlog turned off. It's called XA mode and it
requires an fsync per commit to a transaction log file.

------
1000units
Very unprofessional name.

~~~
crispinb
This misuse of ‘professionalism’ to refer to the faux Victorian mannerliness
fashionable amongst lesser suits is just one more troubling aspect of loss of
conceptual resources inculcated by corporate culture.

~~~
1000units
It's objectively a matter of bad optics for a product marketed towards general
professional audiences. I'm personally unoffended, but I recognize it as a
mistake because I know business. I don't think you know how to size a suit.

If you're interested in making this about my personality, click on my
username.

~~~
matt4077
I hate these mid-level pencil-pushers that "aren't personally offended" but
terribly fear others might be.

There's an airline named Virgin, a morning-after pill "Plan B", one of
SpaceX's vessels is called "Of Course I Still Love You", and the Secret
Service uses a weapon called YAR ("Yet another rifle"). Insisting everything
should be named "Widget X2000-F1" is the telltale sign of mediocrity.

~~~
degenerate
Every time I see "The Boring Company" mentioned somewhere, I crack a smile.
The business world needs more fun and liveliness, so I'm appreciative when
someone names a product something neat.

