[#CONF-12319] HibernateUserManager#addMembership is extremely inefficient

Confluence

HibernateUserManager#addMembership is extremely inefficient

Created: 02/Jul/08 08:35 PM Updated: 10/Jul/08 10:49 PM

Component/s:

Permissions, Users & Groups

Affects Version/s:

2.8

Fix Version/s:

None

Time Tracking:

Not Specified

Environment:

MySQL5

Issue Links:

Reference

This issue relates to:

CONF-10030 db2: queries that use 'lower' do not use index because of case sensitivity

This issue is related to:

CONF-8675 Support for thousands of groups needs improvement

Participants:	Don Willis [Atlassian] and Igor Minar
Since last comment:	13 weeks, 3 days ago
Internal Complexity:	4
Internal Value:	5
Labels:

Description

« Hide

While debugging an outage at wikis.sun.com I noticed that the code in HibernateUserManager#addMembership generates some extremely inefficient queries that were giving our db and network hard time:

membership = dGroup.getLocalMembers();

if (membership == null)
{
       membership = new HashSet();
       dGroup.setLocalMembers(membership);
}

membership.add(user);

The last line of the code translates to:

DEBUG 2008-07-02 11:35:43,801 [service-j2ee-3] BatcherImpl:log - select localmembe0_.groupid as groupid__, localmembe0_.userid as userid__, defaulthib1_.id as id0_, defaulthib1_.name as name0_, defaulthib1_.password as password0_, defaulthib1_.email as email0_, defaulthib1_.created as created0_, defaulthib1_.fullname as fullname0_ from local_members localmembe0_ inner join users defaulthib1_ on localmembe0_.userid=defaulthib1_.id where localmembe0_.groupid=?

DEBUG 2008-07-02 11:35:43,806 [service-j2ee-3] BatcherImpl:log - insert into local_members (groupid, userid) values (?, ?)

Which means retrieve*all* members of a given group and then insert the user to the db.

If you run this query on our db with 25k users in a group, you run into some really big problems. By that I mean that the query can easily run several minutes and affect the overall db performance.

The code should be rewritten so that the uniqueness constraint is checked by a SELECT and if no dupe is found the INSERT can follow, otherwise this code will never scale.

All

Comments

Work Log

Change History

Sort Order:

[ Permlink | « Hide ]

Don Willis [Atlassian] added a comment - 02/Jul/08 10:03 PM

Hi Igor,

Thanks for raising this issue.
While obviously I agree that's a very inefficient way to perform the operation, I'm also surprised that such a query would take several minutes, even with 25000 users in the group.
Are you missing an index on either local_members.groupid or users.id?
What does MySQL say if you ask it to EXPLAIN the actual query that runs?

Cheers,
Don

[ Permlink | « Hide ]

Igor Minar added a comment - 02/Jul/08 10:16 PM

Hi Don,

Here is the query plan:

mysql> explain select localmembe0_.groupid as groupid__, localmembe0_.userid as userid__, defaulthib1_.id as id0_, defaulthib1_.name as name0_, defaulthib1_.password as password0_, defaulthib1_.email as email0_, defaulthib1_.created as created0_, defaulthib1_.fullname as fullname0_ from local_members localmembe0_ inner join users defaulthib1_ on localmembe0_.userid=defaulthib1_.id where localmembe0_.groupid=13205505;
+----+-------------+--------------+--------+----------------------------+---------+---------+---------------------------+-------+-------------+
| id | select_type | table        | type   | possible_keys              | key     | key_len | ref                       | rows  | Extra       |
+----+-------------+--------------+--------+----------------------------+---------+---------+---------------------------+-------+-------------+
|  1 | SIMPLE      | localmembe0_ | ref    | PRIMARY,FK6B8FB445CE2B3226 | PRIMARY | 8       | const                     | 22686 | Using index | 
|  1 | SIMPLE      | defaulthib1_ | eq_ref | PRIMARY                    | PRIMARY | 8       | wikis.localmembe0_.userid |     1 |             | 
+----+-------------+--------------+--------+----------------------------+---------+---------+---------------------------+-------+-------------+

I think that we are facing several issues here, combination of which causes our db to perform terribly.

Issues we know about are:

cheers,
Igor

[ Permlink | « Hide ]

Don Willis [Atlassian] added a comment - 10/Jul/08 02:42 AM

CONF-10030 is definitely a problem, but that query plan shows that this query should run very fast. I suspect the mysql people will be able to help you with your mysql configuration to avoid your thread thrashing problems.
Now that you've addressed CONF-10030 in your instance are you still having problems with the groups query?

[ Permlink | « Hide ]

Igor Minar added a comment - 10/Jul/08 10:49 PM

wikis.sun.com has been stable since the patch for CONF-10030 was applied, but I still consider addMembership to be an issue waiting to bite us at any time. Especially as the number of registered users grow rapidly.

Running the query above currently takes quite some time:

25437 rows in set (13.57 sec)

Other than this query, the db is screaming fast at the moment.