This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes JDBC and the fly likes Too many connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Too many connections" Watch "Too many connections" New topic
Author

Too many connections

mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Hello,

I am using mysql and java. I had to implement connection pooling for my standalone java program so I picked up some examples from one of the websites.
I get the following error.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
at sun.reflect.GeneratedConstructorAccessor16.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
..
..
..
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at ConnectionPool.getConnection(ConnectionPool.java:62)
at ConnectionPool.<init>(ConnectionPool.java:53)
at ConnectionPool.getInstance(ConnectionPool.java:31)
at DBHandler.dbConnect(DBHandler.java:87)


Even if I make the connections in the connection pool equal to the number of connections that I'm opening, i get the error message.
This webpage is the refernce to the connectin pool class that I used.

Could you please help.

Mayank.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
There are many examples of connection pools floating around the net; I'd go with a standard one like http://commons.apache.org/dbcp/.

Why does a standalone application (meaning desktop app, I assume) need so many connections? Are they properly closed (i.e., returned to the pool) after use?


Ping & DNS - my free Android networking tools app
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Even if I use just 50 connections i get the error. I have some records in a text file which i need to process and i do so by creating different threads for the each process.So if there are 50 records, 50 threads will be created and each of them will access the DB.

Why do i get the error when such less connections are used.
Having left some connections open could be the only reason or anything else also.
I have checked my program.
Should i attach it as well?
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
I cant use apache's DBCP because mine is a standalone application. Thats why i resorted to that class avialable for CP.

Please suggest.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
So each thread processes just a single record? Does the processing take a long time, and is it a mix of local processing and DB operations? If so, fewer threads (maybe 5) working on multiple records each might be a better approach.

Without knowing more about the problem, I'd probably create just a single background thread, and have it process all records sequentially. What happens if multiple users run the program at the same time? You could have hundreads of open connections simultaneously.

Why couldn't you use DBCP in a standalone application?
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Yes each record is a single thread which does some minimal processing including fetching information from the db and doing some calculations on it.
It is like this: the text file contains call detail records(CDRs) which have to be processes in real time. Like in a prepaid call..if 1000 CDR's arrive at a time then all of them have to be processed in real time and the resulting balance updated.

Its an application which runs atall the time. Multiple users will not run it. The application will process all the CDRs in the text file.

Why couldn't you use DBCP in a standalone application?

