Page MenuHomePhabricator

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

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.

Event Timeline

BTullis triaged this task as High priority.May 21 2024, 4:31 PM

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

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

Change #1043063 merged by Btullis:

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

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

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

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?

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?

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
Marostegui added subscribers: fnegri, taavi.

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)

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.

image.png (205×1 px, 71 KB)

This comment was removed by Marostegui.

@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

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

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 please address T368354 when you can, otherwise we are sort of blind from cumin hosts

@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

@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

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