Update: MariaDB is now on github. I have created my own fork, and pushed my changes on to the selfTuningOptimizer branch.
In the previous blog post, I had stated that I will start coding the things discussed in it. I have set up a branch on launchpad and have pushed a commit which I will be discussing in this post.
I first needed to make a table in the mysql database, from where we would be reading constants and later updating them too. The sql commands for creating a table in the mysql database are written in the file scripts/mysql_system_tables.sql
. I have added a table named all_constants
which currently has only two columns const_name
and const_value
.
-- Tables for Self Tuning Cost Optimizer
CREATE TABLE IF NOT EXISTS all_constants (const_name varchar(64) NOT NULL, const_value double NOT NULL, PRIMARY KEY (const_name) ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Constants for optimizer';
-- Remember for later if all_constants table already existed
set @had_all_constants_table= @@warning_count != 0;
I also added the constants READ_TIME_FACTOR
and SCAN_TIME_FACTOR
in the all_constants
table. Here is the corresponding code from scripts/mysql_system_tables_data.sql
.
CREATE TEMPORARY TABLE tmp_all_constants LIKE all_constants;
INSERT INTO tmp_all_constants VALUES ('READ_TIME_FACTOR', 1.0);
INSERT INTO tmp_all_constants VALUES ('SCAN_TIME_FACTOR', 1.0);
INSERT INTO all_constants SELECT * FROM tmp_all_constants WHERE @had_all_constants_table=0;
DROP TABLE tmp_all_constants;
Next I needed to read these constants from the all_constants
table. The file sql/statistics.cc
has functions to update persistent statistical tables and to read from them. I have created two files sql/opt_costmodel.h
and sql/opt_costmodel.cc
which will have functions to read and update all_constants
table. I have created All_constants
class. Its methods allow us to read constants from the all_constants
table. I have still not written the code for updating the table. Let us have a look at the function read_constant_from_table
.
static double read_constant_from_table(THD *thd, const LEX_STRING const_name)
{
TABLE_LIST table_list;
Open_tables_backup open_tables_backup;
if (open_table(thd, &table_list, &open_tables_backup, FALSE))
{
thd->clear_error();
return 1.0;
}
All_constants all_constants(table_list.table, const_name);
all_constants.set_key_fields();
all_constants.read_const_value();
close_system_tables(thd, &open_tables_backup);
return all_constants.get_const_value();
}
open_table
is a helper function which opens the all_constants
table for reading/writing. set_key_fields
sets the value of const_name_field
, which is the primary key and read_const_value
searches for the constant having that name in all_constants
table and fills that value in const_value
variable.
void set_key_fields()
{
const_name_field->store(const_name.str, const_name.length, system_charset_info);
}
bool find_const()
{
uchar key[MAX_KEY_LENGTH];
key_copy(key, record[0], key_info, key_length);
return !all_constants_file->ha_index_read_idx_map(record[0], key_idx, key,
HA_WHOLE_KEY, HA_READ_KEY_EXACT);
}
void read_const_value()
{
if (find_const())
{
Field *const_field= all_constants_table->field[ALL_CONSTANTS_CONST_VALUE];
if(!const_field->is_null())
{
const_value= const_field->val_real();
}
}
}
So to read READ_TIME_FACTOR
from the all_constants
table, a call to read_constant_from_table is made with const_name
set as READ_TIME_FACTOR
. Similar is the case for SCAN_TIME_FACTOR
.
Next big thing was how to test the code which I had just written. ./mtr
was showing a lot of failed tests because I had added an extra table in the system database. So I decided to write a simple test of mine own in mysql-test/t/costmodel.test
.
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1);
SELECT * FROM t1;
DROP TABLE t1;
This test just creates a table with one field and runs a select query. To test my code, I added calls to get_read_time_factor
and get_scan_time_factor
in sql/sql_select.cc
. Then I ran ./mtr --gdb costmodel
to run the test-case with gdb. Using gdb I set up breakpoints on these functions and confirmed that the correct values were indeed being read.
Now that we have functions which will read the constants from the database, I need to figure out where all I need to call this functions. I made a list of places where handler::read_time()
and handler::scan_time()
were being called. The problem is that get_read_time_factor
and get_scan_time_factor
requires a pointer to current thread as an argument. I will tackle this problem in the coming week. Also I need an example query to add to my test case, which will take different execution plans on changing the values of READ_TIME_FACTOR
and SCAN_TIME_FACTOR
.
Suggestions/review on the code are welcome. Again, this is the link to the commit. I will be pushing to that branch regularly from now on.
blog comments powered by Disqus