JOOQ performance with lots of small queries

[Ryan How]

I'm just doing an investigation into JOOQ performance with lots of small queries on an embedded database. I was wondering if anyone else has got any experience on JOOQ overhead in constructing queries and inserting results into "Record" objects or POJOs.

I expect the .into() operation could be quite expensive as it needs to use reflection to work out what to do with the class. And it appears to be quite a complicated algorithm.

Anyway, I'll post some findings. I just wanted to see if anyone else had any experience or data for this use case.

Thanks, Ryan

[Lukas Eder]

Hi Ryan,

There are lots of performance improvements in recent releases of jOOQ,
including 2.6.3 and 3.0-RC1. They also include some improvements of
mapping records to POJOs, where all reflection data is reused
throughout the complete org.jooq.Result. This won't help if you're
running small queries, of course.

As a general rule of thumb, jOOQ can never beat plain JDBC. It will
always have a certain overhead. Generally, this can be said:

1. By default, jOOQ loads the JDBC ResultSet into memory and closes it
early. This can cause some memory overhead, which can be circumvented
by using fetchLazy()
2. jOOQ doesn't really support primitve types. It will always load
wrapper types for ints, longs, etc.
3. Construction and rendering of queries comes with its cost. This has
been greatly improved recently.
4. Reflection will always impair performance. You know your target
domain model better than jOOQ. Use RecordMappers instead of
Record.into() for tuning.

Please, do post your findings when you're ready. There's always room
for improement.

Cheers
Lukas

2013/4/5 Ryan How <rh...@exemail.com.au>:

> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to jooq-user+...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Ryan How]

Hi Lukas,

Just following up on this.

We found that in some over 2/3 of our overall request times were spent in JOOQ. The actual database query time was negligible, the other time was spent on the server rendering complex pages. It wasn't specifically the multiple small queries that were taking lots of time, it was using the fetchInto() in any situation. I'm hoping to change over to JOOQ 3 when we get a chance and I imagine this will improve performance a lot, but like you said wouldn't help the small queries.

Sorry, I haven't got the profiler data here. But we already managed to get huge performance increases just by some tweaking of how we are using JOOQ.

One thing I can think of, which I'm not sure if you would consider... you could cache the results of the reflection (really it is the whole "parsing" algorithm, which uses reflection) globally for each class, so it re-uses the same generated "algorithm" whenever it sees the same class. Seems a waste to need to "parse" the entire class every time .into is called in it's various forms. Or how about being able to pass a "cache" object into the configuration so the lifecycle of the cache can be controlled by the application. It might not be desirable to cache in all cases, especially for one off things, as the memory would never be freed (or global caching could create classloader issues, etc). And with the whole convenience of JOOQ, it would seem a step backwards to have to generate a recordmapper for every POJO. It would mean a whole lot more maintenance every time a field in a POJO changes (and probably would end up resulting in fields getting missed when database schema is updated, then wondering why sometimes a field is null after it has been fetched from the database).

Thanks, Ryan

[Lukas Eder]

Hi Ryan,

 

Thanks for reporting these things. I had thought about similar improvements to the Record.into(Class) algorithm. Indeed, it's a waste of CPU power, to reflectively discover relevant fields and methods at every call.

 

Caching mappers might seem an obvious way to solve this if the cache is sophisticated enough. But one (caching) size won't fit all, it wouldn't be easy to implement this sensibly in jOOQ.

 

Another option would be to leverage code generation, as you said. This is already done when generating DAOs with jOOQ. Generated DAOs instanciate a ReflectionMapper instance for reuse. Without using DAOs, it is a bit more difficult to associate mappers with records and POJOs.

 

Note that a 3.1 feature request plans for adding a RecordMapper factory registry to the Configuration, defaulting to the current behaviour:

https://github.com/jOOQ/jOOQ/issues/2311

 

The request's main purpose was to override the default behaviour to handle more complex mapping scenarios, globally. I think that this might be the cleanest way to solve your performance issues...? What would be any specific requirements you might have towards this registry?

 

Cheers

Lukas

2013/6/17 Ryan How <rh...@exemail.com.au>

[Ryan How]

Hi Lukas,

The record mapper factory sounds like a really good idea. Then it would be possible to plug in a "CachingReflectionMapper" for example to do what I was thinking. Or just maintain a couple of manual mappers for the most used pojos and not have to remember to always call the mapper manually, but continue using .into.

