pubsubhubbub_topic_urls massive size and autoload=yes

[archon810]

Hi,

I noticed a lot of simple wp_options queries to our db started taking several seconds to return in the last few months and finally decided to analyze.

I found that by far the top offender, which is returned by all queries SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' is pubsubhubbub_topic_urls.

This means every single page request loads this value and that much more data, unnecessarily. On one of our sites, the length is 2MB, on another 500KB.

But setting it to autoload=no apparently doesn't get rid of the code accessing it on every page load either. Now I am seeing a separate query for SELECT option_value FROM wp_options WHERE option_name = 'pubsubhubbub_topic_urls' LIMIT 1 fire all the time instead.

This is a very taxing event on the network.

Can you please take a look? I don't think there's a need to access pubsubhubbub_topic_urls on every request to the site.

Thank you.

[pfefferle]

Hey archon810, this is because of extensibility... The current design allows me to extend the plugin with other plugins with one simple filter. That is why I have to save new topic URLs to the options table to check on page load if the plugin has to add the pubsubhubbub headers or not. I understand your problem and try to find a different solution.

[archon810]

Looking forward to your new solution and approach.

[pfefferle]

should be fixed with 3.0.0

[archon810]

@pfefferle Hooray! Updating now.

Do we need to clear out this option from the db? Can you please explain the fix? Is the value still used and just less frequently or is it no longer needed?

[archon810]

The effect of this change on our db servers really shows how drastic performance tuning can be:

[archon810]

And here's the web servers:

[pfefferle]

It is no longer needed, I added a second filter to add/enable the PuSH headers. You can clean up the dB field and I will update the readme with the next version.

Glad that it has such a positive effect ☺️