Archive Job Performance Decreases Dramatically After Retain 2 to Retain 3 Migration

Customer's archive jobs used to take 20 minutes.  After migrating to Retain 3, the same job is taking over 5 hours.

We implemented slow query logging in MySQL to see if there were any queries taking over 1 second during an archive job.  Sure enough, queries against the t_document table were taking 1.66 seconds.  With another customer, they were taking over 6 seconds.

Upon checking the indexes for the t_document table in the database, it was discovered that one or more of the indexes were missing. 

During the final phase of the migration (when you choose to "exit"), the migration utility creates the indexes for the new tables.  Somehow, this step must have generated an error or the migration utility was prematurely exited before being able to finish because indexes do not just go missing.

After creating the missing indexes, the job completion time went back to its normal 20 minutes.

Here are the four columns in the t_document table that should be indexed:

• document_id
• hash
• tagID
• f_referenceCount

To get a list of indexes for a table in MySQL, type:  show indexes from retain.[tablename];  (where the database name is "retain").

In this case, once we saw in the MySQL slow query log (which we had activated) that t_document was the culprit, we queried the table as follows:

This example appropriately has all of the indexes for t_document that should exist.

If an index is missing from any table, see "Creating Missing Indexes for Retain Tables".

This article was originally published in the GWAVA knowledgebase as article ID 2336.