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