CUNY Academic Commons

I've done some thinking and research about how this might be best effected, and I have a couple notes to jot down.

The simplest and perhaps best solution is to convert every single table to utf8mb4. For the last year+, newly created sites on the Commons have their tables created with a utf8mb4 charset, but older tables use a mix of encodings. Converting them all to utf8mb4 would make it very simple to have a basic, hardcoded callback for the 'pre_' filters discussed above. However, converting every table is problematic. Moving to utf8mb4 means that the max index length is shorter, meaning that many tables can't be automatically converted using ALTER TABLE. This is true of some core tables - as described in the make/core post linked above - and also for a fairly large variety of custom plugin tables. In each of these cases, we'd need to first drop and re-add the shorter index before converting. There's also the fact that converting is not guaranteed to preserve weird characters in all cases, which means that I would like to have a full-fidelity backup before getting started. But our database is mammoth, making this quite hard.

My thought on the other end of the spectrum was to assemble a matrix of actual table encodings, and for the filter callback to reference that matrix. This is hard, though. Encodings are not consistent across blogs (as noted, older blogs have different encodings, and even then they're not totally consistent, depending in part on the server environment at the time of table creation). Moreover, since we also have to filter column charsets, the matrix size gets much bigger. And, sadly, column encoding even within similar tables across blogs is not always consistent, making it hard to come up with general rules (like: blogs with ID < 2000 have table encodings utf8_general_ci; wp_terms tables with utf8_general_ci have such-and-such encoding for the 'name' column).

So I think we should do a more scaled-back version of the conversion project. Instead of converting all tables, we'll look for some targeted ones. The SHOW FULL COLUMNS queries in the processlist log are focused on, in rough order of frequency:
- options tables
- postmeta tables
- the wp_usermeta table
- statpress tables

It makes sense that these are on the list most, since they're some of the tables most likely to be updated on a fairly regular basis. So my next step is to write some scripts that crawl through the database, looking for tables of each of these types; uses mysqldump to get a backup of the table; converts indexes if necessary; and then converts the table encoding. Once this is done, we can then write pre_ filters for these specific tables. This targeted approach should allow us to get the worst offenders.

wp_bp_activity is another high-impact place to make this change, since it's a single table, and is updated frequently.

Ray, could I ask you to do a review of what I've done so far?

Thanks for the well-researched post, Boone.

The CLI script looks good and since you've already tested it on cdev with no errors, it should be safe to run on production. The Foliovision article notes that running this sort of script should be done when the site isn't really busy.

I also agree that converting tables based on the number of hits in the process log is a good and safe way to show if the charset conversion will improve performance. I'd say go ahead when you feel comfortable to pull the trigger, Boone!

I've run the scripts on the following tables:

- postmeta https://github.com/cuny-academic-commons/cac/commit/976af0726f21be8d8318039d92fdf099991d8c29
- statpress https://github.com/cuny-academic-commons/cac/commit/60beb92038394a49fac52c9bbee6611cce5bbf58

Cavalcade tables converted and added to whitelist in https://github.com/cuny-academic-commons/cac/commit/87d774e91886143b557ecfc8a6464bfbe3ec9e6b