greenplum中在master上查看底层所有节点的sql语句

 

greenplum是一个分布式数据库，底下有很多的postgresql的数据库，我们有时候需要知道底层节点在干些什么，能否直接在master上有一个视图，或者sql，可以看到底下每个节点的sql，并且可以标识出是哪一个机器，哪一个端口的数据库，下面介绍方法：

3.3跟4.0的架构有所改变，所以方法也不一样。

1.创建v_active_sql视图方便查看sql：

CREATE VIEW v_active_sql AS

SELECT pg_stat_activity.procpid, pg_stat_activity.sess_id, pg_stat_activity.usename, pg_stat_activity.waiting AS w, to_char(pg_stat_activity.query_start, ‘mm-dd hh24:mi:ss’::text) AS query_start, to_char(now() – pg_stat_activity.query_start, ‘hh24:mi’::text) AS exec, pg_stat_activity.current_query

FROM pg_stat_activity

WHERE pg_stat_activity.current_query <> ‘<IDLE>’::text

ORDER BY pg_stat_activity.datname, to_char(pg_stat_activity.query_start, ‘yyyymmdd hh24:mi:ss’::text);

Gp3.3:

CREATE or replace FUNCTION public.hostname(a text)

RETURNS text

AS $$

import socket

return socket.gethostname()

$$ LANGUAGE plpythonu;

查询每个节点的sql：

select hostname(current_query), current_setting(replace(’port’||current_query,current_query,”)),* from gp_dist_random(’v_active_sql’) where current_query  not like ‘%IDLE%’;

Gp4.0:

1.创建hostname获取hostname

CREATE or replace FUNCTION public.hostname()

RETURNS text

AS $$

import socket

return socket.gethostname()

$$ LANGUAGE plpythonu;

2.创建v_sql视图：

create VIEW v_sql AS select hostname() as hostname ,current_setting(’port’) as port,* from v_active_sql;

3.创建get_sql函数：

CREATE OR REPLACE FUNCTION get_sql(a text)

returns setof v_sql

security definer

as

$$

return plpy.execute(”SELECT hostname() as hostname ,current_setting(’port’) as port,pg_stat_activity.procpid, pg_stat_activity.sess_id, pg_stat_activity.usename, pg_stat_activity.waiting AS w, to_char(pg_stat_activity.query_start, ‘mm-dd hh24:mi:ss’::text) AS query_start, to_char(now() – pg_stat_activity.query_start, ‘hh24:mi’::text) AS exec, pg_stat_activity.current_query/

FROM pg_stat_activity/

WHERE pg_stat_activity.current_query <> ‘<IDLE>’::text/

ORDER BY pg_stat_activity.datname, to_char(pg_stat_activity.query_start, ‘yyyymmdd hh24:mi:ss’::text)”);

$$ language plpythonu;

4.

CREATE VIEW all_seg_sql AS select (t.get_sql).* from (select get_sql(gpname::text) from gp_dist_random(’gp_id’))t;

最后的效果大概如下：

aligputf8=# select * from all_seg_sql;

hostname | port  | procpid | sess_id | usename | w |  query_start   | exec  |       current_query

———-+——-+———+———+———+—+—————-+——-+—————————-

hadoop4  | 30000 |   14101 |      84 | gpadmin | f | 03-31 18:44:16 | 00:00 | select * from all_seg_sql;

hadoop4  | 30001 |   14103 |      84 | gpadmin | f | 03-31 18:44:16 | 00:00 | select * from all_seg_sql;

hadoop5  | 30000 |   10822 |      84 | gpadmin | f | 03-31 18:44:16 | 00:00 | select * from all_seg_sql;

hadoop5  | 30001 |   10824 |      84 | gpadmin | f | 03-31 18:44:16 | 00:00 | select * from all_seg_sql;

hadoop6  | 30000 |   16492 |      84 | gpadmin | f | 03-31 18:44:16 | 00:00 | select * from all_seg_sql;

hadoop6  | 30001 |   16494 |      84 | gpadmin | f | 03-31 18:44:16 | 00:00 | select * from all_seg_sql;

(6 rows)