I am using Percona 8 for a while now, and we have a huge database with heavy compression using Rocksdb. It’s been a solid experience, but now I come across something I cannot find a solution (or a cause) myself.
We normally have no deletions in the DB usage, but this time there is some data that needs to be removed. Querying everything goes very fast, but when I perform a actual delete it will take 1 minute, while the resulting deleted row count is around 300. That doesn’t sound like normal right?
The table where the deletions take place consists of 80.000.000.000 rows (2 ints, combined primary key and a 2 field reverse index), total size on disk 280GB (120GB data, 160GB index, using ZSTD 12 compression)
rocksdb conf:
rocksdb-override-cf-options='default={compression=kZSTD;bottommost_compression=kZSTD;compression_opts=-14:12:0;level_compaction_dynamic_level_bytes=true;write_buffer_size=512m;target_file_size_base=64m;max_bytes_for_level_base=512m;block_based_table_factory={format_version=5;index_block_restart_interval=16;cache_index_and_filter_blocks=1;filter_policy=bloomfilter:16:false;whole_key_filtering=1};optimize_filters_for_hits=true;}'
rocksdb_block_size=8096
rocksdb_max_open_files=-1
rocksdb_max_background_jobs=10
rocksdb_flush_log_at_trx_commit=1
rocksdb_large_prefix=1
open_files_limit = 102400
rocksdb_block_cache_size = 48G
rocksdb_max_total_wal_size=4G
transaction-isolation=READ-COMMITTED
rocksdb_max_subcompactions=6
Mysqld.conf
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
explicit_defaults_for_timestamp
character_set_server = utf8mb4
collation-server = utf8mb4_bin
bind-address = 0.0.0.0
log-error = /var/log/mysql/error.log
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
max_connections = 50
max_user_connections = 25
connect_timeout = 5
wait_timeout = 28800
max_allowed_packet = 1024M
thread_cache_size = 128
sort_buffer_size = 512M
bulk_insert_buffer_size = 512M
tmp_table_size = 512M
max_heap_table_size = 512M
max_connect_errors = 100000
key_buffer_size = 128M
table_open_cache = 400
myisam_sort_buffer_size = 16M
concurrent_insert = 2
read_buffer_size = 32M
read_rnd_buffer_size = 32M
default_storage_engine = InnoDB
innodb_buffer_pool_size = 2048M
innodb_log_buffer_size = 4M
innodb_file_per_table = 1
innodb_open_files = 400
innodb_io_capacity = 400
innodb_flush_method = O_DIRECT
innodb_compression_level = 6
innodb_buffer_pool_instances = 2
secure-file-priv = ""
slow_query_log=1
slow_query_log_file = /var/log/mysql/percona-slow.log
long_query_time = 15
log-slow-verbosity=query_plan
range_optimizer_max_mem_size=32M
server_id = 1
skip-log-bin
net_read_timeout = 180
net_write_timeout = 180
transaction_isolation = READ-COMMITTED
default-storage-engine=rocksdb
DB is stored on a Optane 905P 960GB disk. Server is a 24 core AMD Milan-X. Memory 200GB.
CREATE TABLE `table` (
`foo` int unsigned NOT NULL,
`bar` int unsigned NOT NULL,
PRIMARY KEY (`foo`,`bar`),
KEY `bar_foo` (`bar`,`foo`)
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
Query used is:
DELETE FROM table WHERE foo = 7880843
When there are no other queries, Percona uses 100% CPU (1 thread) until the query ends after 1 minute. (Note that a select with the same parameter runs in 0.003s)
Is there anything I can do to speed this deletion up? Any pointers to how to troubleshoot this?
I do have a staging copy of the main DB so can experiment a bit.
