Database Principle Differences
After the session idle time exceeds sesseion_time, the database actively disconnects and the client initiates the request again, this type of error is reported;
When a session connection does not execute SQL or activity for a long period of time, the session is released and the cache can be freed to avoid problems such as OOM.
In Oracle, the default connection session never times out, in Mysql, the default connection session times out in 8 hours, and in GaussDB, the default timeout is 30min, postgresql 14 has a new idle_session_timeout parameter, which is used to control the idle session connection timeout time.
problem screening
Confirmation of the validity of the visit
Way 1: Query the audit log, you can see that the session exit method is timeout exit:
select time,type,result,client_conninfo,detail_info from pg_query_audit('2023-06-06 13:56:00','2023-06-06 14:56:00') where database='xxx' and username='xxx';
time | type | result | client_conninfo | detail_info
------------------------+---------------+--------+--------------------------------------+----------------------------------------------------------------------
2023-06-06 | login_success | ok | [unknown]@...* | login success,the current user is:, SSL=off
2023-06-06 | user_logout | ok | PostgreSQL JDBC Driver@...*** | session timeout, logout db(**) success
(4 rows)
Way 2: Query the DN log to see:
dn_6001 0 [BACKEND] LOG: close session : 978843 for 1 times due to session timeout : 180, max finish time is 754196672463385. But now is:754196673078048
Way 3: Query the pg_stat_activity view while the application is running, and observe if the query has any live probing statements
gaussdb=# select datname,usesysid,usename,application_name,client_addr,query_start,waiting,state,query,connection_info from pg_stat_activity where datname='xxx';
Common questions are:
druid is configured to explore live or explore live does not take effect, the database session_timeout default 30min timeout automatically disconnect, druid connection pool is not aware of continue to use the link will throw an exception;
Problem solutions
The application uses druid connection pooling, add the following configuration to keep the connection pool connection alive.
#Prior to version 1.0.27, it is recommended to use TestWhileIdle to ensure the validity of the connection
test-while-idle: true
#After version 1.0.28, it is recommended to use keepAlive to keep the connection alive.
keep-alive: true
validation-query: SELECT 1
The application uses tomcat connection pooling, add the following configuration to keep the connection pool connection alive.
The number of milliseconds to hibernate between runs of the #idle connection verification/cleanup thread. This value should not be set below 1 second. It determines how often we check for idle connections, drop connections, and how often we verify idle connections. It can be left unconfigured and defaults to 5s.
timeBetweenEvictionRunsMillis: 60000
# Indicates whether an object will be validated by the idle object evictor (if any). If an object fails validation, it will be removed from the pool.
test-while-idle: true
The application uses hikari connection pooling, add the following configuration to keep the connection pool connection alive.
#The lifetime (in milliseconds) of a connection will be released if it times out and is not used, default:30 minutes, recommended to set it to 60 seconds less than the database timeout.
-lifetime=1740000