Page MenuHomePhabricator
Create Task
Maniphest T231520

Replace labsdb (wikireplicas) dbproxies: dbproxy1010 and dbproxy1011
Closed, ResolvedPublic
Actions

Description

wikireplicas service (labsdb1009, labsdb1010, labsdb1011) run behind a pair of proxies: dbproxy1010 and dbproxy1011.

These two proxies were scheduled to be decommissioned January 2016, as they were purchased in 2011.
Finally, we were able to purchase and provision dbproxy1018 and dbproxy1019 as replacements (T202367)

  • dbproxy1010 -> dbproxy1018
  • dbproxy1011 -> dbproxy1019

The replacement should be transparent for the users, as it is a matter of changing the DNS record to point to the new hosts and let the connections move slowly. Once the old proxies are fully empty from connections we can proceed to decommission the old ones.

Details

SubjectRepoBranchLines +/-
wikireplica_dns: Replace dbproxy1010 with dbproxy1018operations/puppetproduction+8 -8
wmnet: Replace dbproxy1011 with dbproxy1019operations/dnsmaster+2 -2
wikireplicas_dns: Replace dbproxy1011 with dbproxy1019operations/puppetproduction+8 -8
dbproxy101: Clarify that it is not an active proxyoperations/puppetproduction+1 -0
wmnet: Replace dbproxy1010 with dbproxy1018operations/dnsmaster+2 -2
Customize query in gerrit

Related Objects
Search...

Event Timeline

• Marostegui created this task.Aug 29 2019, 7:29 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 29 2019, 7:29 AM
• Marostegui triaged this task as Medium priority.Aug 29 2019, 7:29 AM
• Marostegui moved this task from Triage to Pending comment on the DBA board.
• Marostegui updated the task description. (Show Details)
• Marostegui added a parent task: T231280: Remove grants for the old dbproxy hosts from the misc databases.Aug 29 2019, 7:31 AM
gerritbot added a comment.Sep 5 2019, 7:05 AM

Change 534573 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/dns@master] wmnet: Replace dbproxy1010 with dbproxy1018

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

gerritbot added a project: Patch-For-Review.Sep 5 2019, 7:05 AM
• Marostegui added a subscriber: MoritzMuehlenhoff.Sep 5 2019, 7:24 AM

I have been talking to Jaime about this, and we might better wait for https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/531670/ to be pushed so we can start with the new hosts fully configured.

gerritbot added a comment.Sep 5 2019, 7:57 AM

Change 534577 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] wikireplica_dns: Replace dbproxy1010 with dbproxy1018

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

• Marostegui removed • Marostegui as the assignee of this task.Sep 5 2019, 8:07 AM
bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Oct 24 2019, 1:54 PM
• Marostegui added a comment.Mar 25 2020, 6:35 AM

This can now go after merging:
https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/582961/
https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/582960/
https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/582643/
https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/582020/

• Marostegui added a comment.Mar 25 2020, 6:38 AM

Everything seems to be working fine on dbproxy1019 and dbproxy1018 after merging the above changes. Everything is reachable normally and haproxy looks good too,

root@cumin1001:/home/marostegui# telnet dbproxy1019 3306
Trying 10.64.37.28...
Connected to dbproxy1019.eqiad.wmnet.
Escape character is '^]'.
Y
5.5.5-10.1.43-MariaDBGi�fjd9-s!F�??�}e_0o^jw<bWmmysql_native_passwordConnection closed by foreign host.


root@tools-sgebastion-07:~# telnet dbproxy1019.eqiad.wmnet 3306
Trying 10.64.37.28...
Connected to dbproxy1019.eqiad.wmnet.
Escape character is '^]'.
Y
5.5.5-10.1.43-MariaDB&
                      /!XwnA:>�??�hq}Bo*"hjX[Cmysql_native_password

