8000 WL#16548 Option and option tracker for optimizers · mysql/mysql-server@ea6b92c · GitHub
[go: up one dir, main page]
Skip to content

Commit ea6b92c

Browse files
mbremykdahlerlend
mbremyk
authored and
dahlerlend
committed
WL#16548 Option and option tracker for optimizers
New options in performance_schema.mysql_option: - Traditional Optimizer, OPTION_ENABLED=TRUE - Hypergraph Optimizer, OPTION_ENABLED=WITH_HYPERGRAPH_OPTIMIZER compile flag New usage entries in mysql_option.option_usage: - Traditional Optimizer - Hypergraph Optimizer Change-Id: I9700d8f5a9a03f97635bc2569f110d330ccc0a9d
1 parent 591ff50 commit ea6b92c

File tree

7 files changed

+212
-5
lines changed

7 files changed

+212
-5
lines changed

‎mysql-test/include/excludenoskip.list

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -293,6 +293,11 @@ not_partial_revokes.inc
293293
have_hypergraph.inc
294294
not_hypergraph.inc
295295

296+
# Must be skipped when the server is built without the Hypergraph Optimizer.
297+
# This is different to not_hypergraph in that not_hypergraph skips if the
298+
# Hypergraph is OFF, while this skips if the Hypergraph is unavailable.
299+
not_built_with_hypergraph.inc
300+
296301
# 30. Keyring_file component - can be skipped based on cmake configuration
297302
have_component_keyring_file.inc
298303

‎mysql-test/include/not_built_with_hypergraph.inc

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,17 @@
1+
# include/not_built_with_hypergraph.inc
2+
#
3+
# Skip the test if the server is built with the Hypergraph Optimizer.
4+
# This is different from not_hypergraph, as we here check if the Hypergraph
5+
# Optimizer is available at all, not just that it is off.
6+
#
7+
8+
--disable_query_log
9+
--error 0, ER_HYPERGRAPH_NOT_SUPPORTED_YET
10+
SET @@session.optimizer_switch = 'hypergraph_optimizer=on',
11+
@@global.optimizer_switch = 'hypergraph_optimizer=on';
12+
if (`SELECT FIND_IN_SET('hypergraph_optimizer=on', @@optimizer_switch)`) {
13+
SET SESSION optimizer_switch=DEFAULT;
14+
SET GLOBAL optimizer_switch=DEFAULT;
15+
--skip Test requires binaries built with WITH_HYPERGRAPH_OPTIMIZER=OFF
16+
}
17+
--enable_query_log

