planner: Plan Binding Enhancement

[qw4990]

Enhancement

Binding Cache Enhancement

• correct the inappropriate behavior when binding cache miss: wait until load the binding from disk planner: wait until binding is loaded into memory if binding cache miss #51400
• introduce timeout when waiting for binding load: if timeout, output a warning and optimize the query without binding bindinfo: add timeout for loading binding from storage #51550
• a new metrics panel including cache memory usage, cache hit/miss, timeout, number of bindings planner: remove unused binding metrics #51665 planner: add more metrics for binding #51739 planner: output a warning instead of returning an error when creating fast binding on a incomplete hint #51781

Massive binding scenario testing (100,000 bindings)

• impact of loading massive bindings to workload (TiKV I/O, TiDB CPU/Mem, etc.)
• binding cache works correctly with such amount of bindings (the eviction mechanism, memory control, etc.)

A new doc to demo how to create necessary bindings through statement_summary

• update related documents planner: update SPM doc docs-cn#16770

Plan to Binding/Hint Enhancement (create binding from <plan-digest>)

• support leading hint
• support hash_join_build and hash_join_probe hint
• more tests to cover multi-way joins, CTEs, sub-queries, Union

Auto Capture Enhancement

• re-design the strategy, based on execution_times and plan_stability
• introduce a few variables to control its behavior: tidb_auto_capture_frequency, tidb_auto_capture_schema
• related doc updates
• need to add more test cases

Binding Management Enhancement

• a new syntax IMPORT BINDINGS FROM HISTORY PLAN DIGEST @p1[, @p2, @p3, ...] to support creating a batch of bindings
• a new syntax IMPORT BINDINGS FROM HISTORY AUTO SHOW | ACCEPT to support creating a batch of bindings automatically
• a new view mysql.binding (defined as mysql.bind_info JOIN information_schema.statement_summary) to help improve the usability of managing massive bindings
• a new column extras in mysql.bind_info to provide necessary Information to identify sub-optimal bindings
• related doc updates

[qw4990]

A Python script to generate massive bindings:

package main

import (
	"database/sql"
	"fmt"
	"math/rand"
	"sync"
	"time"

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

func createTable() {
	createTableStmt := `
	create table if not exists t (a0 int, a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, 
		key(a0), key(a1), key(a2), key(a3), key(a4), key(a5), key(a6), key(a7))`
	db := connectDB()
	_, err := db.Exec(createTableStmt)
	must(err)
}

func setThreshold(mb int) {
	db := connectDB()
	_, err := db.Exec(fmt.Sprintf("set global tidb_mem_quota_binding_cache=%d", mb*1024*1024))
	must(err)
	_, err = db.Exec("admin reload bindings")
	must(err)
}

func genQueryBindings() (queries, bindings []string) {
	for _, a0 := range []string{"=", ">", "<", ">=", "<="} {
		for _, a1 := range []string{"=", ">", "<", ">=", "<="} {
			for _, a2 := range []string{"=", ">", "<", ">=", "<="} {
				for _, a3 := range []string{"=", ">", "<", ">=", "<="} {
					for _, a4 := range []string{"=", ">", "<", ">=", "<="} {
						for _, a5 := range []string{"=", ">", "<", ">=", "<="} {
							for _, a6 := range []string{"=", ">", "<", ">=", "<="} {
								for _, a7 := range []string{"=", ">", "<", ">=", "<="} {
									query := fmt.Sprintf(`select 1 from t where a0%v1 and a1%v1 and a2%v1 and a3%v1 and a4%v1 and a5%v1 and a6%v1 and a7%v1`,
										a0, a1, a2, a3, a4, a5, a6, a7)
									queries = append(queries, query)
									bindings = append(bindings, fmt.Sprintf("create global binding using %s", query))
								}
							}
						}
					}
				}
			}
		}
	}
	return
}

func connectDB() *sql.DB {
	dsn := "root:@tcp(127.0.0.1:4000)/test"
	db, err := sql.Open("mysql", dsn)
	must(err)
	return db
}

func must(err error) {
	if err != nil {
		panic(err)
	}
}

func createBindings(bindings []string) {
	db := connectDB()
	for _, binding := range bindings {
		_, err := db.Exec(binding)
		must(err)
	}
}

func queryDB(queries []string) {
	db := connectDB()
	for {
		query := queries[rand.Intn(len(queries))]
		r, err := db.Query(query)
		must(err)
		for r.Next() {
			var i int
			must(r.Scan(&i))
		}
		must(r.Close())
		time.Sleep(time.Millisecond)
	}
}

func main() {
	createTable()
	//setThreshold(10) // no enough cache space if set

	N := 100000
	queries, bindings := genQueryBindings()
	queries = queries[:N]
	bindings = bindings[:N]

	// create N bindings
	createBindingWorkers := 10
	var wg sync.WaitGroup
	for i := 0; i < createBindingWorkers; i++ {
		wg.Add(1)
		go func(id int) {
			defer wg.Done()
			createBindings(bindings[id*(N/createBindingWorkers) : (id+1)*(N/createBindingWorkers)])
		}(i)
	}
	wg.Wait()
	fmt.Println("create bindings done")

	// load all bindings periodically
	//go func() {
	//	for {
	//		db := connectDB()
	//		_, err := db.Exec("admin reload bindings")
	//		must(err)
	//		time.Sleep(time.Minute * 2)
	//	}
	//}()

	// query the database
	queryWorkers := 5
	for i := 0; i < queryWorkers; i++ {
		go func() {
			queryDB(queries)
		}()
	}
	time.Sleep(time.Minute * 60)
}

[coderplay]

Can we also include the future major items of SPM in this issue?

[qw4990]

Can we also include the future major items of SPM in this issue?

Thanks for the suggestion, but this issue only focuses on the fundamental binding enhancement, and some work items about the future SPM like evolution are still unclear. I'll create a new issue to track these future SPM items when they are clearer.

[qw4990]

Test binding cache with 100,000 bindings locally.

Binding Metrics work correctly:

Binding loading has no impact on query latency:

Reload all bindings during the test, query latency seems stable.

Limit the binding cache memory:

The binding memory usage and number of bindings in memory decrease.
Cache miss metrics goes up and queries are affected.