JDBC persistance with pojo cache and performance issue

[jlukar]

I turned on JDBC persistance with Informix backend using TreeCacheAOP.

The performance numbers are horribly slow.

The "jbosscache" table created as part of service startup has the columns "fqn","node","parent" with types varchar(255), blob, varchar(255) respectively.

The inserts of my pojo business objects as they are serialized into byte stream by the cache, are taking on the average of 15 seconds for each row. The DB is on another server off of the subnet.

denormolizing the pojo object and writing a row out with all pojo fields to non-blob/byte columns as varchar, integers, etc. is a lot faster.

Is this going to be the expected behaviour where JDBC persistance of POJO objects using serialized byte stream as default mechanism by TreeCache/AOP is going to be magnitude slower ?

Can someone suggest a way to use JDBC persistance and yet make it fast or as fast as file persistance, etc. ? Maybe my own cacheLoader scheme ?

I have to use JDBC/informix(vs. sleepycat) in order to not worry about local disk failure issues on persistance and recovery. A remote DB seems to be way.

thanks

[belaban]

You're saying BLOBs are terribly slow ?
You know that you can override the types of the columns in the created table ? And you can also override the SQL for the INSERT and UPDATE statements, so you could possibly replace the BLOB with a VARCHAR. But (and my understanding of SQL is limited), VARCHARs have a limited length, so your data would have to be below that limit.
If you suggest a different mapping, we'll be happy to take a look. Especially if it improves performance

[jlukar]

I am thinking that writing a BLOB to DB in itself should not be that slow. Its is probably the way the aspectized POJO is being serialized and writen to DB is taking a while.

To not use BLOB, would mean I have to break up my POJO and write out fields seperatly to jbosscache table, right ?

I assume that should be done using JDBCCacheLoader or my own custom cache loader. Is this correct ?

Do you think, hybernate buys me anything here, or am I totaly confused.

thanks for info.

[ben.wang]

This does not sound right. Pojo cache serializes to cache loader are all primitive fields (e.g, String & int) in the node hashmap. So unless the granularit is causing loader to slow down, otherwise it should not be 1 order of magnitude slower.

Can you provide a test case?

-Ben

[belaban]

Yes, a test case would be helpful. Are you sure your POJO is *really* aspectized ? Otherwise, we serialize the POJO and store the *entire* serialized POJO in a value (a.k.a. a BLOB) in the DB

[ben.wang]

But if not "aspectized" properly, this will be just like a regular cache object of which should not be more expensive.

[hmesha]

The sql statements are straight forward so I'm very skeptical that the problem is JBoss Cache cache loader. Can you turn logging level to debug and extract the sql statment going to the database then try to run it within informix client to see if the database itself is taking long to handle blobs? Also Can you try a different version of the jdbc driver. It might be the way the jdbc driver is handling blobs causing the slowness you're seeing. Finally, as Bela said you can change the cloumn types and even the create statement for the jboss cache table, if non of the above turned out better results. This will tune the sql statment better for informix.

- Hany

[jlukar]

I traced through the code and found that the majority of the time for processing of one POJO object (14 seconds out of 17 seconds) was spend in

JDBCCacheLoader.put(List modifications)

The insert of serialized object into DB didn't take any more than 300 miliseconds.

The POJO object has about a 100 fields, primitives mostly, distributed between 3 different interfaces, one interface extending another, all the way to the POJO object that implements the aggregate interface.

This delay however seems to be only endemic of the JDBCCacheLoader. The FileCacheLoader does not display this kind of behaviour.

I can put togehter a test case and upload to Jira ?

[ben.wang]

Yes, that would be great to attach it in Jira.

thanks,

-Ben

[jlukar]

I just created Jira issue.

I have created a test case with dummy business objects and associated class/interfaces that showcases the slowness of this scenario. There is a build.xml that does the whole thing after configuring the jdbc parameters in the service xml file.

I'd appreciate any feedback later on when you have a chance to look at it.

[ben.wang]

Do you have the Jira issue at hand? I can't seem to locate it under JBCache jira. :-)

-ben

[jlukar]

you might already have the issue number. Its JBCACHE-382

here is the link I got in my email. I still don't see it on the jira main page for jboss cache.

http://jira.jboss.com/jira/browse/JBCACHE-382?page=all

[ben.wang]

Ihave updated my finding in the Jira, please take a look.