Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-5269

LOAD DATA INFILE poor performance

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Won't Fix
    • 6.4.4-dompe
    • 23.10.0
    • cpimport
    • None
    • 56cores, 528gb, 5 node cluster

    Description

      mariadb documentation https://mariadb.com/docs/reference/col6/cli/cpimport/ ,says

      "If the LOAD DATA [ LOCAL ] INFILE or INSERT INTO .. SELECT FROM .. statements are used with a ColumnStore table, MariaDB Enterprise ColumnStore 6 transforms the operation into a bulk load that uses cpimport by default"

      The performance between LOAD DATA INFILE and cpimport by loading from the same file, there's a huge difference. Below is the log.

      root@multi-unmanaged-db-p09:~# cpimport -s ',' -E '"' test tab1 /snfs1/temp/.db_backups/multi/test.csv
      2022-10-17 04:43:07 (462877) INFO : Running distributed import (mode 1) on all PMs...
      2022-10-17 04:44:16 (462877) INFO : For table test.tab1: 109593561 rows processed and 109593561 rows inserted.
      2022-10-17 04:44:16 (462877) INFO : Bulk load completed, total run time : 69.135 seconds

      MariaDB [test]> LOAD DATA INFILE
      -> '/snfs1/temp/.db_backups/multi/test.csv'
      -> INTO TABLE tab2
      -> FIELDS TERMINATED BY ','
      -> OPTIONALLY ENCLOSED BY '"'
      -> LINES TERMINATED BY '\n'
      -> ;
      Query OK, 109593561 rows affected (6 min 59.174 sec)
      Records: 109593561 Deleted: 0 Skipped: 0 Warnings: 0

      Attachments

        1. directCall.txt
          35 kB
          Bryan Bancroft
        2. infileCall.txt
          35 kB
          Bryan Bancroft

        Issue Links

          Activity

            People

              tntnatbry Gagan Goel (Inactive)
              bbancroft Bryan Bancroft (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.