Purge old job_artifacts and defrag the DB

RESOLVED FIXED

Status

Tree Management
Treeherder: Infrastructure
P1
normal
RESOLVED FIXED
a year ago
a year ago

People

(Reporter: emorley, Assigned: emorley)

Tracking

Details

(Assignee)

Description

a year ago
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');
(Assignee)

Updated

a year ago
Depends on: 1301729, 1258861, 1299861
(Assignee)

Updated

a year ago
Depends on: 1303102
(Assignee)

Comment 1

a year ago
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.
Status: ASSIGNED → RESOLVED
Last Resolved: a year ago
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.