greenplum常用脚本

1、创建资源队列

create resource queue name with (memory_limit='2000MB',active_statements=10);

每条sql的最大可使用内存为：memory_limit/active_statement;如果设置了max_cost，那么可使用的最大内存为：memory_limit*(query_cost/max_cost); query_cost为在执行计划中的cost值。

 

2、查询资源队列中的等待语句

SELECT * FROM gp_toolkit.gp_locks_on_resqueue WHERE lorwaiting='true';

 

3、查看role对应的资源队列

SELECT rolname, rsqname FROM pg_roles, gp_toolkit.gp_resqueue_status WHERE pg_roles.rolresqueue=gp_toolkit.gp_resqueue_status.queueid;

 

4、查看当前处于活动状态或者等待状态的语句

SELECT rolname, rsqname, pid, granted, current_query, datname FROM pg_roles, gp_toolkit.gp_resqueue_status, pg_locks, pg_stat_activity WHERE pg_roles.rolresqueue=pg_locks.objid AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid

AND pg_stat_activity.procpid=pg_locks.pid;

 

5、查询当前活动语句的优先级

select * from gp_toolkit.gp_resq_priority_statement;

 

6、重置语句的优先级

select gp_adjust_priority(session_id, statement_count, priority);

其中session_id和statement_count两个参数分别对应gp_toolkit.gp_resq_priority_statement的rqpsession和rqpcommand。该函数只对指定的语句有效，同一个资源队列随后的语句仍然使用其预先设定的优先级

 

7、查看压缩率

select * from get_ao_compression_ratio('test_appendonly2')

select * from get_ao_distribution('test_appendonly') order by segmentid;

 

8、gp_segment_id为隐藏字段，用来唯一标识数据节点

select *,gp_segment_id from gptest order by gp_segment_id;

select * from gp_dist_random('tab1') order by gp_segment_id asc;

 

9、查看表在每个数据节点上的数据量

select * from test_appendonly2 where test_appendonly2.id=gp_segment_id group by gp_segment_id;

gpskew -t public.ate -a postgres

 

10、表名存放在pg_class，schema名放在pg_namespace里面，字段信息放在pg_attribute里面

select oid,oid::regclass from pg_class where relname='test_appendonly'

select oid,oid::regclass from pg_class where relname like 'pg_aoseg%'

 

11、查看锁信息：

所有锁：

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;

 

查询当前锁：

SELECT pid,rolname,rsqname,granted,current_query,datname

FROM pg_roles,gp_toolkit.gp_resqueue_status,pg_locks,pg_stat_activity

WHERE pg_roles.rolresqueue=pg_locks.objid

AND pg_locks.objid=gp_toolkit.gp_resqueue_status.queueid

AND pg_stat_activity.procpid=pg_locks.pid;

 

查询资源占用,冲突语句

SELECT w.current_query AS waiting_query, w.procpid AS w_pid, w.usename AS w_user,

l.current_query AS locking_query, l.procpid AS l_pid, l.usename AS l_user,

(t.schemaname::text || '.'::text) || t.relname::text AS tablename, w.query_start

   FROM pg_stat_activity w

   JOIN pg_locks l1 ON w.procpid = l1.pid AND NOT l1.granted

   JOIN pg_locks l2 ON l1.relation = l2.relation AND l2.granted

   JOIN pg_stat_activity l ON l2.pid = l.procpid

   JOIN pg_stat_user_tables t ON l1.relation = t.relid

  WHERE w.waiting;

 

解除锁定  

杀进程

SELECT pg_cancel_backend(procpid) ;

杀会话

SELECT pg_terminate_backend(procpid);

 

12、查询表空间对应的文件空间名

select a.spcname,b.fsname from pg_tablespace a,pg_filespace b where spcfsoid=b.oid;

 

13、查询对象对应的数据文件：

select oid,relname,relfilenode from pg_class where relname='objectname';

 

14、查询异常数据节点：

select * from gp_segment_configuration where status='d' or mode<>'s'；

 

15、创建心跳表（判断各计算节点是否正常）：

 创建临时表：

create table xdual_temp as select generate_series(1,10000) id ;

创建心跳表：

