[opencms-dev] Caching at the OpenCMS persistence layer?

[Andreas Haumer]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

I'm new to OpemCMS and try to learn more about the concepts for
a few weeks now. So far I like it very much and it seems to be the
perfect tool for my needs.

Playing with OpenCMS, I found one issue, though: the database
performance seems to be worse than I'd expected (i.e. the way,
OpenCMS handles the persistence layer, not the RDBMS itself)

Example: With a OpemCMS 6.0 full installation (importing all
available modules and documentation), OpenCMS performs 193
queries on the database just to produce the "Welcome to OpenCMS"
page. Out of this 193 queries, a _lot_ are executed multiple
times within a short time.

For instance:

The query

~  SELECT CMS_ONLINE_STRUCTURE.STRUCTURE_ID,
~         CMS_ONLINE_STRUCTURE.RESOURCE_ID,
~         CMS_ONLINE_STRUCTURE.RESOURCE_PATH,
~         CMS_ONLINE_STRUCTURE.STRUCTURE_STATE,
~         CMS_ONLINE_STRUCTURE.DATE_RELEASED,
~         CMS_ONLINE_STRUCTURE.DATE_EXPIRED,
~         CMS_ONLINE_RESOURCES.RESOURCE_ID,
~         CMS_ONLINE_RESOURCES.RESOURCE_TYPE,
~         CMS_ONLINE_RESOURCES.RESOURCE_FLAGS,
~         CMS_ONLINE_RESOURCES.RESOURCE_STATE,
~         CMS_ONLINE_RESOURCES.DATE_CREATED,
~         CMS_ONLINE_RESOURCES.DATE_LASTMODIFIED,
~         CMS_ONLINE_RESOURCES.USER_CREATED,
~         CMS_ONLINE_RESOURCES.USER_LASTMODIFIED,
~         CMS_ONLINE_RESOURCES.PROJECT_LASTMODIFIED AS LOCKED_IN_PROJECT,
~         CMS_ONLINE_RESOURCES.RESOURCE_SIZE,
~         CMS_ONLINE_RESOURCES.SIBLING_COUNT,
~         CMS_ONLINE_RESOURCES.PROJECT_LASTMODIFIED
~    FROM CMS_ONLINE_STRUCTURE INNER JOIN CMS_ONLINE_RESOURCES ON CMS_ONLINE_STRUCTURE.RESOURCE_ID=CMS_ONLINE_RESOURCES.RESOURCE_ID
~   WHERE CMS_ONLINE_STRUCTURE.RESOURCE_PATH='/sites/default/index.jsp'
ORDER BY CMS_ONLINE_STRUCTURE.STRUCTURE_STATE ASC

is called 12 times

Another query,

~  SELECT CMS_ONLINE_STRUCTURE.STRUCTURE_ID,
~         CMS_ONLINE_STRUCTURE.RESOURCE_ID,
~         CMS_ONLINE_STRUCTURE.RESOURCE_PATH,
~         CMS_ONLINE_STRUCTURE.STRUCTURE_STATE,
~         CMS_ONLINE_STRUCTURE.DATE_RELEASED,
~         CMS_ONLINE_STRUCTURE.DATE_EXPIRED,
~         CMS_ONLINE_RESOURCES.RESOURCE_ID,
~         CMS_ONLINE_RESOURCES.RESOURCE_TYPE,
~         CMS_ONLINE_RESOURCES.RESOURCE_FLAGS,
~         CMS_ONLINE_RESOURCES.RESOURCE_STATE,
~         CMS_ONLINE_RESOURCES.DATE_CREATED,
~         CMS_ONLINE_RESOURCES.DATE_LASTMODIFIED,
~         CMS_ONLINE_RESOURCES.USER_CREATED,
~         CMS_ONLINE_RESOURCES.USER_LASTMODIFIED,
~         CMS_ONLINE_RESOURCES.PROJECT_LASTMODIFIED AS LOCKED_IN_PROJECT,
~         CMS_ONLINE_RESOURCES.RESOURCE_SIZE,
~         CMS_ONLINE_RESOURCES.SIBLING_COUNT,
~         CMS_ONLINE_RESOURCES.PROJECT_LASTMODIFIED
~    FROM CMS_ONLINE_STRUCTURE INNER JOIN CMS_ONLINE_RESOURCES ON CMS_ONLINE_STRUCTURE.RESOURCE_ID=CMS_ONLINE_RESOURCES.RESOURCE_ID
~   WHERE CMS_ONLINE_STRUCTURE.RESOURCE_PATH='/sites/default'
ORDER BY CMS_ONLINE_STRUCTURE.STRUCTURE_STATE ASC

is called 17 times.

There are several other examples. Loading the "Template One Demo"
startpage produces 965 database queries (with an empty FlexCache),
where some queries are executed more than 80(!) times. All these
queries are executed against the OpenCMS database within a short
time. They all take a few milliseconds each, but in sum the time
and load they produce on the database server is quite noticeable.
To me it looks like those queries are good candidates for utilizing
a persistence layer cache.

Now I already know that OpenCMS has some caching mechanism
on the web layer (FlexCache). I did a (very) short look on the
sources and it looks like OpenCMS uses its own persistence layer
implementation, which currently does not seem to have a caching
mechanism in itself (only the query strings as stored in the
query properties seem to get cached in a map).

Are there any plans to implement one? Are there any plans to
use some other persistence framework (like Hibernate), where
cache implementations might already exist?

I know it is a lot of work to change such an important
layer in an existing piece of software, but I would really
like to know your thoughts about it as I might get a little
bit deeper into this. Perhaps someone is already working on
it? Is there any documentation or concept paper dealing with
the concepts, issues and details of the persistence layer and
data model? Do you think it is worth the effort anyway?

Please share your thoughts with me. I'm open for suggestions
and discussion!

Regards,

- - andreas

- --
Andreas Haumer                     | mailto:andreas at xss.co.at
*x Software + Systeme              | http://www.xss.co.at/
Karmarschgasse 51/2/20             | Tel: +43-1-6060114-0
A-1100 Vienna, Austria             | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFC/gWaxJmyeGcXPhERAo07AJ0bPkZvHOS4RpJB1f8FbTtakZCB3QCeMLBy
GsmDe+VIy8vGhjxJb2Hu8iE=
=ooXu
-----END PGP SIGNATURE-----