Setting MySQL Load Balancing in Play and Scala

April 11th, 2012 — 1:34pm

In my previous post I showed a way to set MySQL load balancing with Java and Spring.

To make your queries go to the slave database, you need to set connection to read-only. In Play Framework 2.0 with Scala, this can’t be simpler:

def withReadOnlyConnection[A](block: Connection => A)(implicit app: Application): A = {
    DB.withConnection {
      connection =>
        try {
          connection.setAutoCommit(false)
          connection.setReadOnly(true)
          block(connection)
        } finally {
          connection.setReadOnly(false)
        }
    }
}

Now you can just wrap your database code as:

withReadOnlyConnection[Option[User]] {
   implicit connection: Connection =>

      SQL(....)
      etc, etc.
}

For additional info on Play and Anorm see: Anorm, simple SQL data access

Comment » | Software

MySQL Master/Slave Load Balancing with JPA and Spring

June 13th, 2011 — 1:25pm

MySQL Connector/J driver has built-in feature for load balancing.

If you have a cluster of read/write MySQL servers. Putting loadbalance: in the JDBC URL will ensure both read and write operations are distributed across servers.
jdbc:mysql:loadbalance://master1,master2,master3…/database?loadBalanceBlacklistTimeout=5000&loadBalanceConnectionGroup=cgroup&loadBalanceEnableJMX=true&autoReconnect=true&autoReconnectForPools=true

What we needed is all write operations to go to master server and read-only operations to be equally distributed among multiple read-only slaves.

For that you need to:

  1. Use special JDBC driver: com.mysql.jdbc.ReplicationDriver
  2. Set replication: in the URL:
    jdbc:mysql:replication://master,slave1,slave2…/database?loadBalanceBlacklistTimeout=5000&loadBalanceConnectionGroup=ugc&loadBalanceEnableJMX=true&autoReconnect=true&autoReconnectForPools=true

After setting our connection pool like this, all load still ended up going to our single read/write master server.

The reason is that, for the ReplicationDriver to know that queries can go to read-only slaves, two conditions need to be met:

  1. Auto commit needs to be turned off. (*)
  2. Connection needs to be set to read-only.

(*)There is a workaround to allow auto commit: Connector/J load-balancing for auto-commit-enabled deployments

Turns out, even if transaction is set to read-only, neither Spring nor JPA providers like Hibernate or EclipseLink will set JDBC connection to readOnly.

To ensure JDBC Connection is set to read-only, I created an annotation and a simple AOP interceptor.

Here is an example code:

@Aspect
public class ReadOnlyConnectionInterceptor implements Ordered {

    private int order;
    private EntityManager entityManager;

    public void setOrder(int order) {
        this.order = order;
    }

    @Override
    public int getOrder() {
        return order;
    }

    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Around("@annotation(readOnlyConnection)")
    public Object proceed(ProceedingJoinPoint pjp, ReadOnlyConnection readOnlyConnection) throws Throwable {

        Connection connection = entityManager.unwrap(java.sql.Connection.class);

        boolean autoCommit = connection.getAutoCommit();
        boolean readOnly = connection.isReadOnly();

        try {
            connection.setAutoCommit(false);
            connection.setReadOnly(true);

            return pjp.proceed();

        } finally {
            // restore state
            connection.setReadOnly(readOnly);
            connection.setAutoCommit(autoCommit);
        }
    }
}

One important thing is to set priority of this interceptor to be lower than the Spring’s transaction interceptor. That way we want to make sure transaction is created before we start modifying the connection. In other words, you would set order for @Transactional to be less than order for your new interceptor:

<tx:annotation-driven order="10">
    <bean id="readOnlyInterceptor" class="com.myproject.ReadOnlyConnectionInterceptor">
        <property name="order" value="20"></property>
    </bean>
</tx:annotation-driven>

Now, if you want queries to execute on your pool of slave databases, you can just set an annotation on the service method:

