Bilel Khaled

How to see Amazon RDS (postgres) connection limit?

select * from pg_settings where name='max_connections';

for t2.micro for example we have 87 max connexion

for large.micro 522 i think xD

you have always try t close hibernate session if yu use it to make connexion to databases in order to do some (insertions , reading …)

 

How too close all connexions to your database ? 

u can open your psql terminal or your  mysql client like Pgadmin select your database and fire this command :

 

SELECT 
 pg_terminate_backend(pid) 
FROM 
 pg_stat_activity 
WHERE 
 -- don't kill my own connection!
 pid <> pg_backend_pid()
 -- don't kill the connections to other databases
 AND datname = 'mydatabasename'
 ;

How to kill all connexion (my connexion included)

select pg_terminate_backend(pid)
from pg_stat_activity
where datname = 'doomed_database'

How to kill all connexion (my connexion included and all other connxeion to other database)

select pg_terminate_backend(pid)
 from pg_stat_activity

How to select list of active connections to a PostgreSQL database ?

SELECT * FROM pg_stat_activity;

 

 

Problem 

each time we fire a request to read write .. to interact with database we open a connexion , we can explose and depass the limit of our database connexion wich is limited for t2.micro fr example to 87 connexion .

when we get an object from our database we i’ve used hibernate ( create factory session ,session fire the read request commit, close session )

but the problem that connexion is not closed after the close()

Solution (2 ways)

 

First way 

adding to my hibernate.cfg.xml

this property wich is an Hibernate’s own connection pooling algorithm is, however, quite rudimentary

A)    <property name="connection.pool_size">10</property>

second way

Just replace the hibernate.connection.pool_size property with connection pool specific settings. This will turn off Hibernate’s internal pool. For example, you might like to use c3p0. or any other third party

so A) became

 <property name="hibernate.c3p0.min_size">5</property>
  <property name="hibernate.c3p0.max_size">20</property>
  <property name="hibernate.c3p0.timeout">300</property>
  <property name="hibernate.c3p0.max_statements">50</property>
  <property name="hibernate.c3p0.idle_test_period">3000</property>

ressource :https://www.mkyong.com/hibernate/how-to-configure-the-c3p0-connection-pool-in-hibernate/