Page MenuHomePhabricator

Fix problematic database master queries performed on HTTP GET/HEAD
Open, MediumPublic

Description

Most of MediaWiki was written with the assumption that MediaWiki and its data stores are collocated and connected via reliable, low-latency network links. Until recently, MediaWiki had minimal facilities for maintaining consistency and partition tolerance across wide-area network links. As a result, although the Wikimedia Foundation operates data centers in multiple locations, we only run MediaWiki in one location at any one time.

This has several practical consequences: first, we are not as fault-tolerant as we'd like to be. We have a secondary data center with enough capacity to serve our traffic in case our primary datacenter goes down, but it is in cold standby, meaning it takes some time (and some manual effort) to get it running. Second, site performance is poor for logged-in users that are geographically remote from Ashburn, Virginia, due to the time it takes to transmit and receive data across long-distance links. Thirdly, in some basic cases, like parsing pages, the the master database must be up, leading to a SPOF.

It's going to take a lot of work to fix this completely, but we are getting closer to being able to serve some traffic from secondary datacenter. Specifically, we would like to serve "reads" -- requests that don't require a master database connection -- from a secondary datacenter.

In order to serve reads from a different datacenter, we need to be able to predict which incoming requests will modify data, so that we can route them accordingly. We need to be able to make this determination at the edge -- i.e., the outermost layers of the infrastructure, so it cannot be complicated or slow.

The solution we have is to use the HTTP request method (T91820): GETs/HEADs are read-only, while POSTs are not. This was already true for most cases, but there is a long tail of actions with side-effects that are done via GET, such as purge, rollback, markpatrolled.

This task mostly involves fixing DBPerformance log warnings. Warnings can be dealt with be:
a) Changing DB master reads to use DB slaves
b) Moving the database updates to POST requests, the jobqueue, or at least to post-send updates via DeferredUpdates
c) Disabling warnings for a few exceptional cases like CentralAuth.

See +channel:DBPerformance on logstash.wikimedia.org

Most of these warnings are writes or master queries on HTTP GET requests, which would be cross DC in active-active setup for some user. Ideally we could eventually get these to zero.

Details

