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.
After upgrading to StorNext 6, an admin alert is generated, indicating that your system should be converted by running the MySQL file-per-table conversion. Only systems running releases prior to StorNext 5 release 5.2.x need to run the conversion script. Quantum recommends converting and compressing database data files for systems that are using a global datafile in StorNext 6.
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?|
||Systems created using releases prior to StorNext 5 release 5.2.x.||Optional|
||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:
- 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.
- 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.
- 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 a log file. If the message is not present, the conversion did not complete successfully.
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
sn_fpt_convert -con 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.
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.
You may use the
-aoption 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.
You can view the script log output under /usr/adic/mysql/logs/sn_mysql_fpt_log.out.
The system will be in one of several states during the conversion process:
|State 1||Unconverted, the state prior to running the script.|
|State 2||The state after Step 1 (Create) completes. The system is still unconverted, and the secondary has been created and is running.|
|State 3||The state when the system has failed over or been restarted in State 2. The system is still unconverted, and the secondary has been created, but is not running. The secondary will be restarted when Step 2 (Switch) is run.|
|State 4||The state after Step 2 (Switch) completes. The system is converted and the secondary instance is not running|
After Step 1 (Create) has completed, a new Storage Manager MySQL Database secondary instance is created and running (State 2). The secondary instance will remain running until the Step 2 (Switch) is run or the system is restarted or failed over (State 3).
After the system has a secondary created, Step 2 (Switch) may be run. If the system is in State 3, Step 2 (Switch) will restart the previously created secondary instance, returning it to State 2 before continuing. When Step 2 (Switch) returns successfully, the conversion is complete and the secondary instance is shut down (State 4).
Execute the following command to determine if the system has been properly converted after Step 2 (Switch):
Note: Running the command on a converted system (State 4) produces a warning message. This is to be expected and confirms that the system has been properly converted.
You can use the
-F option to re-run the conversion on an already converted system.
This section provides some guidance if an error such as involuntary failover, I/O error, out of space error, or system restart occurs during Step 1 (Create).
If a database conversion fails, as shown below, remove the working directory, start Storage Manager if needed, and then re-run Step 1 (Create). The procedure shown below is different for HA and standalone installations, because they use a different working directory.
Use the following procedure to restart Step 1 (Create) for a standalone installation:
Run the following command:rm -rf /usr/adic/mysql/convert_mysql
- Start Storage Manager (if needed).
Run the following command:sn_fpt_convert -c
For an HA system, use the following procedure on the MDC:
Run the following command:rm -rf /usr/adic/HAM/shared/convert_mysql
- Start the Storage Manager (as needed).
Run the following command:sn_fpt_convert -c
If an involuntary failover occurs during Step 2 (Switch), then execute the following command on the new MDC to restart this step:sn_fpt_convert -s
You can use the following options to recover from a failed conversion. These are mutually exclusive, so use only one. These are the same options discussed earlier in this topic:
Create and start a new Storage Manager database, the secondary instance (for example, Step 1 (Create)).
Note: As mentioned earlier, executing
|Switch Storage Manager to use the secondary instance (for example, Step 2 (Switch)).|
|Use to perform the entire conversion at once (
Displays database size information relevant to the conversion.
Note: This is for output/print only, and does not perform any conversion steps.
Lets you specify the
The default directory location is sufficient for most systems; however, for systems with very limited disk space, this optional argument provides a way to specify an alternate working directory location to use for the conversion process.
Note: Changing the default directory location may increase the overall conversion time and Storage Manager downtime, due to an increased data transfer time.
|Skips the confirmation prompt.|
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.
For these tests, test tables were created using sysbench.
|100 million row table, 25 GB total DB size|
|Total Elapsed Time||01:25:05 (hours:minutes:seconds)|
|MySQL Dump Time||00:02:29 (hours:minutes:seconds)|
|MySQL Restore Time||01:20:19 (hours:minutes:seconds)|
|200 million row table, 49 GB total DB size|
|Total Elapsed Time||02:39:45 (hours:minutes:seconds)|
|MySQL Dump Time||00:05:18 (hours:minutes:seconds)|
|MySQL Restore Time||02:34:09 (hours:minutes:seconds)|
|MySQL Dump Size||14 GB|
|1,000,000,000 row table, 241 GB total DB size|
|Total Elapsed Time||14:29:39 (hours:minutes:seconds)|
|MySQL Dump Time||00:25:37 (hours:minutes:seconds)|
|MySQL Restore Time||13:33:14 (hours:minutes:seconds)|
|MySQL Dump Size||66 GB|
Use the chart in Figure 1 to help estimate the total conversion duration.