This is not the simple transition that I expected it to be. Here's what we know so far:

1. We have a lot of bad data -- there are a lot of tickets that need to be "fixed", over 300 in the test database. The largest cohort of these are of files that have no filenames recorded, so cannot be written. This can be solved with update article_data_mime_attachment set filename = 'untitled_file' where filename = "";, but there are still tickets/files that don't write correctly. This has slowed down the transfer significantly, since without using the --tolerant option, it will fail at each bad ticket. Migration looks like it will take about a day, but should be less than 48 hours.

2. Answering a question from above: Can we still view tickets with attachements when migrating? -- Yes, but only once we enable this option: $Self->{'Ticket::Article::Backend::MIMEBase::CheckAllStorageBackends'} = 1;. Without this option, setting the ArticleStorage backend to be ArticleStorageFS will lead to otrs only searching that backend. Setting CheckAllStorageBackends will check both DB and FS. It's unclear what or whether there's a performance hit here. If we proceed, this option should probably not be left on long term.

3. What happens if otrs tries to read an attachment that gets lost from the disk (e.g., we lose data due to failing over without copying all data, disk loss, etc) -- It seems tolerant of files not being readable. We haven't tried removing the files from disk, but given that it gracefully handles the situation when CheckAllStorageBackends is off (i.e., it just shows a ticket with no attachment) it should will handle the same issue where a file is missing.

Change 1002577 had a related patch set uploaded (by AOkoth; author: AOkoth):

[operations/puppet@production] vrts: increase envoy timeout for vrts1002

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

We ran the migration yesterday with --tolerant allowing it to continue on failures. Unfortunately, we filled up the disk. Some observations:

• There's 560gb of disk used, and it's still not all the data from mysql. Over 12.5 million files were written
• Migration took over 22 hours, I estimate that for the full run it would require 36-48 hours total
• We haven't tested rsync, but just to do find /srv/otrs-attachments -type f took almost 15 minutes.
• We still have a bunch of tickets with bad data

After performing the article migration on the test instance, we have some doubts as to whether this is going to be the best solution long-term for the following reasons:

1. It’s likely going to require that we use dedicated hosts as attaching large volumes to a VM is not recommended and might result in reliability and scaling issues.
2. We noticed that the data consumes a lot more space on disk than it does on the database (likely due to the use of compression). The migration did not run to completion and it filled a 600GB drive with what we've estimated to be slightly over half the attachments. At this point it’s not clear whether there is a way for the application to access compressed data while using file system storage.
3. We also run the risk of creating more problems for both our team and IF as the volume of data is likely to increase over short periods of time and is going to be painstakingly slow to sync across servers and also result in frequently needing additional storage space.

Something to consider, definitely needs to talk to the community first: Is really attachments from ten years ago needed? Can we set up a threshold and delete attachments after certain time (to be determined by VRTS admins)?

In T355980#9539657, @Ladsgroup wrote:

Something to consider, definitely needs to talk to the community first: Is really attachments from ten years ago needed? Can we set up a threshold and delete attachments after certain time (to be determined by VRTS admins)?

For permissions tickets, absolutely. We often get messages where all the important text has been printed out by someone so they could sign it and attach it as a PDF. Being able to review what was actually agreed to is vital for the process, especially when things are disputed a decade later.

Fair, if someone can take a look at what queues are the biggest, maybe we can trim non-permission ones.

@Ladsgroup As a start we are trying to identify what attachments could potentially be safely purged from the database perhaps on a schedule. An example (based on assumptions at this point) is that an article attachment associated to a ticket in one of the junk queues is a candidate for permanent deletion or migration to different storage outside the database.

-- What are the junk queues?
SELECT id, name FROM queue WHERE name LIKE '%junk%';
+-----+-----------------+
| id  | name            |
+-----+-----------------+
|   3 | Junk            |
| 203 | Junk (non-spam) |
+-----+-----------------+

-- What's the size of article attachments associated with junk tickets?
SELECT (sum(cast(adma.content_size as INTEGER))/1024/1024/1024) as size_gb
FROM article_data_mime_attachment adma 
INNER JOIN article a
ON a.id = adma.article_id
INNER JOIN ticket t
ON a.ticket_id = t.id
WHERE t.queue_id = 3 OR t.queue_id = 203;
+----------------+
| size_gb        |
+----------------+
| 5.646167408675 |
+----------------+

In T355980#9539267, @Arnoldokoth wrote:

After performing the article migration on the test instance, we have some doubts as to whether this is going to be the best solution long-term for the following reasons:

This is a good write up. As someone that "suffers" (owns) the maintenance of mysql backups (and recovery) problems that this causes, I am willing to provide some of my time to get this through, because how less painful on my side would be have either plain file or object store source backups rather than database ones. At the moment, despite technically having backups, I don't think those are logistically useful, as it could take days to recover those.

• Migration time doesn't seem to be a huge issue as it will just run once
• Do not worry about storage, storage is way cheaper than the extra time this requires to maintain as it is now from my side :-D, so I don't think it will be the limiting factor. It is true that we should come up with some compression solution, though.
• We handle on the plain filesystem 130 million files for media backups and 600 TB in total, so I don't see those numbers as too problematic. However, I wonder if the system is S3-compatible, or if we could implement ourselves an ArticleStorageS3, or other kind of remote & redundant storage (swift, ceph)?

Regarding reliability, we could create a long term backup of the database and keep it exported offline just in case- the main cost is keeping it fully online.

Icinga downtime and Alertmanager silence (ID=8b40e79f-31a1-4187-bfd2-99fa2eac452d) set by aokoth@cumin1002 for 2 days, 0:00:00 on 1 host(s) and their services with reason: Migration Ongoing

vrts1002.eqiad.wmnet

@jcrespo That's useful context, thanks!

It's probably important to note that we don't think it's feasible to move this to on-disk storage _right now_, but we think it will be possible in the future. @LSobanski is putting in a request for a physical host for vrts in the next round of hardware purchases, which we think will be more possible to manage this on physical hosts.

We've answered the questions that we're able to answer for the moment, so I'm moving this back to the Backlog until we have physical machines in place. Once that happens, we'll work on a plan to migrate.

Just as an opinion, as soon as the plan is going to become complete, I may worth involving the VRT community for feedback before the actual changes are made. At best via VRT wiki as maybe not everybody is able to follow here.

Hey @Krd, for the attachment storage this will be an invisible change to the vrt community, attachments will continue to be loaded and displayed as normal, and we're not anticipating needing to purge old attachments/tickets yet. For this particular piece of work we don't anticipate making any user changes, but if we do we'll definitely involve the community (This is something that's likely going to come out of T358065, I'd be interested to hear the best way to solicit feedback from the community for changes like these).

Change 1002577 abandoned by AOkoth:

[operations/puppet@production] vrts: increase envoy timeout for vrts1002

Reason:

not needed anymore

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