Page MenuHomePhabricator
Create Task
Maniphest T140788

Labs databases rearchitecture (tracking)
Closed, ResolvedPublic
Actions

Description

With the years, there are a lot of technical debt on the way we handle labs databases (not only with the labsdb* hosts themselves, but with general administration of users and policies). This directly impacts the security, performance and availaiblity of databases (specially replicas, but also toolsdb) attending user queries.

In 2016, budget was given to renewal labsdb hosts. This is a meta-ticket to group tasks needed to not only set those up, but to make at the same time all improvements needed to serve better to our users.

Related Objects
Search...

StatusSubtypeAssignedTask
 OpenJdrewniakT128546 [Recurring Task] Update Wikipedia and sister projects portals statistics
 ResolvedKrenairT134017 Create Wikipedia Jamaican
 Resolved• MarosteguiT130128 Fix dbstore2001 and dbstore2002
 ResolvedjcrespoT140788 Labs databases rearchitecture (tracking)
 OpenNoneT140832 Investigate moving labsdb (replicas) user credential management to 'Striker' (codename)
 ResolvedjcrespoT141096 Having lots of accounts with separate grants makes auditing difficult.
 ResolvedjcrespoT141097 Implement a frontend failover solution for labsdb replicas
 ResolvedjcrespoT140452 Initial setup and provision of labsdb1009, labsdb1010 and labsdb1011
 ResolvedjcrespoT149422 Prepare for mariadb 10.1
 ResolvedjcrespoT148500 Implement proxysql both for labs and for later production usage
 Resolved• CmjohnsonT149170 Move dbproxy1010 and dbproxy1011 to labs-support network, rename them to labsdbproxy1001 and labsdbproxy1002
 ResolvedjcrespoT149844 Decide between proxysql and haproxy for labsdbproxy service
 ResolvedcorenT50875 Unable to explain queries on replicated databases
 DeclinedNoneT141095 Explore 'Analyze' statement as substitute for Explain
Restricted Task
 Resolved• srodlundT85868 Document labsdb replication set up
Restricted Task
 ResolvedyuvipandaT104453 replica.my.cnf creation broken
 ResolvedcorenT104476 Provision a labsdb useraccount that can be used to run replica-addusers.pl
 Resolved• chasempT147051 Provide at least 2 separate service endpoints: one for slow, long running queries; and another for quick, web requests
 Resolved• chasempT147052 Provision with data the new labsdb servers and provide replica service with at least 1 shard from a sanitized copy from production
 Resolved• MarosteguiT146261 Reimage dbstore2001 as jessie
 Resolved• MarosteguiT149457 install new disks into dbstore2001
 Resolved• MarosteguiT150017 Reimage dbstore2002
 Resolved• madhuvishyT149933 Migrate existing labs users from the old servers, if possible using roles and start maintaining users on the new database servers, too
 ResolvedjcrespoT152377 Create labsdb_accounts db on m5 to store state about labsdb accounts
 ResolvedjcrespoT152194 Provision sanitized data on labsdb1009, labsdb1010, labsdb1011 with from db1095
Resolved• MarosteguiT150802 Provision db1095 with at least 1 shard, sanitize and test slave-side triggers
 Resolved• MarosteguiT150960 Initial data tests for db1095 (temporary db1069 - sanitarium replacement)
 ResolvedNoneT151607 Rebuild old timestamp format tables
 ResolvedjcrespoT149829 Move labsdb1008 to production, rename it back to db1095, use it as a temporary sanitarium
 Resolved• CmjohnsonT150793 relabel labsdb1008 to db1095, update racktables
 ResolvedjcrespoT153058 LabsDB infrastructure pending work
 Resolved• MarosteguiT153680 Create a cronjob/check to run check_private_data data script and report back
 Resolved• MarosteguiT149418 Deploy gtid_domain_id flag in our mysql hosts
 StalledNoneT59617 Make watchlist table available as curated foo_p.watchlist_count on labsdb
 DuplicateNoneT93887 Replicate watchlist to labs
 DuplicateNoneT106089 Replicate sanitized watchlist table
 ResolvedoriT135029 adywiki and jamwiki are missing the associated *_p databases with appropriate views
