Too many connections (JDBC) - como lidar?

Vou tentar te ajudar sem mudar sua arquitetura.
Bom ao invés de abrir e fechar as conexões, volta a fazer da forma antiga que você descreveu (todo mundo usa uma única conexão), quando chegar uma solitação, seu servlet deve usar o metodo:

suaconexao.isvalid(tempo em segundos)

se verdadeiro continua se for falso, você reconecta.
Basicamente isso resolverá seu problema sem ter de reaquiteturar tudo.

Mas seria legal você procurar melhorar essa forma, blz?

Força lá.

vc pode configurar um pool de conexões para gerenciar suas conexões através de um datasource

acho que é isso

procure no guj por datasoure ou pool de conexões

Não teria que usar a opção autoReconnect=true na url de conexão?
Tipo assim: jdbc:mysql://localhost:3306/banco?autoReconnect=true

romarcio:

Não teria que usar a opção autoReconnect=true na url de conexão?
Tipo assim: jdbc:mysql://localhost:3306/banco?autoReconnect=true

Blz pessoal…muito obrigado pelas respostas…vou tentar as dicas que vocês me deram…começando pelo autoReconnect na url que é mais simples de implementar. Conforme eu for tendo os resultados eu posto aqui.

Mas ainda tenho a curiosidade de saber como que o pessoal que tem uma aplicação web com muitos acessos fazem para lidar com a situação de quanto o número de usuários simultâneos cresce muito…se há alguma técnica na própria programação ou se tem de solicitar ao provedor mesmo que aumente a capacidade de conexões simultâneas…

Aqui na empresa usamos XAPool para gerenciar o pool de conexões num tomcat. Outras alternativas seriam o c3po e o atomikos.

Quanto a problemas com conexões, tivemos vários.
Infelizmente o framework da empresa é um tanto limitado e, da mesma forma que o seu projeto, ele abria e fechava conexões a cada operação no banco.
Em testes de desempenho usando versões antigas do XAPool obtínhamos vários nullpointers devido a falhas no gerenciamento de concorrência. Isso acontecia esporadicamente a partir de 10 usuários, mais ou menos, aumentando conforme o volume.
Atualizando o XAPool e outras dependências para as últimas verões, tivemos menos problemas mas ainda ocorreram bugs esporádicos.
Algo que ajudou muito e praticamente resolveu o problema foi uma espécie de cache de conexão por requisição (na verdade, por thread). Como cada requisição num container é realizada em uma thread, colocamos um filtro que inicia e finaliza o cache, então todas as operações realizadas nessa requisição compartilham a mesma conexão.

Outro problema que tivemos é com a abertura de muitos objetos prepared statement e similares. Alguém implementou um loop onde, para cada linha de uma tabela, um outro comando SQL era executado.
Resumindo, é importante sempre fechar os statements e resultsets já utilizados.

Mais importante ainda é fechar o Connection para devolver a conexão ao pool após o uso. Chegamos a criar uma mini ferramenta que varre o código em busca de métodos que abrem conexões e não fecham, pois várias vezes o pessoal esqueceu de chamar o closeConnection no finally, pois nos testes simples do desenvolvedor tudo funcionava, até colocar em produção.

Enfim, essas são algumas experiências e dicas que lembrei agora.

Olá Pessoal.

Primeiro, obrigado pelas dicas. Seguindo-as, utilizei o autoReconnect na string de conexão, e também estou utilizando agora o C3po.

após as configurações do c3po, consegui fazer funcionar. As configurações que usei foram:

cpds.setMinPoolSize(10);
	cpds.setAcquireIncrement(4);
	cpds.setMaxPoolSize(100);
	cpds.setMaxIdleTime(30);
	cpds.setIdleConnectionTestPeriod(60);
	cpds.setBreakAfterAcquireFailure(false);

Porém, fiz alguns testes com o JMeter, onde é possível testar por exemplo o que aconteceria caso 500 usuários simultâneos tentassem acessar a aplicação. Bom, aí começam a surgir os problemas. O fato de o servidor recusar conexão uma vez que no pool defini um máximo de 100 e no mysql a variável max_user_connections está como 40 é entendível. O problema, é que executando o teste no Jmeter com 500 usuários, o servidor simplesmente tem de ser reiniciado, ou seja, ninguém consegue conectar mais (se eu deixar assim, minha aplicação está vulnerável a ataque Dos). rodo o comando show full processlist no banco e aparecem 40 conexões ativas…até que eu reinicie o servidor.

