Feature #20828
closedRe-enable Editoria11y?
0%
Description
In #20515, Ray disabled Editoria11y because it was causing database problems that were causing systemwide service issues. In https://redmine.gc.cuny.edu/issues/18842#note-19, Colin has indicated that it would be nice to re-enable the plugin.
Ray, can you provide more context to your comment about slow queries at https://redmine.gc.cuny.edu/issues/20515#note-14? Did it have to do with installation/upgrade routines? Queries like https://github.com/itmaybejj/editoria11y-wp/blob/c26ef57b487937efdf11b9ba7dad98746373ad39/editoria11y.php#L181 that touch INFORMATION_SCHEMA
are generally going to be problematic on the Commons.
If so, I wonder whether we might develop a fix for the plugin that uses SHOW CREATE TABLE
instead of queries against INFORMATION_SCHEMA
. ChatGPT is suggesting that it would serve a similar purpose (double-checking that foreign keys had been added to the tables) without the overhead: https://chatgpt.com/share/24ac9049-be62-4d02-8778-b87f0c426524
Files
Related issues
Updated by Boone Gorges 4 months ago
- Related to Bug #20515: Is the Commons Down? added
Updated by Raymond Hoh 4 months ago
Ray, can you provide more context to your comment about slow queries at https://redmine.gc.cuny.edu/issues/20515#note-14? Did it have to do with installation/upgrade routines?
Yes, I mention the problems in this comment -- https://redmine.gc.cuny.edu/issues/20515#note-10 :
In our last maintenance update (v2.4.1) on June 25th, we updated the Editoria11y Accessibility Checker plugin to v1.0.17. This plugin update included a database upgrade routine that can degrade performance especially on a multisite install. See https://github.com/itmaybejj/editoria11y-wp/issues/32 for a detailed report.
The plugin author has updated the plugin to v1.0.18 to address this issue; I've committed it here -- https://github.com/cuny-academic-commons/cac/commit/bcc4595d1b0a37f4514e6c85ed715a37b40ee445 -- and pushed the update to production in hopes that the routine is fixed. Hopefully this helps with database responsiveness, but will keep checking the plugin update list to see what else could have caused the database slowness.
According to https://github.com/itmaybejj/editoria11y-wp/issues/32 , it looks like the database upgrade routine in v1.0.17 was always running on each request for those running MySQL 8. The plugin author tried to address the issue in v1.0.18, but that didn't seem to solve the problems when I pushed it to production. It was after I force-deactivated Editoria11y that the Commons was able to resume normal operations -- https://redmine.gc.cuny.edu/issues/20515#note-13 .
v1.0.18 still uses INFORMATION SCHEMA
queries though. Before I force-deactivated Editoria11y, it was active on 1939 sites. Perhaps we can try reactivating the Editoria11y plugin only on one site to see if their upgrade routine will run successfully. If so, we can look to reactivate Editoria11y on an incremental basis.
If so, I wonder whether we might develop a fix for the plugin that uses SHOW CREATE TABLE instead of queries against INFORMATION_SCHEMA. ChatGPT is suggesting that it would serve a similar purpose (double-checking that foreign keys had been added to the tables) without the overhead: https://chatgpt.com/share/24ac9049-be62-4d02-8778-b87f0c426524
ChatGPT knows more than me on this! :)
Before we try the SHOW CREATE TABLE
approach, maybe we can forward this info to the plugin developer at https://github.com/itmaybejj/editoria11y-wp/issues/32 and see whether it is something they might consider.
Updated by Boone Gorges 4 months ago
According to https://github.com/itmaybejj/editoria11y-wp/issues/32 , it looks like the database upgrade routine in v1.0.17 was always running on each request for those running MySQL 8. The plugin author tried to address the issue in v1.0.18, but that didn't seem to solve the problems when I pushed it to production. It was after I force-deactivated Editoria11y that the Commons was able to resume normal operations -- https://redmine.gc.cuny.edu/issues/20515#note-13 .
Right. My reading of that ticket (and our own tickets) is that there were two sorts of problems. One is that the foreign-key update routine was being run too frequently - on every request, or in way that doesn't respect site-specific database prefixes. The fixes in 1.0.18 appear to address these issues. The other issue - which is not mentioned in the GitHub ticket, but is based on my own observations - is that running the INFORMATION_SCHEMA
update routine even a single time is going to cause database overload.
v1.0.18 still uses INFORMATION SCHEMA queries though. Before I force-deactivated Editoria11y, it was active on 1939 sites. Perhaps we can try reactivating the Editoria11y plugin only on one site to see if their upgrade routine will run successfully. If so, we can look to reactivate Editoria11y on an incremental basis.
Do we have live access to the slow query log, or did Yiu Ming have to send it to us? I think it would be useful to activate on a single site and let the update routine run. We can verify whether the update has run by running our own SHOW CREATE TABLE
at the command line. Then we can verify that the plugin internals have properly cached the successful upgrade (ie, check that the 1.0.18 fixes worked properly). Finally, we can check against the slow query log to see whether the upgrade routine took an inordinate amount of time. If so, then we can try patching to use SHOW CREATE TABLE
instead, and do the same set of checks. If it seems better, we can send a PR to the dev. (I've had a bit of correspondence with the Editoria11y developer in the past and he's open to support, especially regarding the WP-specific parts of the plugin. I'd like to have a sense that the solution is actually going to work before suggesting it to him.) What do you think of that plan?
Updated by Raymond Hoh 4 months ago
- File slow_query-20240702.zip slow_query-20240702.zip added
Do we have live access to the slow query log, or did Yiu Ming have to send it to us?
I've attached the last slow query log that Yiu Ming provided via email on July 2nd. Search in the log for "wp_ed11y_". We'll probably need to ping Yiu Ming for new slow query logs.
What do you think of that plan?
Sounds good to me. Boone, if you're going to try doing an Editoria11y upgrade on one site, modify the /mu-plugins/20515-disable-editoria11y.php
to add a check for the site ID you want to try it on and bail before the code takes place.
We can verify whether the update has run by running our own SHOW CREATE TABLE at the command line.
Editoria11y also bumps a DB version after the DB upgrade so that could also be checked as well: https://github.com/cuny-academic-commons/cac/blob/aa0e389636fa8dabaf1e59a96a1490af8690bb26/wp-content/plugins/editoria11y-accessibility-checker/editoria11y.php#L273
Updated by Boone Gorges 4 months ago
Ray, thanks for sharing the slow query log. Looking at the logs, it's pretty obvious that the information_schema queries are the problematic ones - while most of the slow queries here are in the 10-15 second range, these queries take 40,000+ seconds!
Because my hunch is so strong, and because I know it's going to be less than straightforward to get the slow query log, I decided to do some YOLO testing. Here's what I did:
1. I rewrote the logic in the Editoria11y plugin to parse SHOW CREATE TABLE
results rather than querying INFORMATION_SCHEMA
. I then did some testing locally to ensure that all the relevant cases (try/catch/finally) were properly handled. https://github.com/cuny-academic-commons/cac/commit/1f35e24aabefec88ac7580af0799307fd775f503
2. I deployed this to the production site. Ray, I was unable to do a git deployment because the rhoh user owns the editoria11y-accessibility-checker directory (git couldn't create the symlinks) - I don't know if this is something you can easily fix. Maybe just chmod g+w
, since it doesn't look like the group currently has write access. Anyway, I deployed manually using cut-and-paste in Vim.
3. I chose a site, https://us222723f24el.commons.gc.cuny.edu/ 34652, that had the plugin activated. In 20515-disable-editoria11y.php, I whitelisted that blog ID.
4. When I visited https://us222723f24el.commons.gc.cuny.edu/wp-admin/admin.php?page=editoria11y-accessibility-checker%2Fsrc%2Fadmin.php, I got a notice that database tables were broken. That's because the 'editoria11y_db_version' option was '1.1-failed', and the plugin has error checking built in that shows the error when the update has failed.
5. I checked the old schema for the wp_34652_ed11y_results table. It showed no CONSTRAINT.
6. I deleted the 'editoria11y_db_version' option for that site. This tells the plugin to trigger its update process.
7. I reloaded the page. There was minimal lag time - maybe 1 second. The page loaded properly and showed all previous editoria11y results.
8. I checked the new schema:
> show create table wp_34652_ed11y_results; +------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | wp_34652_ed11y_results | CREATE TABLE `wp_34652_ed11y_results` ( `pid` int(9) unsigned NOT NULL, `result_key` varchar(32) COLLATE utf8mb4_unicode_520_ci NOT NULL, `result_count` smallint(4) NOT NULL, `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `updated` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`pid`,`result_key`), CONSTRAINT `wp_34652_ed11y_results_pid` FOREIGN KEY (`pid`) REFERENCES `wp_34652_ed11y_urls` (`pid`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci |
9. I checked the db_version flag:
[CAC ldv2] ~/commons-production/www [1011]$ wp --url=us222723f24el.commons.gc.cuny.edu option get editoria11y_db_version 1.1
Based on all of this, I'd say that I've correctly identified the problem and that the fix works properly. Ray, could you read this over and see if my logic seems OK? If so, I'll turn off the filter in 20515-disable-editoria11y.php tool and see if the sky falls.
Updated by Raymond Hoh 4 months ago
I don't know if this is something you can easily fix. Maybe just chmod g+w, since it doesn't look like the group currently has write access.
Done. Looks like the permissions are okay now, but let me know if there are still problems.
Ray, could you read this over and see if my logic seems OK? If so, I'll turn off the filter in 20515-disable-editoria11y.php tool and see if the sky falls.
Logic looks good, Boone. Thanks for modifying Editoria11y's upgrade routine. I'm sure the plugin author will be pleased to see these enhancements! Let's hope everything runs okay when we do this en masse.
Updated by Boone Gorges 3 months ago
- Status changed from New to Resolved
- Target version changed from 2.5.0 to 2.4.6
Thanks, Ray!
I ran some more testing this morning. First, I had to fix a minor typo that caused duplicate constraints to be created on the results table: https://github.com/cuny-academic-commons/cac/commit/aec8082530547a43edaadc46d184bde93fac7916
My first step was to reenable the plugin by commenting out the filter in /mu-plugins/20515-disable-editoria11y.php
. The sky did not fall.
Then, I selected several sites with the plugin active: one that had never run the 1.0->1.1 upgrade routine, one that had 1.1-failed status, and one that had never had the tables installed. I visited the Editoria11y Dashboard page for each, which triggers the plugin's db installation routine. In each case, the site loaded quickly and the db operations were performed successfully.
Then, I ran a script that found the handful of sites with 1.1-failed status, and deleted the status. This will allow the plugin to trigger its DB creation routines on the next visit. After this, I believe the fix is fully implemented for the Commons. Colin and Laurie, would you care to take a look, to make sure the plugin's working as expected on the production site?
I've also opened a PR for the plugin author: https://github.com/itmaybejj/editoria11y-wp/pull/34
I'm going to mark this ticket Resolved, but please reopen if you find issues.
Updated by Raymond Hoh 3 months ago
I'm glad everything turned out okay. Thanks for your work on this, Boone!
Updated by Colin McDonald 3 months ago
I just gave the plugin a solid test drive on a site of mine, and it seems to be working well. Thanks for all of your work on this, Ray and Boone! I'm glad that it's back and will update #18842 about general accessibility concerns.