InnoDB is a storage engine that can be used as a last-resort alternative to my MyISAM for databases experiencing slowness.
MySQL and MariaDB can store data using a handful of different storage engines. Almost all installations of Open Dental use the MyISAM storage engine. For large offices, MyISAM tables lock up for short periods of time when complex queries are being run (e.g., running the Recall List).
If experiencing slowness:
Before switching to InnoDB, the practice must have reliable IT. There are steps in the process that require the assistance of an IT professional that Open Dental support does not assist with.
MySQL version 5.5 is the minimum version required to convert the storage engine to InnoDB, however we strongly recommend the database is upgraded to MariaDB 10.5 before converting.
InnoDB users are responsible for their own backups.
Replication Warning: Offices using Replication will need to disable replication on all servers prior converting the database. It is suggested that the practice call and speak with a Replication Coordinator at Open Dental before this process is started.
These steps are for advanced technical users and will cause all databases within the database server to be converted to InnoDB format.
Review the backup/restore strategy before converting the database to ensure that users know how to validate restored backups. It will probably involve a dump to text file instead of copying files. Users will not be able to use Open Dental's built in backup/restore tool.
All of the tables in the database should have changed from three files per table (FRM, MYD, MYI) to two files per table (FRM, IBD). The database tables will now all be in a single file rather than separate files inside a folder.
If there are multiple databases on the server users will need to follow steps 6 and 7 for each database.
This database type will not function with most online backups. It is important to have a backup plan in place prior to making changes. A combination of backup methods is recommended, with at least one method being automated and incremental.
Backup methods vary between Hot and Cold. Hot Backups allow users to continue working while Cold Backups do not. Restores are considered Cold as users should not be using the Open Dental database while a restore is in progress.
Open Dental Backup Tool (Hot Method): Use the built-in Backup Tool to routinely make backups of the entire database. The tool can be used for backups only, not restores.
Data Directory Backup (Cold Method): This solution involves routinely making a copy of the entire data directory. This can require a fair amount of disk space.To back up using this method:
Mariabackup (Hot Method): MariaDB has a built-in utility to create hot, full and incremental backups. IT can research this on mariadb.com and utilize Windows task scheduler to create automated backups. An incremental backup can still create lag and we do recommend doing a full, nightly backup with whatever other method is chosen.
Binary Logs (Hot Method): This method requires specialized IT. The practice can use this method in conjunction with Mariabackup or a dump backup method to get up-to-the-minute recovery. This method requires shadow-copying the binary logs so they don't get set read-only and requires more in-depth knowledge of applying them to the backup for the additional data recovery.
SQL Dump Backup (Hot Method): This method will create a smaller total file size, in a .sql file format, which can be further compressed as a .zip file. While this method can be done while Open Dental is in use, it may cause slowness while running.
To back up using this method
Network Path Supplemental Backups (Hot Method): The Supplemental Backups utility in Open Dental can be used to create an automated, encrypted, zipped, database dump in a network or local location nightly. Supplemental Backups are not a complete copy of a database and should never be relied upon as the primary backup method.
Data Directory Restore (Cold): To restore a database using this method, follow the steps below.
SQL Dump Restore (Cold): To restore a database using this method, follow the steps below.
Alternative InnoDB Backup and Restore Methods Using Third-Party Software (Varies): A backup and restore can also be achieved using 3rd-party software such as MySQL Workbench or SQLYog. Methods may be Hot or Cold and can vary by software. Please reference their documentation for more information. Also see: Online Backups.