create table xdual (id int,update_time timestamp(0)) distributed by (id);

心跳表添加数据：

insert into xdual(id,update_time)

select id,now() from

(select id,row_number() over(partition by gp_segment_id order by id) rn

from xdual_temp) t

where rn=1;

 

16、查询数据库事务号消耗，age(datfrozenxid)超过2亿会触发autovacuum。事务号只剩100万时，为保护数据安全，数据库将挂掉，无法启动。通过standonly、单用户模式启动数据库执行vacuum回收事务号才能启动

select gp_segment_id,datname,datfrozenxid,age(datfrozenxid) from gp_dist_random('pg_database') where datname='dp_dw163';

select gp_segment_id,pg_size_pretty(pg_relation_size(oid)) from gp_dist_random('pg_class') where relname='test2'

 

17、添加master standby节点：

gpinitstandby -s smdw

 

18、删除master standby节点：

gpinitstandby -r

 

19、重新同步：

gpinitstandby -n

 

20、查看当前连接状况：

ps -ef|grep postgres |grep  con

 

21、查看启动时间：

select pg_postmaster_start_time();

 

22、创建外部表

在服务器上启动gpfdist：

nohup $GPHOME/bin/gpfdist -d /home/gpadmin -p 8888 > /tmp/gpfidist.log 2>&1 &

nohup $GPHOME/bin/gpfdist -d /home/gpadmin/data -p 8888 -l /home/gpadmin/data/gpfdist.log &

 

将需要加载的数据文件放在服务器的/home/gpadmin的目录或子目录下，然后创建外部表：

create external table test1(id integer,name varchar(8))

location ('gpfdist://192.168.218.126:8888/member_delta.dat')

format 'text' (delimiter ',' null as '' escape 'off')

encoding ''

log errors into member_err segment reject limit 2 rows;

 

insert into test2 select * from test1;

 

23、执行SQL文本文件

psql gpdbname -f yoursqlfile.sql

或者psql登陆后执行

\i yoursqlfile.sql

 

24、查询所有表的大小（包括分区表）

Select tabs.nspname as schema_name,

COALESCE(parts.tablename,tabs.relname) AS table_name,   

ROUND(SUM(sotaidtablesize)/1024/1024/1024,3) AS table_GB ,    

ROUND(SUM(sotaididxsize)/1024/1024/1024,3) AS index_GB    

FROM gp_toolkit.gp_size_of_table_and_indexes_disk sotd ,

(select c.oid,c.relname,n.nspname from pg_class c,pg_namespace n 

where n.oid=c.relnamespace)tabs 

LEFT JOIN pg_partitions parts ON tabs.nspname = parts.schemaname

AND tabs.relname=parts.partitiontablename where sotd.sotaidoid = tabs.oid 

GROUP BY tabs.nspname,COALESCE(parts.tablename, tabs.relname)

ORDER by 3 desc;

 

25、查询索引大小

SELECT soisize/1024/1024 as size_MB, relname as indexname

FROM pg_class, gp_toolkit.gp_size_of_index

WHERE pg_class.oid = gp_size_of_index.soioid

AND pg_class.relkind='i' order by 1;

 

26、查询表大小与索引大小

SELECT ns.nspname as namespace,pc.relname as tablename,pg_size_pretty((sotaidtablesize)::int8) tablesize,

pg_size_pretty((sotaididxsize)::int8) indexsize,to_char((pc.reltuples),'FM999,999,999,999,999') as rowcnt

FROM gp_toolkit.gp_size_of_table_and_indexes_disk tid

JOIN pg_class pc ON tid.sotaidoid=pc.oid

JOIN pg_namespace ns ON ns.oid = pc.relnamespace

LEFT JOIN gp_toolkit.gp_size_of_partition_and_indexes_disk pid

ON pid.sopaidpartitionoid = tid.sotaidoid

LEFT JOIN gp_toolkit.gp_size_of_partition_and_indexes_disk pid2

ON pid2.sopaidparentoid = tid.sotaidoid

WHERE pid.sopaidpartitionoid IS Null

AND pid2.sopaidparentoid  IS Null

UNION ---分区表信息

