'在Pentaho中创建仪表板时在postgres中创建的连接数过多 [英] 'Too many connections' created in postgres when creating a dashboard in Pentaho

问题描述

我在Pentaho PUC中创建了一个使用postgres连接作为数据源的Dashboard。大多数时候，这会导致postgres说

Postgres中已经有太多客户端

SHOW max_connections; 查询显示最大连接数为200

我使用这个查询 select * from pg_stat_activity; 。从这90％的连接是从Pentaho服务器到数据库，我用作我的新仪表板中的数据源。 等待为 f 和状态为 idle 在大多数连接。这看起来像Pentaho创建太多的连接。我如何限制或控制呢？我已经尝试增加连接限制从默认100到200从postgres侧，但仍然有问题。

解决方案

线程上原来的问题似乎你在你的仪表板上使用SQL over JDBC连接。这将为每个需要运行的查询创建不同的数据库连接，如果它们有点慢，您可能会达到并发连接数的限制。

应该在你的数据源管理窗口中设置一个JNDI：添加一个新的连接并设置正确的凭证。在高级选项下设置连接池。给它一个有意义的名字。从那时起，您应该在仪表板查询上引用该名称，并使用SQL over JNDI而不是SQL over JDBC。这样每个SQL查询将从连接池获取一个连接，并且尽管运行多个查询，DB每次只能看到1个连接。

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.

解决方案

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.

这篇关于'在Pentaho中创建仪表板时在postgres中创建的连接数过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！