https://bugzilla.wikimedia.org/show_bug.cgi?id=62360
Bug ID: 62360
Summary: LocalFileDeleteBatch::getHashes oldimage table scan on
commons is slow
Product: MediaWiki
Version: 1.23-git
Hardware: All
OS: All
Status: NEW
Severity: major
Priority: Unprioritized
Component: File management
Assignee: wikibugs-l@lists.wikimedia.org
Reporter: sprin...@wikimedia.org
CC: aarcos.w...@gmail.com, bawolff...@gmail.com,
bryan.tongm...@gmail.com, fflo...@wikimedia.org,
gti...@wikimedia.org, mtrac...@member.fsf.org
Web browser: ---
Mobile Platform: ---
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?
2. oldimage deserves a primary key to make online schema changes possible.
3. Can the query go to a slave?
--
You are receiving this mail because:
You are the assignee for the bug.
You are on the CC list for the bug.
_______________________________________________
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
