Problem with navigation keys stored in civicrm_settings table / proposal to move it
We've had a huge bloat in our log_civicrm_settings logging table - millions of rows. The culprit is the navigation key, which is stored in civicrm_settings with a name of 'navigation' for many contact IDs.
Every time caches are flushed, the navigation cache strings are 'NULLed' by \CRM_Core_Config::clearDBCache();
and then rewritten by
CRM_Core_BAO_Navigation::resetNavigation();
In practice, if I truncate log_civicrm_settings, and then run I get:
- 2785 distinct contact IDs in log_civicrm_settings
- 39270 new entries for 'navigation' in log_civicrm_settings!
My contact ID's navigation value is first NULLed and updated 13 times to different values with the same log_conn_id ! Multiply that by our 4 domains, and that's 56 entries per contact per cache flush...
-
1 - Why is it being updated so many times? That's a performance issue even without logging. -
2 - Can we move the 'navigation' key out of civicrm_settings, and into a cache table? Probably its own cache table, like civicrm_navigation_cache