root@dbproxy1019:~# echo "show stat" | socat /run/haproxy/haproxy.sock stdio
# pxname,svname,qcur,qmax,scur,smax,slim,stot,bin,bout,dreq,dresp,ereq,econ,eresp,wretr,wredis,status,weight,act,bck,chkfail,chkdown,lastchg,downtime,qlimit,pid,iid,sid,throttle,lbtot,tracked,type,rate,rate_lim,rate_max,check_status,check_code,check_duration,hrsp_1xx,hrsp_2xx,hrsp_3xx,hrsp_4xx,hrsp_5xx,hrsp_other,hanafail,req_rate,req_rate_max,req_tot,cli_abrt,srv_abrt,comp_in,comp_out,comp_byp,comp_rsp,lastsess,last_chk,last_agt,qtime,ctime,rtime,ttime,agent_status,agent_code,agent_duration,check_desc,agent_desc,check_rise,check_fall,check_health,agent_rise,agent_fall,agent_health,addr,cookie,mode,algo,conn_rate,conn_rate_max,conn_tot,intercepted,dcon,dses,
mariadb,FRONTEND,,,0,0,5000,0,0,0,0,0,0,,,,,OPEN,,,,,,,,,1,2,0,,,,0,0,0,0,,,,,,,,,,,0,0,0,,,0,0,0,0,,,,,,,,,,,,,,,,,,,,,tcp,,0,0,0,,0,0,
mariadb,labsdb1009,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,17,0,,1,2,1,,0,,2,0,,0,L7OK,0,0,,,,,,,,,,,0,0,,,,,-1,5.5.5-10.1.43-MariaDB,,0,0,0,0,,,,Layer7 check passed,,99999999,20,100000018,,,,,,tcp,,,,,,,,
mariadb,labsdb1010,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,17,0,,1,2,2,,0,,2,0,,0,L7OK,0,0,,,,,,,,,,,0,0,,,,,-1,5.5.5-10.1.43-MariaDB,,0,0,0,0,,,,Layer7 check passed,,99999999,20,100000018,,,,,,tcp,,,,,,,,
mariadb,BACKEND,0,0,0,0,500,0,0,0,0,0,,0,0,0,0,UP,2,2,0,,0,17,0,,1,2,0,,0,,1,0,,0,,,,,,,,,,,,,,0,0,0,0,0,0,-1,,,0,0,0,0,,,,,,,,,,,,,,tcp,,,,,,,,
root@cumin1001:/home/marostegui# telnet dbproxy1018 3306
Trying 10.64.37.27...
Connected to dbproxy1018.eqiad.wmnet.
Escape character is '^]'.
Y
5.5.5-10.1.43-MariaDB˅�
k[gs+zI=�??�53=w`^64>{rJmysql_native_password

root@tools-sgebastion-07:~# telnet dbproxy1018.eqiad.wmnet 3306
Trying 10.64.37.27...
Connected to dbproxy1018.eqiad.wmnet.
Escape character is '^]'.
Y
5.5.5-10.1.43-MariaDB���
V]Ras<qE�??�EgZm6cp)xE/Wmysql_native_password

root@dbproxy1018:~#  echo "show stat" | socat /run/haproxy/haproxy.sock stdio
# pxname,svname,qcur,qmax,scur,smax,slim,stot,bin,bout,dreq,dresp,ereq,econ,eresp,wretr,wredis,status,weight,act,bck,chkfail,chkdown,lastchg,downtime,qlimit,pid,iid,sid,throttle,lbtot,tracked,type,rate,rate_lim,rate_max,check_status,check_code,check_duration,hrsp_1xx,hrsp_2xx,hrsp_3xx,hrsp_4xx,hrsp_5xx,hrsp_other,hanafail,req_rate,req_rate_max,req_tot,cli_abrt,srv_abrt,comp_in,comp_out,comp_byp,comp_rsp,lastsess,last_chk,last_agt,qtime,ctime,rtime,ttime,agent_status,agent_code,agent_duration,check_desc,agent_desc,check_rise,check_fall,check_health,agent_rise,agent_fall,agent_health,addr,cookie,mode,algo,conn_rate,conn_rate_max,conn_tot,intercepted,dcon,dses,
mariadb,FRONTEND,,,0,1,5000,5,5,502,0,0,0,,,,,OPEN,,,,,,,,,1,2,0,,,,0,0,0,1,,,,,,,,,,,0,0,0,,,0,0,0,0,,,,,,,,,,,,,,,,,,,,,tcp,,0,1,5,,0,0,
mariadb,labsdb1011,0,0,0,1,,5,5,502,,0,,0,1,0,0,UP,1,1,0,0,0,85113,0,,1,2,1,,5,,2,0,,1,L7OK,0,0,,,,,,,,,,,0,1,,,,,18,5.5.5-10.1.43-MariaDB,,0,0,0,41,,,,Layer7 check passed,,99999999,20,100000018,,,,,,tcp,,,,,,,,
mariadb,labsdb1010,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,0,1,0,0,85113,0,,1,2,2,,0,,2,0,,0,L7OK,0,0,,,,,,,,,,,0,0,,,,,-1,5.5.5-10.1.43-MariaDB,,0,0,0,0,,,,Layer7 check passed,,2,3,4,,,,,,tcp,,,,,,,,
mariadb,BACKEND,0,0,0,1,500,5,5,502,0,0,,0,1,0,0,UP,1,1,1,,0,85113,0,,1,2,0,,5,,1,0,,1,,,,,,,,,,,,,,0,1,0,0,0,0,18,,,0,0,0,41,,,,,,,,,,,,,,tcp,,,,,,,,
Stashbot added a comment.Mar 30 2020, 7:40 AM

Mentioned in SAL (#wikimedia-operations) [2020-03-30T07:40:17Z] <marostegui> Replace dbproxy1010 with dbproxy1011 for wiki replicas, analytics - T231520

gerritbot added a comment.Mar 30 2020, 7:40 AM

Change 534573 merged by Marostegui:
[operations/dns@master] wmnet: Replace dbproxy1010 with dbproxy1018

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

gerritbot added a comment.Mar 30 2020, 7:41 AM

Change 534577 merged by Marostegui:
[operations/puppet@production] wikireplica_dns: Replace dbproxy1010 with dbproxy1018

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

Stashbot added a comment.Mar 30 2020, 7:48 AM

Mentioned in SAL (#wikimedia-operations) [2020-03-30T07:48:16Z] <marostegui> Run cloudcontrol1003:~# wmcs-wikireplica-dns to promote dbproxy1018 to wikireplicas active proxy T231520

• Marostegui added a comment.Mar 30 2020, 8:00 AM

@Bstorm @bd808 I have pushed https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/534577/3/modules/openstack/files/util/wikireplica_dns.yaml and run:

$ ssh cloudcontrol1003.wikimedia.org
$ source <(sudo cat ~root/novaenv.sh)
$ wmcs-wikireplica-dns

This is all I had in my notes to be able to change the DNS to point to a different dbproxy. This change should be transparent for everyone.
I am already seeing:

root@tools-sgebastion-07:~# host s1.analytics.db.svc.eqiad.wmflabs
s1.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27

And also seeing connections arriving thru the new proxy:

root@cumin1001:/home/marostegui# mysql.py -h labsdb1011 -e "show processlist" | grep "37.27"
217150574	s52835	10.64.37.27:49494	frwiki_p	Query	3	Sending data	select page_namespace, ll_title, pa_importance, pa_class\n    from page, langlinks, page_assessments,	0.000
217150602	s52467	10.64.37.27:49528	tlwiki_p	Sleep	0		NULL	0.000
217150603	s52585	10.64.37.27:49530	enwiki_p	Query	0	Sending data	SELECT COUNT(*) as count FROM `page` JOIN `revision` ON `rev_page` = `page_id` WHERE `page_namespace	0.000

I have also run some queries via Quarry and I have seen them arriving correctly to labsdb1011 via the new proxy.

Is this all or should I run something else?

Thanks

gerritbot added a comment.Mar 30 2020, 8:02 AM

Change 584539 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] dbproxy101: Clarify that it is not an active proxy

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

