Page MenuHomeMiraheze
Create Task
Maniphest T6095

Lack of PK on RottenLinks tables causes huge replication lag
Closed, ResolvedPublic
Actions

Description

https://grafana.miraheze.org/d/_9zrwMHmk/mariadb-replication?viewPanel=16&orgId=1&var-interval=$__auto_interval_interval&var-host=db7.miraheze.org:9104&from=1598277635054&to=1598309632462

db7 log: 2020-08-24 17:00:29 108 [Note] The slave is applying a ROW event on behalf of a DELETE statement on table rottenlinks and is currently taking a considerable amount of time (61 seconds). This is due to the fact that it is scanning the table while looking up records to be processed. Consider adding a primary key (or unique key) to the table to improve performance.

db7 replica state: Slave_SQL_Running_State: Delete_rows_log_event::find_row(-1)

A PK was added in https://github.com/miraheze/RottenLinks/pull/18, but reverted https://github.com/miraheze/RottenLinks/pull/21. What's the reason? @Paladox

Related Objects

Mentioned Here
P365 T6095

Event Timeline

Southparkfan triaged this task as High priority.Aug 24 2020, 22:55
Southparkfan created this task.
Herald added subscribers: RhinosF1, Reception123. · View Herald TranscriptAug 24 2020, 22:55
Paladox added a comment.Aug 25 2020, 01:35

The sql failed to update with some error I forgot.

Paladox added a project: RottenLinks.Sep 8 2020, 13:43
John moved this task from Backlog to Bugs on the RottenLinks board.Sep 8 2020, 17:49
John closed this task as Resolved.Sep 8 2020, 17:57
John claimed this task.
Southparkfan reopened this task as Open.Oct 9 2020, 21:47
Southparkfan claimed this task.
Southparkfan added a subscriber: John.

While the change was committed, the change has not been applied to existing tables.

Herald added a subscriber: Universal_Omega. · View Herald TranscriptOct 9 2020, 21:47
Southparkfan added a comment.Oct 10 2020, 01:54
02:56:34 <+SPF|Cloud> JohnLewis: db7 doesn't seem to accept a varchar(8192) for rl_externallink
02:56:38 <+SPF|Cloud> ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
03:01:39 <+SPF|Cloud> looking at https://mariadb.com/kb/en/innodb-system-variables/#innodb_large_prefix and https://mariadb.com/kb/en/innodb-large_prefix-deprecated-resulting-key-length/, relying on such huge varchars for index keys seems deprecated
03:09:42 <+SPF|Cloud> I don't think having the varchars as primary key is a good idea, given the deprecation comments. What do you think?

Changes have been deployed on all db13 wikis now, db1[12] not updates yet.

Southparkfan added a comment.Oct 13 2020, 21:40

@John it doesn't look like larger VARCHARs are possible. Even if you don't use the rl_id field, it does seem to be working fine at first glance, without fundamentally changing the schema (the current, old fields can stay). What do you think?

John added a comment.Oct 13 2020, 22:01
In T6095#123471, @Southparkfan wrote:

@John it doesn't look like larger VARCHARs are possible. Even if you don't use the rl_id field, it does seem to be working fine at first glance, without fundamentally changing the schema (the current, old fields can stay). What do you think?

I guess it’s fine to have an entirely unused column then

Southparkfan lowered the priority of this task from High to Normal.Oct 24 2020, 18:13

I was deploying the ALTERs to the wikis while RottenLinks was disabled (the ALTERs cannot be done live using pt-osc, unfortunately), but I forgot to re-enable RottenLinks. For now, I have enabled RottenLinks. The extension only needs to be disabled again (and the update scripts killed) if you are deploying the schema changes on databases.

John added a comment.Dec 22 2020, 13:30

@Southparkfan status?

Southparkfan closed this task as Resolved.Jan 1 2021, 19:19

Done (using P365).