We need a strategy when to remove old data from the database. Not only time based but also entire trees, e.g. Bug 703824 removed the reference to the Mobile tree. (In that case the data is based on the m-c branch so it does not need to be removed; other cases might leave a lot of useless data around) So lets discuss for how long we want to retain the data at all and whats the best strategy to remove obsolete data.

Well the query itself is quite simple: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); However the performance is horrible, it took like 110s on my poor mysql server to complete. I tried to add indexes to endtime (and also starttime) but thats no good as they are basically unique. (there are no two runs in my db sharing the same start or end times) Expiring unused builders: DELETE FROM builders WHERE buildername NOT IN ( SELECT DISTINCT buildername FROM runs) This runs fairly fast, once I have an index on runs.buildername (which takes 200s to be created) Any ideas how we can make these operations fast enough so we can add them to our import cron job?

I have no idea why the first query should be that slow even with an index on endtime. But if they're going to run as part of cron jobs, maybe we shouldn't care a lot about their performance?

The tables are MySQL, we can partition them based on time, and then drop old partitions when the time comes. This has 2 benefits: 0) Each partition is a file on the filesystem, so dropping a partition takes about as long as it takes to delete that file. Much faster than a regular DELETE query. 1) There is no fragmentation in the data file - because it's removed. When you do a DELETE query, there is fragmentation in the data file.

What are the next steps on this bug? When would you like to be able to purge the older data by? (for example, "in a few months" or something similar)

What are the next steps on this bug? When would you like to be able to purge the older data by? (for example, "in a few months" or something similar)

I think the next step is for someone (other than me) to make the decision "We want to retain data for X month". After that it should be easy to set up a monthly job that deletes partitions that are older than X month.

Sheeri, partitioning them by time as opposed to using a DELETE query sounds great. Please may we retain the last 1 month of data in the runs table (which will keep us roughly on-par with ftp.m.o's expiry of tinderbox logs after 30 days) & expire builders that are then unused from the builders table (along the lines of comment 1). Rough timeframe within the next month or two if that's ok? In case it helps before you take a look at the DB directly, the schema can be found here: http://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/file/tip/schema.sql Thanks! :-)

Ed - that's great! The timeframe sounds good, too.

So, in order to add partitioning, the partitioning key (in this case, endtime) must be part of the table's primary key. Here's what I'm doing on tbpl_dev_allizom_org: -- get rid of the existing primary key -- - requires getting rid of the auto_increment -- - requires an index on (id) for the foreign key from runs_notes ALTER TABLE runs MODIFY id INT NOT NULL, ADD INDEX(id), DROP PRIMARY KEY; -- Add the new primary key of (id,endtime) -- - drop the existing index on (id) b/c the foreign key will have the new primary key -- - add back the auto_increment ALTER TABLE runs ADD PRIMARY KEY (id,endtime), DROP INDEX id, MODIFY id INT NOT NULL AUTO_INCREMENT; -- Partition the table by month, from 1/2012 onwards: ALTER TABLE runs PARTITION BY RANGE ( UNIX_TIMESTAMP(endtime) ) ( PARTITION p2011_12 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-01-01 00:00:00') ), PARTITION p2012_01 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-02-01 00:00:00') ), PARTITION p2012_02 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-03-01 00:00:00') ), PARTITION p2012_03 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-04-01 00:00:00') ), PARTITION p2012_04 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-05-01 00:00:00') ), PARTITION p2012_05 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-06-01 00:00:00') ), PARTITION p2012_06 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-07-01 00:00:00') ), PARTITION p2012_07 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-08-01 00:00:00') ), PARTITION p2012_08 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-09-01 00:00:00') ), PARTITION p2012_09 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-10-01 00:00:00') ), PARTITION p2012_10 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-11-01 00:00:00') ), PARTITION p2012_11 VALUES LESS THAN ( UNIX_TIMESTAMP('2012-12-01 00:00:00') ), PARTITION p2012_12 VALUES LESS THAN ( UNIX_TIMESTAMP('2013-01-01 00:00:00') ), PARTITION pMAX VALUES LESS THAN (MAXVALUE) ); Except that this last part fails, because of the foreign key from runs_notes. Can we remove the foreign key? It is: CONSTRAINT "runs_notes_ibfk_1" FOREIGN KEY ("run_id") REFERENCES "runs" ("id") ON DELETE CASCADE The runs_notes table is quite small, I'd recommend doing a manual orphan check and delete in the same script as the partition drop would be run. If this makes sense, let me know. We'll have to drop the foreign key from the runs_notes table, which I can do on the dev server but you might also want to do it in your code....... (and if you need me to wait for you to do it in your code, let me know).

