Slow query after update to 1.0.37

  • Resolved 1benk1

    (@1benk1)


    4 years, 11 months ago

    Hello,

    With the update from 1.0.36 to 1.0.37 you have changed the query args on several places to something like this:

    'meta_query' => array(
	array(
		'value'   => 'hide-amp',
		'compare' => '!='
	)
)

    We have monitored that with these updates the DB-load jumps 100% from 0,2 to 0,4.
    The slow-query-log is showing many SQL queries like this one:

    SELECT   wp_posts.ID FROM wp_posts  LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) WHERE 1=1  AND wp_posts.ID NOT IN (301840) AND ( 
  wp_term_relationships.term_taxonomy_id IN (6,394)
) AND ( 
  wp_postmeta.meta_value != 'hide-amp'
) AND wp_posts.post_password = '' AND wp_posts.post_type = 'post' AND ((wp_posts.post_status = 'publish')) GROUP BY wp_posts.ID ORDER BY wp_posts.ID DESC LIMIT 0, 3

    The SQL-process overview is showing tons of query like this and every query takes round about 2-3 seconds, which is not good for the health of our site.

    Please check this outm re-change the query args and optimize your queries.

    Best regards,
    Ben

    • This topic was modified 4 years, 11 months ago by 1benk1.
    • This topic was modified 4 years, 11 months ago by 1benk1.
Viewing 7 replies - 1 through 7 (of 7 total)
  • Thread Starter 1benk1

    (@1benk1)

    Hello again,

    After some analysis I can tell you what is the problem with these queries…

    This is the original query from my first comment above:

    SELECT wp_posts.ID
FROM wp_posts LEFT JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id )
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND wp_posts.ID NOT IN ( 301840 )
AND ( wp_term_relationships.term_taxonomy_id IN ( 6,394 ) )
AND ( wp_postmeta.meta_value != 'hide-amp' )
AND wp_posts.post_password = ''
AND wp_posts.post_type = 'post'
AND ( (wp_posts.post_status = 'publish') )
GROUP BY wp_posts.ID
ORDER BY wp_posts.ID DESC
LIMIT 0, 3

    -> query execution time ~3 seconds

    This is the same query with a pre-selection to the postmeta-key “ampforwp-amp-on-off”:

    SELECT wp_posts.ID
FROM wp_posts LEFT JOIN wp_term_relationships ON ( wp_posts.ID = wp_term_relationships.object_id )
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND wp_posts.ID NOT IN ( 301840 )
AND ( wp_term_relationships.term_taxonomy_id IN ( 6,394 ) )
AND ( wp_postmeta.meta_key = 'ampforwp-amp-on-off' )
AND ( wp_postmeta.meta_value != 'hide-amp' )
AND wp_posts.post_password = ''
AND wp_posts.post_type = 'post'
AND ( (wp_posts.post_status = 'publish') )
GROUP BY wp_posts.ID
ORDER BY wp_posts.ID DESC
LIMIT 0, 3

    -> query execution time ~0,15 seconds

    You have definitely to pre-select the millions auf postmeta-data (on big sites) to some thousand postmeta-data with the key “ampforwp-amp-on-off”.

    Best regards,
    Ben

    Plugin Author Ahmed Kaludi

    (@ahmedkaludi)

    Can you please update the plugin to the latest version(1.0.38)? And let me know if the same issue or not.

    Thread Starter 1benk1

    (@1benk1)

    Version 1.0.38 has the same problem.

    Plugin Author Ahmed Kaludi

    (@ahmedkaludi)

    We checked your issue. Our development team is working on this issue. It will be fixing in our next update.

    Thread Starter 1benk1

    (@1benk1)

    This sounds great! Thank you!

    For all others out there with a realy big site (> 50k posts and millions of postmeta-data), the version 1.0.35 seems stable.

    After downgrade to this version the site rans smooth and the DB-load is nearly the half of the DB-load we checked out with 1.0.37 or 1.0.38.

    (The version 1.0.36 was never live on our site, but in other threads I have read that this version make troube as well.)

    Plugin Author Ahmed Kaludi

    (@ahmedkaludi)

    This issue has been fixed into the latest update. Can you please update the plugin and let me know if the issue is resolved or not.

    Thread Starter 1benk1

    (@1benk1)

    Hello again,
    I was 1 week in vacation, so sorry for the late answer…
    Today morning we have updated to version 1.0.39 and currently everything seems fine (after ~5 hours).

    Best regards,
    Ben

Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Slow query after update to 1.0.37’ is closed to new replies.