Skip to content
Announcing BigAnimal: Fully managed PostgreSQL in the Cloud

Monitor CPU and MEMORY percentage used by each process in PostgreSQL/PPAS 9.1

Vibhor Kumar7/23/2012

PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives 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;

Now create following functions:

CREATE OR REPLACE FUNCTION get_pid_cpu_mem(int) returns table(PID INT,CPU_perc float,MEM_perc float) 
  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;
 $$ 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;

Enterprise-ready Postgres tools for high availability, monitoring, and disaster recovery. Download Now.

Filed under: Plperl, Postgres Plus Advanced Server, PostgreSQL, PostgreSQL DBA SQLs, PPAS Tagged: Advanced Server, information code, Monitor, plperl, Post, PostgreSQL, PostgreSQL 9.1

Vibhor Kumar is Vice President, Performance Engineering at EDB, with 12+ years of leadership experience in designing innovative business solutions for customers. He leads the performance engineering team at EDB.