migration_results.md

5.39.0 -> 6.0.0 Migration Results (70M+ posts)

Dataset

Table	Rows
Posts	72M
Reactions	14M
Preferences	6M
Threads	5M
ChannelMembers	4M
Channels	140k
Users	10k

Database Cluster

• 1x r5.2xlarge Aurora RDS writer (8vCPU, 64GB RAM)
• 1x r5.2xlarge Aurora RDS reader (8vCPU, 64GB RAM)

MySQL

#	Query	Duration
1	ALTER TABLE Posts MODIFY COLUMN FileIds text, MODIFY COLUMN Props JSON, DROP COLUMN ParentId;	3h40m
2	ALTER TABLE ChannelMembers MODIFY NotifyProps JSON;	~2m
3	ALTER TABLE LinkMetadata MODIFY Data JSON;	~50ms
4	ALTER TABLE Jobs MODIFY Data JSON;	~90ms
5	ALTER TABLE Sessions MODIFY Props JSON;	~100ms
6	ALTER TABLE Threads MODIFY Participants JSON;	~2m
7	ALTER TABLE Users MODIFY Props JSON;	~3s
8	ALTER TABLE Users MODIFY NotifyProps JSON;	~2s
9	ALTER TABLE Users MODIFY Timezone JSON;	~2s
10	UPDATE Posts SET RootId = ParentId WHERE RootId = '' AND RootId != ParentId;	~12m
11	UPDATE CommandWebhooks SET RootId = ParentId WHERE RootId = '' AND RootId != ParentId;	~1ms
12	ALTER TABLE CommandWebhooks DROP COLUMN ParentId;	~1ms
13	CREATE INDEX idx_posts_root_id_delete_at ON Posts (RootId, DeleteAt);	~15m
14	DROP INDEX idx_posts_root_id ON Posts;	~500ms
15	CREATE INDEX idx_channels_team_id_display_name ON Channels (TeamId, DisplayName);	~2s
16	CREATE INDEX idx_channels_team_id_type ON Channels (TeamId, Type);	~400ms
17	DROP INDEX idx_channels_team_id ON Channels;	~30ms
18	CREATE INDEX idx_threads_channel_id_last_reply_at ON Threads (ChannelId, LastReplyAt);	~23s
19	DROP INDEX idx_threads_channel_id ON Threads;	~60ms
20	CREATE INDEX idx_channelmembers_user_id_channel_id_last_viewed_at ON ChannelMembers (UserId, ChannelId, LastViewedAt);	~20s
21	CREATE INDEX idx_channelmembers_channel_id_scheme_guest_user_id ON ChannelMembers (ChannelId, SchemeGuest, UserId);	~20s
24	DROP INDEX idx_channelmembers_user_id ON ChannelMembers;	~60ms
25	CREATE INDEX idx_status_status_dndendtime ON Status (Status, DNDEndTime);	~40ms
26	DROP INDEX idx_status_status ON Status;	~30ms

Notes

Query #1 needed some DB specific tuning to succeed as it showed very high memory consumption (~16GB peak) which on config defaults caused an OOM (out of memory) database crash:

• Overall Posts table size was of ~25GB data and ~45GB index.
• innodb_buffer_pool_size was modified from the default of {DBInstanceClassMemory*3/4} to {DBInstanceClassMemory*1/2} to allow for more freeable memory.
• Significant DB CPU usage was noticed throughout the query execution.

Freeable Memory

CPU Usage

PostgreSQL

#	Query	Duration
1	ALTER TABLE channelmembers ALTER COLUMN notifyprops TYPE jsonb USING notifyprops::jsonb	~35s
2	ALTER TABLE jobs ALTER COLUMN data TYPE jsonb USING data::jsonb;	~20ms
3	ALTER TABLE linkmetadata ALTER COLUMN data TYPE jsonb USING data::jsonb;	~40ms
4	ALTER TABLE posts ALTER COLUMN props TYPE jsonb USING props::jsonb;	~1h
5	ALTER TABLE sessions ALTER COLUMN props TYPE jsonb USING props::jsonb;	~100ms
6	ALTER TABLE threads ALTER COLUMN participants TYPE jsonb USING participants::jsonb;	~43s
7	ALTER TABLE users ALTER COLUMN props TYPE jsonb USING props::jsonb;	~1s
8	ALTER TABLE users ALTER COLUMN notifyprops TYPE jsonb USING notifyprops::jsonb;	~1s
9	ALTER TABLE users ALTER COLUMN timezone TYPE jsonb USING timezone::jsonb;	~1s
10	UPDATE Posts SET RootId = ParentId WHERE RootId = '' AND RootId != ParentId;	~23s
11	ALTER TABLE Posts DROP COLUMN ParentId;	~3ms
12	UPDATE CommandWebhooks SET RootId = ParentId WHERE RootId = '' AND RootId != ParentId;	~1ms
13	ALTER TABLE CommandWebhooks DROP COLUMN ParentId;	~5ms
14	CREATE INDEX idx_posts_root_id_delete_at ON Posts (RootId, DeleteAt);	~1m30s
15	DROP INDEX idx_posts_root_id;	~120ms
16	CREATE INDEX idx_channels_team_id_display_name ON Channels (TeamId, DisplayName);	~60ms
17	CREATE INDEX idx_channels_team_id_type ON Channels (TeamId, Type);	~25ms
18	DROP INDEX idx_channels_team_id;	~4ms
19	CREATE INDEX idx_threads_channel_id_last_reply_at ON Threads (ChannelId, LastReplyAt);	~6s
20	DROP INDEX idx_threads_channel_id;	~20ms
21	CREATE INDEX idx_channelmembers_user_id_channel_id_last_viewed_at ON ChannelMembers (UserId, ChannelId, LastViewedAt);	~10s
22	CREATE INDEX idx_channelmembers_channel_id_scheme_guest_user_id ON ChannelMembers (ChannelId, SchemeGuest, UserId);	~10s
23	DROP INDEX idx_channelmembers_user_id;	~8ms
24	CREATE INDEX idx_status_status_dndendtime ON Status (Status, DNDEndTime);	~14ms
25	DROP INDEX idx_status_status;	~6ms
26	ALTER TABLE posts ALTER COLUMN fileids TYPE character varying(300);	~4ms

FYI additional explanations appear to exist at https://gist.github.com/streamer45/59b3582118913d4fc5e8ff81ea78b055.