Page MenuHomePhabricator

Permit temporary table operations to be executed on replica connections
Closed, ResolvedPublic

Description

In rMW36f4daf32c591d6b7e2435629fc6e431398b641a, MediaWiki's DBAL was adjusted to reject any write query on read-only DB replica connections. This poses a problem for extensions that use temporary tables in their queries, as such queries now have to be executed on the source DB rather than a replica to work around this fact. An example of such an extension is Semantic MediaWiki, whose QueryEngine uses temporary tables extensively in serving reads. The current situation, where all writes, including non-persistent ones, must be executed on a source DB connection, causes scalability issues since it's no longer possible to distribute these queries between multiple replicas.

In Fandom's SMW setup, we have configured the extension to use replica DBs (MySQL 5.7 running with --read-only flag set) for its reads, including the temporary table operations, and we have not observed any issues from this configuration. Thus, I think it may be worth to again permit temporary table operations to be executed on DB replica connections as well, to allow for better scaling of deployments that utilize extensions that need them.

See also: https://github.com/SemanticMediaWiki/SemanticMediaWiki/issues/4817

Event Timeline

TK-999 created this task.Jul 31 2020, 3:45 PM
Restricted Application added a project: Platform Engineering. · View Herald TranscriptJul 31 2020, 3:45 PM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript

Tagging Daniel, Aaron, Roan and Adam on this ticket for visibility as contributors to T183265 and T183242.

Change 617741 had a related patch set uploaded (by TK-999; owner: TK-999):
[mediawiki/core@master] Permit temporary table writes on replica DB connections

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

Change 617741 merged by jenkins-bot:
[mediawiki/core@master] Permit temporary table writes on replica DB connections

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

Krinkle closed this task as Resolved.Aug 1 2020, 3:00 AM
Krinkle assigned this task to TK-999.
Krinkle awarded a token.
Krinkle moved this task from Untriaged to Rdbms library on the Wikimedia-Rdbms board.
TK-999 added a comment.Aug 1 2020, 3:01 PM

Thanks for the review! :)

Change 617817 had a related patch set uploaded (by Reedy; owner: TK-999):
[mediawiki/core@REL1_35] Permit temporary table writes on replica DB connections

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

Change 617817 merged by jenkins-bot:
[mediawiki/core@REL1_35] Permit temporary table writes on replica DB connections

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