摘要:
针对mysql/stonedb大数据量子查询测试
数据填入参考: 2022-07-06 使用tpch大数据量压测mysql_帝尊悟世的博客
TPC-C 、TPC-H和TPC-DS三者最大的一个区别是,TPCC是针对OLTP数据库进行性能测试,而TPC-H和TPC-DS是针对OLAP数据库进行测试的。
数据库处理大致可分为两大类:OLTP和OLAP。
OLTP的全称是On-line Transaction Processing,中文名称是联机事务处理。其特点是会有高并发且数据量级不大的查询,是主要用于管理事务(transaction-oriented)的系统。此类系统专注于short on-line-tansactions 如INSERT, UPDATE, DELETE操作。通常存在此类系统中的数据都是以实体对象模型来存储数据,并满足3NF(数据库第三范式)。
OLAP的全称是 On-line Analytical Processing,中文名称是联机分析处理。其特点是查询频率较OLTP系统更低,但通常会涉及到非常复杂的聚合计算。 OLAP系统以维度模型来存储历史数据,其主要存储描述性的数据并且在结构上都是同质的。TPCC
TPC-C于1992年7月获得批准,是针对OLTP的基准测试。因为多事务类型、更复杂的数据库和整体执行的结构。TPC-C混合五种不同类型的比并发事务及其复杂性,要么在线执行成功要么排队等候延迟执行。这个数据库由九种类型的表,这些表具有广泛的数据记录和数据大小。TPC-C通过每分钟事务数来衡量。TPC-C 基准测试描述了批发供应商的活动,他不局限于特定的商业活动,而是代表了包含管理、销售、分发产品或者服务的任何行业。利用数据库处理类型就能区分三者,那么TPCH与TPCDS又有什么区别呢?
TPC-H基准的数据库模式遵循第三范式。新兴的数据仓库开始采用新的模型,如星型模型、雪花模型。TPC-H已经不能精准反映当今数据库系统的真实性能。为此,TPC组织推出了新一代的面向决策应用的TPC-DS 基准。所以,TPCH与TPCDS最大区别在于测试的数据库模型上。
查询语句:
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate >= date '1993-07-01'
and o_orderdate < date '1993-07-01' + interval '3' month
and exists (
select
*
from
lineitem
where
l_orderkey = o_orderkey
and l_commitdate < l_receiptdate
)
group by
o_orderpriority
order by
o_orderpriority ;
核心处理:
stonedb::core::Descriptor::EvaluatePackImpl
调用堆栈:
(gdb) bt
#0 stonedb::core::Descriptor::EvaluatePackImpl (this=0x7fce10168040, mit=...) at /root/zsl/stonedb/storage/stonedb/core/descriptor.cpp:874
#1 0x0000000001ce8c78 in stonedb::core::Descriptor::EvaluatePack (this=0x7fce10168040, mit=...) at /root/zsl/stonedb/storage/stonedb/core/descriptor.cpp:988
#2 0x0000000001d51a2f in stonedb::core::ParameterizedFilter::ApplyDescriptor (this=0x7fce10157f40, desc_number=1, limit=-1)
at /root/zsl/stonedb/storage/stonedb/core/parameterized_filter.cpp:1340
#3 0x0000000001d5005b in stonedb::core::ParameterizedFilter::UpdateMultiIndex (this=0x7fce10157f40, count_only=false, limit=-1)
at /root/zsl/stonedb/storage/stonedb/core/parameterized_filter.cpp:1053
#4 0x00000000019e9800 in stonedb::core::Query::Preexecute (this=0x7fd1a84a3b80, qu=..., sender=0x7fce10140d70, display_now=true)
at /root/zsl/stonedb/storage/stonedb/core/query.cpp:776
#5 0x00000000019b845f in stonedb::core::Engine::Execute (this=0x3661870, thd=0x3616030, lex=0x3617df0, result_output=0x7fce10135f90, unit_for_union=0x0)
at /root/zsl/stonedb/storage/stonedb/core/engine_execute.cpp:408
#6 0x00000000019b77e6 in stonedb::core::Engine::HandleSelect (this=0x3661870, thd=0x3616030, lex=0x3617df0, result=@0x7fd1a84a40a0: 0x7fce10135f90,
setup_tables_done_option=0, res=@0x7fd1a84a409c: 0, optimize_after_sdb=@0x7fd1a84a4094: 1, sdb_free_join=@0x7fd1a84a4098: 1, with_insert=0)
at /root/zsl/stonedb/storage/stonedb/core/engine_execute.cpp:232
#7 0x0000000001aad103 in stonedb::dbhandler::SDB_HandleSelect (thd=0x3616030, lex=0x3617df0, result=@0x7fd1a84a40a0: 0x7fce10135f90, setup_tables_done_option=0,
res=@0x7fd1a84a409c: 0, optimize_after_sdb=@0x7fd1a84a4094: 1, sdb_free_join=@0x7fd1a84a4098: 1, with_insert=0)
at /root/zsl/stonedb/storage/stonedb/handler/ha_rcengine.cpp:82
#8 0x000000000137b2b0 in execute_sqlcom_select (thd=0x3616030, all_tables=0x7fce100052e0)
#9 0x00000000013740c1 in mysql_execute_command (thd=0x3616030)
#10 0x000000000137dd0c in mysql_parse (thd=0x3616030,
rawbuf=0x7fce10004bc0 "select\n o_orderpriority,\n count(*) as order_count\nfrom\n orders\nwhere\n o_orderdate >= date '1993-07-01'\n and o_orderdate < date '1993-07-01' + interval '3' month\n and exists (\n "..., length=414, parser_state=0x7fd1a84a5170)
#11 0x00000000013712bd in dispatch_command (command=COM_QUERY, thd=0x3616030,
packet=0x50062f1 "select\n o_orderpriority,\n count(*) as order_count\nfrom\n orders\nwhere\n o_orderdate >= date '1993-07-01'\n and o_orderdate < date '1993-07-01' + interval '3' month\n and exists (\n "..., packet_length=414)
#12 0x00000000013703f7 in do_command (thd=0x3616030)
#13 0x000000000133c063 in do_handle_one_connection (thd_arg=0x3616030) at /root/zsl/stonedb/sql/sql_connect.cc:982
#14 0x000000000133bbc6 in handle_one_connection (arg=0x3616030) at /root/zsl/stonedb/sql/sql_connect.cc:898
#15 0x00000000019532c2 in pfs_spawn_thread (arg=0x4fc3cf0) at /root/zsl/stonedb/storage/perfschema/pfs.cc:1860
#16 0x00007fd1b17b4ea5 in start_thread () from /lib64/libpthread.so.0
#17 0x00007fd1b02c0b0d in clone () from /lib64/libc.so.6
stonedb查询耗时统计:
耗时:
mysql> select
-> o_orderpriority,
-> count(*) as order_count
-> from
-> orders
-> where
-> o_orderdate >= date '1993-07-01'
-> and o_orderdate < date '1993-07-01' + interval '3' month
-> and exists (
-> select
-> *
-> from
-> lineitem
-> where
-> l_orderkey = o_orderkey
-> and l_commitdate < l_receiptdate
-> )
-> group by
-> o_orderpriority
-> order by
-> o_orderpriority ;
+-----------------+-------------+
| o_orderpriority | order_count |
+-----------------+-------------+
| 1-URGENT | 114839 |
| 2-HIGH | 114276 |
| 3-MEDIUM | 114716 |
| 4-NOT SPECIFIED | 114913 |
| 5-LOW | 114927 |
+-----------------+-------------+
5 rows in set (29 min 28.03 sec)
explain分析:
mysql> explain select
-> o_orderpriority,
-> count(*) as order_count
-> from
-> orders
-> where
-> o_orderdate >= date '1993-07-01'
-> and o_orderdate < date '1993-07-01' + interval '3' month
-> and exists (
-> select
-> *
-> from
-> lineitem
-> where
-> l_orderkey = o_orderkey
-> and l_commitdate < l_receiptdate
-> )
-> group by
-> o_orderpriority
-> order by
-> o_orderpriority \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: orders
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 15000000
Extra: Using where with pushed condition (t0) Pckrows: 229, susp. 229 (0 empty 0 full). Conditions: 2; Using temporary; Using filesort
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: lineitem
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: tpch.orders.o_orderkey
rows: 5998606
Extra: Using where with pushed condition (t0) Pckrows: 916, susp. 916 (0 empty 0 full). Conditions: 1
2 rows in set (0.02 sec)
代码中函数处理耗时埋点记录:
[2022-07-07 09:33:20.471942] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 7.822402 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2419 , check_cast: 3.861969 , reset_cast :3.956491
[2022-07-07 09:33:20.472016] [15125] [INFO] [descriptor.cpp:976] MSG: Timer 7.822536 : __SUBQUERY EvaluatePackImpl
[2022-07-07 09:33:20.472076] [15125] [INFO] [parameterized_filter.cpp:1346] MSG: Timer 7.830184 : __SUBQUERY index: 0
[2022-07-07 09:33:28.740846] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 8.262488 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2395 , check_cast: 4.081810 , reset_cast :4.176275
[2022-07-07 09:33:28.740944] [15125] [INFO] [descriptor.cpp:976] MSG: Timer 8.262638 : __SUBQUERY EvaluatePackImpl
[2022-07-07 09:33:28.740957] [15125] [INFO] [parameterized_filter.cpp:1346] MSG: Timer 8.268870 : __SUBQUERY index: 1
[2022-07-07 09:33:37.135881] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 8.388199 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2432 , check_cast: 4.141668 , reset_cast :4.241339
[2022-07-07 09:33:37.135957] [15125] [INFO] [descriptor.cpp:976] MSG: Timer 8.388316 : __SUBQUERY EvaluatePackImpl
[2022-07-07 09:33:37.135972] [15125] [INFO] [parameterized_filter.cpp:1346] MSG: Timer 8.395005 : __SUBQUERY index: 2
[2022-07-07 09:33:45.788586] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 8.647067 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2499 , check_cast: 4.265711 , reset_cast :4.377057
[2022-07-07 09:33:45.788701] [15125] [INFO] [descriptor.cpp:976] MSG: Timer 8.647217 : __SUBQUERY EvaluatePackImpl
[2022-07-07 09:33:45.788715] [15125] [INFO] [parameterized_filter.cpp:1346] MSG: Timer 8.652734 : __SUBQUERY index: 3
[2022-07-07 09:33:55.376633] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 9.582033 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2503 , check_cast: 4.730818 , reset_cast :4.845997
[2022-07-07 09:33:55.376719] [15125] [INFO] [descriptor.cpp:976] MSG: Timer 9.582146 : __SUBQUERY EvaluatePackImpl
[2022-07-07 09:33:55.376735] [15125] [INFO] [parameterized_filter.cpp:1346] MSG: Timer 9.588010 : __SUBQUERY index: 4
[2022-07-07 09:34:04.022585] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 8.640280 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2531 , check_cast: 4.260005 , reset_cast :4.375338
[2022-07-07 09:34:04.022677] [15125] [INFO] [descriptor.cpp:976] MSG: Timer 8.640406 : __SUBQUERY EvaluatePackImpl
[2022-07-07 09:34:04.022741] [15125] [INFO] [parameterized_filter.cpp:1346] MSG: Timer 8.645947 : __SUBQUERY index: 5
[2022-07-07 09:34:12.679057] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 8.650971 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2536 , check_cast: 4.261463 , reset_cast :4.384627
[2022-07-07 09:34:12.679164] [15125] [INFO] [descriptor.cpp:976] MSG: Timer 8.651111 : __SUBQUERY EvaluatePackImpl
[2022-07-07 09:34:12.679180] [15125] [INFO] [parameterized_filter.cpp:1346] MSG: Timer 8.656423 : __SUBQUERY index: 6
[2022-07-07 09:34:22.345600] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 9.654268 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2529 , check_cast: 4.760097 , reset_cast :4.888962
[2022-07-07 09:34:22.345730] [15125] [INFO] [descriptor.cpp:976] MSG: Timer 9.654481 : __SUBQUERY EvaluatePackImpl
[2022-07-07 09:34:22.345754] [15125] [INFO] [parameterized_filter.cpp:1346] MSG: Timer 9.666560 : __SUBQUERY index: 7
[2022-07-07 09:34:31.172240] [15125] [INFO] [descriptor.cpp:920] MSG: Timer 8.818599 : __SUBQUERY EvaluatePackImpl common::TRIBOOL_UNKNOWN, index: 2496 , check_cast: 4.349025 , reset_cast :4.464462
innodb子查询:
mysql> select
-> o_orderpriority,
-> count(*) as order_count
-> from
-> orders
-> where
-> o_orderdate >= date '1993-07-01'
-> and o_orderdate < date '1993-07-01' + interval '3' month
-> and exists (
-> select
-> *
-> from
-> lineitem
-> where
-> l_orderkey = o_orderkey
-> and l_commitdate < l_receiptdate
-> )
-> group by
-> o_orderpriority
-> order by
-> o_orderpriority ;
+-----------------+-------------+
| o_orderpriority | order_count |
+-----------------+-------------+
| 1-URGENT | 105214 |
| 2-HIGH | 104821 |
| 3-MEDIUM | 105227 |
| 4-NOT SPECIFIED | 105422 |
| 5-LOW | 105356 |
+-----------------+-------------+
5 rows in set (13.46 sec
mysql> explain select
-> o_orderpriority,
-> count(*) as order_count
-> from
-> orders
-> where
-> o_orderdate >= date '1993-07-01'
-> and o_orderdate < date '1993-07-01' + interval '3' month
-> and exists (
-> select
-> *
-> from
-> lineitem
-> where
-> l_orderkey = o_orderkey
-> and l_commitdate < l_receiptdate
-> )
-> group by
-> o_orderpriority
-> order by
-> o_orderpriority \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: orders
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 14480417
filtered: 11.11
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: lineitem
partitions: NULL
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: tpch.orders.o_orderkey
rows: 4
filtered: 33.33
Extra: Using where; FirstMatch(orders)
2 rows in set, 2 warnings (0.02 sec)