SELECT ns.nspname as namespace,pc2.relname as tablename,pg_size_pretty(sum(sopaidpartitiontablesize)::int8) tablesize,

pg_size_pretty(sum(sopaidpartitionindexessize)::int8) indexsize,to_char(sum(pc.reltuples),'FM999,999,999,999,999') as rowcnt

FROM gp_toolkit.gp_size_of_partition_and_indexes_disk pid

JOIN gp_toolkit.gp_size_of_table_disk td

ON pid.sopaidparentoid = td.sotdoid

JOIN pg_class pc ON pid.sopaidpartitionoid=pc.oid

JOIN pg_class pc2 ON pid.sopaidparentoid=pc2.oid

JOIN pg_namespace ns ON ns.oid = pc2.relnamespace where

GROUP BY ns.nspname, pc2.relname, sopaidparentoid::regclass

ORDER BY namespace, tablename;

 

27、查看实例文件位置、数据库版本

select hostname,fselocation from gp_segment_configuration  left outer join

pg_filespace_entry on dbid=fsedbid order by 1;

psql -c "select version()"

 

28、查看所有数据库大小

select datname,pg_size_pretty(pg_database_size(datname)) from pg_database;

 

29、查看表的隐藏字段

select n.nspname, c.relname, a.attname, a.attnum, t.typname

from pg_class c

join pg_namespace n on c.relnamespace = n.oid

join pg_attribute a on c.oid = a.attrelid

join pg_type t on a.atttypid = t.oid

where n.nspname=

and c.relname=

order by c.relname;

 

30、查看数据库中大于20G的表

select * from gp_toolkit.gp_size_of_table_and_indexes_disk where sotaidtablesize > 21474836480 order by sotaidtablesize;

 

31、查看数据的倾斜率

select gp_segment_id,count(*),abs(count(*)-avg(count(*)) over(order by gp_segment_id rows

between unbounded preceding and unbounded following))/count(*) skew 

from group by gp_segment_id  order by 1;

select * from gp_toolkit.gp_disk_free order by 1

 

32、查看表上的操作

SELECT schemaname as schema, objname as table, usename as role, actionname as action, subtype as type, statime as time，username FROM pg_stat_operations WHERE objname = '';

 

33、查看所有参数

show all

 

34、历史负载查看

psql gpperfmon

查询时间段内各时间点系统均值

select ctime,round(avg(cpu_user)::numeric,2) cpu_user,round(avg(cpu_sys)::numeric,2) cpu_sys,round(avg(mem_actual_used)/1024/1024/1024::numeric,2) mem_used,

round(avg(disk_ro_rate)::numeric,2) iops,round(avg(disk_rb_rate)/1024/1024::numeric,2) disk_r_mb,round(avg(disk_wb_rate)/1024/1024::numeric,2) disk_w_mb

from system_history where ctime > timestamp'2016-01-13 00:45:00'

and ctime < timestamp'2016-01-13 07:00:00'

and hostname <> 'smdw'

group by ctime order by 1;

 

查询前一天时间段内各时间点系统均值

select ctime,round(avg(cpu_user)::numeric,2) cpu_user,round(avg(cpu_sys)::numeric,2) cpu_sys,round(avg(mem_actual_used)/1024/1024/1024::numeric,2) mem_used,

round(avg(disk_ro_rate)::numeric,2) iops,round(avg(disk_rb_rate)/1024/1024::numeric,2) disk_r_mb,round(avg(disk_wb_rate)/1024/1024::numeric,2) disk_w_mb

from system_history,(select ctime time from system_history where ctime >= timestamp'2016-07-13 00:00:00' and ctime <= timestamp'2016-07-13 08:00:00' group by ctime order by 1) t

where hostname = 'sdw1'

and ctime=t.time - interval'1 day'

group by ctime order by 1;

 

 

查询时间段内各时间点系统峰值

select ctime,max(cpu_user) cpu_user,max(cpu_sys) cpu_sys,

max(mem_actual_used)/1024/1024/1024 mem_used,

max(disk_ro_rate) iops,max(disk_rb_rate)/1024/1024 disk_r_m

from system_history where ctime > timestamp'2016-01-13 00:45:00'

