IF you think postgres performance is the cause of an application performing badly these sql statements may help
Long Running Transactions
The statement below shows the current running sessions and how long they’ve been running with.
SELECT pid, now() - pg_stat_activity.query_start AS duration, query, state FROM pg_stat_activity WHERE state != 'idle' order by duration desc;
you can further troubleshoot what these queries are doing by using the pg_top utility
Use the ‘Q’ key and enter a PID of the statement you’re interested in to get more information.
Temporary data such as sort and temporary tables are handled very differently in postgres. Mostly this is done in memory and not on disk. A section of memory is allocated to each session for this. It’s called the work_mem and can be increased if performance is poor. When the memory set is too small it writes temp file but this is expensive.
See work_mem in https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
This is simple In postgres but adds load and generates a lot of data which has to be analyzed manually as far as I am aware.
Here’s the procedure.
Find –name postgresql.conf
Back it up
Edit the postgresql.conf to update these entries
track_activities = on
track_activity_query_size = 6000
log_statement_stats = on
log_statement = ‘all’
reload the postgres instance
/opt/vmware/vpostgres/current/bin/pg_ctl reload –U secureall -D /common/db/vshield
The values of the-U and –D commands will be differnet you can get these from
By doing a ps –ef | grep postgres you’ll be able to see the values for –U and –D
Verify logs have verbose information. These logs will be postgresql.log or server,log.
To roll back the change just replace the postgresql.conf with the original and reload again.