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.

Join a talented and intelligent team. Become an expert in administering open source databases and operating systems, tuning the entire LAMP stack for optimal performance. Participate in active and lively open source communities. Work with and learn from experts in the field, building and optimizing the infrastructures and software behind successful and popular websites.

At the beginning of 2009, I was hired by Tag1 Consulting as Jeremy Andrews' full time partner. A decidedly questionable decision on his part, but a great change for me! I used to work at the Open Source Lab at Oregon State University and I am currently the sysadmin for drupal.org. Working at the OSL and drupal.org spoiled me, I'll be completely honest about that. I got used to working with interesting new technologies and consistently pushing the limits of my knowledge.

Description:
This patch can greatly reduce the number of database queries on every page load caused by URL path aliases. Groups of paths are cached together for each page and language allowing optimal cache retrieval of cached paths. Furthermore, a whitelist is built for uncached paths to intelligently limit the necessary database queries.

This patch was backported from Drupal 7.

Description:
This patch introduces caching for both nodes and users. While it will still help performance without using memcache, it is recommended that you enable the memcache module to cache this data in memory for optimal performance. Both tables can be manually flushed along with Drupal's standard caches by clicking the 'Clear cached data' button on the Performance administrator site configuration page.

Description:
This patch modifies Drupal core so it can be configured to send specified queries to a one or more MySQL replication slaves, helping to distribute the database load. This patch alone does not change where any queries are run.

An optional 'replication-slave.patch' utilizes the changes made in the first patch. It causes all temporary tables to be created on the slaves, sends all pager queries to the slaves, and sends some queries from node_load to the slaves.

We at Tag1 have used the Cacti Memcache templates many times. (http://dealnews.com/developers/cacti/memcached.html) They have become a staple for us and many others. However, we and one of our clients have had issues making them work for monitoring multiple Memcache instances (running on different ports) on the same host. It is possible to make this work with the vanilla templates, but it is a bit difficult.

There are some standard tools in every MySQL DBA's back pocket, these include maatkit, mysqlreport and mysqlsla. I have a new addition to this: statpack (http://www.markleith.co.uk/?p=21)

There are few options for sharing webroots among multiple webnodes. This is especially true for Drupal, which has a files directory that could be constantly diverging on each webnode. Usually people decide to go with NFSv3. This is what we do on drupal.org and most of my clients do at Tag1. Sadly, this is not really a decision on merit, but on a lack of other options. NFSv3 is pretty much like communicating with two cans and a string.

I've had the opportunity in the last few days to use quite a few cool little utilities that made my life considerably easier. I've known about most of them for awhile, but discovered some new tools or new uses for old tools and wanted to share two of them.