Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

mysql crash: aborting on BG write error

Description

Using the RocksDB engine, there are about 45,000 SST files, mainly a very large table (about 30T of text data). Before MySQL Crash, using bulk load to load data, encountered the following error:

2022-03-30T16:26:11.172667+09:00 27754 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: '[db/version_set.cc:4330] Error in committing version edit to MANIFEST: VersionEdit { PrevLogNumber: 0 NextFileNumber: 80204 LastSeq: 3897361155 AddFile: 6 66257 42238211 '0000012E80000000BEBC2001' seq:0, type:1 .. '0000012E80000000BEBCB65B' seq:0, type:1 oldest _ancester_time:1648625170 file_creation_time:1648625170 file_checksum: file_checksum_func_name: Unknown AddFile: 6 66258 42661797 '0000012E80000000BEBCB65C' seq:0, type:1 .. '00000 12E80000000BEBD4F51' seq:0, type:1 oldest_ancester_time:1648625170 file_creation_time:1648625170 file_checksum: file_checksum_func_name: Unknown AddFile: 6 66259 42790936 '0000012E 80000000BEBD4F52' seq:0, type:1 .. '0000012E80000000BEBDE24A' seq:0, type:1 oldest_ancester_time:1648625170 file_creation_time:1648625170 file_checksum: file_checksum_func_name: Unkn own AddFile: 6 66260 42686241 '0000012E80000000BEBDE24B' seq:0, type:1 .. '0000012E80000000BEBE74F9' seq:0, type:1 oldest_ancester_time:1648625170 file_creation_time:1648625170 fil e_checksum: file_checksum' 2022-03-30T16:26:11.553218+09:00 27754 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'Error 504 finalizing last SST file while setting bulk loading variable' 2022-03-30T16:26:25.651224+09:00 27770 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: '[db/version_set.cc:4330] Error in committing version edit to MANIFEST: VersionEdit { PrevLogNumber: 0 NextFileNumber: 83708 LastSeq: 3897361155 AddFile: 6 80204 41271204 '0000012E80000000F32FDC01' seq:0, type:1 .. '0000012E80000000F330A2C0' seq:0, type:1 oldest _ancester_time:1648625185 file_creation_time:1648625185 file_checksum: file_checksum_func_name: Unknown AddFile: 6 80205 41124270 '0000012E80000000F330A2C1' seq:0, type:1 .. '00000 12E80000000F3316314' seq:0, type:1 oldest_ancester_time:1648625185 file_creation_time:1648625185 file_checksum: file_checksum_func_name: Unknown AddFile: 6 80206 41587816 '0000012E 80000000F3316315' seq:0, type:1 .. '0000012E80000000F3322313' seq:0, type:1 oldest_ancester_time:1648625185 file_creation_time:1648625185 file_checksum: file_checksum_func_name: Unkn own AddFile: 6 80207 42221366 '0000012E80000000F3322314' seq:0, type:1 .. '0000012E80000000F332E020' seq:0, type:1 oldest_ancester_time:1648625185 file_creation_time:1648625185 fil e_checksum: file_checksum' 2022-03-30T16:26:26.063840+09:00 27770 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'Error 504 finalizing last SST file while setting bulk loading variable' 2022-03-30T16:26:32.258426+09:00 27742 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: '[db/version_set.cc:4330] Error in committing version edit to MANIFEST: VersionEdit { PrevLogNumber: 0 NextFileNumber: 87256 LastSeq: 3897361155 AddFile: 6 81949 41968700 '0000012E800000007BFA4801' seq:0, type:1 .. '0000012E800000007BFAD9C3' seq:0, type:1 oldest _ancester_time:1648625192 file_creation_time:1648625192 file_checksum: file_checksum_func_name: Unknown AddFile: 6 81950 41950342 '0000012E800000007BFAD9C4' seq:0, type:1 .. '00000 12E800000007BFB6FDE' seq:0, type:1 oldest_ancester_time:1648625192 file_creation_time:1648625192 file_checksum: file_checksum_func_name: Unknown AddFile: 6 81951 41635601 '0000012E 800000007BFB6FDF' seq:0, type:1 .. '0000012E800000007BFC05C2' seq:0, type:1 oldest_ancester_time:1648625192 file_creation_time:1648625192 file_checksum: file_checksum_func_name: Unkn own AddFile: 6 81952 41745031 '0000012E800000007BFC05C3' seq:0, type:1 .. '0000012E800000007BFC95CD' seq:0, type:1 oldest_ancester_time:1648625192 file_creation_time:1648625192 fil e_checksum: file_checksum' 2022-03-30T16:26:32.427064+09:00 27760 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: '[db/version_set.cc:4330] Error in committing version edit to MANIFEST: VersionEdit { PrevLogNumber: 0 NextFileNumber: 89004 MaxColumnFamily: 1 LastSeq: 3897361155 ColumnFamily: 0 } ' 2022-03-30T16:26:32.620788+09:00 27744 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'Error 504 finalizing last SST file while setting bulk loading variable' 2022-03-30T16:26:32.678357+09:00 27738 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'Error 504 finalizing last SST file while setting bulk loading variable' 2022-03-30T16:26:32.695567+09:00 27742 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'Error 504 finalizing last SST file while setting bulk loading variable' 2022-03-30T16:26:32.826278+09:00 27760 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'Error 504 finalizing last SST file while disconnecting' 2022-03-30T16:26:56.318103+09:00 27750 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: '[db/version_set.cc:4330] Error in committing version edit to MANIFEST: VersionEdit { PrevLogNumber: 0 NextFileNumber: 92551 LastSeq: 3897361155 AddFile: 6 90780 42162205 '0000012E80000000A6E49C01' seq:0, type:1 .. '0000012E80000000A6E52BB5' seq:0, type:1 oldest _ancester_time:1648625216 file_creation_time:1648625216 file_checksum: file_checksum_func_name: Unknown AddFile: 6 90781 41986677 '0000012E80000000A6E52BB6' seq:0, type:1 .. '00000 12E80000000A6E5BFAA' seq:0, type:1 oldest_ancester_time:1648625216 file_creation_time:1648625216 file_checksum: file_checksum_func_name: Unknown AddFile: 6 90782 41972788 '0000012E 80000000A6E5BFAB' seq:0, type:1 .. '0000012E80000000A6E65389' seq:0, type:1 oldest_ancester_time:1648625216 file_creation_time:1648625216 file_checksum: file_checksum_func_name: Unkn own AddFile: 6 90783 42176641 '0000012E80000000A6E6538A' seq:0, type:1 .. '0000012E80000000A6E6E465' seq:0, type:1 oldest_ancester_time:1648625216 file_creation_time:1648625216 fil e_checksum: file_checksum' .... 2022-03-30T16:45:02.150553+09:00 27808 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'Error 504 finalizing last SST file while setting bulk loading variable'

 

