WL#10128: Add defaults column to optimizer cost tables
Affects: Server-8.0 — Status: Complete
WL#7276 added two tables, server_cost and engine_cost, to the mysql schema. These tables can be used to tune the cost constants that are used for the optimizer cost model. However, there is no way, except by reading code, to find the default value for these cost constants. This worklog will add a generated column that will show the default values for the constants defined in the two tables. Since a generated column is read-only, one avoids any confusion around what it would imply if such a column was updated. User Documentation ================== * http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html * http://dev.mysql.com/doc/refman/8.0/en/cost-model.html
F-1 Table mysql.server_cost will contain a column, default_value, which will define a column that will contain the default value for each cost constant. F-2 Table mysql.engine_cost will contain a column, default_value, which will define a column that will contain the default value for each cost constant. F-3 It should not be possible to update these columns for any user (including root) F-4 For databases created with earlier versions, the columns will be added to the tables upon upgrade.
Generated column ---------------- A generated column, default_value, will be added to the two tables, server_cost and engine cost. The column definition will be of the form: default_value FLOAT GENERATED ALWAYS AS (CASE cost_name WHEN
THEN WHEN THEN ... ELSE NULL END) VIRTUAL Such definitions will be added to the SQL script that creates the MySQL system tables. Upgrade ------- If default values for cost constants are changed in the code, the column definition for the corresponding cost tables will need to be updated. In addition to updating the script for creating the tables, the SQL script that is run during upgrade will have to modify the definitions of these columns. The update script will handle both tables that does not already contain this column, and tables that do contain it. Comments in the code will be added to remind developers to change these scripts if they change the default value for a cost constant. Testing ------- There already exists MTR tests that verifies the content of the cost tables. These results files for these tests will be updated to reflect the addition of the new column. An MTR test case will be added to verify that the default_value column is not null for any row of the cost tables. An MTR test case will be added to verify that the default_value column cannot be updated since it is a generated column. An MTR to verify the upgrade script will also be added. Documentation ------------- Documentation should no longer specify defaults, but refer to the contents of the two tables.
scripts/mysql_system_tables.sql: ================================ Table definitions for server_cost and engine_cost are updated to included to define the default values for all current cost constants: CREATE TABLE IF NOT EXISTS server_cost ( cost_name VARCHAR(64) NOT NULL, cost_value FLOAT DEFAULT NULL, last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, comment VARCHAR(1024) DEFAULT NULL, default_value FLOAT GENERATED ALWAYS AS (CASE cost_name WHEN 'disk_temptable_create_cost' THEN 20.0 WHEN 'disk_temptable_row_cost' THEN 0.5 WHEN 'key_compare_cost' THEN 0.05 WHEN 'memory_temptable_create_cost' THEN 1.0 WHEN 'memory_temptable_row_cost' THEN 0.1 WHEN 'row_evaluate_cost' THEN 0.1 ELSE NULL END) VIRTUAL, PRIMARY KEY (cost_name) ) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci STATS_PERSISTENT=0; CREATE TABLE IF NOT EXISTS engine_cost ( engine_name VARCHAR(64) NOT NULL, device_type INTEGER NOT NULL, cost_name VARCHAR(64) NOT NULL, cost_value FLOAT DEFAULT NULL, last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, comment VARCHAR(1024) DEFAULT NULL, default_value FLOAT GENERATED ALWAYS AS (CASE cost_name WHEN 'io_block_read_cost' THEN 1.0 WHEN 'memory_block_read_cost' THEN 0.25 ELSE NULL END) VIRTUAL, PRIMARY KEY (cost_name, engine_name, device_type) ) ENGINE=InnoDB CHARACTER SET=utf8 COLLATE=utf8_general_ci STATS_PERSISTENT=0; INSERT statements for these tables have been simplified by not specifying columns for which default column values should be used. scripts/mysql_system_tables_fix.sql: ==================================== Add SQL code that checks if default_value columns exists and adds/modifies column definition depending on the outcome. (Column definition is the same as above) mysql-test/t/opt_costmodel_tables.test: ======================================= In addition to updating existing test cases to reflect the new columns, add a test case that verifies that all default values are non-NULL. mysql-test/t/opt_costmodel_upgrade.test: ======================================== Add a test that verifies that column values are as expected after upgrade. sql/opt_costconstantcache.cc: ============================= A fix is needed to be able to resolve generated columns when opening tables in a separate THD.
Copyright (c) 2000, 2021, Oracle Corporation and/or its affiliates. All rights reserved.