Page MenuHomeMiraheze

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

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 created this task.

The sql failed to update with some error I forgot.

John claimed this task.
Southparkfan claimed this task.
Southparkfan added a subscriber: John.

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

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.

@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 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.