We have a write intensive applications, which write many thousand of logs per hour to a MariaDB database in a RocksDB table. We want to manage the situations where the disk space is nearly filled up.
Currently when the free space reaches the predefined limits, we start deleting 1% of the oldest logs with a query like this:
DELETE FROM log_table LIMIT 1000000;
of course breaking the number of rows to some smaller count and delayed queries to not overload the disk.
The problem is that deleting the rows does not free the disk space and only creates tombstones, so we cannot check that if the free space is available and not running this query again.
So we use the manual compaction after all queries finished to really free the disk space, with a query like this one:
SET GLOBAL `rocksdb_compact_cf` = 'log_family';
This creates another problem which is currently our issue. This manual compaction process, needs about 2 times free space as current database size, and to do such a compaction we need to keep half of the disk capacity free, which is a great waste.
I greatly appreciate any help/advise on our problem.
Thank you very much.
Platform information:
OS: OpenSUSE 15.5
DBMS: MariaDB 10.6.14
MariaDB Configurations:
[mysqld]
plugin-load-add=ha_rocksdb
rocksdb_compaction_sequential_deletes = 10000
rocksdb_compaction_sequential_deletes_count_sd = true
rocksdb_max_total_wal_size = 128MB
rocksdb_keep_log_file_num = 1
rocksdb_delete_obsolete_files_period_micros = 30000000
rocksdb_max_background_jobs = 2
rocksdb_max_subcompactions = 2
rocksdb-override-cf-options='default={compression=kZlibCompression; bottommost_compression=kZlibCompression; max_write_buffer_number=6; write_buffer_size=16m; min_write_buffer_number_to_merge=2; level0_file_num_compaction_trigger=4; max_bytes_for_level_base=128m; max_bytes_for_level_multiplier=10; target_file_size_base=12800k; target_file_size_multiplier=10; level0_slowdown_writes_trigger=16; level0_stop_writes_trigger=20;}'
We read many of RocksDB github wiki pages and tried combinations of different settings with no luck
After searching and reading the documentations for many days, I ended up using FIFO-Style compaction with a maximum size which is updated dynamically by the application.