-
Type:
Bug
-
Status: Open (View Workflow)
-
Priority:
Major
-
Resolution: Unresolved
-
Affects Version/s: 10.4.7
-
Fix Version/s: 10.4
-
Component/s: Storage Engine - RocksDB
-
Labels:None
-
Environment:Linux
I was adding a field on a very large table
ALTER TABLE npadata ADD COLUMN name1 VARCHAR(64) NOT NULL DEFAULT '';
ERROR 2013 (HY000): Lost connection to MySQL server during query
Note: there is plenty of space available in the box
tail /var/lib/mysql/*.err
Version: '10.4.7-MariaDB-1:10.4.7+maria~bionic' socket: '/var/lib/mysql/mysql.sock' port: 3306 mariadb.org binary distribution
2019-08-19 15:32:00 419 [Note] Zerofilling moved table: './mysql/proc'
2019-08-20 14:19:50 901 [Note] mysqld: O_TMPFILE is not supported on /temp (disabling future attempts)
2019-08-22 5:53:01 0 [Note] RocksDB: 2 column families found
2019-08-22 5:53:01 0 [Note] RocksDB: Column Families at start:
2019-08-22 5:53:01 0 [Note] cf=default
2019-08-22 5:53:01 0 [Note] write_buffer_size=268435456
2019-08-22 5:53:01 0 [Note] target_file_size_base=67108864
2019-08-22 5:53:01 0 [Note] cf=_system_
2019-08-22 5:53:01 0 [Note] write_buffer_size=268435456
2019-08-22 5:53:01 0 [Note] target_file_size_base=67108864
2019-08-22 5:53:44 0 [ERROR] RocksDB: Error opening instance, Status Code: 2, Status: Corruption: truncated header
2019-08-22 5:53:44 0 [ERROR] Plugin 'ROCKSDB' init function returned error.
2019-08-22 5:53:44 0 [ERROR] Plugin 'ROCKSDB' registration as a STORAGE ENGINE failed.
2019-08-22 5:53:44 0 [ERROR] Unknown/unsupported storage engine: rocksdb
2019-08-22 5:53:44 0 [ERROR] Aborting
Note:
If the table is very large, adding a fields takes literally days. But also, which is worse, if you need to add many fields at once, there is no way to do it in one single event. It has to copy the entire table many times. In my case, the table has 1.92BN records. One additional field took 3 days to add. That was las week. Now I need to add 5 fields. This is not acceptable even in case that this corruption can be resolved.
The box
df -H
Filesystem Size Used Avail Use% Mounted on
/dev/sdb1 1.1T 649G 425G 61% /
none 504k 0 504k 0% /dev
tmpfs 127G 0 127G 0% /dev/shm
tmpfs 127G 1.0M 127G 1% /run
tmpfs 5.3M 0 5.3M 0% /run/lock
tmpfs 127G 0 127G 0% /sys/fs/cgroup
tmpfs 26G 0 26G 0% /run/user/0
free -g
total used free shared buff/cache available
Mem: 236 30 196 0 8 203
Swap: 7 4 3
my.cnf
[mysqld]
open_files_limit=65535
log_warnings= 0
datadir=/var/lib/mysql
tmpdir=/temp
slave_load_tmpdir=/data/temp
plugin-load-add=ha_rocksdb.so
port =3306
table_open_cache=3000
table_open_cache_instances=24
default-storage-engine = rocksdb
skip_innodb=1
max_allowed-packet=1G
large_pages=1
log_error=/var/lib/mysql/mariadb.err
general_log=0
general_log_file=mariadb.log
log_output='FILE'
lower_case_table_names=1
max_heap_table_size=20G
memlock=1
query_cache_size=0
query_cache_type=0
skip_name_resolve=1
sort_buffer_size=8M
symbolic_links=0
thread_cache_size=256
thread_handling=pool-of-threads
thread-pool-size=64
thread_stack=192K
tmp_table_size=64M
general-log=0
general-log-file=queries.log
log-output=file
join_buffer_size=8M
event_scheduler = 1
max_connections=1250
extra_max_connections=10
extra_port=9433
connect_timeout=200
interactive_timeout=3000000
net_read_timeout=2000
net_write_timeout=2000
slave_net_timeout=3600
wait_timeout=6000
rocksdb_commit_in_the_middle=1
rocksdb_allow_concurrent_memtable_write=1
rocksdb_blind_delete_primary_key=1
rocksdb_tmpdir=/temp
rocksdb_bulk_load_size=10000
rocksdb_max_total_wal_size=14G
rocksdb_table_cache_numshardbits=19
rocksdb_default_cf_options=write_buffer_size=256m;target_file_size_base=64m;max_bytes_for_level_base=512m;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=256;level0_stop_writes_trigger=256;max_write_buffer_number=16;compression_per_level=kNoCompression:kNoCompression:kNoCompression:kZlibCompression:kZlibCompression:kZlibCompression;bottommost_compression=kZlibCompression;compression_opts=-14:1:0;block_based_table_factory=
;level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true
rocksdb_override_cf_options=system=
rocksdb_write_disable_wal=0
rocksdb_flush_log_at_trx_commit=1
rocksdb_strict_collation_check=off
sync_binlog=0
join_buffer_size=256K
sort_buffer_size=256K
##innodb_spin_wait_delay=96
rocksdb_max_background_jobs=24
rocksdb_compaction_sequential_deletes=199999
rocksdb_compaction_sequential_deletes_window=200000
rocksdb_max_subcompactions=16
rocksdb_compaction_readahead_size=16m
rocksdb_use_direct_reads=ON
rocksdb_use_direct_io_for_flush_and_compaction=ON
rocksdb_max_row_locks=1073741824
rocksdb-table-stats-sampling-pct=15
rocksdb_max_open_files=-1
rocksdb_block_size=16384
rocksdb_block_cache_size=62G
rocksdb_bytes_per_sync=4194304
rocksdb_wal_bytes_per_sync=4194304
rocksdb_rate_limiter_bytes_per_sec=204857600
rocksdb_compaction_sequential_deletes_count_sd=1
rocksdb_compaction_sequential_deletes=199999