Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Scaling WordPress Queries With Elasticsearch

25e2ecf9b520e06d71e47ab083924300?s=47 xyu
May 19, 2015

Scaling WordPress Queries With Elasticsearch

25e2ecf9b520e06d71e47ab083924300?s=128

xyu

May 19, 2015
Tweet

Transcript

  1. @HypertextRanch  me@xyu.io  xyu.io  xyu  Scaling WordPress

    Queries With Elasticsearch Xiao Yu / Automattic
  2.  

  3.  18,600,000,000 Page Views

  4.  18,600,000,000
 409,000,000 Page Views
 Unique Visitors

  5.  18,600,000,000
 409,000,000 56,000,000 Page Views
 Unique Visitors
 New Posts

  6.  18,600,000,000
 409,000,000 56,000,000
 68,000,000 Page Views
 Unique Visitors
 New

    Posts
 New Comments
  7.  18,600,000,000
 409,000,000 56,000,000
 68,000,000 5,000 Page Views
 Unique Visitors


    New Posts
 New Comments
 Deploys
  8.  18,600,000,000
 409,000,000 56,000,000
 68,000,000 5,000
 1 Page Views
 Unique

    Visitors
 New Posts
 New Comments
 Deploys
 WordPress Multisite
  9. MySQL server has gone away

  10. None
  11. –Nick Daugherty, WordPress VIP “Elasticsearch can represent a
 >200x improvement

    for queries
 against cold caches.”
  12. new WP_Query( array( 'tax_query' => array( array( 'taxonomy' => 'beer_style',

    'field' => 'slug', 'terms' => array( 'ipa', 'pale' ), ) ) ) );
  13. SELECT wp_term_taxonomy.term_id FROM wp_term_taxonomy INNER JOIN wp_terms USING ( term_id

    ) WHERE taxonomy = 'beer_style' AND wp_terms.slug IN ( 'ipa', 'pale' )
  14. SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = 'beer_style' AND term_id

    IN ( 2, 3 )
  15. SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (

    wp_posts.ID = wp_term_relationships.object_id ) WHERE 1=1 AND wp_term_relationships.term_taxonomy_id IN ( 5, 7 ) AND wp_posts.post_type = 'post' AND … GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 25
  16. SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships ON (

    wp_posts.ID = wp_term_relationships.object_id ) WHERE 1=1 AND wp_term_relationships.term_taxonomy_id IN ( 5, 7 ) AND wp_posts.post_type = 'post' AND … GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 25 SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_term_relationships wp_posts.ID = wp_term_relationships.object_id ) WHERE 1=1 wp_term_relationships.term_taxonomy_id wp_posts.post_type = … GROUP BY wp_posts.ID ORDER BY wp_posts.post_date LIMIT 0, 25
  17. SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID

    = wp_postmeta.post_id ) WHERE wp_postmeta.meta_key = 'hops' AND wp_postmeta.meta_value IN ( 'Amarillo', 'Calypso' )
  18. wp_postmeta.meta_value IN ( 'Amarillo', 'Calypso' ) SELECT wp_posts.* FROM wp_posts

    INNER JOIN wp_postmeta wp_posts.ID = wp_postmeta.post_id WHERE wp_postmeta.meta_key = wp_postmeta.meta_value
  19. Elasticsearch Optimizes for Search not Indexing

  20. Elasticsearch is Near Realtime

  21. Elasticsearch is Denormalized

  22. Elasticsearch uses Inverted Indices

  23. Elasticsearch uses Bitsets

  24. Elasticsearch Optimized for Searching

  25. WP_Query() is an API

  26. new WP_Query( array( 'tax_query' => array( array( 'taxonomy' => 'beer_style',

    'field' => 'slug', 'terms' => array( 'ipa', 'pale' ), ) ) ) );
  27. POST /es-index/post/_search { "query": { "filtered": { "filter": { "bool":

    { "must": [ { "terms": { "taxonomy.beer_style.slug": [ "ipa", "pale" ] } } ] } } } } }
  28. https://github.com/alleyinteractive/es-wp-query

  29. @HypertextRanch  me@xyu.io  xyu.io  xyu  Thanks!