Home / server / Questions / 411161
In Process
ceejayoz
Asked: 2012-07-26 09:26:53 +0800 CST

large number of InnoDB tables plus SHOW TABLE STATUS

  • 772

We've got several hundred InnoDB tables in a database, and we use phpMyAdmin to manage them. Unfortunately, phpMyAdmin does a SHOW TABLE STATUS query whenever the list of tables is shown, and this seems to dig into each InnoDB table to get an approximate row count.

This seems to lock up the entire database, which subsequently means all other queries to this (busy) database all queue up until the database hits the max users.

  1. Can SHOW TABLE STATUS be sped up in a reasonable manner?
  2. Can phpMyAdmin be easily modified to not do a full SHOW TABLE STATUS query, or at least not lock the entire database at once for it?
mysql

2 Answers

  1. SHOW TABLE STATUS doesn't lock tables—at least, I've certainly never encountered it doing so. Furthermore, the row count for InnoDB in SHOW TABLE STATUS is just an estimate, so it's definitely not doing a COUNT(*) or moral equivalent. What do you get from a SHOW PROCESSLIST while the SHOW TABLE STATUS is running?

    One possibility to investigate: your table_open_cache might be too small, causing you to get stuck closing and re-opening many tables in the course of running your status query.

    • 0

  2. Just discovered the innodb_stats_on_metadata setting, which appears to do the trick. Disables InnoDB's diving into the indexes to get approximate counts.

    • 0