gerritbot added a comment.Mar 30 2020, 8:04 AM

Change 584539 merged by Marostegui:
[operations/puppet@production] dbproxy101: Clarify that it is not an active proxy

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

• Marostegui claimed this task.Mar 30 2020, 8:05 AM
• Marostegui moved this task from Pending comment to In progress on the DBA board.
Maintenance_bot removed a project: Patch-For-Review.Mar 30 2020, 8:11 AM
bd808 added a comment.Mar 30 2020, 6:06 PM
In T231520#6009325, @Marostegui wrote:

I have also run some queries via Quarry and I have seen them arriving correctly to labsdb1011 via the new proxy.

Is this all or should I run something else?

I think that's all that should be needed. The s<number>.analytics... records all seem to have been updated as expected:

$ for n in $(seq 1 8); do host s${n}.analytics.db.svc.eqiad.wmflabs; done
s1.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s2.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s3.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s4.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s5.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s6.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s7.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s8.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
• Marostegui updated the task description. (Show Details)Mar 31 2020, 5:46 AM
• Marostegui added a subtask: T248944: Decommission dbproxy1010.eqiad.wmnet.Mar 31 2020, 5:49 AM
• Marostegui added a comment.Mar 31 2020, 5:55 AM
In T231520#6011841, @bd808 wrote:
In T231520#6009325, @Marostegui wrote:

I have also run some queries via Quarry and I have seen them arriving correctly to labsdb1011 via the new proxy.

Is this all or should I run something else?

I think that's all that should be needed. The s<number>.analytics... records all seem to have been updated as expected:

