Bug #13250

Editing/deleting a repository with a big amount of related descriptions can timeout

Added by José Raddaoui Marín 29 days ago. Updated 22 days ago.

Status:QA/ReviewStart date:01/28/2020
Priority:MediumDue date:
Assignee:-% Done:


Category:Performance / scalability
Target version:-
Google Code Legacy ID: Tested version:2.5, 2.6
Sponsored:No Requires documentation:


From Steve Breker via email:

It appears that this line is the source of the timeouts when updating the repository:


Which produces this query:

SELECT object.CLASS_NAME, object.CREATED_AT, object.UPDATED_AT, object.ID, object.SERIAL_NUMBER, information_object.ID, information_object.IDENTIFIER, information_object.OAI_LOCAL_IDENTIFIER, information_object.LEVEL_OF_DESCRIPTION_ID, information_object.COLLECTION_TYPE_ID, information_object.REPOSITORY_ID, information_object.PARENT_ID, information_object.DESCRIPTION_STATUS_ID, information_object.DESCRIPTION_DETAIL_ID, information_object.DESCRIPTION_IDENTIFIER, information_object.SOURCE_STANDARD, information_object.DISPLAY_STANDARD_ID, information_object.LFT, information_object.RGT, information_object.SOURCE_CULTURE FROM `object`, `information_object` WHERE information_object.REPOSITORY_ID='112238' AND information_object.ID=object.ID

...which returns 615465 rows in approx 10-12 seconds.

It appears that php-fpm then spends the rest of the time processing and possibly creating 615K info objs in memory - the process is killed due to memory usage:

2020/01/27 15:11:23 [error] 19455#19455: *4046 FastCGI sent in stderr: "PHP message: PHP Fatal error:  Allowed memory size of 2147483648 bytes exhausted (tried to allocate 20480 bytes) in /usr/share/nginx/atom/lib/QubitQuery.class.php on line 112" while reading response header from upstream, client: _, server: _, request: "POST _ HTTP/1.1", upstream: "fastcgi://unix:/var/run/php-fpm.atom.sock:", host: "_", referrer: "_" 

This will require a code change to make this query more efficient, and reduce the amount of memory consumed by this QubitRepository code.

Any fix must be tested against a repository with a large number of IO's linked to it.

Please notice that the same can happen on the deletion process. In addition to that, it looks like the deletion is triggering a job per description with descendants when it should be launching only one like it's done on save.

Mike Cantelon recently fixed a similar problem on https://github.com/artefactual/atom/pull/1034, using raw SQL to get the ids and clearing the classes cache while looping through the descriptions.

Related issues

Related to Access to Memory (AtoM) - Task #13224: Improve hierarchy management queries Code Review 12/09/2019


#1 Updated by José Raddaoui Marín 29 days ago

  • Related to Task #13224: Improve hierarchy management queries added

#2 Updated by Mike Cantelon 29 days ago

  • Status changed from New to In progress
  • Assignee set to Mike Cantelon

#3 Updated by José Raddaoui Marín 27 days ago

  • Subject changed from Editing a repository with a big amount of related descriptions can timeout to Editing/deleting a repository with a big amount of related descriptions can timeout
  • Description updated (diff)

#4 Updated by Mike Cantelon 26 days ago

PR merged into qa/2.5.x. Fixes issue with editing/updating a repo, but not deleting a repo. Will revisit.

#5 Updated by Mike Cantelon 23 days ago

  • Status changed from In progress to Code Review
  • Assignee deleted (Mike Cantelon)

#7 Updated by Mike Cantelon 22 days ago

  • Status changed from Code Review to QA/Review

Merged into qa/2.6.x.

Also available in: Atom PDF