[Archivesspace_Users_Group] Spreadsheet imports locking up database

[James Bullen]

Hi Peter,

I’m not familiar with the spreadsheet importer, but it sounds like you’ve done the analysis to be in a position to fix it. :)

Here’s a commit for the “run tonight” feature implemented in a plugin:
https://gitlab.gaiaresources.com.au/qsa/as_runcorn/-/commit/8a2bd97 <https://gitlab.gaiaresources.com.au/qsa/as_runcorn/-/commit/8a2bd97>

It was refined in a few subsequent commits (4369dd0, 800ac1c, 5f1207e).


Cheers,
James


> On Jun 6, 2021, at 6:02 PM, Peter Heiner <ph448 at cam.ac.uk> wrote:
> 
> James Bullen wrote on 2021-06-06 11:35:03:
> 
>> Bulk update operations usually run in database transactions. This is great because it guarantees that the database will be in a consistent state when the job completes - if it succeeds then all of the updates are applied, if it fails then none of the updates are applied.
>> 
>> The price for this guarantee is that any affected records are locked until the job completes.
> 
> Thanks for your response. I generally agree with your points but have mine to make.
> One is that the consistency unit here need not be the entire job, it is sufficient to isolate a single line, and I would normally expect the database to cope fine with such a workload.
> Secondly, if I read my database output correctly, in this case the problem is not a wholly isolated bulk update but unnecessary table scans occurring for each line that make the database hold a lock for several seconds instead of milliseconds. I've yet to understand the purpose of the subnote_metadata table and why the delete is taking place, but we did notice that the query tried IDs that were not in the database.
> In any case, it should be possible to unroll the subquery or provide optimiser hints for the bottleneck to go away.
> 
>> We recently implemented the ability to submit import jobs to be run after hours for a client to work around this.
> 
> We were thinking of a similar workaround, do you have anything you could share with us?
> 
> Thanks,
> p
> _______________________________________________
> Archivesspace_Users_Group mailing list
> Archivesspace_Users_Group at lyralists.lyrasis.org
> http://lyralists.lyrasis.org/mailman/listinfo/archivesspace_users_group

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20210606/1ab3df29/attachment.html>