Done
Details
Details
Assignee
patrick.birch
patrick.birchReporter
Dmitry Lenev
Dmitry LenevNeeds QA
Yes
Components
Fix versions
Affects versions
Priority
Created December 1, 2022 at 5:40 AM
Updated April 2, 2024 at 1:32 PM
Resolved February 16, 2023 at 11:37 AM
We need to document new system variables, status variables and small change in SHOW CREATE TRIGGER behavior which were introduced by https://jira.percona.com/browse/PS-7963 and https://github.com/percona/percona-server/pull/4921 as part of smart trigger caching/lazy loading implementation.
Here are some details:
This patch allows to avoid using table instances with fully loaded and parsed triggers by read-only queries. Also some data which we were copying for each open table instance in Table Cache is now associated only with table share in Table Definition Cache. These steps should to reduce memory consumption by Table Cache on systems which use triggers, but also have significant read-only workload for the same tables .
New table_open_cache_triggers system variable and start-up opton was added. It is similar to table_open_cache variable/option and allows to set soft limit on maximum number of open tables in Table Cache which have fully loaded triggers. If number of open table instances with fully loaded triggers in TC exceed this value, we will try to expel unused table instances with fully loaded triggers from TC (using LRU principle). By default set to high value so existing users should not observe change in behavior.
New table_open_cache_triggers_hits, table_open_cache_triggers_misses, table_open_cache_triggers_overflows status variables which are similar to existing TC-related status variables. In this context hit means that statement required open table instance with fully loaded triggers and was able to get one from TC, miss means that table requiring such open table instance was not able to found it in TC (but might have found table instance without fully loaded triggers), overflows - indicates how many unused table instances with triggers were expelled from TC due to hitting table_open_cache_triggers soft limit. Should allow to understand if table_open_cace_triggers value is set too low.
There is a change in SHOW CREATE TRIGGER behavior. This statement now shows definition even for triggers which exist (i.e. were created at some earlier point) but are unparseable now. E.g. when they become such after upgrade to new server version which outlaws some syntax used in trigger.