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

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

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

        Issue Links

          Activity

            Please mention in the commit message that the second commit of MDEV-24818 made the HA_EXTRA_IGNORE_INSERT redundant.

            marko Marko Mäkelä added a comment - Please mention in the commit message that the second commit of MDEV-24818 made the HA_EXTRA_IGNORE_INSERT redundant.

            commit 8ea923f55b7666a359ac2c54f6c10e8609d16846
            Author: Marko Mäkelä <marko.makela@mariadb.com>
            Date:   Tue Mar 16 15:21:34 2021 +0200
             
                MDEV-24818: Optimize multi-statement INSERT into an empty table
                
                If the user "opts in" (as in the parent
                commit 92b2a911e516ded986945d5d3dc6326f5575a21a),
                we can optimize multiple INSERT statements to use table-level locking
                and undo logging.
                
                There will be a change of behavior:
                
                    CREATE TABLE t(a PRIMARY KEY) ENGINE=InnoDB;
                    SET foreign_key_checks=0, unique_checks=0;
                    BEGIN; INSERT INTO t SET a=1; INSERT INTO t SET a=1; COMMIT;
                
                will end up with an empty table, because in case of an error,
                the entire transaction will be rolled back, instead of rolling
                back the failing statement. Previously, the second INSERT statement
                would have been logged row by row, and only that second statement
                would have been rolled back, leaving the first INSERT intact.
                
                lock_table_x_unlock(), trx_mod_table_time_t::WAS_BULK: Remove.
                Because we cannot really support statement rollback in this
                optimized mode, we will not optimize the locking. The exclusive
                table lock will be held until the end of the transaction.
            

            Above commit has made the HA_EXTRA_IGNORE_INSERT redundant.

            thiru Thirunarayanan Balathandayuthapani added a comment - commit 8ea923f55b7666a359ac2c54f6c10e8609d16846 Author: Marko Mäkelä <marko.makela@mariadb.com> Date: Tue Mar 16 15:21:34 2021 +0200   MDEV-24818: Optimize multi-statement INSERT into an empty table If the user "opts in" (as in the parent commit 92b2a911e516ded986945d5d3dc6326f5575a21a), we can optimize multiple INSERT statements to use table-level locking and undo logging. There will be a change of behavior: CREATE TABLE t(a PRIMARY KEY) ENGINE=InnoDB; SET foreign_key_checks=0, unique_checks=0; BEGIN; INSERT INTO t SET a=1; INSERT INTO t SET a=1; COMMIT; will end up with an empty table, because in case of an error, the entire transaction will be rolled back, instead of rolling back the failing statement. Previously, the second INSERT statement would have been logged row by row, and only that second statement would have been rolled back, leaving the first INSERT intact. lock_table_x_unlock(), trx_mod_table_time_t::WAS_BULK: Remove. Because we cannot really support statement rollback in this optimized mode, we will not optimize the locking. The exclusive table lock will be held until the end of the transaction. Above commit has made the HA_EXTRA_IGNORE_INSERT redundant.

            marko

            I think that it would be helpful issue a warning "ignoring IGNORE" on the SQL layer if LOAD…IGNORE or INSERT…IGNORE will be ignored if it is being executed on an InnoDB table while unique_checks=0,foreign_key_checks=0 are in effect.

            No, I don't think it should be a warning. You don't execute the command that user wanted, but do something different, this produces incorrect (according to the semantics of the user issued command) result. So I am sure this should be an error — if you cannot do what user wants, you should not proceed doing something else instead.

            Come to think of it, IGNORE doesn't make much sense when unique_checks=0, perhaps the server can error out early and don't even pass it to the engine?

            serg Sergei Golubchik added a comment - marko I think that it would be helpful issue a warning "ignoring IGNORE" on the SQL layer if LOAD…IGNORE or INSERT…IGNORE will be ignored if it is being executed on an InnoDB table while unique_checks=0,foreign_key_checks=0 are in effect. No, I don't think it should be a warning. You don't execute the command that user wanted, but do something different, this produces incorrect (according to the semantics of the user issued command) result. So I am sure this should be an error — if you cannot do what user wants, you should not proceed doing something else instead. Come to think of it, IGNORE doesn't make much sense when unique_checks=0 , perhaps the server can error out early and don't even pass it to the engine?

            serg, thank you. I have filed MDEV-31985 for the missing error handling.

            marko Marko Mäkelä added a comment - serg , thank you. I have filed MDEV-31985 for the missing error handling.

            unique_checks may only disable checking of UNIQUE .. HASH.
            Primary keys in all engines I know of are always checked!
            This means that IGNORE is still relevant even if unique_checks=0

            monty Michael Widenius added a comment - unique_checks may only disable checking of UNIQUE .. HASH. Primary keys in all engines I know of are always checked! This means that IGNORE is still relevant even if unique_checks=0

            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.