prepared statement doesn't work on 'insert into t1 select from t2 where id = ?'

[tiancaiamao]

Enhancement

The query is 'insert into t1 select from t2 where id = ?', it inserts a single line each time, repeat 1000 times with 200 concurrency.

The plan cache doesn't take effect, and it seems to be very inefficient.
Even worse, I suspect in this case it's even slower than the non-prepared statement.
As you can see, most of the time are spend on rebuild of the plan,

[tiancaiamao]

After changing to non-prepared statement:

[Reminiscent]

/assign

[Reminiscent]

In the latest master, the plan cache can be hit. Please confirm the issue @tiancaiamao . Note: Enable the config prepared-plan-cache.

mysql> drop table if exists t1, t2;
Query OK, 0 rows affected (0.04 sec)

mysql> create table t1(id int);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t2(id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t2 values(1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> prepare stmt from 'insert into t1 select * from t2 where id = ?';
Query OK, 0 rows affected (0.00 sec)

mysql> set @p = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @p;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      0 |
+------------------------+
1 row in set (0.00 sec)

mysql> execute stmt using @p;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select @@last_plan_from_cache;
+------------------------+
| @@last_plan_from_cache |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

[tiancaiamao]

@Reminiscent OK, I can confirm those steps and get the same result using the latest master.
But the flamegraph still not seems correct?

pprof.tidb-server.samples.cpu.036.pb.gz

[tiancaiamao]

You can try this one to test it on a local tidb:

package main

import (
	"context"
	"database/sql"
	"flag"
	"fmt"
	"log"
	"math/rand"
	"sync"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

var (
	host     = flag.String("h", "127.0.0.1", "host")
	port     = flag.Int("P", 4000, "port")
	user     = flag.String("u", "root", "username")
	password = flag.String("p", "", "password")
)

func varchar(size int) []byte {
	const str = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
	res := make([]byte, 0, size)
	for i := 0; i < size; i++ {
		res = append(res, str[rand.Intn(len(str))])
	}
	return res
}

func bigint(from, to int) int {
	return from + rand.Intn(to-from)
}

func openDB() (*sql.DB, error) {
	dbDSN := fmt.Sprintf("%s:%s@tcp(%s:%d)/test", *user, *password, *host, *port)
	db, err := sql.Open("mysql", dbDSN)
	if err != nil {
		return nil, err
	}
	return db, nil
}

func mustExec(db *sql.DB, query string, args ...interface{}) {
	_, err := db.Exec(query, args...)
	if err != nil {
		log.Fatal(err)
	}
}

func assert(err error) {
	if err != nil {
		log.Fatal(err)
	}
}	

func main() {
	db, err := openDB()
	if err != nil {
		log.Fatal(err)
	}

	mustExec(db, `use test`)

// 	// Create Table
	mustExec(db, `CREATE TABLE test (
actualid bigint(20) NOT NULL,
topid bigint(20) DEFAULT NULL,
planno varchar(64) DEFAULT NULL,
c2 varchar(64) DEFAULT NULL,
c3 varchar(64) DEFAULT NULL,
pad1 varchar(64) DEFAULT NULL,
pad2 varchar(64) DEFAULT NULL,
pad3 varchar(64) DEFAULT NULL,
primary key (actualid))`)

	mustExec(db, `set @@tidb_enable_global_temporary_table = 1`)
	mustExec(db, `CREATE GLOBAL TEMPORARY TABLE tmp (
actualid bigint(20) DEFAULT NULL,
topid bigint(20) DEFAULT NULL,
planno varchar(64) DEFAULT NULL,
c2 varchar(64) DEFAULT NULL,
c3 varchar(64) DEFAULT NULL,
pad1 varchar(64) DEFAULT NULL,
pad2 varchar(64) DEFAULT NULL,
pad3 varchar(64) DEFAULT NULL,
KEY topid (topid)
) ON COMMIT DELETE ROWS`)

	const md5 = "42fc97fcbf28d277721e32b9ced8f2ca"
	const ROWS = 1000
	for i := 0; i < ROWS; i++ {
		mustExec(db, "INSERT INTO test VALUES(?, ?, ?, ?, ?, ?, ?, ?)",
			i,
			// bigint(10000, 20000),
			bigint(20000, 30000),
			varchar(50),
			varchar(50),
			md5,
			md5,
			md5,
			md5)
	}

	var wg sync.WaitGroup
	wg.Add(10)

	now := time.Now()
	fmt.Println("benchmark start ...")

	// 10 sessions run "insert in to select" concurrently.
	for i := 0; i < 100; i++ {
		go func() {
			conn, err := db.Conn(context.Background())
			assert(err)

			defer wg.Done()
			defer conn.Close()

			_, err = conn.ExecContext(context.Background(), "use test")
			assert(err)


			_, err = conn.ExecContext(context.Background(), `set @@tmp_table_size = 1000000000`)
			assert(err)

			stmt, err := conn.PrepareContext(context.Background(), `insert into tmp select * from test where actualid = ?`)
			assert(err)


			time.Sleep(time.Duration(rand.Intn(2000)) * time.Millisecond)

			for {

				for x:=0; x<1000; x++ {
					_, err := stmt.Exec(x)
					assert(err)
				}
				
				// start := time.Now()

				// _, err = conn.ExecContext(context.Background(), "insert into tmp select * from test")
				// assert(err)

				// dur := time.Since(start)
				// fmt.Println("finish takes ===", dur)
			}
		}()
	}

	wg.Wait()
	fmt.Println("benchmark end ...", time.Since(now))
}

[tiancaiamao]

If I comment this condition !stmtCtx.OptimDependOnMutableConst in the line

tidb/planner/core/common_plans.go

Line 483 in 8219011

if !isTableDual && prepared.UseCache && !stmtCtx.OptimDependOnMutableConst {

The prepared plan cache can be used.