How can I do this? My application cannot run as a service inside the server.
Please help regarding this last part if its possible. Wont i have to deploy my app as a ear or servlet etc in the server(i'm not sure), this will not work for my application.

Also, I observed that in the class that I was using for connection pooling., in that if i give the connection pool size as 15 and try to run 70 threads(i.e. 70 connections) to the db it gives the "too many connections" error. Please help.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
mayank gupta wrote:...which have to be processes in real time.

I doubt that a standalone Java application will be able to process anything reliably in real time. Are you using the Real Time JVM?

Why couldn't you use DBCP in a standalone application?

How can I do this? My application cannot run as a service inside the server. Please help regarding this last part if its possible. Wont i have to deploy my app as a ear or servlet etc in the server(i'm not sure), this will not work for my application.

DBCP is a connection pool library. You can use it wherever you can run Java code. It does not need any server or service or whatever to run in.

I observed that in the class that I was using for connection pooling., in that if i give the connection pool size as 15 and try to run 70 threads(i.e. 70 connections) to the db it gives the "too many connections" error.

What is "it" - your pooling code or the DB?
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
By 'it' i meant the db was giving an error of too many connections.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
The first thing to check is whether all the connections to the DB actually originate from your application (they could be coming from elsewhere).

If they are coming from your application, then it would seem that the logic that limits the pool size to 15 isn't working. That's the benefit of reusing existing components (like DBCP) - they're debugged and working already.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
I guess I will get started with DBCP then.
Thank You.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
hello again,

I used DBCP and I still get the following:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"


I have set the following:
config.maxActive = 150;
config.maxIdle = 100;
config.minIdle = 30;
config.maxWait = 1000;
config.whenExhaustedAction = 2;

even when whenExhaustedAction = 2(WHEN_EXHAUSTED_GROW), why are my connections limited?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
Have you tried it with a more reasonable maximum, like 10?
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
When i reduce the number of records it has to deal with (the ones in my flat file to 15) and the maxActive to 10 then everything works out fine.
But with the number of records in the flat file as 150 and maxAxtive as 10 i get the same error. What could be wrong.
* I am closing the connections everywhere and anyway at the end of each method i have a if statement saying:

So even if at some there is any connection opened, it gets closed.

* Also in the class which provides connection, inside the closeConnection() and openConnection() i put a counter.
At the end of the run when i see the counts, the openConnection() has been entered 463 times while the closeConnection() method has been entered 734 times.
closeConnection() and openConnection() are methods which provide and close connections.

Please suggest.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Could it be because I have not closed my Statement and ResultSet objects? Dont they expire automatically!
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
You should close Statements; closing them will close ResultSet automatically.

More interesting than the total number of opened and closed connections would be the maximum difference between opened and closed connections over time. If that number rises too high you'd get a problem.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Thanks a lot, it finally did work as soon as i closed the Statements.
But I have still not closed the ResultSet

The API docs say
close() -Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.

That means it does get closed automatically even if i dont do it. Why isnt this happening in my case?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
mayank gupta wrote:That means it does get closed automatically even if i dont do it. Why isnt this happening in my case?

It gets closed automatically if the Connection is closed. Bit since in a connection pool that never happens, the Statement is not closed automatically.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Hello,
When my config is:
config.maxActive = 150;
config.maxIdle = 70;
config.minIdle = 10;
config.maxWait = 1000;

And I have 149 records which have to picked up from the flat file and processed individually even then I get the "Too many connections" error.
It works fine for small number of records, say 20 or so. Can you please suggest what could be wrong?
Please find attached the DBHandler that I am using. In that I am not using selectQueryRS & executeUpdate at all so I've not added close statements to that right now.

Could you please let me know where exactly am I going wrong?
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
When I have 149 records to process and my program is running then:

mysql> show processlist;
+------+------+-----------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+------+-----------------+----------+---------+------+-------+------------------+
| 4150 | root | localhost | BlueBill | Query | 0 | NULL | show processlist |
| 4156 | root | localhost:60967 | BlueBill | Sleep | 32 | | NULL |
..
..
| 4250 | root | localhost:32829 | BlueBill | Sleep | 33 | | NULL |
+------+------+-----------------+----------+---------+------+-------+------------------+
71 rows in set (0.00 sec)

I have made sure I am closing the connections but i still get the error.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Infact with the above configurations (config.maxActive = 150; config.maxIdle = 70; config.minIdle = 10; config.maxWait = 1000;)
I can open a maximum of 99 connections. I tried 99 records in the flat fle(each record creates a thread which opens a connection). As soon as I make the record count in the flat file to 100 i get the "too many connections" error.

Is it because the mysql version that I'm using is : 5.0.45 and I read here webpage there will be 100 max_connections.
Cant I have 1000 -1500 or even as high as 5000 connections. Ofcourse all of them will not be in parallel but the threads which complete there work close their connections.


What could be wrong?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
I'm still not clear on what you hope to gain by processing that many records in parallel. A low number of worker threads (maybe 5) seems OK to take advantage of other threads being blocked, but beyond that they'll just compete either for the client CPU time or for the DB's CPU time, and the thread overhead (low though it is) will actually slow things down ever so slightly.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
I'm still not clear on what you hope to gain by processing that many records in parallel. A low number of worker threads (maybe 5) seems OK to take advantage of other threads being blocked, but beyond that they'll just compete either for the client CPU time or for the DB's CPU time, and the thread overhead (low though it is) will actually slow things down ever so slightly.


1) Even if i do have a large number of threads whats happening in my case is that the text file is read sequentially. As each record is read, it is assigned to a thread, the record is processed and the thread gets killed automatically. The time process a cdr is minimal so i think it shouldnt matter much as the threads will keep getting destroyed as and when they finish the work assigned to them. Please correct me if i am wrong.

2) I made some changes as i saw i had made mistakes in certain places but i still get the error when i try to process 2000 records. Is there anyway that i can see the number of connections currently available in the pool?

3) What is the idea behind letting connections being idle (maxIdle,minIdle)?

Could you please help. This is just not moving forward.
Can i post my files to you?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
...the threads will keep getting destroyed as and when they finish the work assigned to them.

Apparently it does not work this way, since the number of connections being used goes up, but doesn't seem to come down (I'm assuming that each thread uses a single connection from the pool).

I made some changes as i saw i had made mistakes in certain places but i still get the error when i try to process 2000 records. Is there anyway that i can see the number of connections currently available in the pool?

If the connection pool API doesn't have a method for this (and I'd be surprised if it didn't) then you can calculate it yourself - it's the pool size, minus the number of connections you have obtained from the pool, plus the number of connections you have returned to the pool.

