The fourth section of the book will examine database administration for a Drupal powered website.
This chapter will note that Drupal is described as being “database agnostic”, as the code strives to not depend on the underlying database that is being used. It will review the database abstraction layer, and will talk about the currently supported databases. It will detail how in spite of this noble aim, MySQL is still strongly favored. It will compare MySQL support with PostgreSQL support. Finally, it will offer a preview of the database layer rewrite that is happening for Drupal 7, detailing how this may finally make Drupal database agnostic.
This chapter will primarily compare MyISAM and InnoDB. It will look at Drupal's history of being designed for MyISAM, and talk about some of the Drupal-specific pitfalls with using InnoDB. It will then explain the many advantages to using InnoDB, presenting this as currently being the only serious option for large high traffic websites using MySQL. This chapter will also briefly look at some of the up and coming MySQL storage engines currently being developed.
This chapter will first explain the importance of monitoring your database server. It will then present several useful tools for monitoring MySQL, including mytop and innotop. It will also discuss MySQL's built in reports, including SHOW FULL PROCESSLIST, SHOW GLOBAL STATUS, and SHOW INNODB STATUS. This chapter will also discuss MySQL's various logs.
This chapter will build upon what was learned in the previous chapter, detailing how to use that knowledge to isolate and fix performance bottlenecks. It will take a lengthy look at the mysqlreport perl script, explaining how it summarizes many of the reports discussed in the previous chapter, and how to use this tool to tune your server for optimal performance. It will highlight the MySQL configuration options that most affect Drupal performance.
This chapter will take a closer look at MySQL queries. It will examine the mysqlsla perl script, detailing how it is used to quickly track down the database queries that are wasting the greatest amount of resources. It will then explain how to determine why a query is performing poorly. It discuss how some queries can be optimized by adding indexes, while also looking at the impact of adding too many indexes to your tables. It will offer an in depth look at how MySQL indexes work, comparing indexes in MyISAM versus InnoDB. It will also review when to use multiple simple queries instead of complex queries. Throughout these chapter, specific Drupal examples will be provided.
This chapter will define MySQL replication, explaining how it works and how it can be used to improve a Drupal website's performance and scalability. It will explore patches that have been deployed on Drupal.org to send some database queries to a slave server, and the rest to the master server. It will examine the idea of using Master-Master replication, arguing against this as a means for scaling Drupal websites. It will also briefly look at the concept of sharding, and look at plans in Drupal 7 for potentially supporting these advanced scalability features, reviewing the limitations imposed by Drupal's design in Drupal 5 and Drupal 6.