Because there are some failures in data loading, I can't judge which data is not loaded, so I want to delete the table data and reload it. When I execute truncate, the database crashes, and the error log is as follows:

 

2022-03-30T23:33:40.013013+09:00 0 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: '[db/version_set.cc:4330] Error in committing version edit to MANIFEST: VersionEdit { Prev LogNumber: 0 NextFileNumber: 190491 MaxColumnFamily: 1 LastSeq: 3897361163 DeleteFile: 6 34065 DeleteFile: 6 34066 DeleteFile: 6 34067 DeleteFile: 6 34068 DeleteFile: 6 34069 DeleteFile: 6 34070 DeleteFile: 6 34071 DeleteFile: 6 34072 DeleteFile: 6 34073 DeleteFile: 6 34074 DeleteFile: 6 34075 DeleteFile: 6 34076 DeleteFile: 6 340 77 DeleteFile: 6 34078 DeleteFile: 6 34079 DeleteFile: 6 34080 DeleteFile: 6 34081 DeleteFile: 6 34082 DeleteFile: 6 34083 DeleteFile: 6 34084 DeleteFile: 6 34085 DeleteFile: 6 34086 DeleteFile: 6 34087 DeleteFile: 6 34088 DeleteFile: 6 34089 DeleteFile: 6 34090 DeleteFile: 6 34091 DeleteFile: 6 34092 DeleteFile: 6 34093 DeleteFile: 6 34094 DeleteFile: 6 34095 DeleteFile: 6 34096 DeleteFile: 6 34097 DeleteFile: 6 34098 DeleteFile: 6 34099 DeleteFile: 6 34100 DeleteFile: 6 34101 DeleteFile: 6 34102 DeleteFile: ' 2022-03-30T23:33:40.034370+09:00 0 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'BG thread failed to write to RocksDB, Status Code: 5, Status: IO error: While open a file for appending: ./.rocksdb/MANIFEST-190490: Too many open files' 2022-03-30T23:33:40.034404+09:00 0 [ERROR] [MY-000000] [Server] Plugin rocksdb reported: 'aborting on BG write error.' 14:33:40 UTC - mysqld got signal 6 ; Most likely, you have hit a bug, but this error can also be caused by malfunctioning hardware.Error opening mysql binary Server Version: 8.0.27-18 Percona Server (GPL), Release 18, Revision 24801e21b45Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0 thread_stack 0x100000 /usr/sbin/mysqld(my_print_stacktrace(unsigned char const*, unsigned long)+0x3d) [0x202759d] /usr/sbin/mysqld(handle_fatal_signal+0x37b) [0x11215fb] /lib64/libpthread.so.0(+0x117e0) [0x7f9d283f37e0] /lib64/libc.so.6(gsignal+0x110) [0x7f9d26656ae0] /lib64/libc.so.6(abort+0x148) [0x7f9d26657f88] /usr/lib64/mysql/plugin/ha_rocksdb.so(myrocks::rdb_handle_io_error(rocksdb::Status, myrocks::RDB_IO_ERROR_TYPE)+0x1de) [0x7f9a3bd33dbe] /usr/lib64/mysql/plugin/ha_rocksdb.so(myrocks::Rdb_drop_index_thread::run()+0x6eb) [0x7f9a3bd5a3fb] /usr/lib64/mysql/plugin/ha_rocksdb.so(myrocks::Rdb_thread::thread_func(void*)+0x29) [0x7f9a3bdb09f9] /usr/sbin/mysqld() [0x24c68b0] /lib64/libpthread.so.0(+0x740b) [0x7f9d283e940b] /lib64/libc.so.6(clone+0x3f) [0x7f9d2670fe7f] Please help us make Percona Server better by reporting any bugs at https://bugs.percona.com/You may download the Percona Server operations manual by visiting

