Drupal performance and MySQL transaction isolation levels

This article discusses a warning message that Drupal displays about performance and MySQL transaction isolation levels, and how to resolve it.

About the Drupal warning message

After you install Drupal, you may see the following warning message on the Status report page of the administration interface:

Transaction isolation level
REPEATABLE-READ
The recommended level for Drupal is "READ COMMITTED". See the setting MySQL transaction isolation level page for more information.

By default, MySQL uses the REPEATABLE READ isolation level. According to the Drupal documentation, however, this level can cause deadlocks on database tables, leading to poor site performance.

Determining the current transaction isolation level

To determine the current transaction isolation level enabled on your account, follow these steps:

1. Log in to your account using SSH.
2. At the command prompt, log in to MySQL by typing the following command. Replace username with your username:

   mysql -u username -p
   

3. At the prompt, type the following command:
   

   SHOW variables WHERE variable_name LIKE "%_isolation";
   

4. Examine the command output:
   

   • If the isolation level is set to REPEATABLE READ, you receive the following output:

     +---------------+-----------------+
     | Variable_name | Value           |
     +---------------+-----------------+
     | tx_isolation  | REPEATABLE-READ |
     +---------------+-----------------+
     1 row in set (0.001 sec)
     

   • If the isolation level is set to READ COMMITTED, you receive the following output:
     

     +---------------+----------------+
     | Variable_name | Value          |
     +---------------+----------------+
     | tx_isolation  | READ-COMMITTED |
     +---------------+----------------+
     1 row in set (0.001 sec)
     

Changing the transaction isolation level

The procedure to change the transaction isolation level depends on the type of hosting account you have.

Shared, reseller, and Turbo Web hosting accounts

To change the transaction isolation level on these types of hosting accounts, follow these steps:

1. Open the Drupal settings.php file in your preferred text editor.
   By default, this file is located in the sites/default directory.
2. In the settings.php file, locate the database connection array. This array is usually near the end of the file and looks similar to the following:

   $databases['default']['default'] = array (
     'database' => 'example_drup123',
     'username' => 'example_drup123',
     'password' => 'example-password',
     'prefix' => 'drom_',
     'host' => 'localhost',
     'port' => '3306',
     'isolation_level' => '',
     'driver' => 'mysql',
   );
   

3. Change the isolation_level line as follows:
   

     'isolation_level' => 'READ COMMITTED',
   

   For example, the database connection array should now look like:

   $databases['default']['default'] = array (
     'database' => 'example_drup123',
     'username' => 'example_drup123',
     'password' => 'example-password',
     'prefix' => 'drom_',
     'host' => 'localhost',
     'port' => '3306',
     'isolation_level' => 'READ COMMITTED',
     'driver' => 'mysql',
   );
   

4. Save your changes to the settings.php file and exit the text editor.
5. Log in to Drupal as the administrator, and then go to the Status report page. The transaction isolation level warning message should be gone.

VPS and Dedicated servers

How you change the transaction isolation level on a VPS or Dedicated server depends on whether or not you have root access:

• If you do not have root access to the server, please open a support ticket on the Customer Portal at https://my.a2hosting.com and we will assist you.
• If you do have root access to the server, you can change the isolation level yourself. To do this, follow these steps:
  1. As the root user, open the /etc/my.cnf file in your preferred text editor.
  2. Locate the [mysqld] section.
  3. Copy the following line, and then paste it into the /etc/my.cnf file in the [mysqld] section:

     transaction_isolation="READ-COMMITTED"

  4. Save your changes to the /etc/my.cnf file, and then exit the text editor.

  5. Type the following command to restart the MySQL service:
     

     systemctl restart mysqld

  6. The isolation level is now set to READ COMMITTED. To verify this, follow the procedure described in the Determining the current transaction isolation level section above.

More Information

• To view the official Drupal documentation, please visit https://www.drupal.org/documentation.
• For more information about transaction isolation levels and how they affect Drupal performance, please visit https://www.drupal.org/docs/getting-started/system-requirements/setting-the-mysql-transaction-isolation-level.