AntDB数据库性能调优方案介绍

AntDB性能调优

CPU

适用场景		interl	AMD	ARM
1.批量运行大数据量查询 2.大量数据排序	AntDB4.x	Y	Y	Y
	AntDB5.x	Y	Y	Y
	AntDB6.x	Y	Y	Y
1.并发量大 2.小查询多	AntDB4.x		Y	Y
	AntDB5.x		Y	Y
	AntDB6.x		Y	Y

AntDB4.X以上版本，基于pg9.6内核，支持并行，单个操作可以在多个core处理，因此CPU速度可以通过更多的廉价的cores来弥补。

并且将cpu调优为 性能模式

cpupower frequency-set --governor performance

memory

1.结果集小的数据库，不建议购买大内存。

2.表数据量巨大，以至于全表扫描时，内存不能全部加载的数据库，与其购买大内存或不断扩内存，不如购买IO性能更好的硬盘。

disk

类型	指标
SATA	转速：7,200 是标准，部分可达 10K
SAS	转速：10 K or 15 K
SSD

TP类业务要求SSD磁盘

raid

类型	适用场景	数据冗余/数据安全
Raid10	是一种重写的方式， 适合读多于写的环境。	至少4块盘，2块盘组成RAID1，再将这2组RAID1组成RAID0。 数据冗余实际按 1:1 冗余。
Raid5	是一种重读的方式， 读性能有所降低。	至少3块盘，每块盘都保留奇偶校验信息，只允许损坏1块盘，则可以恢复数据。如果在损坏1块盘期间，又损坏另一块盘，则是灾难性，数据无法恢复。
Raid6		是一种较新的磁盘冗余方案，只有在较新的磁盘阵列卡才支持raid6模式。可以像raid5一样提供更大的磁盘空间，同时像raid10一样提供更佳的磁盘读能力。

IO调度器调优

1.普通盘选择deadline模式 echo deadline > /sys/block/sdb/queue/scheduler 2.扇区预读提升至65536 (建议区间 4096 to 65536，一个扇区是0.5KB，也就是块预热 32MB) blockdev --setra 65536 /dev/sdb 注：该设置影响顺序读，在AntDB中有大量全表扫描、或 读取csv文件copy导入 场景 3. 提升调度器请求队列深度 echo 4096 > /sys/block/sdb/queue/nr_requests

write-back caching回写缓存

类型	说明
系统层	sync （sync会调用 fsync or fdatasync ） pg 通过 wal_sync_method 参数控制
硬盘控制器层	a battery-backed write cache（电池支持的回写高速缓存） mode： 1.write-back：快 （注意配合机房UPS电源一起使用）。当 battery 异常，有可能自动切换至 write-though 模式。 2.write-though：性能下降
硬盘层	做了raid或有控制器的磁盘，建议禁用磁盘本身的回写缓存，尽量使用raid或控制器的回写缓存更安全。 一般 系统回写缓存高达 1G 以上，控制器回写缓存在 128MB 至 512MB 之间，磁盘本身的回写缓存在 8MB 至 32MB 之间。 启用写缓存：hdparm -W 1 /dev/sdc 禁用写缓存：hdparm -W 0 /dev/sdc 显示信息：hdparm -I /dev/sdc
文件系统层	mount时指定-o 选项设置 data=writeback/ordered/journal barrier=0/1

综上所述，建议的磁盘配置，

raid阵列(支持a battery-backed write cache)+

启用raid阵列write-back cache +

禁用磁盘本身的write-back cache+

机房UPS不间断电源，

这是一种读写效率最佳 且 数据安全的搭配方案。

文件系统调优

data存储模式

在上述建议配置的存储情况下：

data=writeback

barrier=0

否则：

data=ordered

barrier=1

ext4 默认打开barrier。

File access times

-o noatime

Write cache sizing调优

/etc/sysctl.conf

vm.dirty_background_ratio=5 vm.dirty_expire_centisecs=6000 vm.dirty_writeback_centisecs=500 vm.dirty_ratio=10

禁用swappiness

/etc/sysctl.conf

