Raible Designs

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.