MySQL Bugs: #76480: mysqlimport --use-threads doesn't use multiple threads

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:	None
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

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

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.

Hello Miguel,

Thank you for the report.
Observed that 5.5, 5.6 and 5.7 are affected.

Thanks,
Umesh

test results

Attachment: 76480_results.txt (text/plain), 6.92 KiB.

Posted by developer:
 
Approved on review board.

Noted in 5.5.46, 5.6.27, 5.7.8, 5.8.0 changelogs.

mysqlimport --use-threads did not actually use multiple threads.