Loading some team-related pages takes ages

[boobaa]

Description

More than one of our customers reported that loading some team-related pages takes ages (5+ seconds, sometimes over a minute which causes PHP timeouts). Those customers are common in having a relatively high number of teams (10k+), with relatively low number of members in each (<20). Some already-identified examples:

• /user/{uid}/team-invitations
• /teams/{team}/members
• /teams
• /admin/config/apigee-edge/team-settings/fields/team.team.field_list_text (which is a field with the type of list_text, having 10-100 allowed_values).

By the help of apigee_edge_debug.module, we could identify that Apigee Edge is requested 11 times for 10 allowed_values on this last one – this triggered us submitting #1147 and helped to alleviate the problem somewhat: the other pages are still loading slow.

This debug module also told us that Apigee Edge itself is NOT the bottleneck: some 11 MB of JSON data was returned in about 400 msecs (for an OPDK customer; for cloud customers it was a bit higher, but still not the bottleneck).

We disabled apigee_edge_debug.module (as it also takes a considerable amount of time for only logging this amount of data), and started debugging ourselves. Some pain points we have found so far, after applying #1148 for a quick remedy:

• Loading some pages (/user/{uid}/team-invitations and /teams/{team}/members in particular) takes ages only for the first time; subsequent page loads are fast (as they're coming from Drupal's cache).
• Both the /teams page and /admin/config/apigee-edge/team-settings/fields/team.team.field_list_text rely on a $teamStorage->loadMultiple(NULL) call at some point. Because of that, a GET /v1/organizations/{organization}/companies?expand=true request is sent to Apigee Edge, and the returned 10k+ teams are then instantiated as Drupal entities, which are then cached. Now this caching is the culprit: \Drupal\apigee_edge\Entity\Storage\EdgeEntityStorageBase::setPersistentCache calls \Drupal\pgsql\Driver\Database\pgsql\Upsert::execute for EACH team, for EACH page load.

At this point we decided to kill the team storage cache (by using a \Drupal\Core\Cache\NullBackend as its cache_backend). Now everything loads acceptably quickly (<2s even with cold caches), except the /teams page (5+ secs, even with warm caches).

Apigee Info

Affected customers are using either Apigee Edge (the cloud version) or OPDK. Cloud is considerably slower, but that's not the problem here, as OPDK-connected Drupal sites are also unacceptably slow.

Steps to Reproduce

Steps to reproduce the behavior:

1. Have like 10k+ (yes, more than ten thousand) teams in Apigee Edge.
2. Apply the patch from #1148 as a quick remedy.
3. Navigate to the above paths on a connected Drupal site.

Actual Behavior

Teams-related pages are loading slow: 5+ seconds, or even minutes.

Expected Behavior

Teams-related pages should load fine (<2s, even with cold caches), even with vast amounts of teams. Probably some paging on the /teams page could also help.

Screenshots

N/A

Notes

We also had the idea of mirroring teams' data in Drupal's DB, which would make the site FAST (and even allow paging), but keeping the Drupal's DB in sync with Apigee Edge with regards to the teams could be hard.

Version Info

Versions don't really matter as the affected customers are using various Drupal 10 versions and various 3.x and 4.x versions of the apigee_edge.module.

[mxr576]

Because of that, a GET /v1/organizations/{organization}/companies?expand=true request is sent to Apigee Edge, and the returned 10k+ teams are then instantiated as Drupal entities, which are then cached. Now this caching is the culprit: \Drupal\apigee_edge\Entity\Storage\EdgeEntityStorageBase::setPersistentCache calls \Drupal\pgsql\Driver\Database\pgsql\Upsert::execute for EACH team, for EACH page load.

Is going to be addressed in #1155

[divya-intelli]

Hi @boobaa , Created a PR #1210 to optimize performance, please test the PR and also check the comment. Let us know how it performs in your environment.