TransWikia.com

Why are my queries in idle state?

Database Administrators Asked by user6826691 on November 5, 2021

I’m new to postgres , I’ve aws rds instance running postgresql with engine version 11.5.

All my queries are clientRead has the wait_event. Why do I have all my queries in idle status.Does this mean they are idle in transaction?

What are the steps should I take to fix this?

If I change to idle_in_transaction_session_timeout to 10 mins for example will it fix this?

select count(*),state FROM pg_stat_activity group by 2;
 count | state
-------+--------
     5 |
     1 | active
   451 | idle


Select pid, datname, usename, wait_event_type, wait_event, backend_type FROM pg_stat_activity where state='idle';
  pid  | datname  |         usename          | wait_event_type | wait_event |  backend_type
-------+----------+--------------------------+-----------------+------------+----------------
 14797 | xxxxx    | user                     | Client          | ClientRead | client backend


SELECT current_setting('idle_in_transaction_session_timeout');
 current_setting
-----------------
 1d
(1 row)

One Answer

Your session is in idle state, not the query.

Sessions that are idle are not a problem. It simply means the backend is waiting for the client to send a SQL query.

The number of sessions is a little bit on the high side though. You will need to have a really powerful server to cope with that, once all of them start doing something.

Sessions that are idle in transaction would be a problem if they stayed in that state for too long. That's what the idle_in_transaction_session_timeout handles, not "idle" sessions.

Answered by a_horse_with_no_name on November 5, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP