Monday, May 4, 2009

Hibernate Wars: The Query Cache Strikes Back

Not so long ago, in a galaxy not very far away... a middle-aged programmer battled to free memory from the dark side of the hibernate query cache.  He was successful, or so it seemed. For the query cache memory problems had risen from the ashes -- stronger and more bloated than ever...

What's With All This Garbage?

We hit a case at work (again), where the java server process came to a grinding halt.  It wasn't dead, it just wasn't making much forward progress either.  A quick look at the heap stats showed we were nearly at capacity and that garbage collection was eating all CPU time, thus not allowing any real work to happen.  Looks like it is time to whip out the ol' memory profiler.  This time I went with Eclipse's Memory Analysis Tool .  It's pretty slick.  I suggest you try it.

Query Cache Waste Redux

Not terribly unexpected, the hibernate caches were the top consumer of heap space.  I have previously explored ways to trim down the memory used by the query cache.  But, we depend heavily on it and size it pretty big, so that's why I wasn't too surprised to see them at the top of the heap report.  Drilling down a little further showed that it was not the contents of the query cache results that were causing the problem.  That was the unexpected part.  Rats, our friend QueryKey is once again a giant waster of memory.

What is it this time?  In A Dirty Little Secret, I showed that you should use object identifiers in your HQL and as parameters so that full objects are not stored as part of the QueryKey.  This was a major win.  I also showed that by providing a decorator query cache implementation, you can reduce all duplicate stored parameters by replacing them with canonical representations.  Using MAT (Memory Analysis Tool), I proved that this was still working as expected.

What I hadn't previously accounted for was the QueryKey.sqlQueryString field.  Since we use a lot of natural id query cache optimizations, we have tens of thousands (and in some cases, well over 100,000) copies of identical queries tucked away in QueryKey as the sqlQueryString (thus, differentiated only by the query parameters).  And since hibernate generated SQL is not exactly terse, we have a nice formula for memory explosion.

Different Tune, Same Dance

We're already using my decorated query cache which eliminates duplicates in the parameters.  So I decide to modify it to also swap out the sqlQueryString for a canonical representation.  One caveat is that sqlQueryString is private and final.  Lo and behold, you can modify private final fields with reflection in Java 1.5!  Granted, you could really do some silly stuff and fake out the JVM and screw up compiler optimizations, but we're only replacing the field with another String that should be functionally equivalent, so hopefully any 'weirdness' is mitigated.  Again, for licensing reasons I won't paste the whole query cache decorator.  Creating the full one (and the factory to instantiate it) is left as an exercise to the reader.

The modified query cache decorator looks like this:

private final Map<Object, Object> canonicalObjects = new HashMap<Object, Object>();