In light of bug 766524, it probably makes more sense to not bother with partitioning on the runs table....but to put an index on (endtime) and then the delete won't be so slow. We will need to defragment the tables to reclaim space. I'm reverting the changes I made on the tbpl_dev_allizom_org runs table now.

OK, so putting an index on (endtime) - is that a dev thing? Some departments have django stuff that creates/modifies tables. I'm happy to add the index, if so, do I have to try it out on dev/stage first? I'm happy to do that, but I want to know the process. :D

I'm not really too sure I'm afraid. CCing someone who has been helping unstick the tbpl-dev to prod cron job (we don't have staging), in case they know more :-) Oh and I guess any schema changes will need to be mirrored in the in-tree copy eventually: http://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/file/tip/schema.sql

cturra says he doesn't know either. So I think we can just add the index and if it comes up that it needs to be done in the code, then we'll deal with it then. Who would be the one to mirror the change to the in-tree copy?

That would be me. Or well, you can just attach a patch to schema.sql and I can r+ it :-)

Hi Sheeri, do you need one of us to land the changes in-tree now? [Just making sure you're not waiting on us for the next step :-)]

Nope, I've been on a speaking tour and fly home during the day today....we're not waiting on you, but if putting it in-tree doesn't actually do anything then go for it, I'll be able to add this index by the end of the week.

I'm a bit of a mysql noob, so let me know if this wasn't what you meant :-)

Adding moconnor so she can make sure I get this done by end of July.

Ed - looks good! In fact I'll use that index name too: ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime); Running this on dev now: mysql> use tbpl_dev_allizom_org; mysql> ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime); Note that the purge process should also include defragmentation of the 3 tables affected: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE runs_notes;

The index is added on dev. I'll follow the procedure of: on dev..... DONE 0) add index: ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime); (this took over 9 minutes on dev, but it's a shared, slow machine...then again, it has less data) DONE 1) export the table values with INSERT IGNORE, in case we have to re-import: mysqldump --no-create-info tbpl_dev_allizom_org runs > runs_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org runs_notes > runs_notes_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org runs_logs > runs_logs_2012_07_19.sql 2) Delete the values: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); 3) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE runs_notes;

Would it be possible to delete the old builders too? (Will make my life a lot easier when using the TBPL admin panel, since it won't list all the ancient configs when trying to edit the visible tests) So I guess something like this? DELETE FROM builders WHERE buildername NOT IN ( SELECT DISTINCT buildername FROM runs) OPTIMIZE TABLE builders; OPTIMIZE TABLE builders_history; I'd imagine the optimise of those tables is less critical, since they are /much/ smaller, but suppose it doesn't hurt for completeness :-)

Ed - sure! we could do that. Let me work on steps 0-3 on dev and I'll add in a step 4 to purge builders and builders_history too.

Confirmed that builders_history is the only table that depends on builders, and that the foreign key is set to ON DELETE CASCADE.

on dev: mysql> DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE runs_notes; Query OK, 5179549 rows affected (4 min 22.95 sec) mysql> OPTIMIZE TABLE runs; +---------------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------+----------+----------+-------------------------------------------------------------------+ | tbpl_dev_allizom_org.runs | optimize | note | Table does not support optimize, doing recreate + analyze instead | | tbpl_dev_allizom_org.runs | optimize | status | OK | +---------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (3 min 38.00 sec) mysql> OPTIMIZE TABLE runs_logs; +--------------------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+----------+----------+-------------------------------------------------------------------+ | tbpl_dev_allizom_org.runs_logs | optimize | note | Table does not support optimize, doing recreate + analyze instead | | tbpl_dev_allizom_org.runs_logs | optimize | status | OK | +--------------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (3.84 sec) mysql> OPTIMIZE TABLE runs_notes; +---------------------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------+----------+----------+-------------------------------------------------------------------+ | tbpl_dev_allizom_org.runs_notes | optimize | note | Table does not support optimize, doing recreate + analyze instead | | tbpl_dev_allizom_org.runs_notes | optimize | status | OK | +---------------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.60 sec)

