look at performance of readonly queries

Evan Hughes
1 day ago
Does anyone have any advice if a folder with a few policy groups (15) should take around 2.5s to load. Infra is K8S pods with 3000 cpu and 3g of memory for the jvm, DB is Aurora Postgres. Grouper is 4.10.3, with DataDog APM looks like the db is doing 213 sql requests of 'SHOW TRANSACTION LEVEL' for half the time which I assume is c3p0 / hibernate?

Carey Black
23 hours ago
a folder with a few policy groups (N) should take around 2.5s to load.
Do other folders take a very different amount of time?
FWIW: My guess is that the work to resolve the privileges for the UI user on the folder's objects ( itself and nested folders and groups ) is likely what you are observing. And you may find that if you navigate away from the folder and back to it that the 'time to display' is better on re-display than on 'first display'.
I know that the project has moved a lot of the privilege (re-)calculations into jobs. And I think a lot of that is also cached in the UI RAM too. Which is why "redisplay" may be faster than the "first display". (Got to build the cache on first read. )

Carey Black
23 hours ago
If you have multiple UI nodes you likely are already using a sticky session too. ( If not that could also play in to more slowness if you are bouncing across multiple UI nodes between calls. )

Chris Hyzer
21 hours ago
are all your indexes there (do a gsh -registry -deep -check)? Sometimes vacuum analyzing all the indexes helps

Evan Hughes
17 hours ago
Grouper ddl object type 'Grouper' has db/ddl version: 0 (introduced in null) and container/grouperJava version: 44 (introduced in 2.6.18)
Grouper ddl object type 'Subject' has db/ddl version: 0 (introduced in null) and container/grouperJava version: 1 (introduced in 1.4.0)
Grouper database schema DDL requires updates
(should run script manually and carefully, in sections, verify data before drop statements, backup/export important data before starting, follow change log on confluence, dont run exact same script in multiple envs - generate a new one for each env),
script file is:
/opt/grouper/grouperWebapp/WEB-INF/ddlScripts/grouperDdl_20240221_23_09_58_083.sql
Note: this script was not executed due to option passed in
To run script via gsh, carefully review it, then run this:
gsh -registry -runsqlfile /opt/grouper/grouperWebapp/WEB-INF/ddlScripts/grouperDdl_20240221_23_09_58_083.sql

SUCCESS: Database DDL is correct!