and ctime < timestamp'2016-01-13 07:00:00'

and hostname <> 'smdw'

group by ctime order by 1;

 

查询时间段系统均值

select avg(cpu_user) cpu_user,avg(cpu_sys) cpu_sys,

avg(mem_actual_used)/1024/1024/1024 mem_used,

avg(disk_ro_rate) iops,avg(disk_rb_rate)/1024/1024 disk_r_m

from system_history where ctime > timestamp'2016-01-13 10:00:00'

and ctime < timestamp'2016-01-13 12:00:00'  and hostname <> 'smdw';

 

查询时间段系统峰值

select max(cpu_user) cpu_user,max(cpu_sys) cpu_sys,

max(mem_actual_used)/1024/1024/1024 mem_used,

max(disk_ro_rate) iops,max(disk_rb_rate)/1024/1024 disk_r_m

from system_history where ctime > timestamp'2016-01-13 10:00:00'

and ctime < timestamp'2016-01-13 12:00:00'  and hostname <> 'smdw';

 

与前一天的比较

select a.time,

       abs(a.cpu_user - b.cpu_user) cpu_user_dif,

       abs(a.cpu_sys - b.cpu_sys) cpu_sys_dif,

       abs(a.mem_used - b.mem_used) mem_used_dif,

       abs(a.iops - b.iops) iops_dif,

       abs(a.disk_r_mb - b.disk_r_mb) disk_r_mb_dif,

       abs(a.disk_w_mb - b.disk_w_mb) disk_w_mb_dif

  from (select ctime time,

               round(avg(cpu_user) ::numeric, 2) cpu_user,

               round(avg(cpu_sys) ::numeric, 2) cpu_sys,

               round(avg(mem_actual_used) / 1024 / 1024 / 1024 ::numeric, 2) mem_used,

               round(avg(disk_ro_rate) ::numeric, 2) iops,

               round(avg(disk_rb_rate) / 1024 / 1024 ::numeric, 2) disk_r_mb,

               round(avg(disk_wb_rate) / 1024 / 1024 ::numeric, 2) disk_w_mb

          from system_history

         where ctime >= current_date

           and ctime <= current_timestamp

           and hostname <> 'mdw'

           and hostname <> 'smdw'

         group by ctime

         order by 1) a,

       (select ctime time,

               round(avg(cpu_user) ::numeric, 2) cpu_user,

               round(avg(cpu_sys) ::numeric, 2) cpu_sys,

               round(avg(mem_actual_used) / 1024 / 1024 / 1024 ::numeric, 2) mem_used,

               round(avg(disk_ro_rate) ::numeric, 2) iops,

               round(avg(disk_rb_rate) / 1024 / 1024 ::numeric, 2) disk_r_mb,

               round(avg(disk_wb_rate) / 1024 / 1024 ::numeric, 2) disk_w_mb

          from system_history,

               (select ctime time

                  from system_history

                 where ctime >= current_date

                   and ctime <= current_timestamp

                 group by ctime

                 order by 1) t

         where hostname <> 'mdw'

           and hostname <> 'smdw'

           and ctime = t.time - interval '1 day'

         group by ctime

         order by 1) b

 where a.time = b.time

 order by 1

 

每十分钟比较一次