Ed - if we're going to do those deletes, I strongly recommend an index on buildername in both tables: ALTER TABLE runs ADD INDEX `runs_buildername_idx` (`buildername`); ALTER TABLE builders ADD INDEX `builders_buildername_idx` (`buildername`); I'm doing this now on dev and will test the delete query after I do that.

Ed - I narrowed the query down to this, and it went pretty quickly: mysql> DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; Query OK, 4777 rows affected (1.07 sec) mysql> optimize table builders; +-------------------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------------+----------+----------+-------------------------------------------------------------------+ | tbpl_dev_allizom_org.builders | optimize | note | Table does not support optimize, doing recreate + analyze instead | | tbpl_dev_allizom_org.builders | optimize | status | OK | +-------------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.76 sec) mysql> optimize table builders_history; +---------------------------------------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +---------------------------------------+----------+----------+-------------------------------------------------------------------+ | tbpl_dev_allizom_org.builders_history | optimize | note | Table does not support optimize, doing recreate + analyze instead | | tbpl_dev_allizom_org.builders_history | optimize | status | OK | +---------------------------------------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (0.32 sec)

So here's the updated procedure, this was all done on dev: 0) add indexes: ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`); ALTER TABLE builders ADD INDEX `builders_buildername_idx` (`buildername`); 1) export the table values with INSERT IGNORE, in case we have to re-import: mysqldump --no-create-info tbpl_dev_allizom_org runs > runs_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org runs_notes > runs_notes_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org runs_logs > runs_logs_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org builders > builders_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org builders_history > builders_history_2012_07_19.sql 2) Delete the values from the runs table: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); 3) Delete the values from the builders table: DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; 4) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_notes; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE builders; OPTIMIZE TABLE buiders_history;

Updated with the indexes added in comment 27 :-)

After doing a test on a backup server of the production database, the deletes took 10 minutes and the optimizes took 35 minutes. This would probably be faster on production but I'd like to take the slave out of service, run it on the slave, then failover the master and do that one. Not immediately of course. But if I find that nobody's using the slave I may do it today, doing the appropriate failover of course.

Note that after the defag, the filesizes were MUCH smaller: -rw-rw---- 1 mysql mysql 9.0M Jul 19 16:52 builders_history.ibd -rw-rw---- 1 mysql mysql 15M Jul 20 08:26 runs_notes.ibd -rw-rw---- 1 mysql mysql 13M Jul 20 08:35 builders.ibd -rw-rw---- 1 mysql mysql 952M Jul 20 08:35 runs.ibd -rw-rw---- 1 mysql mysql 46G Jul 20 08:37 runs_logs.ibd

BTW I was going to do it on stage, but it doesn't seem to be where I expect it to be: mysql> use tbpl_allizom_org Database changed mysql> ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`); ERROR 1146 (42S02): Table 'tbpl_allizom_org.runs' doesn't exist mysql> show tables; Empty set (0.00 sec)

I've set the master in production to be both the read and write servers. Here we go!

IN PROGRESS 0) add indexes: ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`); ALTER TABLE builders ADD INDEX `builders_buildername_idx` (`buildername`); 1) export the table values with INSERT IGNORE, in case we have to re-import: mysqldump --no-create-info tbpl_dev_allizom_org runs > runs_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org runs_notes > runs_notes_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org runs_logs > runs_logs_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org builders > builders_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org builders_history > builders_history_2012_07_19.sql 2) Delete the values from the runs table: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); 3) Delete the values from the builders table: DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; 4) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_notes; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE builders; OPTIMIZE TABLE builders_history; -------- What I'm actually doing: mysql> set sql_log_bin=0; Query OK, 0 rows affected (0.00 sec) mysql> use tbpl_mozilla_org Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select @@global.hostname -> ; +------------------------------+ | @@global.hostname | +------------------------------+ | generic2.db.phx1.mozilla.com | +------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`);