Note: Database version for Grouper: 44 (2.6.18)
Note: Java version for Grouper: 44 (2.6.18)
Success: Database version is the same as the Java codebase Grouper version
Success: Table 'grouper_attr_assign_action': Table is up to date. 7 columns, 1 indexes, 1 foreign keys.
Success: Table 'grouper_attr_assign_action_set': Table is up to date. 10 columns, 3 indexes, 3 foreign keys.
Success: Table 'grouper_attribute_assign': Table is up to date. 20 columns, 8 indexes, 8 foreign keys.
Success: Table 'grouper_attribute_assign_value': Table is up to date. 10 columns, 4 indexes, 1 foreign keys.
Success: Table 'grouper_attribute_def': Table is up to date. 28 columns, 3 indexes, 1 foreign keys.
Success: Table 'grouper_attribute_def_name': Table is up to date. 13 columns, 2 indexes, 2 foreign keys.
Success: Table 'grouper_attribute_def_name_set': Table is up to date. 10 columns, 3 indexes, 3 foreign keys.
Success: Table 'grouper_attribute_def_scope': Table is up to date. 9 columns, 1 indexes, 1 foreign keys.
Success: Table 'grouper_audit_entry': Table is up to date. 30 columns, 13 indexes, 1 foreign keys.
Success: Table 'grouper_audit_type': Table is up to date. 20 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_cache_instance': Table is up to date. 2 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_cache_overall': Table is up to date. 2 columns, 0 indexes, 0 foreign keys.
Success: Table 'grouper_change_log_consumer': Table is up to date. 6 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_change_log_entry': Table is up to date. 16 columns, 15 indexes, 1 foreign keys.
Success: Table 'grouper_change_log_entry_temp': Table is up to date. 16 columns, 13 indexes, 0 foreign keys.
Success: Table 'grouper_change_log_type': Table is up to date. 19 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_composites': Table is up to date. 9 columns, 8 indexes, 4 foreign keys.
Success: Table 'grouper_config': Table is up to date. 13 columns, 4 indexes, 0 foreign keys.
Success: Table 'grouper_ddl': Table is up to date. 5 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_ddl_worker': Table is up to date. 5 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_ext_subj': Table is up to date. 16 columns, 2 indexes, 0 foreign keys.
Success: Table 'grouper_ext_subj_attr': Table is up to date. 10 columns, 3 indexes, 1 foreign keys.
Success: Table 'grouper_failsafe': Table is up to date. 11 columns, 2 indexes, 0 foreign keys.
Success: Table 'grouper_fields': Table is up to date. 7 columns, 3 indexes, 0 foreign keys.
Success: Table 'grouper_file': Table is up to date. 9 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_group_set': Table is up to date. 22 columns, 15 indexes, 10 foreign keys.
Success: Table 'grouper_groups': Table is up to date. 21 columns, 17 indexes, 3 foreign keys.
Success: Table 'grouper_last_login': Table is up to date. 4 columns, 4 indexes, 1 foreign keys.
Success: Table 'grouper_loader_log': Table is up to date. 29 columns, 6 indexes, 0 foreign keys.
Success: Table 'grouper_members': Table is up to date. 26 columns, 20 indexes, 0 foreign keys.
Success: Table 'grouper_memberships': Table is up to date. 16 columns, 21 indexes, 7 foreign keys.
Success: Table 'grouper_message': Table is up to date. 11 columns, 6 indexes, 1 foreign keys.
Success: Table 'grouper_mship_req_change': Table is up to date. 8 columns, 4 indexes, 0 foreign keys.
Success: Table 'grouper_password': Table is up to date. 16 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_password_recently_used': Table is up to date. 8 columns, 0 indexes, 1 foreign keys.
Success: Table 'grouper_pit_attr_assn_actn': Table is up to date. 9 columns, 4 indexes, 1 foreign keys.
Success: Table 'grouper_pit_attr_assn_actn_set': Table is up to date. 11 columns, 6 indexes, 3 foreign keys.
Success: Table 'grouper_pit_attr_assn_value': Table is up to date. 12 columns, 8 indexes, 1 foreign keys.
Success: Table 'grouper_pit_attr_def_name': Table is up to date. 10 columns, 6 indexes, 2 foreign keys.
Success: Table 'grouper_pit_attr_def_name_set': Table is up to date. 11 columns, 6 indexes, 3 foreign keys.
Success: Table 'grouper_pit_attribute_assign': Table is up to date. 17 columns, 12 indexes, 8 foreign keys.
Success: Table 'grouper_pit_attribute_def': Table is up to date. 10 columns, 7 indexes, 1 foreign keys.
Success: Table 'grouper_pit_config': Table is up to date. 20 columns, 4 indexes, 0 foreign keys.
Success: Table 'grouper_pit_fields': Table is up to date. 9 columns, 5 indexes, 0 foreign keys.
Success: Table 'grouper_pit_group_set': Table is up to date. 19 columns, 18 indexes, 9 foreign keys.
Success: Table 'grouper_pit_groups': Table is up to date. 9 columns, 6 indexes, 1 foreign keys.
Success: Table 'grouper_pit_members': Table is up to date. 11 columns, 6 indexes, 0 foreign keys.
Success: Table 'grouper_pit_memberships': Table is up to date. 13 columns, 11 indexes, 5 foreign keys.
Success: Table 'grouper_pit_role_set': Table is up to date. 11 columns, 6 indexes, 3 foreign keys.
Success: Table 'grouper_pit_stems': Table is up to date. 9 columns, 6 indexes, 1 foreign keys.
Success: Table 'grouper_prov_duo_user': Table is up to date. 16 columns, 3 indexes, 0 foreign keys.
Success: Table 'grouper_prov_zoom_user': Table is up to date. 15 columns, 4 indexes, 0 foreign keys.
Success: Table 'grouper_qz_blob_triggers': Table is up to date. 4 columns, 0 indexes, 1 foreign keys.
Success: Table 'grouper_qz_calendars': Table is up to date. 3 columns, 0 indexes, 0 foreign keys.
Success: Table 'grouper_qz_cron_triggers': Table is up to date. 5 columns, 0 indexes, 1 foreign keys.
Success: Table 'grouper_qz_fired_triggers': Table is up to date. 13 columns, 6 indexes, 0 foreign keys.
Success: Table 'grouper_qz_job_details': Table is up to date. 10 columns, 2 indexes, 0 foreign keys.
Success: Table 'grouper_qz_locks': Table is up to date. 2 columns, 0 indexes, 0 foreign keys.
Success: Table 'grouper_qz_paused_trigger_grps': Table is up to date. 2 columns, 0 indexes, 0 foreign keys.
Success: Table 'grouper_qz_scheduler_state': Table is up to date. 4 columns, 0 indexes, 0 foreign keys.
Success: Table 'grouper_qz_simple_triggers': Table is up to date. 6 columns, 0 indexes, 1 foreign keys.
Success: Table 'grouper_qz_simprop_triggers': Table is up to date. 14 columns, 0 indexes, 1 foreign keys.
Success: Table 'grouper_qz_triggers': Table is up to date. 16 columns, 12 indexes, 1 foreign keys.
Success: Table 'grouper_recent_mships_conf': Table is up to date. 6 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_role_set': Table is up to date. 10 columns, 3 indexes, 3 foreign keys.
Success: Table 'grouper_stem_set': Table is up to date. 10 columns, 3 indexes, 3 foreign keys.
Success: Table 'grouper_stem_view_privilege': Table is up to date. 3 columns, 2 indexes, 0 foreign keys.
Success: Table 'grouper_stems': Table is up to date. 16 columns, 13 indexes, 3 foreign keys.
Success: Table 'grouper_sync': Table is up to date. 14 columns, 2 indexes, 0 foreign keys.
Success: Table 'grouper_sync_group': Table is up to date. 27 columns, 8 indexes, 1 foreign keys.
Success: Table 'grouper_sync_job': Table is up to date. 14 columns, 1 indexes, 1 foreign keys.
Success: Table 'grouper_sync_log': Table is up to date. 14 columns, 2 indexes, 1 foreign keys.
Success: Table 'grouper_sync_member': Table is up to date. 28 columns, 9 indexes, 1 foreign keys.
Success: Table 'grouper_sync_membership': Table is up to date. 15 columns, 6 indexes, 3 foreign keys.
Success: Table 'grouper_table_index': Table is up to date. 6 columns, 1 indexes, 0 foreign keys.
Success: Table 'grouper_time': Table is up to date. 5 columns, 0 indexes, 0 foreign keys.
Success: View 'grouper_attr_asn_asn_attrdef_v': View is up to date. 23 columns.
Success: View 'grouper_attr_asn_asn_efmship_v': View is up to date. 27 columns.
Success: View 'grouper_attr_asn_asn_group_v': View is up to date. 24 columns.
Success: View 'grouper_attr_asn_asn_member_v': View is up to date. 24 columns.
Success: View 'grouper_attr_asn_asn_mship_v': View is up to date. 28 columns.
Success: View 'grouper_attr_asn_asn_stem_v': View is up to date. 24 columns.
Success: View 'grouper_attr_asn_attrdef_v': View is up to date. 14 columns.
Success: View 'grouper_attr_asn_efmship_v': View is up to date. 20 columns.
Success: View 'grouper_attr_asn_group_v': View is up to date. 16 columns.
Success: View 'grouper_attr_asn_member_v': View is up to date. 16 columns.
Success: View 'grouper_attr_asn_mship_v': View is up to date. 20 columns.
Success: View 'grouper_attr_asn_stem_v': View is up to date. 15 columns.
Success: View 'grouper_attr_assn_action_set_v': View is up to date. 10 columns.
Success: View 'grouper_attr_def_name_set_v': View is up to date. 10 columns.
Success: View 'grouper_attr_def_priv_v': View is up to date. 12 columns.
Success: View 'grouper_audit_entry_v': View is up to date. 45 columns.
Success: View 'grouper_aval_asn_asn_attrdef_v': View is up to date. 28 columns.
...... all success further down
(edited)

Evan Hughes
17 hours ago
@black.123
only single node for the UI currently as theirs 0 users other than the admins of the service

Chris Hyzer
14 hours ago
did you vacuum analyze everything?
New

Evan Hughes
10 hours ago
yup, no significant change, the instance is basically fresh

Chris Hyzer
< 1 minute ago
if you can get by for now we can look at this in a future release. i think the data layer does not know there are readonly queries about to happen when the connection is retrieved from the pool, so it is doing extra overhead in case there is a transaction about to happen. if we can reduce this for the SELECT only SQL calls maybe it would significantly improve the performance? we need to look at it