Tools > Storage Manager > Convert Database

If you use the CLI, the sn_fpt_convert script in StorNext 6 allows you to split a global datafile into separate files for each table as, well as enabling compression for those tables.

Note: In a future release, if the conversion has not been performed, it will be performed automatically at upgrade time. Running the sn_fpt_convert script tool on StorNext 6 allows you to avoid performing the conversion at upgrade time. It is strongly recommended that you manually perform the conversion while running StorNext 6, and not wait for a future upgrade.

If you have a global datafile, after you run the script, you will have data split into separate files for each table. In addition, duplicate indexes will be removed, and the data will be compressed. The script takes advantage of Percona backup to perform this operation with minimal Storage Manager downtime, but it does require sufficient space on disk. The time for conversion depends on the size of the database, because the process must re-write data into new, compressed data files in such a way that Storage Manger can continue running.

Note: For StorNext 6, running the script is optional. All Storage Manger operations function properly, regardless of file-per-table setting. You may run the script and convert your system to reduce disk usage and avoid running the conversion during a future upgrade. Separate per-table files also allow greater flexibility for Storage Manager to perform maintenance and optimization operations in the future.

File-per-table Setting	Applicable StorNext Release	Run the Conversion Script?
Global datafile (file-per-table=off)	Systems created using releases prior to StorNext 5 release 5.2.x.	Optional
File-per-table without compression (file-per-table=on)	Systems created using StorNext 5 release 5.2.x through StorNext 5 release 5.4.x.	No
File-per-table with compression (file-per-table=on)	Systems created using StorNext 6 (and later).	No

The following space is required by the MySQL file-per-table conversion process.

• 3 times the size of the database, which includes:
  • Space for the existing database
  • Space for the binary backup
  • Space for the instance created by the backup
• Space for the MySQL dump output, approximately 20% to 40% of the global tablespace file size, depending on the size of the actual data, 1 times the size of the database.

Note: It takes less than four times the size of the ibdata file to perform the conversion process.

Note the following considerations:

• The conversion requires a Storage Manager license.
• The conversion requires Storage manager Admin Access Control in the GUI.
• The conversion only applies to systems created using releases prior to StorNext 5 release 5.2.x.
• Systems created using StorNext 5 release 5.2.x or later do not need to be converted.
• After you confirm that you want to convert, theGUI runs the conversion process in the background.
• Storage Manager and the database are restarted multiple times throughout the conversion process.
• When privileged/admin users log in to systems where the database has not been converted, they are automatically directed to the conversion page.

To convert the database using the GUI:

1. On the Convert Database page, click Convert to perform the database conversion. Alternatively, check the Postpone Conversion check-box to perform the database conversion at a later date. You are prompted to confirm the conversion.
2. If you chose to convert now, click Yes to perform the database conversion, or No to cancel the operation and return to the Convert Database page. If you click Yes, a dialog appears informing you that the database conversion has initiated.
3. Click Remove Conversion Working Directory to remove the directory where the copy of the pre-conversion backup is located. The directory is not needed after the conversion is complete and the database is started. You are prompted to confirm the removal of the directory.
   • Click Yes to remove the directory, or No to cancel the operation and return to the previous page.

Note: Until the system is converted or you postpone the conversion, you cannot navigate to other GUI pages. This occurs each time privileged/admin users log in if the database conversion is required.

Note: If the Space Requirements are not met, the system does not allow you to perform the conversion and the Convert button is disabled.

You can use the sn_fpt_convert script to convert a Storage Manager MySQL database from global to per-table data.

Note: The script is intended to be used once, during or before the upgrade process, to convert the Storage Manager database to use one file per table. Systems installed using StorNext 5 release 5.2.x or later already have file-per-table enabled and do not need to run this conversion.

Normally, there are two steps to the conversion, as shown below.

Note: This approach is recommended for large systems, where creating a second instance takes a long time, and when you want to control when Storage Manager restarts.

You can also run the conversion in one step, as shown in Using the -a Option to Perform the Conversion with One Command.

Note: This approach is recommend for smaller installations, where the conversion time is short . Both Storage Manger restarts will occur shortly after the script is invoked.

Caution: Upon completion, a message indicating a successful run is printed to the standard output, and to the log file /usr/adic/mysql/logs/sn_mysql_fpt_log.out.

The message that indicates the successful run is two lines:

Create instance step complete.
You may complete the conversion using the -s option: 'sn_fpt_convert -s'

If this message is not present, the conversion did not complete successfully.

• Step 1 (Create)

  Run the following command to start the conversion process and create a Storage Manager' s MySQL database with per-table data:

  sn_fpt_convert -c
  For additional about the script options and arguments, see Script Options and Optional Arguments.

  Note: Executing sn_fpt_convert -c on a system that has already been converted produces a warning message, indicating that the system already has a per-table datafile setting enabled.

  Note: You must restart Storage Manager two times during the process; once during Step 1 (Create) to apply replication settings, and again during Step 2 (Switch) to perform the final change to use converted database files.

• Step 2 (Switch)

  To switch over to the new database, shut down Storage Manager, then restart Storage Manager using the converted database:

  sn_fpt_convert -s

  Note: The script prompts you to restart Storage Manager.

• Using the -a Option to Perform the Conversion with One Command

  You may use the -a option to combine both steps and perform the conversion with a single command:

  sn_fpt_convert -a

  Note: The script prompts you to restart Storage Manager.

Using actual Tertiary Storage Manager data, the mysqldump timing on a M330 metadata appliance yielded the following results.

Note: The mysqldump time is the longest part of the conversion process.

ibdata1 Size = 3.6 GB
Actual Data Size	2119 Mb
Actual Index Size	1197 Mb
Total mysqldump Duration	00:01:41 (hours:minutes:seconds)

ibdata1 size = 13 GB
Actual Data Size	5048 Mb
Actual Index Size	5539 Mb
Total mysqldump Duration	00:03:10 (hours:minutes:seconds)

ibdata1 Size = 17 GB
Actual Data Size	5929 Mb
Actual Index Size	5391 Mb
Total mysqldump Duration	00:04:35 (hours:minutes:seconds)

ibdata1 size = 766 GB
Actual Data Size	275 GB
Actual Index Size	225 GB
Total mysqldump Duration	05:23:46 (hours:minutes:seconds)

To gauge the total mysqldump duration, use the Actual Data Size as an indicator to yield the most consistent prediction. If we use the Total mysqldump Duration for all data points thus far, the most conservative estimate would be a rate of 70 seconds per GB.