slow down when creating index.

[DavisLinDB]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

Create 64 connections to insert data to a single table, 4000~5000 records per seconds.
The table has 0.6 billion rows.
Then creating index for the table and insert data at the same time.
Before create index, 4.5k qps. When creating index, only 825 qps.
Any way to increase QPS when creating index?

The index creation time for 629663920 rows is 19 hours 43 min 54.23 sec, any way to improve?
MySQL [test]> create index IDX on testdata (adddate,agold,bgold);
Query OK, 0 rows affected (19 hours 43 min 54.23 sec)

2. What did you expect to see?
   Faster.

3. What did you see instead?
   When creating index, only 825 qps.
   Create an index need 20 hours.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   Release Version: v1.0.4-1-ga05e014
   Git Commit Hash: a05e014
   Git Commit Branch: release-1.0
   UTC Build Time: 2017-12-11 03:07:11

[DavisLinDB]

6 servers to deploy TiDB cluster.
.1 E5620 CPU, 32G RAM, 480G * 4 SSD, TiKV + PD
.2 E5620 CPU, 32G RAM, 480G * 4 SSD, TiKV + PD
.3 E5620 CPU, 32G RAM, 480G * 4 SSD, TiKV + PD
.4 E5620 CPU, 32G RAM, 480G * 4 SSD, TiKV
.5 E5620 CPU, 32G RAM, SAS HDD, TiDB
.6 E5620 CPU, 32G RAM, SAS HDD, TiDB
each running CentOS 7.

table schema:

CREATE TABLE `testdata` (
  `nid` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(36) NOT NULL,
  `hid` int(11) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  `agold` decimal(18,6) DEFAULT NULL,
  `bgold` decimal(18,6) DEFAULT NULL,
  `adddate` datetime DEFAULT NULL,
  PRIMARY KEY (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

create index statement:
create index IDX on testdata (adddate,agold,bgold);

[zimulala]

@DavisLinDB Data in the field of nid is discrete?

[DavisLinDB]

@zimulala

using AUTO_INCREMENT

[zimulala]

@DavisLinDB Do you delete the data randomly?

[DavisLinDB]

@zimulala

No delete, just creating index and insert data.

Insert statement:
insert Into testdata (id, HId, cid, agold, bgold, adddate) select UUID() as uuid, cast(rand()*100 as SIGNED) as Hid,cast(rand()*100000 as SIGNED) as cid,cast(rand()*10000000 as SIGNED ) as agold,cast(rand()*10000000 as SIGNED ) as bgold, DATE_ADD("2016-01-01", INTERVAL RAND() * 364 DAY) as adddate

[zimulala]

@DavisLinDB
Whether you insert data while adding this index？

[DavisLinDB]

@zimulala

at first hour, insert data and adding index at the same time. Got 825 qps.
Then I stop insert data just adding index, adding index still need 19 hours to complete.

[zimulala]

@DavisLinDB
Could you send me your tidb-server's log to lixia@pingcap.com.

[DavisLinDB]

@zimulala

Sent. Please check mail.

[shenli]

@DavisLinDB Got it. Thanks!

[zimulala]

@DavisLinDB
You have two questions as following:
The QPS is falling. We are concurrent processing add index in our background. So it will affect the performance of related TiKV. We can adjust the degree of concurrency(Relate #5371 ) or add more TiKV to reduce the effects.
On the slow addition of the index, we will handle it later. Relate #5192.
Thanks for your report!

[DavisLinDB]

@zimulala

Any parameters or sql_hint to set adding index low priority let QPS stay normal performance? Or update network speed from gigabit to 10-gigabit can improve?

[zimulala]

@DavisLinDB
I will set the low priority for the operation of adding the index later. Now you can add the priority to your insert statement to fix it. The priority syntax is compatible with MySQL https://dev.mysql.com/doc/refman/5.7/en/insert.html.
Besides, the network is only one aspect. And we can see your metric graph, the QPS of these two TiDB are falling, so the network is not the main reason. Adjusting the priority is more useful.

[winkyao]

This issue was fixed by PR: #5976 , you can try the newest master branch. Appreciate to get your feedback :) @DavisLinDB