Faster MySQL Connector/J

I have submitted 4 new patches to improve the performance of MySQL Connector/J:

• [71621] MysqlXAConnection#xidToString(Xid xid) produces too much garbage
  
  This one affects all applications which use XA transactions. Applying the patch attached to the issue avoids creation of a lot of garbage produced by converting Xid to a string when executing the XA commands.
   
• [71623] Field#getStringFromBytes() creates useless byte array when using JVM converter
  
  When the JDBC driver uses JVM Charsets (e.g. you set useJvmCharsetConverters to true) to convert bytes representing the meta-data of a ResultSet into strings, the current implementation would create an useless byte array. The patch attached to the issue fixes this problem.
   
• [71672] Every SQL statement is checked if it contains "ON DUPLICATE KEY UPDATE" or not
  
  The current implementation of server prepared statements (e.g. you set useServerPrepStmts to true) in Connector/J checks every SQL statement, whether it contains the clause "ON DUPLICATE KEY UPDATE" or not. But this clause is only valid for INSERT statements. The patch attached to the issue changes the code of Connector/J to perform this check only for INSERT statements and it adds also an option "avoidCheckOnDuplicateKeyUpdateInSQL", which is set to false per default, to disable the check even for INSERT statements, as not all applications make use of the "ON DUPLICATE KEY UPDATE" clause.
   
• [71679] Avoid iterator creation when invoking statement interceptors in MysqlIO
  
  This is a minor change, which just avoids creation of an Iterator instance when executing statement interceptors.

So in case you want to improve the performance of your application and you don't want to wait, until Oracle releases a new version of MySQL Connector/J, then you should consider patching MySQL Connector/J using my patches.

MySQL Connector/J throws too many SQLExceptions

Some time ago while profiling the product I'm working on, I saw in the profiler that a lot of SQLExceptions are thrown during the execution of one of our use cases. But none of them shows up in the log files. We use the latest version of MySQL Connector/J with XA transactions. The URL we use to connect to the database looks like this:

jdbc:mysql://localhost:3306/myschema?useConfigs=maxPerformance&useLocalTransactionState=true&rewriteBatchedStatements=true&useCursorFetch=true&defaultFetchSize=50&maintainTimeStats=false&useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=true&dumpQueriesOnException=true

The majority of the SQLExceptions were thrown on closing of already closed ResultSets. After looking into the bug database I found that this problem were already reported, s. the bug #67318. The fix for this bug is very easy. You can find the patch attached to the bug report. Fixing this bug helped me to reduce the number of thrown SQLExceptions from roughly 420000 (yep, so many exceptions) to ca. 25000 exceptions.

Profiling again the application I found that other SQLExceptions were thrown when executing the XA commands. I found it very, very strange. I mean, all transactions are processed without problems. In the URL above we force the JDBC driver to use server prepared statements. But the current implementation of the prepared statement protocol does not support XA commands yet. So the Connector/J sends a XA command first as a prepared statement. The server rejects it with the message:

This command is not supported in the prepared statement protocol yet

The server rejection results in an SQLException which we see in the profiler. The JDBC driver catches the exception and resends the XA command as a plain SQL statement. That is every XA command is sent twice to the MySQL server. I have reported this behavior in the bug report #67803. The fix for this issue is also very easy - only two additional lines of code. The patch is attached to the bug report. After fixing this bug we see no SQLExceptions anymore in the profiler. The best impact of fixing this bug was the performance improvement in our application, up to 15%, and the reduced network traffic.

So if you use MySQL with server prepared statements and XA protocol, then you should consider patching the MySQL Connector/J using the patches attached to the bug reports as long as Oracle has not yet integrated them into their code base.

JBoss 7 and WebSockets

As far as I know there is now no WebSockets support for web applications in JBoss 7. JBoss 7 uses JBoss Web as its servlet container. JBoss Web is based on Tomcat. Tomcat developers added only recently the support for WebSockets. Mike Brock from RedHat started some days ago to work on the WebSockets support for JBoss 7. But his solution is far away from being production ready and it is targeted for JBoss 7.1.2+ only. So if you have only one single JEE application which needs WebSockets support and you want deploy your application on JBoss 7 and you wish to have a single port (e.g. 8080) for your application,  then maybe I have a possible solution for you.

OK let me summurize the requirements for my solution:

• our application must be deployed on JBoss 7.
• all content (JSP, HTML, JavaScript, WebSockets, Images, etc.) of the application must be accessed through one single port (e.g. 8080).
• it must be possible to receive and send data through WebSockets.