‎sql/CMakeLists.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -458,6 +458,7 @@ SET(SQL_SHARED_SOURCES
458458
opt_explain_traditional.cc
459459
opt_explain_json.cc
460460
opt_hints.cc
461+
opt_option_usage.cc
461462
opt_statistics.cc
462463
opt_sum.cc
463464
opt_trace.cc

‎sql/mysqld.cc

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -813,9 +813,10 @@ MySQL clients support the protocol:
813813
#include "sql/mdl.h"
814814
#include "sql/mdl_context_backup.h" // mdl_context_backup_manager
815815
#include "sql/mysqld_daemon.h"
816-
#include "sql/mysqld_thd_manager.h" // Global_THD_manager
817-
#include "sql/opt_costconstantcache.h" // delete_optimizer_cost_module
818-
#include "sql/options_mysqld.h" // OPT_THREAD_CACHE_SIZE
816+
#include "sql/mysqld_thd_manager.h" // Global_THD_manager
817+
#include "sql/opt_costconstantcache.h" // delete_optimizer_cost_module
818+
#include "sql/opt_option_usage.h" // Option tracker for optimizer usage
819+
#include "sql/options_mysqld.h" // OPT_THREAD_CACHE_SIZE
819820
#include "sql/partitioning/partition_handler.h" // partitioning_init
820821
#include "sql/persisted_variable.h" // Persisted_variables_cache
821822
#include "sql/plugin_table.h"
@@ -2132,7 +2133,8 @@ static void server_component_init() {
21322133
srv_weak_option_option::init(
21332134
srv_registry, srv_registry_registration,
21342135
[&](SERVICE_TYPE(mysql_option_tracker_option) * opt) {
2135-
return 0 != opt->define("MySQL Server", "mysql_server", 1);
2136+
return 0 != opt->define("MySQL Server", "mysql_server", 1) ||
2137+
optimizer_options_usage_init(opt, srv_registry);
21362138
},
21372139
false);
21382140
}
@@ -2193,7 +2195,8 @@ static bool component_infrastructure_deinit(bool print_message) {
21932195
srv_weak_option_option::deinit(
21942196
srv_registry_no_lock, srv_registry_registration_no_lock,
21952197
[&](SERVICE_TYPE(mysql_option_tracker_option) * opt) {
2196-
return 0 != opt->undefine("MySQL Server");
2198+
return 0 != opt->undefine("MySQL Server") ||
2199+
optimizer_options_usage_deinit(opt, srv_registry_no_lock);
21972200
});
21982201
persistent_dynamic_loader_deinit();
21992202
bool retval = false;
@@ -12042,6 +12045,13 @@ SHOW_VAR status_vars[] = {
1204212045
reinterpret_cast<char *>(
1204312046
&Rpl_opt_tracker::m_opt_option_tracker_usage_replication_replica),
1204412047
SHOW_LONGLONG, SHOW_SCOPE_GLOBAL},
12048+
{"option_tracker_usage:Traditional Optimizer",
12049+
reinterpret_cast<char *>(
12050+
&option_tracker_traditional_optimizer_usage_count),
12051+
SHOW_LONGLONG, SHOW_SCOPE_GLOBAL},
12052+
{"option_tracker_usage:Hypergraph Optimizer",
12053+
reinterpret_cast<char *>(&option_tracker_hypergraph_optimizer_usage_count),
12054+
SHOW_LONGLONG, SHOW_SCOPE_GLOBAL},
1204512055
{NullS, NullS, SHOW_FUNC, SHOW_SCOPE_ALL}};
1204612056

1204712057
void add_terminator(vector<my_option> *options) {

‎sql/opt_option_usage.cc

Lines changed: 124 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,124 @@
1+
/* Copyright (c) 2025, Oracle and/or its affiliates.
2+
3+
This program is free software; you can redistribute it and/or modify
4+
it under the terms of the GNU General Public License, version 2.0,
5+
as published by the Free Software Foundation.
6+
7+
This program is designed to work with certain software (including
8+
but not limited to OpenSSL) that is licensed under separate terms,
9+
as designated in a particular file or component or in included license
10+
documentation. The authors of MySQL hereby grant you an additional
11+
permission to link the program and your derivative works with the
12+
separately licensed software that they have either included with
13+
the program or referenced in the documentation.
14+
15+
This program is distributed in the hope that it will be useful,
16+
but WITHOUT ANY WARRANTY; without even the implied warranty of
17+
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18+
GNU General Public License, version 2.0, for more details.
19+
20+
You should have received a copy of the GNU General Public License
21+
along with this program; if not, write to the Free Software
22+
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
23+
24+
#include "sql/opt_option_usage.h"
25+
26+
#include <string>
27+
#include "mysql/components/library_mysys/option_tracker_usage.h"
28+
29+
static const std::string container_mysql_server_name("mysql_server"),
30+
traditional_optimizer_option_name("Traditional Optimizer"),
31+
hypergraph_optimizer_option_name("Hypergraph Optimizer");
32+
33+
unsigned long long option_tracker_traditional_optimizer_usage_count = 0;
34+
unsigned long long option_tracker_hypergraph_optimizer_usage_count = 0;
35+
36+
static bool set_option_tracker_traditional_optimizer_usage_count(
37+
unsigned long long new_value) {
38+
option_tracker_traditional_optimizer_usage_count = new_value;
39+
return false;
40+
}
41+
42+
static bool set_option_tracker_hypergraph_optimizer_usage_count(
43+
unsigned long long new_value) {
44+
option_tracker_hypergraph_optimizer_usage_count = new_value;
45+
return false;
46+
}
47+
48+
static bool traditional_optimizer_option_callback_define_failed = false;
49+
static bool hypergraph_optimizer_option_callback_define_failed = false;
50+
51+
bool optimizer_options_usage_init(SERVICE_TYPE(mysql_option_tracker_option) *
52+
opt,
53+
SERVICE_TYPE(registry) * srv_registry) {
54+
bool traditional_err = false, hypergraph_err = false;
55+
#ifdef WITH_HYPERGRAPH_OPTIMIZER
56+
bool with_hypergraph_optimizer = true;
57+
#else
58+
bool with_hypergraph_optimizer = false;
59+
#endif
60+
unsigned long long temp_usage_counter = 0;
61+
// Traditional Optimizer option.
62+
// Option definition
63+
traditional_err |= opt->define(traditional_optimizer_option_name.c_str(),
64+
container_mysql_server_name.c_str(), true);
65+
66+
// Fetch usage data from database
67+
traditional_err |=
68+
option_usage_read_counter(traditional_optimizer_option_name.c_str(),
69+
&temp_usage_counter, srv_registry);
70+
if (!traditional_err) {
71+
option_tracker_traditional_optimizer_usage_count = temp_usage_counter;
72+
}
73+
temp_usage_counter = 0;
74+
75+
// Register callback to update usage data
76+
traditional_err |=
77+
(traditional_optimizer_option_callback_define_failed =
78+
option_usage_register_callback(
79+
traditional_optimizer_option_name.c_str(),
80+
set_option_tracker_traditional_optimizer_usage_count,
81+
srv_registry));
82+
83+
// Hypergraph Optimizer option
84+
// Option definition
85+
hypergraph_err |= opt->define(hypergraph_optimizer_option_name.c_str(),
86+
container_mysql_server_name.c_str(),
87+
with_hypergraph_optimizer);
88+
89+
// Fetch usage data from database
90+
hypergraph_err |=
91+
option_usage_read_counter(hypergraph_optimizer_option_name.c_str(),
92+
&temp_usage_counter, srv_registry);
93+
if (!hypergraph_err) {
94+
option_tracker_hypergraph_optimizer_usage_count = temp_usage_counter;
95+
}
96+
temp_usage_counter = 0;
97+
98+
// Register callback to update usage data
99+
hypergraph_err |=
100+
(hypergraph_optimizer_option_callback_define_failed =
101+
option_usage_register_callback(
102+
hypergraph_optimizer_option_name.c_str(),
103+
set_option_tracker_hypergraph_optimizer_usage_count,
104+
srv_registry));
105+
return traditional_err || hypergraph_err;
106+
}
107+
108+
bool optimizer_options_usage_deinit(SERVICE_TYPE(mysql_option_tracker_option) *
109+
opt,
110+
SERVICE_TYPE(registry) * srv_registry) {
111+
bool err = false;
112+
err |=
113+
!traditional_optimizer_option_callback_define_failed &&
114+
option_usage_unregister_callback(
115+
traditional_optimizer_option_name.c_str(),
116+
set_option_tracker_traditional_optimizer_usage_count, srv_registry);
117+
err |= opt->undefine(traditional_optimizer_option_name.c_str());
118+
err |= !hypergraph_optimizer_option_callback_define_failed &&
119+
option_usage_unregister_callback(
120+
hypergraph_optimizer_option_name.c_str(),
121+
set_option_tracker_hypergraph_optimizer_usage_count, srv_registry);
122+
err |= opt->undefine(hypergraph_optimizer_option_name.c_str());
123+
return err;
124+
}

‎sql/opt_option_usage.h

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
/* Copyright (c) 2025, Oracle and/or its affiliates.
2+
3+
This program is free software; you can redistribute it and/or modify
4+
it under the terms of the GNU General Public License, version 2.0,
5+
as published by the Free Software Foundation.
6+
7+
This program is designed to work with certain software (including
8+
but not limited to OpenSSL) that is licensed under separate terms,
9+
as designated in a particular file or component or in included license
10+
documentation. The authors of MySQL hereby grant you an additional
11+
permission to link the program and your derivative works with the
12+
separately licensed software that they have either included with
13+
the program or referenced in the documentation.
14+
15+
This program is distributed in the hope that it will be useful,
16+
but WITHOUT ANY WARRANTY; without even the implied warranty of
17+
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18+
GNU General Public License, version 2.0, for more details.
19+
20+
You should have received a copy of the GNU General Public License
21+
along with this program; if not, write to the Free Software
22+
Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */
23+
24+
#pragma once
25+
26+
#include "include/mysql/components/component_implementation.h"
27+
#include "mysql/components/services/mysql_option_tracker.h"
28+
29+
extern bool optimizer_options_usage_init(
30+
SERVICE_TYPE(mysql_option_tracker_option) * opt,
31+
SERVICE_TYPE(registry) * srv_registry);
32+
extern bool optimizer_options_usage_deinit(
33+
SERVICE_TYPE(mysql_option_tracker_option) * opt,
34+
SERVICE_TYPE(registry) * srv_registry);
35+
extern unsigned long long option_tracker_traditional_optimizer_usage_count;
36+
extern unsigned long long option_tracker_hypergraph_optimizer_usage_count;

‎sql/sql_select.cc

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -95,6 +95,7 @@
9595
#include "sql/opt_explain.h"
9696
#include "sql/opt_explain_format.h"
9797
#include "sql/opt_hints.h" // hint_key_state()
98+
#include "sql/opt_option_usage.h"
9899
#include "sql/opt_trace.h"
99100
#include "sql/opt_trace_context.h"
100101
#include "sql/parse_tree_node_base.h"
@@ -796,6 +797,19 @@ bool Sql_cmd_dml::execute(THD *thd) {
796797
.secondary_engine_execution_count++;
797798
}
798799

800+
// Count usage of Traditional or Hypergraph Optimizer
801+
// Using is_explainable_query() as there is almost complete overlap between
802+
// explainable queries and queries for which we want to count the optimizer
803+
// used.
804+
if (!using_secondary_storage_engine() &&
805+
is_explainable_query(sql_command_code())) {
806+
if (lex->using_hypergraph_optimizer()) {
807+
++option_tracker_hypergraph_optimizer_usage_count;
808+
} else {
809+
++option_tracker_traditional_optimizer_usage_count;
810+
}
811+
}
812+
799813
assert(!thd->is_error());
800814

801815
// Pop ignore / strict error handler

0 commit comments

Comments
 (0)
0