Wordpress-seo: Avoid attachment_url_to_postid() AMAP

Created on 2 May 2018 · 11Comments · Source: Yoast/wordpress-seo

[x] I've read and understood the contribution guidelines.
[x] I've searched for any related issues and avoided creating a duplicate issue.

Please give us a description of what happened.

Site goes down, because of the slow queries caused by attachment_url_to_postid() calls in the plugin.

Please describe what you expected to happen and why.

I expected the plugin developers to have the large sites in mind, especially because attachment_url_to_postid() is uncached function, some of them can use the WP.Com cached versions and some cannot. At least, the images URLs in the settings shall be stored as IDs not URLs whenever possible.

Technical info

WordPress version: 4.9.5
Yoast SEO version: 7.4

Source

nash-ye

Most helpful comment

The referenced issue #9679 will combat this problem by making sure this function is only used when really needed.

Please check that issue for more optimizations applied internally.
We've dropped the custom caching we added ourselves again.

moorscode on 22 May 2018

👍3

All 11 comments

We had disabled the OpenGraph Images feature, since it increase the CPU load dramatically, we need better optimized implementation.

nash-ye on 4 May 2018

Same situation. Leaving the Facebook settings on triggers slow queries galore. I embed 5-10 images directly into the stories, some from wordpress some from dedicated folders on domain outside of wordpress. The system is now checking each one against the database holding up page load.

Below shows this, using Query Monitor plugin, and only difference is the Yoast setting on/off.

The delays are even worse on first publish, as it's checking every single image. I'm getting 12 second page loads in that scenario.

I have a large multi-site setup with tens of thousands of images per site and dozens of pieces of meta-data per story. So scanning the DB each time for each image is killing things.

Yoast Social FB ON FIRST LOAD / PREVIEW
with yoast fb on first load

Yoast Social FB ON
with yoast fb on

Yoast Social FB OFF
with yoast fb off

alex2k5 on 7 May 2018

👍2

The code that is being used on VIP can be found here: https://github.com/Automattic/vip-go-mu-plugins/tree/master/vip-helpers

More information about this package and using it on your WordPress installation: https://vip.wordpress.com/documentation/vip-go/local-vip-go-development-environment/

We need to look at what they are doing and if we want to implement a similar approach.

I assume that Core has not implemented this functionality because attachment_url_to_postid applies a filter at the end of the function, which (ideally) should not be cached as well, but called after collecting the cached result.

moorscode on 14 May 2018

We are already using the VIP functions in our website but the load remains high despite that it had been reduced a little bit. From the first place Yoast plugin shall store the IDs of attachments in the meta fields or options instead of using attachment_url_to_postid several times in each page load.

Currently, We had modified how Yoast plugin works (Of course not direct modifications =) ) to limit the OpenGraph images to the featured images only.

nash-ye on 15 May 2018

The referenced issue #9679 will combat this problem by making sure this function is only used when really needed.

Please check that issue for more optimizations applied internally.
We've dropped the custom caching we added ourselves again.

moorscode on 22 May 2018

👍3

Is the issue still relevant with the latest version of Yoast SEO @nash-ye?

benvaassen on 24 Jul 2018

I'm still having this problem with yoast 8.3
heavy CPU load on mysql

problem is not present when Facebook and Twitter Opengraph dissabled.

KosinusKC on 30 Sep 2018

The same problem for us with the latest version!

tonybahama on 8 Oct 2018

@tonybahama @KosinusKC I think you are experiencing issue https://github.com/Yoast/wordpress-seo/issues/10195. Can you please leave a comment there?

benvaassen on 9 Oct 2018

This very much still seems to be an issue. Version 13.4.1 (Premium), updated from 9.6.1 (yes a large jump), just brought one of our sites to its knees. Forcing this function to return 0; before executing the SQL query was all that we could do in the short term (outside of rolling back to the previous version that we know was running well).

trevorllarson on 28 Apr 2020

I am also experiencing this slow query on all admin pages using the latest version of Yoast (15.5). Query Monitor output below:

Slow Database Queries (above <span class="qm-warn">0.05</span>s)
Query   Caller  Component   Rows    Time
SELECT post_id, meta_value
FROM wp_postmeta
WHERE meta_key = '_wp_attached_file'
AND meta_value = '2018/07/site_logo.png'    

attachment_url_to_postid()
wp-includes/media.php:4725
WPSEO_Image_Utils::attachment_url_to_postid()
wp-content/plugins/wordpress-seo/inc/class-wpseo-image-utils.php:70
WPSEO_Image_Utils::get_attachment_by_url()
wp-content/plugins/wordpress-seo/inc/class-wpseo-image-utils.php:44
WPSEO_Image_Utils::get_attachment_id_from_settings()
wp-content/plugins/wordpress-seo/inc/class-wpseo-image-utils.php:457
Yoast\W\S\H\Image_Helper->get_attachment_id_from_settings()
wp-content/plugins/wordpress-seo/src/helpers/image-helper.php:308
Yoast\W\S\C\Meta_Tags_Context->generate_company_logo_id()
wp-content/plugins/wordpress-seo/src/context/meta-tags-context.php:286
Yoast\W\S\P\Abstract_Presentation->__get()
wp-content/plugins/wordpress-seo/src/presentations/abstract-presentation.php:64
Yoast\W\S\C\Meta_Tags_Context->generate_site_represents()
wp-content/plugins/wordpress-seo/src/context/meta-tags-context.php:322
Yoast\W\S\P\Abstract_Presentation->__get()
wp-content/plugins/wordpress-seo/src/presentations/abstract-presentation.php:64
Yoast\W\S\C\Meta_Tags_Context->generate_open_graph_publisher()
wp-content/plugins/wordpress-seo/src/context/meta-tags-context.php:384
Yoast\W\S\P\Abstract_Presentation->__get()
wp-content/plugins/wordpress-seo/src/presentations/abstract-presentation.php:64
Yoast\W\S\P\Indexable_Post_Type_Presentation->generate_open_graph_article_publisher()
wp-content/plugins/wordpress-seo/src/presentations/indexable-post-type-presentation.php:246
Yoast\W\S\P\Abstract_Presentation->__get()
wp-content/plugins/wordpress-seo/src/presentations/abstract-presentation.php:64
Yoast\W\S\P\O\Article_Publisher_Presenter->get()
wp-content/plugins/wordpress-seo/src/presenters/open-graph/article-publisher-presenter.php:33
Yoast\W\S\P\Abstract_Indexable_Tag_Presenter->present()
wp-content/plugins/wordpress-seo/src/presenters/abstract-indexable-tag-presenter.php:30
Yoast\W\S\I\Front_End_Integration->present_head()
wp-content/plugins/wordpress-seo/src/integrations/front-end-integration.php:276
do_action('wpseo_head')
wp-includes/plugin.php:484
Yoast\W\S\I\Front_End_Integration->call_wpseo_head()
wp-content/plugins/wordpress-seo/src/integrations/front-end-integration.php:251
do_action('wp_head')
wp-includes/plugin.php:484
wp_head()
wp-includes/general-template.php:3005
load_template('wp-content/themes/[theme-name]/header.php')
wp-includes/template.php:730
locate_template()
wp-includes/template.php:676
get_header()
wp-includes/general-template.php:48