MySQL Forums :: NDB clusters :: NDB Cluster dump import slow


Advanced Search

Re: NDB Cluster dump import slow
Posted by: Thomas Waibel ()
Date: February 14, 2017 09:39AM

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...

Options: ReplyQuote


Subject Views Written By Posted
NDB Cluster dump import slow 527 Nils Burkhardt 11/11/2016 07:00AM
Re: NDB Cluster dump import slow 297 David Ashman 11/11/2016 12:06PM
Re: NDB Cluster dump import slow 317 Nils Burkhardt 11/14/2016 01:28AM
Re: NDB Cluster dump import slow 273 David Ashman 11/14/2016 12:27PM
Re: NDB Cluster dump import slow 99 Thomas Waibel 02/14/2017 09:39AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.