Sample Header Ad - 728x90

Aurora3 RDS MySQL Stored function/trigger performance when using information_schema

0 votes
1 answer
40 views
Since moving to Aurora3 RDS (MySQL 8 based) vs Aurora 2 (MySQL 5.7 based) noticed that execution time of certain triggers and stored functions performance has degraded. Correlation between triggers and functions was the select query on information_schema.table something like SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name LIKE tablename LIMIT 1 RDS Stack has 50+ schemas with close to 2000+ tables in each. Can take > 25s (unloaded system, no other users/connections). Query above run outside of stored function/trigger takes < 0.1s (same as it did in 5.7 stored/trigger). Performs best when single database stack. Can also get better performance by changing the definer of the function/trigger to be schema based user. Unable to reproduce locally on Community Edition of MySQL8.
Asked by BigKiwiDev (1 rep)
Feb 9, 2024, 02:17 AM
Last activity: Dec 31, 2024, 07:31 AM