Related Gerrit Patches:
mediawiki/core : master[WIP] Avoid Category count refresh DB writes on HTTP GET
mediawiki/core : masterUse the main stash for basic user talk page notifications
mediawiki/core : masterAdd UserOptionsUpdateJob class and use it for namespaces at SpecialSearch
mediawiki/extensions/NewUserMessage : masterUse a job for triggering new user talk messages
mediawiki/extensions/CentralAuth : masterAvoid triggering master queries in ApiValidatePassword
mediawiki/extensions/CentralAuth : masterAvoid master queries in beginSecondaryAuthentication()
mediawiki/core : masterAvoid DB_MASTER queries in User::newSystemUser() when possible
mediawiki/extensions/CentralAuth : masterAvoid master queries in SpecialGlobalRenameProgress
mediawiki/extensions/Translate : masterUse TranslateUtils::getSafeReadDB() in loadAggregateGroups
mediawiki/extensions/PageTriage : masterAvoid DB_MASTER queries on HTTP GET in ArticleMetadata->getMetadata
mediawiki/extensions/CentralAuth : masterAdd $flags parameter to renameInProgressOn()
mediawiki/core : masterAvoid master queries in loadAndLazyInit() for miser mode
mediawiki/extensions/CentralAuth : masterMake opportunistic password hash upgrades post-send
mediawiki/core : masterAvoid page_touched update for HTTP GET action=purge requests
mediawiki/extensions/CentralAuth : masterMake CentralAuthHooks::onLocalUserCreated() trigger importLocalNames()
mediawiki/core : masterMake SessionBackend::save() update the user post-send
mediawiki/extensions/CentralAuth : REL1_27Fix central logout
mediawiki/extensions/CentralAuth : masterFix central logout
mediawiki/extensions/CentralAuth : REL1_27Avoid DBPerformance log warnings in onUserLogout()
mediawiki/extensions/CentralAuth : masterAvoid DBPerformance log warnings in onUserLogout()
mediawiki/extensions/Math : masterAvoid DBPerformance warnings in writeToDatabase()
mediawiki/core : masterAvoid DBPerformance warnings in Category::initialize()
mediawiki/extensions/CentralAuth : masterAvoid master queries/writes in listUnattached()
mediawiki/extensions/FlaggedRevs : masterAvoid master queries on HTTP GET in getVisibilitySettingsFromRow()
mediawiki/core : masterAvoid DBPerformance warnings on PURGE/TRACE requests
mediawiki/extensions/LiquidThreads : masterAvoid master connection in recacheMessageCount()
mediawiki/extensions/Wikibase : masterAvoid master queries on GET in showPermissionError()
mediawiki/extensions/CentralAuth : wmf/1.28.0-wmf.2Fix central logout
mediawiki/extensions/EducationProgram : masterDefer DB update in showEnrollmentView()
mediawiki/extensions/CentralAuth : masterRevert "Avoid DBPerformance log warnings in onUserLogout()"
mediawiki/extensions/Translate : masterUse DeferredUpdates in queueUpdates()
mediawiki/extensions/LiquidThreads : masterDefer DB update in NewUserMessagesView
mediawiki/extensions/FlaggedRevs : masterAvoid master queries in GET in onProtectionForm()
mediawiki/extensions/FlaggedRevs : masterConvert the top X reviewers cache to the main stash
mediawiki/core : masterAvoid master queries on GET in ProtectionForm
mediawiki/extensions/LiquidThreads : masterDefer DB writes in SpecialNewMessages
mediawiki/core : masterAvoid master queries in getAutoDeleteReason()
mediawiki/core : masterAvoid DBPerformance log warnings in saveNamespaces()
mediawiki/extensions/AbuseFilter : masterAvoid master queries on page views in loadFilterData()
mediawiki/extensions/CentralAuth : masterAvoid master queries on page view in lazyImportLocalNames()
mediawiki/extensions/CentralAuth : masterAvoid always using DB_MASTER with ?CentralAuthLatest=1
mediawiki/extensions/CentralAuth : masterAvoid incorrectly setting mFromMaster to true
mediawiki/extensions/VisualEditor : masterDefer the user save in onCustomEditor() to post-send
mediawiki/core : masterAvoid master queries on deletion form view
mediawiki/extensions/VisualEditor : masterAvoid master queries on GET requests for blocking
mediawiki/extensions/Translate : masterUse DB_SLAVE in TranslatablePage for GET request
mediawiki/core : masterAvoid master queries in Title::getLinksFrom()
mediawiki/extensions/LiquidThreads : masterAvoid DB master block table queries on view
mediawiki/core : masterMove category refreshes to a deferred update
mediawiki/core : masterAvoid calling Block::deleteIfExpired() when not needed
mediawiki/core : wmf/1.27.0-wmf.8resourceloader: Migrate from msg_resource table to object cache
mediawiki/core : masterresourceloader: Migrate from msg_resource table to object cache
mediawiki/core : masterDefer the redirect table update in WikiPage::insertRedirect()
mediawiki/extensions/CentralAuth : masterAllow for CentralAuthUser::loadState() to generally use DB slaves
mediawiki/extensions/CentralNotice : masterUse DB slaves for Campaign reads on GET requests
mediawiki/extensions/Translate : masterDefer the ApiTranslateUser::trackGroup saveSettings() call
mediawiki/extensions/ContentTranslation : masterDefer the user update in enableCXBetaFeature()
mediawiki/extensions/BetaFeatures : masterDefer the User::saveSettings call in getPreferences()
mediawiki/core : masterAvoid master queries on image history view
mediawiki/extensions/Flow : masterDefer the User::saveSettings() call in TopicListBlock
mediawiki/extensions/Echo : masterAlso defer the markRead() master updates
mediawiki/extensions/PageTriage : masterDefer "lastuse" preference updates
mediawiki/extensions/Echo : masterDefer the "seen time" updates on GET requests
mediawiki/extensions/MobileFrontend : masterDefer User::saveSettings() updates on watchlist view
mediawiki/extensions/UniversalLanguageSelector : masterDefer "language" preference update
mediawiki/core : masterDefault the "watchlisttoken" value to a derived HMAC value
mediawiki/core : masterSet watchlisttoken preference on account creation
mediawiki/extensions/CentralAuth : masterDefer the resetAuthToken() call on logout
mediawiki/core : masterRemoved READ_LATEST from revision load in getMsgFromNamespace()
mediawiki/extensions/CentralAuth : masterSuppress DBPerformance log during account autocreation
mediawiki/extensions/FlaggedRevs : masterRemoved pointless Title::GAID_FOR_UPDATE for altBaseRevId field
mediawiki/extensions/LiquidThreads : masterUse quickUserCan() in showHeader() to avoid master queries
mediawiki/extensions/CentralAuth : masterDefer "remember" preference updates
mediawiki/core : masterIgnore DBPerformance thresholds in Special:ConfirmEmail
mediawiki/extensions/OAI : masterSupress DBPerformance log entries from OAI special page
mediawiki/extensions/Echo : masterDefer clearTalkNotification() updates as they often happen on GET requests
mediawiki/core : masterAvoid using DB_MASTER in SpecialEditWatchlist::getWatchlist()
mediawiki/core : masterDefer spreadAnyEditBlock() post-send
mediawiki/extensions/GettingStarted : masterDefer the saveSettings() call in onMakeGlobalVariablesScript()
mediawiki/core : masterMade User::loadFromId() use READ_NORMAL by default
mediawiki/core : masterMade User generally use DB_SLAVE by default
mediawiki/extensions/TimedMediaHandler : masterReplace prior fix for view-time master manipulation in transcodes
mediawiki/core : REL1_25Moved ActiveUsers updates to recent changes jobs
mediawiki/core : masterMoved ActiveUsers updates to recent changes jobs
mediawiki/extensions/TimedMediaHandler : masterRemoved DB updates in updateJobQueue
mediawiki/extensions/FlaggedRevs : masterAvoid doing DB link updates on page view
mediawiki/extensions/LiquidThreads : masterAvoid constant master ipblocks queries on page views
mediawiki/core : masterMade replaceSectionContent try to use DB_SLAVE
mediawiki/extensions/FlaggedRevs : masterAvoid calling purgeExpiredConfigurations() on page views
mediawiki/core : masterAvoid master queries on SpecialBlockList
mediawiki/core : masterMade getWatchlistInfo use DB_SLAVE
mediawiki/core : masterAvoid DB_MASTER in replaceSectionAtRev()
mediawiki/extensions/CodeReview : masterUse DB_SLAVE in a few places
mediawiki/core : wmf/1.25wmf23Track request method in dbperformance.log
mediawiki/core : wmf/1.25wmf24Track request method in dbperformance.log
mediawiki/core : masterTrack request method in dbperformance.log
mediawiki/core : masterSkin: Account for User::newFromName returning false
mediawiki/core : masterMade Skin::getRelevantUser use READ_NORMAL
mediawiki/extensions/CentralAuth : masterSwitched to using User::touch() to avoid DB write spam
mediawiki/core : masterAvoid using DB_MASTER in makeLikeArray
mediawiki/core : masterRemoved view event from $wgAutopromoteOnce
mediawiki/extensions/MobileFrontend : masterAvoid needless master queries on page views
mediawiki/core : masterIntroduced User::touch() method to bump the getTouched() value using memcached
mediawiki/extensions/ConfirmEdit : masterAvoid master queries on edit form views
mediawiki/core : masterAllow callers to load user objects from slaves if desired
mediawiki/extensions/MobileFrontend : masterAvoid master queries on page view for block checks
mediawiki/core : masterMade getNewtalk use slaves instead of cache/master
mediawiki/core : masterAvoid excess master queries in getTimestampFromId
mediawiki/extensions/LiquidThreads : masterAvoid pointless block table master queries

