The DB Maintenance module can be used to optimize your MySQL database tables. Depending on the type of database you are running, the module allows you to use a function called OPTIMIZE TABLE, which troubleshoots and then optimizes various errors in your MySQL tables. For MyISAM tables, the optimize table will repair your database tables if they have deleted rows. For BDB and InnoDB types of tables the function will rebuild the entire table. You can use this module in tandem with phpMyAdmin to determine if you do or do not need to optimize your database tables. The benefit of this module is that it allows you to keep your database optimized and defragmented, similar to keeping your computer hard drive optimized and defragmented so that it runs faster, and you can do all this from the Drupal administrative interface.
The project page where you can download the module is here: http://drupal.org/project/db_maintenance.
Download the module tar.gz and extract it to your desktop. Then, upload the files through FTP, or upload and extract using a cPanel utility if your host provides this. The module should go in your /sites/all/modules directory.
Once you upload and extract the module folder, enable the module on your modules admin page and save your configuration. We'll use the version that's recommended for Drupal 6.x, which is 6.X-1.1. You can try out the beta version, but you should not run this beta version on a production level website unless you've tested it sufficiently in a sandbox environment.
Once you save your module configuration, you'll notice that the module adds a link to its settings and configuration page under your main Site configuration section. Go to Site configuration | DB maintenance to access the configuration admin screen for the module. The DB maintenance screen will contain a checkbox at the top allowing you to log OPTIMIZE queries. If you check this box, your watchdog log entries module will log all table optimization entries and give you detailed information on the tables that were optimized.
At the time of writing this book, the 1.1 version of the DB Maintenance module contained bugs that caused glitches with the method of adding this module's queries to the recent log entries or prevented this entirely. You may also experience these glitches. The module's developers are aware of the issues because they have been posted to the issue queue at http://drupal.org/ on the module project page.
Let's go ahead and check this box. You can then select the frequency with which you would like to run the optimization. The choices are daily, Run during every cron, Hourly, Bi-Hourly, Daily, Bi-Daily, Weekly, Bi-Weekly, Monthly, and Bi-Monthly.
You can also click on the Optimize now link to force the optimization to occur immediately without scheduling in advance. We'll click on this link for the purpose of this demo, but in future you may want to schedule the optimization. We'll then run a cron job through the Status report, or a module such as Poormanscron, and the tables will be optimized.
Next, you can select the tables in your Drupal database that you want to optimize. A nice feature of this module is that it allows you to multi select database tables, only select a few tables, or just one table. This gives you the same flexibility and functionality as your phpMyAdmin tool, but you can run everything from within your Drupal interface. It's like a phpMyAdmin lite version right in your Drupal site. This is a preferred option for those developers who may not have immediate access to a client's phpMyAdmin or a host's database management utility.
Choose a selection of tables that you want to optimize, or select all the tables. For this demo I'm going to optimize all of my content type tables, so I'll select all of those. I'll also optimize my block tables:
blocks blocks_roles content_type_blog content_type_book content_type_forum content_type_page content_type_photo content_type_poll content_type_story content_type_webform
Once you've selected the tables you want to optimize, click on the Optimize now link.
As with any module or optimization enhancement that you make to your Drupal site, it is good practice to run a full backup of your MySQL database before performing any maintenance, including optimizing tables using the DB Maintenance module. This way you will have a full backup of your data if you run into any issues that the module could potentially create. It's better to play it safe and perform the backup first.
Once you click on the Optimize now link, you should receive a message notifying you that the Database tables are optimized.
This concludes our discussion and walkthrough of using the DB Maintenance module. Let's now turn to the Boost module and use it to speed up our site page and content loads.
Was this article helpful?