?

Log in

No account? Create an account
ss - oscar

My SQL Dump

MySQL musings by a self professed MySQL Geek

Previous Entry Share Next Entry
RocksDB doesn't support large transactions very well
ss - oscar
swanhart
So I tried to do my first set of benchmarks and testing on RocksDB today, but I ran into a problem and had to file a bug:
https://github.com/facebook/mysql-5.6/issues/365

MySQL @ Facebook RocksDB appears to store at least 2x the size of the volume of changes in a transaction. I don't know how much space for the row + overhead there is in each transcation, so I'm just going to say 2x the raw size of the data changed in the transaction, as approximation. I am not sure how this works for updates either, that is whether old/new row information is maintained. If old/row data is maintained, then a pure update workload you would need 4x the ram for the given transactional changes. My bulk load was 12GB of raw data, so it failed as I have only 12GB of RAM in my test system.

The workaround (as suggested in the bug) is to set two configuration variables:
set rocksdb_bulk_load=1; 
set rocksdb_commit_in_the_middle=1


Note that if you set these settings and there is a crash (not sure about client abort) then the table will be partially loaded. This is fine for an import of a large data set, but it doesn't address the need to load bulk amounts of data in a nightly job for example. In that case, you definitely don't want to end up with half a table loaded after a crash or query error.

For now I would stay away from RocksDB if you need to regularly load data that is more than 1/2 the size of available ram. If you have concurrent read queries, then obviously that number will probably be smaller.

It would be ideal if the database didn't crash, and instead the load rolled back when this situation was encountered. It isn't very user friendly to crash the database due to large transactions, and I'm sure a stored procedure could be created to crash the database by simply inserting rows in a loop, which is a serious potential DOS.