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

Innodb bulk load : high IO, crashes on Linux, OOM on Linux, as tested with LOAD DATA INFILE

    XMLWordPrintable

Details

    Description

      Found by accident, trying to understand why sysbench loads its data faster than
      mariadb-import --parallel. Mostly likely present before 11.6, did not try.

      TLDR: for me, Bulk load turns out to be 1.5 times slower (on fast NVME), and more than 15 times more IO intensive.
      I tried on Windows, and it is just slow, and this case is below

      I tried on Linux, hoping that maybe BULK relies on temp directory being in memory, but got crash (tmpdir exhausted), or when
      I allowed tmpfs to be 32GB (whole memory), then server process got killed, probably Linux peculiarity the OOM killer.
      If tmpdir points to persistent disk, effects are about the same on Linux as they are on Windows

      Below, my test case:

      Step 0 - server parameters

      We run load "in-memory", and both bufferpool and redo log will be larger than the IBD file.

      [mysqld]
      innodb_log_file_size=20G
      innodb_buffer_pool_size=20G
      innodb-buffer-pool-load-at-startup=OFF
      innodb_buffer_pool_dump_at_shutdown=OFF
      innodb_stats_auto_recalc=0
      

      Step 1. Using sysbench, load a single table with 50M rows (~11GB ibd file)

      sysbench oltp_read_write --mysql-user=root  --tables=1 --table-size=50000000 --threads=1  prepare
      

      Elapsed time (seconds): 273.673
      Process IO Read Bytes : 14387837038
      Process IO Write Bytes : 19291281550

      Step 2: backup sbtest.sbtest1 table

      mariadb -uroot -e "select * into outfile 'C:/work/sbtest1.data' from sbtest.sbtest1"
      

      Elapsed time (seconds): 36.482

      restart server

      Step 3: Load this data again, without bulk optimization

      mariadb -uroot -e "truncate sbtest.sbtest1; load data infile 'C:/work/sbtest1.data'  into table sbtest.sbtest1"
      

      Elapsed time (seconds): 240.996
      Process Read Bytes 9916879781
      Process Write Bytes 15149246158

      Restart server again,

      Step 4: Load the same data once more, with bulk optimization (enforced by setting unique_checks=0,foreign_key_checks=0)

      mariadb -uroot -e "truncate sbtest.sbtest1; set unique_checks=0,foreign_key_checks=0, load data infile 'C:/work/sbtest1.data'  into table sbtest.sbtest1"
      

      This is the step that is very slow, pegs the disk at ~1GB reads and writes, and this is where it crashes on Linux, if /tmp is memory.
      When it is slow, CPU is at about 3%, busy with some reads and writes to temp. files.

      Elapsed time (seconds): 371.664
      Process Read Bytes: 167433868189
      Process Write Bytes: 170475124430

      Compare execution times and IO usage from Step 3 (without bulk) and Step 4(with bulk) , and sysbench "prepare"

        Execution Time IO-read Gbytes IO-write Gbytes
      Sysbench prepare 273 13.4 17.97
      LOAD DATA NO-Bulk 240 9.24 14.11
      LOAD DATA Bulk 371 155.93 158.77

      Attachments

        Issue Links

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              wlad Vladislav Vaintroub
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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