Postgresql – Detect bottleneck of server via psycopg2

performancepostgresql

I have PostgreSQL superuser called "admin" which can connect to a PostgreSQL database.

I am not managing the database server. I have no root-access to this machine.

Sometimes the performance is not good.

Up to now I have no numbers, but I would like to have some.

Is there a (common) way to do small benchmarks on the production db to see how it performs?

I have something like this on my mind: Every ten minutes some magic sql commands get execute which give me some numbers. I would like to see CPU/MEM/IO usage/performance.

The same queries lead to different performance. I guess the hypervisor hosting the PostgreSQL VM is overloaded from time to time

The current situation is not nice for me, since the customer sees the slow application, and blames me. But it is not me, it is the slow DB.

How can I proof that the DB is slow, and not the application?

(I guess this situation is common. How to do handle this?)

Best Answer

If you have access to PostgreSQL superuser account, then you can run arbitrary OS commands on the server as the OS user who is running the postgres server. For example:

create temp table top (x text);
copy top from program 'top -b -n1';
select x from top limit 25;

However, if you were intentionally not given direct shell access to this server, whoever is running the server could consider this a hostile action. It would probably be better to ask whoever is in charge to set up some OS-level monitoring solution, or explain to you any existing ones.

Related Question