Versions Compared

Old Version 55

changes.mady.by.user Michael Gettes

Saved on

compared with

New Version 56

changes.mady.by.user Chad Redman (unc.edu)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

No Format
bin/gsh.sh -registry

Database Tuning

Analyzing Tables to Improve Query Performance

Whenever a lot of changes are made to the data in the Groups Registry database (including upgrades of Grouper), you should analyze your database tables to improve query performance.

...

No Format
ANALYZE TABLE grouper_groups;
ANALYZE TABLE grouper_stems;
ANALYZE TABLE grouper_memberships;
ANALYZE TABLE grouper_group_set;
....

Anchor
sources
sources

Improving queries using histogram statistics

Even with a full set of statistics on tables, columns, and indexes, this is sometimes not enough information for some queries. For example, in a database with 100,000 groups and 100,000 users, a query plan based on memberships may think that there will likely be at most one group per member. So the query plan may be built on the assumption that it can safely do a Nested Loop iteration through the few rows returned. But it is a plausible example that the GrouperAll subject is granted read access to a large number of these groups. This could have an effect on queries for non-wheel users when checking whether the logged in user can read a group. Instead of looping through a few rows, it could be looping through thousands.

With database histograms, values are put into a fixed number of bins. If the column data is heavily skewed toward one value, that value will occupy one or more bins by itself, and the query analysis can use that information to get a rough estimate on the cardinality of a filter on that column.

With Oracle, a first step toward improving these queries is to add a histogram for a single column, e.g.:

Panel

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (
ownname => 'GROUPER'
, tabname => 'GROUPER_MEMBERSHIPS'
, method_opt => 'FOR COLUMNS MEMBER_ID'
);
END;


Histograms on more than one column require an extended version of this.

Panel

select dbms_stats.create_extended_stats(null, 'GROUPER_GROUP_SET', '(OWNER_GROUP_ID, FIELD_ID)') from dual;
-- (will return a generated rowid such as SYS_STU$V77C8_NRNA1MJMG#1SPOH$)

exec dbms_stats.gather_table_stats(null, 'GROUPER_GROUP_SET');

select * from user_tab_col_statistics where table_name = 'GROUPER_GROUP_SET';


MySQL starting from version 8 has histograms, probably similar to Oracle. https://mysqlserverteam.com/histogram-statistics-in-mysql/ .

Configuration of Source Adapters

...

Notifications / change log

To enable the change log, set this:

Property Name

Default Value

Description

changeLog.enabled

true

if we should insert records into grouper_change_log_temp when events happen

...

No Format
##################################
## enabled / disabled cron
##################################

#quartz cron-like schedule for enabled/disabled daemon.  Note, this has nothing to do with the changelog
#leave blank to disable this, the default is 12:01am, 11:01am, 3:01pm every day: 0 1 0,11,15 * * ?
changeLog.enabledDisabled.quartz.cron = 0 1 0,11,15 * * ?

Manage change log consumers and also specify whether some events are written to the change log.

...