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
Last activity: Dec 31, 2024, 07:31 AM