Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24468

mariadb crashes when converting a large table to innodb

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Cannot Reproduce
    • 10.5.8
    • N/A
    • None
    • Linux

    Description

      [ 5623.361638] mariadbd[3596]: segfault at fffffffffffffffd ip 000055a847115d2b sp 00007f3b75630570 error 5 in mariadbd[55a846472000+158c000]
      [ 5623.361650] Code: 87 0f 1f 40 00 66 2e 0f 1f 84 00 00 00 00 00 55 48 89 e5 41 57 41 56 41 55 41 54 49 89 f4 53 48 89 fb 48 83 ec 28 48 8b 76 18 <0f> b6 47 fd f6 46 40 01 74 23 83 e0 07 3c 04 0f 87 cd 06 00 00 48
      

      I have table like this:

      CREATE TABLE `shortxxx` (
        `zzz` bigint(20) unsigned NOT NULL,
        `xxx` int(11) unsigned DEFAULT NULL,
        PRIMARY KEY (`zzz`),
        KEY `IDX_shortxxx_xxx` (`xxx`)
      ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 AVG_ROW_LENGTH=12 ROW_FORMAT=FIXED
      

      it has 600 M rows.
      I issue an alter table shortxxx engine=innodb
      and it crashes after an hour.
      Before that I tried to insert the data (813MM rows) into a new innodb table, and it crashed many times loading.
      The box has 256 GB

      innodb_adaptive_flushing = 1
      innodb_adaptive_hash_index=1
      innodb_buffer_pool_dump_at_shutdown=OFF
      innodb_buffer_pool_load_at_startup=OFF
      innodb_buffer_pool_size=20G
      innodb_compression_algorithm=zlib
      innodb_compression_level=6
      innodb_doublewrite=0
      innodb_file_per_table=1
      innodb_flush_log_at_timeout=10
      innodb_flush_log_at_trx_commit=0
      innodb_flush_method=O_DIRECT_NO_FSYNC
      innodb_flush_neighbors = 0
      innodb_force_recovery=0
      innodb_io_capacity=5000
      innodb_io_capacity_max=10000
      innodb_log_buffer_size=64M
      innodb_lru_scan_depth=1024
      innodb_max_dirty_pages_pct=90
      innodb_open_files=4000
      innodb_page_size=4096
      innodb_purge_threads=4
      innodb_read_io_threads = 64
      innodb_stats_persistent = 1
      innodb_strict_mode=0
      innodb_thread_concurrency=0
      innodb_use_atomic_writes=1
      innodb_use_native_aio=1
      innodb_write_io_threads = 64
       
      connect_timeout=200
      datadir=/var/lib/mysql
      default-storage-engine = rocksdb
      event_scheduler = 1
      extra_max_connections=10
      extra_port=9433
      general-log-file=queries.log
      general-log=0
      general_log_file=mariadb.log
      interactive_timeout=3000000
      join_buffer_size=256K
      join_buffer_size=8M
      large_pages=1
      log-output=file
      log_error=/var/lib/mysql/mariadb.err
      log_output='FILE'
      log_warnings= 0
      lower_case_table_names=1
      max_allowed-packet=1G
      max_connections=5000
      max_heap_table_size=72M
      memlock=1
      net_read_timeout=2000
      net_write_timeout=2000
      open_files_limit=65535
      plugin-load-add=ha_rocksdb.so
      plugin_load_add = ha_blackhole
      port =3306 
      query_cache_size=0
      query_cache_type=0
      skip-innodb=0
      skip_name_resolve=1
      slave_load_tmpdir=/data/temp
      slave_net_timeout=3600
      socket=/var/lib/mysql/mysql.sock
      sort_buffer_size=256K
      sort_buffer_size=8M
      symbolic_links=0
      table_open_cache=3000
      table_open_cache_instances=24
      thread-pool-size=32
      thread_cache_size=256
      thread_handling=pool-of-threads
      thread_stack=192K
      tmp_table_size=64M
      tmpdir=/large/temp
      wait_timeout=6000
       
      rocksdb-table-stats-sampling-pct=15
      rocksdb_allow_concurrent_memtable_write=1
      rocksdb_blind_delete_primary_key=1
      rocksdb_block_cache_size=25G
      rocksdb_block_size=16384
      rocksdb_bulk_load_allow_unsorted=1
      rocksdb_bulk_load_size=10000
      rocksdb_bytes_per_sync=16777216
      rocksdb_commit_in_the_middle=1
      rocksdb_compaction_readahead_size=16m
      rocksdb_compaction_sequential_deletes=199999
      rocksdb_compaction_sequential_deletes_count_sd=1
      rocksdb_compaction_sequential_deletes_window=200000
      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={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=1};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true
      rocksdb_enable_ttl=1
      rocksdb_flush_log_at_trx_commit=2
      rocksdb_max_background_jobs=24
      rocksdb_max_open_files=-1
      rocksdb_max_row_locks=1073741824
      rocksdb_max_subcompactions=16
      rocksdb_max_total_wal_size=14G
      rocksdb_strict_collation_check=off
      rocksdb_table_cache_numshardbits=19
      rocksdb_tmpdir=/data/temp
      rocksdb_use_direct_io_for_flush_and_compaction=ON
      rocksdb_use_direct_reads=OFF
      rocksdb_wal_bytes_per_sync=4194304
      rocksdb_write_disable_wal=1
      

      Attachments

        Issue Links

          Activity

            People

              marko Marko Mäkelä
              philip_38 Philip orleans
              Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.