799554 - runs_logs.content where content is null query doesn't use an index

Status: RESOLVED FIXED

(Tree Management Graveyard :: TBPL, defect)

Description

[Sheeri Cabral [:sheeri]]

I noticed this query is taking a long time on dev:

     Id: 2177030
   User: tbpl_dev_user
   Host: 10.8.70.208:29575
     db: tbpl_dev_allizom_org
Command: Query
   Time: 1427
  State: updating
   Info: DELETE FROM runs_logs WHERE content IS NULL

But this query is taking a long time (see, at least 1427 so far) and will cause replication delay. Also, unless you actually defragment the table, it won't free up any space on the disk (which would be nice).

Is this something that will happen in production? We do a tbpl purge (in the middle of it as we speak) so we can do this and get the space back, or at least do it in a controlled way.....

Do we want to add an index on "content" so that we can easily find the NULL values and the query won't take so long?

Comment 1

[Phil Ringnalda (:philor)]

Oops, I was going to file something about fixing more than just prod, but it was midnight and I forgot.

Yes, it will happen in production, last Friday night, in bug 798689.

Comment 2

[Ed Morley [:emorley]]

Ok, so I wrote out this post not seeing comment 1, but hopefully still of use, if there's a better way we can do this.

--

Not sure if an index is the best solution, perhaps if I explain what we're trying to do, you may know of a better way? (The code wasn't written by me & I'm a bit of a DB noob).

In TBPL's ParallelLogGenerating.php we insert rows into runs_logs with NULL content values, as a sentinel value to indicate processing is taking place for that log & so other processes should wait for it to finish. After processing completes, the NULL is replaced with the log content, or else the row deleted if an error occurred, in order that another process can attempt again:

https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/file/9c7f496beb97/php/inc/ParallelLogGenerating.php

However, if the PHP script aborts unexpectedly, or hits max_execution_time (or in the case of the prefetching, the php-cli worker is killed by import-buildbot-data.py for taking too long; which happens on a fairly regular basis at the moment, due to bug lookups from bzapi taking ages), then we have to clear up the NULL row, otherwise any later attempt to generate that row will falsely believe another process is still processing it.

We therefore run the query in comment 0 as part of prefetching (which occurs on a 5 min cron):
https://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/file/9c7f496beb97/dataimport/import-buildbot-data.py#l387
...in order to clean them up.

(The above happens on both prod and tbpl-dev).

Comment 3

[Arpad Borsos [:Swatinem]]

Is an index on a BLOB field possible at all?
We could change the code to just use a boolean field in which case the indexing would be very cheap.

Comment 4

[Sheeri Cabral [:sheeri]]

Doing this on the backup for dev: alter table runs_logs add  KEY `idx_content` (`content`(1));

Check out http://hg.mozilla.org/users/mstange_themasta.com/tinderboxpushlog/file/tip/dataimport/import-buildbot-data.py#l387 at the end - this runs every 5 minutes.

I've moved all traffic to dev2, once the currently running query on dev1 finishes I'll start the data purge, including adding the index to dev1 (we'll then need to add the index to dev2).

Comment 5

[Ed Morley [:emorley]]

The query has been happily running for several weeks, I'm surprised it needs taking one of the machines offline to complete; but as I said, I'm a DB noob :-)

Your thoughts on comment 2 once this is resolved would be appreciated - would be glad of a better way of doing this if there is one.

Comment 6

[Sheeri Cabral [:sheeri]]

Added the index in dev today.