Scaling WordPress Queries With Elasticsearch

Transcript

1. @HypertextRanch 
   
   me@xyu.io 
   
   xyu.io 
   
   xyu 
   Scaling WordPress Queries With Elasticsearch
   Xiao Yu / Automattic
   

   View Slide

2. 
   
   

   View Slide

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

   View Slide

4. 
   18,600,000,000

   409,000,000
   Page Views

   Unique Visitors
   

   View Slide

5. 
   18,600,000,000

   409,000,000
   56,000,000
   Page Views

   Unique Visitors

   New Posts
   

   View Slide

6. 
   18,600,000,000

   409,000,000
   56,000,000

   68,000,000
   Page Views

   Unique Visitors

   New Posts

   New Comments
   

   View Slide

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
   

   View Slide

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
   

   View Slide

9. 
   

   View Slide

10. MySQL server has gone away
    

    View Slide

11. View Slide

12. –Nick Daugherty, WordPress VIP
    “Elasticsearch can represent a

    >200x improvement for queries

    against cold caches.”
    

    View Slide

13. new WP_Query( array(
    'tax_query' => array( array(
    'taxonomy' => 'beer_style',
    'field' => 'slug',
    'terms' => array( 'ipa', 'pale' ),
    ) )
    ) );
    

    View Slide

14. 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' )
    

    View Slide

15. SELECT
    term_taxonomy_id
    FROM
    wp_term_taxonomy
    WHERE
    taxonomy = 'beer_style' AND
    term_id IN ( 2, 3 )
    

    View Slide

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
    

    View Slide

17. 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
    

    View Slide

18. 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'
    )
    

    View Slide

19. 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
    

    View Slide

20. Elasticsearch Optimizes for Search not Indexing
    

    View Slide

21. Elasticsearch is Near Realtime
    

    View Slide

22. Elasticsearch is Denormalized
    

    View Slide

23. Elasticsearch uses Inverted Indices
    

    View Slide

24. Elasticsearch uses Bitsets
    

    View Slide

25. Elasticsearch Optimized for Searching
    

    View Slide

26. WP_Query() is an API
    

    View Slide

27. new WP_Query( array(
    'tax_query' => array( array(
    'taxonomy' => 'beer_style',
    'field' => 'slug',
    'terms' => array( 'ipa', 'pale' ),
    ) )
    ) );
    

    View Slide

28. POST /es-index/post/_search
    { "query": { "filtered": { "filter": {
    "bool": {
    "must": [
    {
    "terms": {
    "taxonomy.beer_style.slug": [
    "ipa",
    "pale"
    ]
    }
    }
    ]
    }
    } } } }
    

    View Slide

29. https://github.com/alleyinteractive/es-wp-query
    

    View Slide

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

    View Slide