VPS (Vignette Professional Services) came into the office last week to help us tune our VCM and database instance. The guy they sent was extremely professional and knowledgeable both of the VCM (which you would expect) but also of Oracle. He offered some tuning for the WebLogic Java Engine that the VCM runs on, but more importantly, showed us the monitoring tools that Oracle has for taking a look at exactly what the database is doing and where the slowdowns are caused.
First, VPS had a number of database configuration changes which would increase the memory space the database was allowed to use, the timeout time, and the number of users. These require a restart to be implemented and did seem to show some small improvements. However, while we were in the Oracle tools, we took a look at some of the VCM actions which take a long time – such as waiting for the Content or Site pages to load, or anytime you go down to the base level in the Content dialog windows. The Oracle tools allow you to see what queries are taking the most time during a slice of time – we could see the query that was causing the slowdown and then were able to add some indexes to the table. Without the indexes, the query will do a tablescan of a huge table (over 150,000 pieces of content). Once we added the indexes, the increase in speed was amazing! All of a sudden the system was reacting the way you would expect from a web application.
Getting the system back to normal was well worth the money spent, however, there were a number of frustrations with the whole process… 1) Vignette should have installed the indexes and written more efficient queries into their software. 2) Vignette should have been able to give us the tuning parameters and what to look for in the database without us having to hire VPS. 3) Our DBA should have been able to find many of the database issues.
A few other caveats – since the tuning last week, the system has been running fine until a huge slowdown yesterday. It seemed to be related to a query that was doing a large tablescan and was structured in such a way that it couldn’t use the indexes we put on that table (according to our DBA). We’ve opened a ticket with Vignette to double check this and see if they have any patches to fix it.
Increasing the timeout is supposed to help with applying permissions in the system, but anything related to applying permissions on the sites seems to just about kill the system still. We use AD (Active Directory) to handle permissions for the VCM, but you have to apply permissions for user groups throughout the VCM. Because we have so much content, this has been timing out. We will have to try applying the permissions at a time when the VCM is not in heavy use and see if we’re still having problems.