For my solution I'm going to use the wonderful, embeddable servlet container Jetty. For older JBoss versions (I mean really old like JBoss 4.2.3) there is a Jetty module, which can be used to replace JBoss Web. But it does not work with JBoss 7 and Jetty team has no plans to provide a new module for JBoss 7.  On the other hand Jetty has a lot of other cool things which allow us to use WebSockets in JBoss 7.

The main idea of the solution is to embed Jetty inside the WEB application and to ensure that Jetty delegates all plain HTTP requests to JBoss Web and handles all WebSockets requests itself. The first thing that we have to do is to determine at runtime on which port and host JBoss Web is running. This is needed to avoid hard-coding of host and port in our application. OK let's do it using JMX API:

String jbossWebHost;
Integer jbossWebPort;

MBeanServer mBeanServer = ManagementFactory.getPlatformMBeanServer();
try {
    ObjectName http =
        new ObjectName("jboss.as:socket-binding-group=standard" +
            "-sockets,socket-binding=http");
    jbossWebHost =
        (String) mBeanServer.getAttribute(http, "boundAddress");
    jbossWebPort =
        (Integer) mBeanServer.getAttribute(http, "boundPort");
} catch (Exception e) {
    throw new Error(e);
}

The next step is to create a transparent proxy, which will delegate all plain HTTP requests to JBoss Web. For this purpose I will use ProxyServlet from Jetty:

/**
 * @author Andrej Golovnin
 */
final class TransparentProxy extends ProxyServlet {

    // Fields *************************************************************

    private String jbossWebHost;
    private Integer jbossWebPort;


    // Instance Creation **************************************************

    TransparentProxy(String jbossWebHost, Integer jbossWebPort) {
        this.jbossWebHost = jbossWebHost;
        this.jbossWebPort = jbossWebPort;
    }


    // Overriding default behavior ****************************************

    @Override
    protected HttpURI proxyHttpURI(String scheme, String serverName,
        int serverPort, String uri) throws MalformedURLException
    {
        try {
            URI dstURI = new URI(
                scheme + "://" + jbossWebHost + ":" + jbossWebPort + uri)
                .normalize();
            if (!validateDestination(dstURI.getHost(), dstURI.getPath())) {
                return null;
            }
            return new HttpURI(dstURI.toString());
        } catch (URISyntaxException e) {
            throw new MalformedURLException(e.getMessage());
        }
    }

} 

To handle WebSockets requests in Jetty you must create a subclass of WebSocketServlet and implement one of the subinterfaces of WebSocket. Instead of writing my own example of WebSockets usage in Jetty I have used a modified Jetty Test WEB application from the Jetty project. The test WEB application provides a small chat application, which uses WebSockets for the communication between clients and server. The test application from the Jetty project has a servlet named WebSocketChatServlet. I will use this servlet for the demonstration of my solution.

The next step is to register the WebSocketChatServlet and the TransparentProxy with a Jetty instance. For this purpose I will use ServletContextHandler. The TransparentProxy should be registered for the root context path:

ServletContextHandler proxy = new ServletContextHandler(
    ServletContextHandler.SESSIONS);
proxy.setContextPath("/");
proxy.addServlet(
    new ServletHolder(new TransparentProxy(jbossWebHost, jbossWebPort)),
    "/*");

The WebSocketChatServlet is registered to a defined context path, which is then used in the application to establish the WebSocket connection:

ServletContextHandler ws = new ServletContextHandler(
    ServletContextHandler.SESSIONS);
ws.setContextPath("/test/ws/chat");
ws.addServlet(new ServletHolder(new WebSocketChatServlet()), "/*");
ws.setAllowNullPathInfo(true);

It is important to note that you must allow null path info for your WebSocket servlet. If you don't do it, Jetty will redirect the request "/test/ws/chat" to "/test/ws/chat/" and the WebSocket connection won't be established. Registering of both ServletContextHandlers with Jetty server is very simple:

server = new Server();
.....
ContextHandlerCollection contexts = new ContextHandlerCollection();
contexts.setHandlers(new Handler[] {proxy, ws});

server.setHandler(contexts);

Starting and stopping of the Jetty server is done in an implementation of a ServletContextListener which is registered in the web.xml of the WEB application. You can find the full code of the solution on GitHub. I have also created a ready to deploy example. You can just put it into the deployments directory and go to http://localhost:8181/test to see it in action. Everything else on your JBoss installation should be also accessible on port 8181, e.g. the start page of JBoss 7. The port 8181 were chosen to simplify the deployment of the test application on a plain JBoss 7 installation.

The presented solution has some disadvantages:

• it works only if you have a single application which needs WebSockets.
• you can not use web.xml to deploy WebSocket servlets.
• there is additional overhead to process plain HTTP request as Jetty must establish a connection to JBoss Web.

The advantage of this solution is that it can be used theoretically with any application server which does not provide WebSockets support for now.