TiDB集群优化方案
程序员文章站
2022-07-13 10:52:48
...
TiDB集群优化方案
部署环境
硬件
3台64G内存,CPU为2*Xeon E5-2640 v4,共计20核40线程的设备.
TiDB集群拓扑
参考 topology.yaml如下
global:
user: "root"
ssh_port: 22
deploy_dir: "/tidb-deploy"
data_dir: "/tidb-data"
arch: "amd64"
monitored:
node_exporter_port: 9100
blackbox_exporter_port: 9115
pd_servers:
- host: 10.121.110.33
- host: 10.121.110.34
- host: 10.121.110.35
tidb_servers:
- host: 10.121.110.33
- host: 10.121.110.34
- host: 10.121.110.35
tikv_servers:
- host: 10.121.110.33
- host: 10.121.110.34
- host: 10.121.110.35
monitoring_servers:
- host: 10.121.110.35
grafana_servers:
- host: 10.121.110.35
alertmanager_servers:
- host: 10.121.110.35
数据量及使用场景
数据量
现有测试数据量为asset_list1000W,vul_list1300W
使用场景概括及相关sql
增:少量插入.不作分析
删:批量删除asset或vul(比如几十W)
改:批量修改(比如几十W)
查:主要是分页查询asset_list及连表查询
| SQL种类 | SQL语句 | 运行时间 | 备注 |
|---|---|---|---|
| 删 | delete from asset_list where asset_org_id in (orgUuids） | 事务过大 | |
| delete from vul_list where asset_org_id in (orgUuids） | 事务过大 | ||
| 改 | update asset_list set suspicious=“1” where suspicious=‘0’ and asset_org_id in (orgUuids) | 事务过大 | |
| update vul_list set status=‘reported’ where status=‘unreported’ and asset_org_id in (orgUuids) | 事务过大 | ||
| 查 | select * from (select * from asset_list where suspicious=‘0’ and asset_org_id in (orgUuids))as a order by recent_time desc limit 50 offset 700000; | 23s | |
| select * from (select * from asset_list where suspicious=‘0’ and asset_org_id in (orgUuids))as a order by recent_time desc limit 50 offset 5000000; | 25s | ||
| select * from ( select * from asset_list where suspicious = ‘0’ and asset_org_id in (orgUuids) and asset_type in (‘cc’,‘主机设备’) and lower(os) in (‘bb’) and asset_vul_level in (‘0’,‘2’,‘3’) and service is not null and locate(‘22’,service) ) as a where asset_ip like ‘%41.193%’ or lower(asset_name) like ‘%41.193%’ or lower(asset_type) like ‘%41.193%’ or lower(service) like ‘%41.193%’ or lower(os) like ‘%41.193%’ or lower(business_system) like ‘%41.193%’ or lower(firm) like ‘%41.193%’ or lower(area) like ‘%41.193%’ or lower(mac) like ‘%41.193%’ or lower(asset_subclass) like ‘%41.193%’ or lower(user) like ‘%41.193%’ order by recent_time desc limit 50 offset 300 | 20s | ||
| select * from asset_list a left JOIN vul_list v on v.asset_uuid=a.asset_uuid where v.status=‘unreported’ and v.asset_org_id in (orgUuids) limit 50 offset 700000; | 32s | ||
| select * from asset_list a left JOIN vul_list v on v.asset_uuid=a.asset_uuid where v.status=‘unreported’ and v.asset_org_id in (orgUuids) limit 50 offset 7000000; | 275s | ||
| select * from asset_list a left JOIN vul_list v on v.asset_uuid=a.asset_uuid where v.status=‘unreported’ and v.asset_org_id in (orgUuids) limit 50 offset 1200000; | 42s | 先连表查询再过滤 | |
| select * from asset_list a left JOIN vul_list v on v.asset_uuid=a.asset_uuid where v.status=‘unreported’ and v.asset_org_id in (orgUuids) limit 50 offset 2400000; | 48s | 先连表查询再过滤 | |
| select * from (select * from vul_list where status=‘unreported’ and asset_org_id in (orgUuids) limit 50 offset 700000) v left JOIN asset_list a on v.asset_uuid=a.asset_uuid; | 1.3s | 先过滤再连表 | |
| select * from (select * from vul_list where status=‘unreported’ and asset_org_id in (orgUuids) limit 50 offset 1200000) v left JOIN asset_list a on v.asset_uuid=a.asset_uuid; | 0.3s | 先过滤再链表 |
当前问题
- 批量修改及删除时,存在失败过大导致失败.
- 分页查询大数据量时,查询时间过长(一般为20s+).
优化方案
使用explain来查询sql语句的执行计划,从而针对性进行优化.
建表语句
CREATE TABLE `asset_list` (
`ROW` varchar(450) NOT NULL COMMENT '主键',
`seq_uuid` varchar(64) DEFAULT '' COMMENT '结构uu',
`asset_org_id` varchar(64) DEFAULT '' COMMENT '结构uuid',
`asset_ip` varchar(64) DEFAULT '' COMMENT '结构uuid',
`asset_ip_long` bigint(20) DEFAULT NULL COMMENT '结构uuid',
`mac` varchar(64) DEFAULT '' COMMENT '结构uuid',
`os` varchar(255) DEFAULT NULL COMMENT '结构uuid',
`sensor_os` varchar(255) DEFAULT '' COMMENT '结构uuid',
`asset_type` varchar(64) DEFAULT '' COMMENT '结构uuid',
`sensor_asset_type` varchar(64) DEFAULT '' COMMENT '结构uuid',
`firm` varchar(64) DEFAULT '' COMMENT '结构uuid',
`software` varchar(64) DEFAULT '' COMMENT '结构uuid',
`serial_num` varchar(64) DEFAULT '' COMMENT '结构uuid',
`device_model` varchar(64) DEFAULT '' COMMENT '结构uuid',
`asset_vul` varchar(64) DEFAULT '' COMMENT '结构uuid',
`asset_vul_level` varchar(64) DEFAULT '' COMMENT '结构uuid',
`asset_uuid` varchar(64) DEFAULT '' COMMENT '结构uuid',
`online` varchar(64) DEFAULT '' COMMENT '结构uuid',
`suspicious` varchar(64) DEFAULT '' COMMENT '结构uuid',
`first_time` varchar(64) DEFAULT '' COMMENT '结构uuid',
`recent_time` varchar(64) DEFAULT '' COMMENT '结构uuid',
`asset_name` varchar(64) DEFAULT '' COMMENT '结构uuid',
`business_system` varchar(64) DEFAULT '' COMMENT '结构uuid',
`area` varchar(64) DEFAULT '' COMMENT '结构uuid',
`net_status` varchar(64) DEFAULT '' COMMENT '结构uuid',
`integrator` varchar(64) DEFAULT '' COMMENT '结构uuid',
`service` varchar(255) DEFAULT '' COMMENT '结构uuid',
`value` varchar(64) DEFAULT '' COMMENT '结构uuid',
`remarks` varchar(64) DEFAULT '' COMMENT '结构uuid',
`asset_source` varchar(64) DEFAULT '' COMMENT '结构uuid',
`is_alarm` varchar(64) DEFAULT '' COMMENT '结构uuid',
`asset_subclass` varchar(64) DEFAULT '' COMMENT '结构uuid',
`user` varchar(64) DEFAULT '' COMMENT '结构uuid',
`network_type` varchar(64) DEFAULT '' COMMENT '结构uuid',
`module` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra0` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra1` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra2` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra3` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra4` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra5` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra6` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra7` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra8` varchar(64) DEFAULT '' COMMENT '结构uuid',
`extra9` varchar(64) DEFAULT '' COMMENT '结构uuid',
`expire_date` bigint(40) DEFAULT NULL,
PRIMARY KEY (`ROW`) /*T![clustered_index] CLUSTERED */,
KEY `index1` (`suspicious`,`asset_org_id`,`asset_ip`),
KEY `index2` (`asset_uuid`)
) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 6 ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE TABLE `vul_list` (
`ROW` varchar(450) NOT NULL,
`asset_org_id` varchar(64) DEFAULT '',
`ip` varchar(64) DEFAULT '',
`port` varchar(64) DEFAULT '',
`vul_id` varchar(64) DEFAULT '',
`vul_name` varchar(64) DEFAULT '',
`vul_engine` varchar(64) DEFAULT '',
`vul_level` varchar(64) DEFAULT '',
`cvss_score` varchar(64) DEFAULT '',
`cve` varchar(64) DEFAULT '',
`cncve` varchar(64) DEFAULT '',
`cnvd` varchar(64) DEFAULT '',
`cnnvd` varchar(64) DEFAULT '',
`service_category` varchar(64) DEFAULT '',
`vul_scope` varchar(64) DEFAULT '',
`vul_type` varchar(64) DEFAULT '',
`vul_desc` text DEFAULT NULL,
`vul_solution` text DEFAULT NULL,
`vul_result` varchar(64) DEFAULT '',
`link` varchar(128) DEFAULT '',
`is_permeate` varchar(64) DEFAULT '',
`asset_uuid` varchar(64) DEFAULT '',
`first_time` varchar(64) DEFAULT '',
`recent_time` varchar(64) DEFAULT '',
`status` varchar(64) DEFAULT '',
`file_path` varchar(64) DEFAULT '',
`fix_org_id` varchar(64) DEFAULT '',
`operate_message` text DEFAULT NULL,
`operate_time` varchar(64) DEFAULT '',
`report_time_out` varchar(64) DEFAULT '',
`operate_log` varchar(64) DEFAULT '',
`vul_source` varchar(64) DEFAULT '',
`vul_score` double DEFAULT NULL,
`source_dp` tinyint(4) DEFAULT NULL,
`check_status` varchar(64) DEFAULT '',
`source_lm` tinyint(4) DEFAULT NULL,
`source_tf` tinyint(4) DEFAULT NULL,
PRIMARY KEY (`ROW`) /*T![clustered_index] NONCLUSTERED */,
KEY `uuid_index` (`asset_uuid`),
KEY `my_index` (`status`,`asset_org_id`)
) SHARD_ROW_ID_BITS = 4 PRE_SPLIT_REGIONS = 6 ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
前置知识
摘录:
Task简介
目前 TiDB 的计算任务隶属于两种不同的 task：cop task 和 root task。cop task 是指使用 TiKV 中的 coprocessor 执行的计算任务，root task 是指在 TiDB 中执行的计算任务。
SQL 优化的目标之一是将计算尽可能地下推到 TiKV 中执行。TiKV 中的 coprocessor 能支持大部分 SQL 内建函数（包括聚合函数和标量函数）、SQL LIMIT 操作、索引扫描和表扫描。但是，所有的 Join 操作都只能作为 root task 在 TiDB 上执行。
范围查询
在 WHERE/HAVING/ON 条件中，TiDB 优化器会分析主键或索引键的查询返回。如数字、日期类型的比较符，如大于、小于、等于以及大于等于、小于等于，字符类型的 LIKE 符号等。
值得注意的是，TiDB 目前只支持比较符一端是列，另一端是常量，或可以计算成某一常量的情况，类似 year(birth_day) < 1992 的查询条件是不能利用索引的。还要注意应尽可能使用同一类型进行比较，以避免引入额外的 cast 操作而导致不能利用索引，如 user_id = 123456，如果 user_id 是字符串，需要将 123456 也写成字符串常量的形式。
针对同一列的范围查询条件使用 AND 和 OR 组合后，等于对范围求交集或者并集。对于多维组合索引，可以写多个列的条件。例如对组合索引 (a, b, c)，当 a 为等值查询时，可以继续求 b 的查询范围，当 b 也为等值查询时，可以继续求 c 的查询范围；反之，如果 a 为非等值查询，则只能求 a 的范围。
简单dashboard查看
主控机命令行输入 tiup cluster display tidb-test --dashboard后,返回访问地址
慢查询及语句分析
访问TiDB的dashboard后,点击左边的慢查询或SQL语句分析即可查看相关语句的执行方面的相关信息.慢查询列出了最近的执行时间较长的语句;SQL语句分析列出了语句的相关执行情况.
启用Tiflash
0.前置
启用Tiflash需要配合副本规则系统一起使用,因此需要先将TiDB的副本规则系统设为可用.设置命令
[[email protected] ~]# tiup ctl:v5.0.1 pd -u http://10.121.110.34:2379 config set enable-placement-rules true
其中web地址即为pd组件地址
1.编辑Tiflash节点设置
在已有集群的情况下,增加tiflash节点;需要先编辑好待增加的节点配置
tiflash_servers:
- host: 10.121.110.30
- host: 10.121.110.32
2. 启动Tiflash节点
tiup cluster scale-out tidb-test scale-out.yaml
将设置文件导入集群,进行扩容.
3. 设定副本数
ALTER TABLE vul_list SET TIFLASH REPLICA 2;
ALTER TABLE asset_list SET TIFLASH REPLICA 2;
将表的Tiflash的副本数设为2,此时Tiflash节点开始从Tikv节点同步数据.SELECT * FROM information_schema.tiflash_replica; 可以查看各个表的Tiflash副本数.
单项设置参数查看
1.客户端下直接输入 show config where name = '参数名称'后,返回结果即为设置参数
建表时预分区
PRE_SPLIT_REGIONS = 6
表分区查看
SHOW TABLE asset_list REGIONS
设置修改方法
1.命令行下输入 tiup cluster edit-config ${cluster-name}
2.在打开的设置文件中追加(以修改事务大小为例)
server_configs:
tidb:
performance.txn-total-size-limit: 1073741824
tiup cluster reload ${cluster-name} -R tidb载入设置
调优方向
批量写入
批量写入速度较慢,大约30条/s.
批量修改
报错:8004 - Transaction is too large, size: 104857622
解决办法:TiDB4.0版本以后,只需要修改 txn-total-size-limit参数即可.默认参数为104857622(即100MB).修改为1073741824(即1GB)
修改后
| SQL种类 | SQL语句 | 运行时间 | 备注 |
|---|---|---|---|
| 改 | update asset_list set suspicious=“1” where suspicious=‘0’ and asset_org_id in (orgUuids) | 305s | Affected rows: 1147088 |
| update vul_list set status=‘reported’ where status=‘unreported’ and asset_org_id in (orgUuids) | 200s | Affected rows: 588658 |
批量删除
报错:同批量修改
解决办法:同批量修改
修改后:
| SQL种类 | SQL语句 | 运行时间 | 备注 |
|---|---|---|---|
| 删 | delete from asset_list where asset_org_id in (orgUuids） | 215s | Affected rows: 1254529 |
| delete from vul_list where asset_org_id in (orgUuids） | 302s | Affected rows: 1470736 |
批量查询
单表查询
SQL语句:
select * from (select * from asset_list where suspicious='1' and asset_org_id in ( '0454a683-ed58-42e0-bb53-45c5ae956928', '08a329df-ae25-4d5c-88d5-d3523ce5204f', '095bffa8-6d26-484e-982f-d8c20070631a', '0beed008-3621-4c10-ae7f-20e87ab4fb98', '10039851-dff4-4476-959d-9695495f9d97', '137a9d27-9726-4f84-9e71-7d698580ef86', '16b30254-f9b7-4e8d-8410-32953eb556a6', '188e263f-64af-4f45-b093-56c0458bc4d6', '19592f59-e556-4e6d-aed2-eff025357afc', '1985230e-4ccb-4f51-89c5-367425145be3', '1bbd5b55-a69c-43aa-bad2-2cd5265c278b', '1cd4e8b4-a3db-43ca-9592-bead86b0814e', '1e2cd284-86ae-4cec-a01c-7e876f494046', '21bd0e27-34c2-45af-b8fa-831d956e3a55', '279dc03d-2257-41da-b2ab-51234bf1c0c2', '2a43e696-fd3e-4161-9193-bdac2de30d8f', '2ea9fc5f-d7b6-4965-b70c-18dc4d7a1af0', '2fbd2bf0-f178-476a-adf4-ae7c2f869d7c', '32a13fbd-37cc-49ab-8d10-15a3e1e89245', '334846a9-26aa-4876-96cb-97567e7b16e9', '39778e87-d3dd-4980-9cbc-a0cbd895c31c', '3d1dc09c-3388-4568-a805-7a141bd1185b', '3df90fea-85a0-426a-851a-e5295f4283eb', '3f419a99-2c7c-4ca1-9e92-c7fd53d04359', '46376e90-9461-4cf7-8863-7bf8c66d1ec4', '47d64c3d-72d3-49f2-b043-7aa3bd05f626', '4f5aeebb-32b3-484f-a227-ef0aa83bf0ef', '4fc6f006-c24b-4ff7-b952-501ac13a1513', '503454eb-7384-4e19-a1b4-f66583ffb671', '5d521f18-9a01-4ee8-8b23-0eef8c26704e' ) )as a order by recent_time desc limit 50 offset 700000
执行计划
id
TopN_11 root 50 tidb_test.asset_list.recent_time:desc, offset:700000, count:50 50 time:1m16.2s, loops:2 1.15 GB N/A
└─IndexLookUp_28 root 1021.78 1254529 time:1m10.9s, loops:1227, index_task: {total_time: 1m12s, fetch_handle: 242.6ms, build: 186.3µs, wait: 1m11.7s}, table_task: {total_time: 6m11.1s, num: 65, concurrency: 5} 358.1 MB N/A
├─IndexRangeScan_26 cop[tikv] 1021.78 table:asset_list, index:index1(suspicious, asset_org_id, asset_ip), range:["1" "0454a683-ed58-42e0-bb53-45c5ae956928","1" "0454a683-ed58-42e0-bb53-45c5ae956928"], ["1" "08a329df-ae25-4d5c-88d5-d3523ce5204f","1" "08a329df-ae25-4d5c-88d5-d3523ce5204f"], ["1" "095bffa8-6d26-484e-982f-d8c20070631a","1" "095bffa8-6d26-484e-982f-d8c20070631a"], ["1" "0beed008-3621-4c10-ae7f-20e87ab4fb98","1" "0beed008-3621-4c10-ae7f-20e87ab4fb98"], ["1" "10039851-dff4-4476-959d-9695495f9d97","1" "10039851-dff4-4476-959d-9695495f9d97"], ["1" "137a9d27-9726-4f84-9e71-7d698580ef86","1" "137a9d27-9726-4f84-9e71-7d698580ef86"], ["1" "16b30254-f9b7-4e8d-8410-32953eb556a6","1" "16b30254-f9b7-4e8d-8410-32953eb556a6"], ["1" "188e263f-64af-4f45-b093-56c0458bc4d6","1" "188e263f-64af-4f45-b093-56c0458bc4d6"], ["1" "19592f59-e556-4e6d-aed2-eff025357afc","1" "19592f59-e556-4e6d-aed2-eff025357afc"], ["1" "1985230e-4ccb-4f51-89c5-367425145be3","1" "1985230e-4ccb-4f51-89c5-367425145be3"], ["1" "1bbd5b55-a69c-43aa-bad2-2cd5265c278b","1" "1bbd5b55-a69c-43aa-bad2-2cd5265c278b"], ["1" "1cd4e8b4-a3db-43ca-9592-bead86b0814e","1" "1cd4e8b4-a3db-43ca-9592-bead86b0814e"], ["1" "1e2cd284-86ae-4cec-a01c-7e876f494046","1" "1e2cd284-86ae-4cec-a01c-7e876f494046"], ["1" "21bd0e27-34c2-45af-b8fa-831d956e3a55","1" "21bd0e27-34c2-45af-b8fa-831d956e3a55"], ["1" "279dc03d-2257-41da-b2ab-51234bf1c0c2","1" "279dc03d-2257-41da-b2ab-51234bf1c0c2"], ["1" "2a43e696-fd3e-4161-9193-bdac2de30d8f","1" "2a43e696-fd3e-4161-9193-bdac2de30d8f"], ["1" "2ea9fc5f-d7b6-4965-b70c-18dc4d7a1af0","1" "2ea9fc5f-d7b6-4965-b70c-18dc4d7a1af0"], ["1" "2fbd2bf0-f178-476a-adf4-ae7c2f869d7c","1" "2fbd2bf0-f178-476a-adf4-ae7c2f869d7c"], ["1" "32a13fbd-37cc-49ab-8d10-15a3e1e89245","1" "32a13fbd-37cc-49ab-8d10-15a3e1e89245"], ["1" "334846a9-26aa-4876-96cb-97567e7b16e9","1" "334846a9-26aa-4876-96cb-97567e7b16e9"], ["1" "39778e87-d3dd-4980-9cbc-a0cbd895c31c","1" "39778e87-d3dd-4980-9cbc-a0cbd895c31c"], ["1" "3d1dc09c-3388-4568-a805-7a141bd1185b","1" "3d1dc09c-3388-4568-a805-7a141bd1185b"], ["1" "3df90fea-85a0-426a-851a-e5295f4283eb","1" "3df90fea-85a0-426a-851a-e5295f4283eb"], ["1" "3f419a99-2c7c-4ca1-9e92-c7fd53d04359","1" "3f419a99-2c7c-4ca1-9e92-c7fd53d04359"], ["1" "46376e90-9461-4cf7-8863-7bf8c66d1ec4","1" "46376e90-9461-4cf7-8863-7bf8c66d1ec4"], ["1" "47d64c3d-72d3-49f2-b043-7aa3bd05f626","1" "47d64c3d-72d3-49f2-b043-7aa3bd05f626"], ["1" "4f5aeebb-32b3-484f-a227-ef0aa83bf0ef","1" "4f5aeebb-32b3-484f-a227-ef0aa83bf0ef"], ["1" "4fc6f006-c24b-4ff7-b952-501ac13a1513","1" "4fc6f006-c24b-4ff7-b952-501ac13a1513"], ["1" "503454eb-7384-4e19-a1b4-f66583ffb671","1" "503454eb-7384-4e19-a1b4-f66583ffb671"], ["1" "5d521f18-9a01-4ee8-8b23-0eef8c26704e","1" "5d521f18-9a01-4ee8-8b23-0eef8c26704e"], keep order:false 1254529 time:135ms, loops:1230, cop_task: {num: 6, max: 1.49s, min: 16ms, avg: 687ms, p95: 1.49s, max_proc_keys: 322990, p95_proc_keys: 322990, tot_proc: 943ms, tot_wait: 6ms, rpc_num: 6, rpc_time: 4.12s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:238ms, min:14ms, p80:188ms, p95:238ms, iters:1253, tasks:6}, scan_detail: {total_process_keys: 1254529, total_keys: 1296825, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1296791, block: {cache_hit_count: 144, read_count: 944, read_byte: 57.1 MB}}} N/A N/A
└─TableRowIDScan_27 cop[tikv] 1021.78 table:asset_list, keep order:false 1254529 time:6m9.5s, loops:1293, cop_task: {num: 3510, max: 20.6s, min: 3.07ms, avg: 1.18s, p95: 3.47s, max_proc_keys: 436, p95_proc_keys: 410, tot_proc: 26m6s, tot_wait: 51.3s, rpc_num: 6966, rpc_time: 2h17m16.8s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:18.3s, min:2ms, p80:15ms, p95:462ms, iters:13638, tasks:3510}, scan_detail: {total_process_keys: 1254529, total_keys: 1261715, rocksdb: {delete_skipped_count: 102, key_skipped_count: 14642, block: {cache_hit_count: 7678550, read_count: 63892, read_byte: 1.39 GB}}} N/A N/A
线程池设置修改
1.读取线程池使用统一的UnifyReadPool进行读取请求.
server_configs:
tidb:
readpool.storage.use-unified-pool: true
2.增加线程池中的最大及最小线程数量
tikv:
readpool.unified.max-thread-count: 40
readpool.unified.min-thread-count: 32
下推结果缓存修改
将下推的tiKV计算结果在pd实例中进行缓存,增大缓存大小为5000.0MB
tidb:
tikv-client.copr-cache.capacity-mb: 5000.0
增加单条SQL可以占用的最大内存阈值
全表扫描.排序时防止内存溢出
tidb:
mem-quota-query: 4294967296
关闭sync-log
可以提高写入速度
关闭
tikv:
raftstore.sync-log: false
在jdbc中指定部分参数
url=jdbc:mysql://127.0.0.1:4000/tidb_test?allowMultiQueries=true&rewriteBatchedStatements=true&useConfigs=maxPerformance&useServerPrepStmts=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT
allowMultiQueries=true #避免某个同时开启配置 rewriteBatchedStatements = true 和 useServerPrepStmts = true时会产生的bug
rewriteBatchedStatements=true #SQL语句批量发送,而不是一条条发送
useConfigs=maxPerformance #同时配置多个参数如下
cacheServerConfiguration = true
useLocalSessionState = true
elideSetAutoCommits = true
alwaysSendSetIsolation = false
enableQueryTimeouts = false
useServerPrepStmts=true # 在服务端执行预处理语句
提高了写入速度
现有配置
global:
user: root
ssh_port: 22
ssh_type: builtin
deploy_dir: /tidb-deploy
data_dir: /tidb-data
os: linux
arch: amd64
monitored:
node_exporter_port: 9100
blackbox_exporter_port: 9115
deploy_dir: /tidb-deploy/monitor-9100
data_dir: /tidb-data/monitor-9100
log_dir: /tidb-deploy/monitor-9100/log
server_configs:
tidb:
mem-quota-query: 4294967296
performance.txn-total-size-limit: 3221226507
tikv-client.copr-cache.capacity-mb: 5000.0
tikv:
readpool.unified.max-thread-count: 40
readpool.unified.min-thread-count: 32
pd:
replication.enable-placement-rules: true
dashboard.enable-telemetry: false
tiflash: {}
tiflash-learner: {}
pump: {}
drainer: {}
cdc: {}
tidb_servers:
- host: 10.121.110.37
ssh_port: 22
port: 4000
status_port: 10080
deploy_dir: /tidb-deploy/tidb-4000
log_dir: /tidb-deploy/tidb-4000/log
arch: amd64
os: linux
tikv_servers:
- host: 10.121.110.37
ssh_port: 22
port: 20160
status_port: 20180
deploy_dir: /tidb-deploy/tikv-20160
data_dir: /tidb-data/tikv-20160
log_dir: /tidb-deploy/tikv-20160/log
arch: amd64
os: linux
tiflash_servers:
- host: 10.121.110.37
ssh_port: 22
tcp_port: 9000
http_port: 8123
flash_service_port: 3930
flash_proxy_port: 20170
flash_proxy_status_port: 20292
metrics_port: 8234
deploy_dir: /tidb-deploy/tiflash-9000
data_dir: /data1/tiflash/data,/data2/tiflash/data
log_dir: /tidb-deploy/tiflash-9000/log
arch: amd64
os: linux
pd_servers:
- host: 10.121.110.37
ssh_port: 22
name: pd-10.121.110.37-2379
client_port: 2379
peer_port: 2380
deploy_dir: /tidb-deploy/pd-2379
data_dir: /tidb-data/pd-2379
log_dir: /tidb-deploy/pd-2379/log
arch: amd64
os: linux
cdc_servers:
- host: 10.121.110.37
ssh_port: 22
port: 8300
deploy_dir: /tidb-deploy/cdc-8300
data_dir: /tidb-data/cdc-8300
log_dir: /tidb-deploy/cdc-8300/log
arch: amd64
os: linux
monitoring_servers:
- host: 10.121.110.37
ssh_port: 22
port: 9090
deploy_dir: /tidb-deploy/prometheus-9090
data_dir: /tidb-data/prometheus-9090
log_dir: /tidb-deploy/prometheus-9090/log
external_alertmanagers: []
arch: amd64
os: linux
grafana_servers:
- host: 10.121.110.37
ssh_port: 22
port: 3000
deploy_dir: /tidb-deploy/grafana-3000
arch: amd64
os: linux
username: admin
password: admin
anonymous_enable: false
root_url: ""
domain: ""
alertmanager_servers:
- host: 10.121.110.37
ssh_port: 22
web_port: 9093
cluster_port: 9094
deploy_dir: /tidb-deploy/alertmanager-9093
data_dir: /tidb-data/alertmanager-9093
log_dir: /tidb-deploy/alertmanager-9093/log
arch: amd64
os: linux
附录
1.如何查看磁盘读写速度?
写: time dd if=/dev/zero of=/root/testdisk/out bs=8k oflag=direct count=500000
/dev/zero不产生IO,可以用来测纯写入
if为数据来源即挂载磁盘点,of为数据输出路径.该命令将返回磁盘的写速度．
读：time dd if=/tidb/testdisk/out of=/dev/null bs=8k
该命令返回if路径的读取速度
如果路径不变的话,需要先执行写速度测试,生成对应的out文件.
上一篇： Session与Cookie底层实现原理
下一篇： SpringCloud性能参数调优