Server side Prepared statements in MYSQL + Java

One thing that we generally forget when using MySQL prepared statements in Java is the setting up of the parameter "useServerPrepStmts". This variable must be set at the JDBC connection level to take advantage of MySQL server side prepared statements. Otherwise, though we follow all the standards of coding in writing client-side prepared statements, MySQL ultimately treats them as a regular statement only.

Below is an example datasource setting in JBOSS




SomeMySQLPool
true
jdbc:mysql://HOSTNAME:3306/SomeMYSQLDb
com.mysql.jdbc.Driver
NON ROOT USER NAME
PASSWD
true
org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter

mySQL

JBoss Performance Tuning

The first thing that we need to do as a part of this exercise is to identify the parameters that we have to work upon. Below is a list of different things that we can look upon before actually going into the tuning part.

JVM Settings
a) JAVA HEAP SIZE
b) PERM GEN SPACE
c) Parallel GC? (Good for multi CPU systems)

DB settings
a) Max Pool and Min Pool sizes for all Oracle and MySQL Pools.
b) idle-timeout-minutes
c) blocking-timeout-millis

HTTP connector Settings (Thread Pool Settings)
a) Max Threads -- Concurrency
b) Accept Count -- Queue size of each thread
c) Compression -- True/False

JSP Compilation Settings
a) We don't need to check for compiled JSP's every now and then unlike in dev.
b) No debugging for JSP's.

Security Settings
a) Remove universal access to JMS console and WEB consoles.
b) We can even try to restrict the access to this from the local machine.

Log4J Settings
a) Make sure we log only the critical errors.
http://tomcat.apache.org/tomcat-5.5-doc/config/http.html

Load Testing the application
a) This is one critical part that needs lot of application context. First of all we have to be really clear on what we are going to load test and the most probable pain points to look for. Then we can think of the data to test with.

Hibernate Settings
a) Cache Size

OS Settings
a) Large Pages (2.6 kernel supports it seems)

Apache for serving Static Content
a) Traditionally App servers are not great at serving static content. Apache is generally good when it comes to a serving static content. Some reasons for this are web servers having their own disk caching etc.
b) Over time, app servers have improved a lot in serving static content. But going for a web server for serving static content gives us the flexibility of creating a new domain and the content download can happen much faster.

References

http://www.jayson.in/wp-content/uploads/2008/08/jboss-folder-structure.jpg