Matt Raible attended Mark Matthew's "Performance Tuning J2EE Applications deployed on MySQL" at the MySQL Conference. He saw some interesting points, so he wrote them up for us all to see.

Excerpt

Mark's presentation had a lot of tips and tricks with the MySQL JDBC Driver. I asked him to send me a copy - and he did. Below are a number of tips that you might be able to use in your apps. One thing that Mark forgot to mention (he did later over beers) was that MOST apps (with less than 50 tables) won't need this stuff. You should probably only performance tune your server (or JVM) if you *really* think it's not your app or database. The database, and its indexes, is the first thing to check for slow performance.

Read Matt Raible in Performance Tuning J2EE Applications deployed on MySQL

Hi,

Any books or articles relating to MySQL and Data warehousing/datamining ?

best regards

I am not sure, but i think that mySQL does not support DW or DM !.

But a quick search on google gave some intresting results ! [ HERE ]

Don't set the heap size AND -XX:+AggressiveHeap
Set one or the other!

http://java.sun.com/docs/hotspot/gc1.4.2/#4.2.2.%20AggressiveHeap|outline

Do we have access to the article, I can just see the comment made on the discussion. How can i have access to the material

Do we have access to the article, I can just see the comment made on the discussion. How can i have access to the material

http://raibledesigns.com/page/rd?anchor=performance_tuning_j2ee_applications_deployed

I dont see a great in depth study or anything here. Just a bunch of tips - which we know already .. Is it worth calling a Featured Article ???? .....

I think the article was meant to share a few tips which are specific to MySQL.

For a more general approach to eliminating data bottlenecks with O-R mapping and distributed caching, you could look at:
<blush-my article>
http://www.javaworld.com/javaworld/jw-04-2004/jw-0405-bottleneck.html
<\blush>

Or at the fair and open slugfest celebrating that article:
http://www.theserverside.com/news/thread.tss?thread_id=24932

<vendor>
Persistence Software supports the MySQL database - if you combine a Java O-R mapping took with distributed caching and put the whole thing on top of MySQL you get an incredible bang for a pretty small buck.

You can download the whole thing integrated with the Eclipse IDE here:
http://www.persistence.com/download
<\vendor>

- chris

From the article:

-Xms128m -Xmx256m

The Sun JVM will run significantly faster with the following config instead:

-Xms256m -Xmx256m

That's because the Sun implementation acquires and releases memory from / to the OS way too aggresively if the "ms != mx". Furthermore, either your server has the 256MB available or it doesn't. If you don't have it available, don't set the max that high. If you do have it available, you gain nothing from setting the min lower. This isn't a desktop system, it's a server -- make sure you have the necessary resources and if you do then use them!

Furthermore, unless it causes instability, you should always use:

-server

For code such as JDBC drivers, it can make a big difference by more agressively inlining methods.

Connection Pool Size: 15-20 is more than enough to handle an average application. Never have more connections than threads that can use them. For MySQL, the pool size is resource throttling, not saving connection setup time. Click here for a chart that shows the number of connections used doesn't change between a pool size of 10 and 20.

Connection pooling is one of the least understood "tunables" for appliation servers. It can make a huge difference in performance. I'll be talking about this at a BoF session at TheServerSide Symposium coming up soon. Basically, you need to set the connection pool size to infinite to tune for the optimum number of threads, and having achieved that, then set it to self-growing (but not shrinking) and do another load test to see how large it grows "naturally." That is *probably* (but not always, for a variety of reasons) the "correct" (i.e. optimum) size for the connection pool. Set its min and max to that value for the production deployment.

One of the biggest perf*cks is the connection testing with certain databases. If you can get away with it, don't enable it. Otherwise, make sure that the check only gets done "rarely" (e.g. every 10 seconds instead of every use.) Some connection pool impls are now building this logic in, thankfully. Also, eval how the pool is testing the connections, and make sure that (a) the designated approach will actually test the connection and (b) that the approach (e.g. a specific query) it is the most optimal approach that the database can support.