If the generated mappers instantiate a DAO for re-use, wouldn't it just be possible to have a global cache with Map<Class, ReflectionMapper>. Then whenever the class is encountered, it checks the cache and re-uses the reflection mapper (which shouldn't need to do too much work now it knows the class already). But I don't know enough about the internals of JOOQ to know if this would work. I'm sure there would be some multithreading and classloader issues that could arise. I guess the mapper factory would be an ideal solution because multithreading and class loader issues wouldn't then have to be handled by JOOQ.

But I should really start using JOOQ 3 first, it might help performance out so none of this is needed :).

Thanks, Ryan

[Lukas Eder]

Hi Ryan,

2013/6/17 Ryan How <rh...@exemail.com.au>

Hi Lukas,

The record mapper factory sounds like a really good idea. Then it would be possible to plug in a "CachingReflectionMapper" for example to do what I was thinking. Or just maintain a couple of manual mappers for the most used pojos and not have to remember to always call the mapper manually, but continue using .into.

If the generated mappers instantiate a DAO for re-use,

It's the other way round. The generated DAO reuses its internal mapper reference. Unless you aren't referring to the existing functionality.

 

wouldn't it just be possible to have a global cache with Map<Class, ReflectionMapper>. Then whenever the class is encountered, it checks the cache and re-uses the reflection mapper (which shouldn't need to do too much work now it knows the class already). But I don't know enough about the internals of JOOQ to know if this would work. I'm sure there would be some multithreading and classloader issues that could arise.

Multi-Threading would be easy to handle. The cache is not accessed too often, so a single synchronized block shouldn't hurt. But making a global cache class-loading dependent is trickier. A WeakHashMap won't do the trick, as ReflectionMapper (value) references Class (key), which prevents keys from being removed.

OSGi might have its own views on how to correctly implement these things. Unfortunately, I don't have enough experience to get this sensibly right from the beginning. Introducing a badly implemented cache to cover the default behaviour sounds like a bad idea to me... :-)

I guess the mapper factory would be an ideal solution because multithreading and class loader issues wouldn't then have to be handled by JOOQ.

Yes. Since I have been talked into adding such factories to the Configuration in general, things have improved for most users. This will be another good factory use-case, where jOOQ's convenience is just the default behaviour for more sophisticated user-designed implementations.

 

But I should really start using JOOQ 3 first, it might help performance out so none of this is needed :).

Well, in high-throughput scenarios, there is still a considerable amount of CPU waste, when mapping "into" POJOs...

Cheers

Lukas

[Ryan How]

Hi Lukas,

If the generated mappers instantiate a DAO for re-use,

It's the other way round. The generated DAO reuses its internal mapper reference. Unless you aren't referring to the existing functionality.

Oops I actually meant the other way around :). Hands and brain are thinking in different order.

 

Well, in high-throughput scenarios, there is still a considerable amount of CPU waste, when mapping "into" POJOs...

 

Looking forward to JOOQ 3,1 then!.

Thanks, Ryan

[Durchholz, Joachim]

> [...] you could cache the results of the reflection

> (really it is the whole "parsing" algorithm, which
> uses reflection) globally for each class, so it
> re-uses the same generated "algorithm" whenever it
> sees the same class.

Please make sure that the class objects don't get strongly
linked to from the cache.
This is surprisingly hard to get right actually. I once
tried to do something like that, and found that garbage
collection and weak references and WeakHashMaps somehow
do something subtly different than I had thought. In the
end, I punted and declared Introspector "barely sufficient
for the use case at hand".

Since caching reflection is easy to get wrong, if Jooq is
ever going to use that, the hard-to-get-right aspects of
this should be solved by Jooq. Could be done by
- having (or linking to) a full in-depth description
how to do it right,
- offering a reflection cache data structure that does it
right,
- make Jooq's API accept the reflection info, but manage
the cache inside Jooq (and say in the Javadoc that the
code computing that reflection info must never retain
any references to that info).

[Lukas Eder]

2013/6/17 Durchholz, Joachim <Joachim....@hennig-fahrzeugteile.de>

> [...] you could cache the results of the reflection

> (really it is the whole "parsing" algorithm, which
> uses reflection) globally for each class, so it
> re-uses the same generated "algorithm" whenever it
> sees the same class.

Please make sure that the class objects don't get strongly
linked to from the cache.
This is surprisingly hard to get right actually. I once
tried to do something like that, and found that garbage
collection and weak references and WeakHashMaps somehow
do something subtly different than I had thought. In the
end, I punted and declared Introspector "barely sufficient
for the use case at hand".

It is indeed very hard to get right. Consider the WeakHashMap's Javadoc [1]:

Implementation note: The value objects in a WeakHashMap are held by ordinary strong references. Thus care should be taken to ensure that value objects do not strongly refer to their own keys, either directly or indirectly, since that will prevent the keys from being discarded. 

 

