GreenPlum之日常SQL脚本笔记(二)

1.查看数据库中大于100MB的表的倾斜情况

(数据倾斜率公式：最大子节点数据量/平均节点数据量。为避免整张表的数据量为空，同时对结果的影响很小，在平均节点数据量基础上加上一个很小的值)

SELECT table_name,max_div_avg,pg_size_pretty(total_size) table_size FROM (
SELECT table_name,
MAX(size)/(AVG(size)+0.001) AS max_div_avg,
CAST(SUM(size) AS BIGINT) total_size
FROM
(SELECT gp_segment_id,
oid::regclass table_name,
pg_relation_size(oid) size
FROM gp_dist_random('pg_class')
WHERE relkind='r'
AND relstorage IN ('a','h')) t
GROUP BY table_name)tab WHERE total_size >= 104857600
ORDER BY total_size DESC;

2.队列限制，执行和等待查询的数量：　

select * from pg_resqueue_status

3.查看role分配的resource queue　　

SELECT rolname, rsqname FROM pg_roles, pg_resqueue WHERE pg_roles.rolresqueue=pg_resqueue.oid;

4.查询所有的resource queue的当前活动sql 　

 SELECT usename, rsqname, locktype, objid, transaction,
 pid, mode, granted, waiting
 FROM pg_stat_activity, pg_resqueue, pg_locks
 WHERE pg_stat_activity.procpid=pg_locks.pid
 AND pg_locks.objid=pg_resqueue.oid;

5.各主、镜像节点存放的文件系统路径　　

 

select * from pg_filespace_entry;

 

6.查看数据库空间大小　　

 

SELECT *,pg_size_pretty(sodddatsize) dbsize FROM gp_toolkit.gp_size_of_database ORDER BY sodddatsize desc;

 

7.查看活动语句的优先级　　

 

select *from gp_toolkit.gp_resq_priority_statement;

 

8.查看锁信息　　

 

SELECT locktype, database, c.relname, l.relation, l.transactionid, l.transaction, l.pid, l.mode, l.granted, a.current_query 
FROM pg_locks l, pg_class c, pg_stat_activity a 
WHERE l.relation=c.oid AND l.pid=a.procpid 
ORDER BY c.relname; 

 

9.修改某个数据库为只读状态(要求管理员权限)　　

 

alter database mp_mvt set default_transaction_read_only= on ;

 

10.查看哪些节点挂了　　

 

select *from gp_segment_configuration where status = 'd';

 

11.当前数据库使用状态　　

 

select * from pg_stat_database;

 

12.查看GP对应的PostgreSQL版本信息　　

 

SELECT VERSION();

 

/*
部分GP优化总结:

not in==》改用left join去重后的表关联来实现

cost：返回第一行记录前的启动时间，和返回所有记录的总时间（以磁盘页面存取为单位计量）
rows：根据统计信息估计SQL返回结果集的行数
width：返回的结果集的每一行的长度，这个长度值是根据pg_statistic表中的统计信息来计算的

hash join: 先对其中一张关联的表计算hash值，在内存中用一个散列表保存，然后对另外一张表进行全表扫描，之后将每一行与这个散列表进行关联。
nestedloop:关联的两张表中的数据量比较小的表进行广播，如笛卡尔积：select * fromtest1，test2
merge join:将两张表按照关联键进行排序，然后按照归并排序的方式将数据进行关联，效率比hash join差。full outer join只能采用merge join来实现。

哈希关联 - 用关联字段做哈希键，对小表建立哈希表。然后扫描大表，计算大表每行关联字段的哈希键，从哈希表中查找哈希值相同的行。通常哈希关联是速度最快的关联方式。解释计划中的Hash Cond是关联字段。
嵌套循环 - 遍历大数据集，对其每一行，扫描小数据集，并找到匹配的行。嵌套循环关联需要广播一个表的数据，以便另一个表的数据可以和该表的每一行进行比较。对于小表或者使用索引的表性能良好。也用于笛卡尔关联和范围关联。对大表使用嵌套关联性能不佳。如果查询节点使用嵌套循环关联操作符，则检查SQL，确保结果是期望的。设置配置参数enable_nestloop 为 OFF （默认）以优先使用哈希关联。
合并关联 - 对两个数据集排序，然后合并。合并关联对已经排序的数据性能很好，但是较少使用。如要使用合并关联，设置 enable_mergejoin 为 ON。

关联键强制类型转换 
一般，表按照指定的分布键作hash分部。如果两个表按照id:intege、id:numericr分布，关联时，需要有一个表id作强制类型转化，因为不同类型的hash值不一样，因而导致数据重分布。
关联键与分部键不一致
group by、开窗函数、grouping sets会引发重分布

1、批量数据处理后，无论成功与否，都应该进行vaccumanalyze <table_name>.
 
2、对于大表的DISTINCT操作，请用 GROUPBY操作进行替代
 
3、对于大表的UNION操作，请用UNIONALL 加 group by进行改写
 
4、嵌套查询操作，尽量改写成连接查询操作
 
5、大表更新操作，尽量通过外连接+插入+truncate进行替代
 
6、大表删除操作，尽量通过外连接+插入+truncate进行替代
 
7、尽量避免进行存储过程函数嵌套导致锁冲突，考虑使用其它语言进行总体调度，比如shell,Java，c等。
 
8、尽量避免在数据库中使用序列，游标, 循环，考虑对数据进行整体操作。
 
9、避免高度频繁的建表删表操作，容易造成字典破碎和字典锁的问题，因此可以通过临时表和其它语言方式替代。
 
11、检查大表的hash键定义是否可以保证每个segment存储均匀，数据处理均匀，以及大表连接操作过程中可以尽量避免motion操作。从目前业务看，可以考虑客户ID, 电话号码作为首选的hash键，然后进行检查。
 
12、为了加快开发进度，减少开发时的彼此影响，建议每个开发人员装一个gp虚拟机，相关表放入少量记录，进行单步测试。通过单步测试后，统一提交到测试服务器上进行联合测试和压力测试。
 
13、运行较大操作时，不建议使用pgadmin完成操作，建议写成shell脚本在后台进行测试。避免频繁不正常中断操作，对整个开发产生影响
 
14、尽量避免不正常中断操作，如需不正常中断操作，请使用函数pg_cancel_backend函数，并耐心等待3~5分钟，再继续相关操作，避免造成数据字典破坏。

聚合函数太多:
一条SQL中聚合函数太多，而且可能由于统计信息不够详细或者SQL太负责，错选hashaggregate来执行，导致内存不足。解决方法：
拆分成多个SQL来执行，减少hashaggregate使用的内存执行enable_hashagg=off，把hashaggregate参数关掉，强制不采用。将会采用groupaggregate，这样排序时间会长一些，但是内存可控，建议采用这种方式比较简单。

资源队列:
数据写入、查询分别使用不同的用户，GP创建用户时为不同用户指定不同的资源队列。

其它优化技巧:
用group by对distinct改写，因为DISTINCT要进行排序操作用UNION ALL加GROUP BY的方式对UNION改写尽量使用GREENPLUM自身提供的聚合函数和窗口函数去完成一些复杂的分析

*/