vm.swappiness=0 优先收缩文件系统缓存，而不是优先进行文件交换swap vm.overcommit_memory=2 vm.overcommit_ratio=90 vm.min_free_kbytes=2048000 vm.drop_caches=3 vm.vfs_cache_pressure=100 vm.zone_reclaim_mode=0

测试CPU/disk速度

--测试cpu速度 select sum(generate_series) from generate_series(1, 100000); sum ------------ 5000050000 (1 row) Time: 72.782 ms select sum(generate_series) from generate_series(1, 1000000); sum -------------- 500000500000 (1 row) Time: 514.764 ms --测试磁盘速度 create table b1 (id int primary key); insert into b1 select generate_series(1, 100000); INSERT 0 100000 Time: 1010.510 ms insert into b1 select generate_series(1, 1000000); INSERT 0 1000000 Time: 4148.271 ms

AntDB 相关

1. 分片键尽量选择数据分布均匀、重复率低的字段，建议使用手机号、身份证号等。

2. 单表操作时：

2.1 通过过滤条件能够使操作仅涉及单节点的情况，尽量采用过滤条件字段作为分片键。

如 where phoneNum = xxx，建议phoneNum作为分片键。

2.2 操作必须涉及多节点的情况，分片键建议和条件字段保持一致。

如 select distinct date_no from，建议date_no作为分片键。

如 select stuID,name from xxx order by stuID，建议stuID作为分片键。

2.3 根据多个维度分组操作时，建议采用其中的任意一个维度作为分片键。

如 select from xxx group by a,b,c,d,e ，建议a、b、c、d、e 任意字段均可作为分片键。

2.4 对于继承表，分片键保持与父表一致。

另外，如果子表建了索引，建议父表也建上相同的索引（有助于执行计划减少排序，即便是SQL中没有order by，内部也是有可能需要排序的）。

3. 多表关联操作时：

3.1 为了避免引起数据的重分布，建议数据的分布纬度和查询纬度保持一致。 如 on a.aid = b.bid ,建议表a以aid为分片键，表b以bid为分片键，可以避免引起数据重分布。

在此基础上，尽量满足条件1的要求，使得各节点的数据均匀分布，以使各节点能够在一个比较均衡的时间段内返回结果集

4. 表数据导入后，建议手工执行一次 analyze tabName 的操作。

由于autovacuum触发需要时间间隔，如果在数据导入后，立即执行select 操作，返回结果集会非常慢。

5. 建表原则：小表replication，大表hash

6. 避免一次处理太多的数据

SELECT * FROM EVENTS ORDER BY ID DESC LIMIT 3;

id列没有索引，seq scan，排序，返回结果集

id创建索引后，index scan后直接返回结果集

6.避免内存命中率低

7.避免返回的结果集太大

LIMIT OFFSET

8.避免执行大量的小查询

会引发网络延迟

9. 简化复杂sql

view

with as

10.加快查询效率

加大统计精度 default_statistics_target

复合索引 查询字段a，并按字段b排序 CREATE INDEX t1_a_b_ndx ON t1(a,b);

部分索引

使用继承表

重update的表 设置填充因子 ALTER TABLE t1 SET (fillfactor = 70);

11.参数设置

启动adb的集群计划：

set coordinator all (enable_cluster_plan=on);

set datanode master all(enable_cluster_plan = on);

启动并行

set coordinator all (max_parallel_workers_per_gather = 5);

set coordinator all (max_worker_processes = 16);

排序操作多的局点：适当调高work_mem size，但不要超过96MB，避免内存不足

work_mem = 96MB

hash操作多的局点：适当调高hash_mem_multiplier，提供2倍的work_mem内存计算hash

hash_mem_multiplier = 2

对于有继承表实现分区表的局点：避免扫描所有分区表。

constraint_exclusion = on

对于只有原生分区表的局点：避免原生分区表判断继承表的逻辑，而降低原生分区表的效率

constraint_exclusion = off

写请求多，导致磁盘IO写非常忙的局点：

合并wal flush写请求.

wal_writer_delay = 10ms

commit_siblings = 5