[MDEV-7949] Item_field::used_tables() takes 0.29% in OLTP RO Created: 2015-04-09  Updated: 2015-10-29  Resolved: 2015-10-29

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.1
Fix Version/s: 10.1.9

Type: Bug Priority: Major
Reporter: Sergey Vojtovich Assignee: Oleksandr Byelkin
Resolution: Fixed Votes: 0
Labels: None

Epic Link: Performance: micro optimizations
Sprint: 10.1.6-1, 10.1.6-2, 10.1.8-1, 10.1.9-1

 Description   

Data comes from Sandy Bridge system running sysbench OLTP RO in 1 thread against 1 table.

Call graphs:

-   0.29%  mysqld  mysqld
 
   - Item_field::used_tables() const
 
      + 16.02% Item::const_item() const
 
      + 13.23% 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*)
 
      + 11.63% Item_func::update_used_tables()
 
      + 8.13% JOIN::optimize_inner()
 
      + 6.51% build_equal_items_for_cond(THD*, Item*, COND_EQUAL*, bool)
 
      + 5.63% st_select_lex::update_used_tables()
 
      + 5.11% setup_fields(THD*, Item**, List<Item>&, enum_mark_columns, List<Item>*, bool)
 
      + 5.03% Item_equal::update_const()
 
      + 4.33% Item_equal::update_used_tables()
 
      + 4.08% Item_func::fix_fields(THD*, Item**)
 
      + 4.07% Item_func::eval_not_null_tables(unsigned char*)
 
      + 3.44% Item_equal::fix_fields(THD*, Item**)
 
      + 2.52% JOIN::optimize()
 
      + 1.96% Arg_comparator::cache_converted_constant(THD*, Item**, Item**, Item_result)
 
      + 1.43% Item_sum::check_sum_func(THD*, Item**)
 
      + 1.42% make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*)
 
      + 1.11% add_key_fields(JOIN*, key_field_t**, unsigned int*, Item*, unsigned long long, st_sargable_param**)
 
      + 0.77% setup_conds(THD*, TABLE_LIST*, List<TABLE_LIST>&, Item**)
 
      + 0.75% Item_func_between::fix_fields(THD*, Item**)
 
      + 0.74% Item_sum::update_used_tables()
 
      + 0.66% is_local_field(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.

 Comments   
Comment by Oleksandr Byelkin [ 2015-05-12 ]

I doubts we can remove virtual status, but we can check how it is called, frequently such functions called several times in a row instead of 1 call with storing result...

Comment by Oleksandr Byelkin [ 2015-05-13 ] 

  /* Item_subselect in --ps-protocol mode will state it as a non-const */
 
  is_const= args[0]->const_item() || !args[0]->used_tables();

in

sql/item_strfunc.cc|630| is_const= args[0]->const_item() || !args[0]->used_tables();
 
sql/item_strfunc.cc|642| is_const= args[i]->const_item() || !args[i]->used_tables();
 
sql/item_strfunc.cc|978| is_const= args[i]->const_item() || !args[i]->used_tables();

above in most cases is just 2 used_tables() call and it looks like workaround for some unfixed bug

Comment by Oleksandr Byelkin [ 2015-05-13 ]

Itea: check const_item() calls and if in the class there is used_table_cache use it instead of used_tables() call.

UPDATE: it is not a problem because they have const_item_cache.

Comment by Oleksandr Byelkin [ 2015-05-17 ]

I analyzed dependencies of used_tables, in theory it can be changed to

inline table_map used_tables() { return used_tables_cache; }

but it will require heavy enough refactoring of the way how we work now.

In fact now we have 2 ways of getting used tables - precalculation and getting it on the fly. Some dependencies are easy to trace now:

  • direct arguments
  • belonging to outer query
    Other are more tricky:
  • constant tables
  • reference table for NULL
  • reference on the item (all family of Item_ref)

So the changes of removing 'virtual' from used_tables is movement in the right direction (dependencies are not volatile and changes not so frequently so could be traced) but it is too much refactoring for betta and should be done in next version if gain we get really pay for 1-2 weeks of intensive development.

Now I'd better fix obvious mistakes and make small improvements.

Comment by Oleksandr Byelkin [ 2015-05-17 ]

There are 2 commits:

revision-id: b2db8e85422a455a10cd28ef7e44977182528f70
parent(s): b22959903b89e798f8804ec9a815c88f75915cd9
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-05-13 16:17:22 +0200
message:

MDEV-7949: Item_field::used_tables() takes 0.29% in OLTP RO

small sixes of used_tables() usage

—

revision-id: 8b30c0f26c7b35fc73429f85aa35eb3614d60bd8
parent(s): b2db8e85422a455a10cd28ef7e44977182528f70
committer: Oleksandr Byelkin
branch nick: server
timestamp: 2015-05-17 15:10:45 +0200
message:

MDEV-7949: Item_field::used_tables() takes 0.29% in OLTP RO

Part 2: removed hack workaround for bug we do not have.

Comment by Sergei Golubchik [ 2015-09-15 ]

Possibly related commit: mysql-5.6.5-gaaacb85

Generated at Thu Feb 08 07:23:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.