1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
|
#
# Test of the cost constants when restarting the server
#
# To get stable cost estimates, the test should only be run with
# 16K InnoDB page size.
#
# Test that changes to cost constants are used after restarting server
#
# Verify that the content of the two cost constants tables are as expected
SELECT cost_name,cost_value FROM mysql.server_cost;
SELECT engine_name,cost_name,cost_value FROM mysql.engine_cost;
#
# Create a test database that will be used for running queries
#
CREATE TABLE t0 (
i1 INTEGER
);
INSERT INTO t0 VALUE (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
# Create a table with 100 records each having size approximately 1000 bytes
CREATE TABLE t1_innodb (
pk INTEGER PRIMARY KEY,
i1 INTEGER NOT NULL,
c1 CHAR(250),
c2 CHAR(250),
c3 CHAR(250),
c4 CHAR(250),
INDEX i1_key (i1)
) ENGINE=InnoDB;
--echo # Since ANALYZE TABLE only reads a subset of the data, the statistics for
--echo # table t1 depends on the row order. And since the INSERT INTO ... SELECT
--echo # may be executed using different execution plans, we've added ORDER BY
--echo # to ensure that we rows has the same order every time. If not, the
--echo # estimated number of rows in EXPLAIN may change on different platforms.
INSERT INTO t1_innodb
SELECT a0.i1 + 10 * a1.i1, a0.i1, 'abc', 'def', 'ghi', 'jkl'
FROM t0 AS a0, t0 AS a1 ORDER BY a0.i1, a1.i1;
ANALYZE TABLE t1_innodb;
# Run the query to see cost estimates when run with default cost constants
let query_innodb= SELECT * FROM t1_innodb;
--echo "Explain with cost estimate against InnoDB"
--skip_if_hypergraph # Depends on the query plan.
eval EXPLAIN FORMAT=JSON $query_innodb;
#
# Update one cost constant in the server cost table and the two cost
# constants in the engine cost table (the reason for updating both is that
# after a restart the statistics about whether pages are in memory or on disk
# may vary). The new value is double of the default value.
#
UPDATE mysql.server_cost
SET cost_value=0.4
WHERE cost_name="row_evaluate_cost";
UPDATE mysql.engine_cost
SET cost_value=2.0
WHERE cost_name="memory_block_read_cost";
UPDATE mysql.engine_cost
SET cost_value=2.0
WHERE cost_name="io_block_read_cost";
--echo "Restarting MySQL server"
--source include/restart_mysqld.inc
--echo "MySQL restarted"
SELECT cost_name, cost_value FROM mysql.server_cost;
SELECT engine_name, cost_name, cost_value FROM mysql.engine_cost;
#
# Run the two queries and validate that the cost estimate has doubled
#
--echo "Explain with cost estimate against MyISAM"
--skip_if_hypergraph # Depends on the query plan.
eval EXPLAIN FORMAT=JSON $query_innodb;
# Reset the cost constants
UPDATE mysql.server_cost
SET cost_value=DEFAULT;
UPDATE mysql.engine_cost
SET cost_value=DEFAULT;
#
# Test that adding engine specific cost constants does not influence
# other engines.
#
INSERT INTO mysql.engine_cost VALUES
("InnoDB", 0, "memory_block_read_cost", 4.0, CURRENT_TIMESTAMP, DEFAULT, DEFAULT);
INSERT INTO mysql.engine_cost VALUES
("InnoDB", 0, "io_block_read_cost", 4.0, CURRENT_TIMESTAMP, DEFAULT, DEFAULT);
--echo "Restarting MySQL server"
--source include/restart_mysqld.inc
--echo "MySQL restarted"
SELECT cost_name, cost_value FROM mysql.server_cost;
SELECT engine_name, cost_name, cost_value FROM mysql.engine_cost;
#
# Run the query and validate that the query against InnoDB has
# read cost four times the original
#
--echo "Explain with cost estimate against InnoDB"
--skip_if_hypergraph # Depends on the query plan.
eval EXPLAIN FORMAT=JSON $query_innodb;
# Delete the added entry for InnoDB
DELETE FROM mysql.engine_cost
WHERE engine_name NOT LIKE "default";
--echo "Restarting MySQL server"
--source include/restart_mysqld.inc
DROP TABLE t0, t1_innodb;
|