Related Objects

StatusAssignedTask
Openaaron
OpenNone
Resolvedaude
DuplicateGilles
Duplicateaaron
Resolvedaaron
DuplicateNone
Resolvedhoo
DuplicateNone
DuplicateNone
Resolvedaaron
OpenNone
Resolvedaaron
DuplicateNone
Resolvedaaron
ResolvedNikerabbit
OpenNone
StalledNone
ResolvedKrinkle
ResolvedNikerabbit
Resolvedaaron
DuplicateNone
OpenNone
DuplicateNone
OpenNone
Resolved MarcoAurelio
Resolvedaaron
OpenNone
Resolvedtstarling
ResolvedLadsgroup
ResolvedKrinkle
Duplicateaaron
Resolvedkostajh
ResolvedHuji
Resolvedaaron
Resolvedaaron
Resolvedaaron
OpenNone

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes

Change 292522 merged by jenkins-bot:
Avoid DBPerformance warnings in writeToDatabase()

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

aaron renamed this task from Fix various DB master warnings from dbperformance.log to Fix problematic master queries performed on HTTP GET.Jun 17 2016, 1:37 AM
aaron updated the task description. (Show Details)
aaron renamed this task from Fix problematic master queries performed on HTTP GET to Fix problematic master queries performed on HTTP GET/HEAD.Jun 17 2016, 1:50 AM
aaron renamed this task from Fix problematic master queries performed on HTTP GET/HEAD to Fix problematic database master queries performed on HTTP GET/HEAD.
aaron updated the task description. (Show Details)

Change 297577 had a related patch set uploaded (by Gergő Tisza):
Avoid DBPerformance log warnings in onUserLogout()

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

