'Too many connections' created in postgres when creating a dashboard in Pentaho

Question

I was creating a Dashboard in Pentaho PUC which uses a postgres connection as the data source. Most of the time this causes the postgres to say

Too many clients already in Postgres'

SHOW max_connections; Query shows maximum connections of 200

I used this query select * from pg_stat_activity;. From that 90% of connections are from the Pentaho server to the database I use as the datasource in my new dashboard. waiting is f and state is idle in most of the connections. This looks like Pentaho is creating too many connections. How can I limit or Control it? I have already tried increasing connection limit from default 100 to 200 from postgres side but still the issue is there.

@wingedpanther I don't control the data sources. Its automatically done by Pentaho. I created one data source there and keep using that thats all. — Marlon Abeykoon, May 18 '15 at 06:06
I also got issue when I connect to database.like "Error connecting to database: (using class org.postgresql.Driver) FATAL: sorry, too many clients already" — Pirinthan, May 18 '15 at 06:13
just go through http://stackoverflow.com/questions/9798705/arval-sqlexception-fatal-sorry-too-many-clients-already-in-postgres — Vivek S., May 18 '15 at 06:14
@wingedpanther Yes I already checked it and thts why I increased to 200. But still one of the answers provided by Erwin Brandstetter there states that increasing max connection is not a solution. But I will try increasing it to 500 as you said. — Marlon Abeykoon, May 18 '15 at 06:52
@wingedpanther thanks I will try this when we get a down time tonight — Marlon Abeykoon, May 18 '15 at 06:55
are you using a connection Pool on Pentaho's data source definitions? — nsousa, May 18 '15 at 10:16
create a connection pool. Set up a JNDI connection and give it enough connections in the pool. That way only 1 connection is established with the database and all queries share the pool. Otherwise, each query opens a new connection with the same credentials and you run out of connection slots on the DB. — nsousa, May 26 '15 at 08:31
@nsousa Could you explain the said method how to do it as an answer? I will accept it as the correct answer if it helps. Thanks — Marlon Abeykoon, May 30 '15 at 03:42

score 3 · Accepted Answer · answered May 31 '15 at 10:49

From the comments thread on the original question it seems you're using SQL over JDBC connections on your dashboard. This will create a different database connection for each query that needs to run and if they are somewhat slow you may reach the limit on the number of concurrent connections.

Instead, you should set up a JNDI: on your datasource management window add a new connection and set up the correct credentials. Under advanced options set up a connection pool. Give it a meaningful name. From that point on, you should refer to that name on your dashboard queries and use SQL over JNDI instead of SQL over JDBC. This way each SQL query will get a connection from the connection pool and the DB only sees 1 connection at each time, despite running multiple queries.

'Too many connections' created in postgres when creating a dashboard in Pentaho

1 Answers1

Linked