public boolean put(QueryKey key, Type[] returnTypes,
@SuppressWarnings("unchecked") List result, boolean isNaturalKeyLookup,
SessionImplementor session) throws HibernateException {

// duplicate natural key shortcut for space and time efficiency
if (isNaturalKeyLookup && result.isEmpty()) {
return false;


return queryCache.put(key, returnTypes, result, isNaturalKeyLookup,

private void canonicalizeKey(QueryKey key) {
try {
synchronized (canonicalObjects) {
} catch (Exception e) {
throw Exceptions.toRuntime(e);

private void canonicalizeParamValues(QueryKey key)
throws NoSuchFieldException, IllegalAccessException {
final Field valuesField;
valuesField = key.getClass().getDeclaredField("values");
final Object[] values = (Object[]) valuesField.get(key);

private void canonicalizeQueryString(QueryKey key)
throws NoSuchFieldException, IllegalAccessException {
final Field sqlQueryString;
sqlQueryString = key.getClass().getDeclaredField("sqlQueryString");
Object sql = sqlQueryString.get(key);
Object co = ensureCanonicalObject(sql);
if (co != sql) {
sqlQueryString.set(key, co);

private void canonicalizeValues(Object[] values) {
for (int i = 0; i < values.length; i++) {
Object object = values[i];
Object co = ensureCanonicalObject(object);
values[i] = co;

// assumes canonicalObjects is locked. TODO: consider a concurrent hash
// map and putIfAbsent().
private Object ensureCanonicalObject(Object object) {
Object co = canonicalObjects.get(object);
if (co == null) {
co = object;
canonicalObjects.put(object, co);
} else if (co != object) {
// System.out.println("using pre-existing canonical object "
// + co);
return co;

As you can see, we simply change the sqlQueryString via reflection just like we do the param values.

Your Mileage May Vary

How much of a win this is for you depends on your use case.  As I said, since we heavily use the natural id query optimization, we had tons of repeated sql strings.  So, exactly how much memory you 'reclaim' in this fashion totally depends on the variety and uniqueness of the queries you run through the hibernate query cache.

Bonus: L2 Cache Reduction

While we're at it, I noticed a few other things that seemed odd in the L2 cache.  There was more HashMap overhead than there was contents in the cache itself.  I poked around the source a bit and saw that every entry in the L2 cache was being turned into a property->value map before it was stored in your L2 cache provider (and the inverse process occurs on the way out).  This seemed odd to me, as we already have a decomposed CacheEntry object which is an array of the Serializable[] properties from your persistent entity.  Why create another (less efficient) representation as well as introduce unnecessary conversions?  After some google-fu, I realized you can bypass this conversion by setting hibernate.cache.use_structured_entries to false in your hibernate configuration.

Any docs I found on hibernate.cache.use_structured_entries merely seemed to suggest that it stores the properties in a more 'human friendly format.'  And, who wouldn't want that?  And, all examples we built on when first starting with hibernate turned it on, so... so did we.  What they don't mention is what it actually does, and the penalty you pay for doing so -- which is apparently too much HashMap overhead for what should be pretty simple in memory storage.

However, be aware -- this only works for non-clustered, in-memory use of L2 cache.  Apparently, if you cluster your JVMs and need L2 cache clustering, the in/out of the property order cannot be guaranteed between JVMs.  Thus, you have to use structured entities in the cache so they can be re-hydrated properly by property name.

Right-Size Your Caches

We moved to the 1.6 release of EH Cache, so... this may only apply to that version.  But, I noticed that whatever you configure as 'max elements' for ehcache, it uses as the 'min capacity' for the ConcurrentHashMap.  Needless to say, if you size them for your potential largest usage, but then deploy to a smaller-usage environment, you can end up with some waste in the overhead for the hash tables.  It didn't seem terribly significant in my case, but it did show up on the radar.


Even in today's multi-gigabyte server (heck, even desktop...) environments, memory is still a precious resource.  Even if you have lots of it available, wouldn't you want to make more use of that memory instead of having it wasted?  Freeing up wasted memory means there is more for the 'transient' objects that come and go quickly.  There's less pressure to garbage collect and try to find available heap.  And, there's more memory available to do smart things with useful caching. In short:

  • Use a query cache decorator to reduce parameter and SQL string duplication.

  • If you are in a single JVM using in memory cache only, use hibernate.cache.use_structured_entries=false in your hibernate configuration.

  • Right-size your caches to reduce hash table overhead.

Tuesday, April 14, 2009

Arbitrary Replication/Proxy Topologies with MySQL Sandbox

How would you like to use MySQL Sandbox to deploy and test any replication topology imaginable? Do you want to describe load balancer and proxy configurations, too? If you already love sandbox (or even if you don't), and you want to play with arbitrarily complex replication/proxy topologies... read on.

Sandbox Is Your Friend

MySQL Sandbox is a great tool. As of this writing, version 3 is feature complete -- and the new features are greatly desired. Replication support has been there a while for master/slave setups with as many slaves as you want and for replication rings of any size. Version 3 adds multi-level replication trees on a multi-sandbox using sbtool.

But what if the provided replication setups do not quite match what you need? Granted, I'll argue they are sufficient for 99% of setups. But there's always that 1%... Then, throw MySQL Proxy in the mix and things really start get interesting. Sandbox (at the moment) supports very limited proxy configuration.

It's Just a Directed Graph

Giuseppe and I had some conversations a while back on ways to describe replication topologies. In its purest form, a replication topology is a directed graph with some limitations imposed by MySQL replication technology itself (a slave cannot have two master). One can either think of it as Master->Slave (conceptual data flow direction), or Slave->Master (connection initiation direction). I choose to think of it as Master->Slave, because for my wee bitty brain it helps to think in terms of the event flow.

After a modicum of research, I realized the Graphviz DOT language seems very well suited to describing graph/network topologies. Sure, the intention of dot is mostly for visualization. But the language itself is generic enough to describe graphs, nodes, edges, and any properties you want on any of those components. Perfect.

Enter Sandbox

So, we have a generic graph description language. We have a tool that can deploy nodes and configure them. Sandbox is in perl. There exists Graph::Reader::Dot in CPAN. We. Are. Golden. In short order, I've got the latest MySQL::Sandbox and Graph::Reader::Dot installed into my perl environment. It doesn't take long to start reading in dot files and deploying mysqls with sandbox. The rest is just SMOP™ -- a Simple Matter of Programming™.

Enough with the History Lesson

The perl code is here: make_sandbox_topology. Get the perl prerequisites from CPAN, MySQL::Sandbox and Graph::Reader::Dot. Then in typical sandbox fashion, you invoke it as

./make_sandbox_topology { VERSION | tarball }

Some example dot topologies follow now...

digraph MasterSlave

Master, Slaves

digraph MasterSlaves
... and repeat with as many slaves as you want.

digraph Tree


digraph MasterMaster


digraph Ring

Ring With Slaves

digraph Ethanol

digraph Benzene

Ok. Enough sillyness. I think you get the point.

Let There Be Proxy

Proxies are just another node in the topology. But, we have to identify them to sandbox as a different type, so they are not deployed as mysql instances. In dot notation, this becomes Proxy [class=proxy]. How about a simple load balanced setup with two slaves.
digraph LoadBalanced

LoadBalancer [class=proxy];

The LoadBalancer when deployed and started, is now configured for the two slave backends. Notice, however, that there is no lua goodness for the proxy (yet)....

Let's do one more with a combination of load balancing and pure proxy.

digraph ProxyExample

LoadBalancer [class=proxy];

ProxyM [class=proxy];

ProxyA [class=proxy];
ProxyB [class=proxy];

In this case, SlaveA and SlaveB are load balanced, and every other mysql node has a proxy in front him (for who knows what, query analysis perhaps?) except for the standby slave.

Node Configuration

The previous section introduced the first concept of a node's configuration. A node's class defaults to mysql, but can be specified as proxy. All other attribute=value pairs are passed as configuration to the deployed type.

Topology Level
digraph Simple

Currently, the only 'topology' level supported attribute is baseport. It, as you might guess, defines the starting port for port assignment via sandbox.

MySQL Class

Any argument that is valid for a mysqld command line or my.cnf configuration file is fair game for a mysql class node configuration. Want to hard code the port? Want to make a slave read-only? No problem.

Slave [port=9999, read_only=1]

Now the slave will be deployed via sandbox with the given port 9999 and read_only in the my.sandbox.cnf

Proxy Class

Any argument that is valid for mysql-proxy command line or ini file setting may be set as node configuration. The class=proxy attribute is not forwarded, as this is a recognized 'internal' node config option.

Proxy [class=proxy, "log-level"=debug, "proxy.proxy-lua-script"="share/mysql-proxy/lb.lua"]

Notice, the quotes around some of the keys and values is due to dot grammar limitations.

Default Node Attributes

Obviously, a lot of configuration could end up being repeated. And that sort of defeats the purpose of a lot of the automation that sandbox provides. Dot provides a default node attribute concept that we can build on.

node ["proxy.log-level"=message, "proxy.proxy-lua-script" = "script.lua",
"mysql.read_only"=1, "mysql.default-character-set"=utf8 ]

"node" is literally n o d e. That is the dot syntax for "every node should contain these attributes." Every proxy will have log-level as message and the script.lua lua script, unless the node definition itself overrides that same attribute. Likewise for mysql nodes. Every one would be read only and utf8, unless the node itself is explicit about those attributes.
Since the options are really passed to ALL nodes, I had to scope the attributes to what classes they really belong to. To me, it makes it slightly more readable at a 'default attribute' level anyway, but one could envision being smarter about what attributes belong to what classes, and doing some automagic scoping for you. The only problem then would be collisions -- which could be resolved with explicit scoping in those cases.


I've already joined sandbox-developers on launchpad. Next step is to work with the team and get it on the blueprints, and then into sandbox release.

Once on launchpad, we'll start soliciting user ideas for features.

Issues and Limitations
  • Dot language. Attribute key and value grammar is pretty limited, so remember to quote them. I don't catch parse errors and die (yet), so if you get funky errors later remember to check your dot syntax.
  • Only valid replication topologies are allowed. If you try to configure two masters for one slave, we'll catch it. Wait, this is actually a feature, not a limitation...
  • master/slave binlog events not proxied, so it is caught and disallowed. Maybe proxy will proxy binlogs... soon?
  • Currently it does not honor all sandbox options (like user and password, or port conflict detection and resolution)
  • the Blogger editor REFUSES to maintain proper whitespace in my dot examples. Ideally, I like to use 4-space indent. But it keeps removing them for me, so I'm tired of adding them back in. sorry.
Please remember, this is just a rough cut at the concept. Architecture and design could use some serious refactoring. But, it's out there to use and abuse -- and we'll see about getting into MySQL::Sandbox proper.

If desired, I can followup in further blogs with actual usage examples and walk-throughs. Perhaps the best place for documentation though is on mysql forge.