Change 297577 merged by jenkins-bot:
Avoid DBPerformance log warnings in onUserLogout()

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

Change 299880 had a related patch set uploaded (by Gergő Tisza):
Fix central logout

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

Change 299880 merged by jenkins-bot:
Fix central logout

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

Change 309861 had a related patch set uploaded (by Aaron Schulz):
Avoid page_touched update for HTTP GET action=purge requests

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

Change 309868 had a related patch set uploaded (by Aaron Schulz):
Make SessionBackend::save() update the user post-send

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

Change 309877 had a related patch set uploaded (by Aaron Schulz):
Make CentralAuthUser::register() trigger importLocalNames()

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

Change 309861 merged by jenkins-bot:
Avoid page_touched update for HTTP GET action=purge requests

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

Change 309868 merged by jenkins-bot:
Make SessionBackend::save() update the user post-send

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

Change 309877 merged by jenkins-bot:
Make CentralAuthHooks::onLocalUserCreated() trigger importLocalNames()

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

Change 348030 had a related patch set uploaded (by Aaron Schulz):
[mediawiki/extensions/CentralAuth@master] Make opportunistic password hash upgrades post-send

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

Change 348030 merged by jenkins-bot:
[mediawiki/extensions/CentralAuth@master] Make opportunistic password hash upgrades post-send

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

Change 348658 had a related patch set uploaded (by Aaron Schulz):
[mediawiki/extensions/CentralAuth@master] Avoid triggering master queries in ApiValidatePassword

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

Change 350969 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] Avoid master queries in loadAndLazyInit() for miser mode

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

Change 350969 merged by jenkins-bot:
[mediawiki/core@master] Avoid master queries in loadAndLazyInit() for miser mode

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

Change 351790 had a related patch set uploaded (by Krinkle; owner: Aaron Schulz):
[mediawiki/extensions/CentralAuth@master] Add $flags parameter to renameInProgressOn()

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

Change 351790 merged by jenkins-bot:
[mediawiki/extensions/CentralAuth@master] Add $flags parameter to renameInProgressOn()

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

Change 353823 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/extensions/CentralAuth@master] Avoid master queries in beginSecondaryAuthentication()

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

Change 353824 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/extensions/PageTriage@master] Avoid DB_MASTER queries on HTTP GET in ArticleMetadata->getMetadata

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

Change 353825 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/extensions/CentralAuth@master] Avoid master queries in SpecialGlobalRenameProgress

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

Change 353827 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] Avoid DB_MASTER queries in User::newSystemUser() when possible

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

Change 353829 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/extensions/Translate@master] Use TranslateUtils::getSafeReadDB() in loadAggregateGroups

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

Change 353824 merged by jenkins-bot:
[mediawiki/extensions/PageTriage@master] Avoid DB_MASTER queries on HTTP GET in ArticleMetadata->getMetadata

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

Change 353829 merged by jenkins-bot:
[mediawiki/extensions/Translate@master] Use TranslateUtils::getSafeReadDB() in loadAggregateGroups

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

Change 353825 merged by jenkins-bot:
[mediawiki/extensions/CentralAuth@master] Avoid master queries in SpecialGlobalRenameProgress

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

Change 353827 merged by jenkins-bot:
[mediawiki/core@master] Avoid DB_MASTER queries in User::newSystemUser() when possible

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

Seb35 added a subscriber: Seb35.Jun 3 2017, 6:48 AM

Change 353823 merged by jenkins-bot:
[mediawiki/extensions/CentralAuth@master] Avoid master queries in beginSecondaryAuthentication()

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

Change 348658 merged by jenkins-bot:
[mediawiki/extensions/CentralAuth@master] Avoid triggering master queries in ApiValidatePassword

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

Change 499969 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] Use the main stash for basic user talk page notifications

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

Change 499985 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] Add UserOptionsUpdateJob class and use it for namespaces at SpecialSearch

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

Change 499990 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/extensions/NewUserMessage@master] Use a job for triggering new user talk messages

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

Change 499995 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] [WIP] Avoid Category count refresh DB writes on HTTP GET

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

Change 499990 merged by jenkins-bot:
[mediawiki/extensions/NewUserMessage@master] Use a job for triggering new user talk messages

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

Change 499985 abandoned by Aaron Schulz:
Add UserOptionsUpdateJob class and use it for namespaces at SpecialSearch

Reason:
Taking a different route

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

aaron removed aaron as the assignee of this task.May 21 2019, 9:04 PM
aaron removed a project: Patch-For-Review.