摘要:
mysql列存储引擎-聚合中间缓存结果-分析
DML:
TPCH表
使用Q16
select
p_brand,
p_type,
p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,
part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#45'
and p_type not like 'MEDIUM POLISHED%'
and p_size in (49, 14, 23, 45, 19, 3, 36, 9)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size
limit 10;
聚合运算中间缓存分析:
存放缓存数据:
(gdb) bt
#0 Tianmu::core::AggregatorCount32::PutAggregatedValue (this=0x7fa130929300, buf=0x7fa1858d6074 "\002", factor=1)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregator.h:301
#1 0x0000000002fa46ff in Tianmu::core::AggregatorCount32::PutAggregatedValue (this=0x7fa130929300, buf=0x7fa1858d6074 "\002", v=5005, factor=1)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregator.h:311
#2 0x0000000002fa2604 in Tianmu::core::GroupTable::PutAggregatedValue (this=0x7fc4969f8218, col=3, row=0, mit=..., factor=1, as_string=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/group_table.cpp:583
#3 0x0000000002faefd5 in Tianmu::core::GroupByWrapper::PutAggregatedValue (this=0x7fc4969f8150, gr_a=3, pos=0, mit=..., factor=1)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/groupby_wrapper.cpp:520
#4 0x0000000002f44174 in Tianmu::core::AggregationAlgorithm::AggregatePackrow (this=0x7fc4969f84e0, gbw=..., mit=0x7fc4969f7e00, cur_tuple=2)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregation_algorithm.cpp:614
#5 0x0000000002f428b9 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7fc4969f84e0, gbw=..., limit=@0x7fc4969f8138: 118274, offset=@0x7fc4969f8568: 0, sender=0x0,
limit_less_than_no_groups=false, force_parall=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregation_algorithm.cpp:302
#6 0x0000000002f420ab in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7fc4969f84e0, just_distinct=false, limit=@0x7fc4969f8560: -1, offset=@0x7fc4969f8568: 0, sender=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregation_algorithm.cpp:203
#7 0x0000000002d2f6f8 in Tianmu::core::TempTable::Materialize (this=0x7fa130924460, in_subq=false, sender=0x7fa130924350, lazy=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/temp_table.cpp:2051
#8 0x0000000002c76fa3 in Tianmu::core::Engine::Execute (this=0x52ea120, thd=0x7fa1300031d0, lex=0x7fa1300054f8, result_output=0x7fa13001e800, unit_for_union=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:482
#9 0x0000000002c75d16 in Tianmu::core::Engine::HandleSelect (this=0x52ea120, thd=0x7fa1300031d0, lex=0x7fa1300054f8, result=@0x7fc4969f8cd8: 0x7fa13001e800, setup_tables_done_option=0,
res=@0x7fc4969f8cd4: 0, optimize_after_tianmu=@0x7fc4969f8ccc: 1, tianmu_free_join=@0x7fc4969f8cd0: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:238
#10 0x0000000002d5f769 in Tianmu::handler::ha_my_tianmu_query (thd=0x7fa1300031d0, lex=0x7fa1300054f8, result_output=@0x7fc4969f8cd8: 0x7fa13001e800, setup_tables_done_option=0, res=@0x7fc4969f8cd4: 0,
optimize_after_tianmu=@0x7fc4969f8ccc: 1, tianmu_free_join=@0x7fc4969f8cd0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/handler/ha_rcengine.cpp:82
#11 0x000000000239ef34 in execute_sqlcom_select (thd=0x7fa1300031d0, all_tables=0x7fa13001caa0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5184
#12 0x00000000023982b8 in mysql_execute_command (thd=0x7fa1300031d0, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:2831
#13 0x000000000239fefd in mysql_parse (thd=0x7fa1300031d0, parser_state=0x7fc4969f9e70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5622
#14 0x0000000002395195 in dispatch_command (thd=0x7fa1300031d0, com_data=0x7fc4969fa610, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1495
#15 0x00000000023940c1 in do_command (thd=0x7fa1300031d0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1034
#16 0x00000000024c6cc1 in handle_connection (arg=0x177c8420) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/conn_handler/connection_handler_per_thread.cc:313
#17 0x0000000002bac43c in pfs_spawn_thread (arg=0x177d03e0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/perfschema/pfs.cc:2197
#18 0x00007fc4a16b5ea5 in start_thread () from /lib64/libpthread.so.0
#19 0x00007fc49e932b0d in clone () from /lib64/libc.so.6
获取当前行的缓存buffer:
(gdb) bt
#0 Tianmu::core::ValueMatching_HashTable::GetAggregationRow (this=0x7ef2e49a9330, row=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/value_matching_hashtable.h:43
#1 0x0000000002fa1335 in Tianmu::core::GroupTable::FindCurrentRow (this=0x7f164deaf218, row=@0x7f164deaec20: 0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/group_table.cpp:420
#2 0x0000000002f47304 in Tianmu::core::GroupByWrapper::FindCurrentRow (this=0x7f164deaf150, row=@0x7f164deaec20: 0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/groupby_wrapper.h:109
#3 0x0000000002f43fe0 in Tianmu::core::AggregationAlgorithm::AggregatePackrow (this=0x7f164deaf4e0, gbw=..., mit=0x7f164deaee00, cur_tuple=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregation_algorithm.cpp:584
#4 0x0000000002f428b9 in Tianmu::core::AggregationAlgorithm::MultiDimensionalGroupByScan (this=0x7f164deaf4e0, gbw=..., limit=@0x7f164deaf138: 118274, offset=@0x7f164deaf568: 0, sender=0x0,
limit_less_than_no_groups=false, force_parall=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregation_algorithm.cpp:302
#5 0x0000000002f420ab in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f164deaf4e0, just_distinct=false, limit=@0x7f164deaf560: -1, offset=@0x7f164deaf568: 0, sender=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregation_algorithm.cpp:203
#6 0x0000000002d2f6f8 in Tianmu::core::TempTable::Materialize (this=0x7ef2e4924460, in_subq=false, sender=0x7ef2e4924350, lazy=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/temp_table.cpp:2051
#7 0x0000000002c76fa3 in Tianmu::core::Engine::Execute (this=0x5210120, thd=0x7ef2e40031d0, lex=0x7ef2e40054f8, result_output=0x7ef2e401e800, unit_for_union=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:482
#8 0x0000000002c75d16 in Tianmu::core::Engine::HandleSelect (this=0x5210120, thd=0x7ef2e40031d0, lex=0x7ef2e40054f8, result=@0x7f164deafcd8: 0x7ef2e401e800, setup_tables_done_option=0,
res=@0x7f164deafcd4: 0, optimize_after_tianmu=@0x7f164deafccc: 1, tianmu_free_join=@0x7f164deafcd0: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:238
#9 0x0000000002d5f769 in Tianmu::handler::ha_my_tianmu_query (thd=0x7ef2e40031d0, lex=0x7ef2e40054f8, result_output=@0x7f164deafcd8: 0x7ef2e401e800, setup_tables_done_option=0, res=@0x7f164deafcd4: 0,
optimize_after_tianmu=@0x7f164deafccc: 1, tianmu_free_join=@0x7f164deafcd0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/handler/ha_rcengine.cpp:82
#10 0x000000000239ef34 in execute_sqlcom_select (thd=0x7ef2e40031d0, all_tables=0x7ef2e401caa0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5184
#11 0x00000000023982b8 in mysql_execute_command (thd=0x7ef2e40031d0, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:2831
#12 0x000000000239fefd in mysql_parse (thd=0x7ef2e40031d0, parser_state=0x7f164deb0e70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5622
#13 0x0000000002395195 in dispatch_command (thd=0x7ef2e40031d0, com_data=0x7f164deb1610, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1495
#14 0x00000000023940c1 in do_command (thd=0x7ef2e40031d0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1034
#15 0x00000000024c6cc1 in handle_connection (arg=0x176c12e0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/conn_handler/connection_handler_per_thread.cc:313
#16 0x0000000002bac43c in pfs_spawn_thread (arg=0x17704600) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/perfschema/pfs.cc:2197
#17 0x00007f1658b72ea5 in start_thread () from /lib64/libpthread.so.0
#18 0x00007f1655defb0d in clone () from /lib64/libc.so.6
(gdb) p t
$4 = {
bman = std::shared_ptr (count 4, weak 0) 0x7ef2e49732b0,
row_len = 124,
block_size = 8126464,
npower = 16,
ndx_mask = 65535,
rows_in_block = 65536,
no_rows = 1,
blocks = std::vector of length 1, capacity 1 = {0x7ef33d8d6000},
current = -1,
release = false
}
初始化hashTable数据:
(gdb) bt
#0 Tianmu::core::ValueMatching_HashTable::Init (this=0x7ef2e49d5420, mem_available=73802976, max_no_groups=118274, _total_width=120, _input_buf_width=116, _match_width=75)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/value_matching_hashtable.cpp:52
#1 0x0000000003014617 in Tianmu::core::ValueMatchingTable::CreateNew_ValueMatchingTable (mem_available=73802976, max_no_groups=118274, max_group_code=9223372036854775807, _total_width=120,
_input_buf_width=116, _match_width=75, power=16, use_lookup_table=false) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/value_matching_table.cpp:65
#2 0x0000000002fa0a25 in Tianmu::core::GroupTable::Initialize (this=0x7f164deaf218, max_no_groups=118274, parallel_allowed=true)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/group_table.cpp:351
#3 0x0000000002fad319 in Tianmu::core::GroupByWrapper::Initialize (this=0x7f164deaf150, upper_approx_of_groups=118274, parallel_allowed=true)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/groupby_wrapper.cpp:259
#4 0x0000000002f41aef in Tianmu::core::AggregationAlgorithm::Aggregate (this=0x7f164deaf4e0, just_distinct=false, limit=@0x7f164deaf560: -1, offset=@0x7f164deaf568: 0, sender=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/aggregation_algorithm.cpp:141
#5 0x0000000002d2f6f8 in Tianmu::core::TempTable::Materialize (this=0x7ef2e491fb30, in_subq=false, sender=0x7ef2e4961cf0, lazy=false)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/temp_table.cpp:2051
#6 0x0000000002c76fa3 in Tianmu::core::Engine::Execute (this=0x5210120, thd=0x7ef2e40031d0, lex=0x7ef2e40054f8, result_output=0x7ef2e491e2c0, unit_for_union=0x0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:482
#7 0x0000000002c75d16 in Tianmu::core::Engine::HandleSelect (this=0x5210120, thd=0x7ef2e40031d0, lex=0x7ef2e40054f8, result=@0x7f164deafcd8: 0x7ef2e491e2c0, setup_tables_done_option=0,
res=@0x7f164deafcd4: 0, optimize_after_tianmu=@0x7f164deafccc: 1, tianmu_free_join=@0x7f164deafcd0: 1, with_insert=0)
at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/core/engine_execute.cpp:238
#8 0x0000000002d5f769 in Tianmu::handler::ha_my_tianmu_query (thd=0x7ef2e40031d0, lex=0x7ef2e40054f8, result_output=@0x7f164deafcd8: 0x7ef2e491e2c0, setup_tables_done_option=0, res=@0x7f164deafcd4: 0,
optimize_after_tianmu=@0x7f164deafccc: 1, tianmu_free_join=@0x7f164deafcd0: 1, with_insert=0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/tianmu/handler/ha_rcengine.cpp:82
#9 0x000000000239ef34 in execute_sqlcom_select (thd=0x7ef2e40031d0, all_tables=0x7ef2e491c560) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5184
#10 0x00000000023982b8 in mysql_execute_command (thd=0x7ef2e40031d0, first_level=true) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:2831
#11 0x000000000239fefd in mysql_parse (thd=0x7ef2e40031d0, parser_state=0x7f164deb0e70) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:5622
#12 0x0000000002395195 in dispatch_command (thd=0x7ef2e40031d0, com_data=0x7f164deb1610, command=COM_QUERY) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1495
#13 0x00000000023940c1 in do_command (thd=0x7ef2e40031d0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/sql_parse.cc:1034
#14 0x00000000024c6cc1 in handle_connection (arg=0x176c12e0) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/sql/conn_handler/connection_handler_per_thread.cc:313
#15 0x0000000002bac43c in pfs_spawn_thread (arg=0x17704600) at /home/jenkins/workspace/stonedb5.7-zsl-centos7.9-30-119/storage/perfschema/pfs.cc:2197
#16 0x00007f1658b72ea5 in start_thread () from /lib64/libpthread.so.0
#17 0x00007f1655defb0d in clone () from /lib64/libc.so.6