DB Maintenance
Runs database optimization queries (OPTIMIZE TABLE for MySQL, VACUUM ANALYZE for PostgreSQL) on selected tables during cron runs.
db_maintenance
Install
composer require 'drupal/db_maintenance:^2.0'
Overview
DB Maintenance is a Drupal module that performs database maintenance operations by running optimization queries on selected database tables. For MySQL databases, it executes OPTIMIZE TABLE which repairs tables with deleted or split rows, sorts table indexes, and updates table statistics. For PostgreSQL databases, it runs VACUUM ANALYZE to reclaim storage occupied by deleted tuples.
The module is designed to work with tables that have high data turnover, such as cache tables, session tables, and watchdog logs. It supports configurable cron-based scheduling with frequency options ranging from every cron run to bi-monthly intervals. Additionally, a time interval restriction feature allows administrators to limit optimization to specific hours, which is useful for running maintenance during low-traffic periods.
Important considerations: MySQL's OPTIMIZE TABLE command locks tables during execution, which may affect site performance on busy sites. PostgreSQL's VACUUM (non-FULL) operation can run in parallel with normal database operations. For large installations, it is recommended to select only high-churn tables rather than optimizing all tables.
Features
- Runs OPTIMIZE TABLE query on selected MySQL/MariaDB database tables to defragment and repair tables with deleted or split rows
- Runs VACUUM ANALYZE on PostgreSQL tables to reclaim storage from deleted tuples and update statistics
- Configurable cron execution frequency: every cron run, hourly, bi-hourly, daily, bi-daily, weekly, bi-weekly, monthly, or bi-monthly
- Time interval restriction to limit optimization to specific hours (e.g., during low-traffic periods at night)
- Support for multiple database connections in multi-database Drupal installations
- Option to automatically optimize all tables or manually select specific tables
- Optional watchdog logging of optimization operations
- Manual optimization trigger via admin interface link
- Handles database table prefixes correctly
Use Cases
Optimizing cache tables on a high-traffic site
On sites with heavy traffic, cache tables frequently have rows inserted and deleted. Configure DB Maintenance to optimize cache_* tables daily during low-traffic hours (e.g., 02:00-04:00) by enabling the time interval feature. This helps maintain database performance without impacting users.
Maintaining watchdog table size
The watchdog table can grow significantly on sites with extensive logging. Select the watchdog table for weekly optimization to reclaim disk space from deleted log entries and maintain query performance when reviewing logs.
Multi-site database maintenance
For Drupal installations with multiple database connections, DB Maintenance can optimize tables across all configured databases. Each database appears with its own table selection list in the configuration form.
Scheduled overnight maintenance
Configure the module to run optimization between 01:30 and 02:30 AM by setting the time interval. Combined with a weekly frequency, this ensures maintenance runs during the lowest traffic period without manual intervention.
PostgreSQL vacuum scheduling
PostgreSQL databases benefit from regular VACUUM operations. While PostgreSQL has autovacuum, DB Maintenance provides Drupal-aware scheduling that can target specific tables and integrate with Drupal's cron system.
Tips
- Focus on tables with high data turnover (cache_*, sessions, watchdog) rather than optimizing all tables
- Use the time interval feature to schedule optimization during off-peak hours to minimize impact on users
- Check the watchdog logs periodically when logging is enabled to verify optimization is running as expected
- For MySQL, monitor the Overhead column in phpMyAdmin to identify tables that would benefit from optimization
- The module supports midnight crossover for time intervals (e.g., 23:00 to 01:00)
- Use the 'Optimize now' link to manually trigger optimization after making significant database changes
Technical Details
Admin Pages 1
/admin/config/system/db_maintenance
Configure database table optimization settings. Select which tables to optimize, set the optimization frequency, and optionally restrict optimization to specific time intervals.
Permissions 1
Hooks 1
hook_cron
Performs scheduled database table optimization. Checks if the configured time interval has passed since the last run and if the current time falls within any configured time restriction window before executing optimization.
Troubleshooting 4
Verify that Drupal cron is running at the expected frequency. The module can only run when cron executes. Also check if time interval restriction is enabled and verify the current server time falls within the configured window.
If you see log messages about tables not existing, the table may have been removed or renamed. Go to the configuration page and update your table selection to remove references to non-existent tables.
MySQL locks tables during OPTIMIZE TABLE operations. Schedule optimization during low-traffic periods using the time interval feature, or reduce the number of tables being optimized at once. Consider optimizing only high-churn tables like cache and sessions.
Ensure you have the 'administer db maintenance' permission. Clear Drupal caches if the form is not displaying correctly.
Security Notes 3
- The 'administer db maintenance' permission should only be granted to trusted administrators as it allows database operations
- The manual optimization link is protected by CSRF token to prevent unauthorized triggering
- Database credentials are never exposed through the module interface