But as I keep saying, you need to keep the number of connections small. 100 seems to be the maximum the DB can handle - that's a strong indicator that it's a bad idea to use that many. While maybe having 20 should be OK, I doubt that anything more than 5 buys you much in terms of performance.

What is the idea behind letting connections being idle (maxIdle,minIdle)?

DB Connections are expensive to create. So the pool keeps a certain number of them around -even if they have nothing to do and are "idle"- so that they can be handed out quickly whenever the application needs them - at least "minIdle" connections, at most "maxIdle" connections.

This is just not moving forward.

I'm beginning to suspect that it won't, unless and until you move away from opening too many threads that try to obtain too many connections.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Apparently it does not work this way, since the number of connections being used goes up, but doesn't seem to come down (I'm assuming that each thread uses a single connection from the pool).

How would I know if the number of connection goes up and doesnt come down. Is there any way of finding it out?

I'm beginning to suspect that it won't, unless and until you move away from opening too many threads that try to obtain too many connections.


How can I implement thread pooling?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
How would I know if the number of connection goes up and doesnt come down. Is there any way of finding it out?

Didn't I outline that a couple of times already? You increment a counter each time you obtain a new connection, and decrement it each time you release a connection. The DB pool you're using should also have an API method that tells you how many connection are allocated, and how many are still available.

How can I implement thread pooling?

Let's not make this more complicated than it needs to be. Thread pools don't help in this situation. You can start a fixed number of threads (say, 5), and then give each thread a chunk of inputs to work on sequentially. If you then make sure that each thread only allocates a single DB connection you should be all set.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
The active connections decrease also.
74534:connectionPool.getNumActive() =101
74545:connectionPool.getNumActive() =99
74548:connectionPool.getNumActive() =100
74555:connectionPool.getNumActive() =99
74560:connectionPool.getNumActive() =99
74565:connectionPool.getNumActive() =99
74568:connectionPool.getNumActive() =100
..
..
76939:connectionPool.getNumActive() =12
76944:connectionPool.getNumActive() =12
76954:connectionPool.getNumActive() =9
76966:connectionPool.getNumActive() =5

I would like to point out that when a cdr is read and a thread starts on it, it processed the CDR (the record) and for that it goes through various methods, each time opening and closing connections.
I was totally at my wits end so I attached some code.
please help.

=================================

This is the rough outline of the code. Once the record is picked a thread is created for it and it goes to processCDR() which in turn has various methods inside it. Now for each mehod, i have opened and closed connections as and when I needed them.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
How big is your connection pool now? Didn't you say that it worked fine if you set it to a reasonable size like 20?
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
The size of my connection pool is 150. I said that there were 20 records (20 threads) when it worked fine. As i increase the number of threads, say to 500 i start getting errors.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
And since the number of records will go on increasing to say 10,000 i cant test only with small number of records in my flat file. s you suggested I can limit the number of threads to 5 and use amongst those only. But wouldnt that mean creating a thread pool?
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
The size of my connection pool is 150.

That just doesn't make sense if the DB only allows 100 connections. And even that would be way too many.

And since the number of records will go on increasing to say 10,000 i cant test only with small number of records in my flat file. s you suggested I can limit the number of threads to 5 and use amongst those only.

As I said before, creating so many separate threads is the wrong approach.

But wouldnt that mean creating a thread pool?

A thread pool would be of use if you intend to reuse the threads. But that's not the case here. You create a few threads, give them each a portion of the input to work with, and let them go away once they're done.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
That just doesn't make sense if the DB only allows 100 connections. And even that would be way too many.

I picked up the idea of having 100 connections from
webpage

A thread pool would be of use if you intend to reuse the threads. But that's not the case here. You create a few threads, give them each a portion of the input to work with, and let them go away once they're done.


Please correct me if i'm wrong.
You mean to say that if i read 20,000 records from the flat file then i assign 5 threads to read 4,000 records each.
This would mean sequential processing of 4000 records.
Also if there are are 500,000 records in the flat file then also the number of threads will be constant (i.e. 5 in this case). Would nt that effecr performance?
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
I am sorry for so much of questioning but thank you for the patience.
In my case how do you suggest i give threads some portion to work with.
What is happening is:
Record get picked -> thread is created
thread calls method A() which calls method B-> and C-> so essentially once a record is picked up only sequential processing can be done on it.
In this case how can I create a few threads and give them each a portion of the input to work with?
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78

So basically I cant do the above because i am reading the records one by one and that is a requirement that i cant change.as and when the records get written to the file, i have to pick them up. So in this case i will have to reuse the threads.
Please suggest.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
You will get much better performance with 5 or 10 threads than with 20000. The overhead of managing that many threads, and switching between them, would kill performance.

