CompareService::getTotalEditsFromIp queries exceeding TransactionProfiler limits
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Dreamy_Jazz
	Jul 25 2023, 10:07 AM

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).

Logtash single document

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}

Details

	Subject	Repo	Branch	Lines +/-
	CompareService: Limit results when counting edits	mediawiki/extensions/CheckUser	master	+1 -0

Customize query in gerrit

Related Objects
Search...

View Standalone Graph

This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Status	Subtype	Assigned	Task
			· · ·
Resolved		None	T248926 Performance review of checkuser database queries [NOT READY]
Resolved		tstarling	T342613 CompareService::getTotalEditsFromIp queries exceeding TransactionProfiler limits
Resolved	Feature	Dreamy_Jazz	T324907 Create separate tables for log events in CheckUser
			· · ·

Event Timeline

Dreamy_Jazz created this task.Jul 25 2023, 10:07 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 25 2023, 10:07 AM

Dreamy_Jazz updated the task description. (Show Details)Jul 25 2023, 10:10 AM

Dreamy_Jazz moved this task from Untriaged to CheckUser on the Anti-Harassment board.

Dreamy_Jazz moved this task from General / Unsorted to Investigate on the CheckUser board.

Dreamy_Jazz updated the task description. (Show Details)

Dreamy_Jazz edited projects, added Anti-Harassment (AHaT Sprint 32 - Baseball Cap); removed Anti-Harassment.Jul 25 2023, 12:07 PM

Dreamy_Jazz added a subtask: T324907: Create separate tables for log events in CheckUser.Jul 25 2023, 12:15 PM

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.

Dreamy_Jazz edited projects, added Anti-Harassment; removed Anti-Harassment (AHaT Sprint 32 - Baseball Cap).Jul 25 2023, 12:20 PM

Dreamy_Jazz added a parent task: T248926: Performance review of checkuser database queries [NOT READY].

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

gerritbot added a project: Patch-For-Review.Nov 17 2023, 3:36 AM

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

Maintenance_bot removed a project: Patch-For-Review.Nov 21 2023, 4:30 PM

ReleaseTaggerBot added a project: MW-1.42-notes (1.42.0-wmf.7; 2023-11-28).Nov 21 2023, 5:00 PM