【 TiDB 使用环境】生产环境
【 TiDB 版本】v5.4.3
【复现路径】做过哪些操作出现的问题
【遇到的问题:问题现象及影响】上游MySQL集群通过DM同步到下游一套tidb小集群,tidb主要用作OLAP类查询使用。上游MySQL月底一跑批,下游tidb的所有tikv节点就开始IO告急。
情况如下:
涉及表的表结构如下:
CREATE TABLE `t1` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`aid` varchar(64) NOT NULL COMMENT 'aid',
`order_no` varchar(64) NOT NULL COMMENT '原订单号',
`p_code` varchar(64) NOT NULL COMMENT '一级标签',
`tag_code` varchar(64) NOT NULL COMMENT '标签code',
`created_by` varchar(45) DEFAULT NULL COMMENT '创建人',
`updated_by` varchar(45) DEFAULT NULL COMMENT '更新人',
`created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`is_default` tinyint(4) DEFAULT '0' COMMENT '是否默认枚举,1-是',
`effective_date` timestamp NULL DEFAULT NULL COMMENT '生效时间',
`expire_date` timestamp NULL DEFAULT NULL COMMENT '失效时间',
`remark` varchar(512) DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`),
KEY `idx_aid_p_code` (`aid`,`p_code`),
KEY `idx_order_no` (`order_no`),
KEY `idx_tag_code` (`tag_code`),
KEY `idx_p_code` (`p_code`)
) ENGINE=InnoDB ;
tidb 集群参数主要有:
server_configs:
tidb:
log.level: info
log.slow-threshold: 300
mem-quota-query: 10737418240
performance.txn-entry-size-limit: 125829120
performance.txn-total-size-limit: 10737418240
prepared-plan-cache.enabled: true
tikv:
raftstore.apply-pool-size: 8
raftstore.messages-per-tick: 4096
raftstore.raft-max-inflight-msgs: 2048
raftstore.store-pool-size: 5
raftstore.sync-log: false
readpool.coprocessor.use-unified-pool: true
readpool.storage.use-unified-pool: true
readpool.unified.max-thread-count: 12
rocksdb.defaultcf.force-consistency-checks: false
rocksdb.lockcf.force-consistency-checks: false
rocksdb.max-background-jobs: 3
rocksdb.max-sub-compactions: 2
rocksdb.raftcf.force-consistency-checks: false
rocksdb.writecf.force-consistency-checks: false
server.grpc-concurrency: 8
storage.block-cache.capacity: 32GB
storage.block-cache.shared: true
MySQL同步到TiDB集群的表中有自增主键ID,还有3条辅助索引都是递增属性的,上游MySQL批量写入的时候下游 tikv IO 就飙高。这样情况求问怎么处理比较好?