@Transactional(readOnly=true)
@ReadOnlyConnection
public Result serviceMethod(...) {
    ...
}

References:

10 comments » | Software

String class broken in latest JDK

September 30th, 2010 — 11:32am

Java bug 6935535:

public class Test {
    public static void main(String args[]) {
        String str = "1111111111111xx1111111111111xx1x";
        str = str.substring(0, 31);
        String query = "1111111111111xx1x";
        int idx = str.indexOf(query);
        System.out.println("IndexOf(" + query + ") = " + idx + " in " + str);
    }
}
javac Test.java

# Java interpreter
java Test
IndexOf(1111111111111xx1x) = -1 in 1111111111111xx1111111111111xx1

# BUG !! When HotSpot compiler is on
java -Xcompile Test
IndexOf(1111111111111xx1x) = 15 in 1111111111111xx1111111111111xx1

Seen on both Sun JDK 1.6.0_20 and OpenJDK 1.6.0_18

Update: This bug is fixed in Sun JDK 1.6.0_22

Comment » | Software

Merging EOL and Freebase

August 25th, 2010 — 5:41pm

I wrote a simple Acre application that allows users to easily add EOL keys to Freebase organism classification.

Encyclopedia of Life is an ambitious project to create “.. electronic page for each species of organism on Earth..” and Freebase is a open structured data repository recently purchased by Google.

Go to app: http://eolfetch.freebaseapps.com/ (you need to have Freebase account)

Comment » | Software

ActiveMQ flow control and Apache Camel transacted route gotchas

July 9th, 2010 — 9:02pm

We have a system that uses Apache Camel and ActiveMQ. It handles periodic bursts of 20,000 messages. The end consumer is slow and it takes about an hour to process all the messages. On their route, messages are passed from one queue to another. On our production system we stumbled onto an unexpected problem. After finishing few thousand messages the whole system would freeze.

The problem was with ActiveMQ flow control and transacted routes in Camel.

When you have slow consumers, to prevent queues from growing infinitely, ActiveMQ has a limit of how many messages you can put in a queue. When limit is reached, producer is, by default, forced to wait until the resources free. You can set these limits on system level or per queue level. Problem arises when you have transacted Camel route and system limit is reached.

AMQ Flow Control

Route that moves messages from queue A to queue B is inside a JMS transaction – you can’t remove message from queue A until the message is successfully placed on queue B.

If system limit is reached, no new messages can be sent to any queue. So, producers are forced to wait, transaction doesn’t complete, messages can’t be taken off queue A and no resource gets freed. The  whole processing freezes.

There are numerous ways you can work around this problem. You can turn off flow control and potentially let queues grow indefinitely.

In our case, solution was to set per-queue limits so that system limits can never be reached. Sum of limits for all queues needs to be less than the system limit. That way, as consumer takes messages from queue B, new messages can come in, transactions can complete and messages can be taken off queue A. Messages are consumed from queues A and B at the same pace and the whole system works fine.

In our case, I’ve set memoryLimit to 10m for our 13 queues and system memoryUsage to 180m.

For details see :

2 comments » | Software

Solr DisMax parser and stop words

May 25th, 2010 — 11:49pm

If you want to use DisMax parser in Solr you need to be careful how to index the fields that DisMax will be using.

If you mix fields that filter out stop words (plain text) and fields that do not filter out stop words (like author names), your simple queries might end up with no results.

By default, DisMax will display only results that contain all the words from your query string. If your query has stop words like “ants of madagascar”, stop word “of” might not be found in any of the fields – it’s not in author names and it’s filtered out in article body – and you will get zero results.

Possible workarounds:

  • Relax Minimum Match (mm) requirement.
    Downside: Lowering mm will increase number of results. mm of 50% on “ants madagascar” will return all documents that have “ants” and all docs that have “madagascar” in them.
  • Do not filter out stop words.
    Downside: Your index can get large and you might get large number of less relevant results.
  • Use other indexing schemes like N-Grams.

