add rocksdb as transactional engine

[wangzihuacool]

gh-ost now treats innodb and tokudb as transactional engine.

We now use rocksdb as MySQL's storage engine, for its high compression ratio and write performance. Rocksdb engine also supports transactions and implements transaction concurrency control based on row locks.

I hope gh-ost will treat rocksdb as a transactional engine as well, by adding a case option to IsTransactionalTable() .

Thank you!

[wangzihuacool]

refer: https://github.com/facebook/mysql-5.6/wiki/Row-Locking

👋 @wangzihuacool thanks for the PR! Has this been tested? My main area of concern is whether gh-ost's atomic cut-over still works as expected with RocksDB.

If this all works fine in testing, I'm happy to accept the PR to allow gh-ost to run against RocksDB - but we won't provide any official support for RocksDB as we don't use it here at GitHub currently.

[timvaillancourt]

Has this been tested? My main area of concern is whether gh-ost's atomic cut-over still works as expected with RocksDB.

I'm also curious about testing and the potential differences due to no Gap Lock in RocksDB

Are the functional-tests in localtests/ passing on RocksDB and would you be able to assist with adding a RocksDB-enabled MySQL server to the CI jobs that run these tests @wangzihuacool? This would help gain confidence of the compatibility

We are currently testing on a Oracle Community Edition 5.7 and 8.0 server, InnoDB only. I believe RocksDB can be tested using a Percona Server for MySQL server

[wangzihuacool]

We are testing RocksDB with gh-ost in our test environment now , and I am glad to add a RocksDB-enabled MySQL server to the ci jobs.

[wangzihuacool]

Has this been tested? My main area of concern is whether gh-ost's atomic cut-over still works as expected with RocksDB.

I'm also curious about testing and the potential differences due to no Gap Lock in RocksDB

Are the functional-tests in localtests/ passing on RocksDB and would you be able to assist with adding a RocksDB-enabled MySQL server to the CI jobs that run these tests @wangzihuacool? This would help gain confidence of the compatibility

We are currently testing on a Oracle Community Edition 5.7 and 8.0 server, InnoDB only. I believe RocksDB can be tested using a Percona Server for MySQL server

I have tested RocksDB using a Percona Server for MySQL 8.0. Most of the functional-tests in localtests/ for release v1.1.5 are passed on RocksDB , except a few features that RocksDB not supportted. To be specific, RocksDB does not support Foreign Key, Generated Columns,GEOMETRY and Gap Locks, refre to MyRocks-limitations.

But tests for master branch are failed, due to the transaction isolation changed to REPEATABLE-READ in #1156. gh-ost can only run in READ_COMMITTED isolation on RocksDB, due to no support for gap locking within RocksDB. @timvaillancourt Can you please change the default isolation to READ_COMMITTED, or make the transaction isolation level user-configurable?

Since we have modified thousands of tables using gh-ost, under READ_COMMITTED isolation level. It suppose to be safe to use gh-ost under READ_COMMITTED isolation level.

@wangzihuacool I think this is going to be a larger piece of work. Would you mind creating a PR with all of the changes needed in a single PR?

We can then review this PR to decide whether to include it in gh-ost - however if the changes are significant then it may be best maintained as a separate fork 😅

The approach that I would prefer for adding RocksDB support would be:

• add a new storage-engine command line flag (defaults to innodb)
  • when storage-engine is set to rocksdb, this enables any changes necessary to support RocksDB (e.g. sets isolation level to READ_COMMITTED)
  • when storage-engine is set to innodb, gh-ost runs in the same way that it does currently
• add RocksDB migration testing
  • update existing tests to exclude any that don't work with RocksDB

[wangzihuacool]

I'm glad to do that, I'll create a PR with all the changes in a few days.

[wangzihuacool]

I have created a PR #14 in gh-ost-ci-env , adding PerconaServer-8.0.21 tarball so that gh-ost can run tests for RocksDB.

And a PR #1190 with all the changes needed to support RocksDB. Hope you take the time to review it @dm-2 @timvaillancourt

Thanks @wangzihuacool1 I've merged github/gh-ost-ci-env#14 and left a review on #1190 👍

PR merged 🚀

• add rocksdb as transactional engine #1190

Thanks again for all of your work on this @wangzihuacool! ❤️

[nccx]

fwiw, rocksdb now supports range lock as well (https://github.com/facebook/rocksdb/tree/main/utilities/transactions/lock/range).