I’ve faced a lot of skepticism (rightfully so) over my Materialized View module, which I’m pushing for inclusion in Drupal 7 as a solution to the overhead of table-per-field storage in Field API, as well as many other scalability issues.

I could have responded with contrived benchmarks, but I wanted real results. With Drupal 7 far from release and large projects on Drupal 7 even farther from release, I decided to rewrite MV for Drupal 6 and install it on Drupal.org to replace some of the worst queries.

I used MV-based tables to rewrite #1 and #4 of Drupal.org’s slowest, most common queries. This required the creation (and indexing) of two materialized view tables. The rewritten queries went live on Saturday at around 19:00 UTC.

The drop in load is visible at 19:00 on Cacti graphs from Drupal.org’s DB2:

Temporary disk tables, one of the worst causes of scalability issues, showed a small drop:

The execution plans of SELECT queries also improved. “Read next,” an indicator of table-scan behavior, dropped significantly following the MV switch-over:

And a commenter below requested the standard “load average” stats from the server:

If anything, the results are pessimistic because MV indexed data (a one-time process) from 19:00 UTC through Sunday morning. You can see the effect of MV indexing on load by comparing Sat 12:00-19:00 and Sun 04:00 – 05:00, where MV was not indexing, to surrounding times, where MV was indexing.

This indexing load is visible on the “Volatile Queries” graph:

Weekday vs. Weekday (scale change)

The most interesting graphs, in my opinion, are some Friday versus Monday ones: