Author Topic: Getting The ACL Code To Scale (Read 1153 times)

torenware · February 07, 2008, 01:17:24 pm

I'm working on a site on the bleeding edge of what the 1.9 ACL code will handle. The site has to the order of a 100K contacts, and close to 500 groups, including around 200 groups under ACLs.

At this point, I've already modified CiviCRM's query writing code so that the left joins required for a complex query execute faster (by 100x or more in some cases). But with a recent upgrade of MySQL 5.x, I'm finding that we are getting errors on "too many left joins" -- more than 60 in some extreme cases.

I'm curious if anyone else is anywhere close to where we are right now on scaling this stuff. Since large numbers of groups are usually arranged in a hierarchy, I figure it's possible to design a hook that winnows the number of left joins required, since if you have the right to see all sub groups of a "group-of-groups", you can just have a single left join for the super-group.

I'd also like to know if 2.0 has any more smarts for dealing with hierarchies of groups.

Donald Lobo · February 07, 2008, 09:13:19 pm

1. default civicrm v2.0 works with mysql5 (which is our default db). 2.0 addresses the custom value left join issue, since a group of custom values is stored in one table, and hence would need one left join (rather than n left join's as in the 1.x series)

2. in general, we need to restructure search to be more efficient for medium-large sized db's. I suspect we'll hit the problem along 2 axis. Using some sort of caching for smart groups via temp tables (reevaluating a smart group query and joining it to the original query will always be slow for large numbers of smart groups, like the DA case). The second optimization would be to split one complex query into n smaller queries (like we do in civimail), where n is a small constant (i.e. not related to the size of the db)

as always if folks are interested in seeing this happen sooner rather than later, please step up and contribute development resources to see this happen. this has been on our plate for some time (but keeps sliding down

lobo

torenware · February 07, 2008, 10:20:08 pm

Quote from: Donald Lobo on February 07, 2008, 09:13:19 pm

1. default civicrm v2.0 works with mysql5 (which is our default db). 2.0 addresses the custom value left join issue, since a group of custom values is stored in one table, and hence would need one left join (rather than n left join's as in the 1.x series)

2. in general, we need to restructure search to be more efficient for medium-large sized db's. I suspect we'll hit the problem along 2 axis. Using some sort of caching for smart groups via temp tables (reevaluating a smart group query and joining it to the original query will always be slow for large numbers of smart groups, like the DA case). The second optimization would be to split one complex query into n smaller queries (like we do in civimail), where n is a small constant (i.e. not related to the size of the db)

as always if folks are interested in seeing this happen sooner rather than later, please step up and contribute development resources to see this happen. this has been on our plate for some time (but keeps sliding down

lobo

ACLs turn out to have similar issues to custom values. Reducing the number of left joins, I agree, is important. But doing the left joins efficiently is important as well.

Efficient left joins need to be "sparse" -- i.e., they should be mostly null values. Good left joins restrict the number of non-rows in a join as much as is possible, so logic that's in the where clauses should migrate into ON clauses.

For ACLs in particular, bad code is stored in the database; right now we're using regular expressions to fix the bad SQL as we extract it. I have not been able to determine where the SQL gets stuffed into civicrm_group in the first place, but the real fix is there.

CiviCRM Community Forums (archive)

News:

Author Topic: Getting The ACL Code To Scale (Read 1153 times)

torenware

Getting The ACL Code To Scale

Donald Lobo

Re: Getting The ACL Code To Scale

torenware

Re: Getting The ACL Code To Scale