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

Scaling WordPress Queries With Elasticsearch

xyu
May 19, 2015

Scaling WordPress Queries With Elasticsearch

xyu

May 19, 2015
Tweet

More Decks by xyu

Other Decks in Technology

Transcript

  1. @HypertextRanch 

    [email protected]

    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 

    [email protected]

    xyu.io 

    xyu 
    Thanks!

    View Slide