CUNY Academic Commons

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.

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?

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.

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.

I'm glad everything turned out okay. Thanks for your work on this, Boone!

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.

Such awesome and impressive work, and for an important plugin. Thank you, Boone, and thank you, Ray! And thanks, Colin, for suggesting we look again at this~