Performance Tuning J2EE Applications deployed on MySQL

Last week I attended Mark Matthew's "Performance Tuning J2EE Applications deployed on MySQL" at the MySQL Conference (see Anthony's Review). 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.

  • To get JVM Metrics, i.e. Heap/Garbage Collection stats, add the following to your java command:
    -verbose:gc -XX:+PrintGCTimeStamps
  • The default JVM heap size is 64MB, which is likely too small for most webapps. You can change the default min/max by adding the following to your JAVA_OPTS (or CATALINA_OPTS) environment variable:
    -Xms128m -Xmx256m
  • Young generation sizing - let the JVM do it by specifying:
    -XX:+AggressiveHeap
  • 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/J 3.0 is 40-400% faster than 2.0.14 depending on the situation - use the latest driver!

Finally, here is the really good stuff. Below are a number of parameters you can add to your JDBC URL (like autoReconnect=true) to get information from MySQL's JDBC Driver:

  • Logging Slow Queries: logSlowQueries=true and slowQueryThresholdMillis=n (2000 default)
  • Reporting Performance Metrics: gatherPerfMetrics=true and reportMetricsIntervalMillis=n (30s default)
  • Usage Advisor (abandoned objects, un-used columns in selects, incomplete ResultSet traversal): useUsageAdvisor=true

Reminder: if your app doesn't have performance issues - you probably don't need to modify anything. Mark said he'd put his presentation online soon, so I won't bother doing that. I'll try to link to it when he puts it up.

Update May 25th, 2004: Mark notified me that his slides have been posted.

Posted in Java at Apr 19 2004, 08:44:25 PM MDT 2 Comments
Comments:

Looking through this documentation: http://www.mysql.de/documentation/connector-j/ There are a bunch of other interesting flags that can be passed to the JDBC driver (MySQL) via the URL. Several options on cache control for prepared statements, various types of metadata, and profiling options. I'll be interested to see if any of these other options are covered in the presentation, since most of these default to off.

Posted by pauld on April 21, 2004 at 09:11 PM MDT #

Hi Matt,

I know this post is outdated. But I am still interested in this topic and want to see the slides. Unfortunately the download link it's not available now. Do you have the slides to share with us? I googled it but could not found anything.

Thanks.

Mark

Posted by Mark on June 09, 2008 at 07:26 PM MDT #

Post a Comment:
  • HTML Syntax: Allowed