mailman3web will have the emails

That is more concerning, not because it is not doable, but because with attachments, the other database storing organization's emails on a database (OTRS) is the largest database we host.

It would be nice to have an idea of projected size even if that means asking other organizations that have migrated already or doing a test migration. We may need to purchase dedicated hardware- I don't like the idea of storing a lot of meaningful data on a VM for practical reasons (backups, recovery, ...).

In T256538#6262958, @Marostegui wrote:

@herron any idea how big these DBs can be and how many writes we'd be expecting?
Which grants would be needed?

I would assume we do need backups, right?

I'm not sure off-hand about the specifics of the newer version, but can say that the current lists archives footprint is in the 175G ballpark, and would want it backed up yes.

In T256538#6265626, @herron wrote:

In T256538#6262958, @Marostegui wrote:

@herron any idea how big these DBs can be and how many writes we'd be expecting?
Which grants would be needed?

I would assume we do need backups, right?

I'm not sure off-hand about the specifics of the newer version, but can say that the current lists archives footprint is in the 175G ballpark, and would want it backed up yes.

That's a pretty big database and I do share Jaime's concerns.
I would definitely not store that on m2 as it already has OTRS which is around 500GB already. If we had to store this in our misc infra, I guess m1 is in better shape on that regards.

Here are the largest ones as requested by Ladsgroup on T262773

10G	helpdesk-l
6.1G	reading-web-team.mbox
6.1G	arbcom-l
5.5G	wmfall
4.8G	reading-web-team
3.8G	wikidata-bugs
3.1G	wmfall.mbox
3.0G	qa-alerts
2.9G	wmfsf
2.4G	arbcom-l.mbox
2.1G	wikimedia-in-exec
2.0G	ops
2.0G	blog-admin
1.9G	wikimedia-l
1.8G	wmfsf.mbox
1.7G	wikinews-pl
1.7G	pressemeldungen
1.6G	wikimediail-board
1.6G	affcom
1.5G	wikimedia-il
1.5G	wikien-l
1.4G	wmfcc-l
1.3G	wikiit-admins-l
1.3G	wikidata-bugs.mbox
1.3G	functionaries-en
1.2G	clerks-l
1.1G	wikitech-l
1.1G	wikinews-pl.mbox
1.1G	wikimediaua
1.1G	wikimedia-in-exec.mbox
1.1G	analytics-internal

for each list there is an .mbox file and a directory of HTML generated from that mbox file. the list above contains both and is simply sorted by size

That's a pretty big database and I do share Jaime's concerns.
I would definitely not store that on m2 as it already has OTRS which is around 500GB already. If we had to store this in our misc infra, I guess m1 is in better shape on that regards.

While I agree it shouldn't be on m2, I suggest better m5 (which recently got much smaller) or even create a new section just for mailman3, as something tells me this will grow a lot (like OTRS). m1 is mostly fully dedicated to SRE critical tools (like backups), and while it has space, I would like to keep it "light" and not mix it with non-sre tools, with a separation of concerns.

We may have enough for a dedicated m6, given we would only need 1 host per dc?

In T256538#6469302, @jcrespo wrote:

That's a pretty big database and I do share Jaime's concerns.
I would definitely not store that on m2 as it already has OTRS which is around 500GB already. If we had to store this in our misc infra, I guess m1 is in better shape on that regards.

While I agree it shouldn't be on m2, I suggest better m5 (which recently got much smaller) or even create a new section just for mailman3, as something tells me this will grow a lot (like OTRS)

This requires new hardware, and as far as I remember we didn't budget for it (we do have the "unexpected" hosts one. But I would like to keep that for core, just in case.

We may have enough for a dedicated m6, given we would only need 1 host per dc?

I rather put it on m5, although my idea was to move wikitech out of m5 and get rid of m5 entirely, but if that happens, I guess mailman can live in m5 anyways.
m5 doesn't use the proxies, which is not something I like. so we need to see what to do there. Perhaps now that m5 isn't as loaded, we can re-start using the proxies again.