também queria que após um tempo de inatividade, a conexão fechasse. Mesmo eu definindo cpds.setMaxIdleTime(30), as conexões estão continuando ativas.

Ah, este é o erro que apresenta no log do Tomcat após os testes com JMeter e 500 usuários simultâneos:

java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
	at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:639)
	at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
	at ConectaBanco.obterConexao(ConectaBanco.java:67)
	at PesquisarDAO.conectar(PesquisarDAO.java:35)
	at PesquisarDAO.pesquisar(PesquisarDAO.java:53)
	at Controlador.executarComando(Controlador.java:123)
	at Controlador.doPost(Controlador.java:76)
	at Controlador.doGet(Controlador.java:46)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:185)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:151)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
	at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:929)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:405)
	at org.apache.coyote.ajp.AjpProcessor.process(AjpProcessor.java:193)
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:515)
	at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
	at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
	at java.lang.Thread.run(Thread.java:662)
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@14ae5cd -- timeout at awaitAvailable()
	at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1404)
	at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:594)
	at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:514)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:707)
	at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:634)
	... 25 more

Aqui http://www.mchange.com/projects/c3p0/index.html#c3p0-config.xml no site do c3p0 tem um exemplo de configuração do que eles chamam de massiva. Neste exemplo parece que a app é configura para 1000 acessos simultâneos.
Da uma olhada.

Tentei a configuração “massiva”, mas o resultado foi o mesmo, ou seja, quando realizo testes com o JMeter e 500 usuários simultâneos, o mysql já passa a não aceitar nenhuma conexão, fazendo com que eu precise reiniciar o servidor. ( O log exibido pelo Tomcat continua idêntico ao que postei anteriormente ).

Já tentei várias configurações, a última que eu testei foi a abaixo:

cpds.setInitialPoolSize(10);
		cpds.setMinPoolSize(10);
		cpds.setMaxPoolSize(30);
		cpds.setTestConnectionOnCheckin(true);
		cpds.setMaxIdleTimeExcessConnections(10);
		cpds.setAcquireRetryAttempts(5);
		cpds.setAcquireRetryDelay(3);
		cpds.setMaxConnectionAge(0);
		cpds.setUnreturnedConnectionTimeout(10);
		cpds.setDebugUnreturnedConnectionStackTraces(false);
		cpds.setBreakAfterAcquireFailure(false);

Eu sei que o meu servidor mysql está limitando a 40 conexões, e eu até poderia controlar isso via minha aplicação. Mas a minha preocupação é com ataques Dos. Um a pessoa que utilize o JMeter já consegue derrubar fácil meu servidor mysql da forma como está, entendem?

Infelizmente to meio sem tempo para ajudar, mas gostaria de deixar um comentário.
Acho que a forma de evitar ataques DoS não é na aplicação, pois a mesma pode sofrer sobrecarga mesmo com uma quantidade grande de usuários legítimos. Cada sistema tem um limite de carga e aumentar esse limite exige que muitas vezes a arquitetura seja alterada.
Talvez um proxy ou firewall no meio do caminho que controle o tráfego seja mais adequado, por exemplo, que limite a taxa de requisições por minuto de um determinado IP.

utluiz:

Infelizmente to meio sem tempo para ajudar, mas gostaria de deixar um comentário.
Acho que a forma de evitar ataques DoS não é na aplicação, pois a mesma pode sofrer sobrecarga mesmo com uma quantidade grande de usuários legítimos. Cada sistema tem um limite de carga e aumentar esse limite exige que muitas vezes a arquitetura seja alterada.
Talvez um proxy ou firewall no meio do caminho que controle o tráfego seja mais adequado, por exemplo, que limite a taxa de requisições por minuto de um determinado IP.

Em geral, é utilizado a abordagem de ter um proxy/firewall e ele só redireciona. Existem várias técnicas, não acho que deixar o servidor parrudo como solução.

Olá Pessoal.

Obrigado pelos comentários…acho que consegui “minimizar o problema”…

Analisando as experiências relatadas pelo utluiz, verifiquei que eu estava deixando alguns statments e resultsets abertos…assim, o c3po acabava não encerrando a conexão mesmo quando dava timeout devido aos resultset e statment estarem abertos. De maneira que eu acabava com várias conexões abertas por tempo indefinido até estourar o pool de conexões.

Obrigado pessoal.

[]´s