MYSQL error: too many connections

sol · **Joined:** Fri Sep 18, 2009 7:04 pm **Posts:** 12

So, I'm using java persistence to connect a flex/flash web app to a backend mysql database.

Everything is working fine in the java services, but VERY frequently (and with only one user currently) I get a "too many connections" error from the MYSQL server. I'm using one services class to connect to all my POJO's, and I generate an EntityManagerFactory only once at the initialization of the class. I create EntityManagers once for each method, and am very careful to close them at the end of the method.

I think what's happening is that the EntityManagerFactories are never getting closed, so each time I run the application, a new connection is generated. But even this wouldn't explain the rate (probably one in ten uses of the application) that the available connections fill up.

I also feel like I shouldn't need to initialize a new services object each time I open the flex application, but I'm not sure how to avoid doing this. I'm a bit lost with this, so any help would be appreciated. Thanks!

Sol

Cameron McKenzie · **Posted:** Fri Sep 18, 2009 7:19 pm

What type of connection pooling are you using?

sol · **Joined:** Fri Sep 18, 2009 7:04 pm **Posts:** 12

None, currently (unless something is set up by default?). That's not something I know how to implement.

Sol

Froestel · **Posted:** Fri Sep 18, 2009 8:24 pm

Hi sol,

the Hibernate docs state in Sec. 3.3 http://docs.jboss.org/hibernate/stable/core/reference/en/html/session-configuration.html#configuration-hibernatejdbc

Quote:

Hibernate's own connection pooling algorithm is, however, quite rudimentary. It is intended to help you get started and is not intended for use in a production system, or even for performance testing. You should use a third party pool for best performance and stability.

Obviously you haven't changed the default settings. So, check out a third party tool (it does not come for free (meaning, without some effort) but it's not too hard to be handled).

I've tried C3P0, see http://www.mchange.com/projects/c3p0/index.html, and am satisfied so far.

CU
Froestel

Cameron McKenzie · **Posted:** Sat Sep 19, 2009 7:14 pm

Definitely look into connection pooling. For live applications, it's an absolute must. Fortunately, it's not too difficult to figure out. It'll take a day for sure, if it's your first time, but it's an important skill that will serve you well in the future.

sol · **Joined:** Fri Sep 18, 2009 7:04 pm **Posts:** 12

I looked through that link and I'm a little confused. I import the c3p0.jar into my library; that's easy enough. But then where do I create the CompoPooledDataSource object, and what do I use it for? How does this relate to creating the EntityManagerFactory?

Sorry for the newbie questions.

Sol

Froestel · **Posted:** Mon Sep 21, 2009 1:40 pm

Hi sol,

tell Hibernate to use it, i.e. in the hibernate.cfg.xml by including the element:

Code:

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

Then you can configure it to your specific needs. Some more property elements will be required as you will see. Refer, i.e., to http://www.mchange.com/projects/c3p0/index.html

CU
Froestel

sol · **Joined:** Fri Sep 18, 2009 7:04 pm **Posts:** 12

OK, that's done and it seems to work. Is there any way to check that I am indeed pooling connections? All the CRUD operations are working fine on the database, but I don't know for sure if the connections are actually being pooled...

Edit: With a little testing, performance is EXTREMELY slow. Just initializing my web-app is taking several minutes (compared to 10 seconds before I implemented connection pooling) Any thoughts? Thanks for all the help.

Sol

Froestel · **Posted:** Mon Sep 21, 2009 3:01 pm

Hi sol,

I think you can try setting the Hibernate property hibernate.generate_statistics to true (can be done in hibernate.cfg.xml). This provides a lot of info in the log/console - as well, if I remember correctly, on C3P0. (Don't forget to turn it off again when going into production state)

CU
Froestel

sol · **Joined:** Fri Sep 18, 2009 7:04 pm **Posts:** 12

I'm actually using an EntityManagerFactory, not a SessionFactory, so I can't use the statistics. Any reasons that connection pooling should be slowing me way down?

Sol

Froestel · **Posted:** Mon Sep 21, 2009 4:37 pm

Hi sol,

if you kept the default settings this might slow you down. C3P0 can be configured pretty granular. I'm afraid you have to test with with different settings for your particular purpose to find out what fits your needs. There are a couple of settings for debugging hibernate.c3p0 as I recall. Check the link I posted before.

CU
Froestel

sol · **Joined:** Fri Sep 18, 2009 7:04 pm **Posts:** 12

So a while back I got connection pooling with C3P0 figured out and that fixed the too many connections error. Now, though, I've got a new problem: A java out of memory error. The error occurs once per ~20 runs of the application.

I'm carefully closing all the entity managers I open, and I only create an EntityManagerFactory once per application...Any ideas on what might cause this?

Sol

Froestel · **Posted:** Sat Oct 03, 2009 12:09 pm

Hi sol,

sol wrote:

Any ideas on what might cause this?

actually, no! You just provided the information that you have an OutOfMemory problem with no details. This can have a lot of reasons.

You should check how much data you actually load from the DB. If you do not use proxying you might easily run into memory problems. Especially BLOB and CLOB fields might contain enormous amounts of data.

CU
Froestel