How do I debug the following error org.postgresql.util.PSQLException. message: FATAL: connection limit exceeded for non-superusers?

Mariana_Cedica

Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126) at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52) at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449) at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167) at org.hibernate.jdbc.AbstractBatcher.prepareSelectStatement(AbstractBatcher.java:145) at org.hibernate.id.SequenceGenerator.generate(SequenceGenerator.java:96) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:122) at org.hibernate.ejb.event.EJB3PersistEventListener.saveWithGeneratedId(EJB3PersistEventListener.java:49) at org.hibernate.event.def.DefaultPersistEventListener.entityIsTransient(DefaultPersistEventListener.java:154) at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:110) at org.hibernate.event.def.DefaultPersistEventListener.onPersist(DefaultPersistEventListener.java:61) at org.hibernate.impl.SessionImpl.firePersist(SessionImpl.java:646) at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:620) at org.hibernate.impl.SessionImpl.persist(SessionImpl.java:624) at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:220) ... 14 more Caused by: org.postgresql.util.PSQLException: FATAL: connection limit exceeded for non-superusers at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:469) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:112)

slacoin_Lacoin

You've got in trouble with the database connections. That's mean that you should start to monitor the connection pools of your server. This is do-able by just adding a new bundle in your runtime nuxeo-core-management-jtajca and activate the remote JMX monitoring in your nuxeo.conf.

Once the bundle is installed, you should inspect the following resources using your JMX browser :

• Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource
• org.nuxeo:name=ConnectionMonitoring,type=service

As an example, using the command line tool jmx term, the following command will figure out how much connections is currently used by your server.

$ java -jar jmxterm-1.0-alpha-4-uber.jar -l localhost:1089 -n <<!
bean Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource
get numActive
get numIdle
get maxActive
get maxIdle
bean org.nuxeo:name=ConnectionMonitoring,type=service
get ConnectionCount
get IdleConnectionCount
get PartitionCount
get PartitionMaxSize
!

The ouput will be something like

#bean is set to Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource
#mbean = Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource:
numActive = 0;

#mbean = Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource:
maxActive = 100;

#mbean = Catalina:class=javax.sql.DataSource,name="jdbc/nuxeo",type=DataSource:
numIdle = 1;

#bean is set to org.nuxeo:name=ConnectionMonitoring,type=service
#mbean = org.nuxeo:name=ConnectionMonitoring,type=service:
ConnectionCount = 1;

#mbean = org.nuxeo:name=ConnectionMonitoring,type=service:
IdleConnectionCount = 1;

#mbean = org.nuxeo:name=ConnectionMonitoring,type=service:
PartitionCount = 1;

#mbean = org.nuxeo:name=ConnectionMonitoring,type=service:
PartitionMaxSize = 20;

By summing all the counters, we see that our server is currently using 3 connections at all. Also, we know now that the maximum connection our server may open is 120.

Looking deeper in the stack trace, we can see that the error comes from the database itself. That means that you've configured your app server for more connections than what it's available on the database.

You can check what is configured on your database server by executing the following command line :

$ /usr/local/bin/check_postgres.pl --warning='80%' --critical='90%' \
  -H localhost -p 5433 --action=backends --dbname=nuxeo \
  --dbuser=monitor --dbpass=monitor://nx

The output will be something like

 3 of 100 connections (3%) | time=0.08  'nuxeo'=5;80;90;0;100 \
 'postgres'=0;80;90;0;100 'template0'=0;80;90;0;100 'template1'=0;80;90;0;100

So, there is a mismatch of 20 connections. You should always allocate more connection on your database server than what you're using on your app server.

As this stage, you have two options :

• reconfigure nuxeo for using less connections, just read the nuxeo configuration parameters documentation for the pools.
• allocate more connections on your postgresql server (I let you investigate this option by reading the postgresql administration documentation).