Linux

philip_38 Hi! Would you have table definition for npadata please? Also, a full error log would be of assistance, if you still have it from 2019. Thanks.

Similar outcome (Status Code: 2, Status: Corruption: truncated header) in MDEV-17777 with OOS.
Similar outcome (Status Code: 2, Status: Corruption: truncated header) in https://github.com/facebook/mysql-5.6/issues/814 with OOM (and some extra info!).

 CREATE TABLE `npadata` (

  `state` varchar(2) NOT NULL DEFAULT '??',

  `company` varchar(30) DEFAULT NULL,

  `ocn` varchar(4) NOT NULL DEFAULT 'N/A',

  `prefix_type` varchar(10) DEFAULT NULL,

  `ratecenter` varchar(40) DEFAULT NULL,

  `clli` varchar(20) DEFAULT NULL,

  `lata` varchar(5) DEFAULT NULL,

  `country` varchar(2) DEFAULT NULL,

  `wireless` tinyint(1) unsigned NOT NULL DEFAULT 0,

  `did` bigint(20) unsigned NOT NULL,

  `lrn` bigint(20) unsigned DEFAULT NULL,

  `port_type` tinyint(4) unsigned DEFAULT NULL,

  `dnc` tinyint(4) unsigned DEFAULT NULL,

  `reachable` tinyint(4) unsigned DEFAULT NULL,

  `npa` varchar(3) DEFAULT NULL,

  `nxx` varchar(3) DEFAULT NULL,

  `xxxx` varchar(4) DEFAULT NULL,

  `tested` tinyint(1) DEFAULT 0,

  `lastchange` date NOT NULL DEFAULT curdate(),

  `residential` tinyint(4) unsigned NOT NULL DEFAULT 0,

  PRIMARY KEY (`did`),

  KEY `IDX_npadata` (`wireless`,`ocn`,`company`),

  KEY `IDX_npadata_state` (`state`,`wireless`,`country`),

  KEY `IDX_npadata_lrn` (`lrn`),

  KEY `IDX_npadata3` (`reachable`,`tested`),

  KEY `IDX_npadata_COMPANY` (`company`,`wireless`),

  KEY `IDX_npadata_dnc` (`dnc`),

  KEY `IDX_npadata_lastchange` (`lastchange`),

  KEY `IDX_npadata_tested` (`tested`),

  KEY `IDX_npadata2` (`npa`,`lrn`),

  KEY `NUK_npadata` (`npa`,`dnc`,`did`,`lrn`,`ocn`,`port_type`),

  KEY `IDX_npadata_residential` (`residential`),

  KEY `IDX_npadata4` (`country`,`npa`),

  KEY `IDX_npadata_country` (`country`,`npa`,`wireless`),

  KEY `IDX_npadata5` (`npa`,`nxx`)

) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 

I don't have the error log but I can give you access to the box, and you can try to replicate it.

philip_38 Thank you. No need for box access. Can you please clarify data size (number of table entries/records)? Can you also clarify machine specs at the time of the corruption especially memory and disk size.

In terms of avoiding the OOM/OOS and subsequent crash recovery to start with, this is not a bug in MariaDB, and a pure RocksDB configuration issue as far as I can tell.

Preventing this issue would involve things like:

• Ensuring sufficient memory and disk space is available for the requested operation.
• Ensuring rocksdb_max_row_locks is set correctly.
• Ensuring RocksDB is tuned correctly.

For this, I would propose that any person running into this takes a sample of their dataset, for example 10% of the data, and tests the ALTER on a test environment. This way the disk and memory required can be monitored. Whilst multiplying those numbers by 10 may be a bit too simplistic a calculation, it will at least give an idea/an indication of the approximate required memory and disk space.

In terms of the failing crash recovery/failed RocksDB open (Status Code: 2, Status: Corruption: truncated header), this is a known Facebook RocksDB issue https://github.com/facebook/mysql-5.6/issues/814 - as such this bug was marked upstream.

The table has 2BN records.
I can give you access via SQL
the data is not important.

1) Thank you for a nice bug report and sorry you encounter this
2) Can you share the RocksDB LOG file? I am rusty but think it is at $mysql-data-dir/.rocksdb/LOG. If the file is too big then the prefix and suffix of it will be useful.
3) How much RAM does your server have? With a 62G block cache and 16 x 256M write buffers, that can be ~66G of RAM. Regardless, corruption after OOM isn't a reasonable outcome.
4) I don't see any problems from the my.cnf except for rocksdb_max_open_files=-1. Although it isn't clear to me that an open file would use too much memory given my.cnf has: cache_index_and_filter_blocks=1
5) If supported, performance should improve by using zstd rather than zlib – less CPU overhead from compression means there will be fewer write stalls

> [ERROR] RocksDB: Error opening instance, Status Code: 2, Status: Corruption: truncated header