Restricted Task
 Resolved• chasempT138450 maintain-replicas.pl unmaintained, unmaintainable
 Resolved• MarosteguiT153743 Add and sanitize s2, s4, s5, s6 and s7 to sanitarium2 and new labsdb hosts
 Resolved• MarosteguiT157931 s5: db1070 not using file per table
 Resolved• MarosteguiT168021 setup dewiki and wikidatawiki on the labsdb1009, 1010 and 1011
 DeclinedNoneT156869 Design a method for keeping user-created tables in sync across labsDBs
 Resolved• MarosteguiT172169 Compress InnoDB on db1102
 ResolvedjcrespoT166546 Point labsdb1001 and labsdb1003 to db1095 and db1102
 Resolved• MarosteguiT172996 Migrate s4 from db1095 to db1102
 ResolvedjcrespoT182450 db1102 (sanitarium) filling up (WAS: Clean up old binlogs from db1102 (sanitarium multi-instance))
 Resolvedbd808T142807 Migrate all users to new Wiki Replica cluster and decommission old hardware
 Resolvedbd808T172704 Promote initial use of new Wiki Replica servers
 Resolvedbd808T174860 Define naming scheme for connecting to new wiki replica cluster
 Resolved• madhuvishyT168584 Labsdb* servers need to be rebooted
 Resolvedbd808T175086 Create and announce timeline for shutting down labsdb100[13]
 Resolvedbd808T175096 Identify tools hosting databases on labsdb100[13] and notify maintainers
 Resolvedbd808T176688 Update `sql` command to use new wiki replica servers
 Resolvedzhuyifei1999T176694 Switch Quarry to use *.analytics.db.svc.eqiad.wmflabs as replica database host
 OpenNoneT176886 Update meta_p database for new service names
 ResolvedjcrespoT177096 Some queries to new replica hosts are dramatically slower than labsdb; missing indexes?
 Resolvedbd808T177223 Determine schema differences between labsdb1001 and labsdb1009
 ResolvederanrozT179227 Migrate copypatrol & plagiabot to use tools.labsdb
 Resolved• MarosteguiT179464 labsdb1001 crashed - storage issue
 DeclinedNoneT179628 Consider granting `CREATE TEMPORARY TABLES` to labsdbuser
 OpenNoneT180558 Include namespace IDs and their names to mysql wikireplicas (meta_p database)
 DeclinedNoneT180636 Make Dispenser's principle_links table accessible in new Wiki replica cluster
 OpenFeatureNoneT173511 Implement technical details and process for "datasets_p" on wikireplica hosts
 DeclinedNoneT173512 Create a phabricator project called "wikireplica-datasets"
 DuplicateNoneT173513 Create a database on the wikireplica servers called "datasets_p"
 DuplicateNoneT173514 Document the process for importing a new "datasets_p" table
 Resolvedzhuyifei1999T181492 sql command should point to the new labsdb servers
 DeclinedNoneT182948 Create method for accessing user watchlists in database queries
 Resolved• madhuvishyT183029 Stop managing account creation for labsdb1001 and 1003 through the maintain-dbusers script
 ResolvedjcrespoT186585 Review m5 backups
 Resolvedbd808T183651 centralauth database service name missing from new replicas
 Resolved• MarosteguiT183758 Create backups of user tables from decommissioned database servers
 Resolved• BanyekT183983 Re-institute query killer for the analytics WikiReplica
 Resolved• BanyekT203674 Debian package or files managed my puppet for pt-kill-wmf
 Resolved• CmjohnsonT184832 Decommission labsdb1001 and labsdb1003

Event Timeline

jcrespo created this task.Jul 19 2016, 4:33 PM
Restricted Application added a project: Cloud-Services. · View Herald TranscriptJul 19 2016, 4:33 PM
Restricted Application added subscribers: Zppix, Aklapper. · View Herald Transcript
jcrespo added a project: Epic.Jul 19 2016, 4:34 PM
jcrespo added a subtask: T140452: Initial setup and provision of labsdb1009, labsdb1010 and labsdb1011.
• chasemp added a comment.Jul 19 2016, 9:15 PM

https://phabricator.wikimedia.org/P3514

• chasemp created subtask T140832: Investigate moving labsdb (replicas) user credential management to 'Striker' (codename).Jul 19 2016, 9:29 PM
Danny_B renamed this task from Labs databases rearchitecture to Labs databases rearchitecture (tracking).Jul 19 2016, 9:37 PM
Danny_B moved this task from Tag to Should be Epic instead on the Tracking-Neverending board.
Danny_B subscribed.

This should actually be rather goal (have its own orange tag) instead of tracking task.

