0

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?)

  • 1
    How do you know it's the database server and not your application? – a_horse_with_no_name Jun 25 at 12:23
  • "I have no root-access to this machine." So, what kind of access do you have? – jjanes Jun 25 at 12:43
  • @a_horse_with_no_name I guess that it is the server hosting the database since the same queries are sometimes fast, sometimes slow. – guettli Jun 25 at 14:23
  • @jjanes I can connect to the db via the postgres-protocol (psycopg2 in my case). – guettli Jun 25 at 14:23
  • Are the queries identical (either they have no parameters, or always run with identical parameters)? Different parameters can lead to very different performance. And of course if the database is not static, different data also leads to different performance. – jjanes Jun 25 at 16:24
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.