执行sql的实际内存占用和慢日志记录的sql占用内存不符,同时设置单条sql查询内存上限没有生效,是什么原因呢??

为提高效率,提问时请尽量提供详细背景信息,问题描述清晰可优先响应。以下信息点请尽量提供:

  • 系统版本 & kernel 版本:centOS7.5
  • TiDB 版本:2.1.10
  • 磁盘型号:SAS
  • 集群节点分布:两台TIDB,三台PD,三台TIKV ,其中两个pd在tidb上
  • 数据量 & region 数量 & 副本数:总量五千万 , region:6300 , loead:2200
  • 集群 QPS、.999-Duration、读写比例:
  • 问题描述(我做了什么): 背景: 使用sql语句,一张六百万的表和2百万的表进行join,同时还有sum运算、GROUP BY。sql语句在tidb执行期间内存直接在十分钟内飙升了20个G,导致服务器宕机,此时sql查询时间为672s,查询slow_query表的慢日志找到对应的sql但是上面显示的内存占用才1.7G,我们配置文件里配置了单条sql占用的内存上限为5G mem-quota-query = 5368709120,但是从现象来看改配置没有阻止此次查询。请问这个内存问题需要怎样的优化。 服务器慢日志信息: ![](file:///C:UsersAdminDocumentsTencent Files857766397ImageC2C`ICFXO05H5T{0}(P]6T]I92.png) sql语句: INSERT INTO ods_product_collect_day ( data_time, cust_merch_id, cust_organ_id, level_code, manu_merch_id, manu_organ_id, manu_assoc_id, manu_assorgan_id, owner_merch_id, owner_organ_id, provid_merch_id, provid_organ_id, sale_merch_id, sale_organ_id, settle_merch_id, settle_organ_id, accept_merch_id, accept_organ_id, finance_merch_id, finance_organ_id, oper_review_merchant, oper_review_organ, prod_prod_code, expenses_item_id, period_code, payment_channel_id, collect_product_number, collect_order_number, collect_order_amount, reduce_product_number, reduce_order_number, reduce_order_amount, collect_merch_fee, collect_custom_fee, collect_bank_fee, collect_other_fee, refund_product_number, refund_order_number, refund_order_amount, refund_merch_fee, refund_merchant_fee, refund_custom_fee, refund_bank_fee, refund_other_fee, pay_order_number, pay_order_amount, pay_merch_fee, pay_custom_fee, pay_bank_fee, pay_other_fee, total_amount, total_amount2, grouping_dimension ) SELECT ooib.pay_date, ooib.cust_merch_id, ooib.cust_organ_id, ulc.level_code, ooib.manu_merch_id, ooib.manu_organ_id, ooib.manu_assoc_id, ooib.manu_assorgan_id, ooib.owner_merch_id, ooib.owner_organ_id, ooib.provid_merch_id, ooib.provid_organ_id, ooib.sale_merch_id, ooib.sale_organ_id, ooib.settle_merch_id, ooib.settle_organ_id, ooib.accept_merch_id, ooib.accept_organ_id, ooib.finance_merch_id, ooib.finance_organ_id, ooib.oper_review_merchant, ooib.oper_review_organ, ooib.prod_prod_code, ooib.setmanag_item_id, ooib.period_code, ooib.payment_channel_id, SUM(IF (ooib.pay_flag = “1”,ooib.quantity,0)) AS collect_product_number, SUM(IF (ooib.pay_flag = “1”,ooib.receipt_quantity,0)) AS collect_order_number, SUM(IF (ooib.pay_flag = “1”,ooib.order_amount, 0)) AS collect_order_amount, SUM(IF (ooib.reduce_amount != 0, ooib.quantity, 0)) AS reduce_product_number, SUM(IF(ooib.reduce_amount = 0 ,0,1)) AS reduce_order_number, SUM(IFNULL(ooib.reduce_amount, 0)) AS reduce_order_amount, SUM(IF (ooib.pay_flag = “1”,ooib.merch_fee, 0 )) AS collect_merch_fee, SUM(IF (ooib.pay_flag = “1”,ooib.custom_fee, 0)) AS collect_custom_fee, SUM(IF (ooib.pay_flag = “1”,ooib.bank_fee, 0)) AS collect_bank_fee, SUM(IF (ooib.pay_flag = “1”,ooib.other_fee, 0)) AS collect_other_fee, SUM(IF (ooib.pay_flag = “3”,ooib.quantity,0)) AS refund_product_number, SUM(IF (ooib.pay_flag = “3”,ooib.quantity,0)) AS refund_order_number, SUM(IF (ooib.pay_flag = “3”,-ooib.refund_actual_amount, 0)) AS refund_order_amount, SUM(IF (ooib.pay_flag = “3”,ooib.merch_fee, 0)) AS refund_merch_fee, SUM(IF (ooib.pay_flag = “3”,ooib.refund_merchant_fee, 0)) AS refund_merchant_fee, SUM(IF (ooib.pay_flag = “3”,ooib.custom_fee, 0)) AS refund_custom_fee, SUM(IF (ooib.pay_flag = “3”,ooib.bank_fee, 0)) AS refund_bank_fee, SUM(IF (ooib.pay_flag = “3”,ooib.other_fee, 0)) AS refund_other_fee, SUM(IF (ooib.pay_flag = “2”,ooib.quantity,0)) AS pay_order_number, SUM(IF (ooib.pay_flag = “2”,ooib.order_amount, 0)) AS pay_order_amount, SUM(IF (ooib.pay_flag = “2”,ooib.merch_fee, 0)) AS pay_merch_fee, SUM(IF (ooib.pay_flag = “2”,ooib.custom_fee, 0)) AS pay_custom_fee, SUM(IF (ooib.pay_flag = “2”,ooib.bank_fee, 0)) AS pay_bank_fee, SUM(IF (ooib.pay_flag = “2”,ooib.other_fee, 0)) AS pay_other_fee, SUM(IFNULL(ooib.reduce_amount , 0 )) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “1”,ooib.merch_fee, 0 ), 0)) + SUM(IF (ooib.pay_flag = “1”,ooib.custom_fee, 0)) + SUM(IF (ooib.pay_flag = “1”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “3”,-ooib.refund_actual_amount, 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “3”,ooib.merch_fee, 0), 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “3”,ooib.refund_merchant_fee, 0), 0)) + SUM(IF (ooib.pay_flag = “3”,ooib.custom_fee, 0)) + SUM(IF (ooib.pay_flag = “3”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.order_amount, 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “2”,ooib.merch_fee, 0), 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.custom_fee, 0)) AS total_amount, SUM(IF (ooib.pay_flag = “2”,ooib.order_amount, 0)) + SUM(IFNULL(ooib.reduce_amount , 0 )) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “1”,ooib.merch_fee, 0 ), 0)) + SUM(IF (ooib.pay_flag = “1”,ooib.custom_fee, 0)) + SUM(IF (ooib.pay_flag = “1”,ooib.other_fee, 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “3”,ooib.merch_fee, 0), 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “3”,ooib.refund_merchant_fee, 0), 0)) + SUM(IF (ooib.pay_flag = “3”,ooib.custom_fee, 0)) + SUM(IF (ooib.pay_flag = “3”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.order_amount, 0)) + SUM(IF(ooib.fee_settle_type = ‘03’,IF (ooib.pay_flag = “2”,ooib.merch_fee, 0), 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.other_fee, 0)) + SUM(IF (ooib.pay_flag = “2”,ooib.custom_fee, 0)) AS total_amount2, CONCAT(DATE_FORMAT(ooib.pay_date,"%Y%m%d"), ooib.cust_merch_id, ooib.cust_organ_id, ulc.level_code, ooib.manu_merch_id, ooib.manu_organ_id, ooib.manu_assoc_id, ooib.manu_assorgan_id, ooib.owner_merch_id, ooib.owner_organ_id, ooib.provid_merch_id, ooib.provid_organ_id, ooib.sale_merch_id, ooib.sale_organ_id, ooib.settle_merch_id, ooib.settle_organ_id, ooib.accept_merch_id, ooib.accept_organ_id, ooib.finance_merch_id, ooib.finance_organ_id, ooib.oper_review_merchant, ooib.oper_review_organ, ooib.setmanag_item_id, ooib.period_code, ooib.prod_prod_code, ooib.payment_channel_id) AS grouping_dimension FROM ods_order_item_base_two ooib JOIN user_level_code ulc ON ooib.read_cust_user_id = ulc.id where 1=1 AND ooib.status_id = ‘80’ GROUP BY ooib.pay_date, ooib.cust_merch_id, ooib.cust_organ_id, ulc.level_code, ooib.manu_merch_id, ooib.manu_organ_id, ooib.manu_assoc_id, ooib.manu_assorgan_id, ooib.owner_merch_id, ooib.owner_organ_id, ooib.provid_merch_id, ooib.provid_organ_id, ooib.sale_merch_id, ooib.sale_organ_id, ooib.settle_merch_id, ooib.settle_organ_id, ooib.accept_merch_id, ooib.accept_organ_id, ooib.finance_merch_id, ooib.finance_organ_id, ooib.oper_review_merchant, ooib.oper_review_organ, ooib.setmanag_item_id, ooib.period_code, ooib.prod_prod_code, ooib.payment_channel_id;

表结构信息:

CREATE TABLE ods_order_item_base_two ( id int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘主键ID’, order_id varchar(32) DEFAULT NULL COMMENT ‘订单表主键:收款来自子项表主键,退款为退款表主键’, item_no varchar(32) DEFAULT NULL COMMENT ‘订单子项流水’, old_item_no varchar(32) DEFAULT ‘’ COMMENT ‘原订单子项流水(退款才有)’, trans_no varchar(32) DEFAULT NULL COMMENT ‘交易流水号 订单号(源于order_header表)’, pay_time datetime DEFAULT NULL COMMENT ‘订单支付时间(展示用)’, pay_date date DEFAULT ‘0001-01-01’ COMMENT ‘订单支付时间(检索用)’, account_time date DEFAULT ‘0001-01-01’ COMMENT ‘支付平台对账日期’, trade_time date DEFAULT ‘0001-01-01’ COMMENT ‘支付平台交易日期’, settle_time date DEFAULT ‘0001-01-01’ COMMENT ‘结算日期’, order_no varchar(32) DEFAULT NULL COMMENT ‘业务流水号’, expenses_subject_id int(11) DEFAULT NULL COMMENT ‘科目id’, setmanag_item_id int(11) DEFAULT NULL COMMENT ‘项目ID(订单子项表-管理结算信息-项目ID)’, period_code varchar(20) DEFAULT NULL COMMENT ‘周期编码’, manu_merch_id int(11) DEFAULT NULL COMMENT ‘产品制造方-发布商户id’, manu_organ_id int(11) DEFAULT NULL COMMENT ‘产品制造方-发布机构id’, manu_assoc_id int(11) DEFAULT NULL COMMENT ‘产品制造方-联营商户id’, manu_assorgan_id int(11) DEFAULT NULL COMMENT ‘产品制造方-联营机构id’, owner_merch_id int(11) DEFAULT NULL COMMENT ‘产品归属方-商户id’, owner_organ_id int(11) DEFAULT NULL COMMENT ‘产品归属方-机构id’, provid_merch_id int(11) DEFAULT NULL COMMENT ‘产品提供方-商户id’, provid_organ_id int(11) DEFAULT NULL COMMENT ‘产品提供方-机构id’, sale_merch_id int(11) DEFAULT NULL COMMENT ‘销售方-商户id’, sale_organ_id int(11) DEFAULT NULL COMMENT ‘销售方-机构id’, settle_merch_id int(11) DEFAULT NULL COMMENT ‘产品资金结算方-商户id’, settle_organ_id int(11) DEFAULT NULL COMMENT ‘产品资金结算方-机构id’, accept_merch_id int(11) DEFAULT NULL COMMENT ‘产品业务受理方-商户id’, accept_organ_id int(11) DEFAULT NULL COMMENT ‘产品业务受理方-机构id’, finance_merch_id int(11) DEFAULT NULL COMMENT ‘当前管理商户-商户id’, finance_organ_id int(11) DEFAULT NULL COMMENT ‘当前管理机构-机构id’, oper_review_merchant int(11) DEFAULT NULL COMMENT ‘产品审核方-商户id’, oper_review_organ int(11) DEFAULT NULL COMMENT ‘产品审核方-机构id’, prod_prod_code varchar(50) DEFAULT NULL COMMENT ‘产品信息-产品编号’, bill_instructions varchar(500) DEFAULT ‘’ COMMENT ‘缴费说明’, status_id tinyint(3) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘订单状态 (收款:1 - ITEM_REVIEW待商户审核;2 - ITEM_NOPAY待支付;10 - ITEM_RUSHING 订单冲正中;80 - ITEM_COMPLETED完成;99 - ITEM_CANCELLED取消;退款:0 - ORDER_REFUNDACCEPTED退款受理中;1 - ORDER_REFUND退款中;80 - ORDER_COMPLETED完成订单;99 - ORDER_CANCELLED取消订单);)’, order_type varchar(20) DEFAULT NULL COMMENT ‘业务类型(BILLS_ORDER账单订单;SALES_ORDER销售订单;RECHARGE_ORDER充值订单;TRANSFER_ORDER转账订单)’, cust_merch_id int(11) DEFAULT NULL COMMENT ‘客户业务主体-商户id’, cust_organ_id int(11) DEFAULT NULL COMMENT ‘机构ID(订单子项表-客户业务主体-人员机构id)’, read_cust_user_id int(11) NOT NULL COMMENT ‘真实客户主体’, merchant_code int(11) DEFAULT NULL COMMENT ‘客商识别码’, student_code int(11) DEFAULT NULL COMMENT ‘学籍识别码’, pay_flag char(1) DEFAULT NULL COMMENT ‘收付标识(1-收 2-付 3-退)’, receipt_quantity int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘收款订单笔数’, payment_quantity int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘代付订单笔数’, refund_quantity int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘退款订单笔数’, quantity int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘数量(账单、充值缴费类默认1; 商品类购买的数量或退的数量)’, amount decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘实际支付金额,保留小计(按交易金额比例折算)’, order_amount decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘下单金额(=数量*折扣价)’, reduce_amount decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘减免金额。人工调整金额(在下单金额基础上增或减的金额,以符号+/- 表示增减)’, voucher_amount decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘抵用金额’, pay_amount decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘实际支付金额’, refund_actual_amount decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘实际退费金额’, merch_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘商户承担手续费’, custom_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘客户承担手续费’, bank_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘银行承担手续费’, refund_merchant_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘退还商户的手续费 (仅退费有:返回值,正值 )’, other_fee decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘其它承担手续费’, fee_settle_type char(2) DEFAULT NULL COMMENT ‘手续费结算方式 01-后结算02-预充值(充值账号扣除) 03-实时结算’, arrival_amount decimal(18,2) NOT NULL DEFAULT ‘0.00’ COMMENT ‘应到账金额(退款为负)’, tran_remark varchar(300) DEFAULT NULL COMMENT ‘交易人-留言’, tran_postscript varchar(300) DEFAULT NULL COMMENT ‘交易人-附言/备注’, change_amount_explain varchar(300) DEFAULT NULL COMMENT ‘调价说明’, expenses_account_id int(11) DEFAULT NULL COMMENT ‘费用账户ID’, payment_method_id varchar(20) DEFAULT NULL COMMENT ‘支付方式’, payment_channel_id varchar(10) DEFAULT NULL COMMENT ‘支付通道’, payment_method_type tinyint(3) UNSIGNED NOT NULL DEFAULT ‘0’ COMMENT ‘支付分类 (0 - 线上;1 - 线下;)’, invoice_header_no varchar(50) DEFAULT NULL COMMENT ‘开票头流水’, invoice_type varchar(1) DEFAULT NULL COMMENT ‘发票类型 1电票 2 纸票’, invoice_merchant_id int(11) DEFAULT NULL COMMENT ‘开票商户’, invoice_mechanism_id int(11) DEFAULT NULL COMMENT ‘开票机构’, invoice_status varchar(1) NOT NULL DEFAULT ‘0’ COMMENT ‘开票状态 0 否 1 开票中 2 已开票 3 退票中’, invoice_time datetime DEFAULT NULL COMMENT ‘开票时间’, non_tax_flag char(1) DEFAULT NULL COMMENT ‘非税标识’, non_tax_code varchar(50) DEFAULT NULL COMMENT ‘非税标识码’, created_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’, last_updated_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘最后修改时间’, PRIMARY KEY (id), UNIQUE KEY uk_item_no (item_no), KEY item_order_id (order_id), KEY item_pay_date_index (pay_date), KEY item_account_time_index (account_time), KEY item_manu_merch_id_index (manu_merch_id), KEY item_manu_organ_id_index (manu_organ_id), KEY item_manu_assoc_id_index (manu_assoc_id), KEY item_manu_assorgan_id_index (manu_assorgan_id), KEY item_owner_merch_id_index (owner_merch_id), KEY item_owner_organ_id_index (owner_organ_id), KEY item_provid_merch_id_index (provid_merch_id), KEY item_provid_organ_id_index (provid_organ_id), KEY item_sale_merch_id_index (sale_merch_id), KEY item_sale_organ_id_index (sale_organ_id), KEY item_settle_merch_id_index (settle_merch_id), KEY item_settle_organ_id_index (settle_organ_id), KEY item_accept_merch_id_index (accept_merch_id), KEY item_accept_organ_id_index (accept_organ_id), KEY item_finance_merch_id_index (finance_merch_id), KEY item_finance_organ_id_index (finance_organ_id), KEY item_oper_review_merchant_index (oper_review_merchant), KEY item_oper_review_organ_index (oper_review_organ), KEY item_prod_prod_code_index (prod_prod_code), KEY item_custMerchIdProdProdCode (cust_merch_id,prod_prod_code), KEY item_settle_time_index (settle_time) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=8580001 COMMENT=‘订单子项基础数据表(由订单系统-订单子项表丰富而来)’

CREATE TABLE user_level_code ( id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘user_info_id’, level_code varchar(2000) DEFAULT NULL COMMENT ‘人员所在机构,多个机构以逗号隔开’, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3056864 COMMENT=‘人员多机构临时表’;

1赞

服务器慢日志信息:

麻烦提供下 tidb.log 和 dmesg 信息

这是tidb.log 第三行是开始执行的sql,到服务器宕机重启 tidb_error.log (255.5 KB)

服务器系统日志也请提供一份

请问需要哪一个系统日志,类别太多了,没有明确的系统日志名吗??

/var/log/dmesg

系统文件不大,里面有两个,由于没有时间只能两个都打包发送了dmesg.zip (21.5 KB)

这个版本对系统资源的掌握存在一些问题,新的 3.0.3 对内存控制有加强,这个问题在 3.0.3 里面应该就会正常 cancel

请问,有相关的git issue嘛?

我们是V2.1.15的版本,没有设置单条SQL内存阈值,准备搞一下。这么看,要升级到3.0.3 ?