You won't get parallel processing anyway. How many CPUs does the desktop machine have? 2? Maybe 4, at most? The processing is either CPU-bound with regards to the local machine, or CPU-bound with regards to the DB. Either way it's highly unlikely that starting more threads than 5 or 10 will improve matters, and in all likelihood will make it worse.

As to how to distribute the workload amongst threads: You would start all worker threads, and then -as you read the file- send each line to them in turns. The thread classes would need an internal List of inputs to work on, and you can add to that from the file-reading thread by calling some method like "addInputLine(String)" which adds that line to the List of inputs the thread should work on. Be sure to handle this in a thread-safe manner. If a worker's list is empty, it simply sleeps until a new work item arrives, or until the main thread signals that no more work will be forthcoming (in which case the worker thread can shut down).
Norman Java
Greenhorn

Joined: Mar 11, 2007
Posts: 2
I used the sample from http://www.java-samples.com/showtutorial.php?tutorialid=620 and works like a charm.

However in your case, since you are using it in standalone program you will have to tweak your my.ini file of your MySQL server. You should probably check this in a MySQL forum as well.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Hi,
I used Executors and ExecutorService from the java.util.concurrent package to create a thread pool. I read from the flat file and use the ExecutorService.execute() to pick a thread from the pool and process the CDR.
Inititally i had used Executors.newFixedThreadPool( 10 ). After 10 threads the execution was always sequential so I switched to using Executors.newCachedThreadPool(); wherein a new thread is created if the pool is exhausted and the old ones are also reused. But with this I again get the Too many connections error.
I learnt about Executors at webpage


The stack trace is:
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection, message from server: "Too many connections"
...
at CreditCalculatorThread.processCDR(CreditCalculatorThread.java:237)
at CreditCalculatorThread.run(CreditCalculatorThread.java:61)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
at java.lang.Thread.run(Thread.java:619)

Please suggest.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
If you keep starting new threads, and each threads acquires a connection, then of course you are going to run out of connections.

It seems that we've covered this ground multiple times by now. Nothing is gained by having so many threads or connections, especially not performance. Please read my previous replies with regards to I/O-bound vs. CPU-bound processes, and sequential vs. parallel execution.

Thread pools -and anything that the Executors class can do- achieve nothing but make the code more complicated than it needs to be.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
I have tried with Executor class and just increased the thread size a little. The thread pool is fixed size of threads. It does seem to work fine for 4000 records. I cannot somehow understand the difference between what you suggested and Thread pooling.
I tried thread pooling with a fixed size of 30, the time taken was 179 units.
Whereas with a fixed thread pool size of 5 it took 800 units.
The only problem that i committed, i think, when i sent the previous post was that i was using newCachedThreadPool() which spawned a lot of threads and couldnt use the old threads. But now I use Executors.newCachedThreadPool(30) and with a optimum thread size set i can see concurrency.
Thread pools -and anything that the Executors class can do- achieve nothing but make the code more complicated than it needs to be.

Even thread pools with a fixed thread size do serve the purpose. Please comment.
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42374
    
  64
The only reason why you find that a thread pool may be advantageous here is that you insist on a design that ties one thread to one connection; I've said multiple times of what I think about that design.

If you used a DB connection pool as it's meant to be used, combined with a reasonable number of worker threads, then you wouldn't have the problem that you're now trying to solve using a thread pool.
mayank gupta
Ranch Hand

Joined: Dec 21, 2008
Posts: 78
Hi,

My program runs fine. I have not been using one connection for one thread. I just pick connections from the pool and keep them back as and when i need them.
Anyway, i have got the too many connection problem solved by using thread pooling but I see a major performance hit when i have around 80,000 records.

Time elapsed = 179.25 sec, No of records=4000, no of threads= 30, maxActive= 35
Time elapsed = 861.52 sec, No of records=4000, no of threads= 5, maxActive= 35
Time elapsed = 198.30 sec, No of records=4000, no of threads= 45, maxActive= 35

With the above data (3 runs) I decided that a thread size of 30 was the best. I tried the same with the maxActive size as well and came to a conclusion that the runs were at a thread size=30 and maxActive=35.

When I run the test now with larger number of records, the number of records/sec reduces drastically

What can i do to increase performance?
You will get much better performance with 5 or 10 threads than with 20000. The overhead of managing that many threads, and switching between them, would kill performance.

I tried a couple of runs with 5 threads and each time the results were not comparable to when i took 30 threads.
Please suggest.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Too many connections