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

LOAD DATA into InnoDB w/partitions: huge performance loss, affected 10.6+

    XMLWordPrintable

Details

    Description

      Hi,
      Starting from MariaDB ver 10.6, I experience huge performance loss (like x10)
      when loading (via LOAD DATA INFILE) data into table with partitions.
      Time of loading depends on number of partitions in table.
      This bug appeared on ver. 10.6.
      10.11.4 is also affected, although I don't include results here.
      All previous versions up to 10.5.21 are not affected.

      Let me share results first:
      tbl0 - simple table with 3 columns, without partitions
      tbl1 - as above, with 1 partition
      tbl2 - as above, with 800 partitions

      Below are times spent on LOAD DATA INFILE (each test repeated 3 times):
      10.6.14 tbl0 = 1.406s 1.384s 1.338s
      10.6.14 tbl1 = 1.553s 1.432s 1.429s
      10.6.14 tbl2 = 11.692s 10.929s 11.019s
      10.5.21 tbl0 = 1.457s 1.332s 1.228s
      10.5.21 tbl1 = 1.511s 1.496s 1.249s
      10.5.21 tbl2 = 1.500s 1.543s 1.499s

      More info about my configuration:
      OS: Linux Rocky 8.8, x86_64

      my.cnf:

      [mysqld]
      innodb_buffer_pool_size = 10G
      tmpdir = /opt/tmp
      log_error
      log_warnings
      innodb_log_file_size = 6G
      innodb_file_per_table = 1

      How I build MariaDB:

      VER=10.6.14
      tar xzf mariadb-$VER.tar.gz
      mkdir build-$VER
      cd build-$VER
      NP=/opt/db.$VER
      cmake3 ../mariadb-$VER \
      -DCMAKE_INSTALL_PREFIX=$NP \
      -DINSTALL_MYSQLDATADIR=$NP/data \
      -DINSTALL_SYSCONFDIR=$NP/etc \
      -DINSTALL_UNIX_ADDRDIR=$NP/tmp/mysql.sock \
      -DCMAKE_CXX_FLAGS=-march=sandybridge \
      -DCMAKE_C_FLAGS=-march=sandybridge \
      -DPLUGIN_MROONGA=NO \
      -DWITHOUT_TOKUDB=1 \
      -DCMAKE_BUILD_TYPE=Release
      make -j12
      make install

      Tables - see attached tables.sql for details, but in principle simple table like:

      CREATE TABLE `tbl0` (
      `id` int(11) NOT NULL,
      `tm` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
      `v` varchar(15) DEFAULT NULL,
      PRIMARY KEY (`tm`,`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin
      ;

      Test scenario:

      cat ~/tables.sql | mysql -uroot test
      FILE=~/test.txt
      perl -e 'print "$_\t2023-07-25\t1234567890ABCD\n" for (1..2e5)' > $FILE
      time mysql -uroot -e "LOAD DATA INFILE '$FILE' IGNORE INTO TABLE tbl0" test
      time mysql -uroot -e "LOAD DATA INFILE '$FILE' IGNORE INTO TABLE tbl1" test
      time mysql -uroot -e "LOAD DATA INFILE '$FILE' IGNORE INTO TABLE tbl2" test
      mysql -uroot -e "truncate tbl0; truncate tbl1; truncate tbl2" test

      Best Regards,
      Karol

      Attachments

        1. tables.sql
          58 kB
          Karol Roslaniec

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              Karol Karol Roslaniec
              Votes:
              3 Vote for this issue
              Watchers:
              18 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.