The databases that live there now are:

• wikitech
• testreduce and friends
• labsdbaccounts

m5 activity has decreased a lot, so the issues with the proxies (continue flapping) won't be an issue anymore:

Yes, agree, once wikitech is moved, we can keep it and not introduce a new section, keep it for mailman, that will need significant IOPS and disk.

I'm asking to have a test VM in production to test it. Can I have a test db in production for this? two databases (mailman3 and mailman3web with users exactly the same name) preferably somewhere sandboxes.

@Ladsgroup are these just testing databases that will be deleted at some point or are these testing databases that will become production? :-)
The concerns raised on this task earlier about disk space still apply, and we really need to get some estimations before we can decide where to place them (once they are going to become production).

Here T256538#6468985 we have some estimations, but here are talking about about a lot more just to start with T256538#6265626. We'd need to have a clear picture of what we should expect.

While I don't want to block your tests, I'd like to get some estimations on when these testing databases will be dropped in order not to create them as testing and then assuming they become production from one day to another (as this has happened in the past with other databases).

In T256538#6889858, @Marostegui wrote:

@Ladsgroup are these just testing databases that will be deleted at some point or are these testing databases that will become production? :-)

Not sure what he had in mind, but I would like for them to be deleted when we delete the test VM as well. Maybe we can name them "testmailman3" and "testmailman3web"?

The concerns raised on this task earlier about disk space still apply, and we really need to get some estimations before we can decide where to place them (once they are going to become production).

My understanding (@Ladsgroup correct me if I'm wrong) is that overall we'll use less storage overall because the mails will just be in the database once instead of in both HTML and mbox format. We can probably do some napkin math in Cloud seeing how much 1 or 10 or 100 messages takes up in the database and multiply based on our current archives and growth rate.

But that figure wouldn't include attachments, so I think we'd only be able to give a real number once we have a test setup and can import a list archive into mailman3 and extrapolate from there. Since the archives contain PII, I don't think we can do this outside of production.

In T256538#6889860, @Legoktm wrote:

In T256538#6889858, @Marostegui wrote:

@Ladsgroup are these just testing databases that will be deleted at some point or are these testing databases that will become production? :-)

Not sure what he had in mind, but I would like for them to be deleted when we delete the test VM as well. Maybe we can name them "testmailman3" and "testmailman3web"?

Yeah, we will delete it. The name should be fine but the username can be slightly complicated. Let me check.

Also exactly, One reason to have a test system is to see how big it'll become and get a better estimation of its size.

Name of the db and the mysql user is hard-coded (we can hiera-fiy it though. Not too complicated)

In T256538#6889860, @Legoktm wrote:

In T256538#6889858, @Marostegui wrote:

@Ladsgroup are these just testing databases that will be deleted at some point or are these testing databases that will become production? :-)

Not sure what he had in mind, but I would like for them to be deleted when we delete the test VM as well. Maybe we can name them "testmailman3" and "testmailman3web"?

That works for me.

In T256538#6890253, @Ladsgroup wrote:

Yeah, we will delete it. The name should be fine but the username can be slightly complicated. Let me check.

Also exactly, One reason to have a test system is to see how big it'll become and get a better estimation of its size.

I am ok with "normal" usernames, but let's give the databases a "test" name as @Legoktm suggested above.
Do you have some estimations on how long you want to run this test for?

In T256538#6916381, @Marostegui wrote:

Do you have some estimations on how long you want to run this test for?

For me, hopefully a month or two. Add one or two to be safe.

In T256538#6917363, @Ladsgroup wrote:

In T256538#6916381, @Marostegui wrote:

Do you have some estimations on how long you want to run this test for?

For me, hopefully a month or two. Add one or two to be safe.

That'd be ok with me

So to sum up for this testing databases:

Section: m5
Name: testmailman3 and testmailman3web
Approximate time frame before deleting them: 2-3 months

I would need the users and the desired grants.

@Ladsgroup I assume no backups needed as this is a test?

In T256538#6920684, @jcrespo wrote:

