MyRocks – A RocksDB storage engine with MySQL

http://myrocks.io

by gfortaine • 9 years ago

101 45 9 years ago

myrocks.io

1000units5• 9 years ago

Very unprofessional name.

jaysoncena• 9 years ago

Can you give more context?

1000units5• 9 years ago

It evokes the gesture of one grabbing himself and shouting something impolite, as depicted in any of a number of De Niro films, for example.

crispinb3• 9 years ago

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.

jrs952• 9 years ago

This really should have ended with a Jesse Pinkman-esque "Bitch".

1000units5• 9 years ago

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.

IMTDb• 9 years ago

Maybe I don't get it, but...why ? According to comment, some people might be offended by the name, but I really can't see how :/

paulryanrogers2• 9 years ago

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

EDIT: They do mention it's not transactional.

cakoose• 9 years ago

Where do they say it's not transactional?

antoncohen• 9 years ago

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...). They have a "Transaction Isolation" doc that describes some options and differences (https://github.com/facebook/mysql-5.6/wiki/Transaction-Isola...).

paulryanrogers2• 9 years ago

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...

nwrk• 9 years ago

More juicy details is here

https://github.com/facebook/mysql-5.6/wiki

Also Percona backs it: https://www.percona.com/blog/2016/10/24/announcing-myrocks-i...

bstill• 9 years ago

And MariaDB: https://mariadb.com/kb/en/library/about-myrocks-for-mariadb/

vadimberman2• 9 years ago

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• 9 years ago

with what columns/serialization ?

vadimberman2• 9 years ago

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

kevincox• 9 years ago

This sounds like a different problem then the one being solved here. To do this it would probably be better to go for the "foreign table" interface then you can just point it at the db.

perlgeek• 9 years ago

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

https://github.com/facebook/mysql-5.6/wiki/Transaction-Isola...

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...

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.

markpapadakis6• 9 years ago

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.

Twirrim3• 9 years ago

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

innagadadavida• 9 years ago

You new here? Reinventing the wheel and blogging about it has glory. Where is the glory in writing some SQL that just works? Moreover it makes the code base very readable and maintainable, making your job redundant.

hendzen• 9 years ago

"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...

continuations• 9 years ago

What did you use MyRocks for and what kind of data store you ended up writing on your own?

Over 2 orders of magnitude reduction in disk footprint and going from 20s to 20ms in query performance - those are incredible improvements. Would love to learn more about how you pulled that off.

markpapadakis6• 9 years ago

It's not that incredible; for most use-cases there is often an optimal(or "better") way to implement a solution that's specifically designed and implemented to support it, as opposed to relying on systems and designs that are broadly applicable/useful (e.g RDBMS).

We just figured out exactly what we wanted, stored the data in chunks, compressed(we used 2 var-int encoding schemes, and snappy compression), indices(skip-lists) for each file and each chunk and for queries, we parellize access to as files required across multiple OS threads (scatter-gather). In fact, I am sure we could have gotten better performance if we wanted to spend more time on that problem. It wasn't novel or particularly interesting or hard anyway. Just something that needed to be done to help us solve a problem.

falcolas• 9 years ago

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• 9 years ago

you might want to check out the tests conducted by Mark in his blog - https://smalldatum.blogspot.com/2017/12/io-bound-linkbench-a.... There's a lot more there.

MyRocks – A RocksDB storage engine with MySQL

Related Stories