How to relieve Broker performance issues?

Question

In our current project, still running on Tridion R5.3, we publish many components with metadata (category/keywords). Our server-side Java code queries the Broker API for published content, relying heavily on this attached component metadata. The Broker API queries translate into quite inefficient SQL statements with many nested (INNER) JOINs, which take a relatively long time to execute. On several occasions, this has even led to the Broker database going down, thereby bringing down the entire website. To find a solution for this, we are looking into 2 solutions directions:

Migrate asap to Tridion 2013: The question we face is if the Broker performance in Tridion 2013 is better than compared to Tridion 5.3? Are queries that rely on categories/keywords more optimized in some way?

Push component metadata at publish-time into another storage: To relieve the Broker from executing complicated metadata queries, we could perform those queries on another datastore like a search engine or a NoSQL document store. We are wondering if any clients use this type of solution to store and query for component metadata?

Ideas for other solution directions would of course be most welcome.

Answer 1

I don't think the queries themselves have changed (much) in SDL Tridion 2013, but the Query API model has changed a lot, which will result in different queries which shouldn't generate those inner joins anymore (which you could read as the queries have changed).

So definitely it will be worth looking into an upgrade to SDL Tridion 2013, but that does mean you will have to rewrite your Broker code, so maybe you should investigate further with a Proof Of Concept (which will give you an idea of the total work involved and performance gain to be achieved).

Now you are mentioning the inefficient SQL being generated with those nasty inner joins, that is typical R5.3 code which is indeed heavy on your database. However the database going down, I wouldn't expect that to happen, maybe you can elaborate more on that? (do you have Broker caching enabled, what is the Broker code you use that generates the mentioned SQL, is that code optimized, and what information can you give about the Broker database going down, was that a crash, or was it at 100% CPU or memory?)

Answer 2

We also faced quite similar kind of issue while migrating our application from R5.3 to Tridion 2011 SP1. The way metadata is stored in broker is that multiple rows are created for a component (one row for each metadata field) resulting in multiple self join. There were some of the broker queries which were taking forever and hence we had to use raw sql queries on broker to resolve the issue (not a good practise as it wont be supported while upgrading to newer version). I would suggest you to revisit your design and see if it can be refactored in such a way that number of records returned is miniumum.(Assuming you are already using the filters in your broker queries)

Answer 3

Regarding the broad question of "Are queries that rely on categories/keywords more optimized in some way?" the answer is definitely so.

This was partially added in 2009, and further improved in 2011 and 2013. Tridion used to cache only the objects returned by a query result, while now it actually caches the query itself, so unless you published something, the queries should never hit the database (assuming you setup cache properly).

Given that caching queries is almost a dark art form, we took the approach to invalidate query cache everytime you publish anything, since it could change the query results. If you are constantly publishing, then this might not add much improvement, but if you only publish, say, 40-50 times per day, then you should see a noticeable performance improvement.

As was mentioned already, we also replaced and improved the query API and moved away from the "Search Filters" into a more structured "Criteria" API that does give you a lot more flexibility (and less dependencies on the database model).

Regarding alternate storages, with Tridion 2011 we introduced JPA extensibility, so you could use a different storage altogether and still use Tridion to control what gets written where and when. There's some good tutorials about this on SDL Tridion World and pretty good implementation of it on the SI4T extension (Search Integration for Tridion).