Project

General

Profile

Bug #15242

Slugist site

Added by Raffi Khatchadourian 5 months ago. Updated 5 months ago.

Status:
Reporter Feedback
Priority name:
Normal
Assignee:
Category name:
Performance
Target version:
Start date:
2022-02-02
Due date:
% Done:

0%

Estimated time:

Description

My site https://khatchad.commons.gc.cuny.edu has become super sluggish. I compared it with another site https://tlc.commons.gc.cuny.edu. That site seems much faster. Do you have any idea what could be slowing my site down?

History

#1 Updated by Boone Gorges 5 months ago

  • Status changed from New to Reporter Feedback

Hi Raffi - I spent a few minutes looking and noticed some general sluggishness, but wasn't able to pinpoint any single item that might be causing the bulk of the problem. It's likely that it's a death-by-a-thousand-cuts sort of situation, with many plugins etc interacting in many ways.

Two things did stand out to me, however.

1. Jetpack is doing a couple of things that might be causing some network latency. There are lots of pings to wordpress.com and related URLs. Many of them are non-blocking, and so shouldn't really affect pageload, but it may be worth experimenting with certain features to see if they're the source of the slowness. I also noticed in the pageload waterfall that a particular request to https://khatchad.commons.gc.cuny.edu/wp-admin/admin.php?page=stats&noheader&proxy&chart=admin-bar-hours-scale was loading particularly slowly; this is a stats image generated by Jetpack and shown in the admin bar to site admins only. As such, you might consider trying the site side-by-side as an admin (your normal status) and a non-admin (either logged-out or as a non-admin Commons user) to see if there's a dramatic difference. If so, it mitigates your concerns about sluggishness somewhat, and also helps to narrow down potential root causes.

2. I ran a utility that identifies slow-running MySQL queries that your page generates. In your case, Gravity Forms and Subscribe2 are both triggering SHOW TABLES LIKE ... requests on every single page load, and on our very large database, this caused some database-level latency. It's an architectural problem with those plugins that they do this: they're checking to see whether schema upgrades are required, but they should have some other mechanism for this, or at least only do it for logged-in users, while looking at the Dashboard. As a test, I've forced these two specific checks to happen only when viewing the Dashboard, and in my tests it shaves almost a second off of the TTFB for front-end pages on your site. Let me know whether you notice a difference with these changes in place. If so, I'll consider sending the suggestion to the plugin authors.

#2 Updated by Raffi Khatchadourian 5 months ago

Thanks for the prompt response, Boone!

1. I did a side-by-side with one browser window logged in as me (admin) and another in incognito mode. There's a significant difference in the performance of the pages, especially ones with images. It's good to know that at least visitors will have a better experience.

2. Yes, I noticed a difference with these changes in place. Thanks!

#3 Updated by Boone Gorges 5 months ago

  • Target version set to Not tracked

Thanks for confirming, Raffi.

I've submitted requests to the Gravity Forms and Subscribe2 teams with suggestions. For posterity, here's my support request to GF:

Hello - I've run into a problem on a client site. It's a large WordPress Multisite installation. Sites running GF have experience performance issues due to the requires_schema_upgrade() check in the GFWebAPI. The SHOW TABLES LIKE ... query is run on every pageload, and on databases with many tables (in this case, several hundred thousand), this query can take .5 seconds or more to run.

Schema upgrades need to run very rarely, so ideally you would be target the check more carefully. Some ideas:

- Only run based on a version check
- Only run in the Dashboard, or when logged in as an Administrator, or both (other places where GF uses SHOW TABLES... are limited to GF admin pages, which makes them slow, but at least it's limited to a small number of admin users)

The first option is the best for limiting how often the query is run. The second is easier to implement - just wrap some of the maybe_upgrade_schema() calls:

if ( is_admin() && current_user_can( 'manage_options' ) ) ...

When GF is upgraded, the very next pageload will, in almost 100% of cases, come from an admin user viewing wp-admin, so this feels like a very safe change.

Thanks for considering.

Here's the PR for Subscribe2: https://github.com/weMail/Subscribe2/pull/14

I'm going to commit these two changes to the Commons repo as well. They will be overwritten the next time the plugins are upgraded, but at least it will provide some record of the change, as well as a reference to this ticket.

gravityforms: https://github.com/cuny-academic-commons/cac/commit/74979bfa5ce538b6274d76ed061ca07b7409a1f2
subscribe2: https://github.com/cuny-academic-commons/cac/commit/68e947f9d22638008e02e9309331f899984806fc

#4 Updated by Raffi Khatchadourian 5 months ago

Thanks for all of this, Boone. Why do we have so many tables? Is it because CAC is a multitenant installation? Does every site have its own set of tables?

#5 Updated by Boone Gorges 5 months ago

Yes, this is how WordPress Multisite works. Each site has 10-20 of its own database tables.

#6 Updated by Boone Gorges 5 months ago

FYI, my pull request was accepted on the Subscribe2 repo.

I'm not holding my breath on the Gravity Forms suggestion, since they don't have an open model for feature development and historically have not taken kindly to interlopers like myself making recommendations. But we'll hold this ticket for a bit longer in case there's an update.

#7 Updated by Raffi Khatchadourian 5 months ago

Ah, that's a shame. It's a really nice plug-in.

Also available in: Atom PDF