This article explains the details.

Also see this and this discussion.

Comment » | Software

XSLT Unicode Horror

May 18th, 2010 — 10:50pm

Different Java XSLT implementation have different handling of UTF-8 characters. Here is test code that parses UTF-8 XML into DOM document and then serializes it using a transformer.

System.out.println("    SOURCE:  " + source);
DocumentBuilderFactory builderFactory = DocumentBuilderFactory.newInstance(parserClass, TestUnicode.class.getClassLoader());
Document document = builderFactory.newDocumentBuilder().parse(new InputSource(new StringReader(source)));
TransformerFactory transformerFactory = TransformerFactory.newInstance(transformerClass, TestUnicode.class.getClassLoader());
StringWriter writer = new StringWriter();
transformerFactory.newTransformer().transform(new DOMSource(document), new StreamResult(writer));
System.out.println("    RESULT:  " + writer.toString());

I tested following transformers:

  • Xalan 2.7.1:
    • org.apache.xalan.processor.TransformerFactoryImpl
    • org.apache.xalan.xsltc.trax.TransformerFactoryImpl
    • org.apache.xalan.xsltc.trax.SmartTransformerFactoryImpl
  • Sun-Xalan (an internal transformer factory present in Sun JDK 5 and 6):
    • com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl
  • Saxon 8.7:
    • net.sf.saxon.TransformerFactoryImpl

Here are the results for Mathematical Script Capital D character: 𝒟

TRANSFORMER: org.apache.xalan.processor.TransformerFactoryImpl
SOURCE:  <!--?xml version="1.0" encoding="UTF-8"?-->𝒟
RESULT:  <!--?xml version="1.0" encoding="UTF-8"?-->��
TRANSFORMER: org.apache.xalan.xsltc.trax.TransformerFactoryImpl
SOURCE:  <!--?xml version="1.0" encoding="UTF-8"?-->𝒟
RESULT:  <!--?xml version="1.0" encoding="UTF-8"?-->��
TRANSFORMER: org.apache.xalan.xsltc.trax.SmartTransformerFactoryImpl
SOURCE:  <!--?xml version="1.0" encoding="UTF-8"?-->𝒟
RESULT:  <!--?xml version="1.0" encoding="UTF-8"?-->��
TRANSFORMER: com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl
SOURCE:  <!--?xml version="1.0" encoding="UTF-8"?-->𝒟
RESULT:  <!--?xml version="1.0" encoding="UTF-8" standalone="no"?-->𝒟
TRANSFORMER: net.sf.saxon.TransformerFactoryImpl
SOURCE:  <!--?xml version="1.0" encoding="UTF-8"?-->𝒟
RESULT:  <!--?xml version="1.0" encoding="UTF-8"?-->𝒟

Or, summarized in a table:

  𝒟 &#119967;
Xalan 2.7.1 &#55349;&#56479; &#55349;&#56479;
Sun-Xalan (Sun JDK 1.5+) &#119967; &#119967;
Saxon 8.7 𝒟 𝒟

The results were the same regardless of the parser implementation. Xerces or Saxon.

Xalan’s handling of UTF-8 multi-byte characters seems to be seriously flawed. &#55349;&#56479; are not valid UTF-8 characters and both Xerces and Saxon parsers will throw SAXParseException when trying to parse documents that have them.

5 comments » | Software

Nasty bug with generics and introspection

April 19th, 2010 — 5:15pm

Comment » | Software

RDFa on PLoS

October 24th, 2009 — 8:10am

8 comments » | Software

Hoy Kid

October 17th, 2009 — 6:43pm

Laurie wrote another article for Mission Local, San Francisco Mission neighborhood blog/news site. Hoy Kids is turning into a regular column. And my baby boy is on the front page !

Read the article>

Comment » | Mission, Web

Back to top