Drupal performance and MySQL transaction isolation levels

When running Drupal on MySQL, the transaction isolation level can significantly affect site performance. By default, MySQL uses the REPEATABLE READ isolation level, but Drupal recommends READ COMMITTED to avoid deadlocks and improve efficiency. On ruachost.com, you may see a warning message in Drupal’s Status report if the isolation level is not set correctly.

Why Isolation Levels Matter

• REPEATABLE READ (default in MySQL) → Can cause deadlocks and slow performance in Drupal.
• READ COMMITTED (recommended) → Reduces deadlocks and improves concurrency.
• Correct configuration ensures smoother database operations and faster page loads.

Determining the Current Isolation Level

1. Log in to your server via SSH.

2. Access MySQL with:

   mysql -u username -p

3. Run the command:

   SHOW variables WHERE variable_name LIKE "%isolation";

4. Check the output:

   • REPEATABLE-READ → Default, may cause issues.
   • READ-COMMITTED → Recommended for Drupal.

Changing the Isolation Level

For Shared Hosting Accounts

1. Open the settings.php file in your Drupal installation (sites/default/settings.php).

2. Locate the database connection array.

3. Add or update the line:

   'isolation_level' => 'READ COMMITTED',

4. Save the file and refresh the Status report page in Drupal.

5. The warning message should disappear.

Important Notes

• Always back up your database before making changes.
• Isolation level changes affect all databases on the server.
• Use READ COMMITTED for optimal Drupal performance.