Use the Pt-fifo-split tool to insert massive amounts of data into MySQL

In "MySQL Insert/Update data" This article mentions that, using the load data infile statement, you can load data from a file directly into MySQL, but if the file is very large, you may also need to cut the file, multiple loads, in which case, You can use the Pt-fifo-split tool to split a file into multiple data blocks (chunks) to control the amount of data that is transferred to the MySQL server each time.

Pt-fifo-split can simulate cutting files and pass through pipelines to first-in, first-out queues without actually cutting files. Pt-fifo-split is part of the Percona-toolkit kit, which can be found in detail here.

Pt-fifo-split Command usage:

pt-fifo-split [options] [FILE ...]

For example: Use Pt-fifo-split to split a large file and read 1000000 lines at a time:

pt-fifo-split --lines 1000000 hugefile.txt
while [ -e /tmp/pt-fifo-split ]; do cat /tmp/pt-fifo-split; done

Pt-fifo-split by default in/TMP to establish a FIFO file, and read the large file of data written to the FIFO file, each time the specified number of lines to the FIFO file to print an EOF character, after the completion of the read, close the FIFO file and remove, Then rebuild the FIFO file and print more rows. This will ensure that you can read the number of lines you read until the read is complete. Note This tool can only work on Unix-like operating systems.

Common options:

--fifo/tmp/pt-fifo-split, specifying the path to the FIFO file;

--offset 0, if you do not intend to start reading from the first line, you can set this parameter;

--lines 1000, number of rows per read;

--force, if the FIFO file already exists, delete it first and then recreate a FIFO file;

Here is a complete example of usage:

FLAT_FILE="/tmp/big_file.txt"
FIFO_PATH="${FLAT_FILE}.fifo"
LOAD_FILE="${FLAT_FILE}.load"
CHUNK_SIZE=100000

# Split the file
pt-fifo-split --force --lines ${CHUNK_SIZE} ${FLAT_FILE} --fifo ${FIFO_PATH} &
# Sleep 10 seconds to assure ${FIFO_PATH} exists before entering loop
sleep 10
while [ -e ${FIFO_PATH} ]
do
  # Write chunk to disk
  cat ${FIFO_PATH} > ${LOAD_FILE}
  # Load chunk into table
  mysql --database=test    --show-warnings    -vve "load data infile ‘${LOAD_FILE}‘ into table my_table;"
done

About Pt-fifo-split performance evaluation can refer to this article;

Use the Pt-fifo-split tool to insert massive amounts of data into MySQL