Large number of revisions for a single page cause performance problems when using MySQL

When using MySQL and working with pages with a large number of versions, indexing stalls at 99%. The MySQL slow query log shows that queries like this are taking a very long time to run:

select contentent0_.CONTENTID as CONTENTID, contentent0_.CONTENTTYPE as CONTENTT2_, contentent0_.TITLE as TITLE, contentent0_.VERSION as VERSION, contentent0_.CREATOR as CREATOR, contentent0_.CREATIONDATE as CREATION6_, contentent0_.LASTMODIFIER as LASTMODI7_, contentent0_.LASTMODDATE as LASTMODD8_, contentent0_.VERSIONCOMMENT as VERSIONC9_, contentent0_.PREVVER as PREVVER, contentent0_.CONTENT_STATUS as CONTENT11_, contentent0_.SPACEID as SPACEID, contentent0_.CHILD_POSITION as CHILD_P13_, contentent0_.PARENTID as PARENTID, contentent0_.MESSAGEID as MESSAGEID, contentent0_.DRAFTPAGEID as DRAFTPA16_, contentent0_.DRAFTSPACEKEY as DRAFTSP17_, contentent0_.DRAFTTYPE as DRAFTTYPE, contentent0_.DRAFTPAGEVERSION as DRAFTPA19_, contentent0_.PAGEID as PAGEID, contentent0_.PARENTCOMMENTID as PARENTC21_, contentent0_.USERNAME as USERNAME from CONTENT contentent0_ where (contentent0_.VERSION in(select max(contentent1_.VERSION) from CONTENT contentent1_ where (contentent1_.PREVVER=131080 )or(contentent1_.CONTENTID=131080 ) group by contentent1_.LASTMODIFIER))and((contentent0_.PREVVER=131080 )or(contentent0_.CONTENTID=131080 )) order by contentent0_.VERSION asc;

After deleting the version information, rebuilding the indexes takes a much shorter amount of time (less than 30 seconds versus more than a half hour). Customers have reported indexing times over six hours related to this problem.

XML backups from the instance also fail with heap space warnings, even though the instance has a total of two pages and has 1.5 Gb of heap.

Attached is an SQL dump from a sample instance of Confluence 3.1.2 that can be used to reproduce the problem. The sample data was created using the remote API to update two pages a few thousand times. One page simply contains a number (the pass number of the update script). The other page has more data.

Workaround

Always backup your data before performing any modifications to the database.
To remove the version data for the problem pages:

1- Make sure no queries are stuck reading from the content table, as this would prevent you from adding or removing any information from that table. You can either:

• Stop Confluence and make sure the process is no longer running.
• Kill any existing queries reading from the content table using a tool like the mysqladmin command-line utility.

2- Run a query like the following to find pages with over 1000 versions:

select PREVVER from CONTENT group by PREVVER having count(PREVVER) > 1000;

Review the pages by entering the ID returned in the query into a URL like BASE_URL/pages/viewpage.action?pageId=PREVVER as in:
http://wiki.mycompany.com/pages/viewpage.action?pageId=PREVVER
http://wiki.mycompany.com/confluence/pages/viewpage.action?pageId=PREVVER
If the pages are outdated or unneeded, you can simply delete them from within the web interface.
If the pages are still needed, you can remove previous versions using the previous version data from the query above. Simply replace PREVVER in the following queries with the value for the page, and run the queries in the order shown:

delete from bodycontent where contentid in (select contentid from content where prevver='PREVVER');

delete from content where prevver='PREVVER';

3- Rebuild your indexes by rebuilding the content index from scratch.