Page MenuHomePhabricator

LocalFileDeleteBatch::getHashes oldimage table scan on commons is slow
Closed, ResolvedPublic

Description

Spotted this on commonswiki master:

SELECT /* LocalFileDeleteBatch::getHashes */ oi_archive_name, oi_sha1 FROM oldimage WHERE oi_archive_name IN ('20140306203724!Copa_América_Argentina_2011_(logo).png', '20140306204156!Copa_América_Argentina_2011_(logo).png')

Commons oldimage has millions of rows, and this does a table scan because oi_archive_name is only indexed in second position:

KEY oi_name_archive_name (oi_name,oi_archive_name(14))

This transaction was also holding locks on page table presumably from an earlier query. Those locks caused a number of other file-upload transaction to exceed innodb_lock_wait_timeout.

tendril> select * from innodb_locks_log where lock_trx_id = 'AB64BF626'\G

  • 1. row ******* stamp: 2014-03-07 05:51:47 server_id: 1060 lock_id: AB64BF626:0:34662177:177

lock_trx_id: AB64BF626

lock_mode: X
lock_type: RECORD

lock_table: commonswiki.page
lock_index: name_title
lock_space: 0

lock_page: 34662177
 lock_rec: 177
lock_data: 6, 0x436F70615F416DC3A9726963615F417267656E74696E615F323031315F286C6F676F292E706E67

... more similar locks ...
16 rows in set (0.35 sec)

Therefore:

  1. oi_archive_name deserves an index, or can oi_name be included in the query?
  1. oldimage deserves a primary key to make online schema changes possible.
  1. Can the query go to a slave?

Version: 1.23.0
Severity: major

Details

Reference
bz62360

Event Timeline

bzimport raised the priority of this task from to High.Nov 22 2014, 3:07 AM
bzimport set Reference to bz62360.
Springle created this task.Mar 7 2014, 6:28 AM

Change 127871 had a related patch set uploaded by Aaron Schulz:
Fixed slow query in LocalFileDeleteBatch::getHashes()

https://gerrit.wikimedia.org/r/127871

Change 127871 merged by Springle:
Fixed slow query in LocalFileDeleteBatch::getHashes()

https://gerrit.wikimedia.org/r/127871

Change 128943 had a related patch set uploaded by Aaron Schulz:
Fixed slow query in LocalFileDeleteBatch::getHashes()

https://gerrit.wikimedia.org/r/128943

Change 128943 merged by jenkins-bot:
Fixed slow query in LocalFileDeleteBatch::getHashes()

https://gerrit.wikimedia.org/r/128943

Closing again - all merged.

Gilles raised the priority of this task from High to Unbreak Now!.Dec 4 2014, 10:11 AM
Gilles moved this task from Untriaged to Done on the Multimedia board.
Gilles lowered the priority of this task from Unbreak Now! to High.Dec 4 2014, 11:22 AM
bd808 moved this task from Tag to Archive on the Performance Issue board.Mar 11 2015, 7:30 PM