Drupal db_maintenance Module: Automated Database Optimization & MySQL Performance

The performance of a Drupal site depends largely on the health of its MySQL database. The db_maintenance module provides an elegant solution to automatically maintain table optimization, reduce fragmentation, and ensure optimal performance.

1. What is the db_maintenance Module?

The db_maintenance module (drupal.org/project/db_maintenance) is a contrib module for Drupal that automatically optimizes MySQL database tables.

🎯 Key Features

Automatic optimization: Runs via Drupal cron
Custom selection: Choose which tables to optimize
MyISAM and InnoDB support: Compatible with both engines
Built-in monitoring: Detailed logs in watchdog

2. How OPTIMIZE TABLE Works

The module uses the SQL command OPTIMIZE TABLE. Behavior varies by MySQL storage engine.

MyISAM Tables

Repairs deleted or fragmented rows
Reorganizes indexes
Updates MySQL optimizer statistics
Defragments and reclaims disk space

InnoDB Tables

Rebuilds the table
Reorganizes internal data structure
Compacts disk space
Improves data access performance

⚠️ Important: Table Locking

OPTIMIZE TABLE can cause temporary table locking during the operation. For large tables, this can take several minutes. Run optimizations during off-peak hours.

3. Installation & Configuration

Install via Composer

composer require drupal/db_maintenance drush en db_maintenance -y

Configuration Steps

Go to Configuration → System → Database Maintenance (/admin/config/system/db_maintenance)
Select tables to optimize
Configure execution frequency (every cron or every N crons)
Enable logging
Save configuration

4. Which Tables to Optimize Priority?

📦 Cache Tables

cache_* (cache_bootstrap, cache_data, cache_render, etc.)
Highly fragmented due to frequent flushes and expirations

📊 Logs & History Tables

watchdog
Drupal system logs, frequent rotation
sessions
User sessions, regular cleanup
votingapi_*
Voting data (if Voting API installed)

5. Monitoring & Execution Logs

The module logs each optimization in Drupal's watchdog:

db_maintenance  24/10/2025 - 10:59  Optimized votingapi_vote table in db database. db_maintenance  24/10/2025 - 10:59  Optimized watchdog table in db database.

Access logs

Web interface: Reports → Recent log messages (/admin/reports/dblog)
Drush: drush watchdog:show --type=db_maintenance

6. Best Practices

✅ Schedule optimizations

Run optimizations during off-peak hours (night, weekends) to avoid user impact.

✅ Backup before optimization

Always backup your database before optimizing critical tables.

⚠️ Avoid on large tables

For multi-GB tables, optimization can take hours and lock the table. Consider alternatives (partitioning, archiving).

7. FAQ

❓ How often to optimize tables?

Depends on site load. High-traffic sites: weekly for cache/logs. Medium sites: monthly. Check fragmentation with SHOW TABLE STATUS.

❓ Does optimization impact site performance?

Yes, temporarily. OPTIMIZE TABLE locks the table during operation. Always schedule during off-peak hours.

❓ Compatible with Drupal 8, 9, 10, 11?

Yes, db_maintenance is compatible with Drupal 7, 8, 9, 10, and 11. Check the module page on drupal.org for specific versions.

Conclusion

The db_maintenance module is an essential tool for maintaining Drupal database health and performance. By automating MySQL table optimization via cron, it reduces fragmentation, reclaims disk space, and ensures optimal response times.

🚀 VOID supports your Drupal maintenance

Need to optimize your Drupal site performance? Our expert Drupal team in Casablanca supports you in auditing, optimization, and maintenance of your web infrastructure.

Contact our Drupal experts →