Monitor CPU and MEMORY percentage used by each process in PostgreSQL/PPAS 9.1
PostgreSQL has pg_stat_activity view which can be use to get the session details. This view gives following information:
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:
Now create following functions:
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:
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/PPAS 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;
Comments
Post a Comment