• chasemp created subtask T141095: Explore 'Analyze' statement as substitute for Explain.Jul 22 2016, 1:32 PM
• chasemp created subtask T141096: Having lots of accounts with separate grants makes auditing difficult..Jul 22 2016, 1:36 PM
• chasemp created subtask T141097: Implement a frontend failover solution for labsdb replicas.Jul 22 2016, 2:00 PM
• chasemp triaged this task as Medium priority.Jul 25 2016, 2:04 PM
zhuyifei1999 edited subtasks, added: T50875: Unable to explain queries on replicated databases; removed: T141095: Explore 'Analyze' statement as substitute for Explain.Jul 28 2016, 6:33 PM
• chasemp mentioned this in T141095: Explore 'Analyze' statement as substitute for Explain.Aug 1 2016, 2:19 PM
• chasemp added a subtask: T141095: Explore 'Analyze' statement as substitute for Explain.
• chasemp reopened subtask T141095: Explore 'Analyze' statement as substitute for Explain as Open.
jcrespo removed a subtask: T140452: Initial setup and provision of labsdb1009, labsdb1010 and labsdb1011.Aug 12 2016, 7:27 AM
jcrespo added a subtask: Restricted Task.Sep 20 2016, 3:25 PM
jcrespo mentioned this in T146718: [Discuss] Hosting the monthly article quality dataset on labsDB.Sep 28 2016, 3:40 PM
• Marostegui created subtask T147051: Provide at least 2 separate service endpoints: one for slow, long running queries; and another for quick, web requests.Sep 30 2016, 9:39 AM
• Marostegui created subtask T147052: Provision with data the new labsdb servers and provide replica service with at least 1 shard from a sanitized copy from production.
• chasemp mentioned this in T140452: Initial setup and provision of labsdb1009, labsdb1010 and labsdb1011.Oct 17 2016, 3:11 PM
jcrespo mentioned this in T146444: Improve mediawiki data redaction.Oct 18 2016, 7:23 AM
jcrespo added a parent task: T142807: Migrate all users to new Wiki Replica cluster and decommission old hardware.Oct 28 2016, 1:44 PM
jcrespo created subtask T149829: Move labsdb1008 to production, rename it back to db1095, use it as a temporary sanitarium.Nov 2 2016, 6:52 PM
jcrespo moved this task from Triage to Meta/Epic on the DBA board.Nov 10 2016, 12:10 PM
jcrespo mentioned this in T150759: s51412__data reverted to last year state.Nov 15 2016, 12:18 PM
jcrespo closed subtask T149829: Move labsdb1008 to production, rename it back to db1095, use it as a temporary sanitarium as Resolved.Nov 15 2016, 9:01 PM
scfc closed subtask T50875: Unable to explain queries on replicated databases as Resolved.Dec 4 2016, 10:30 PM
• chasemp closed subtask T147051: Provide at least 2 separate service endpoints: one for slow, long running queries; and another for quick, web requests as Resolved.Dec 21 2016, 4:39 PM
• chasemp closed subtask T147052: Provision with data the new labsdb servers and provide replica service with at least 1 shard from a sanitized copy from production as Resolved.
• chasemp closed subtask T141097: Implement a frontend failover solution for labsdb replicas as Resolved.Dec 21 2016, 4:43 PM
jcrespo added a subtask: T153058: LabsDB infrastructure pending work.Dec 21 2016, 5:35 PM
tom29739 subscribed.Jan 31 2017, 10:43 PM
jcrespo mentioned this in T149624: Labsdbs for WMF tools and contributors: get more data, faster.Feb 14 2017, 3:08 PM
jcrespo closed subtask T141096: Having lots of accounts with separate grants makes auditing difficult. as Resolved.
jcrespo mentioned this in T159493: Data integrity issue with enwiki_p user_groups on Wikimedia Tool Labs (missing rows).Mar 5 2017, 12:19 PM
• MZMcBride subscribed.Mar 5 2017, 8:37 PM
jcrespo mentioned this in T165705: No data after 20170517193000 available via Quarry from tables (recentchanges, revision, logging) for several Mediawiki databases (svwiki_p, fiwiki_p, nowiki_p, ...) .May 19 2017, 9:48 AM
• Phabricator_maintenance removed a subscriber: yuvipanda.Jun 7 2017, 6:41 PM
bd808 moved this task from Triage to Tracking on the Cloud-Services board.Jun 8 2017, 4:30 AM
Liuxinyu970226 subscribed.Aug 7 2017, 1:23 AM
jcrespo added a subtask: T172704: Promote initial use of new Wiki Replica servers.Aug 7 2017, 3:58 PM
jcrespo closed subtask Restricted Task as Resolved.Aug 31 2017, 1:55 PM
bd808 mentioned this in Blog Post: New Wiki Replica servers ready for use.Sep 1 2017, 12:58 AM
bd808 removed a parent task: T142807: Migrate all users to new Wiki Replica cluster and decommission old hardware.Sep 5 2017, 8:03 PM
bd808 added a subtask: T142807: Migrate all users to new Wiki Replica cluster and decommission old hardware.
bd808 removed a subtask: T172704: Promote initial use of new Wiki Replica servers.
jcrespo mentioned this in T153058: LabsDB infrastructure pending work.Mar 26 2018, 5:46 PM
jcrespo closed this task as Resolved.Mar 26 2018, 7:50 PM
jcrespo claimed this task.

From the DBA side, labsdb rearchitecture with the new servers is completed, only thing left is this quarter's goal T142807. Closing as resolved. Of course, this doesn't mean labsdb is fixed, lots of things are pending, but the initial scope of fixing the most blatant issues is done.

jcrespo closed subtask T153058: LabsDB infrastructure pending work as Resolved.Mar 26 2018, 7:51 PM
Liuxinyu970226 unsubscribed.Mar 27 2018, 6:49 AM
bd808 closed subtask T142807: Migrate all users to new Wiki Replica cluster and decommission old hardware as Resolved.Jun 29 2018, 9:10 PM
• Marostegui closed subtask T141095: Explore 'Analyze' statement as substitute for Explain as Declined.Oct 16 2018, 12:36 PM
Liuxinyu970226 moved this task from Should be Epic instead to Transition completed / Archived on the Tracking-Neverending board.Oct 27 2018, 11:49 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL