Performance issue debugging

On Thu, Feb 22, 2024 at 4:03 PM veem v <veema0000@gmail.com> wrote:

Hi All,
As i understand we have pg_stats_activity which shows the real time
activity of sessions currently running in the database. And the
pg_stats_statement provides the aggregated information of the historical
execution of all the queries in the database. But I don't see any sampling
or timing information in those views. For example at a certain point in
time in the past , what queries were getting executed in the database and
overall wait events etc.

So is there any other view which provides such information to dig into the
past to diagnose any historical performance issues ? or should we create
our own table and flush the information from the pg_stats_activity view to
that with the current timestamp and that would be helpful in analyzing
performance issues or any extension available for such?

Look at the auto_explain setting. Taking a random interval snapshot of
running queries likely will not teach you anything useful.

Also even the explain analyze can only provide the exact run time stats of
a completed query. If we want to see what's going on for a long running
query and at what step in the execution path the query is spending most
resources and time when it keeps running in the database, is there any
available option in postgres database? for e.g. in a SELECT query index
access path if taking most of the time OR in an INSERT query INDEX block is
causing contention while inserting data into the table , how would we be
able to find that for a currently running query or a for a historical query?

You can see locking contention in the pg_locks table. In my experience I
rarely ever saw anything in there even when I was cranking tens of millions
of inserts and updates per day. I don't think there's anything for
historical queries or to probe anything more about a currently running
query's progress.

Take some time to think about your queries and how you can reduce any
locking they need. If you do need some locking, consider using the FOR
UPDATE clause in SELECT to limit what you do lock.

On Fri, 23 Feb, 2024, 2:54 am Vick Khera, <vivek@khera.org> wrote:

On Thu, Feb 22, 2024 at 4:03 PM veem v <veema0000@gmail.com> wrote:

Hi All,
As i understand we have pg_stats_activity which shows the real time
activity of sessions currently running in the database. And the
pg_stats_statement provides the aggregated information of the historical
execution of all the queries in the database. But I don't see any sampling
or timing information in those views. For example at a certain point in
time in the past , what queries were getting executed in the database and
overall wait events etc.

So is there any other view which provides such information to dig into
the past to diagnose any historical performance issues ? or should we
create our own table and flush the information from the pg_stats_activity
view to that with the current timestamp and that would be helpful in
analyzing performance issues or any extension available for such?

Look at the auto_explain setting. Taking a random interval snapshot of
running queries likely will not teach you anything useful.

Also even the explain analyze can only provide the exact run time stats
of a completed query. If we want to see what's going on for a long running
query and at what step in the execution path the query is spending most
resources and time when it keeps running in the database, is there any
available option in postgres database? for e.g. in a SELECT query index
access path if taking most of the time OR in an INSERT query INDEX block is
causing contention while inserting data into the table , how would we be
able to find that for a currently running query or a for a historical query?

You can see locking contention in the pg_locks table. In my experience I
rarely ever saw anything in there even when I was cranking tens of millions
of inserts and updates per day. I don't think there's anything for
historical queries or to probe anything more about a currently running
query's progress.

Take some time to think about your queries and how you can reduce any
locking they need. If you do need some locking, consider using the FOR
UPDATE clause in SELECT to limit what you do lock.

Thank you. Actually in Oracle Database there used to be a view
(v$active_session_history)which used to collect the stats of session
activity in certain time period and from that we used to be able to track
and debug historical performance issues at exact point in time. So I was
thinking, if such thing not available by default in postgres, and if we can
do it manually and that will be helpful in long-term.