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
125
126
127
128
129
130
131
132
133
134
|
--source include/force_myisam_default.inc
--source include/have_myisam.inc
#
# 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_myisam (
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=MyISAM;
--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_myisam
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_myisam;
# Run the query to see cost estimates when run with default cost constants
let query_myisam= SELECT * FROM t1_myisam;
--echo "Explain with cost estimate against MyISAM"
--skip_if_hypergraph # Depends on the query plan.
eval EXPLAIN FORMAT=JSON $query_myisam;
#
# 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 query to 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_myisam;
# 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 MyISAM has
# the original cost estimates
#
--echo "Explain with cost estimate against MyISAM"
--skip_if_hypergraph # Depends on the query plan.
eval EXPLAIN FORMAT=JSON $query_myisam;
# 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_myisam;
|
