Geek Answers Handbook

Myslqimport --use-threads

I have a large database that I copy to a slave server. An attempt to import it (about 15 GB) via regular mysqldump took 2 days and failed. So I am trying to use the mysqldump -tab trick.

I also want to import using -use-threads, but it does not seem to execute multiple tables at once. Is there any way to tell if it even works?

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

on the 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? It seems stupid and slow that mysql backs up all the data in binlog again.

+4
source share
5 answers

Are you using MySQL 5.1.7 or later ?

If you want to check if the situation really goes as expected, why not use a test circuit and only a sample of data so that it works faster?

Update Regarding whether -use-threads works, I am not sure about the possibility of a final check. However, I see no real difference in some of the tests I just used with ~ 130M data:

 mysqlimport --lock-tables --debug-info --use-threads=2 test /tmp/test/*.txt Records: 2076063 Deleted: 0 Skipped: 0 Warnings: 0 User time 0.02, System time 0.08 Maximum resident set size 3884, Integral resident set size 0 Non-physical pagefaults 737, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 1340, Involuntary context switches 17 ---- mysqlimport --lock-tables --debug-info --use-threads=4 test /tmp/test/*.txt Records: 2076063 Deleted: 0 Skipped: 0 Warnings: 0 User time 0.03, System time 0.09 Maximum resident set size 3884, Integral resident set size 0 Non-physical pagefaults 738, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 1343, Involuntary context switches 41 ---- mysqlimport --lock-tables --debug-info --use-threads=8 test /tmp/test/*.txt Records: 2076063 Deleted: 0 Skipped: 0 Warnings: 0 User time 0.02, System time 0.09 Maximum resident set size 3884, Integral resident set size 0 Non-physical pagefaults 738, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 0 out 0, Signals 0 Voluntary context switches 1341, Involuntary context switches 30
+2
source

Like @ bill-karwin, I also use mydumper / myloader

In Ubuntu:

 sudo apt-get install mydumper

Anything else, follow these instructions: http://centminmod.com/mydumper.html

Then for backup:

 mydumper -h [host] -u [user] -p [pass] -P [port] -B [database] -c -C --regex '^(?!excluded_table_1|excluded_table_2)' -v 3

Then, to recover:

 myloader -d [dir_created_by_mydumper] -h [host] -u [user] -p [pass] -P [port] -o -C -v 3

Notes:

  • -C flag compresses the MySQL protocol, used only on external servers
  • -C uses gzip compression, it is recommended to leave it all the time
  • -v 3 gives verbose output (so you can see what is happening), the script will run faster if you don't turn it off.
  • --regex can be any valid regular expression.
  • create the ~/.my.cnf file and you no longer need the host, user, password, port.
  • mydumper --help and myloader --hel p will provide you with a complete list of options
+2
source

in fact, it seems that streaming is performed only when you specify several imported files (in several tables) - this does not help with one large file.

way to find out if this is really streaming processing to watch the SHOW PROCESSLIST output. here you can see that this really works, although, as already 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 | +-------+--------+------------------+------+---------+------+-----------+------------------------------------------------------------------------------------------------------+

- A detailed output is also output.

+1
source

By changing the source code, I think you should disable the --lock-tables option, see below:

  #ifdef HAVE_LIBPTHREAD if (opt_use_threads && !lock_tables) { pthread_t mainthread; /* Thread descriptor */ pthread_attr_t attr; /* Thread attributes */ pthread_attr_init(&attr); pthread_attr_setdetachstate(&attr, PTHREAD_CREATE_DETACHED); VOID(pthread_mutex_init(&counter_mutex, NULL)); VOID(pthread_cond_init(&count_threshhold, NULL)); for (counter= 0; *argv != NULL; argv++) /* Loop through tables */ { pthread_mutex_lock(&counter_mutex); while (counter == opt_use_threads) { struct timespec abstime; set_timespec(abstime, 3); pthread_cond_timedwait(&count_threshhold, &counter_mutex, &abstime); } /* Before exiting the lock we set ourselves up for the next thread */ counter++; pthread_mutex_unlock(&counter_mutex); /* now create the thread */ if (pthread_create(&mainthread, &attr, worker_thread, (void *)*argv) != 0) { pthread_mutex_lock(&counter_mutex); counter--; pthread_mutex_unlock(&counter_mutex); fprintf(stderr,"%s: Could not create thread\n", my_progname); } } /* We loop until we know that all children have cleaned up. */ pthread_mutex_lock(&counter_mutex); while (counter) { struct timespec abstime; set_timespec(abstime, 3); pthread_cond_timedwait(&count_threshhold, &counter_mutex, &abstime); } pthread_mutex_unlock(&counter_mutex); VOID(pthread_mutex_destroy(&counter_mutex)); VOID(pthread_cond_destroy(&count_threshhold)); pthread_attr_destroy(&attr); }
0
source

I used mydumper as an alternative to mysqldump / mysqlimport.

Mydumper creates logical backups in .sql format using multiple streams, and myloader can import them using multiple streams.

The only drawback is that you must create it yourself from the source, since there seems to be no binary package. I found a very simple way to build CentOS, though.

https://launchpad.net/mydumper

0
source

More articles:


All Articles