Actions
action #55907
closedDeleting job results is too slow
Description
The main bottleneck is checking screenshots that are no longer attached.
[2019-08-23T18:55:03.0649 CEST] [debug] [pid:2164] [DBIC] Took 0.68212500 seconds: SELECT me.id, me.filename, me.t_created FROM screenshots me LEFT OUTER JOIN screenshot_links links_outer ON links_outer.screenshot_id = me.id WHERE ( id IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ) GROUP BY me.id HAVING COUNT(links_outer.job_id) = 0: '99830535', '191526292', '99830500', '99830499', '99830536', '156209408', '17779700', '185486638', '185486636', '106285578', '190738955', '191527359', '191526710', '191526711', '185107267', '191526708', '152788113', '105046651', '105046658', '176251808', '105047637', '105046831', '176279984', '185486365', '186074859', '185486363', '185486366', '191527327', '106285578', '176245953', '130324508', '191535899', '191526920', '152576923', '152607891', '185107053', '155173141', '191532253', '133347265', '191532253', '133347264', '191532705', '191532702', '191532700', '105083401', '191532706', '191532704', '148183313', '105087464', '191532701', '148163978', '191532703', '190739946', '140809663', '52217985', '56783604', '56783605', '105046651', '191536177', '191535952', '191535953', '191535951', '191535899', '187485544', '187484843', '191528502', '187485557', '187485547', '187485550', '187484844', '187485552', '187485548', '187485553', '187485554', '191527327', '187485558', '187485551', '187485555', '187485546', '191528501', '187485549', '187485545', '187485556', '144866642', '191545745', '191545756', '191545737', '191545736', '191545751', '191545746', '191545743', '191545738', '191545740', '191545741', '191545754', '191545752', '191545749', '191545739', '191545750', '191545747', '191545748', '191545753', '17768866', '191545744', '191545755', '144154994', '140809998', '191547480', '191547483', '191547481', '191547482'
[2019-08-23T18:55:04.0838 CEST] [debug] [pid:2164] [DBIC] Took 0.78038500 seconds: SELECT me.id, me.filename, me.t_created FROM screenshots me LEFT OUTER JOIN screenshot_links links_outer ON links_outer.screenshot_id = me.id WHERE ( id IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ) GROUP BY me.id HAVING COUNT(links_outer.job_id) = 0: '17772437', '156210750', '191526303', '99274929', '99305674', '99274930', '99274931', '99274932', '185486636', '185486638', '185486363', '185486366', '185486365', '190452973', '176245953', '130324119', '191530001', '191528171', '105083126', '105046831', '176279984', '191530001', '191530081', '191530080', '191526708', '185107267', '152607891', '191526920', '190738955', '191526709', '155173141', '185107053', '106285578', '191526710', '191526711', '152788113', '152576923', '106285578', '191530153', '56783488', '56783487', '105046650', '105046651', '52217985', '105046651', '105046658', '176251808', '191539082', '191539080', '191539081', '191539079', '187485552', '191528175', '187485548', '191528171', '187485560', '187485550', '187485547', '187485557', '187485544', '187485556', '187485549', '187485545', '187485555', '187485546', '190269450', '191528174', '187485551', '187485558', '187485553', '187485554', '191530895', '191530896', '191530900', '191530898', '191530902', '105087506', '191530899', '133347406', '133347405', '105083544', '191530895', '191530897', '152581054', '191530901', '140809663', '190740628', '144154994', '191537673', '191537672', '191537666', '191537683', '191537674', '191537679', '17768866', '191537667', '191537669', '191537676', '191537678', '191537677', '191537665', '191537668', '191537680', '191537670', '191537671', '191537681', '191537682', '191537675', '142394961', '144154994'
[2019-08-23T18:55:09.0039 CEST] [debug] [pid:2164] [DBIC] Took 3.77121400 seconds: SELECT me.id, me.filename, me.t_created FROM screenshots me LEFT OUTER JOIN screenshot_links links_outer ON links_outer.screenshot_id = me.id WHERE ( id IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ) GROUP BY me.id HAVING COUNT(links_outer.job_id) = 0: '191662048', '191662052', '191662048', '107017704', '107017708', '148213088', '191662050', '107017707', '107017705', '191662051', '191661350', '130407153', '99830536', '99830499', '156209408', '99830535', '99830500', '191526292', '190817757', '191526986', '191659519', '191659517', '191659516', '191659518', '191659515', '180993512', '190738444', '190738927', '190738926', '190738925', '190818308', '140809775', '191661243', '106403140', '191663586', '191663596', '191663589', '191663587', '191663582', '191663590', '191663594', '191663591', '190738970', '190738969', '106366457', '187486088', '106302664', '106302530', '187485773', '106302600', '191661459', '191661350', '191661459', '191661460', '191661458', '140809842', '191664193', '191664190', '191664195', '191664192', '191664194', '30402654', '191664191', '191663583', '191663585', '191663595', '191663593', '191663580', '191663588', '191663598', '191663581', '191663584', '191663592', '191663599', '191663597', '52217985', '191541980', '191668332', '191668335', '191668331', '191668334', '180993512', '94026418', '191668333', '191541789', '17780336', '191541980', '191664094', '140809998', '140809842', '53340426', '76809710', '140810010', '30402654', '112145167', '191665304', '30402312', '191665302', '191665307', '191665303', '191665301', '191665306', '52436568', '191665305', '110620931', '110603864', '110500248', '191541980', '110450278', '30402312'
[2019-08-23T18:55:10.0322 CEST] [debug] [pid:2164] [DBIC] Took 0.85597900 seconds: SELECT me.id, me.filename, me.t_created FROM screenshots me LEFT OUTER JOIN screenshot_links links_outer ON links_outer.screenshot_id = me.id WHERE ( id IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ) GROUP BY me.id HAVING COUNT(links_outer.job_id) = 0: '106285578', '185107053', '176022236', '187483653', '130220341', '191527066', '56783604', '56783488', '105046650', '52217986', '185486363', '185486365', '185486366', '185958437', '99830536', '156209408', '99830535', '99830500', '191526292', '99830499', '17772437', '191526709', '191526708', '190738955', '155173141', '191526711', '191526710', '185107053', '185486636', '185486638', '191527012', '105046732', '105046500', '105046735', '130220341', '130324204', '105046499', '105046733', '105046734', '176279984', '105047637', '105046831', '105046658', '191527066', '191527067', '191527065', '176251808', '105046650', '186311177', '191527623', '140809663', '191527388', '191527386', '105087464', '191527143', '151514095', '166286790', '148183313', '105083401', '133347265', '191527387', '191527143', '144155069', '152584605', '191531935', '191531945', '191531943', '191531939', '191531944', '191531938', '191531947', '191531949', '191531932', '17768866', '191531942', '191531933', '191531940', '191531948', '191531951', '191531937', '191531946', '191531936', '191531941', '191531934', '191531950', '144155069', '144154994', '191533188'
Notice it takes between 600 and 3700ms to do this query. We already have an index on screenshot_id, so we may require something smarter.
Actions