(In reply to Ed Morley [:edmorley] from comment #28) > Created attachment 644325 [details] [diff] [review] > Add 3 indexes > > Updated with the indexes added in comment 27 :-) Ed, this is perfect - I don't know who officially has to review it, but I personally approve as the DBA :D

DONE 0) add indexes: mysql> ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`); Query OK, 6970874 rows affected, 2 warnings (27 min 45.96 sec) Records: 6970874 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE builders ADD INDEX `builders_buildername_idx` (`buildername`); Query OK, 11681 rows affected, 2 warnings (5.88 sec) Records: 11681 Duplicates: 0 Warnings: 0 DONE 1) export the table values with INSERT IGNORE, in case we have to re-import: mysqldump --no-create-info tbpl_dev_allizom_org runs > runs_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org runs_notes > runs_notes_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org runs_logs > runs_logs_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org builders > builders_2012_07_19.sql mysqldump --no-create-info tbpl_dev_allizom_org builders_history > builders_history_2012_07_19.sql IN PROGRESS 2) Delete the values from the runs table: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); 3) Delete the values from the builders table: DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; 4) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_notes; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE builders; OPTIMIZE TABLE builders_history;

DONE 0) add indexes: DONE 1) export the table values with INSERT IGNORE, in case we have to re-import: DONE 2) Delete the values from the runs table: mysql> DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); Query OK, 5254232 rows affected (12 min 33.14 sec) DONE 3) Delete the values from the builders table: mysql> DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; Query OK, 4778 rows affected (9.38 sec) IN PROGRESS: 4) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_notes; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE builders; OPTIMIZE TABLE builders_history;

Also, just for fun, before optimization: -rw-rw---- 1 mysql mysql 14M Jul 20 10:15 builders.ibd -rw-rw---- 1 mysql mysql 9.0M Jul 20 10:15 builders_history.ibd -rw-rw---- 1 mysql mysql 28M Jul 20 10:27 runs_notes.ibd -rw-rw---- 1 mysql mysql 3.6G Jul 20 10:27 runs.ibd -rw-rw---- 1 mysql mysql 123G Jul 20 10:27 runs_logs.ibd

optimization is complete and the slave is caught up in replication: -rw-rw---- 1 mysql mysql 9.0M Jul 20 13:12 builders_history.ibd -rw-rw---- 1 mysql mysql 13M Jul 20 20:40 builders.ibd -rw-rw---- 1 mysql mysql 960M Jul 20 20:40 runs.ibd -rw-rw---- 1 mysql mysql 47G Jul 20 20:43 runs_logs.ibd -rw-rw---- 1 mysql mysql 15M Jul 20 20:44 runs_notes.ibd Saved 76G in the runs_logs table alone! I have put generic2 back in as the only server in the ro generic pool.

I have moved *all* the traffic to generic2 (so generic1 is out of the ro and rw generic pool). I will leave this running for a day and if everything is still fine and nothing looks bad, I'll purge generic1. Please let us know if ANYTHING data-related looks odd.

Thank you, looks good as far as I can see so far :-) 76G freed up is pretty sweet!

(In reply to Ed Morley [:edmorley] from comment #40) > looks good as far as I can see so far :-) Note to self: think before trying to string a sentence together, *facepalm*.

Awesome, I'll wait until tomorrow to do generic1 just in case :D But I think we're good too, and yeah, 76G freed up is NICE. The backups will be so much happier too!

Comment on attachment 644325 [details] [diff] [review] Add 3 indexes Sheeri is happy with the changes (comment 34); just making this more formal :-)

It's tomorrow, hearing no complaints, I'm going to purge and defrag generic1. IN PROGRESS 0) add indexes: ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`); ALTER TABLE builders ADD INDEX `builders_buildername_idx` (`buildername`); 1) export the table values with INSERT IGNORE, in case we have to re-import: mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs > runs_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs_notes > runs_notes_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs_logs > runs_logs_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org builders > builders_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org builders_history > builders_history_2012_07_19.sql 2) Delete the values from the runs table: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); 3) Delete the values from the builders table: DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; 4) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_notes; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE builders; OPTIMIZE TABLE builders_history;

