Skip to content

ddl: speed up the operation of "create table"#6861

Merged
coocood merged 9 commits intopingcap:masterfrom
zimulala:create-table
Jun 25, 2018
Merged

ddl: speed up the operation of "create table"#6861
coocood merged 9 commits intopingcap:masterfrom
zimulala:create-table

Conversation

@zimulala
Copy link
Contributor

What have you changed? (mandatory)

Handle table split operations asynchronously.

What are the type of the changes (mandatory)?

  • Improvement

Does this PR affect documentation (docs/docs-cn) update? (optional)

No.

Benchmark result if necessary (optional)

Using TiKV
The test code as follows:

start := time.Now()
	for j := 0; j < 1000; j++ {
		sql = fmt.Sprintf("create table db%d.t%d(a int, b int, c int, d varchar(255), e varchar(255))", i, j)
		if _, err = db.Exec(sql); err != nil {
			log.Errorf("create table err %v", err)
		}
	}
	log.Infof("exec sqls take time %v", time.Since(start))

use the original code, the result as follows:
2018/05/18 17:37:16 tidb_create.go:35: [info] exec sql use db0
2018/05/18 17:41:09 tidb_create.go:44: [info] exec sqls take time 3m53.493631103s
2018/05/18 17:41:09 tidb_create.go:35: [info] exec sql use db1
2018/05/18 17:45:03 tidb_create.go:44: [info] exec sqls take time 3m53.914944715s
2018/05/18 17:45:03 tidb_create.go:35: [info] exec sql use db2
2018/05/18 17:48:57 tidb_create.go:44: [info] exec sqls take time 3m53.528751212s
2018/05/18 17:48:57 tidb_create.go:35: [info] exec sql use db3
2018/05/18 17:52:56 tidb_create.go:44: [info] exec sqls take time 3m59.223324563s
2018/05/18 17:52:56 tidb_create.go:35: [info] exec sql use db4
2018/05/18 17:56:56 tidb_create.go:44: [info] exec sqls take time 3m59.867498659s
2018/05/18 17:56:56 tidb_create.go:35: [info] exec sql use db5
2018/05/18 18:01:01 tidb_create.go:44: [info] exec sqls take time 4m5.327908179s
2018/05/18 18:01:01 tidb_create.go:35: [info] exec sql use db6
2018/05/18 18:05:09 tidb_create.go:44: [info] exec sqls take time 4m7.886515515s
2018/05/18 18:05:09 tidb_create.go:35: [info] exec sql use db7
2018/05/18 18:09:15 tidb_create.go:44: [info] exec sqls take time 4m5.60381973s
2018/05/18 18:09:15 tidb_create.go:35: [info] exec sql use db8
2018/05/18 18:13:32 tidb_create.go:44: [info] exec sqls take time 4m16.640030126s

use the new code, the result as follows:
2018/05/18 18:42:03 tidb_create.go:35: [info] exec sql use db0
2018/05/18 18:44:08 tidb_create.go:44: [info] exec sqls take time 2m4.89636175s
2018/05/18 18:44:08 tidb_create.go:35: [info] exec sql use db1
2018/05/18 18:46:14 tidb_create.go:44: [info] exec sqls take time 2m5.840172046s
2018/05/18 18:46:14 tidb_create.go:35: [info] exec sql use db2
2018/05/18 18:48:22 tidb_create.go:44: [info] exec sqls take time 2m7.847079946s
2018/05/18 18:48:22 tidb_create.go:35: [info] exec sql use db3
2018/05/18 18:50:32 tidb_create.go:44: [info] exec sqls take time 2m9.464254105s
2018/05/18 18:50:32 tidb_create.go:35: [info] exec sql use db4
2018/05/18 18:52:42 tidb_create.go:44: [info] exec sqls take time 2m10.56319663s
2018/05/18 18:52:43 tidb_create.go:35: [info] exec sql use db5
2018/05/18 18:54:57 tidb_create.go:44: [info] exec sqls take time 2m14.954220944s
2018/05/18 18:54:58 tidb_create.go:35: [info] exec sql use db6
2018/05/18 18:57:14 tidb_create.go:44: [info] exec sqls take time 2m16.526956612s
2018/05/18 18:57:14 tidb_create.go:35: [info] exec sql use db7
2018/05/18 18:59:33 tidb_create.go:44: [info] exec sqls take time 2m18.566134241s
2018/05/18 18:59:33 tidb_create.go:35: [info] exec sql use db8
2018/05/18 19:02:33 tidb_create.go:44: [info] exec sqls take time 3m0.43806444s

@jackysp
Copy link
Contributor

jackysp commented Jun 19, 2018

/run-all-tests

}

func splitTableRegion(store kv.Storage, tableID int64) error {
type splitableStore interface {
Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Why add it?

Copy link
Contributor Author

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

I don't add it, I only move it from splitTableRegion to here.

Copy link
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

But it still appears in splitTableRegion.

Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Should we remove the code at line 135~137?

ddl/table.go Outdated
tableStartKey := tablecodec.GenTablePrefix(tableID)
if err := s.SplitRegion(tableStartKey); err != nil {
return errors.Trace(err)
// It will be automatically splitting by TiKV later.
Copy link
Member

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

s/splitting/splited/

@zimulala
Copy link
Contributor Author

PTAL @lamxTyler @shenli

@shenli
Copy link
Member

shenli commented Jun 22, 2018

LGTM

@shenli shenli added the status/LGT1 Indicates that a PR has LGTM 1. label Jun 22, 2018
@shenli
Copy link
Member

shenli commented Jun 22, 2018

@winkyao @lamxTyler PTAL

Copy link
Contributor

@alivxxx alivxxx left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@alivxxx alivxxx added status/LGT2 Indicates that a PR has LGTM 2. and removed status/LGT1 Indicates that a PR has LGTM 1. labels Jun 22, 2018
Copy link
Member

@coocood coocood left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

LGTM

@coocood coocood added status/LGT3 The PR has already had 3 LGTM. and removed status/LGT2 Indicates that a PR has LGTM 2. labels Jun 25, 2018
@coocood coocood merged commit 6edbf58 into pingcap:master Jun 25, 2018
@zimulala zimulala deleted the create-table branch June 26, 2018 12:59
@you06 you06 added the sig/sql-infra SIG: SQL Infra label Mar 4, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

sig/sql-infra SIG: SQL Infra status/LGT3 The PR has already had 3 LGTM.

Projects

None yet

Development

Successfully merging this pull request may close these issues.

7 participants