This may happen when operating with rocksdb_wal_recovery_mode=1 (kAbsoluteConsistency) and when RocksDB crashes. We recommend using rocksdb_wal_recovery_mode=2 (kPointInTimeRecovery). kPointInTimeRecovery is actually a default in RocksDB and MyRocks (FB MyRocks) also switched default from 1 to 2 before. Could you try setting to 2 if it is 1? 2 (kPointInTimeRecovery) means recovery stops and opens RocksDB when hitting a corrupted wal entry. It starts the instance with losing some data (up to the last valid wal entry), but replication state is consistent, so the instance can be recovered from a primary instance.

root@scrubber58:/usr/src# free -g
total used free shared buff/cache available
Mem: 503 100 336 0 67 385
Swap: 7 0 7

the box has 64 cores

df -H
Filesystem Size Used Avail Use% Mounted on
/dev/sdb1 1.1T 626G 448G 59% /
none 504k 4.1k 500k 1% /dev
/dev/sda2 53G 21G 29G 42% /usr/lib/modules
none 271G 0 271G 0% /dev/shm
tmpfs 55G 1.1M 55G 1% /run
tmpfs 5.3M 0 5.3M 0% /run/lock
tmpfs 271G 0 271G 0% /sys/fs/cgroup
tmpfs 55G 0 55G 0% /run/user/0

I am uploading the log as a file, it is 97K long

I am trying to stop using zlib, can you please upload the new configuration line that uses zstd instead of zlib?

A blog post from me with advice on my.cnf for MyRocks is here. You need to determine whether the mysqld (mariad?) binary includes support for ZStd. The linked blog post shows how to do that (grep for "Compression algorithms supported"), and if supported you replace kZlibCompression with the name for ZStd listed in that section.

Do you know how big the mysqld (mariad) process is when it dies? From the "df -g" output this host has ~500G of RAM and the my.cnf has a 62G block cache and ~4G (16 x 256M) write buffer.

From the LOG you uploaded I see this, so kZSTD can replace kZlibCompression

2021/09/15-22:14:46.852318 7f87d8ed7800 Compression algorithms supported:

2021/09/15-22:14:46.852320 7f87d8ed7800 	kZSTDNotFinalCompression supported: 1

2021/09/15-22:14:46.852322 7f87d8ed7800 	kZSTD supported: 1

2021/09/15-22:14:46.852323 7f87d8ed7800 	kXpressCompression supported: 0

2021/09/15-22:14:46.852325 7f87d8ed7800 	kLZ4HCCompression supported: 1

2021/09/15-22:14:46.852326 7f87d8ed7800 	kLZ4Compression supported: 1

2021/09/15-22:14:46.852327 7f87d8ed7800 	kBZip2Compression supported: 0

2021/09/15-22:14:46.852328 7f87d8ed7800 	kZlibCompression supported: 1

2021/09/15-22:14:46.852335 7f87d8ed7800 	kSnappyCompression supported: 1

The only odd things I see in the RocksDB LOG file are:

2021/09/15-22:14:47.647397 7f87d8ed7800 [version_set.cc:3289] More existing levels in DB than needed. max_bytes_for_level_multiplier may not be guaranteed.

And a huge size difference between L0 and the next level

** Compaction Stats [default] **

Level    Files   Size     Score Read(GB)  Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) CompMergeCPU(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

  L0      4/0   68.13 MB   1.0      0.0     0.0      0.0       0.0      0.0       0.0   0.0      0.0      0.0      0.00              0.00         0    0.000       0      0

  L6   1689/0   102.60 GB   0.0      0.0     0.0      0.0       0.0      0.0       0.0   0.0      0.0      0.0      0.00              0.00         0    0.000       0      0

 Sum   1693/0   102.67 GB   0.0      0.0     0.0      0.0       0.0      0.0       0.0   0.0      0.0      0.0      0.00              0.00         0    0.000       0      0

 Int      0/0    0.00 KB   0.0      0.0     0.0      0.0       0.0      0.0       0.0   0.0      0.0      0.0      0.00              0.00         0    0.000       0      0

This case is from 2019. I have no records of any kind.

OK, thanks for the report.

Question:
to replace zlib for zstd, can do a global "sed" on my.cnf and change
kzlib for kzstd ?
I looked at your blog and couldn't figure it out.

Something like: sed s/kZlibCompression/kZSTD/g
Although I didn't test that sed command.

psergei, shall we switch rocksdb_wal_recovery_mode to be 2 (kPointInTimeRecovery) by default, like the upstream did?

also we can force jemalloc or recomment jemalloc, and force transparent_huge_pages=never or recommend it. if it'll make any difference.

We just got the same issue after a OOM. If I understand correctly, the risk of rocksdb_wal_recovery_mode=2 is only to lose the Durable property of ACID, in case of unreliable hardware: we'd need https://github.com/facebook/rocksdb/issues/6288 to force the user to first solve IO issues before actually dropping committed transactions.