Reducing writes to disk from MySQL

My Problem

I have a program that runs every 10 seconds. It reads information from a website which includes data on users and products and what each are doing. It takes this information and updates it to an internal database. This takes from 100 to low 1000’s of queries and updates. It’s updating light data, pretty much just integers. The problem with a default configured mysql server is that each of these 10 second instances causes at least 5MB of data to be written to the disk, which is the primary speed bottleneck and significantly reduces the lifetime of the SSD drive its on over time. I plan on expanding this program to include even more website parsing and update more databases, but in order to scale up I really need to bring down the disk usage.

The solution

I solved the high disk utilization problem by replacing Mysql with MariaDB and Innodb with Rocksdb as the storage engine.

Install Rocksdb on Mariadb

I’ll leave how to install Mariadb up to you, there are plenty of resources on how to easily install it on your linux version. The tricky part is Rocksdb since it doesn’t typically come with your Mariadb installation, you’ll have to download and compile it separately.

sudo apt-get -y install g++ cmake libbz2-dev libaio-dev bison zlib1g-dev libsnappy-dev 
sudo apt-get -y install libgflags-dev libreadline6-dev libncurses5-dev libssl-dev liblz4-dev gdb git
git clone https://github.com/MariaDB/server.git mariadb-10.2
cd mariadb-10.2
git checkout mariadb-10.2.18
git submodule init
git submodule update
cmake .
make -j4

The one thing you might have to change on the above is the version number in the “git checkout” line. the Mariadb I checked out is 10.2.18. Use “mysql –version” to see what version you have and “git tag” to try to match that version.

Once you finish compiling Mariadb, which can take some time, you’ll want to copy /mariadb-10.2/storage/rocksdb/ha_rocksdb.so into /usr/lib/mysql/plugin/

To Make sure the module works try installing it in a mysql console.

mysql> install plugin rocksdb soname 'ha_rocksdb.so';

If everything is okay you should have RocksDB loaded into your MariaDB.

lastly you should configure your my.cnf to make use of RocksDB and optimize for it.

/etc/mysql/my.cnf:

[mysqld]
default-storage-engine = rocksdb
rocksdb_max_open_files=-1
rocksdb_max_background_jobs=8
rocksdb_max_total_wal_size=4G
rocksdb_block_size=16384
rocksdb_table_cache_numshardbits=6
rocksdb_bytes_per_sync=16777216
rocksdb_wal_bytes_per_sync=4194304
rocksdb_compaction_sequential_deletes_count_sd=1
rocksdb_compaction_sequential_deletes=199999
rocksdb_compaction_sequential_deletes_window=200000
rocksdb_default_cf_options="write_buffer_size=256m;target_file_size_base=32m;max_bytes_for_level_base=512m;max_write_buffer_number=4;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=20;level0_stop_writes_trigger=30;max_write_buffer_number=4;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=0};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;memtable_prefix_bloom_size_ratio=0.05;prefix_extractor=capped:12;compaction_pri=kMinOverlappingRatio;compression=kLZ4Compression;bottommost_compression=kLZ4Compression;compression_opts=-14:4:0"
rocksdb_max_subcompactions=4
rocksdb_compaction_readahead_size=16m
rocksdb_use_direct_reads=ON
rocksdb_use_direct_io_for_flush_and_compaction=ON

I don’t know what most of the above does since I pulled it off someone who was performing benchmarks with RocksDB, but it seems to work just fine for me.

Restart the service for the changes to take affect:

service mysql restart 

The very last step is you’ll likely need to change your existing database tables to use RocksDB to see the performance improvement.

mysql> ALTER TABLE yourtablename ENGINE = rocksdb;

With this change mysql went from writing 5MB+ to the disk to just a couple KB here and there. I’m not focused with overall speed of performance but from my programs timing information it looks like it’s taking about the same amount of time as Innodb did to perform the same updates to the database.