I began porting the dba module to Drupal 7 as an opportunity to learn more about the new database abstraction layer. In this upcoming Drupal release, our database layer is now built upon the PHP Data Objects (PDO) data-access abstraction layer, introducing new syntax and symantecs into writing queries with Drupal. A powerful new query builder makes it possible to easily write queries that run on any database type supported by Drupal, currently including MySQL, PostgreSQL and SQLite.

The dba module was always intended to be a useful tool for any database administrator, and toward that end I have recently merged in mysqlreport functionality which utilizes MySQL's server statistics to offer an overview of server health and performance. As an enhancement, I also merged in the knowledge shared in this earlier Tag1 Consulting article, so now problem areas detected in the mysqlreport output are automatically highlighted along with some useful help text offering tips on better tuning your database.

Description:
The drupal_lookup_path function unnecessarily counts all rows of the url_alias table to determine if any aliases are defined. This can be expensive on a transactional database such as MySQL with the InnoDB Storage Engine or PostgreSQL when a website has a lot of aliases. This patch modifies the query to always only return one row, possible because any existing pid will be greater than 0. If no aliases are defined, it will return 0.

Status:
This patch has not been merged into any release of Drupal.

Patch

I have completed a rough draft of the first chapter of "Drupal Performance and Scalability". The first chapter of this online book is divided into four sections, the first of which focuses on the importance of fully defining your performance and scalability goals, helping you to identify what you need to accomplish and how to set concrete and attainable goals. The second section discusses monitoring and measuring your ongoing progress, helping you decide what you need to monitor, and how to monitor it. The third section stresses the importance of making regular backups, discussing what needs to be backed up, and offering example scripts for backing up your entire website, including the database. Finally, the fourth section takes an in depth look at using revision control tools to manage your website, providing useful recipes showing how Git can track changes to your website, helping you update to new releases and push those updates into production.

It is important to realize that this is a rough draft, and as such it may contain spelling or grammatical errors, it may be missing key points, and the writing style may not be very polished. However, the book has to start somewhere, and this is the first step toward the end goal of publishing a useful and freely available online resource. I welcome all criticisms, suggestions and feedback. If you find errors in the text or have specific comments, you can help with this writing project by posting your feedback on the appropriate page. The current status of this project is tracked here.

In previous search benchmarks, I utilized random content generated with Drupal's devel module. In these latest benchmarks, I used an actual sanitized copy of the Drupal.org community website database, with email addresses and passwords removed. The first tests were intended to confirm that Xapian continues to perform well with large amounts of actual data. Additional tests were performed to measure the effect of various MySQL tunings and configurations. The following data was derived from several hundred benchmarks run on an Amazon AWS instance over the past week using the SearchBench module.

These tests confirm that Xapian continues to offer better search performance than Drupal's core search module. Contrary to popular belief, the data also shows that using the InnoDB storage engine for search tables significantly outperforms using the MyISAM storage engine for search tables, especially when your database server has sufficient RAM. The data also confirms that allocating additional RAM for MySQL's temporary tables can also improve search performance.

Tag1 Consulting is focused on improving Drupal's performance and scalability. We also believe that when information is freely shared, everyone wins. Toward these ends, we are working on an online book titled, "Drupal Performance and Scalability". The book is divided into five main sections, Drupal Performance, Front End Performance, Improved Caching and Searching, Optimizing the Database Layer, and Drupal In The Cloud. The book is primarily aimed toward users running Drupal on the LAMP stack, with chapters applicable to everything from low-end shared hosts to large-scale multi-server installations.

By publishing on-line, we aim to encourage you to participate in the book writing process as an editor and a technical reviewer. You will currently find the book's complete outline online, along with descriptions of each planned section and chapter. As the book evolves, it will continue to be updated online in real time. We encourage you to post comments with suggestions, critical feedback, grammatical corrections, or anything else relevant to our ongoing effort.

SearchBench has received a couple of useful updates since yesterday's initial cloud tests. It can generate search queries based on actual content, and it can export search benchmark results. In gaining these features, it is now possible to use SearchBench to perform some actual performance comparisons.

Once again I set up these tests on an extra large EC2 instance. I still have not performed any tuning, and I continue to test Drupal 5 core search with Xapian search. My initial benchmarks show that Xapian offers a very significant 6x+ performance advantage over Drupal's core search when a given search query actually returns results. In addition, Xapian is able to index a large site in about a 3rd the time of Drupal 5's built in search. Read on for actual benchmark results and graphs.

There have been some ongoing scalability issues affecting Drupal.org's built in search functionality for some time now. Less interested in outsourcing search to a big black box such as Google, I spent some time helping clean up the Xapian module, making it possible to completely replace Drupal's built in SQL-powered search functionality with a Xapian powered engine. With the basic search functionality complete, there was still a need to actually compare the performance of the two solutions.

Toward this goal, over the weekend I launched a new project called SearchBench, a Drupal module for benchmarking Drupal's search performance. As the module evolves, I hope it will prove extremely useful for comparing the performance and scalability of the many free and open source search options available to Drupal powered websites.

This article provides a comparison between the MyISAM and InnoDB storage engines for MySQL. InnoDB is commonly considered to perform worse than MyISAM, but this article aims to dispel this myth by describing the differences between these engines and what makes InnoDB a good fit for many database needs.

In addition, a look at when it is better to use MyISAM and a case study of the drupal.org site provide insight for determining which engine is best for a given situation.

We are honored to have received praise from Drupal founder Dries Buytaert, who said in his blog (emphasis added):

It is not always easy to scale Drupal -- not because Drupal sucks, but simply because scaling the LAMP stack (including Drupal) takes no small amount of skill. [...] Either you can do all of the above yourself, or you outsource it to a company that knows how to do this for you. Both are non-trivial and I can count the number of truly qualified companies on one hand. Tag1 Consulting is one of the few Drupal companies that excel at this, in case you're wondering.

I'm pleased to announce that Tag1 Consulting has partnered up with MySQL AB to offer an online presentation titled "Achieving Optimal MySQL Performance For Drupal". Aiming to provide a better understanding of how to properly monitor and tune your MySQL database, the online Webinar will take place on Thursday, January 31st, 2008, at 16:00 UTC (11:00 am EST). The presentation will last 45 minutes, followed by 15 minutes for questions and answers.