Jobs table too large to sort?

16 views

Skip to first unread message

Aaron Hunnewell

unread,

May 1, 2018, 9:32:26 AM5/1/18

to Archivematica Tech

I realize now that i probably should have posted here rather than the regular group.

I'm running into a problem where the "Jobs" table is too large to sort effectively and show the transfers tab in AM.

mysql> select count(jobUUID) from Jobs;
+----------------+
| count(jobUUID) |
+----------------+
|         281249 |
+----------------+
1 row in set (0.25 sec)

Jobs only has 281k records, but the sort command seems to be very slow for showing the transfers tab -

 SELECT `Jobs`.`jobUUID`, `Jobs`.`jobType`, `Jobs`.`createdTime`, `Jobs`.`createdTimeDec`, `Jobs`.`directory`, `Jobs`.`SIPUUID`, `Jobs`.`unitType`, `Jobs`.`currentStep`, `Jobs`.`microserviceGroup`, `Jobs`.`hidden`, `Jobs`.`MicroServiceChainLinksPK`, `Jobs`.`subJobOf` FROM `Jobs` WHERE (`Jobs`.`subJobOf` = '' AND `Jobs`.`SIPUUID` = 'faf0066e-7680-4bbb-af54-f785d010c42a') ORDER BY `Jobs`.`createdTime` DESC, `Jobs`.`subJobOf` ASC

It seems like my options are to prune the Jobs table, but i'm not sure if that's a safe thing to do. Could anyone chime in on that?

Or I could try to move the mysql instance off the machine to one with SSD, this is currently my safest bet I think.

Or maybe the query or table structure could be tweaked to better handle large jobs tables?

related - tasks is at 3636307 rows. This is also problematic sometimes for us - related issue - https://github.com/artefactual/archivematica/issues/930