Other: Improving HVR Performance: Query Optimization on MySQL TABLE_CONSTRAINTS Table
We have noticed significant delays when performing certain actions in HVR, such as redefining a table, adding tables, or activating replication. These delays seem to be due to a particular query that is executed against the "KEY_COLUMN_USAGE" and "TABLE_CONSTRAINTS" tables in MySQL for every table in the channel. The query in question is as follows:
SELECT c.`CONSTRAINT_SCHEMA`, c.`CONSTRAINT_NAME`, c.`CONSTRAINT_TYPE`, k.`COLUMN_NAME`
FROM `INFORMATION_SCHEMA`.`TABLE_CONSTRAINTS` c, `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` k
WHERE c.`CONSTRAINT_NAME` = k.`CONSTRAINT_NAME`
AND c.`TABLE_SCHEMA` = k.`TABLE_SCHEMA`
AND c.`TABLE_NAME` = k.`TABLE_NAME`
AND c.`CONSTRAINT_TYPE` IN ('PRIMARY KEY', 'UNIQUE')
AND c.`TABLE_SCHEMA` = '<table schema>'
AND c.`TABLE_NAME` = '<table name>'
# AND k.`TABLE_SCHEMA` = '<schema name>' AND k.table_name = '<table name>' -- (optimization)
ORDER BY c.`CONSTRAINT_TYPE`, c.`CONSTRAINT_NAME`, c.`CONSTRAINT_SCHEMA`, k.`ORDINAL_POSITION`;
Our concern lies in the fact that this query is executed for every table in the channel, even when performing actions that only involve a specific table. We propose that HVR should optimize its query execution strategy to run this query only for the table or tables that are being added, modified, or redefined. This targeted approach would likely result in a significant reduction in overall execution/activation time.
Additionally, we have observed that an optimization can be made to the query itself. If the query filters the table and schema names on KEY_COLUMN_USAGE.table_name and KEY_COLUMN_USAGE.schema_name instead of on TABLE_CONSTRAINTS.schema_name and TABLE_CONSTRAINTS.table_name, it can potentially cut down the run time by several seconds per table.
We believe that implementing these optimizations would greatly enhance the efficiency and responsiveness of HVR during these critical operations.
Looking forward to your input and any enhancements that could be made!
Please sign in to leave a comment.
Comments
0 comments