Page MenuHomePhabricator

Enable innodb-large-prefix on tools.db.svc.eqiad.wmflabs
Closed, ResolvedPublic

Description

Feature request: Would it be possible to enable the innodb-large-prefix setting on tools.db.svc.eqiad.wmflabs? It is currently disabled. There is a simple and obvious use case for this feature: Mediawiki page name fields are varchar(255), which is too long to use as a table key under the current setting.

Event Timeline

russblau created this task.Nov 2 2017, 8:19 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptNov 2 2017, 8:19 PM
bd808 moved this task from Backlog to Wiki replicas on the Data-Services board.Nov 3 2017, 12:35 AM
bd808 added a project: DBA.
Marostegui added a subscriber: Marostegui.EditedNov 3 2017, 7:01 AM

We do not have it enabled on production.
Why do you want to add an index for the whole string on that column instead of the N first chars that might be enough?

To be able to enable this we'd need to also set innodb_file_format to Barracuda (which is fine) but this would also require the tables to be DYNAMIC or COMPRESSED. So it would work for new tables created like that or for existing tables that would need to be altered.

To answer your question: We cannot ensure that titles in a user database are unique unless we can set a UNIQUE KEY on the entire column; two titles might be identical in the first 254 characters but differ in the 255th (in the most extreme possible case).

Change 389425 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] tools.my.cnf.erb: Enable innodb_large_prefix

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

Change 389425 merged by Marostegui:
[operations/puppet@production] tools.my.cnf.erb: Enable innodb_large_prefix

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

Marostegui closed this task as Resolved.Nov 6 2017, 9:10 AM
Marostegui claimed this task.

I have enabled it on both hosts and merged the config change.

Remember that it will only work with DYNAMIC or COMPRESSED tables. So you'd need to alter the desired tables with: alter table XX ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 or alter table XX ROW_FORMAT=DYNAMIC.
https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format-dynamic.html

Marostegui reopened this task as Open.Nov 6 2017, 10:20 AM

I have disabled it because I am seeing some performance issues, and I want to see if they are related (they look related so far)

It matches with a spike on INSERTs for another user's db, so I want to try to isolate both things.

Marostegui moved this task from Triage to In progress on the DBA board.Nov 6 2017, 1:23 PM

Change 389477 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] tools.my.cnf.erb: Enable innodb_large_prefix

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

I have had it enabled for a couple of hours without any issues, but before merging I am going to give it a few more hours so we can make sure it was indeed just a coincidence with the spike on INSERTs I saw.
After inspecting the binlogs, the increase is just for one user, which generate a big amount of writes which made the tools slave lag.

Change 389477 merged by Marostegui:
[operations/puppet@production] tools.my.cnf.erb: Enable innodb_large_prefix

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

Marostegui closed this task as Resolved.Nov 6 2017, 4:11 PM

Closing this as resolved as it has been enabled for hours without issues.