Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1(EOL)
-
None
Description
Data comes from Sandy Bridge system running sysbench OLTP RO in 1 thread against 1 table.
Call graphs:
- 0.26% mysqld mysqld
|
- Item_func::type() const
|
+ 18.31% JOIN::optimize_inner()
|
+ 17.94% MYSQLparse(THD*)
|
+ 11.70% update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.253]
|
+ 10.26% build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.262]
|
+ 6.81% internal_remove_eq_conds(THD*, Item*, Item::cond_result*)
|
+ 5.92% JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*)
|
+ 5.32% join_read_const_table(st_join_table*, st_position*) [clone .isra.248]
|
+ 4.84% JOIN::optimize()
|
+ 3.57% make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*)
|
+ 3.01% SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool)
|
+ 3.00% remove_eq_conds(THD*, Item*, Item::cond_result*)
|
+ 2.92% Item_func::compile(bool (Item::*)(unsigned char**), unsigned char**, Item* (Item::*)(unsigned char*), unsigned char*)
|
+ 2.52% Item_equal::add_const(Item*, Item*)
|
+ 2.01% Item_func::setup_args_and_comparator(THD*, Arg_comparator*)
|
+ 1.31% make_join_select(JOIN*, SQL_SELECT*, Item*)
|
This is virtual method which can't be inlined. Most time spent for function call convention. Check if we can revoke it's virtual status.
Attachments
Issue Links
- is blocked by
-
MDEV-8024 Remove excessive update_used_tables() calls
-
- Closed
-
Activity
Plan:
1. Fix "MDEV-8024 Remove excessive update_used_tables() calls".
Move the call for update_used_tables() from build_equal_items() to build_equal_items_for_cond().
2a. Introduce a new class Item_ident_or_func_or_sum, common parent for Item_func_or_sum and Item_ident (which covert Item_field and Item_ref), as they behave in very similar ways in the function build_equal_items_for_cond.
2b. Change the function build_equal_items_for_cond() into virtual method in Item and split the function into at least:
- Item::build_equal_items_for_cond()
- Item_cond::build_equal_items_for_cond()
- Item_cond_and::build_equal_items_for_cond()
- Item_ident_or_func_or_sum::build_equal_items_for_cond()
2c. Update: After discussion with Igor, it appeared that Item_field and Item_ref cannot appear in this context in the function build_equal_item_for_cond:
else if (cond->type() == Item::FUNC_ITEM ||
|
cond->real_item()->type() == Item::FIELD_ITEM)
|
because conditions of type:
WHERE/HAVING field
|
are already replaced to:
WHERE/HAVING field<>0
|
by the time of a build_eqial_items() call. See normilize_cond().
The part of the condition checking for Item_field::FIELD_ITEM was a dead code.
Therefore the implementation of Item_ident_or_func_or_sum::build_equal_items() can be moved to Item_func, and Item_ident_or_func_or_sum itself is not really needed.
3. Split the function check_equality() into a method in Item. Implement
Item::check_equality() and Item_func_eq::check_equality().
This will replace two virtual calls inside the former function check_equality():
if (item->type() == Item::FUNC_ITEM &&
|
((Item_func*) item)->functype() == Item_func::EQ_FUNC)
|
into a single virtual call in Item_cond_and::build_equal_items :
if (item->check_equality(thd, &cond_equal, &eq_list))
|
Additionally, implement Item_func_eq::build_equal_items() in addition to Item_func::build_equal_items() and move the call for check_equality() from Item_func::build_equal_items() to Item_func_eq::build_equal_items().
4. Change this piece of code in the function build_equal_items():
if (cond)
|
{
|
cond= build_equal_items_for_cond(thd, cond, inherited, link_equal_fields);
|
if (cond->type() == Item::COND_ITEM &&
|
((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
|
cond_equal= &((Item_cond_and*) cond)->cond_equal;
|
|
else if (cond->type() == Item::FUNC_ITEM &&
|
((Item_cond*) cond)->functype() == Item_func::MULT_EQUAL_FUNC)
|
{
|
cond_equal= new COND_EQUAL;
|
cond_equal->current_level.push_back((Item_equal *) cond);
|
}
|
}
|
|
to get rid of virtual calls by adding a new out parameter to Item::build_equal_items() of type COND_EQUAL**:
virtual COND *build_equal_items(THD *thd, COND_EQUAL *inherited,
|
bool link_item_fields,
|
COND_EQUAL **cond_equal_ref)
|
|
so Item::build_equal_items() can create cond_equal internally.
This will replace two virtual calls, type() followed by functype(), to a single virtual call for build_equal_cond().
5. Split the functions remove_eq_conds() and internal_remove_eq_conds() into a virtual method Item::remove_eq_conds(). This will replace about 6 calls for item->type() into a single call for item->remove_eq_conds().
6. Split the function add_key_fields() into a method in Item. This will remove about 3 virtual calls item->type(), as well as some virtual calls item_func->functype(), and will add one virtual call item->add_key_fields() instead.
7. Split the function get_mm_parts() into a virtual method in Item. This will replaces one virtual call item->type() to another virtual call, item->get_mm_tree(), but also will remove a virtual call for functype(), which used to distinguish between COND_AND_FUNC and COND_OR_FUNC:
|
if (((Item_cond*) cond)->functype() == Item_func::COND_AND_FUNC)
|
8. Split the code in Item::get_mm_tree() responsible for functions into methods in Item_func and its descendants:
- Item_func
- Item_func_between
- Item_func_in
- Item_equal
This will remove a virtual call:if (type() != Item::FUNC_ITEM)
as well as remove virtual call checking functype():
switch (cond_func->functype()) {
case Item_func::BETWEEN:
..
9. Add get_mm_tree() in Item_func_like, Item_rowready_func2, Item_func_spatial_rel to get rid of the select_optimize() and have_rev_func() virtual calls.
Remove virtual methods Item_func::select_optimize(), Item_func::have_rev_func(), as well as enum Item_func::optimize_type, because they won't be needed any more.
Item_func_type() calls that remain after step4 (14 calls)
#1 0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8)
|
|
#1 0x00005555559b4a93 in setup_conds (thd=0x55555a92e650,
|
|
#1 0x0000555555c85637 in Arg_comparator::is_owner_equal_func (
|
#2 0x0000555555c73051 in Arg_comparator::set_compare_func (
|
#3 0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0,
|
#4 0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0,
|
#5 0x0000555555c72f83 in Item_func::setup_args_and_comparator (
|
#6 0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (
|
|
#1 0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650,
|
#2 0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48,
|
#3 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6f85f in remove_eq_conds (thd=0x55555a92e650,
|
#2 0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48,
|
#3 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6eabd in internal_remove_eq_conds (thd=0x55555a92e650,
|
#2 0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650,
|
#3 0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48,
|
#4 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6e989 in cond_is_datetime_is_null (cond=0x7fff3c006968)
|
#2 0x0000555555a6f457 in internal_remove_eq_conds (thd=0x55555a92e650,
|
#3 0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650,
|
#4 0x0000555555a6e3b3 in optimize_cond (join=0x7fff3c005e48,
|
#5 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48,
|
#2 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
#3 0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48)
|
#4 0x0000555555a5205a in mysql_select (thd=0x55555a92e650,
|
|
#1 0x0000555555a55ff1 in add_key_fields (join=0x7fff3c005e48,
|
#2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650,
|
#3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48,
|
#4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a5624d in add_key_fields (join=0x7fff3c005e48,
|
#2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650,
|
#3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48,
|
|
#1 0x0000555555a56407 in add_key_fields (join=0x7fff3c005e48,
|
#2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650,
|
#3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48,
|
#4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6f85f in remove_eq_conds (thd=0x55555a92e650,
|
#2 0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48,
|
#3 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6eabd in internal_remove_eq_conds (thd=0x55555a92e650,
|
#2 0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650,
|
#3 0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48,
|
#4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6e989 in cond_is_datetime_is_null (cond=0x7fff3c006968)
|
#2 0x0000555555a6f457 in internal_remove_eq_conds (thd=0x55555a92e650,
|
#3 0x0000555555a6fa56 in remove_eq_conds (thd=0x55555a92e650,
|
#4 0x0000555555a53f78 in make_join_statistics (join=0x7fff3c005e48,
|
#5 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
Item_func_type() calls that remain after step#5 (8 calls)
#1 0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8)
|
|
#1 0x00005555559b4a93 in setup_conds (thd=0x55555a92e650,
|
|
#1 0x0000555555c85637 in Arg_comparator::is_owner_equal_func (
|
#2 0x0000555555c73051 in Arg_comparator::set_compare_func (
|
#3 0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0,
|
#4 0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0,
|
#5 0x0000555555c72f83 in Item_func::setup_args_and_comparator (
|
#6 0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (
|
|
#1 0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650,
|
#2 0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48,
|
#3 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48,
|
#2 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
#3 0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48)
|
#4 0x0000555555a5205a in mysql_select (thd=0x55555a92e650,
|
|
#1 0x0000555555a55ff1 in add_key_fields (join=0x7fff3c005e48,
|
#2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650,
|
#3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48,
|
#4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a5624d in add_key_fields (join=0x7fff3c005e48,
|
#2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650,
|
#3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48,
|
|
#1 0x0000555555a56407 in add_key_fields (join=0x7fff3c005e48,
|
#2 0x0000555555a5804b in update_ref_and_keys (thd=0x55555a92e650,
|
#3 0x0000555555a5303a in make_join_statistics (join=0x7fff3c005e48,
|
#4 0x0000555555a4b050 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
Item_func_type() calls that remain after step#6 (5 calls)
#1 0x0000555555a1db8a in normalize_cond (cond=0x7fff3c005be8)
|
#2 0x0000555555bc8ed0 in MYSQLparse (thd=0x555557f5c360)
|
|
#1 0x00005555559b4a93 in setup_conds (thd=0x55555a92e650,
|
|
#1 0x0000555555c85637 in Arg_comparator::is_owner_equal_func (
|
#2 0x0000555555c73051 in Arg_comparator::set_compare_func (
|
#3 0x0000555555c73e7f in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0,
|
#4 0x0000555555a8bd84 in Arg_comparator::set_cmp_func (this=0x7fff3c005cb0,
|
#5 0x0000555555c72f83 in Item_func::setup_args_and_comparator (
|
#6 0x0000555555c72ffb in Item_bool_func2::fix_length_and_dec (
|
|
#1 0x0000555555a6ca56 in propagate_cond_constants (thd=0x55555a92e650,
|
#2 0x0000555555a6e365 in optimize_cond (join=0x7fff3c005e48,
|
#3 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
|
#1 0x0000555555a6e3d7 in optimize_cond (join=0x7fff3c005e48,
|
#2 0x0000555555a4a818 in JOIN::optimize_inner (this=0x7fff3c005e48)
|
#3 0x0000555555a49fe4 in JOIN::optimize (this=0x7fff3c005e48)
|
#4 0x0000555555a5205a in mysql_select (thd=0x55555a92e650,
|
The goal of this task has been reached.
We just need to do some clean-up after the MDEV-7950.
Separate clean-up tasks have been created:
In this SQL script:
) ENGINE=MyISAM;
Item_func::type() is called 16 times from JOIN::optimizer() and its callees during the above SELECT query:
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x00000000006357ec in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) ()
#2 0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
#3 0x0000000000636890 in JOIN::optimize_inner() ()
#4 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000635802 in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) ()
#2 0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
#3 0x0000000000636890 in JOIN::optimize_inner() ()
#4 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000635853 in build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool) ()
#2 0x0000000000636333 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
#3 0x0000000000636890 in JOIN::optimize_inner() ()
#4 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x000000000063634f in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
#2 0x0000000000636890 in JOIN::optimize_inner() ()
#3 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000636361 in build_equal_items(JOIN*, Item*, COND_EQUAL*, List<TABLE_LIST>*, bool, COND_EQUAL**, bool) [clone .constprop.266] ()
#2 0x0000000000636890 in JOIN::optimize_inner() ()
#3 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000615b8a in propagate_cond_constants(THD*, I_List<COND_CMP>*, Item*, Item*) ()
#2 0x00000000006368a3 in JOIN::optimize_inner() ()
#3 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000620d54 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#2 0x00000000006368b5 in JOIN::optimize_inner() ()
#3 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x000000000061fe5a in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#2 0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#3 0x00000000006368b5 in JOIN::optimize_inner() ()
#4 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x000000000061fd4d in cond_is_datetime_is_null(Item*) ()
#2 0x000000000061fe6d in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#3 0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#4 0x00000000006368b5 in JOIN::optimize_inner() ()
#5 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x00000000006368c9 in JOIN::optimize_inner() ()
#2 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000615e97 in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) ()
#2 0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] ()
#3 0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
#4 0x0000000000636b00 in JOIN::optimize_inner() ()
#5 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000615ea9 in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) ()
#2 0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] ()
#3 0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
#4 0x0000000000636b00 in JOIN::optimize_inner() ()
#5 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000615ebf in add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**) ()
#2 0x000000000061773a in update_ref_and_keys(THD*, st_dynamic_array*, st_join_table*, unsigned int, Item*, unsigned long long, st_select_lex*, st_sargable_param**) [clone .isra.257] ()
#3 0x000000000053f1e7 in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
#4 0x0000000000636b00 in JOIN::optimize_inner() ()
#5 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x0000000000620d54 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#2 0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
#3 0x0000000000636b00 in JOIN::optimize_inner() ()
#4 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x000000000061fe5a in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#2 0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#3 0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
#4 0x0000000000636b00 in JOIN::optimize_inner() ()
#5 0x0000000000638eed in JOIN::optimize() ()
#0 0x00000000005b4a70 in Item_func::type() const ()
#1 0x000000000061fd4d in cond_is_datetime_is_null(Item*) ()
#2 0x000000000061fe6d in internal_remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#3 0x0000000000620d69 in remove_eq_conds(THD*, Item*, Item::cond_result*) ()
#4 0x000000000053facf in make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*) ()
#5 0x0000000000636b00 in JOIN::optimize_inner() ()
#6 0x0000000000638eed in JOIN::optimize() ()