[Deploying Sakai] How to tune this Query in Oracle

Tue Apr 16 08:12:33 PDT 2013

Yasir,
I can vouch that Virginia Tech has been running this patched version of the
query for years with good results. Something between 20% and 100%
improvement in throughput was gained.

However, ultimately, the design of the authz service is the problem, and I
don't think you can wring much more out of the query itself.

A better design would be to have particular role-site-type permission pairs
defined in one place, which could be near-permanently cached at startup,
and then only store specific allow or deny changes on a
per-site/group/resource basis, and then sum the several sources. A map with
all the user permissions for a realm would only need to take a few dozen
bytes of memory, could be looked up in one query, and cached for the
remainder of that user's visit to that site.

However, as it is, the authz service must store a copy of all the
permissions for all the roles for every site, group, and resource. There
are some summative features via the site.helper and user.template realms,
but since there is no concept of a "deny" permission, such helper realms
are of extremely limited value. Every permission must be checked
individually leading to sometimes dozens of these queries per page load.

In the end, even with these improvements, we still had problems despite the
new query, just less often. Ultimately, our research determined that the
problem came down to the limited number of shared memory latches in Oracle.
That number is constrained by the CPU cores available to your database
server. We bought hardware with as many cores as possible, and we've not
had a problem with this query since. So my recommendation for anyone
running Oracle with a relatively large installation is to buy hardware with
as many cores as possible. Slower CPUs is fine if you can get more cores.

Our current hardware has four CPU sockets each with a 10-core Xeon with
hyperthreading. So Oracle sees that as having 80 CPUs. A big leap from our
previous hardware with four quad-core CPUs. Corresponding to the 5x
increase in CPU core count, we got nearly a 5x increase in max throughput
for the authz query you mention. In narrow load testing of just this single
query using real data in a clone of our production database, we went from
maxing out at about 5,000 queries per second on the old hardware to over
22,000 queries per second with the new one. And whereas the 16-CPU hardware
would actually seize up and slow way down if we pushed it too hard, the
80-core boxes do hit a maximum level of throughput but keep running at a
steady pace.

For an idea of our scale, we have 211,624 records in sakai_realm;
49,292,563 records in sakai_realm_rl_fn; and 2,725,211 records in
sakai_realm_rl_gr. We get about 10,000,000 HTTP requests on a typical class
day (that's with the presence tool turned off; turning it on would add
another 30-50%, in our experience). We have over 39,000 relatively active
users (ie, they've logged in during 2013). We do an average of 66,000
sessions per day with a peak of 124,364 so far this year.

I don't know where your school falls on those numbers, but there are many
schools using Oracle with much, much larger loads than we run. They may
have some other thoughts.

-dave

David Adams
Director, Systems Integration and Support
Virginia Tech Learning Technologies

On Tue, Apr 16, 2013 at 8:45 AM, Will Humphries <Will.Humphries at tufts.edu>wrote:

> I think this query is rewritten in
> https://jira.sakaiproject.org/browse/KNL-934 . You could also take a
> look at the authz caching work in
> https://jira.sakaiproject.org/browse/KNL-600 . Note that cache is broken
> out of the box in 2.8.2 and should not be turned on unless all the work
> from KNL-600 is merged.
>
> -Will
>
> On 4/16/13 5:33 AM, Yasir Arfat wrote:
> > Did any one tune this Qurey of sakai 2.8.2 on oracle 11g
> >
> > select count(1) from SAKAI_REALM_RL_FN where  REALM_KEY in (select
> > REALM_KEY from SAKAI_REALM where  SAKAI_REALM.REALM_ID IN
> > (:1,:2,:3,:4,:5)) and FUNCTION_KEY in (select FUNCTION_KEY from
> > SAKAI_REALM_FUNCTION where FUNCTION_NAME = :6)  and (ROLE_KEY in
> > (select ROLE_KEY from SAKAI_REALM_RL_GR where ACTIVE = '1' and USER_ID
> > = :7  and REALM_KEY in (select REALM_KEY from SAKAI_REALM where
> >  SAKAI_REALM.REALM_ID IN (:8,:9,:10,:11,:12)))  or ROLE_KEY in (select
> > ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '.anon') or ROLE_KEY
> > in (select ROLE_KEY from SAKAI_REALM_ROLE where ROLE_NAME = '.auth') )
> > --
> > Yasir Arfat | Software Engineer (LMS Administrator)
> > http://slate.nu.edu.pk
> > National University of Computer and Emerging Sciences
> > A. K. Brohi Road (Near Police Line) H11/4 Islamabad
> > Tel +92-(51)-111-128-128 Ext 344  Cell +92-(343)-8502276
> >
> >
> >
> > _______________________________________________
> > production mailing list
> > production at collab.sakaiproject.org
> > http://collab.sakaiproject.org/mailman/listinfo/production
> >
> > TO UNSUBSCRIBE: send email to
> production-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
> _______________________________________________
> production mailing list
> production at collab.sakaiproject.org
> http://collab.sakaiproject.org/mailman/listinfo/production
>
> TO UNSUBSCRIBE: send email to
> production-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/production/attachments/20130416/e71e9f90/attachment.html 