Bring an-redacteddb1001 into service to replace clouddb1021
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	BTullis
	May 21 2024, 9:58 AM

Description

an-redacteddb1001 is the replacement for clouddb1021

It is the custom analytics instance of the wikireplicas, which contains a redacted copy of every section of the core MariaDB mediawiki databases.

It is used for monthly sqoop jobs and ad-hoc analytics queries, principally by the Data-Engineering team.

The size of the databases on clouddb1021 is around 12 TB, so we will need to transfer these and set up replication so that service can be switched from the old host to the new host relatively quickly.

Details

Related Changes in Gerrit:

Subject	Repo	Branch	Lines +/-
Remove references to clouddb1021 once the host has been decommissioned	operations/puppet	production	+2 -3
Add an-redacteddb to list of hosts that do not get IPv6 records	operations/software/netbox-extras	master	+1 -0
Add an-redacteddb1001 to the mysql eqiad cluster	operations/puppet	production	+1 -1
Switch to using the new redacted replica MariaDB server	analytics/refinery	master	+12 -12
Enable monitoring for an-redacteddb1001	operations/puppet	production	+0 -3
Configure check_private_data_report for an-redacteddb1001	operations/puppet	production	+2 -1
Switch the role for an-redacteddb1001 from insetup	operations/puppet	production	+18 -1

Customize query in gerrit

Related Objects
Search...

Status	Assigned	Task
		Unknown Object (Task)
Resolved	BTullis	T355571 Q#:rack/setup/install an-redacteddb1001
Resolved	BTullis	T365453 Bring an-redacteddb1001 into service to replace clouddb1021
Resolved	Marostegui	T367851 Include an-redacteddb1001 in zarcillo
Resolved	BTullis	T368220 Remove AAAA records from an-redacteddb1001 and allow connection from cumin
Resolved	BTullis	T368354 Modify db-mysql to connect to an-redacteddb1001 from cumin hosts

Event Timeline

BTullis created this task.May 21 2024, 9:58 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 21 2024, 9:58 AM

Marking this as high-priority, since /srv on clouddb1021 is already at 81% full, as mentioned at: T355571#9794965

Gehel moved this task from Incoming to Hardware refresh on the Data-Platform-SRE board.May 24 2024, 8:46 AM

BTullis edited projects, added Data-Platform-SRE (2024.05.27 - 2024.06.16); removed Data-Platform-SRE.May 24 2024, 3:18 PM

BTullis moved this task from Backlog to In Progress on the Data-Platform-SRE (2024.05.27 - 2024.06.16) board.Jun 4 2024, 11:02 AM

BTullis moved this task from In Progress to Blocked / Waiting on the Data-Platform-SRE (2024.05.27 - 2024.06.16) board.Jun 4 2024, 12:10 PM

Change #1043063 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Switch the role for an-redacteddb1001 from insetup

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

gerritbot added a project: Patch-For-Review.Jun 13 2024, 1:27 PM

Change #1043063 merged by Btullis:

[operations/puppet@production] Switch the role for an-redacteddb1001 from insetup

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

Maintenance_bot removed a project: Patch-For-Review.Jun 13 2024, 2:32 PM

The /srv volume on an-redacteddb1001 is a little small at the moment. I will resize it to 17 TB.

btullis@an-redacteddb1001:~$ lsblk
NAME          MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTS
sda             8:0    0 17.5T  0 disk 
├─sda1          8:1    0 37.3G  0 part /
├─sda2          8:2    0  7.5G  0 part [SWAP]
└─sda3          8:3    0 17.4T  0 part 
  └─tank-data 254:0    0  9.1T  0 lvm  /srv

btullis@an-redacteddb1001:~$ sudo lvresize -L 17T tank/data
  Size of logical volume tank/data changed from 9.09 TiB (2384188 extents) to 17.00 TiB (4456448 extents).
  Logical volume tank/data successfully resized.

btullis@an-redacteddb1001:~$ sudo xfs_growfs /srv
meta-data=/dev/mapper/tank-data  isize=512    agcount=32, agsize=76294016 blks
         =                       sectsz=512   attr=2, projid32bit=1
         =                       crc=1        finobt=1, sparse=1, rmapbt=0
         =                       reflink=1    bigtime=1 inobtcount=1 nrext64=0
