RE: Pooling / thread issues - Tom Michalek - com.mysql.lists.java

4 messages in com.mysql.lists.javaRE: Pooling / thread issues

From	Sent On	Attachments
Ronald Muller	14 May 2002 10:37
Mark Matthews	14 May 2002 14:52
Tim Endres	14 May 2002 18:21
Tom Michalek	17 May 2002 06:48

Subject:	RE: Pooling / thread issues
From:	Tom Michalek (mich...@attbi.com)
Date:	05/17/2002 06:48:51 AM
List:	com.mysql.lists.java

I have built a Java web application that handles 100 concurrent users. It consists of a JVM running Tomcat and servlets, and another business logic JVM. The 2 JVMs communicate via RMI. The business logic JVM uses mm.mysql for a persistent store of user information.

There is a thread in the business logic JVM for each user, so there can be 100 threads. Currently all threads share a single database connection. This connection is created when the JVM starts up and always remains open. Transactions are handled by the business logic, not by the database.

Will I get better performance if the business logic JVM had 100 connections to mm.mysql instead of one? If so, why? Waiting on I/O and resource contention were mentioned. Could someone please expand on these reasons?

Thanks, Tom

----- Original Message ----- From: "Ronald Muller" <rk.m...@12move.nl> To: <ja...@lists.mysql.com> Sent: Tuesday, May 14, 2002 12:38 PM Subject: Pooling / thread issues

I am working on an application in which the database i/o must be improved. So i have to design and implement a solid and fast database i/o with our Java (JDK 1.4) application. We are working primarily with MySql 3.23.42 and the mm.mysql-2.0.13 jdbc driver.

Studying all kind of material on this subject the following questions remain unsolved for me:

The JDBC spec states (see http://java.sun.com/products/jdbc/driverdevs.html, section A1.6): "All operations on java.sql and javax.sql objects are required to be multithread safe. They must be able to cope correctly with having several threads simultaneously calling the same object. In other words, a statement execution in one thread should not block an execution in another thread. In particular, JDBC drivers should operate correctly when used from multiple threads." ..... "For example, two Statement objects on the same connection can be executed concurrently, and their ResultSets can be processed concurrently (from the perspective of the developer). Some drivers will provide this full concurrency. Others may execute one statement and wait until it completes before sending the next one."

1) What strategy is used by mm.msql? 2) Why do we need Connection Pooling? If a Connection objects allows concurrent access (so threads are not blocked), we only need one Connection for a given application and keep it alive for the lifetime of the application.

Multi-threaded access to databases in practice does not work well (although it works ok with MM.MySQL).

3) Or ... is the throughput increased by using several (concurrently used) Connections?

Yes, most of the time the driver will be waiting on I/O, so multi-threading against a single connection buys you very little. Opening more connections (within reason) to the database will increase throughput and reduce contention for resources both in and outside of the JDBC driver. Also, you have the notion of transactions mapping one-to-one with a connection with JDBC and MySQL (and any other database for that matter). If you multi-thread access to a connection, you can not use the concept of a transaction, which in most cases would be very dangerous.

It is also very dificult to debug/trace and correctly develop multi-threaded access to anything, so that is why simpler programming models exist (EJB for example).

4) MySql does not cache Prepared Statements as far as I know. So is there any performance advantage by using PreparedStatement instead of Statement objects

Other than the fact that things will be automagically escaped for you, and when MySQL-4.1 comes out with prepared statements, no.

in the MySQL/mm.mysql context? 5) Is there any advantage in reusing the (Prepared)Statement objects? If so, does a "stand alone" poolmanager exists with this functionality (we do not

I would take a look at the jakarta commons project's database connection pool. See http://jakarta.apache.org/ for more information.

-Mark