I was tracking down why views saves were so slow and noticed this:
There are 4 (for me) runs of this function with query show table status

5459.02 ms
4 queries
backup_migrate_source_db_mysql::query

show table status

So around 20 seconds dedicated to finding out what the table names are during a menu rebuild.

Proposed Solution

  1. Static cache the results at the very least
  2. Find a more efficient query or try the suggestion in stack overflow
  3. Possibly find if there is a menu rebuild and avoid the query all together.

Call Stack

  • sources.db.mysql.inc:330, backup_migrate_source_db_mysql->_get_tables()
  • sources.db.mysql.inc:268, backup_migrate_source_db_mysql->_get_table_names()
  • sources.db.inc:73, backup_migrate_source_db_mysql->backup_settings_default()
  • filters.backup_restore.inc:51, backup_migrate_filter_backup_restore->backup_settings_default()
  • filters.inc:89, backup_migrate_filters_invoke_all()
  • filters.inc:168, backup_migrate_filters_settings_default()
  • profiles.inc:186, _backup_migrate_profile_default_profile()
  • profiles.inc:250, backup_migrate_profile->get_default_values()
  • profiles.inc:226, backup_migrate_profile->__construct()
  • crud.inc:113, backup_migrate_crud_type_load()
  • crud.inc:136, backup_migrate_crud_menu()
  • backup_migrate.module:206, backup_migrate_menu()
  • menu.inc:2824, menu_router_build()
  • menu.inc:2787, menu_rebuild()
  • menu.inc:468, menu_get_item()
  • menu.inc:1775, menu_get_custom_theme()
  • menu.inc:1790, menu_set_custom_theme()
  • common.inc:5326, _drupal_bootstrap_full()
  • bootstrap.inc:2551, drupal_bootstrap()
  • index.php:20, {main}()

Related info

https://dba.stackexchange.com/questions/39993/show-table-status-very-slo...

CommentFileSizeAuthor
#5 3046235-5-show-table-status-static.patch7.56 KBjoelpittet
#2 3046235-2-show-table-status-static.patch5.23 KBjoelpittet

Comments

joelpittet created an issue. See original summary.

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Issue summary: View changes
Status: Active » Needs review
StatusFileSize
new 3046235-2-show-table-status-static.patch5.23 KB

Ok this doesn't replace the query because I don't have a good alternative yet... but it cuts the query down to 1/4 by storing the result in a static. It can't be a member variable because there are more than one instance being created.

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Issue summary: View changes
joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Issue summary: View changes

Adding call stack to issue summary.

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
StatusFileSize
new 3046235-5-show-table-status-static.patch7.56 KB

Considering the call stack only needs the table names for the menu stuff, show full tables where Table_Type = 'BASE TABLE' is much faster so I static'd and swapped that out for both views and tables.

Also this is mitigated by configuring your mysql with set global innodb_stats_on_metadata = 0; but that wasn't the default for my drupalvm but it is for percona 5.7 so... you're lucky if you're using percona:)

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented

The query is now only 1 query of 4.51ms!

Status: Needs review » Needs work

The last submitted patch, 5: 3046235-5-show-table-status-static.patch, failed testing. View results
- codesniffer_fixes.patch Interdiff of automated coding standards fixes only.

joelpittet’s picture

joelpittet
he/him
Primary language English
Location Vancouver
commented
Status: Needs work » Needs review
damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Parent issue: » #3020775: Plan for Backup and Migrate 7.x-3.7
damienmckenna’s picture

damienmckenna
Location TN, USA
commented
Status: Needs review » Fixed

Committed. Thanks Joel!

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.