Page MenuHomePhabricator

CompareService::getTotalEditsFromIp queries exceeding TransactionProfiler limits
Closed, ResolvedPublic

Description

The query called by CompareService::getTotalEditsFromIp has exceeded the TransactionProfiler limit for read queries on a number of occasions in the last week. The queries reported in logstash take anywhere from 6 to 10 seconds (bear in mind that if the query took less than 5 seconds it is not reported in logstash).

message
Expectation (readQueryTime <= 5) by MediaWiki::main not met (actual: 7.081221818924) in trx #007d14b30b:
SELECT COUNT(*) AS `rowcount` FROM (SELECT N FROM `cu_changes` JOIN `actor` ON ((actor_id=cuc_actor)) WHERE cuc_ip_hex = 'X' AND cuc_type IN (N,...,N) AND (cuc_id IS NOT NULL) ) `tmp_count`
trace
from /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/TransactionProfiler.php(542)
#0 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/TransactionProfiler.php(351): Wikimedia\Rdbms\TransactionProfiler->reportExpectationViolated(string, Wikimedia\Rdbms\GeneralizedSql, double, string, string)
#1 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/database/TransactionManager.php(615): Wikimedia\Rdbms\TransactionProfiler->recordQueryCompletion(Wikimedia\Rdbms\GeneralizedSql, double, boolean, integer, string, string)
#2 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/database/Database.php(923): Wikimedia\Rdbms\TransactionManager->recordQueryCompletion(Wikimedia\Rdbms\GeneralizedSql, double, boolean, integer, string)
#3 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/database/Database.php(813): Wikimedia\Rdbms\Database->attemptQuery(Wikimedia\Rdbms\Query, string, boolean)
#4 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/database/Database.php(734): Wikimedia\Rdbms\Database->executeQuery(Wikimedia\Rdbms\Query, string, integer)
#5 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/database/Database.php(1427): Wikimedia\Rdbms\Database->query(Wikimedia\Rdbms\Query, string)
#6 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/database/Database.php(1489): Wikimedia\Rdbms\Database->select(array, array, array, string)
#7 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/database/DBConnRef.php(119): Wikimedia\Rdbms\Database->selectRowCount(array, string, array, string, array, array)
#8 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/database/DBConnRef.php(371): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#9 /srv/mediawiki/php-1.41.0-wmf.18/includes/libs/rdbms/querybuilder/SelectQueryBuilder.php(754): Wikimedia\Rdbms\DBConnRef->selectRowCount(array, string, array, string, array, array)
#10 /srv/mediawiki/php-1.41.0-wmf.18/extensions/CheckUser/src/Investigate/Services/CompareService.php(75): Wikimedia\Rdbms\SelectQueryBuilder->fetchRowCount()
#11 /srv/mediawiki/php-1.41.0-wmf.18/extensions/CheckUser/src/Investigate/Pagers/ComparePager.php(221): MediaWiki\CheckUser\Investigate\Services\CompareService->getTotalEditsFromIp(string)
#12 /srv/mediawiki/php-1.41.0-wmf.18/includes/pager/TablePager.php(199): MediaWiki\CheckUser\Investigate\Pagers\ComparePager->formatValue(string, string)
#13 /srv/mediawiki/php-1.41.0-wmf.18/includes/pager/IndexPager.php(549): TablePager->formatRow(stdClass)
#14 /srv/mediawiki/php-1.41.0-wmf.18/includes/pager/IndexPager.php(586): IndexPager->getRow(stdClass)
#15 /srv/mediawiki/php-1.41.0-wmf.18/includes/pager/TablePager.php(110): IndexPager->getBody()
#16 /srv/mediawiki/php-1.41.0-wmf.18/extensions/CheckUser/src/Investigate/SpecialInvestigate.php(393): TablePager->getFullOutput()
#17 /srv/mediawiki/php-1.41.0-wmf.18/extensions/CheckUser/src/Investigate/SpecialInvestigate.php(205): MediaWiki\CheckUser\Investigate\SpecialInvestigate->addTabContent(string)
#18 /srv/mediawiki/php-1.41.0-wmf.18/includes/specialpage/SpecialPage.php(701): MediaWiki\CheckUser\Investigate\SpecialInvestigate->execute(string)
#19 /srv/mediawiki/php-1.41.0-wmf.18/includes/specialpage/SpecialPageFactory.php(1563): SpecialPage->run(string)
#20 /srv/mediawiki/php-1.41.0-wmf.18/includes/MediaWiki.php(344): MediaWiki\SpecialPage\SpecialPageFactory->executePath(string, RequestContext)
#21 /srv/mediawiki/php-1.41.0-wmf.18/includes/MediaWiki.php(948): MediaWiki->performRequest()
#22 /srv/mediawiki/php-1.41.0-wmf.18/includes/MediaWiki.php(597): MediaWiki->main()
#23 /srv/mediawiki/php-1.41.0-wmf.18/index.php(50): MediaWiki->run()
#24 /srv/mediawiki/php-1.41.0-wmf.18/index.php(46): wfIndexMain()
#25 /srv/mediawiki/w/index.php(3): require(string)
#26 {main}

Event Timeline

Dreamy_Jazz moved this task from Untriaged to CheckUser on the Anti-Harassment board.
Dreamy_Jazz moved this task from Inbox to Investigate on the CheckUser board.
Dreamy_Jazz updated the task description. (Show Details)
Dreamy_Jazz updated the task description. (Show Details)

This task can be effectively solved once T324907: Create separate tables for log events in CheckUser has completed migration (and therefore stably SCHEMA_COMPAT_NEW). This is because the SQL query generated can have the cuc_type constraint removed as there will no longer be any rows with the value as RC_LOG. This means that all possible values of cuc_type will be the ones specified in the query which makes the condition redundant and should mean that the query can use existing indexes more effectively to generate a row count.

Change 975108 had a related patch set uploaded (by Tim Starling; author: Tim Starling):

[mediawiki/extensions/CheckUser@master] CompareService: Limit results when counting edits

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

Assuming this is the same as T346970 and the error occurred on wikidata, I think my change linked above is necessary and sufficient. A lot of edits are done from toolforge, so you could have hundreds of millions of edits from a given IP. So it's going to time out trying to count them regardless of whether indexes are used. The only solution is to not count them.

Change 975108 merged by jenkins-bot:

[mediawiki/extensions/CheckUser@master] CompareService: Limit results when counting edits

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

Dreamy_Jazz assigned this task to tstarling.

Seems resolved based on logstash (other exceeds of limits are from other code).