644858 - clobberer is slow to load - do routine maintenance

Status: RESOLVED FIXED

(Release Engineering :: General, defect, P2)

Description

[Nick Thomas [:nthomas] (UTC+12)]

At the moment it's returning about 1.3M of html table and taking several seconds to load, and clobbers sometimes don't take (or appear to take) on the first attempt.

The disk space script run by the slaves (purge_builds.py) removes build directories older than 14 days, so we should be able to expire rows from the clobberer db that are over that threshold (he says without actually verifying how the data is used). From the builds table, 3378 of 6701 rows have last_build_time is more than 14 days ago. For the clobber_times it's 3191 of 7621 for lastclobber. We have data in there going back to Jan 20.

There are probably some special cases, like releases, and anything calling clobberer but not purge_builds.py. And might need to think about if sqlite3 needs to care about locking, but we can surely dream up a way to keep the db trim using cron.

Comment 1

[Chris AtLee [:catlee]]

Here's what I'm thinking...

DELETE from builds where last_build_time < strftime("%s", "now", "-21 days") and buildername not like 'rel%';

DELETE from clobber_times where lastclobber < strftime("%s", "now", "-21 days") and builddir not like 'rel%';

The former would delete 3068/6871 rows right now, and the latter 661/7772 rows.

Comment 2

[Chris AtLee [:catlee]]

I did:

delete from builds where last_build_time < strftime("%s", "now", "-21 days") and buildername not like 'rel%';

delete from clobber_times where lastclobber < strftime("%s", "now", "-21 days");

Comment 3

[Chris AtLee [:catlee]]

Just did the above again, followed by 'vacuum'

Comment 4

[Chris AtLee [:catlee]]

Attachment: cleanup cron script

Comment 5

[Dustin J. Mitchell [:dustin] (he/him)]

Comment on attachment 528866 [details] [diff] [review]
cleanup cron script

Looks good.  Will you be monitoring it to see how often those queries fail?  It may make more sense to run them in a short loop until they succeed.

Comment 6

[Chris AtLee [:catlee]]

Comment on attachment 528866 [details] [diff] [review]
cleanup cron script

running this for a bit as a cron job under my account. if all goes well, will migrate to another user (apache?)

Comment 7

[Chris AtLee [:catlee]]

This has been running for a week now and has only failed once due to a locked database.

I'll file and IT bug to get this set up under a different user.