data     =                       bsize=4096   blocks=2441408512, imaxpct=5
         =                       sunit=64     swidth=256 blks
naming   =version 2              bsize=4096   ascii-ci=0, ftype=1
log      =internal log           bsize=4096   blocks=521728, version=2
         =                       sectsz=512   sunit=64 blks, lazy-count=1
realtime =none                   extsz=4096   blocks=0, rtextents=0
data blocks changed from 2441408512 to 4563402752

btullis@an-redacteddb1001:~$ df -h /srv
Filesystem             Size  Used Avail Use% Mounted on
/dev/mapper/tank-data   17T  122G   17T   1% /srv

Much better.

I'm starting a transfer of s1 from clouddb1021 to an-redacteddb1001 with the following command:

sudo transfer.py --type xtrabackup --verbose clouddb1021.eqiad.wmnet:/run/mysqld/mysqld.s1.sock an-redacteddb1001.eqiad.wmnet:/srv/sqldata.s1 | tee T365453.s1.log

Replication is not stopped on clouddb1021 during the transfer, so this won't be the quickest transfer, but hopefully it will not be distruptive.

Mentioned in SAL (#wikimedia-analytics) [2024-06-13T16:28:58Z] <btullis> started copying sqldata.s1 from clouddbd1021 to an-redacteddb1001 for T365453

BTullis mentioned this in T365450: Upgrade clouddb1021 to bookworm.Jun 13 2024, 4:36 PM

BTullis moved this task from Blocked / Waiting to In Progress on the Data-Platform-SRE (2024.05.27 - 2024.06.16) board.

We need to make sure that the data checks (for PII leaks) get also executed on this new host. Is it having the same puppet role as clouddb1021?

In T365453#9890126, @Marostegui wrote:

We need to make sure that the data checks (for PII leaks) get also executed on this new host. Is it having the same puppet role as clouddb1021?

Thanks. Yes, this will have the same role as clouddb1021, so it already has profile::mariadb::check_private_data applied.

btullis@cumin1002:~$ sudo cumin P:mariadb::check_private_data
14 hosts will be targeted:
an-redacteddb1001.eqiad.wmnet,clouddb[1013-1021].eqiad.wmnet,db[2186-2187].codfw.wmnet,db[1154-1155].eqiad.wmnet
DRY-RUN mode enabled, aborting
btullis@cumin1002:~$

My initial plan to is take the backup for each section directly from clouddb1021, then configure replication from the same upstream replica that clouddb1021 uses.
However, I am aware that streaming backups directly is not recommended (https://wikitech.wikimedia.org/wiki/MariaDB/Backups#Copy_data_from_a_backup_source_to_a_host) so I am open to better suggestions if you have any.

I looked on the dashboard but I couldn't see any backups of the sanitized databases, from which I could recover using the preferred method.
My first xtrabackup of s1 failed after transferring 357 GB with:

[11] 2024-06-13 16:32:44 Streaming ./enwiki/oldimage.ibd
xtrabackup: Error writing file 'UNKNOWN' (errno: 32 "Broken pipe")
[00] 2024-06-13 16:32:45 xb_stream_write_data() failed.
xtrabackup: Error writing file 'UNKNOWN' (errno: 32 "Broken pipe")
[03] 2024-06-13 16:32:45 mariabackup: xtrabackup_copy_datafile() failed.
[00] FATAL ERROR: 2024-06-13 16:32:45 failed to copy datafile.

Should I simply empty the target directory and restart this backup, or is there a better way, in your opinion?

Gehel edited projects, added Data-Platform-SRE (2024.06.17 - 2024.07.07); removed Data-Platform-SRE (2024.05.27 - 2024.06.16).Jun 17 2024, 3:04 PM

Gehel moved this task from Backlog to In Progress on the Data-Platform-SRE (2024.06.17 - 2024.07.07) board.

That backup completed successfully. I have now carried out the prepare operation with the following command:

btullis@an-redacteddb1001:/srv/sqldata.s1$ sudo xtrabackup --prepare --use-memory=300GB --target-dir=.
[00] 2024-06-17 15:14:33 cd to /srv/sqldata.s1/
[00] 2024-06-17 15:14:33 open files limit requested 0, set to 1024
[00] 2024-06-17 15:14:33 This target seems to be not prepared yet.
[00] 2024-06-17 15:14:33 mariabackup: using the following InnoDB configuration for recovery:
[00] 2024-06-17 15:14:33 innodb_data_home_dir = .
[00] 2024-06-17 15:14:33 innodb_data_file_path = ibdata1:12M:autoextend
[00] 2024-06-17 15:14:33 innodb_log_group_home_dir = .
[00] 2024-06-17 15:14:33 InnoDB: Using liburing
[00] 2024-06-17 15:14:33 Starting InnoDB instance for recovery.
[00] 2024-06-17 15:14:33 mariabackup: Using 322122547200 bytes for buffer pool (set by --use-memory parameter)
2024-06-17 15:14:33 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2024-06-17 15:14:33 0 [Note] InnoDB: Number of pools: 1
2024-06-17 15:14:33 0 [Note] InnoDB: Using AVX512 instructions
2024-06-17 15:14:33 0 [Note] InnoDB: Using liburing
2024-06-17 15:14:33 0 [Note] InnoDB: Initializing buffer pool, total size = 322122547200, chunk size = 322122547200
2024-06-17 15:14:34 0 [Note] InnoDB: Completed initialization of buffer pool
2024-06-17 15:14:34 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=112119341280032,112119726834903
2024-06-17 15:14:49 0 [Note] InnoDB: Read redo log up to LSN=112122867837440
2024-06-17 15:14:51 0 [Note] InnoDB: To recover: 962001 pages
2024-06-17 15:15:06 0 [Note] InnoDB: To recover: 962001 pages
2024-06-17 15:15:22 0 [Note] InnoDB: Last binlog file './db1154-bin.000007', position 699704659
[00] 2024-06-17 15:15:22 Last binlog file ./db1154-bin.000007, position 699704659
[00] 2024-06-17 15:15:22 completed OK!

I used the following commands to start replication:

CHANGE MASTER to MASTER_HOST='db1154.eqiad.wmnet', MASTER_USER='repl2024', MASTER_PASSWORD='redacted', MASTER_PORT=3311,MASTER_SSL=1;

SET GLOBAL gtid_slave_pos = '0-171970637-5484646134,171966471-171966471-62,171970572-171970572-3935877275,171970637-171970637-2116621969,171970661-171970661-3655324752,171970704-171970704-351094624,171970745-171970745-3776405226,171974720-171974720-2572451842,171974884-171974884-1473084269,171978768-171978768-202416,171978774-171978774-5,171978777-171978777-514400352,171978826-171978826-1025218266,172000011-172000011-27,180355171-180355171-148310907,180355190-180355190-1378262411,180359172-180359172-49702203,180359179-180359179-96523837,180363268-180363268-3447080256';

CHANGE MASTER TO master_use_gtid = slave_pos;

START SLAVE;

Now we have the following shown from SHOW SLAVE STATUS \G;

     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
Seconds_Behind_Master: 8570

I will move onto the next section.

Started the backup of s2 with the following on cumin1002.

btullis@cumin1002:~$ sudo transfer.py --type xtrabackup --verbose clouddb1021.eqiad.wmnet:/run/mysqld/mysqld.s2.sock an-redacteddb1001.eqiad.wmnet:/srv/sqldata.s2 | tee T365453.s2.log

Thanks @BTullis - if this host is going to be replacing clouddb1021 we need to update the documentation accordingly as there're operations that are happening in clouddb1021 that should keep happening on this host (eg: views creation etc)

We also need to include this host in zarcillo (I will do that)

In T365453#9902189, @Marostegui wrote:

We also need to include this host in zarcillo (I will do that)

Thanks @Marostegui, that would be great.
So far, I have successfully transferred sections 1-3 and s4 is copying across at the moment.

Marostegui closed this task as Resolved.Jun 19 2024, 9:52 AM

This comment was removed by Marostegui.

Sorry wrong task

Marostegui closed subtask T367851: Include an-redacteddb1001 in zarcillo as Resolved.Jun 19 2024, 9:53 AM

@BTullis can you double check why an-redacteddb1001 isn't having check_private_data runs every day like clouddb1021 has? I detected it because it doesn't have the logs:

root@clouddb1021:/var/log#  find . | grep private_data
./private_data_report_clouddb1021.log_s8
./private_data_report_clouddb1021.log_s5
./private_data_report_clouddb1021.log_s3
./private_data_report_clouddb1021.log_s1
./private_data_report_clouddb1021.log_s6
./private_data_report_clouddb1021.log_s2
./private_data_report_clouddb1021.log_s4
./private_data_report_clouddb1021.log_s7



root@an-redacteddb1001:/var/log# find . | grep private_data
root@an-redacteddb1001:/var/log#

@Marostegui - yes, I will look into it. I can see that the timer is firing and the service reports success.

btullis@an-redacteddb1001:~$ journalctl -u check-private-data.service
Jun 14 05:00:01 an-redacteddb1001 systemd[1]: Starting check-private-data.service - Regular jobs for checking and reporting private data...
Jun 14 05:00:01 an-redacteddb1001 systemd[1]: check-private-data.service: Deactivated successfully.
Jun 14 05:00:01 an-redacteddb1001 systemd[1]: Finished check-private-data.service - Regular jobs for checking and reporting private data.
Jun 15 05:00:05 an-redacteddb1001 systemd[1]: Starting check-private-data.service - Regular jobs for checking and reporting private data...
Jun 15 05:00:05 an-redacteddb1001 systemd[1]: check-private-data.service: Deactivated successfully.
Jun 15 05:00:05 an-redacteddb1001 systemd[1]: Finished check-private-data.service - Regular jobs for checking and reporting private data.
Jun 16 05:00:05 an-redacteddb1001 systemd[1]: Starting check-private-data.service - Regular jobs for checking and reporting private data...
Jun 16 05:00:05 an-redacteddb1001 systemd[1]: check-private-data.service: Deactivated successfully.
Jun 16 05:00:05 an-redacteddb1001 systemd[1]: Finished check-private-data.service - Regular jobs for checking and reporting private data.
Jun 17 05:00:02 an-redacteddb1001 systemd[1]: Starting check-private-data.service - Regular jobs for checking and reporting private data...
Jun 17 05:00:02 an-redacteddb1001 systemd[1]: check-private-data.service: Deactivated successfully.
Jun 17 05:00:02 an-redacteddb1001 systemd[1]: Finished check-private-data.service - Regular jobs for checking and reporting private data.
Jun 18 05:00:05 an-redacteddb1001 systemd[1]: Starting check-private-data.service - Regular jobs for checking and reporting private data...
Jun 18 05:00:05 an-redacteddb1001 systemd[1]: check-private-data.service: Deactivated successfully.
Jun 18 05:00:05 an-redacteddb1001 systemd[1]: Finished check-private-data.service - Regular jobs for checking and reporting private data.
Jun 19 05:00:03 an-redacteddb1001 systemd[1]: Starting check-private-data.service - Regular jobs for checking and reporting private data...
Jun 19 05:00:03 an-redacteddb1001 systemd[1]: check-private-data.service: Deactivated successfully.
Jun 19 05:00:03 an-redacteddb1001 systemd[1]: Finished check-private-data.service - Regular jobs for checking and reporting private data.
Jun 20 05:00:05 an-redacteddb1001 systemd[1]: Starting check-private-data.service - Regular jobs for checking and reporting private data...
Jun 20 05:00:05 an-redacteddb1001 systemd[1]: check-private-data.service: Deactivated successfully.
Jun 20 05:00:05 an-redacteddb1001 systemd[1]: Finished check-private-data.service - Regular jobs for checking and reporting private data.
Jun 21 05:00:00 an-redacteddb1001 systemd[1]: Starting check-private-data.service - Regular jobs for checking and reporting private data...
Jun 21 05:00:00 an-redacteddb1001 systemd[1]: check-private-data.service: Deactivated successfully.
Jun 21 05:00:00 an-redacteddb1001 systemd[1]: Finished check-private-data.service - Regular jobs for checking and reporting private data.

...but I will check why there are no log files being created.

Change #1048388 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Configure check_private_data_report for an-redacteddb1001

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

gerritbot added a project: Patch-For-Review.Jun 21 2024, 9:46 AM

Change #1048390 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] [WIP] Remove references to clouddb1021 once the host has been decommissioned

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

Change #1048388 merged by Btullis:

[operations/puppet@production] Configure check_private_data_report for an-redacteddb1001

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

Change #1048422 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Enable monitoring for an-redacteddb1001

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

Change #1048422 merged by Btullis:

[operations/puppet@production] Enable monitoring for an-redacteddb1001

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

Change #1048428 had a related patch set uploaded (by Btullis; author: Btullis):

[analytics/refinery@master] Switch to using the new redacted replica MariaDB server

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

BTullis closed subtask T368220: Remove AAAA records from an-redacteddb1001 and allow connection from cumin as Resolved.Jun 24 2024, 11:20 AM

Change #1048428 merged by Btullis:

[analytics/refinery@master] Switch to using the new redacted replica MariaDB server

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

That patch to refinery is merged, so we start using clouddb1021 during the next sqoop run. I will prepare clouddb1021 for decommissioning, but make sure to do so slowly, so that we hopefully don't get any surprises.

BTullis moved this task from In Progress to To Be Deployed on the Data-Platform-SRE (2024.06.17 - 2024.07.07) board.Jun 24 2024, 2:05 PM

BTullis closed this task as Resolved.Jun 27 2024, 9:03 AM

BTullis moved this task from To Be Deployed to Done on the Data-Platform-SRE (2024.06.17 - 2024.07.07) board.

@BTullis please address T368354 when you can, otherwise we are sort of blind from cumin hosts

BTullis closed subtask T368354: Modify db-mysql to connect to an-redacteddb1001 from cumin hosts as Resolved.Jul 8 2024, 9:55 AM

@BTullis we probably need to add an-redacteddb1001 to hieradata/regex.yaml somewhere.

Change #1054275 had a related patch set uploaded (by Btullis; author: Btullis):

[operations/puppet@production] Add an-redacteddb1001 to the mysql eqiad cluster

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

Change #1054275 merged by Btullis:

[operations/puppet@production] Add an-redacteddb1001 to the mysql eqiad cluster

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

In T365453#9980171, @Marostegui wrote:

@BTullis we probably need to add an-redacteddb1001 to hieradata/regex.yaml somewhere.

Thanks. I hadn't spotted that.

Change #1056892 had a related patch set uploaded (by Cathal Mooney; author: Cathal Mooney):

[operations/software/netbox-extras@master] Add an-redacteddb to list of hosts that do not get IPv6 records

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

Change #1048390 merged by Btullis:

[operations/puppet@production] Remove references to clouddb1021 once the host has been decommissioned

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

BTullis mentioned this in T371991: Investigate MariaDB grant issues with the btmwiki database on an-redacteddb1001.Aug 7 2024, 4:54 PM

Change #1056892 merged by jenkins-bot:

[operations/software/netbox-extras@master] Add an-redacteddb to list of hosts that do not get IPv6 records

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

jenkins-bot mentioned this in rOSNEb8d96b33ad5f: Add an-redacteddb to list of hosts that do not get IPv6 records.Aug 13 2024, 7:45 AM

Maintenance_bot removed a project: Patch-For-Review.Aug 13 2024, 8:30 AM

	F55463406: image.png
	Jun 19 2024, 9:27 AM

Bring an-redacteddb1001 into service to replace clouddb1021Closed, ResolvedPublicActions

Description

Details

Related ObjectsSearch...

Event Timeline

Bring an-redacteddb1001 into service to replace clouddb1021
Closed, ResolvedPublic
Actions

Related Objects
Search...