Taming Monster Drupal Queries with Akiban

Drupal is nearly universally deployed with MySQL as the backend database. We’ve all heard the NoSQL and NewSQL terms thrown around and there are many interesting new database products available. However, deploying these products is difficult and for a Drupal deployment used to running on MySQL, not always an option.

In this talk, we are going to discuss Akiban and how it can be used as a query accelerator in an existing MySQL environment. Akiban can be deployed as a regular MySQL slave so much of your operational knowledge can be put to good use with Akiban. Problematic queries for MySQL are then directed by Drupal to Akiban instead of to MySQL. This is similar to how queries are off-loaded to a read slave with MySQL replication. However, with Akiban, these problematic queries are not just off-loaded, they are usually executed 10-100 times faster.

This talk will first introduce Akiban including how to get it up and running as a MySQL slave. Then we’ll dive into specific examples of problematic queries in various Drupal deployments and how they can be redirected to Akiban. During this portion of the talk, we’ll also discuss actual customer deployments of Akiban for Drupal, including 1 deployment in the Acquia cloud.

Transcript

1. Taming Monster Drupal Queries with Akiban http://bit.ly/UX9JUE
2. Common Drupal Performance Solutions
3. Add Apache nodes (or nginx)
4. Caching (memcached, varnish et al)
5. MySQL Tuning
6. Bigger, Better Hardware
7. http://bit.ly/RP6y2z
8. How to Scale MySQL?
9. MySQL Replication?
10. Send Read Queries to Slave
11. http://bit.ly/VZUwBp
12. What if Queries Still Suck??
13. Temp Tables... ON DISK
15. What’s a Drupalist to Do?
16. Rewrite SQL on the Fly
17. Materialized Views
18. RAM Disk for MySQL Temp Tables
19. http://bit.ly/VUEM58
20. NoSQL?
22. NewSQL?
23. http://bit.ly/X5Slvt
24. What’s a Drupalist to do??
25. Diagnosis? Doomed! http://bit.ly/Y3LvN5
26. AKIBAN {AH - KEE - BAHN}
27. It’s clobberin’ time! http://bit.ly/XfTQGY
28. An entity management system with efficient SQL access and a RESTful API
29. Entities?
32. But I use MySQL?
33. People already use MySQL & replication. Why not deploy as a regular slave?
34. Akiban Adapter for MySQL
35. Reads Writes
36. Akiban drupal.org DB module
37. $databases  =  array  (    'default'  =>    array  (        'default'  =>        array  (            'database'  =>  'drupal',            'username'  =>  'drupal',            'password'  =>  'drupal',            'host'  =>  'localhost',            'port'  =>  '',            'driver'  =>  'mysql',            'prefix'  =>  '',        ),        'slave'  =>        array  (            'database'  =>  'drupal',            'username'  =>  'drupal',            'password'  =>  'drupal',            'host'  =>  'ec2-­‐23-­‐22-­‐112-­‐165.compute-­‐1.amazonaws.com',            'port'  =>  '15432',            'driver'  =>  'akiban',            'prefix'  =>  '',        ),    ), );
38. $query  =  db_select(    'search_index',      'i',      array('target'  =>'slave') ) -­‐>extend('SearchQuery') -­‐>extend('PagerDefault');
39. SELECT DISTINCT node.title AS node_title, node.nid AS nid, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, node.created AS node_created FROM node node INNER JOIN node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid WHERE (( ( node.status = '1' ) AND ( node.comment IN ( '2' ) ) AND ( node.nid >= '111' ) AND ( node_comment_statistics.comment_count >= '2' ) )) ORDER BY node_created ASC LIMIT 50 offset 0
42. Real-World Production Deployments http://bit.ly/WkQiEE
43. Gofobo @ Acquia
44. SELECT  u.name,              gd.*,              COUNT(gr.promo_nid)  AS  promotions,              SUM(gr.passes)            AS  passes,              SUM(gr.attended)        AS  attended,              watch.date_added        AS  watch FROM      gofobo_rsvp  gr              INNER  JOIN  gofobo_demo  gd                  ON  gd.id  =  gr.demo_id              INNER  JOIN  users  u                  ON  gd.uid  =  u.uid              INNER  JOIN  content_type_promotion  p                  ON  p.nid  =  gr.promo_nid              INNER  JOIN  content_type_screening  cts                  ON  cts.nid  =  p.field_screening_nid              LEFT  JOIN  gofobo_report_watch  watch                  ON  gd.uid  =  watch.uid WHERE    gr.released  <>  1              AND  cts.field_screening_date_value  BETWEEN              '2011-­‐06-­‐22T19:35:32'  AND  '2011-­‐12-­‐22T19:35:32' GROUP    BY  gr.demo_id ORDER    BY  passes  DESC LIMIT    0,  25
47. DOOR3 on AWS
48. SELECT DISTINCT node.nid AS nid, node.language AS node_language, node.title AS node_title, node.created AS node_created, node.type AS node_type, node.vid AS node_vid, users.uid AS users_uid, users.name AS users_name, users_extend.first_name AS users_extend_first_name, users_extend.last_name AS users_extend_last_name, node_comment_statistics.comment_count AS node_comment_statistics_comment_count, (SELECT Count(cio_customfield_f.id) FROM dru_flag cio_customfield_f WHERE cio_customfield_f.module = 'node' AND cio_customfield_f.type = 'like' AND cio_customfield_f.id = node.nid) AS cio_customfield_node_like_nl, node_counter.totalcount AS node_counter_totalcount, ... node_data_field_video.field_video_version AS node_data_field_video_field_video_version, FROM dru_node node INNER JOIN dru_users users ON node.uid = users.uid INNER JOIN dru_users_extend users_extend ON node.uid = users_extend.uid INNER JOIN dru_node_comment_statistics node_comment_statistics ON node.nid = node_comment_statistics.nid LEFT JOIN dru_node_counter node_counter ON node.nid = node_counter.nid LEFT JOIN dru_content_field_document node_data_field_document ON node.vid = node_data_field_document.vid LEFT JOIN dru_content_type_podcast node_data_field_podcast ON node.vid = node_data_field_podcast.vid LEFT JOIN dru_content_field_slideshare node_data_field_slideshare ON node.vid = node_data_field_slideshare.vid LEFT JOIN dru_content_field_video node_data_field_video ON node.vid = node_data_field_video.vid LEFT JOIN dru_content_field_doc node_data_field_doc ON node.vid = node_data_field_doc.vid WHERE node.status <> 0 ORDER BY node_created DESC LIMIT 0, 60
50. Non-Drupal Production Deployments
51. http://bit.ly/WLDqpQ Future Work
52. Running Drupal Entirely on Akiban
53. Akiban as a Service
54. Thank you! @posulliv posulliv.github.com http://bit.ly/WcfBsR