1

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?
Improve this question

2 Answers 2

Reset to default
0

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.

Improve this answer
1
  • My understanding is that InnoDB gets its approximate row counts by diving into the various tables' files and seeking a few locations within it. Faster than COUNT(*) but still significant with lots of hefty tables. When SHOW TABLE STATUS runs, I see lots of queries that normally would be nearly instant sitting around waiting. Killing the SHOW TABLE STATUS lets them all execute normally.
    – ceejayoz
    Jul 25, 2012 at 18:02
0

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.

Improve this answer
7
  • By any chance, do you have innodb_file_per_table enabled? If not, try enabling it (and dumping, dropping, and re-importing the tables). I suspect that would allow the disk reads to be much more intelligent than they are when ibdata1 is getting hit for everything.
    – BMDan
    Jul 25, 2012 at 18:54
  • @BMDan innodb_file_per_table is set to 1.
    – ceejayoz
    Jul 25, 2012 at 19:06
  • Are these databases actually in their own .ibds? What's the size of your innodb_buffer_pool? How are table_open_cache, open_files_limit, and the sysctl fs.file-max set? How much memory do you have under Cached in free -m? What's your vm.vfs_cache_pressure set to? Is your disk subsystem performant? I'm beating so hard on this because generating table statistics should be very, very fast in the vast majority of cases; if it's not, that's at best a misconfiguration, because otherwise it's a bug.
    – BMDan
    Jul 25, 2012 at 19:33
  • Yes, all in their own .ibd file. innodb_buffer_pool_size=5662310400. table_open_cache=128. open_files_limit=5000. fs.file-max=766730. 6550 cached in free -m. vm.vfs_cache_pressure=100. No disk subsystem issues to our knowledge.
    – ceejayoz
    Jul 25, 2012 at 19:41
  • Your table_open_cache appears to be too small, but otherwise your settings seem reasonable, if a bit arbitrary (5400 MB buffer pool, rather than 5 GB even?). Try bumping table_open_cache to 1536 and see what happens.
    – BMDan
    Jul 31, 2012 at 20:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .