Slow query after update to 1.0.37

  • Resolved 1benk1

    (@1benk1)


    6 years 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 6 years ago by 1benk1.
    • This topic was modified 6 years ago by 1benk1.
Viewing 7 replies - 1 through 7 (of 7 total)
  • Thread Starter 1benk1

    (@1benk1)

    6 years ago

    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)

    6 years ago

    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)

    6 years ago

    Version 1.0.38 has the same problem.

    Plugin Author Ahmed Kaludi

    (@ahmedkaludi)

    6 years ago

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

    Thread Starter 1benk1

    (@1benk1)

    6 years ago

    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)

    6 years ago

    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)

    5 years, 11 months ago

    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.