Creation of a new board hangs GreenHopper, spikes CPU and causes performance problems with JIRA

Summary

Creation of a new board is executing 7k SQL queries against the DB, at roughly 450 a second. This is tanking JIRA.

Environment

-Xms2048m -Xmx10240m -XX:MaxPermSize=512m.

Steps to Reproduce

Create a GreenHopper board.

Expected Results

The board creates without problems.

Actual Results

It takes 10-15 minutes to create, maxes the CPU for 2.5 hours and can bring JIRA to a standstill (seems like GC is going nuts thrashing that mighty 10gb heap and spiking the CPU load).

Notes

Unable to replicate locally. This is likely caused by the presence of the following:

 select count(*), entitytype from sharepermissions group by 2 order by 1 desc;
+----------+---------------+
| count(*) | entitytype    |
+----------+---------------+
|     6997 | SearchRequest |
|      624 | PortalPage    |

select count(*) from searchrequest;
+----------+
| count(*) |
+----------+
|    10749 |

So 6.9k shared filters. Yikes.

Workarounds

Either of the following will workaround this issue:

• Block the /rest/greenhopper/1.0/rapidview/create/presets call on a reverse-proxy until this can be fixed.
• Remove some unnecessary or unused filters.

Please take a look through the following docs for info on how to block URLs:

• Eight Ways to Blacklist with Apache’s mod_rewrite.
• IIS 7.0 Request Filtering and URL Rewriting.

To remove the filters:

1. Backup the JIRA database prior to making any changes. If available, run this on a test server first.
2. Identify which filters to delete with the following SQL. This will identify all users who have not logged in for 90 days and any filters owned by them:

   SELECT 	u.id AS user_id,
   	u.lower_user_name,
   	u.display_name,
   	to_timestamp(CAST (a.attribute_value as float)/1000) AS last_login,
   	s.filtername,
   	s.id AS filter_id
   	
   FROM 	cwd_user u
   
   JOIN 	cwd_user_attributes a
   ON 	u.id=a.user_id 
   
   JOIN	searchrequest s
   ON	u.lower_user_name = s.username
   
   WHERE 	attribute_name = 'lastAuthenticated'
   AND	to_timestamp(CAST (a.attribute_value as float)/1000) <= current_date - 90;
   

   This is PostgreSQL and may require modifying based on your DBMS.

3. Execute the following DELETEs to remove the filters using the searchrequest.id (filter_id from the previous query).

   DELETE FROM favouriteassociations WHERE entitytype='SearchRequest' AND entityid='10000'
   DELETE FROM sharepermissions WHERE entityid='10000' AND entitytype='SearchRequest'
   DELETE FROM searchrequest WHERE ID='10000'
   

   This will remove all entries for the filter with an id of '10000'.

4. Test the instance. If all is OK, attempt to create a new board.

To identify any filters used within Gadgets, the following SQL may be used:

SELECT 	p.pagename AS dashboard_name,
	s.filtername,
	s.id AS filter_id

FROM 	portalpage p

JOIN 	gadgetuserpreference g
ON 	p.id = g.portletconfiguration

JOIN	searchrequest s
ON	substring(g.userprefvalue from '[0-9]+')::integer = s.id

WHERE 	p.username = 'admin'
AND 	userprefkey = 'filterId';

This example will return gadgets for the admin user.