At the beginning: \! df -h . Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg00-root 261G 189G 60G 76% /

DONE 0) add indexes: ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`); ALTER TABLE builders ADD INDEX `builders_buildername_idx` (`buildername`); IN PROGRESS 1) export the table values with INSERT IGNORE, in case we have to re-import: mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs > runs_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs_notes > runs_notes_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs_logs > runs_logs_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org builders > builders_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org builders_history > builders_history_2012_07_19.sql 2) Delete the values from the runs table: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); 3) Delete the values from the builders table: DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; 4) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_notes; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE builders; OPTIMIZE TABLE builders_history;

Comment on attachment 644325 [details] [diff] [review] Add 3 indexes Thanks for the review :-) http://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/rev/547b10d8cc4c

DONE 0) add indexes: ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`); ALTER TABLE builders ADD INDEX `builders_buildername_idx` (`buildername`); DONE 1) export the table values with INSERT IGNORE, in case we have to re-import: mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs > runs_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs_notes > runs_notes_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs_logs > runs_logs_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org builders > builders_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org builders_history > builders_history_2012_07_19.sql IN PROGRESS 2) Delete the values from the runs table: DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); 3) Delete the values from the builders table: DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; 4) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_notes; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE builders; OPTIMIZE TABLE builders_history;

DONE 0) add indexes: ALTER TABLE runs ADD INDEX runs_endtime_idx (endtime), ADD INDEX `runs_buildername_idx` (`buildername`); ALTER TABLE builders ADD INDEX `builders_buildername_idx` (`buildername`); DONE 1) export the table values with INSERT IGNORE, in case we have to re-import: mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs > runs_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs_notes > runs_notes_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org runs_logs > runs_logs_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org builders > builders_2012_07_19.sql mysqldump --insert-ignore --no-create-info tbpl_dev_allizom_org builders_history > builders_history_2012_07_19.sql DONE 2) Delete the values from the runs table: mysql> DELETE FROM runs WHERE endtime < ( NOW( ) - INTERVAL 2 MONTH ); Query OK, 5415593 rows affected (27 min 17.22 sec) DONE 3) Delete the values from the builders table: mysql> DELETE builders.* from builders LEFT JOIN runs USING (buildername) WHERE runs.buildername IS NULL; Query OK, 4179 rows affected (14.42 sec) IN PROGRESS 4) Defrag the tables: OPTIMIZE TABLE runs; OPTIMIZE TABLE runs_notes; OPTIMIZE TABLE runs_logs; OPTIMIZE TABLE builders; OPTIMIZE TABLE builders_history;

Sheeri you set an end of July deadline for yourself, that is today. Is this work done? :)

tl;dr one-time defrag is done, automation can't happen just yet, so we should plan the next one. ---- We went from 60G available to 140G available. Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg00-root 261G 109G 140G 44% / As for "is this done" - the work is done, but there's no automation to it. So I'd probably make a new ticket to check for tbpl fragmentation every month or something, so we can set up a regular maintenance for it. So a one-time defrag has been done and we are now reaping the benefits of smaller data, but we really should do it regularly (but *how* regularly depends on what we'll see....)

Is everything confirmed fixed after bug 777634 had to stop the optimise? Also, next time we do this, we can reduce the 2 months to 1 months, since the logs (which the TBPL results ultimately summarise) are expired after 30 days - so other than the summary, people can't make much use of results after that time anyway :-) Thank you again for this! :-D

Ed - yeah, I had forgotten to put the command in not to replicate, the problem in bug 777634 was that the optimize was replicated to generic2 - which was already defrag'd before, and was in production. We can certainly reduce 2 months to 1 month, that's even better for us! Hopefully things are snappier on your end.

Closing this ticket in favor of https://bugzilla.mozilla.org/show_bug.cgi?id=779290