1303069 - Purge old job_artifacts and defrag the DB

Status: RESOLVED FIXED

(Tree Management :: Treeherder: Infrastructure, defect, P1)

Description

[Ed Morley [:emorley]]

Now that various job artifact types have been migrated to their own tables/storage of them in the old table stopped, we can purge the old artifacts & defrag the DB.

On Heroku, I'm running:

./manage.py run_sql -s "delete from job_artifact where name='text_log_summary'"
./manage.py run_sql -s "delete from job_artifact where name='buildapi_running'"
./manage.py run_sql -s "delete from job_artifact where name='buildapi'"

It's completed on stage, in progress on prototype.

After, I'll continue Will's work on doing this for SCL3 stage/prod.

We'll also need to defrag the tables to see the benefit. Generating the SQL statements to do so can be done using:

SELECT CONCAT('OPTIMIZE TABLE ',table_schema,'.',table_name,';') OptimizeTableSQL
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'sys');

Comment 1

[Ed Morley [:emorley]]

On Heroku prototype/stage I have:
* Purged artifacts of type {Job Info, text_log_summary, buildapi_running, buildapi}
* Defragged all tables

On SCL3 stage I have:
* Purged artifacts of type {Job Info, text_log_summary, buildapi_running, buildapi}
* Defragged all tables, apart from some of the larger ones that would cause too much load to do online (see below). (Plus SCL3 stage DB will be thrown away soon)

On SCL3 prod I have:
* Purged artifacts of type {Job Info, text_log_summary, buildapi_running}
* Defragged all tables, apart from some of the larger ones that would cause too much load to do online (see below).

Tables not defragged on SCL3 stage/prod:
 - autoland.job
 - mozilla_inbound_jobs_1.job
 - try_jobs_1.job
 - treeherder.failure_line
 - treeherder.job
 - treeherder.job_detail
 - treeherder.job_log
 - treeherder.performance_datum
 - treeherder.text_log_error
 - treeherder.text_log_step
 - treeherder.text_log_summary
 - treeherder.text_log_summary_line

SCL3 prod usage dropped from 413GB to 354GB, stage from 318GB to 288GB.