Reason 4.2 Performance & Database Indexing Outline

Reason 4.2 includes major deep changes that eliminate some and simplify most queries in queries in Reason. In particular, we do three things:

Queries for navigation, unique names, and relationship names are heavily cached by default, speeding up almost every page.

The allowable relationship table is no longer included in most joins.

Many LIKE queries have been eliminated.

If you are upgrading Reason and want to take optimal advantage of these changes, we recommend a few database indexing changes. The details will differ a little depending on what indexing you've done previously, but here is an outline:

Run all the necessary upgrade scripts for Reason 4.2.

Set the new setting REASON_MAINTENANCE_MODE to true, and also set DISABLE_REASON_ADMINISTRATIVE_INTERFACE to true.

Indexing can be quite slow and any insert or update queries may lock your database, effectively bringing the site down during indexes. Without inserts or updates, selects should be fine.

Create a multi-column index on relationship for entity_a and type, then remove the unneeded entity_a index if it exists:

create index entity_a_type on relationship (entity_a,type);

drop index entity_a_index on relationship;

Create a multi-column index on relationship for entity_b and type, then remove the unneeded entity_b index if it exists:

create index entity_b_type on relationship (entity_b,type);

drop index entity_b_index on relationship;

Create a multi-column index on entity for type and state:

create index type_state_index on entity (type, state);

Drop the old type index on entity if it exists:

drop index type_index on entity;

Remove and recreate the unique name index on entity so that it covers the full character length:

drop index unique_name_index on entity;

create index unique_name_index on entity (unique_name);

If there is not an index for the state column, add it:

create index state_index on entity (state);

After you update your code, run upgrade scripts, and get these indexes setup, you will likely see relatively dramatic performance improvements, especially if you have a large Reason database.

At Carleton, overall load on MySQL is about 10% of what it was with Reason 4.1 on release day.