Scaling WordPress Queries With Elasticsearch

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. 
10. MySQL server has gone away
12. –Nick Daugherty, WordPress VIP “Elasticsearch can represent a
 >200x improvement for queries
 against cold caches.”
13. new WP_Query( array( 'tax_query' => array( array( 'taxonomy' => 'beer_style', 'field' => 'slug', 'terms' => array( 'ipa', 'pale' ), ) ) ) );
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' )
15. SELECT term_taxonomy_id FROM wp_term_taxonomy WHERE taxonomy = 'beer_style' AND term_id IN ( 2, 3 )
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
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
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' )
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
20. Elasticsearch Optimizes for Search not Indexing
21. Elasticsearch is Near Realtime
22. Elasticsearch is Denormalized
23. Elasticsearch uses Inverted Indices
24. Elasticsearch uses Bitsets
25. Elasticsearch Optimized for Searching
26. WP_Query() is an API
27. new WP_Query( array( 'tax_query' => array( array( 'taxonomy' => 'beer_style', 'field' => 'slug', 'terms' => array( 'ipa', 'pale' ), ) ) ) );
28. POST /es-index/post/_search { "query": { "filtered": { "filter": { "bool": { "must": [ { "terms": { "taxonomy.beer_style.slug": [ "ipa", "pale" ] } } ] } } } } }
29. https://github.com/alleyinteractive/es-wp-query
30. @HypertextRanch  me@xyu.io  xyu.io  xyu  Thanks!