Linux Arval SQLException：致命：抱歉，postgres 中已经有太多客户端
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9798705/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Arval SQLException: FATAL: sorry, too many clients already in postgres
提问by vikas malik
I am using a database in PostgreSQL 9.1,in which entry are coming continuously from another program . I am sending request from Ajax after 6 sec to fetch the latest entry.tomcat output window shows exception---
我在 PostgreSQL 9.1 中使用一个数据库，其中的条目不断来自另一个程序。我在 6 秒后从 Ajax 发送请求以获取最新的 entry.tomcat 输出窗口显示异常---
Arval SQLException: FATAL: sorry, too many clients already
and program is working correctly also after this. When i check my postgres with query---
并且程序在此之后也正常工作。当我用查询检查我的 postgres 时---
select count(*) from pg_stat_activity;
it shows that connection are increasing continuously but I close the connection after each request.I am using netbeans and struts 1.3.
它表明连接不断增加，但我在每次请求后关闭连接。我使用的是 netbeans 和 struts 1.3。
long previousSNO = Long.parseLong(request.getParameter("previousSNO"));
if(previousSNO == 0)
{
sb.append("SELECT sno,search_type,search_value,search_date FROM log_temp ORDER BY search_date DESC LIMIT 20");
prest = cb.executeSQLQuery(sb.toString());
rs = prest.executeQuery();
}
else
{
sb.append("SELECT sno,search_type,search_value,search_date FROM log_temp WHERE sno > ? ORDER BY search_date DESC");
prest = cb.executeSQLQuery(sb.toString());
prest.setLong(1, previousSNO);
rs = prest.executeQuery();
}
rs.last();
int c = rs.getRow();
rs.beforeFirst();
if(rs!=null && c>0)
{
//code for making json resultsb from resultset here
rs.close();
}
cb.closeConnection();
response.setContentType("text/plain");
response.getWriter().print(resultsb.toString());
//and close method in connection bean is
//连接bean中的close方法是
public void closeConnection() {
try {
// st.close();
conn.close();
System.out.println("con is closed");
conn = null;
} catch (SQLException e) {
e.getMessage();
System.out.println(e.getMessage());
System.out.println("con is not closed");
}
}
Every time its print on console " con is closed";
每次在控制台上打印“ con is closed”；
采纳答案by Jayan
You can increase the max_connectionsin postgres, that is not the solution though. You have resource leaks. It could be any - connection not closed, result set not closed. Please go back and check the code.
您可以在 postgres 中增加 max_connections，但这不是解决方案。你有资源泄漏。它可以是任何 - 连接未关闭，结果集未关闭。请返回并检查代码。
Consider using a connection pooling library like c3p0/BoneCp
考虑使用像c3p0/BoneCp这样的连接池库
A general discussion on connection pooling is here(Thanks to @sinisa229 mihajlovski)
回答by Sachin M Sharma
To increase the connection limit you may like the following document.
要增加连接限制，您可能会喜欢以下文档。
This solution is tested on ubuntu 12.04.
此解决方案已在 ubuntu 12.04 上测试。
1. Make following changes in postgresql.conf file :
1. 在 postgresql.conf 文件中进行以下更改：
Open /etc/postgresql/9.1/main/postgresql.conf
打开 /etc/postgresql/9.1/main/postgresql.conf
max_connections = 200
shared_buffers = 100MB
max_files_per_process = 100
Reference: shared_buffers size should be less than shmmax size.
参考：shared_buffers 大小应该小于 shmmax 大小。
2. Commands to check shmmax:
2. 检查 shmmax 的命令：
$ sysctl -e kernel.shmmax
$ ipcs -l
Reference: Adjusting shmmax and shmall
3. Increase the size of shmmax:
3、增加shmmax的大小：
Run the following command:
运行以下命令：
$ sysctl -w kernel.shmmax=134217728
$ sysctl -w kernel.shmall=2097152
and write on top in /etc/sysctl.conffile:
并在/etc/sysctl.conf文件顶部写入：
kernel.shmmax=134217728
kernel.shmall=2097152
Reference : SHMMAX in Ubuntu
4. Restart postgresql
4.重启postgresql
$ service postgresql restart
Links:
链接：
http://www.varlena.com/GeneralBits/Tidbits/perf.html
http://www.varlena.com/GeneralBits/Tidbits/perf.html
http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html
http://www.postgresql.org/docs/9.1/static/runtime-config-resource.html
