cancel
Showing results for 
Search instead for 
Did you mean: 

Monitor CPU and MEMORY Percentage Used By Each Process In PostgreSQL/PPAS 9.1

Explorer

07/22/2012

 

PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives the following information:

1. datid: database OID
2. datname: database name
3. procpid: process ID
4. usesysid: user OID 
5. usename: user name
6. application_name: application name
7. client_addr: client's address
8. client_hostname: host name (if available)
9. client_port: Clients port number
10. backend_start: time at which the server process started
11. xact_start: time at which current transaction started
12: query_start: time at which current query began execution started
13: waiting:  process's waiting status
14. current_query: text of the current query.

Above is good for monitoring sessions in postgresql. However, suppose user wants to know about percentage of CPU & Memory used by a particular session/user, then he can get this information by using plperlu function.

To Create the pleperlu function, user needs to have plperlu language installed in DB. For creating the plperlu, connect to Database as super user and execute following command:

edb=# CREATE EXTENSION plperlu;
CREATE EXTENSION

Now create following functions:

CREATE OR REPLACE FUNCTION get_pid_cpu_mem(int) returns table(PID INT,CPU_perc float,MEM_perc float) 
as
$$
  my $ps = "ps aux";
  my $awk = "awk '{if (\$2==".$_[0]."){print \$2\":\"\$3\":\"\$4}}'";
  my $cmd = $ps."|".$awk;
  $output = `$cmd 2>&1`;
  @output = split(/[\n\r]+/,$output);
  foreach $out (@output)
  { 
    my @line = split(/:/,$out);
    return_next{'pid' => $line[0],'cpu_perc' => $line[1], 'mem_perc' => $line[2]};
    return undef;
  }
   return;
 $$ language plperlu;

Note:: Above function is made for PostgreSQL running on Linux/Unix System.

Now user can use above function with pg_stat_activity to monitor the percentage of cpu and memory used by particular user/process.

Following is one snapshot:

edb=# select procpid,usename,application_name, get_pid_cpu_mem(procpid).cpu_perc,get_pid_cpu_mem(procpid).mem_perc,current_query from pg_stat_activity;
-[ RECORD 1 ]----+--------------------------------------------------------------------------------------------------------------------------------------------------
procpid          | 12991
usename          | enterprisedb
application_name | psql
cpu_perc         | 0
mem_perc         | 0.6
current_query    | select procpid,usename,application_name, get_pid_cpu_mem(procpid).cpu_perc,get_pid_cpu_mem(procpid).mem_perc,current_query from pg_stat_activity;

  

4 Comments
Level 3 Adventurer

I had to do the same, but only with plpgsql, and I am building an extension for more details

Part of my solution:

 

 

CREATE SCHEMA db_resources;

CREATE TABLE db_resources._ls_ps_postgres
(
  users character varying,
  pid integer,
  cpu_percent double precision,
  mem_percent double precision,
  command character varying
);


CREATE OR REPLACE FUNCTION db_resources._cuts(_text text)
  RETURNS character varying AS
$BODY$
BEGIN
return ltrim(substring($1 from position(';' in $1) for length($1)),';');
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



-- Function: db_resources.ls_ps_postgres()

-- DROP FUNCTION db_resources.ls_ps_postgres();

CREATE OR REPLACE FUNCTION db_resources.ls_ps_postgres()
  RETURNS SETOF db_resources._ls_ps_postgres AS
$BODY$
DECLARE
result text;
users text;
pid text ;
cpu_percent  text ;
mem_percent text ;
command text;
 
BEGIN
--create temporal table to insert result of program in copy 
CREATE TABLE db_resources._ls_ps_postgres_tmp (texts text);
--clean the table
TRUNCATE db_resources._ls_ps_postgres;

COPY db_resources._ls_ps_postgres_tmp FROM PROGRAM 'ps -eo uname,pid,pcpu,pmem,args | grep postgres | sed "s/\ /;/g"' ; 

--process the text of temporal table
FOR result IN SELECT * FROM db_resources._ls_ps_postgres_tmp  LOOP
  --users
  users:= substring(result from 0 for position(';' in result));
  IF users='postgres' THEN 
   result:= db_resources._cuts(result);
   --pid
   pid:= substring(result from 0 for position(';' in result));
   result:= db_resources._cuts(result);
   --cpu_percent
   cpu_percent:= substring(result from 0 for position(';' in result));
   result:= db_resources._cuts(result);
   --mem_percent
   mem_percent= substring(result from 0 for position(';' in result));
   result:= db_resources._cuts(result);
   result:=replace(result,';',' ');
   --command
   command:= substring(result from 0 for  length(result)+1);


    --not insert the process of function
    IF command not like '%ps %' and command not like  '%uname,pid,pcpu,pmem,args%' and command not like '%grep postgres%' and command not like '%sed %' THEN  

    INSERT INTO db_resources._ls_ps_postgres VALUES (users,pid::int,cpu_percent::double precision ,mem_percent::double precision,command);
    END IF;   
  END IF;

