| Bug #76480 | mysqlimport --use-threads doesn't use multiple threads | ||
|---|---|---|---|
| Submitted: | 25 Mar 2015 13:46 | Modified: | 30 Jun 2015 15:07 |
| Reporter: | Miguel Angel Nieto | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S3 (Non-critical) |
| Version: | 5.6.23, 5.5.44, 5.6.25, 5.7.8 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | mysqlimport, regression | ||
[25 Mar 2015 15:00]
Valeriy Kravchuk
That's because (even in 5.7.6) mysqlimport still uses this approach to threads:
...
#ifdef HAVE_LIBPTHREAD
#include <my_thread.h>
#endif
...
#ifdef HAVE_LIBPTHREAD
if (opt_use_threads && !lock_tables)
{
my_thread_t mainthread; /* Thread descriptor */
my_thread_attr_t attr; /* Thread attributes */
my_thread_attr_init(&attr);
pthread_attr_setdetachstate(&attr,
PTHREAD_CREATE_DETACHED);
native_mutex_init(&counter_mutex, NULL);
native_cond_init(&count_threshold);
for (counter= 0; *argv != NULL; argv++) /* Loop through tables */
{
...
and, surely, HAVE_LIBPTHREAD is not defined anywhere and option is just silently ignored. Moreover, defining it also does not let to compile successfully :)
Looks like we deal with the code that is not maintained for ages (since switching to cmake probably).
It works with 5.1 though:
[openxs@centos ~]$ dbs/p5.1/bin/mysqlimport -uroot --host=127.0.0.1 --port=3306 --use-threads=4 --verbose test /tmp/t*.txt
Connecting to 127.0.0.1
Connecting to 127.0.0.1
Connecting to 127.0.0.1
Connecting to 127.0.0.1
Selecting database test
Selecting database test
Selecting database test
Loading data from SERVER file: /tmp/t3.txt into t3
Loading data from SERVER file: /tmp/t1.txt into t1
Loading data from SERVER file: /tmp/t4.txt into t4
Selecting database test
Loading data from SERVER file: /tmp/t2.txt into t2
test.t4: Records: 65536 Deleted: 0 Skipped: 0 Warnings: 0
Disconnecting from 127.0.0.1
test.t1: Records: 65536 Deleted: 0 Skipped: 0 Warnings: 0
Disconnecting from 127.0.0.1
test.t3: Records: 65536 Deleted: 0 Skipped: 0 Warnings: 0
Disconnecting from 127.0.0.1
test.t2: Records: 65536 Deleted: 0 Skipped: 0 Warnings: 0
Disconnecting from 127.0.0.1
[openxs@centos ~]$ dbs/p5.1/bin/mysqlimport --version
dbs/p5.1/bin/mysqlimport Ver 3.7 Distrib 5.1.70, for unknown-linux-gnu (x86_64)
[openxs@centos ~]$
So, we have a regression bug here.
[25 Mar 2015 17:36]
MySQL Verification Team
Hello Miguel, Thank you for the report. Observed that 5.5, 5.6 and 5.7 are affected. Thanks, Umesh
[25 Mar 2015 17:40]
MySQL Verification Team
test results
Attachment: 76480_results.txt (text/plain), 6.92 KiB.
[30 Jun 2015 4:50]
Arun Kuruvila
Posted by developer: Approved on review board.
[30 Jun 2015 15:07]
Paul DuBois
Noted in 5.5.46, 5.6.27, 5.7.8, 5.8.0 changelogs. mysqlimport --use-threads did not actually use multiple threads.
Description: mysqlimport --use-threads doesn't use multiple threads. Even with multiple files and multiple tables to import, it always does it one by one. How to repeat: I have 4 similar tables: mysql> show create table test1\G *************************** 1. row *************************** Table: test1 Create Table: CREATE TABLE `test1` ( `i` int(11) DEFAULT NULL, `name` char(10) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10092391 DEFAULT CHARSET=latin1 mysql> mysql> show create table test2\G *************************** 1. row *************************** Table: test2 Create Table: CREATE TABLE `test2` ( `i` int(11) DEFAULT NULL, `name` char(10) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show create table test3\G *************************** 1. row *************************** Table: test3 Create Table: CREATE TABLE `test3` ( `i` int(11) DEFAULT NULL, `name` char(10) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 mysql> show create table test4\G *************************** 1. row *************************** Table: test4 Create Table: CREATE TABLE `test4` ( `i` int(11) DEFAULT NULL, `name` char(10) DEFAULT NULL, `id` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 and I have 4 different dumps created with mysqldump --tab: # ls /var/lib/mysql/dest/*.txt /var/lib/mysql/dest/test1.txt /var/lib/mysql/dest/test2.txt /var/lib/mysql/dest/test3.txt /var/lib/mysql/dest/test4.txt # head /var/lib/mysql/dest/test1.txt 1 miguel 1 miguel 1 miguel 1 miguel 1 miguel 1 miguel 1 miguel 1 miguel 1 miguel 1 miguel I am not able to import those 4 files in parallel: [root@centos7 ~]# mysqlimport --debug-info --verbose --use-threads=20 --local test /var/lib/mysql/dest/*.txt Connecting to localhost Selecting database test Loading data from LOCAL file: /var/lib/mysql/dest/test1.txt into test1 mysql> show processlist; +----+------+-----------------+------+---------+------+-------------+-------------------------------------------------------------------------------------------+-----------+---------------+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | +----+------+-----------------+------+---------+------+-------------+-------------------------------------------------------------------------------------------+-----------+---------------+ | 5 | root | localhost:59587 | NULL | Sleep | 137 | | NULL | 0 | 0 | | 17 | root | localhost | test | Sleep | 2057 | | NULL | 0 | 0 | | 32 | root | localhost | NULL | Query | 0 | init | show processlist | 0 | 0 | | 47 | root | localhost | test | Query | 2 | System lock | LOAD DATA LOCAL INFILE '/var/lib/mysql/dest/test1.txt' INTO TABLE `test1` IGNORE 0 LINES | 0 | 0 | +----+------+-----------------+------+---------+------+-------------+-------------------------------------------------------------------------------------------+-----------+---------------+