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);