Greenplum之日常SQL记录整理（持续增加中）

1、使用generate_series函数制造测试数据

CREATE TABLE "public"."cccc" (
"id" int8 NOT NULL,
"name" varchar(255)
) DISTRIBUTED BY("id");

insert into "public"."cccc" select 1, 'test' || seq::text from generate_series(1,1000000000) as g(seq);

2、查看数据存储倾斜的表

		SELECT current_database(),schema_name,table_name,max_div_avg,pg_size_pretty(total_size) table_size 
		FROM (
			SELECT schema_name,table_name,
				MAX(size)/(AVG(size)+0.001) AS max_div_avg,
				CAST(SUM(size) AS BIGINT) total_size
			FROM
				(
			SELECT o.gp_segment_id,
						n.nspname as schema_name,
						o.relname as table_name,
						pg_relation_size(o.oid) size
				FROM gp_dist_random('pg_class') o
					LEFT JOIN pg_namespace n on o.relnamespace=n.oid
				WHERE o.relkind='r'
				AND o.relstorage IN ('a','h')
			) t
			GROUP BY schema_name,table_name
			)tab 
		WHERE total_size >= 104857600
		AND max_div_avg>1.5
		ORDER BY total_size DESC;

3、查看各个数据库的大小

SELECT sodddatname as database_name,sodddatsize/(1024*1024) as database_size_mb from gp_toolkit.gp_size_of_database;

或

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

4、查看segment角色节点的剩余可用存储大小

SELECT dfhostname as segment_hostname,sum(dfspace)/count(dfspace)/(1024*1024) as segment_disk_free_gb from gp_toolkit.gp_disk_free GROUP BY dfhostname;

5、查看数据库锁信息

		SELECT pg_locks.pid
			 , pg_database.datname
			 , pg_stat_activity.usename
			 , locktype
			 , mode
			 , pg_stat_activity.application_name
			 , state
			 , CASE
						WHEN granted='f' THEN
							'wait_lock'
						WHEN granted='t' THEN
							'get_lock'
					END lock_satus
			 , pg_stat_activity.query
			 , least(query_start,xact_start) start_time
			 , count(*)::float
		  FROM pg_locks
		  JOIN pg_database ON pg_locks.database=pg_database.oid
		  JOIN pg_stat_activity on pg_locks.pid=pg_stat_activity.pid
		WHERE NOT pg_locks.pid=pg_backend_pid()
		AND pg_stat_activity.application_name<>'pg_statsinfod'
		GROUP BY pg_locks.pid, pg_database.datname,pg_stat_activity.usename, locktype, mode,
		pg_stat_activity.application_name, state , lock_satus ,pg_stat_activity.query, start_time
		ORDER BY start_time

6、检查系统中膨胀率超过N的AO表

膨胀率超过千分之2的AO表：

select * from (  
  select t2.nspname, t1.relname, (gp_toolkit.__gp_aovisimap_compaction_info(t1.oid)).*   
  from pg_class t1, pg_namespace t2 where t1.relnamespace=t2.oid and relstorage in ('c', 'a')   
) t   
where t.percent_hidden > 0.2;

7、检查系统中膨胀的堆存储的表。

		SELECT current_database(),bdinspname,bdirelname,bdirelpages,bdiexppages,(
		case 
			when position('moderate' in bdidiag)>0 then '中度膨胀'
			when position('significant' in bdidiag)>0 then '严重膨胀' 
			else '轻微膨胀'
		end) as bloat_state 
		FROM gp_toolkit.gp_bloat_diag ORDER BY bloat_state asc