$ for n in $(seq 1 8); do host s${n}.analytics.db.svc.eqiad.wmflabs; done
s1.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s2.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s3.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s4.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s5.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s6.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s7.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27
s8.analytics.db.svc.eqiad.wmflabs has address 10.64.37.27

Thank you!
Most of connections are already gone, there's just one pending. Once it is finished, I will stop haproxy on dbproxy1010 and leave it stopped for a few days before proceeding with the decommissioning T248944

• Bstorm mentioned this in T249022: Track and list the services that Cloud Services that connect to internal network endpoints.Mar 31 2020, 6:14 PM
Stashbot added a comment.Apr 6 2020, 6:30 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-06T06:30:22Z] <marostegui> Upgrade dbproxy1019 - T231520

gerritbot added a comment.Apr 6 2020, 6:33 AM

Change 586206 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] wikireplicas_dns: Replace dbproxy1011 with dbproxy1019

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

gerritbot added a project: Patch-For-Review.Apr 6 2020, 6:33 AM

Change 586207 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/dns@master] wmnet: Replace dbproxy1011 with dbproxy1019

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

gerritbot added a comment.Apr 7 2020, 7:46 AM

Change 586206 merged by Marostegui:
[operations/puppet@production] wikireplicas_dns: Replace dbproxy1011 with dbproxy1019

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

gerritbot added a comment.Apr 7 2020, 7:46 AM

Change 586207 merged by Marostegui:
[operations/dns@master] wmnet: Replace dbproxy1011 with dbproxy1019

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

Stashbot added a comment.Apr 7 2020, 7:47 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-07T07:47:36Z] <marostegui> Failover dbproxy1011 to dbproxy1019 - T231520)

• Marostegui added a comment.Apr 7 2020, 7:53 AM

I have changed DNS so dbproxy1019 is now becoming the active proxy for the web service:

root@tools-sgebastion-07:~# host s1.web.db.svc.eqiad.wmflabs
s1.web.db.svc.eqiad.wmflabs has address 10.64.37.28
root@tools-sgebastion-07:~# host 10.64.37.28
28.37.64.10.in-addr.arpa domain name pointer dbproxy1019.eqiad.wmnet.

And I already see connections coming from that proxy:

root@cumin1001:~# mysql.py -hlabsdb1009 -e "show processlist" | grep "10.64.37.28"
39443264	s53278	10.64.37.28:51454	enwiki_p	Sleep	30		NULL	0.000
39443396	s53003	10.64.37.28:51866	enwiki_p	Query	0	Copying to tmp table	SET STATEMENT max_statement_time = 600 FOR\nSELECT c.page_namespace, e.page_title, c.page_is_redirect	0.000
39443403	u2815	10.64.37.28:51886	enwiki_p	Sleep	0		NULL	0.000
39443405	s51187	10.64.37.28:51894	enwiki_p	Query	0	Sending data	SET STATEMENT max_statement_time = 60 FOR\nSELECT *, (\n
• Marostegui added a subtask: T249590: decommission dbproxy1011.eqiad.wmnet.Apr 7 2020, 8:03 AM
Maintenance_bot removed a project: Patch-For-Review.Apr 7 2020, 8:10 AM
• Marostegui added a comment.Apr 8 2020, 6:11 AM

Looks like there are no more connections going through dbproxy1011:

root@cumin1001:/home/marostegui# host dbproxy1011
dbproxy1011.eqiad.wmnet has address 10.64.37.15
root@cumin1001:/home/marostegui# for i in labsdb1009 labsdb1010 labsdb1011 labsdb1012; do mysql.py -h$i -e "show processlist" | grep "37.15" ; done
root@cumin1001:/home/marostegui#
Stashbot added a comment.Apr 8 2020, 6:11 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-08T06:11:24Z] <marostegui> Stop haproxy on dbproxy1011 - T231520

• Marostegui added a comment.Apr 8 2020, 6:11 AM

I have stopped haproxy on dbproxy1011

• Marostegui closed this task as Resolved.Apr 14 2020, 12:22 PM
• Marostegui updated the task description. (Show Details)

dbproxy1019 has been working fine for a week. Considering this done.
Next step is to decommission dbproxy1011 : T249590

• Cmjohnson closed subtask T249590: decommission dbproxy1011.eqiad.wmnet as Resolved.May 13 2020, 6:33 PM
• Cmjohnson closed subtask T248944: Decommission dbproxy1010.eqiad.wmnet as Resolved.May 18 2020, 3:41 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