5.35 Migration

notes.md

Due to the introduction of new features (Shared Channels and Folded Reply Threads) the performance of the migration process for the 5.35 release has been noticeably affected. Depending on the size, type and version of the database, longer than usual upgrade times should be expected. These can vary from a couple of minutes (average case) to hours (worst case, MySQL only). A moderate to significant spike in database CPU usage should also be expected during this process.

MySQL (5.x/8.x)

Worst affected instances are those running on MySQL and having a considerable database size (several millions of rows in Posts table). These installations can expect a worst-case upgrade duration in the order of hours. (1 minute per ~200k Posts rows). This is due to the addition of a column to the Posts table that on such versions causes a complete table rebuild (with indexes).

In cases where such prolonged downtime is not acceptable, an online (manual) migration of the Posts table prior to upgrading is recommended. This means altering the table as such:

ALTER TABLE Posts ADD RemoteId VARCHAR(26);

Note

Simply running the query prior to the upgrade can save some downtime but it would still cause full table locking. This means that the Posts table would be read-only for the whole duration of the query execution (no posts can be created nor modified). This applies to High-Availability installations as well. In such cases, an online, lock-free migration is recommended (e.g. using an external tool like pt-online-schema-change.

PostgreSQL

Instances using PostgreSQL can expect the upgrade process to take up to a few minutes, increasing with database size (~4 minutes on a 12M Posts, 5M ChannelMembers, 250K Channels installation).

Is there any disadvantage using the statement shown above? Just wondering, why the upgrade isn't doing it itself if it is expected to be much faster ...

@jelmd

The query above is executed during migration but unless you are running Mattermost on an HA cluster it will block the server until it completes hence the suggestion to either run it asynchronously prior to the actual migration or through some online db schema migration tool to avoid locking.

@streamer45
Thanx for the info, It is not a HA setup. So asynchronously means: run the statement during MM is still running - MM might get a little bit slower during the run but usually it is still fully functional? So finally it just saves you a long startup time when running the new version for the first time?

@jelmd

Running the query prior to the upgrade (with MM running) will save some downtime but as noted above it will lock the Posts table. That means that for the duration of the query no updates to it will be possible. The installation will be essentially read-only as new messages will fail to be saved.

This is a limitation of the MySQL engine (InnoDB). To avoid this problem we suggest using an online schema migration tool like pt-online-schema-change. This can be executed with MM running and other than some added load to the DB it won't cause any locking so no functionality change. You can take a look at this gist for a sample execution.

@streamer: Did it [in a test instance] using the ALTER TABLE Posts ADD RemoteId VARCHAR(26); approach. Just took ~ 2min, so used the same procedure for the production instance as well w/o any problems. Thanx again for your hints/explanations :)