How to Optimize or Repair a Drupal Database

Introduction: MySQL Database Maintenance & Drupal 8

This Tutorial will show how to optimize a MySQL database for better performance running a Drupal 8 website & how to repair damaged MySQL tables with analytics.

Professional programmers use phpMyAdmin for MySQL database administration on Linux & Windows web servers with Drupal 8 modules available for these functions.

After installing the DB Maintenance & OptimizeDB modules for Drupal 8, developers can launch MySQL diagnostics, repair, & optimization scripts securely from D8 admin.

Step One: Install the Drupal 8 Database Repair Modules

Most open source web developers & programmers are familiar with the use of phpMyAdmin for managing MySQL database installations on a LAMP stack.

The DB Maintenance and OptimizeDB modules for Drupal 8 give administrators the ability to run phpMyAdmin commands through the CMS rather than the web server.

Download - Required Files:

To install the modules, use FTP, Drupal 8 administration, Drush, or Composer according to preference. Navigate to: /admin/modules/install

How to Optimize or Repair a Drupal Database

Upload the files to the CMS:

How to Optimize or Repair a Drupal Database

When the installation has completed successfully, navigate to /admin/modules & enable both of the modules. Save the settings & the installation is complete.

How to Optimize or Repair a Drupal Database

After installation, the DB Maintenance & OptimizeDB modules can be used regularly with Drupal 8 websites to diagnose problems, repair tables, or debug other issues.

Step Two: Run Diagnostics & Repair with DB Maintenance

The DB Maintenance module operates on every cron run, analyzing a Drupal 8 database in production for errors, splits, or deleted files & automatically repairing them.

To use the DB Maintenance module, navigate to: /admin/config/system/db_maintenance

How to Optimize or Repair a Drupal Database

The module administration section states:

DB maintenance performs an optimization query on selected tables.

For MyISAM tables, OPTIMIZETABLE repairs a tableif it has deleted orsplitrows, sorts tableindexes, and updates table statistics. For BDB andInnoDB, OPTIMIZE rebuilds the table. OPTIMIZE works best ontableswithlarge deletions (e.g. cacheor watchdog), however MySQL will reuseoldrecord positions, therefore in most setups, OPTIMIZETABLEis unnecessary unless you just like defragmenting.

Note: MySQL locks tables during the timeOPTIMIZETABLEis running.

The Overhead columnin phpMyAdmin's database view is the most common way to determine the need of an OPTIMIZE TABLE query. It essentially shows the amount of disk space you would recover by running an optimize/defragmentation query.

For PostgreSQL tables, VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to VACUUM periodically, especially on frequently-updated tables.

In order to configure the module, choode “Daily” for the operation time table and select the button for “Optimize all Tables”. Then save the settings.

To do an immediate database optimization, click the button “Optimize now” and wait for the operations to complete. A success message will display upon completion.

Step Three: Run Diagnostics & Repair with OptimizeDB

The OptimizeDB module is one of the most advanced for MySQL database management with Drupal 8. The modules features are:

  • Ability to clean cache_from in administrative page or do it by cron.
  • Ability to optimize all database’s tables and display its sizes.
  • Configuration of notification about necessity to perform maintenance tasks.
  • Perform check and repair operation with tables.
  • Prevent crashing tables when perform all maintained actions.

Additional Features:

  • Works with both MySQL and PostgreSQL Database Formats
  • Ability to execute CLI operations with Drush

To use the OptimizeDB module, navigate to: /admin/config/development/optimizedb

How to Optimize or Repair a Drupal Database

In this section, Drupal 8 administrators can execute a command to “optimize tables” or to set notifications for the database when it requires repair or attention for errors.

The OptimizeDB module also includes a link to view all of the tables installed on a Drupal 8 website. This can be helpful in programming, development, & debugging.

Step Four: Check & Repair Tables with OptimizeDB

To perform advanced diagnostic & repair operations on tables in a MySQL or PostgreSQL database on Drupal 8, use the OptimizeDB options in the next tab.

How to Optimize or Repair a Drupal Database

In this section, administrators can select each table in a Drupal 8 database manually, then perform operations to check, repair, & optimize the table individually.

  • Navigate to: /admin/config/development/optimizedb/list_tables

This section also shows the total size for each installed table in MB which can be helpful for web developers & programmers building new modules for the Drupal 8 platform.

Step Five: Use phpMyAdmin Tools for MySQL Status

Other tools for Drupal 8 database optimization can be accessed directly using phpMyAdmin or phpPgAdmin. Login to cPanel & click on the icon to launch the app.

How to Optimize or Repair a Drupal Database

Once logged in to phpMyAdmin, select the Drupal 8 database from the MySQL installations in the left-sidebar menu. Then navigate to the “Status” tab.

How to Optimize or Repair a Drupal Database

Under the “Status” section in phpMyAdmin, Drupal 8 administrators can access real-time information about connections, data transfers, & errors in operations.

How to Optimize or Repair a Drupal Database

The diagnostics for MySQL Databases available under the tools offered by phpMyAdmin provide vital feedback to errors that need corrected in production.

Conclusion: Access phpMyAdmin Tools in Drupal 8

The combination of the DB Maintenance & OptimizeDB modules for Drupal 8 with the MySQL diagnostic, analytics, & metering tools available in phpMyAdmin is excellent.

Use the DB Maintenance module to schedule continual monitoring & repairs. OptimizeDB offer the ability to check & repair MySQL tables individually.

phpMyAdmin has the most advanced suite of real-time MySQL analytics for Drupal 8 website publishers, but will need to be accessed from the web server or via cPanel.

Check out these top 3 Drupal hosting services:

Was this article helpful?