END LOOP;

DROP TABLE db_resources._ls_ps_postgres_tmp ;
return QUERY SELECT * FROM db_resources._ls_ps_postgres;
END;
$BODY$
  LANGUAGE plpgsql ;

CREATE OR REPLACE VIEW db_resources.ls_ps_postgres AS 
 SELECT ls_ps_postgres.users,
    ls_ps_postgres.pid,
    ls_ps_postgres.cpu_percent,
    ls_ps_postgres.mem_percent,
    ls_ps_postgres.command
   FROM db_resources.ls_ps_postgres() ;

execute teh view call with pg_stat_activity:

 

 

 

select ps.*,ac.query from  db_resources.ls_ps_postgres ps  join pg_stat_activity ac  using (pid)

and the result :db_resources.png

 

 

When I have more time public the extension ;-)

 

regards

 

Explorer

Very nice! 

The only problem I can see is that if monitoring tool is going to use ls_ps_postgres function, then it may result in bloating the catalog table. Mainly due to CREATE/DROP commands in function

Level 3 Adventurer

Thank you for your note
And if the _ls_ps_postgres_tmp table is not always created and destroyed and  I do is truncate the data at the beginning of the function. the table  _ls_ps_postgres_tmp is permanet, Would it be the same bloating catalog table?

 

something like this:

 

CREATE SCHEMA db_resources;

CREATE TABLE db_resources._ls_ps_postgres
(
  users character varying,
  pid integer,
  cpu_percent double precision,
  mem_percent double precision,
  command character varying
);

CREATE TABLE db_resources._ls_ps_postgres_tmp (texts text);


CREATE OR REPLACE FUNCTION db_resources._cuts(_text text)
  RETURNS character varying AS
$BODY$
BEGIN
return ltrim(substring($1 from position(';' in $1) for length($1)),';');
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;



-- Function: db_resources.ls_ps_postgres()

-- DROP FUNCTION db_resources.ls_ps_postgres();

CREATE OR REPLACE FUNCTION db_resources.ls_ps_postgres()
  RETURNS SETOF db_resources._ls_ps_postgres AS
$BODY$
DECLARE
result text;
users text;
pid text ;
cpu_percent  text ;
mem_percent text ;
command text;
 
BEGIN

--clean the table
TRUNCATE db_resources._ls_ps_postgres;
TRUNCATE db_resources._ls_ps_postgres_tmp;
COPY db_resources._ls_ps_postgres_tmp FROM PROGRAM 'ps -eo uname,pid,pcpu,pmem,args | grep postgres | sed "s/\ /;/g"' ; --process the text of temporal table FOR result IN SELECT * FROM db_resources._ls_ps_postgres_tmp LOOP --users users:= substring(result from 0 for position(';' in result)); IF users='postgres' THEN result:= db_resources._cuts(result); --pid pid:= substring(result from 0 for position(';' in result)); result:= db_resources._cuts(result); --cpu_percent cpu_percent:= substring(result from 0 for position(';' in result)); result:= db_resources._cuts(result); --mem_percent mem_percent= substring(result from 0 for position(';' in result)); result:= db_resources._cuts(result); result:=replace(result,';',' '); --command command:= substring(result from 0 for length(result)+1); --not insert the process of function IF command not like '%ps %' and command not like '%uname,pid,pcpu,pmem,args%' and command not like '%grep postgres%' and command not like '%sed %' THEN INSERT INTO db_resources._ls_ps_postgres VALUES (users,pid::int,cpu_percent::double precision ,mem_percent::double precision,command); END IF; END IF; END LOOP; return QUERY SELECT * FROM db_resources._ls_ps_postgres; END; $BODY$ LANGUAGE plpgsql ; CREATE OR REPLACE VIEW db_resources.ls_ps_postgres AS SELECT ls_ps_postgres.users, ls_ps_postgres.pid, ls_ps_postgres.cpu_percent, ls_ps_postgres.mem_percent, ls_ps_postgres.command FROM db_resources.ls_ps_postgres() ;
Explorer

Frequent truncate will bloat the pg_class catalog table. However, this bloat is less than CREATE/DROP table

Also, please note, it could be possible when you are gathering information, for some processes, information may not be consistent. Specially processes which finished during COPY command in the function and new started processes.