15 ms·

MyRocks – A RocksDB storage engine with MySQL

101 points by gfortaine 9y ago

1000units 9y ago
Very unprofessional name.
- crispinb 9y 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.
  - jrs95 9y ago
    This really should have ended with a Jesse Pinkman-esque "Bitch".
    - crispinb 9y ago
      Dare I admit I had no idea who that was? I only wasn’t laughed at because my mates already know TV references are wasted on me.
  - 1000units 9y 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.
    - crispinb 9y ago
      Whether it is ‘objectively’ so or not has little to do with professionalism, which kind of makes my point (which is that an important concept is falling into disrepair because the word for it is being misused). Anyway it seems I offended you, for which I apologise. My ire was roused by the usage, not the writer. I have no doubt I do worse often enough.
    - matt4077 9y ago
      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 9y ago
        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.
- jaysoncena 9y ago
  Can you give more context?
  - 1000units 9y 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.
    - jrs95 9y ago
      That's funny I was just doing that in response to your comments ;)
      - 1000units 9y ago
        I've got your database right here, pal!
    - bdcravens 9y ago
      Presumably that's a colloquialism not widely understood? That meaning really didn't occur to me until I read your comment.
    - kentor 9y ago
      Maybe if it weren't plural. You're way over thinking things.
      - 1000units 9y ago
        There's two of them.
- IMTDb 9y 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 :/
paulryanrogers 9y ago
Anyone know if they are MVCC based? ACID? Mobile site is Spartan on details. EDIT: They do mention it's not transactional.
- cakoose 9y ago
  Where do they say it's not transactional?
- antoncohen 9y 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-limitations 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-Isolation https://github.com/facebook/mysql-5.6/wiki/Transaction-Isola...).
  - paulryanrogers 9y 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-innodb-to-myrocks-in-production http://myrocks.io/docs/getting-started/#migrating-from-innod...
    - electrum 9y ago
      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 9y ago
        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.
nwrk 9y ago
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-in-percona-server-for-mysql/ https://www.percona.com/blog/2016/10/24/announcing-myrocks-i...
- bstill 9y ago
  And MariaDB: https://mariadb.com/kb/en/library/about-myrocks-for-mariadb/ https://mariadb.com/kb/en/library/about-myrocks-for-mariadb/
vadimberman 9y 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 9y ago
  with what columns/serialization ?
  - vadimberman 9y ago
    No idea how it's implemented, but why not come up with dummy names like "key" and "value"?
- kevincox 9y 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 9y ago
Since there seems to be a lot of confusion around transactions in MyRocks: https://github.com/facebook/mysql-5.6/wiki/Transaction-Isolation 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-isolation-levels.html 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.
markpapadakis 9y 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.
- Twirrim 9y ago
  At any stage, did you speak to a DBA? Those are some really weird choices you made along the way.
  - innagadadavida 9y 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.
falcolas 9y 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 9y ago
  you might want to check out the tests conducted by Mark in his blog - https://smalldatum.blogspot.com/2017/12/io-bound-linkbench-and-fast-server.html https://smalldatum.blogspot.com/2017/12/io-bound-linkbench-a.... There's a lot more there.

101 points by gfortaine 9y ago

1000units 9y ago

Very unprofessional name.

crispinb 9y 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.
- jrs95 9y ago
  This really should have ended with a Jesse Pinkman-esque "Bitch".
  - crispinb 9y ago
    Dare I admit I had no idea who that was? I only wasn’t laughed at because my mates already know TV references are wasted on me.
- 1000units 9y 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.
  - crispinb 9y ago
    Whether it is ‘objectively’ so or not has little to do with professionalism, which kind of makes my point (which is that an important concept is falling into disrepair because the word for it is being misused). Anyway it seems I offended you, for which I apologise. My ire was roused by the usage, not the writer. I have no doubt I do worse often enough.
  - matt4077 9y ago
    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 9y ago
      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.
jaysoncena 9y ago
Can you give more context?
- 1000units 9y 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.
  - jrs95 9y ago
    That's funny I was just doing that in response to your comments ;)
    - 1000units 9y ago
      I've got your database right here, pal!
  - bdcravens 9y ago
    Presumably that's a colloquialism not widely understood? That meaning really didn't occur to me until I read your comment.
  - kentor 9y ago
    Maybe if it weren't plural. You're way over thinking things.
    - 1000units 9y ago
      There's two of them.
IMTDb 9y 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 :/

paulryanrogers 9y ago

Anyone know if they are MVCC based? ACID? Mobile site is Spartan on details. EDIT: They do mention it's not transactional.

cakoose 9y ago
Where do they say it's not transactional?
antoncohen 9y 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-limitations 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-Isolation https://github.com/facebook/mysql-5.6/wiki/Transaction-Isola...).
- paulryanrogers 9y 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-innodb-to-myrocks-in-production http://myrocks.io/docs/getting-started/#migrating-from-innod...
  - electrum 9y ago
    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 9y ago
      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.

nwrk 9y ago

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-in-percona-server-for-mysql/ https://www.percona.com/blog/2016/10/24/announcing-myrocks-i...

bstill 9y ago
And MariaDB: https://mariadb.com/kb/en/library/about-myrocks-for-mariadb/ https://mariadb.com/kb/en/library/about-myrocks-for-mariadb/

vadimberman 9y 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 9y ago
with what columns/serialization ?
- vadimberman 9y ago
  No idea how it's implemented, but why not come up with dummy names like "key" and "value"?
kevincox 9y 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 9y ago

Since there seems to be a lot of confusion around transactions in MyRocks: https://github.com/facebook/mysql-5.6/wiki/Transaction-Isolation 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-isolation-levels.html 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.

markpapadakis 9y 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.

Twirrim 9y ago
At any stage, did you speak to a DBA? Those are some really weird choices you made along the way.
- innagadadavida 9y 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.

falcolas 9y 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 9y ago
you might want to check out the tests conducted by Mark in his blog - https://smalldatum.blogspot.com/2017/12/io-bound-linkbench-and-fast-server.html https://smalldatum.blogspot.com/2017/12/io-bound-linkbench-a.... There's a lot more there.