myslqimport --use-threads

Question

I have a large database I'm copying to a slave server. Trying to import it (about 15GB) via a regular mysqldump took 2 days and failed. So I'm trying the mysqldump --tab trick.

I also want to import using --use-threads - but it doesn't seem to be doing multiple tables at once. Is there any way to tell if it's even working?

mysqldump --single-transaction --quick --hex-blob --master-data=1 --tab=/tmp/backup/ apm

on slave:

cat *.sql | mysql apm
mysqlimport --lock-tables --use-threads=4 apm /tmp/backup/*.txt

Also: Any idea how to disable binlog without editing the conf file and restarting the server? Seems kinda dumb & slow that mysql's copying all the data into the binlog again too.

Answer 1

in fact it appears that the threading only kicks in when you are specifying multiple files to be imported (into multiple tables) -- it doesn't help with a single large file.

the way to tell if it's actually threading is to look at SHOW PROCESSLIST output. here you can see it actually working, though as mentioned, on different tables.

mysql> show processlist;
+-------+--------+------------------+------+---------+------+-----------+------------------------------------------------------------------------------------------------------+
| Id    | User   | Host             | db   | Command | Time | State     | Info                                                                                                 |
+-------+--------+------------------+------+---------+------+-----------+------------------------------------------------------------------------------------------------------+
|  4097 | root   | 127.0.0.1:33372  | test | Query   |    0 | executing | show processlist                                                                                     | 
|  6145 | root   | 10.2.13.44:44182 | test | Query   |    3 | executing | LOAD DATA   INFILE 'qpcrecpls2.tsv' INTO TABLE `qpcrecpls2` IGNORE 0 LINES                           | 
|  7169 | root   | 10.2.13.44:44186 | test | Query   |    3 | executing | LOAD DATA   INFILE 'qpcrecpls1.tsv' INTO TABLE `qpcrecpls1` IGNORE 0 LINES                           | 
|  8193 | root   | 10.2.13.44:44184 | test | Query   |    3 | executing | LOAD DATA   INFILE 'qpcrecpls3.tsv' INTO TABLE `qpcrecpls3` IGNORE 0 LINES                           | 
|  9217 | root   | 10.2.13.44:44188 | test | Query   |    3 | executing | LOAD DATA   INFILE 'qpcrecpls4.tsv' INTO TABLE `qpcrecpls4` IGNORE 0 LINES                           | 
| 10241 | root   | 10.2.13.44:44183 | test | Query   |    3 | executing | LOAD DATA   INFILE 'qpcrecpls5.tsv' INTO TABLE `qpcrecpls5` IGNORE 0 LINES                           | 
| 11265 | root   | 10.2.13.44:44185 | test | Query   |    3 | executing | LOAD DATA   INFILE 'qpcrecpls.tsv' INTO TABLE `qpcrecpls` IGNORE 0 LINES                             | 
| 12289 | root   | 10.2.13.44:44189 | test | Query   |    3 | executing | LOAD DATA   INFILE 'qpcrecpls6.tsv' INTO TABLE `qpcrecpls6` IGNORE 0 LINES                           | 
| 13313 | root   | 10.2.13.44:44190 | test | Query   |    3 | executing | LOAD DATA   INFILE 'qpcrecpls7.tsv' INTO TABLE `qpcrecpls7` IGNORE 0 LINES                           | 
+-------+--------+------------------+------+---------+------+-----------+------------------------------------------------------------------------------------------------------+

--verbose output is also illuminating.