Using InnoDB as the Storage Engine for MySQL in Cascade Server
Wednesday, November 30th, 2011 at 3:00pm -- Adam Griffis
This is a technical post addressing instances using MySQL as their database vendor. Readers who are not administrators or administrators whose systems use SQL Server or Oracle should skip it.
If you’ve read the Database Configuration Guide for MySQL (and if you’re an admin, hopefully you have) you may have noticed this line: “IMPORTANT: Make sure that your database is configured to use the InnoDB storage engine.” Unfortunately, despite that insistent all-caps word “IMPORTANT”, it’s easy to miss a single line in a lengthy configuration guide, and installation is possible to complete even if this step is not taken. There is an issue on file to make the InnoDB storage engine a precondition for server start-up, so fixing your database engine should be done sooner rather than later. In the meantime, what is the InnoDB engine, and why should you make sure your database is set up to use it?
InnoDB has two important features that the other major storage engine for MySQL, MyIsam, does not: row-locking and referential constraints (also known as foreign keys). Row locking, in contrast to table locking, means that only accessed records in a table will be locked, preventing other users from viewing or editing them. For MyIsam, all records for a table are locked every time a single record in that table is accessed. This means that as long as Cascade is reading/writing to any given page, file, block, format or template for a user, no other user can be served any page, file, block, format or template. For Cascade instances with a low user count, the performance impacts will probably be small, but for instances with high concurrency, the performance effects can be significant.
The second, and arguably much more important, feature of InnoDB is that it allows referential constraints. Referential constraints allow database integrity to be maintained for rows from a given table that reference other rows, either within different database tables or the same table. When the referenced row is deleted, these constraints can automatically delete the referencing row, delete just the reference or stop the deletion entirely. This means that when a structured data block references a file for one of its asset choosers, when that file is deleted then the association will be automatically cleared out. If this does not happen, then the structured data block cannot be rendered.
Most references in Cascade don’t solely use constraints to enforce data integrity; there is usually procedural code which enforces the integrity and the constraint is merely a backup. However, there are currently over 300 referential constraints and occasionally bugs do mean that our code doesn’t enforce integrity. In these cases, referential constraints become necessary. This means that if your database is using MyIsam as a storage engine, then the longer this continues, the more potentially crippling database inconsistencies may pop up.
So how do we go about verifying that our database is using InnoDB for all of its tables? First, to verify that all existing tables are using InnoDB, run the following commands in the MySQL command line:
mysql> use cascade;(where cascade is the name of your Cascade database)
Database changed
mysql> show table status;
You will see results that look something like this:
For every row, make sure the value for the “Engine” column is InnoDB.
The next step is to make sure that your mysql configuration file has the option “default-storage-engine=innodb” specified under the mysqld section. The MySQL configuration file is named my.cnf (or my.ini for Windows) and is located in the installation directory of MySQL.
If you do find that any of your tables are not using InnoDB as its storage engine, please contact support. Custom scripts are required to fix data inconsistencies, change the storage engines and restore referential constraints. If you’d like to skip the hassle of managing your database entirely, please feel free to contact us regarding a hosted license.
You Might Also Enjoy
Category
- Resources