[Archivesspace_Users_Group] Spreadsheet imports locking up database

[Kennedy, Nancy]

Hi Peter -
We also see issues with concurrent database access.  If one user initiates a large delete or import, other users can experience long delays and timeouts.  We've also noticed that Edit save times are especially long when there is a language subrecord. (An archival_object with a lang_material can take 30 seconds to save, whereas records without a lang_material save much more quickly).

We are in v 2.71 and we also use MariaDB. We see the same issue in our test 2.8.1 instance. I'd be very interested to hear if MySQL offers a better experience for Cambridge,

Thanks,
Nancy

kennedyn at si.edu<mailto:kennedyn at si.edu>
Nancy Kennedy
Smithsonian Institution



From: archivesspace_users_group-bounces at lyralists.lyrasis.org <archivesspace_users_group-bounces at lyralists.lyrasis.org> On Behalf Of Peter Heiner
Sent: Friday, June 4, 2021 11:20 AM
To: archivesspace_users_group at lyralists.lyrasis.org
Subject: [Archivesspace_Users_Group] Spreadsheet imports locking up database

External Email - Exercise Caution
Dear list users,

The Cambridge University Library AS instance has recently been hit with severe performance issues while users were running spreadsheet imports to add archival objects, details are in https://github.com/archivesspace/archivesspace/issues/2275<https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Farchivesspace%2Farchivesspace%2Fissues%2F2275&data=04%7C01%7CKennedyN%40si.edu%7C0d243c2fdc70417e30f508d9276c3359%7C989b5e2a14e44efe93b78cdd5fc5d11c%7C0%7C0%7C637584167938585160%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=PAgCNJJNM%2BSFNRvt3r6M5zysCkIJTfO5V6geI6%2BRNLY%3D&reserved=0>; the summary is that a DELETE query with a dependent subquery running for every line of the spreadsheet input seems to make concurrent database access near impossible.

Has anyone else experienced anything similar? Us using MariaDB instead of MySQL might well be the cause, but I'd like confirmation before work is scheduled to migrate the database that this will indeed help. If you're using spreadsheet imports, we'd be grateful if you could let us know either on or off list whether you use MySQL or MariaDB and if you had any problems with the migration queries hogging the database.

Thanks,
p
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lyralists.lyrasis.org/pipermail/archivesspace_users_group/attachments/20210604/8b46c934/attachment.html>