1 业务现象GaussDB的总体性能调优思路为性能瓶颈点分析、关键参数调整以及SQL调优。在调优过程中通过系统资源、吞吐量、负载等因素来帮助定位和分析性能问题使系统性能达到可接受的范围。2 技术背景在明确调优范围后需要对性能做一个整体问题诊断分析数据库服务器资源消耗情况再看引发资源瓶颈不优的SQL语句。不优的SQL语句主要优化的场景一般有两类单条SQL性能优化和并发SQL的性能优化。其中并发场景的性能优化是要基于单条SQL优化已经完成的前提下进行的下面详细说明一下相关优化思路。3 调优思路3.1 单条SQL的性能优化简单SQL复杂SQL3.1.1 对于单表查询的SQL1选择正确的分布列对于点查SQL选where条件过滤性较好重复值较少的列作为分布列让数据分布均匀2在过滤性较好的列上创建索引确保点查结果集较小的点查SQL使用上索引3对于group by类大数据量的分组统计可打开SMP并行和开启向量化引擎来提升性能同时需要合理判断SQL执行耗时是否正常如单表点查SQL一般explain analyze耗时在0.5ms左右explain查看计划有bypass关键字此类SQL最优的计划是下推至单dn后走索引查询。3.1.2 对于多表关联的SQL1选择合适的分布列如果一个SQL的计划中出现streaming关键字则很有可能表的分布列选择不正确需要进行分布列调整分布列的选择原则是DN的各节点数据均匀分布。2选择合适的分布方式在分布表表的分布列不能完全避免stream的情况下可以考虑将某些表建成复制表分布方式的选择原则复制表Replication方式将表中的全量数据在集群的每一个DN实例上保留一份。主要适用于记录集较小的表。这种存储方式的优点是每个DN上都有该表的全量数据在join操作中可以避免数据重分布操作从而减小网络开销同时减少了plan segment(每个plan segment都会起对应的线程)缺点是每个DN都保留了表的完整数据造成数据的冗余。一般情况下只有较小的维度表才会定义为Replication表。哈希Hash表将表中某一个或几个字段进行hash运算后生成对应的hash值根据DN实例与哈希值的映射关系获得该元组的目标存储位置。对于Hash分布表在读/写数据时可以利用各个节点的I/O资源大大提升表的读/写速度。一般情况下大表定义为Hash表。范围Range和列表List分布是由用户自定义的分布策略根据分布列的取值落入满足一定范围或者具体值的对应目标DN这两种分布方式便于用户灵活地进行数据管理但对用户本身的数据抽象能力有一定的要求。例a.c1b.c1 anda.c2c.c2如果abc三表都是分布表则必然出现stream情况此时为了避免streaming需要判断a表和c表哪张表更小将较小的那张表建成复制表比如c表较小则此时的分布列选择为a以c1列为分布列b以c1为分布列c建成broadcast表。3创建合适的索引对于最终结果集较小1/10/100的SQL此类SQL一般是有某张表有where条件等值过滤过滤后结果集很小然后再去跟其他表关联这种情况下的最优计划是等值条件过滤的列有索引是通过索引进行的快速过滤然后与之关联的表关联列上都有索引通过层层索引过滤后得到最终结果。此类SQL的执行计划中一般都是index scan和nestloop之类的关键字如果出现seq scan、hash join等关键字很有可能计划不是最优此时需要进行调整。4对于有较大数据需要计算的SQL如整表无等值条件关联、统计分析、排序的SQL需要打开SMP并行和开启向量化引擎5以上都分析好以后还需要关注表的关联顺序是否正确原则是哪两个表关联能先过滤较多的数据就哪两个表先关联。3.1.3 创建索引优化性能1调大内存参数set maintenance_work_mem 8GB;可根据实际情况调整值大小2打开表级索引并行创建alter table test_t1 set (parallel_workers8);可根据实际情况调整值大小3创建索引create index ……4关闭并行alter table test_t1 reset parallel_workers;3.2 并发SQL性能优化1先按照上述3.1节描述的将单条SQL性能优化好注并发量较大的情况下复杂SQL也无需开SMP并行视具体情况分析而定2根据硬件资源和数据库活跃连接数判断数据库是否到瓶颈经常碰到并发压力并未到数据库端的情况实际瓶颈在应用压测机客户端常碰到的现象有数据库端CPU、IO压力很低不足50%数据库活跃连接很少远低于实际并发数如1000并发时数据库端活跃连接只有百来个甚至更少此时需要先解决客户端瓶颈问题3确定瓶颈在数据以后再根据等待事件、WDR报告等分析具体瓶颈针对具体情况调整相应内存、IO、计划缓存等相关的参数。详细的分析思维导图和相关命令可参考整体性能和单语句性能问题整体性能单语句性能3.3 对应的一些定位sql语句如下定位sql语句需要开启相关GUC参数gs_guc reload -Z coordinator -Z datanode -N all -I all -c track_activitieson gs_guc reload -Z coordinator -Z datanode -N all -I all -c enable_resource_trackon gs_guc reload -Z coordinator -Z datanode -N all -I all -c enable_instr_track_wait on gs_guc reload -Z coordinator -Z datanode -N all -I all -c track_sql_count on线程池中工作线程及会话的状态信息select node_name,session_info from dbe_perf.global_threadpool_status order by 1;查询本节点活跃会话状态及等待事件select sample_time,substring(unique_query,0,100),application_name,wait_status from dbe_perf.local_active_session where wait_status not in (none,wait cmd) order by start_time desc;查询本节点活跃会话等待事件统计select wait_status,count(*) from dbe_perf.local_active_session group by wait_status order by 2 desc;查询耗时sqlselect query from dbe_perf.statement where user_name!omm order by cpu_time desc;查询采样SQL平均耗时及最大耗时select node_name,unique_sql_id,substring(query,0,100),total_elapse_time/n_calls/1000 as single_time_consumption,max_elapse_time from dbe_perf.summary_statement where n_calls1 and max_elapse_time 1000000;查询会话sql个数抓取select query,count(query) from pg_stat_activity where state ! idle group by query;指定节点查询特定sql历史等待事件select wait_status, count(*) from gs_asp where unique_query_idxxxx group by wait_status; select wait_status, application_name, count(*) from gs_asp where unique_query_idxxxx group by wait_status,application_name;清空系统视图select reset_unique_sql(GLOBAL,ALL,0);查询连接数是否均匀select coorname,count(*) from pgxc_stat_activity where query like %xxxx% group by coorname;4 简单总结在明确调优范围后需要分析服务器资源信息以及资源使用情况再分析不优的SQL语句使用对应的一些定位sql语句找到性能问题。转载华为云论坛