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;

  

10 Comments
Silver 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

Silver 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.

 

 

Silver Adventurer

hi @VibhorKumar i release a extension to monitor CPU and MEMORY  and more resources in SO, you can check in https://github.com/asotolongo/resources

Required:  PG10+, file_fdw extension and linux SO

Regards

 

 

EDB Team Member

Hi asotolongo,

 

I have tried to install resources extension from your GitHub code for testing purpose. However, I have received below error.

postgres=# CREATE EXTENSION resources;
ERROR: invalid option "program"
HINT: Valid options in this context are: filename, format, header, delimiter, quote, escape, null, encoding

 

Please let me know if I am doing anything wrong?

 

Disclaimer: We cannot certify or guarantee what the tested extension results.
 

Silver Adventurer

hi @dhananjayjejur, did you try in PG10+? , the program option in file_fdw is new in PG 10, the extension function in PG+

 

regards

EDB Team Member

Hi asotolongo,

 

Thank you for the help. I have tested package on my environment. And it looks good and useful to me.

However, over the small test, I have found that if we execute the multiple instances of the v_ps_postgres view gives us the same results for every second run.

 

Hope this helps in your further development.

 

Disclaimer: We cannot certify or guarantee what the tested extension results. 

Silver Adventurer

 @dhananjayjejur i will check that, thanks

I will comment later

regards

Silver Adventurer

hi again @dhananjayjejur, can you test again, i fix something

here the youtube video to see the change in %  view(resources.v_ps_postgresv2) 

 

https://youtu.be/hVunkY1tc3U

 

thanks and regards