@Ladsgroup I assume no backups needed as this is a test?

correct

In T256538#6920675, @Marostegui wrote:

I would need the users and the desired grants.

user testmailman3 having all rights on testmailman3 database
user testmailman3web having all rights on testmailman3web database.

We might later need a third database because hyperkitty is being weird but I hope not. I'll let you know if it really become needed.

@Ladsgroup from which hosts would you be connecting from? m5 doesn't use the proxy (yet), so I would need to grant certain IPs instead of the proxy ones.

Databases created on db1128.eqiad.wmnet (m5 master):

# host m5-master.eqiad.wmnet
m5-master.eqiad.wmnet is an alias for db1128.eqiad.wmnet.
db1128.eqiad.wmnet has address 10.64.0.98

Thanks. It should be the IP of the VM but that's not created yet (T276686) we were waiting for the databases to be created first (basically a chicken and egg problem)

Databases are now created, once I get the IPs I will create the users :)

In T256538#6920846, @Marostegui wrote:

Databases are now created, once I get the IPs I will create the users :)

208.80.154.13 (https://netbox.wikimedia.org/virtualization/virtual-machines/364/)

Change 673846 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m5.sql: Add mailman3 databases grant

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

Grants added:

root@db1128.eqiad.wmnet[(none)]> show grants for 'testmailman3'@'208.80.154.13';
+-------------------------------------------------------------------------------------------------------------------------+
| Grants for testmailman3@208.80.154.13                                                                                   |
+-------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testmailman3`@`208.80.154.13` IDENTIFIED BY PASSWORD '*x' |
| GRANT ALL PRIVILEGES ON `testmailman3`.* TO `testmailman3`@`208.80.154.13`                                              |
+-------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

root@db1128.eqiad.wmnet[(none)]> show grants for 'testmailman3web'@'208.80.154.13';
+----------------------------------------------------------------------------------------------------------------------------+
| Grants for testmailman3web@208.80.154.13                                                                                   |
+----------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testmailman3web`@`208.80.154.13` IDENTIFIED BY PASSWORD '*x' |
| GRANT ALL PRIVILEGES ON `testmailman3web`.* TO `testmailman3web`@`208.80.154.13`                                           |
+----------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

@Legoktm I have left the passwords at:

lists1002:/home/legoktm# ls
testmailman3  testmailman3web

Probably not worth adding them to the private repo if this is just going to be for a couple of month testing, but up to you.

@Legoktm @Dzahn you might need to open firewall rules to be able to reach db1128 (m5 master) from lists1002.

# telnet db1128.eqiad.wmnet 3306
Trying 10.64.0.98...



telnet: Unable to connect to remote host: Connection timed out

Change 673846 merged by Marostegui:
[operations/puppet@production] production-m5.sql: Add mailman3 databases grant

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

Yup, the ferm is missing (T277286) I'll add it.

In T256538#6933316, @Marostegui wrote:

@Legoktm @Dzahn you might need to open firewall rules to be able to reach db1128 (m5 master) from lists1002.

# telnet db1128.eqiad.wmnet 3306
Trying 10.64.0.98...



telnet: Unable to connect to remote host: Connection timed out

I'm not really sure where to configure this in ferm, it looks like ferm_misc.pp isn't used for m5-master. I'm going to put this under the current if $shard == 'm5' hack that already exists.

Change 674724 had a related patch set uploaded (by Legoktm; author: Legoktm):
[operations/puppet@production] mariadb::misc: Open firewall hole for lists1002 (mailman3)

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

Change 674724 merged by Legoktm:
[operations/puppet@production] mariadb::misc: Open firewall hole for lists1002 (mailman3)

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

Can connect now:

legoktm@lists1002:~$ telnet db1128.eqiad.wmnet 3306
Trying 10.64.0.98...
Connected to db1128.eqiad.wmnet.
Escape character is '^]'.
]
5.5.5-10.4.14-MariaDB-log���8%PDFnz:�Pb~tL^sHK#gamysql_native_passwordConnection closed by foreign host.