Peace,

Cameron Purdy
Tangosol, Inc.
Coherence: Clustered JCache for Grid Computing!

From the article:

-Xms128m -Xmx256m

The Sun JVM will run significantly faster with the following config instead:

-Xms256m -Xmx256m

That's because the Sun implementation acquires and releases memory from / to the OS way too aggresively if the "ms != mx".

Cameron,

Good point. I'll have to check and see if my slides don't actually have the two values the same...In any case, VM and Heap tuning can be a lot of work, there are many multi-page treatises on it (at least from Sun).

Connection Pool Size: 15-20 is more than enough to handle an average application. Never have more connections than threads that can use them. For MySQL, the pool size is resource throttling, not saving connection setup time. Click here for a chart that shows the number of connections used doesn't change between a pool size of 10 and 20.

Connection pooling is one of the least understood "tunables" for appliation servers. It can make a huge difference in performance. I'll be talking about this at a BoF session at TheServerSide Symposium coming up soon. Basically, you need to set the connection pool size to infinite to tune for the optimum number of threads, and having achieved that, then set it to self-growing (but not shrinking) and do another load test to see how large it grows "naturally." That is *probably* (but not always, for a variety of reasons) the "correct" (i.e. optimum) size for the connection pool. Set its min and max to that value for the production deployment.

I need to change my presentation to make it more clear that what I was getting to get at was this misconceived notion that 'more connections' == 'better performance', because there actually starts to be a diminishing return, and eventually an overloading of your RDBMs server holding resources for basically 'idle' connections.

One of the biggest perf*cks is the connection testing with certain databases. If you can get away with it, don't enable it. Otherwise, make sure that the check only gets done "rarely" (e.g. every 10 seconds instead of every use.) Some connection pool impls are now building this logic in, thankfully. Also, eval how the pool is testing the connections, and make sure that (a) the designated approach will actually test the connection and (b) that the approach (e.g. a specific query) it is the most optimal approach that the database can support.

The real issue is that most people (including O/R and CMP container vendors) don't do anything at all to handle SQLExceptions in their code, even though the facilities exist to detect dead connections (SQLException.getSQLState()) and then do something about them, rather than making the pool run around and check connections for them. Network-based connections do fail at times other than during idle periods (ham-fisted wiring guys pulling the power cord on your switch, an OS or server being restarted, etc, etc).

The real issue is that most people (including O/R and CMP container vendors) don't do anything at all to handle SQLExceptions in their code, even though the facilities exist to detect dead connections (SQLException.getSQLState()) and then do something about them, rather than making the pool run around and check connections for them. Network-based connections do fail at times other than during idle periods (ham-fisted wiring guys pulling the power cord on your switch, an OS or server being restarted, etc, etc).

Absolutely. It's like driving without a seatbelt or jumping without a parachute ... people know it is dangerous but too often they do it anyway. Well, maybe not the parachute thing ;-)

Once I worked on an app that was issuing a select to the database for every JDBC connection operation, just to make sure that the connection was still alive, so the load (in terms of quantity of statements being executed) was 2x higher than it should have been (and in reality about 5-15% higher than it should have been, since the "test" select statement was very simple to parse and run.) Since the app was completely database bound (Toplink, BTW), getting rid of this one redundant test allowed them to put off the necessary database upgrade (from 24xUS-II 440s to 32xUS-III 1050s) for a few months, yet continue to roll out new stores (it was a retail operation.) The way it was fixed was that I wrote a small wrapper JDBC driver that returned "true" on the test method for the connection, except if 10 seconds (or whatever it was configured to be) had passed it would actually do the test (the select statement) and store and also return that result. Also, I think it watched for exceptions that indicated that the connection was dead, and cached that result. That little change to the app dropped the number of selects (etc.) against the database by 49%! And they didn't lose the "dead connection detection" either, so it was a "free" performance improvement (except for my consulting fee.)

Peace,

Cameron Purdy
Tangosol, Inc.
Coherence: Clustered JCache for Grid Computing!