select a.time,

       abs(a.cpu_user - b.cpu_user) cpu_user_dif,

       abs(a.cpu_sys - b.cpu_sys) cpu_sys_dif,

       abs(a.mem_used - b.mem_used) mem_used_dif,

       abs(a.iops - b.iops) iops_dif,

       abs(a.disk_r_mb - b.disk_r_mb) disk_r_mb_dif,

       abs(a.disk_w_mb - b.disk_w_mb) disk_w_mb_dif

  from (select t.date || ' ' || t.hour || ':' || t.minute || ':00' time,

               t.cpu_user,

               t.cpu_sys,

               t.mem_used,

               t.iops,

               t.disk_r_mb,

               t.disk_w_mb

          from (select current_date as date,

                       to_char(ctime, 'hh24') as hour,

                       substr(to_char(ctime, 'mi'), 1, 1) || '0' as minute,

                       round(avg(cpu_user) ::numeric, 2) cpu_user,

                       round(avg(cpu_sys) ::numeric, 2) cpu_sys,

                       round(avg(mem_actual_used) / 1024 / 1024 / 1024

                             ::numeric,

                             2) mem_used,

                       round(avg(disk_ro_rate) ::numeric, 2) iops,

                       round(avg(disk_rb_rate) / 1024 / 1024 ::numeric, 2) disk_r_mb,

                       round(avg(disk_wb_rate) / 1024 / 1024 ::numeric, 2) disk_w_mb

                  from system_history

                 where ctime >= current_date

                   and ctime <= current_timestamp

                   and hostname <> 'mdw'

                   and hostname <> 'smdw'

                 group by current_date,

                          to_char(ctime, 'hh24'),

                          substr(to_char(ctime, 'mi'), 1, 1) || '0') t) a,

       (select t.date || ' ' || t.hour || ':' || t.minute || ':00' time,

               t.cpu_user,

               t.cpu_sys,

               t.mem_used,

               t.iops,

               t.disk_r_mb,

               t.disk_w_mb

          from (select current_date as date,

                       to_char(ctime, 'hh24') as hour,

                       substr(to_char(ctime, 'mi'), 1, 1) || '0' as minute,

                       round(avg(cpu_user) ::numeric, 2) cpu_user,

                       round(avg(cpu_sys) ::numeric, 2) cpu_sys,

                       round(avg(mem_actual_used) / 1024 / 1024 / 1024

                             ::numeric,

                             2) mem_used,

                       round(avg(disk_ro_rate) ::numeric, 2) iops,

                       round(avg(disk_rb_rate) / 1024 / 1024 ::numeric, 2) disk_r_mb,

                       round(avg(disk_wb_rate) / 1024 / 1024 ::numeric, 2) disk_w_mb

                  from system_history

                 where ctime >= current_date - interval

                 '1 day'

                   and ctime <= current_timestamp - interval

                 '1 day'

                   and hostname <> 'mdw'

                   and hostname <> 'smdw'

                 group by current_date,

                          to_char(ctime, 'hh24'),

                          substr(to_char(ctime, 'mi'), 1, 1) || '0') t) b

 where a.time = b.time

 order by 1

 

35、所有对象信息

create or replace view csh_view_object_info as

select relname 对象名,(select nspname from pg_namespace where oid=relnamespace) 模式名,

(select typname from pg_type where oid=reltype) 行类型,(select rolname from pg_roles where oid=relowner) 属主,

(select amname from pg_am where oid=relam) 索引类型,relfilenode 磁盘文件名,

(select spcname from pg_tablespace where oid=reltablespace) 表空间,

relpages 块数,reltuples 行数,(select relname from pg_class where oid=reltoastrelid) 对应toast表,

(select relname from pg_class where oid=reltoastidxid) 对应TOAST表索引oid,relaosegrelid,relaosegidxid,

relhasindex 是否有索引,relisshared 是否全局共享,

relkind 类型,relstorage 存储类型,relnatts 字段数,relchecks 约束数,reltriggers 触发器数,

relukeys,relfkeys,relrefs,relhasoids,relhaspkey 是否有主键,relhasrules,relhassubclass,relfrozenxid,relacl,reloptions

from pg_class

 

36、greenplum dblink模块

下载postgresql支持包postgresql-8.2.23.tar.gz并上传到gp  master服务器

 

http://www.postgresql.org/ftp/source/v8.2.23/

 

修改参数

cd postgresql-8.2.23/contrib/dblink

 

vi Makefile

PG_CPPFLAGS = -I$(libpq_srcdir) -w

 

编译代码

make USE_PGXS=1 install

 

分发支持文件到各个segment节点服务器上

gpscp -f /all_hosts dblink.so =:/usr/local/greenplum-db/lib/postgresql/dblink.so

 

为本地数据库创建支持远程连接的相关函数

psql -f dblink.sql local_dbname

 

创建db_link连接

select dblink_connect('con163','host=192.168.218.163 dbname=dp_dw163 port=6432');

 

使用db_link

select * from dblink('con163','select count(*) from pg_tables') as table_num(n integer);