It is hard to ensure that a Class<?> key is not indirectly referenced by a map value. The ReflectionMapper, for instance, holds a strong reference to the class and to members of that class.

Since caching reflection is easy to get wrong, if Jooq is
ever going to use that, the hard-to-get-right aspects of
this should be solved by Jooq. Could be done by
- having (or linking to) a full in-depth description
  how to do it right,

- offering a reflection cache data structure that does it
  right,
- make Jooq's API accept the reflection info, but manage
  the cache inside Jooq (and say in the Javadoc that the
  code computing that reflection info must never retain
  any references to that info).

I will certainly think about these things a bit more. But I'm also thankful for contributions by someone who will get it right quicker than me...

Cheers

Lukas 

[1]: http://docs.oracle.com/javase/7/docs/api/java/util/WeakHashMap.html

[Ryan How]

I don't see why it needs weak references? I would just control the cache manually within the application and if I need to get rid of all references then call a clear() method.

Of course if it was inbuilt in Jooq and on by default it would be a different story.

Still, weak hash maps I don't think would solve all issues. It might just prevent memory leaks, which I don't think would be a major issue unless you are using "into" for a growing number of classes... which I guess might happen in an osgi environment or webapp if you happen to be using Jooq from the shared classpath. But in that case you're asking for trouble if you wanna cache application classes in the shared classloader classpath... or something... this stuff does my head in...

Lukas Eder <lukas...@gmail.com> wrote:

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/mM19mjR-49g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

2013/6/17 Ryan How <ry...@zbit.net.au>

I don't see why it needs weak references? I would just control the cache manually within the application and if I need to get rid of all references then call a clear() method.

Yes, at the client side, such a solution is good enough. You know your own class loader.

 

Of course if it was inbuilt in Jooq and on by default it would be a different story.

Precisely. That's what Joachim and I were referring to.

 

Still, weak hash maps I don't think would solve all issues. It might just prevent memory leaks, which I don't think would be a major issue unless you are using "into" for a growing number of classes... which I guess might happen in an osgi environment or webapp if you happen to be using Jooq from the shared classpath. But in that case you're asking for trouble if you wanna cache application classes in the shared classloader classpath... or something... 

The point here is that jOOQ shall not hold any strong references to classes loaded by a ClassLoader C. Once a C is GC'ed, classes loaded by C in jOOQ's cache should "disappear". That's where weak references are useful. In fact, it probably makes sense to check if this rule isn't violated already today.

The weak references won't be GC'ed before the ClassLoader is unloaded (to prevent memory leaks), as each ClassLoader contains a Vector of its loaded classes as strong references. to prevent memory leaks, a fixed-sized LRU Map would probably have to be used.

this stuff does my head in...

Same here. In case of doubt, I won't implement any such cache in jOOQ.

 

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

N.B:

Guava's MapMaker might implement some more sophisticated maps for the task:

http://stackoverflow.com/a/264591/521799

2013/6/17 Lukas Eder <lukas...@gmail.com>

[B...@addepar.com]

You'd want to use CacheBuilder, which allowed us to pull away from the limitations of and not abuse the Map interface.

If you want to embed an LRU cache, which I don't think is the case, many prefer its predecessor (ConcurrentLinkedHashMap) as a lighter-weight dependency to shade. We reworked Guava's implementations based on that project.

As a user, I'd prefer a plugin scheme when possible to tease apart complexities. It would be nice if jOOQ provided a good implementation or two, which might require me to add a few more dependencies to the project, to avoid repeated work.

[Lukas Eder]

Hi Ben,

2013/6/18 <B...@addepar.com>

You'd want to use CacheBuilder, which allowed us to pull away from the limitations of and not abuse the Map interface.

True, I had recently encountered this useful class in Guava, but forgot about it again. They're adding so many nice utilities so quickly, it's hard to keep up with all the good choices.

 

If you want to embed an LRU cache, which I don't think is the case, [...]

No, I don't think it's necessary.

 

many prefer its predecessor (ConcurrentLinkedHashMap) as a lighter-weight dependency to shade. We reworked Guava's implementations based on that project.

As a user, I'd prefer a plugin scheme when possible to tease apart complexities. It would be nice if jOOQ provided a good implementation or two, which might require me to add a few more dependencies to the project, to avoid repeated work.

The "plugin scheme" is much easier to implement than the cache. Also, it serves a much more general purpose (e.g. the custom mapping algorithms you originally suggested), so I'm positive that it will fit in 3.1

Cheers

Lukas