GP tips - 9dian/Index GitHub Wiki

 select fsloc.dbid, fsloc.fselocation || '/' ||
                   case when db.dattablespace = 1663
                      then 'base'
                      else db.dattablespace::text
                   end || '/'||db.oid as catloc
            from pg_Database db, pg_tablespace ts,
                 (SELECT dbid, fs.oid, fselocation
                  FROM pg_catalog.gp_segment_configuration
                  JOIN pg_catalog.pg_filespace_entry on (dbid = fsedbid)
                  JOIN pg_catalog.pg_filespace fs on (fsefsoid = fs.oid)) fsloc
                  where db.dattablespace = ts.oid
                  and ts.spcfsoid = fsloc.oid

SELECT pg_namespace.nspname, pg_proc.proname 
FROM pg_proc, pg_namespace 
WHERE pg_proc.pronamespace=pg_namespace.oid 
   AND pg_proc.proname LIKE '%dblink%';

select a.dbid,a.content,a.role,a.port,a.hostname,b.fsname,c.fselocation from gp_segment_configuration a,pg_filespace b,pg_filespace_entry c 
where a.dbid=c.fsedbid and b.oid=c.fsefsoid 
and c.fselocation  ~ '/data/data5/.*/gpseg30.*' --or c.fselocation  ~ '/data/data5/.*/gpseg28.*'
order by content

select dbid,content,role,preferred_role,hostname,port from gp_segment_configuration order by role,dbid;
select a.dbid,a.content,a.role,a.port,a.hostname,b.fsname,c.fselocation from gp_segment_configuration a,pg_filespace b,pg_filespace_entry c where a.dbid=c.fsedbid and b.oid=c.fsefsoid order by content;
select oid, * from pg_filespace
select oid, * from pg_tablespace;
select * from pg_filespace_entry

select c.relname, c.reltablespace, tbs.spcname from pg_class c inner join pg_tablespace tbs on c.reltablespace = tbs.oid where relkind = 'r';


SELECT locktype, database, c.relname, l.relation,
l.transactionid, 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;

网络及带宽工具 nload

nethogs

iperf -s -w 32768

iperf -c server-host -w 1m -P 10 -i 10 -t 30

监控工具： dstat

4TB 7.2K RPM NLSAS 512n 3.5英寸热插拔硬盘