Re: NDB Cluster dump import slow

[Thomas Waibel]

Hi,
had the same problem here - single threaded import of a mysql dump is slow.

Our solution was to split that dump during import over more processes.

Here some sample bash code
----
#!/bin/bash
MAXPROCESSES='32'
FILES=$@
DATABASE='db'
DBUSER='--defaults-extra-file=/etc/mysql/user-root.cnf'
PATH=/opt/mysql/server-5.7/bin:/opt/mysql/server-5.6/bin:/usr/sbin:$PATH
OPTIONS=''

# Loop over all files
for FILE in ${FILES}
do
# Endless loop
echo -n &>${FILE}-restore.log
echo "( lzop -d < ${FILE} | sed -e \"s/^INSERT INTO/INSERT IGNORE INTO/\" | mysql ${DBUSER} ${OPTIONS} ${DATABASE} &>${FILE}-restore.log )&"
lzop -d < ${FILE} | \
sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" | \
while read LINE
do
while true
do
# Compare number of jobs with maximum allowed jobs
JOBLIST=($(jobs -p))
if (( ${#JOBLIST[*]} <= ${MAXPROCESSES} ))
then
( echo ${LINE} | mysql ${DBUSER} ${OPTIONS} ${DATABASE} &>>${FILE}-restore.log )&
# Exit endless loop
break
else
sleep 1
fi
done
done
done

----
You need to separate schema dump from data dump - above is for the data dump containing only INSERT statements...

And the forum software here is really crap...