The RocksDB configuration file is as follows:

 

#rocksdb rocksdb_max_open_files = -1 rocksdb_max_background_jobs = 8 rocksdb_max_total_wal_size = 4G rocksdb_block_cache_size = 32G #25%# rate limiter #rocksdb_bytes_per_sync = 16777216 #rocksdb_wal_bytes_per_sync = 4194304 #rocksdb_rate_limiter_bytes_per_sec = 104857600 #100MB/s# triggering compaction if there are many sequential deletes rocksdb_compaction_sequential_deletes_count_sd=1 rocksdb_compaction_sequential_deletes=199999 rocksdb_compaction_sequential_deletes_window=200000rocksdb_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=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=16mrocksdb_use_direct_reads=ON rocksdb_use_direct_io_for_flush_and_compaction=ON

 

Environment

None

Smart Checklist

Activity

Show:

Aaditya Dubey June 7, 2022 at 7:10 AM

Hi  ,

Thank you for the updates.
closing the bug now.

huang zhiyong June 7, 2022 at 3:44 AM

please close this ticket

Aaditya Dubey May 10, 2022 at 10:54 AM

Hi ,

please let me know if ticket can be closed if issue is fixed.

huang zhiyong May 10, 2022 at 10:51 AM

hi @

got it . thanks

Aaditya Dubey April 11, 2022 at 7:29 AM

Hi ,

Thank you for the updates.

Warning ~ As per the docs
Setting rocksdb_max_open_files to -1 is dangerous, as server may quickly run out of file handles in this case.

A value of -2 denotes auto tuning: just setsrocksdb_max_open_files value to 1/2 of open_files_limit. Finally, -1 means no limit, i.e. an infinite number of file handles.

Please go through the document for more details https://www.percona.com/doc/percona-server/8.0/myrocks/variables.html#rocksdb_max_open_files

Let me know if it helps.

Done

Details

Assignee

Reporter

Components

Affects versions

Priority

Smart Checklist

Created March 30, 2022 at 3:06 PM
Updated March 6, 2024 at 10:14 AM
Resolved June 7, 2022 at 7:11 AM
Configure