Challenges
You may face one or more of these time-consuming issues working with MySQL or MariaDB on premise or in the cloud:
- Data discovery: profiling, classification, ERDs
- Unloading and loading large tables
- Routine utility operations (reorgs)
- Complex queries
- Data warehouse integration (ETL) jobs
- Migration or replication
- Finding and masking sensitive data
- Generating smart, safe test data
Specific performance diagnoses and tuning also take time and may affect other users. Finally, stored SQL procedures may also be programmed inefficiently, require optimization, and then still take too long to run.
Solutions
To:
Use:
Speed MySQL Unloads
IRI FACT (Fast Extract) to speed MySQL unloads. Use SQL syntax in the CLI or GUI for FACT to dump table data to flat files.
Specify SELECT * from the table so you do not encumber the unload with qualifiers like distinct, order by, and group by. But once that data are in the flat file, use the SortCL program in IRI CoSort to de-dupe, sort, join, group (and report on) the extracts far faster ... in parallel, outside the database.
Speed MySQL Loads
IRI CoSort to pre-sort flat files for bulk bcp imports.
Create the clustered index first and pre-sort the input files (on the primary index key) to shorten the create index step. Use the SORTED_DATA option when creating indexes to reflect that CoSort bypassed the slower sort in bcp.
Speed MySQL Reorgs and Queries
Offline Reorg wizard in IRI Workbench to combine and automate the combination of the above fast extraction, sorting, and re-loading operations for classic (offline) MySQL reorgs, which facilitate the maintenance of your tables in optimal query order.
Externalize SQL Transformations
IRI CoSort to relieve the database of processing overhead. Leverage file system I/O, multi-threading, and the proven data transformation power and consolidation of the CoSort SortCL program.
Design full-on ETL jobs that leverage this engine in the free IRI Workbench front-end through a subscription to the IRI Voracity data management platform that supports and combines the discovery, integration, migration, governance and analytics of MySQL and other data sources. Voracity also supports every other operation on this page ...
Migrate and Replicate MySQL Databases
IRI NextForm Database Edition to acquire, re-map, re-format, and build/populate new tables during migrations to and from MySQL. You can also use IRI NextForm or the SortCL program in IRI CoSort to re-map and convert data in MySQL, produce custom reports, copies, and federated views of data.
Mask Data in MySQL Columns
IRI FieldShield or IRI DarkShield to classify, find, and mask personally identifiable information (PII), protected health information (PHI) or other sensitive data in MySQL databases. Apply consistent redaction, encryption, or many other de-identifying functions to one or more columns at a time to preserve referential integrity, provision test data, and comply with data privacy regulations like the CCPA, HIPAA, PCI DSS, FERPA, and the GDPR.
Generate MySQL Test Data
IRI RowGen to rapidly populate MySQL operations with safe test data. RowGen uses your data models to automatically generate the test data for an entire database with referential integrity. RowGen licenses also include RDB subsetting (with optional masking) to provision test data.
Test databases (clones) with pre-masked or synthesized data can also be provisioned in containers, from the cloud, etc. through a direct IRI-Windocks tie-in.
