tiflash UNION ALL查询报错

🪐 TiDB 技术问题
1

2个大数据量查询语句分开执行没问题，2个语句通过UNION ALL，一起查询，报错
1、分开查询，不用union all

image
image1170×873 57.5 KB

查询第一个语句，很快，很好，其执行计划如下
image
image1208×833 53.8 KB

查询第二语句，其执行计划如下
image
image1184×918 67.8 KB

image
image1197×796 51.1 KB

2、2个语句通过union all，一起查询，报错
image
image1165×791 39.5 KB

执行计划如下
image
image1190×642 31.3 KB

image
image1177×613 30.4 KB

image
image1166×433 22.6 KB

报错日志
tiflash_error.log (89.8 KB)

2 
[2023/06/02 17:46:49.068 +08:00] [ERROR] [CreatingSetsBlockInputStream.cpp:273] 
["Creating join.  throw exception: Code: 0, e.displayText() = DB::Exception: Code: 0, 
e.displayText() = DB::Exception: Code: 0, e.displayText() = DB::Exception: Code: 0, 
e.displayText() = DB::Exception: 
Receiver state: ERROR, error message: From MPP<query:<query_ts:1685699205445770925, 
local_query_id:12, server_id:1506433, 
start_ts:441895932500901903>,task_id:4>: Code: 0, 
e.displayText() = DB::Exception: write to tunnel tunnel4+10 which is already closed, , 
e.what() = DB::Exception,, e.what() = DB::Exception, e.what() = DB::Exception,, 
e.what() = DB::Exception, e.what() = DB::Exception In 3.541393374 sec. "] 
[source="MPP<query:<query_ts:1685699205445770925, local_query_id:12, server_id:1506433, start_ts:441895932500901903>,task_id:13>"] [thread_id=1126]

看起来是通道被关闭了，然后没读取到数据…
不知道是不是bug… :rofl:

3

tidb有错误日志吗

4

tidb节点日志
[2023/06/02 17:41:46.971 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895854263762945] [ID=13] [QueryTs=1685698906965726703] [LocalQueryId=10] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{RightHashJoin{Recv(8, )->Recv(11, )}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Projection->Recv(12, )}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Limit->Send(-1, )”] [mpp-version=1] [exchange-compression-mode=NONE]
[2023/06/02 17:41:51.599 +08:00] [WARN] [mpp.go:490] [“other error”] [txnStartTS=441895854263762945] [storeAddr=10.201.14.7:3930] [mpp-version=1] [error=“other error for mpp stream: From MPP<query:<query_ts:1685698906965726703, local_query_id:10, server_id:1506433, start_ts:441895854263762945>,task_id:4>: Code: 0, e.displayText() = DB::Exception: write to tunnel tunnel4+10 which is already closed, , e.what() = DB::Exception,”]
[2023/06/02 17:41:51.624 +08:00] [INFO] [executor.go:1513] [“limit executor close takes a long time”] [elapsed=24.47549ms]
[2023/06/02 17:41:51.625 +08:00] [INFO] [conn.go:1184] [“command dispatched failed”] [conn=3312681199930966419] [connInfo=“id:3312681199930966419, addr:10.203.3.95:59161 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“SELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n b.patient_id,\r\n b.id,\r\n b.id temp_id,\r\n b.inp_number visit_number,\r\n b.patient_name,\r\n b.order_project_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.start_date ttime,\r\n b.issued_dept_id dept_id,\r\n b.issued_dept_name dept_name,\r\n b.issued_doctor_name doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.orders execCombiSeq,\r\n ‘’ exec_id,\r\n ‘inp’ businesstype,\r\n ‘住院’ type,\r\n b.eye_type_name,\r\n b.recipe_kind_code AS recipe_kind,\r\n b.apply_number,\r\n NULL treatment_results,\r\n b.remarks\r\n FROM his.t_inp_order b\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON b.patient_id = pi.id\r\n WHERE b.order_state IN (7,9,10)\r\n AND EXISTS\r\n (SELECT 1\r\n FROM his.t_inp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_time >= STR_TO_DATE(‘2022-01-01 00:00:00’,‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_time < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\nUNION ALL\r\nSELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n a.patient_id,\r\n b.id,\r\n b.temp_id,\r\n a.reg_number visit_number,\r\n a.patient_name,\r\n b.item_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.billing_time ttime,\r\n b.dept_id,\r\n b.dept_name,\r\n b.doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.exec_combi_seq execCombiSeq,\r\n ‘’ exec_id,\r\n ‘outp’ businesstype,\r\n ‘门诊’ type,\r\n b.eye_type_name,\r\n b.recipe_kind,\r\n b.apply_number,\r\n r.treatment_results,\r\n b.remarks\r\n FROM his.t_outp_order a\r\n LEFT JOIN his.t_outp_order_detail b\r\n ON a.id = b.t_outp_order_id\r\n AND a.hosp_id = b.hosp_id\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON a.patient_id = pi.id\r\n LEFT JOIN his.t_outp_order_exec_record r\r\n ON b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n WHERE EXISTS\r\n (SELECT 1\r\n FROM his.t_outp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_date >= STR_TO_DATE(‘2022-01-01 00:00:00’, ‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_date < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\n AND b.order_state IN (7,9,10)\r\nORDER BY EXEC_TIME DESC limit 100”] [txn_mode=PESSIMISTIC] [timestamp=441895854263762945] [err=“other error for mpp stream: From MPP<query:<query_ts:1685698906965726703, local_query_id:10, server_id:1506433, start_ts:441895854263762945>,task_id:4>: Code: 0, e.displayText() = DB::Exception: write to tunnel tunnel4+10 which is already closed, , e.what() = DB::Exception,\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).handleMPPStreamResponse\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:489\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).establishMPPConns\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:447\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).handleDispatchReq\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:353\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).run.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:194\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1598”]
[2023/06/02 17:42:16.018 +08:00] [INFO] [domain.go:2652] [“refreshServerIDTTL succeed”] [serverID=1506433] [“lease id”=4779887b6101dad6]
[2023/06/02 17:46:45.446 +08:00] [INFO] [fragment.go:120] [“Mpp will generate tasks”] [plan=LeftHashJoin{Recv()->Recv()}(his.t_inp_order.id,his.t_inp_order_exec_record.order_id)(his.t_inp_order.hosp_id,his.t_inp_order_exec_record.hosp_id)->Limit->Send()] [mpp-version=1]
[2023/06/02 17:46:45.446 +08:00] [INFO] [fragment.go:120] [“Mpp will generate tasks”] [plan=RightHashJoin{RightHashJoin{Recv()->Recv()}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Projection->Recv()}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Limit->Send()] [mpp-version=1]
[2023/06/02 17:46:45.447 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.448 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.448 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.448 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.449 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.449 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=1] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_inp_order)->Sel([in(his.t_inp_order.order_state, 7, 9, 10)])->Send(4, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=3] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_dcg_patient_info)->Send(4, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=4] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“LeftHashJoin{Recv(1, )->Recv(3, )}(his.t_inp_order.patient_id,base.t_dcg_patient_info.id)->Send(10, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=9] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_inp_order_exec_record)->Projection->Send(10, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.449 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=10] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“LeftHashJoin{Recv(4, )->Recv(9, )}(his.t_inp_order.id,his.t_inp_order_exec_record.order_id)(his.t_inp_order.hosp_id,his.t_inp_order_exec_record.hosp_id)->Limit->Send(-1, )”] [mpp-version=1] [exchange-compression-mode=NONE]
[2023/06/02 17:46:45.449 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.450 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:46:45.450 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=2] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order_detail)->Sel([not(isnull(his.t_outp_order_detail.hosp_id))])->Send(6, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.450 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=5] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order)->Send(6, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=6] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{Recv(2, )->Recv(5, )}(his.t_outp_order_detail.t_outp_order_id,his.t_outp_order.id)(his.t_outp_order_detail.hosp_id,his.t_outp_order.hosp_id)->Send(8, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=7] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_dcg_patient_info)->Send(8, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=8] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{Recv(6, )->Recv(7, )}(his.t_outp_order.patient_id,base.t_dcg_patient_info.id)->Send(13, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=11] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order_exec_record)->Sel([not(isnull(his.t_outp_order_exec_record.order_id))])->Send(13, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=12] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order_exec_record)->Sel([not(isnull(his.t_outp_order_exec_record.order_id))])->Send(13, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:46:45.451 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441895932500901903] [ID=13] [QueryTs=1685699205445770925] [LocalQueryId=12] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{RightHashJoin{Recv(8, )->Recv(11, )}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Projection->Recv(12, )}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Limit->Send(-1, )”] [mpp-version=1] [exchange-compression-mode=NONE]
[2023/06/02 17:46:49.026 +08:00] [WARN] [mpp.go:490] [“other error”] [txnStartTS=441895932500901903] [storeAddr=10.201.14.7:3930] [mpp-version=1] [error=“other error for mpp stream: From MPP<query:<query_ts:1685699205445770925, local_query_id:12, server_id:1506433, start_ts:441895932500901903>,task_id:4>: Code: 0, e.displayText() = DB::Exception: write to tunnel tunnel4+10 which is already closed, , e.what() = DB::Exception,”]
[2023/06/02 17:46:49.063 +08:00] [INFO] [executor.go:1513] [“limit executor close takes a long time”] [elapsed=36.774578ms]
[2023/06/02 17:46:49.064 +08:00] [INFO] [conn.go:1184] [“command dispatched failed”] [conn=3312681199930966419] [connInfo=“id:3312681199930966419, addr:10.203.3.95:59161 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“SELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n b.patient_id,\r\n b.id,\r\n b.id temp_id,\r\n b.inp_number visit_number,\r\n b.patient_name,\r\n b.order_project_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.start_date ttime,\r\n b.issued_dept_id dept_id,\r\n b.issued_dept_name dept_name,\r\n b.issued_doctor_name doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.orders execCombiSeq,\r\n ‘’ exec_id,\r\n ‘inp’ businesstype,\r\n ‘住院’ type,\r\n b.eye_type_name,\r\n b.recipe_kind_code AS recipe_kind,\r\n b.apply_number,\r\n NULL treatment_results,\r\n b.remarks\r\n FROM his.t_inp_order b\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON b.patient_id = pi.id\r\n WHERE b.order_state IN (7,9,10)\r\n AND EXISTS\r\n (SELECT 1\r\n FROM his.t_inp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_time >= STR_TO_DATE(‘2022-01-01 00:00:00’,‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_time < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\nUNION ALL\r\nSELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n a.patient_id,\r\n b.id,\r\n b.temp_id,\r\n a.reg_number visit_number,\r\n a.patient_name,\r\n b.item_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.billing_time ttime,\r\n b.dept_id,\r\n b.dept_name,\r\n b.doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.exec_combi_seq execCombiSeq,\r\n ‘’ exec_id,\r\n ‘outp’ businesstype,\r\n ‘门诊’ type,\r\n b.eye_type_name,\r\n b.recipe_kind,\r\n b.apply_number,\r\n r.treatment_results,\r\n b.remarks\r\n FROM his.t_outp_order a\r\n LEFT JOIN his.t_outp_order_detail b\r\n ON a.id = b.t_outp_order_id\r\n AND a.hosp_id = b.hosp_id\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON a.patient_id = pi.id\r\n LEFT JOIN his.t_outp_order_exec_record r\r\n ON b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n WHERE EXISTS\r\n (SELECT 1\r\n FROM his.t_outp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_date >= STR_TO_DATE(‘2022-01-01 00:00:00’, ‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_date < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\n AND b.order_state IN (7,9,10)\r\nORDER BY EXEC_TIME DESC limit 100”] [txn_mode=PESSIMISTIC] [timestamp=441895932500901903] [err=“other error for mpp stream: From MPP<query:<query_ts:1685699205445770925, local_query_id:12, server_id:1506433, start_ts:441895932500901903>,task_id:4>: Code: 0, e.displayText() = DB::Exception: write to tunnel tunnel4+10 which is already closed, , e.what() = DB::Exception,\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).handleMPPStreamResponse\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:489\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).establishMPPConns\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:447\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).handleDispatchReq\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:353\ngithub.com/pingcap/tidb/store/copr.(*mppIterator).run.func2\n\t/home/jenkins/agent/workspace/build-common/go/src/github.com/pingcap/tidb/store/copr/mpp.go:194\nruntime.goexit\n\t/usr/local/go/src/runtime/asm_amd64.s:1598”]
[2023/06/02 17:47:16.018 +08:00] [INFO] [domain.go:2652] [“refreshServerIDTTL succeed”] [serverID=1506433] [“lease id”=4779887b6101dad6]
[2023/06/02 17:52:16.019 +08:00] [INFO] [domain.go:2652] [“refreshServerIDTTL succeed”] [serverID=1506433] [“lease id”=4779887b6101dad6]
[2023/06/02 17:57:16.018 +08:00] [INFO] [domain.go:2652] [“refreshServerIDTTL succeed”] [serverID=1506433] [“lease id”=4779887b6101dad6]
[2023/06/02 17:59:43.408 +08:00] [INFO] [fragment.go:120] [“Mpp will generate tasks”] [plan=RightHashJoin{RightHashJoin{Recv()->Recv()}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Projection->Recv()}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Limit->Send()] [mpp-version=1]
[2023/06/02 17:59:43.459 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:59:43.475 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:59:43.475 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:59:43.510 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:59:43.523 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 17:59:43.523 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441896136435564561] [ID=1] [QueryTs=1685699983408334412] [LocalQueryId=14] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order_detail)->Sel([not(isnull(his.t_outp_order_detail.hosp_id))])->Send(3, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:59:43.523 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441896136435564561] [ID=2] [QueryTs=1685699983408334412] [LocalQueryId=14] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order)->Send(3, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:59:43.523 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441896136435564561] [ID=3] [QueryTs=1685699983408334412] [LocalQueryId=14] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{Recv(1, )->Recv(2, )}(his.t_outp_order_detail.t_outp_order_id,his.t_outp_order.id)(his.t_outp_order_detail.hosp_id,his.t_outp_order.hosp_id)->Send(5, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:59:43.523 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441896136435564561] [ID=4] [QueryTs=1685699983408334412] [LocalQueryId=14] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_dcg_patient_info)->Send(5, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:59:43.523 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441896136435564561] [ID=5] [QueryTs=1685699983408334412] [LocalQueryId=14] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{Recv(3, )->Recv(4, )}(his.t_outp_order.patient_id,base.t_dcg_patient_info.id)->Send(8, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:59:43.523 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441896136435564561] [ID=6] [QueryTs=1685699983408334412] [LocalQueryId=14] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order_exec_record)->Sel([not(isnull(his.t_outp_order_exec_record.order_id))])->Send(8, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:59:43.523 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441896136435564561] [ID=7] [QueryTs=1685699983408334412] [LocalQueryId=14] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“Table(t_outp_order_exec_record)->Sel([not(isnull(his.t_outp_order_exec_record.order_id))])->Send(8, )”] [mpp-version=1] [exchange-compression-mode=FAST]
[2023/06/02 17:59:43.523 +08:00] [INFO] [mpp_gather.go:107] [“Dispatch mpp task”] [timestamp=441896136435564561] [ID=8] [QueryTs=1685699983408334412] [LocalQueryId=14] [ServerID=1506433] [address=10.201.14.7:3930] [plan=“RightHashJoin{RightHashJoin{Recv(5, )->Recv(6, )}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Projection->Recv(7, )}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Limit->Send(-1, )”] [mpp-version=1] [exchange-compression-mode=NONE]
[2023/06/02 18:00:43.477 +08:00] [WARN] [expensivequery.go:118] [expensive_query] [cost_time=60.076814381s] [cop_time=59.781815679s] [request_count=580] [num_cop_tasks=580] [process_avg_time=0s] [process_p90_time=0s] [process_max_time=0s] [process_max_addr=10.201.14.7:3930] [wait_avg_time=0s] [wait_p90_time=0s] [wait_max_time=0s] [wait_max_addr=10.201.14.7:3930] [stats=t_outp_order:441895468178079766,t_outp_order_exec_record:441895468007686163,t_inp_order:441895468099698715,t_dcg_patient_info:441895468021055496,t_inp_order_exec_record:441895468151865371,t_outp_order_detail:441895468204294166] [conn=3312681199930966419] [user=root] [database=INFORMATION_SCHEMA] [table_ids=“[166,590,158]”] [txn_start_ts=441896136435564561] [mem_max=“16574719344 Bytes (15.4 GB)”] [sql=“SELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n b.patient_id,\r\n b.id,\r\n b.id temp_id,\r\n b.inp_number visit_number,\r\n b.patient_name,\r\n b.order_project_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.start_date ttime,\r\n b.issued_dept_id dept_id,\r\n b.issued_dept_name dept_name,\r\n b.issued_doctor_name doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.orders execCombiSeq,\r\n ‘’ exec_id,\r\n ‘inp’ businesstype,\r\n ‘住院’ type,\r\n b.eye_type_name,\r\n b.recipe_kind_code AS recipe_kind,\r\n b.apply_number,\r\n NULL treatment_results,\r\n b.remarks\r\n FROM his.t_inp_order b\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON b.patient_id = pi.id\r\n WHERE b.order_state IN (7,9,10)\r\n AND EXISTS\r\n (SELECT 1\r\n FROM his.t_inp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_time >= STR_TO_DATE(‘2022-12-01 00:00:00’,‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_time < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\nUNION ALL\r\nSELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n a.patient_id,\r\n b.id,\r\n b.temp_id,\r\n a.reg_number visit_number,\r\n a.patient_name,\r\n b.item_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.billing_time ttime,\r\n b.dept_id,\r\n b.dept_name,\r\n b.doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.exec_combi_seq execCombiSeq,\r\n ‘’ exec_id,\r\n ‘outp’ businesstype,\r\n ‘门诊’ type,\r\n b.eye_type_name,\r\n b.recipe_kind,\r\n b.apply_number,\r\n r.treatment_results,\r\n b.remarks\r\n FROM his.t_outp_order a\r\n LEFT JOIN his.t_outp_order_detail b\r\n ON a.id = b.t_outp_order_id\r\n AND a.hosp_id = b.hosp_id\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON a.patient_id = pi.id\r\n LEFT JOIN his.t_outp_order_exec_record r\r\n ON b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n WHERE EXISTS\r\n (SELECT 1\r\n FROM his.t_outp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_date >= STR_TO_DATE(‘2022-12-01 00:00:00’, ‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_date < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\n AND b.order_state IN (7,9,10)\r\nORDER BY EXEC_TIME DESC limit 100”]
[2023/06/02 18:00:45.649 +08:00] [INFO] [row_container.go:377] [“memory exceeds quota, spill to disk now.”] [consumed=17180063639] [quota=17179869184]
[2023/06/02 18:01:43.576 +08:00] [WARN] [expensivequery.go:118] [expensive_query] [cost_time=120.176328595s] [cop_time=61.628697669s] [request_count=793] [num_cop_tasks=793] [process_avg_time=0s] [process_p90_time=0s] [process_max_time=0s] [process_max_addr=10.201.14.7:3930] [wait_avg_time=0s] [wait_p90_time=0s] [wait_max_time=0s] [wait_max_addr=10.201.14.7:3930] [stats=t_inp_order:441895468099698715,t_dcg_patient_info:441895468021055496,t_inp_order_exec_record:441895468151865371,t_outp_order_detail:441895468204294166,t_outp_order:441895468178079766,t_outp_order_exec_record:441895468007686163] [conn=3312681199930966419] [user=root] [database=INFORMATION_SCHEMA] [table_ids=“[166,590,158]”] [txn_start_ts=441896136435564561] [mem_max=“17180063799 Bytes (16.0 GB)”] [sql=“SELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n b.patient_id,\r\n b.id,\r\n b.id temp_id,\r\n b.inp_number visit_number,\r\n b.patient_name,\r\n b.order_project_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.start_date ttime,\r\n b.issued_dept_id dept_id,\r\n b.issued_dept_name dept_name,\r\n b.issued_doctor_name doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.orders execCombiSeq,\r\n ‘’ exec_id,\r\n ‘inp’ businesstype,\r\n ‘住院’ type,\r\n b.eye_type_name,\r\n b.recipe_kind_code AS recipe_kind,\r\n b.apply_number,\r\n NULL treatment_results,\r\n b.remarks\r\n FROM his.t_inp_order b\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON b.patient_id = pi.id\r\n WHERE b.order_state IN (7,9,10)\r\n AND EXISTS\r\n (SELECT 1\r\n FROM his.t_inp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_time >= STR_TO_DATE(‘2022-12-01 00:00:00’,‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_time < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\nUNION ALL\r\nSELECT pi.sex,\r\n pi.sex_name,\r\n pi.birthday,\r\n a.patient_id,\r\n b.id,\r\n b.temp_id,\r\n a.reg_number visit_number,\r\n a.patient_name,\r\n b.item_name project_name,\r\n b.total_number,\r\n b.packing_unit_name,\r\n b.billing_time ttime,\r\n b.dept_id,\r\n b.dept_name,\r\n b.doctor_name,\r\n b.order_state,\r\n b.skin_test_result,\r\n ‘’ exec_user_name,\r\n ‘’ exec_dept_name,\r\n ‘’ exec_time,\r\n b.exec_combi_seq execCombiSeq,\r\n ‘’ exec_id,\r\n ‘outp’ businesstype,\r\n ‘门诊’ type,\r\n b.eye_type_name,\r\n b.recipe_kind,\r\n b.apply_number,\r\n r.treatment_results,\r\n b.remarks\r\n FROM his.t_outp_order a\r\n LEFT JOIN his.t_outp_order_detail b\r\n ON a.id = b.t_outp_order_id\r\n AND a.hosp_id = b.hosp_id\r\n LEFT JOIN base.t_dcg_patient_info pi\r\n ON a.patient_id = pi.id\r\n LEFT JOIN his.t_outp_order_exec_record r\r\n ON b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n WHERE EXISTS\r\n (SELECT 1\r\n FROM his.t_outp_order_exec_record r\r\n WHERE b.id = r.order_id\r\n AND b.hosp_id = r.hosp_id\r\n AND r.plan_exec_date >= STR_TO_DATE(‘2022-12-01 00:00:00’, ‘%Y-%m-%d %H:%i:%s’)\r\n AND r.plan_exec_date < STR_TO_DATE(‘2022-12-30 00:00:00’, ‘%Y-%m-%d %H:%i:%s’))\r\n AND b.order_state IN (7,9,10)\r\nORDER BY EXEC_TIME DESC limit 100”]
[2023/06/02 18:02:16.018 +08:00] [INFO] [domain.go:2652] [“refreshServerIDTTL succeed”] [serverID=1506433] [“lease id”=4779887b6101dad6]
[2023/06/02 18:02:21.300 +08:00] [INFO] [executor.go:1513] [“limit executor close takes a long time”] [elapsed=4.274656525s]
[2023/06/02 18:02:29.608 +08:00] [INFO] [fragment.go:120] [“Mpp will generate tasks”] [plan=RightHashJoin{RightHashJoin{Recv()->Recv()}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Projection->Recv()}(his.t_outp_order_detail.id,his.t_outp_order_exec_record.order_id)(his.t_outp_order_detail.hosp_id,his.t_outp_order_exec_record.hosp_id)->Limit->Send()] [mpp-version=1]
[2023/06/02 18:02:29.610 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 18:02:29.611 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 18:02:29.611 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 18:02:29.612 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]
[2023/06/02 18:02:29.613 +08:00] [INFO] [batch_coprocessor.go:586] [“detecting available mpp stores”] [total=1] [alive=1]

5

日志看不出来什么，可以先分析下相关表试试，再就是调内存参数，参考这个里面最佳答案

6

我觉得跟数据量大有关系，查询数据量少，不报错，查询数据量大报错，调内存是个解决问题的方向，请教有具体调内存那些参数吗
看官方文档，内存参数已经配置很合理
[profiles.default]
## 存储引擎的 segment 分裂是否使用逻辑分裂。使用逻辑分裂可以减小写放大，但是会造成一定程度的硬盘空间回收不及时。默认为 false。
## 在 v6.2.0 以及后续版本，强烈建议保留默认值 false，不要将其修改为 true。具体请参考已知问题 #5576。
# dt_enable_logical_split = false

## 单次查询过程中，节点对中间数据的内存限制
## 设置为整数时，单位为 byte，比如 34359738368 表示 32 GiB 的内存限制，0 表示无限制
## 设置为 [0.0, 1.0) 之间的浮点数时，指节点总内存的比值，比如 0.8 表示总内存的 80%，0.0 表示无限制
## 默认值为 0，表示不限制
## 当查询试图申请超过限制的内存时，查询终止执行并且报错
max_memory_usage = 0

## 所有查询过程中，节点对中间数据的内存限制
## 设置为整数时，单位为 byte，比如 34359738368 表示 32 GiB 的内存限制，0 表示无限制
## 设置为 [0.0, 1.0) 之间的浮点数时，指节点总内存的比值，比如 0.8 表示总内存的 80%，0.0 表示无限制
## 默认值为 0.8，表示总内存的 80%
## 当查询试图申请超过限制的内存时，查询终止执行并且报错
max_memory_usage_for_all_queries = 0.8
7

此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。